Oracle索引和事务

第四章索引和事务

 

1. 什么是索引？有什么用？

1）索引是数据库对象之一，用于加快数据的检索，类似于书籍的目录。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。

　　2）索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率

　　3）索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；

　　4）索引一旦建立，在表上进行dml操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，索引删除，不会对表产生影响

　　5）索引对用户是透明的，无论表上是否有索引，sql语句的用法不变

　　6）oracle创建主键时会自动在该列上创建索引

 

2. 索引的底层实现原理

         当一列索引创建成功后，oracle系统会这个列复制一个份放入缓存，并按照一定规则做排序和去重，从而减少检索数据的范围。但是索引不是创建的越多越好，因为一个索引的创建，就需要在缓存中开辟一个空间，过多的索引反而会加重数据库的检索速度。

 

3. 索引的设计理念

1）某个字段在条件查询中会被高频率的调用 where 条件

2）表关联的连接字段 on 条件

3）实际业务需求 

 

4. 索引的分类

         1) 唯一索引

                  主键会默认添加索引

         适用：主键

         2）btree索引（默认索引）

1.oracle中最常用的索引；b树索引就是一颗二叉树；叶子节点（双向链表）包含索引列和指向表中每个匹配行的rowid值

　　      2.所有叶子节点具有相同的深度，因而不管查询条件怎样，查询速度基本相同

　　      3.能够适应精确查询、模糊查询和比较查询

      适用：列基数（列不重复值的个数）大时适合使用b树索引

      3）位图索引

            创建位图索引时，oracle会扫描整张表，并为索引列的每个取值建立一个位图（位图中，对表中每一行使用一位（bit，0或者1）来标识该行是否包含该位图的索引列的取值，如果为1，表示对应的rowid所在的记录包含该位图索引列值），最后通过位图索引中的映射函数完成位到行的rowid的转换

      适用：对于基数小的列适合简历位图索引（例如性别等）

         4）单列索引和复合索引

            如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引，即至少要包含组合索引的第一列。即索引按照顺序被调用

 

 

 

5. 索引操作

查看索引

select * from user_indexes  where table_name='employees';

创建btree索引

create    index      index_name    on    table_name(colum1,colum2…);

创建位图索引

create    bitmap index      index_name    on    table_name(colum1,colum2…);

 

修改索引

alter index index_name1 rename to index_name2;

 

删除索引

drop index index_sno;

 

索引在全局唯一

 

6. 图形化操作

 

 

 

事务

   事务是操作数据库最小的单位，是1条或n条sql语句组成的逻辑。

四大特性

   1. 原子性a（atomic）

      事务是一个整体，不可分割，一起成功or一起失败

   2. 一致性c（consistency）

      事务执行前和执行后必须处于一致性状态  

   3. 隔离性i（isolation）

      多事务并发时，相互独立，互不影响

   4. 持久性d（durability）

      数据库数据若不变更，会永久不变

 

事务会造成的问题

   1. 脏读

   脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据

   2. 不可重复读

   不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。

   3. 幻读

   在读写一批数据时，发现读取到的数据不一致，就是发生了幻觉一样

  以上问题，都是发生在多事务并发时，数据前后不一致造成的

 

     数据库提供的四种隔离级别：

read uncommitted(读未提交)：最低级别，任何情况都会发生。

read committed(读已提交)：可避免脏读的发生。

repeatable read(可重复读)：可避免脏读、不可重复读的发生。

serializable(串行化)：避免脏读、不可重复读，幻读的发生。

 

mysql默认级别是 可重复读；

oracle 仅支持读已提交和串行化，默认级别是 读已提交

 

锁机制

1. 共享锁/乐观锁（share locks，即s锁）加了共享锁的数据对象可以被其他事务读取和修改

2. 排它锁/悲观锁（exclusive locks，即x锁）:当数据对象被加上排它锁时，其他的事务不能对它读取和修改

 

死锁

1. 两张表t1，t2

2. a用户已访问t1表，并加锁t1表，尝试访问t2表；访问不到t2表，就不撤t1表的锁

3. b用户已访问t2表，并加锁t2表，尝试访问t1表；访问不到t1表，就不撤t2表的锁

4. 两个用户就发生了死锁