1.数据库课堂笔记之索引

B-Tree(B+Tree平衡树索引)

读数据:将block(4k磁盘针转一圈正好读取4K)整块数据读入到内存中.
存数据:块头(物理标示等)
当装不下了(比如第4条记录),这时会link到一个新block.当读取时前三条记录有一次IO操作,而第4条记录有两次IO操作.这种一条记录存储在多个block的情况称为行迁移.行迁移可能在插入和修改时产生.
避免行迁移方法:
1)
2)设置block最大存储(一般设置为70%,即当存储到一个block的70%时,换另一个block存储)
3)

当数据库变动较大(经常插入更新操作)等情况会导致数据库越用越慢.

当B-Tree深度较大时,导致读取数据的IO操作数多,导致读取过慢,可以扩展B-tree的宽度,深度在3-4层.

B-tree(按顺序存储)适合查询方式:

1)等值查询.IO次数为根节点到存储查询值索引的深度+基本表查询IO次数(没有发生行迁移则为1次,发生一次行迁移则IO次数为2次).
2)范围查询.IO次数取决于查询数据在B-tree和基本表存储的block数.
如:当查询的100条数据存储在一个B-tree的block中,从B-tree中取rowid的IO次数为B-tree深度.
3)字符串前缀查询.

使用索引也有可能降低查询效率

当查询数据基本等于基本表中的所有数据时,使用索引反而会增加IO操作,降低查询效率,并且还浪费存储空间.

检索比例

查询子集占基本表的比例(参考值10%,当检索比例低于10%,倾向于使用索引)
注: 10%只是经验值.
胖表: 如果基本表中一个block中可以存储更多条记录,那么检索比例更低时才倾向于使用索引.
瘦表:如果基本表中一个block中存储较少条记录,那么检索比例更高时才倾向于使用索引.

目录与索引

索引是一种以原子粒度访问数据的手段,而不是为了检索大量数据的.

什么时候使用索引

1)仅当要通过索引访问表中很少一部分行
2)如果要处理表中多行,可以使用索引而不用表.
如果能够在索引表中找到所查询的所有字段,这时不需要使用基本表.

索引方式

1)单字段索引
叶节点形式:查询字段值+rowid
2)组合索引
除叶子结点外,其他节点与单字段索引相同.
以组合字段的第一个字段作为主索引字段,即第一个字段在叶子结点中是有序的,其他字段不是.

其他索引类型

1)哈希索引:mysql
优势: 快
缺点:

1. 只能做等值查询.不能做范围查询和前缀查询.
2. 可能产生冲突,导致读取基本表时会读取多个block,产生多个IO操作.

2)位图索引(BitMap):oracle
如果用户查询的列的基数非常的小， 即只有的几个固定值，如性别、婚姻状况、行政区等等。要为这些基数值比较小的列建索引，就需要建立位图索引。
位图索引博客链接
缺点:不适用于所有的索引键更新.
3)位图联结索引:oracle
4)函数索引
适用场景: where中便有f(x)函数
比如: 不区分大小写.输入x为任意字母,f(x)函数为转换为大写或者小写函数,f(x)值为全部大写值或全部小写值.
5)全文索引
原理是先定义一个词库，然后在文章中查找每个词条(term)出现的频率和位置，把这样的频率和位置信息按照词库的顺序归纳，这样就相当于对文件建立了一个以词库为目录的索引，这样查找某个词的时候就能很快的定位到该词出现的位置。

索引与外键

对主键默认构建索引,大部分也会系统地对外键构建索引,尤其是当表很大时.

如果系统为外键自动增加索引,常常会导致同一字段属于多个索引.
稳定的外键放在后面,不稳定的外键放在前面.
orders(oid, cid,)

系统生成键

1)系统生成序列号(自增字段),远好于

1. 寻找当前最大值并加1
2. 用一个专用表保存“下一个值”且加锁更新

2)如果插入并发性过高,在主键索引的创建操作上会发生严重的资源竞争
解决方法:

1. 反向键索引(逆向索引)
   如插入9527、9528、9529,对其进行反向为:7259、8259、9259,构建索引,这时它们很可能不放到同一个block中,这样便不会发生冲突.
2. 哈希索引

为什么没有使用索引

2)使用select count(*) from T, 而且T上有索引,但是优化器仍然全表扫描
3)对于一个有索引的列作出函数索引
4)
5)如果使用了索引,实际上反而更慢
6)没有正确的统计信息,造成CBO无法做出正确的选择
总结: 不使用索引通常是不能使用索引,使用索引会返回不正确的结果或者不该使用索引,如果使用了索引就会变得更慢.