MySQL数据库索引的研究

1、    mysql数据库索引的研究    摘要：数据库索引是用于提高数据检索速度的关键数据结构，该文结合常用的数据库索引结构b树，分析索引的原理，并结合外存储的原理，分析大多数数据库使用b+树作为索引结构的原因，并结合mysql数据库中innodb存储引擎中的索引实现，分析其优缺点。关键词：b树结构；外存储原理；mysql索引：tp311 ：a ：1009-3044（2014）34-8079-02本文的内容结构如下所示：第一部分主要从数据结构及算法理论层面讨论b-tree。第二部分结合外存储器的存储原理，讨论使用btree作为索引的原因。第三部分讨论my

2、sql数据库中innodb数据存储引擎中的索引改进的b+tree的结构，及其优点。1 索引的数据结构及算法基础数据库查询是数据库的最主要功能之一，提高数据查询速度是数据库索引的主要目标，通常，研究者通过优化检索算法来提高查询速度。查找算法有几类，一种是顺序查找，算法的复杂度为o（n），另外有二分查找、二叉树查找等。一般来说，一种查找算法对应一种数据结构，例如顺序查找对应连续的数据，二分查找对应排好序的数据，二叉树搜索对应二叉查找树。但是，这类数据结构还完全不能满足各种查找需求。比如，二分查找的条件要求将数据排序，但是数据库中不可能同时将两列都按顺序存储。所以，数据库系统必须维护满足特定查找算法

3、的数据结构索引，以实现更高级的查找算法。当前，大部分数据库系统都采用btree作为索引结构，其原因在于btree的数据结构和主流外存储器的存储原理。下面先介绍b-树的基本结构。b-树的基本属性：1） 定义任意非叶子结点最多只有m个儿子；且m>2；2） 根结点的儿子数为2， m；3） 除根结点以外的非叶子结点的儿子数为m/2， m；4） 每个结点存放至少m/2-1（取上整）和至多m-1个关键词；（至少2个关键词）；5） 非叶子结点的关键词个数=指向儿子的指针个数-1；6） 非叶子结点的关键词：k1， k2， ， km-1；且ki < ki+1；7） 非叶子结点的指针：p1， p2，

4、， pm；其中p1指向关键词小于k1的子树，pm指向关键词大于km-1的子树，其它pi指向关键词属于（ki-1， ki）的子树；8） 所有叶子结点位于同一层；如图1所示，是b-树的基本结构。关于如何在一棵b-树中按照key的值去检索数据，描述如下：1） 以根节点作为入口，对key集合作二分查找，如果找到目标key，则可以直接返回data数据。否则进入第二步。2） 找到相应区间，根据区间的指针指向的point，得到对应的节点，执行1中过程。如此递归，直到找到目标节点，或者最终返回null point。加速一个b-树的度为d，有n个key，现在为其建立索引，那么这棵b-树的高h最大为logd（n+

5、1）/2），查找目标key，查找过程中需要遍历节点个数的复杂度为o（logdn），与二叉查找和顺序查找相比，b-树是一个效率很高的索引数据结构。图1 b-tree的结构通常，数据库中数据都很多，导致建立的索引也很大，很难全部存储在内存中，因此，通常将索引保存在磁盘中。这样，在索引中查找时，就需要进行磁盘i/o，而与内存存取速度比较，磁盘i/o的速度要慢几个数量级。所以，通常我们将在查找过程中磁盘的操作次数作为我们评价一种数据结构是否适合作为索引的关键信息。那么，建立好的索引就是要尽量减少查找过程中磁盘i/o的次数。了解磁盘的存取原理，对应理解使用b-树作为索引结构必不可少。2 磁盘的存储原理磁

6、盘的内部结构如图2所示。操作系统在读取磁盘数据时，将数据逻辑地址传给磁盘，磁盘会将操作系统传来的逻辑地址转换为物理地址以确定需要读取的数据的具体位置，即数据所在的磁道和扇区，那么，为了读取目标数据，磁盘需要将磁头移动到对应的那个扇区上，磁盘是通过横向移动磁头做到这点的。移动磁头的过程叫做寻道，而这个过程所耗费时间叫做寻道时间。然后磁盘需要通过旋转将目标扇区旋转到磁头下，这个过程所花费的时间叫做旋转时间。图2 磁盘的内部构造图磁盘的存储结构和寻址方式，导致了磁盘的存取速度比通常的主存储器慢几个数量级。磁盘在寻址过程中的需要的机械运动的时间，是导致磁盘在查找存储数据速度缓慢的主要原因。所以，如果想

7、要提高数据库存储速度，减少磁盘io次数是主要途径。其中一个可行的操作方案就是：每次寻址之后，预读一定的磁盘空间的内容。这样在读取相同大小空间的数据内容时，减少了机械寻址的时间，可以大大减少磁盘的存储速度。实际上，磁盘也正是这样做的。通常，磁盘在读取数据的时候，都会预取出一定长度的数据块，称为页。通常，页是4k大小。由前面对b树的介绍我们可以知道，进行一次检索需要访问h（b树的高度）个节点。那么，h的大小即是衡量一个索引好坏的标准。假设一个数据库表中需要保存n条信息，b树中每个节点保存d个key，那么由前文可知：h=logdn；d越大，h就越小。利用磁盘的预读机制，我们可以将一棵b的节点大小设定

8、为一个页的大小，那么，在磁盘在预读的时候，取出的一个页都是有用key信息，这样大大提高了数据的存储效率，加快了检索的过程。 由以上内容可以判断，b-树非常适合作为索引结构，其查找效率是非常高的。3 mysql中的索引结构分析b-树有多重改进版本，mysql使用的b+树就是其中一种。b+树的改进有以下两点：1） 节点的指针数量可key数量一一对应。2） 内节点不保存data，只保存key和指针；叶子节点只保存key和data，不存储指针。mysql中索引的结构如图3所示：b+树的性能分析：从b+树的性质我们可以知道，b+树的内节点不保存data值，只保存key和指针。那么，假设一个页的大小空间用

9、于保存一个节点，这样在同一个节点中能够保存的key值比b-树结构会更多，即d更大。由h=logdn可知，h更小。所以b+树比b-树更适合作为索引结构。由图3中可以看到，mysql索引的结构，是一种改进的b+树，其每个叶子节点中都包含一个指向相邻叶子节点的指针。添加这个指针的目的是为了提高区间访问的性能。如图3所示，查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。4 总结使用数据库索引是提高数据库查询速度的关键因素，合理利用索引，对于提高数据库性能有很大帮助。数据库的性能优化需要对数据库的索引内部结构及其原理

10、有深入的了解。该文阐述了btree索引的基本原理，并说明了mysql数据库的索引实现，为索引调优和数据库性能优化提供了有效的帮助。参考文献：1 d comer.ubiquitous b-tree； acm computing surveys （csur）， 1979.2 codd e f.a relational model of data for large shared data banks lm.communications of the acm， 1970，13（6）：377-387.3 baron scbwartz.高性能mysqlm.王小东，译.北京：电子工业出版社，2010.4

11、姜承尧.mysql技术内幕-innodb存储引擎m.北京：机械工业出版社，2011. 由以上内容可以判断，b-树非常适合作为索引结构，其查找效率是非常高的。3 mysql中的索引结构分析b-树有多重改进版本，mysql使用的b+树就是其中一种。b+树的改进有以下两点：1） 节点的指针数量可key数量一一对应。2） 内节点不保存data，只保存key和指针；叶子节点只保存key和data，不存储指针。mysql中索引的结构如图3所示：b+树的性能分析：从b+树的性质我们可以知道，b+树的内节点不保存data值，只保存key和指针。那么，假设一个页的大小空间用于保存一个节点，这样在同一个节点中能够

12、保存的key值比b-树结构会更多，即d更大。由h=logdn可知，h更小。所以b+树比b-树更适合作为索引结构。由图3中可以看到，mysql索引的结构，是一种改进的b+树，其每个叶子节点中都包含一个指向相邻叶子节点的指针。添加这个指针的目的是为了提高区间访问的性能。如图3所示，查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。4 总结使用数据库索引是提高数据库查询速度的关键因素，合理利用索引，对于提高数据库性能有很大帮助。数据库的性能优化需要对数据库的索引内部结构及其原理有深入的了解。该文阐述了btree索引

13、的基本原理，并说明了mysql数据库的索引实现，为索引调优和数据库性能优化提供了有效的帮助。参考文献：1 d comer.ubiquitous b-tree； acm computing surveys （csur）， 1979.2 codd e f.a relational model of data for large shared data banks lm.communications of the acm， 1970，13（6）：377-387.3 baron scbwartz.高性能mysqlm.王小东，译.北京：电子工业出版社，2010.4 姜承尧.mysql技术内幕-innod

14、b存储引擎m.北京：机械工业出版社，2011. 由以上内容可以判断，b-树非常适合作为索引结构，其查找效率是非常高的。3 mysql中的索引结构分析b-树有多重改进版本，mysql使用的b+树就是其中一种。b+树的改进有以下两点：1） 节点的指针数量可key数量一一对应。2） 内节点不保存data，只保存key和指针；叶子节点只保存key和data，不存储指针。mysql中索引的结构如图3所示：b+树的性能分析：从b+树的性质我们可以知道，b+树的内节点不保存data值，只保存key和指针。那么，假设一个页的大小空间用于保存一个节点，这样在同一个节点中能够保存的key值比b-树结构会更多，即d

15、更大。由h=logdn可知，h更小。所以b+树比b-树更适合作为索引结构。由图3中可以看到，mysql索引的结构，是一种改进的b+树，其每个叶子节点中都包含一个指向相邻叶子节点的指针。添加这个指针的目的是为了提高区间访问的性能。如图3所示，查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。4 总结使用数据库索引是提高数据库查询速度的关键因素，合理利用索引，对于提高数据库性能有很大帮助。数据库的性能优化需要对数据库的索引内部结构及其原理有深入的了解。该文阐述了btree索引的基本原理，并说明了mysql数据库的索引实现，为索引调优和数据库性能优化提供了有效的帮助。参考文献：1 d comer.ubiquitous b-tree； acm computing surveys （csur）， 1979.2 codd e f.a relational model of data for large shared data banks lm.communications of t