首页 > 代码库 > 数据库中的索引

数据库中的索引

  很多朋友在操作数据库的时候经常会用到索引,通过索引获取数据的确可以让这个sql语句运行的更快,但是很多人并不知道为什么使用索引会变快,更不用说索引的一些弊端了,今天主要介绍一下索引。


  1. 索引结构:

      首先声明,索引有很多种,B树索引、hash索引、位图索引、文本索引等等,这里仅介绍工作中用的最多的B树索引。

      其实B树索引就像名字所描述的那样,就是一棵平衡树(balance tree,当然肯定不止这么简单),这里以mysql的innodb存储引擎为例,平衡树非叶节点存储的是(key,address)、即索引值以及下一层某块地址这样的数据结构;而叶子节点在非主键索引中存储的是(key, primary key)、即索引值以及对应的主键值这样的键值对结构,在主键索引中存储的是(primary key, content)、即主键值以及这一行内容。

    以下是一个两层索引的结构图:(红色代表一个数据块即数据库IO最小单元,蓝色代表上面讨论   的键值对)

    技术分享


    以一个数据块16k,一个非(primary key, content)类型的键值对50+4 bytes 为例,则一个数   据块可以存储约(1600/50)=300个这样的键值对结构,那么到了第二层,就可以存储(300*300)   =9w条被索引数据对应的主键信息,到了第三层就是(300*300*300)=2700w,以此类推,飞速增长。

    当然随着数据的增减,rdbms会自动维护索引,这也是为什么索引对查询有利,对增删改有弊的原   因。


 2.简单比较:

     以上面的索引为例,现在假设有一张9w行的表,一个非主键索引就占有约300个数据块,那么这    里不妨假设主键索引占有600个数据块(包括叶节点的所有数据,当然实际生产应该只会更大)。

     在oracle中,lotp系统缓存命中率低于95%通常都会建议增加内存,因此这里假设所有的索引数    据以及表数据都存放在内存中了。

   场景一:在有索引列的情况下,获取某一行数据

    在这种情况下,如果全表扫描,则需要扫描约(1+600)/2=300个数据块;如果根据上面的索引    则需要扫描2(根据索引列获取主键)+2(不妨假设这里主键索引也只有两层)=4个数据块,理想    情况下后者只用前者4/300=1/75的时间。

   场景二:全表扫描

    在这种情况下,若果全表扫描,则扫描600个数据块;如果根据上述索引则需要扫描4*9w=36w个    数据块,此时后者时间远大于前者!

     在这个场景下,通过计算600/4=125,可以知道如果通过索引获取的数据超过125行那么利用索    引的效率还不如全表扫描,在oracle RBO的年代里面可能真的就会不管成本有索引就用索引,但是    现在oracle已经将优化策略改成CBO了,在扫描行太多的情况下会选择全表扫描,mysql是怎么处理    的还不清楚。


3.索引利弊

 1)索引是随机读的

    如果笔者没有记错,计算机组成原理中,cache会将内存中临近的数据cache到CPU中,虽然内存    跟cache的速度相差没有内存与磁盘那么明显,但是随机的内存读,肯定还是没有将数据全部cache    到CPU快的。


 2)索引并不适合大量查询

     就像上面的例子,如果扫描行超过125行那么还不如全表扫描,虽然在工作中会不一样,但希望    大家做范围扫描的时候多注意一下,不要一次扫描的行太多。


 3)索引耗费空间

    其实索引说白了就是一个只有两列的数据表,在innodb中有一列已经确定了就是主键(即使没    有指定主键,mysql也会自己创建一个),这也是为什么主键不要太长的原因之一,对于一个有n个    B树索引的表,主键要存储n+1次,被索引的列也要多存储一次。


4.补充

   其实索引要考虑的东西还有很多,比如聚合因子、锁相关信息等等,希望大家有空的时候自己在下面多多研究。

                        2016.12.24

                        肯草在深圳


本文出自 “肯草在深圳” 博客,谢绝转载!

数据库中的索引