MySQL优化-索引

索引的本质

官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构

 本质：索引是数据结构，

查询是数据库的最主要功能之一。我们都希望查询速度能尽可能快，因此数据库系统的设计者会从查询算法角度优化

最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的

 好在CS的发展提供了很多更优秀的查找算法，如二分查找（binary search）、二叉树查找（binary tree search）等

 稍微分析一下会发现，每种查找算法都只能应用于特定数据结构之上，如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树，但数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织）

 所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法

 这种ADT，就是索引。

总结：除数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

图1展示了一种可能的索引方式

 左边是数据表，一共有两列14条记录，最左边是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上并不一定物理相邻）

 为加快Col2的查找，可维护一个右边所示二叉查找树，每个节点分别包含索引键值及一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2 N)内取到相应数据

虽然这是一个货真价实的索引，但实际数据库系统几乎没有使用二叉查找树或其进化品种红黑树（red-black tree）实现。

B Tree和B+Tree

目前大部分数据库系统及文件系统都采用B Tree或其变种B+Tree作为索引结构

   B Tree

定义数据记录为一个二元组key, data

key为记录的键值，对于不同数据记录，key是互不相同的

data为数据记录除key外的数据。

那么B Tree满足下列条件

• d为大于1的一个正整数，称为B-Tree的度
• h为一个正整数，称为B-Tree的高度
• 每个非叶节点由n-1个key和n个指针组成，其中d<=n<=2d
• 每个叶节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null
• 所有叶节点具有相同的深度，等于树高h
• key和指针互相间隔，节点两端是指针
• 一个节点中的key从左到右非递减排列
• 所有节点组成树结构
• 每个指针要么为null，要么指向另外一个节点
• 如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于>v(key1),v(key1)为node的第一个key的值
• 如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于v(keym),v(keym)为node的最后一个key的值。
• 如果某个指针在节点node的左右相邻key分别是keyi,keyi+1且不为null，则其指向节点的所有key小于v(keyi+1)且大于v(keyi)

图2 d=2的B-Tree示意图

由于B Tree的特性,按key检索数据的算法非常直观

 

首先从根节点二分查找

如果找到则返回对应节点的data

否则对相应区间的指针指向的节点递归进行查找

直到找到目标节点/null指针，查找成功/失败

关于B-Tree有一系列有趣的性质，例如一个度为d的B-Tree，设其索引N个key，则其树高h的上限为

 

检索一个key，其查找节点个数的渐进复杂度为

 

从这点可以看出，B Tree是一个非常有效率的索引数据结构

   B+Tree

B-Tree有许多变种，其中最常见的是B+Tree，MySQL普遍用其实现其索引结构

与B Tree相比，B+Tree有以下不同点

 

• 每个节点的指针上限为2d
• 内节点只存key
• 叶节点不存指针,叶节点指向被索引的数据而不是其他叶节点,在innodb中,指向的是主键,myshaym中指向的是数据的物理地址

图3 一个简单的B+Tree

由于并不是所有节点都具有相同的域，因此B+Tree中叶节点和内节点一般大小不同

这点与B Tree不同，虽然B Tree中不同节点存放的key和指针可能数量不一致，但是每个节点的域和上限是一致的，所以在实现中B Tree往往对每个节点申请同等大小的空间

一般来说，B+Tree比B Tree更适合实现外存储索引结构

   带有顺序访问指针的B+Tree

在经典B+Tree的基础上进行了优化，增加了顺序访问指针

图4

如图4所示，在B+Tree的每个叶节点增加一个指向相邻叶节点指针，形成带有顺序访问指针的B+Tree

此优化的目的是提高区间访问的性能，例如图4中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提高了区间查询效率

   为什么使用B Tree（B+Tree）

红黑树也可用来实现索引，但是文件系统及数据库系统普遍采用B/+Tree,何也?

一般来说，索引本身也很大，不可能全存内存，往往以索引文件的形式存在磁盘

索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。

换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数

   主存存取原理

计算机使用的主存基本都是随机读写存储器（RAM），这里抛却具体差别，抽象出一个十分简单的存取模型来说明RAM的工作原理

图5 4x4的主存模型

从抽象角度看，主存是一系列的存储单元组成的矩阵，每个存储单元存储固定大小的数据。

每个存储单元有唯一的地址，现代主存的编址规则比较复杂，这里将其简化成一个二维地址：通过一个行地址和一个列地址可以唯一定位到一个存储单元。

存取过程 当系统需要读取主存时，将地址信号通过地址总线传给主存，主存读到地址信号后，解析信号并定位到指定存储单元，然后将此存储单元数据放到数据总线，供其它部件读取。

写主存的过程类似，系统将要写入单元地址和数据分别放在地址总线和数据总线上，主存读取两个总线的内容，做相应的写操作。

这里可以看出，主存存取的时间仅与存取次数呈线性关系，因为不存在机械操作，两次存取的数据的“距离”不会对时间有任何影响，例如，先取A0再取A1和先取A0再取D3的时间消耗是一样的

磁盘存取原理

上文说过，索引一般以文件形式存储在磁盘上，索引检索需要磁盘I/O与主存不同，磁盘I/O存在机械消耗，因此磁盘I/O时间消耗巨大

图6 磁盘的整体结构示意图

磁盘由大小相同且同轴的圆形盘片组成，磁盘可以转动（各磁盘必须同步转动）。

在磁盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容。磁头不能转动，但是可以沿磁盘半径方向运动（实际是斜切向运动），每个磁头同一时刻也必须是同轴的，即从正上方向下看，所有磁头任何时候都是重叠的（不过目前已经有多磁头独立技术，可不受此限制）

图7 磁盘结构的示意图

盘片被划分成一系列同心环，圆心是盘片中心，每个同心环叫做一个磁道，所有半径相同的磁道组成一个柱面。磁道被沿半径线划分成一个个小的段，每个段叫做一个扇区，每个扇区是磁盘的最小存储单元。为了简单起见，我们下面假设磁盘只有一个盘片和一个磁头。

当需要从磁盘读取数据时，系统会将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。

为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点，磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间，然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。

局部性原理与磁盘预读

由于存储介质特性，磁盘本身存取就比主存慢，再加上机械运动耗费，磁盘的存取速度往往是主存的几百万分之一，因此为了提高效率，要尽量减少磁盘I/O。

为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：

当一个数据被用到时，其附近的数据也通常会马上被使用

程序运行期间所需要的数据通常比较集中

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

预读的长度一般为页（page）的整数倍

页是存储器的逻辑块，操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页大小通常为4k），主存和磁盘以页为单位交换数据。

当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行

B-/+Tree索引的性能分析

到这里终于可以分析B-/+Tree索引的性能了

上文说过一般使用磁盘I/O次数评价索引结构的优劣。

先从B-Tree分析，根据B-Tree的定义，可知检索一次最多需要访问h个节点

数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入

为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：

每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)。

一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）

综上所述，用B-Tree作为索引结构效率是非常高的。

而红黑树，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多

上文还说过，B+Tree更适合外存索引，原因和内节点出度d有关。从上面分析可以看到，d越大索引的性能越好，而出度的上限取决于节点内key和data的大小：

dmax=floor(pagesize/(keysize+datasize+pointsize))

floor表示向下取整。由于B+Tree内节点去掉了data域，因此可以拥有更大的出度，拥有更好的性能。

这一章从理论角度讨论了与索引相关的数据结构与算法问题，下一章将讨论B+Tree是如何具体实现为MySQL中索引，同时将结合MyISAM和InnDB存储引擎介绍非聚集索引和聚集索引两种不同的索引实现形式。

MySQL索引实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式

    MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点data域存放数据记录的地址

图8 MyISAM索引的原理图

设Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示例。

可以看出MyISAM的索引文件仅仅保存数据记录的地址。

在MyISAM中，主索引和辅索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅索引的key可以重复

如果我们在Col2上建立一个辅索引，则此索引的结构如下图所示：

图9

同样也是一颗B+Tree，data域保存数据记录的地址。

因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同

第一个重大区别是InnoDB的数据文件本身就是索引文件。

从上文知

• MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址
• 而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引

图10 InnoDB主索引（同时也是数据文件）的示意图

可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形

第二个与MyISAM索引的不同是InnoDB的辅索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，

图11 定义在Col3上的一个辅索引

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，

例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅索引都引用主索引，过长的主索引会令辅索引变得过大。

再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

索引优劣势

优势

1.提高了检索效率，降低数据库的IO成本

2.通过索引列对数据进行排序，降低了数据排序成本，降低CPU的消耗

劣势

1.索引实际也是一张表，该表保存了主键和索引字段，并指向了实体表的记录，所以索引列也是要占用空间的。

2.虽然索引大大提高了查询速度，同时降低了更新表的速度，如对表进行insert，update等，，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新带来的键值变化后的索引信息

3.索引只是提高小路的一种因素，在数据量大的表中就需要建立最优秀的索引，就需要记性查询语句的优化。

索引创建

哪些情况需要建立索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与他表建立关联字段，外键关系需要建立索引
4. 单键/组合索引选择问题？在高并发下倾向组合索引
5. 查询排序中的字段适合创建索引
6. 查询统计或者分组的字段适合创建索引

哪些情况不适合创建索引

1. 表记录太少
2. 经常增删改的表
3. 数据重复且分布平均的字段，如果某列存在许多重复内容，为它建立索引就没有太大意义
4. Where条件用不到的字段不适合建立索引
5. 频繁更新的字段不适合做在索引