高性能MySQL 第五章高性能索引

1 索引类型

索引（在MySQL中也叫做“键(key)”）是存储引擎用于快速找到记录的一种数据结构(这是索引的本质)。当表中的数据比较少的时候，查询的频率比较低的情况下，索引的作用还不是太明显，这时表中的数据可以完全缓存到内存中，就算进行全表扫描也不会太慢；随着表中的数据越来越多，查询频率越来越高，内存已经不能完全缓存数据的时候，索引的性能的提升就会很明显。

索引是在存储引擎层而不是服务器层实现的，所以没有统一的索引标准：不同存储引擎的索引的工作方式不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。在MySQL中通过索引找到对应值，然后根据匹配的索引记录找到对应的数据行，索引可以包含一个或多个列的值，如果包含多个列，那么列的顺序也很重要，因为MySQL只能高效地使用索引的最左前缀列。

1.1 B-Tree索引

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。B树是对二叉查找树的改进，它的设计思想是：将相关数据尽量集中在一起，以便一次读取多个数据，减少硬盘操作次数。B树为系统最优化大块数据的读和写操作。B树算法减少定位记录时所经历的中间过程，从而加快存取速度。
B树和B+树的讲解可以参考博客：浅析B树和B+树以及MySQL索引背后的数据结构及算法

实际上很多存储引擎使用的是B+Tree，即每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣。例如，MyISAM使用前缀压缩技术使得索引更小，但是InnoDB则按照原数据格式进行存储；MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用索引的行。

B-Tree索引是按照键值顺序存储的，每一个叶子页到根的距离相同。B-Tree索引能够加快访问数据的速度，因为存储引擎不需要进行全表扫描来获取需要的数据，B树中按照键值key检索数据，首先从根节点处二分查找，如果找到就返回对应的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针。（B树和B+树的结构不一样，B树的每个节点是key:value的形式，而B+树的非叶子节点只有索引的功能，跟记录有关的信息均存放在叶子节点中）。

书上说叶子节点比较特殊，它们的指针指向的是被索引的数据（实际上是B+树的结构）。

总结B树的特点：
（1） 大部分存储引擎以B+树的结构存储数据，B+树是一种平衡的查找树，每一个叶子到根部的距离都是相同的，并且同一层的节点都是按照键值的大小顺序存放，各个叶子节点由指针进行连接。
（2）B-tree索引能够加快数据的查询速度，通常索引的大小要远小于表中数据的大小，不需要全表扫描来获取数据，而是从索引的根节点开始搜索，索引的根节点存放了指向下一层节点的指针，直到叶子节点，叶子节点中指向的是被索引的数据。
（3）B-tree索引更适合进行范围查找（顺序存储）

B树索引的使用场景

B-Tree索引适用于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀的查找。

针对于如下的数据表：

CREATE TABLE People(
	last_name varchar(50) not null,
	first_name varchar(50) not null，
	dob date not null,
	gender enum('m','f') not null,
	key(last_name,first_name,dob)
	);

对于表中的每一行数据，索引中包含了last_name,first_name和dob列的值，下图显示了该索引是如何组织数据的存储的。

• 全值匹配。全值匹配指的是和索引中的所有列进行匹配，例如，前述示例中的索引可用于查找姓名为Cuba Allen、出生于1960-01-01的人。
• 匹配最左前缀。示例索引可用于查找所有姓为Allen的人，即只使用索引的第一列。
• 匹配列前缀。也可以只匹配某一列的值的开头部分，例如，示例索引可用于查找所有以J开头的姓的人，这里也只使用了索引的第一列。
• 匹配范围值。例如，示例索引可用于查找姓在Allen和Barrymore之间的人，这里也只使用了索引的第一列。
• 精确匹配某一列并范围匹配另外一列。示例索引也可用于查找所有姓为Allen，并且名字是字母K开头的人，即第一列last_name全匹配，第二列first_name范围匹配。
• 只访问索引的查询。B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问数据行。

因为索引树中的结点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作（按顺序查找）。一般来说，如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以，如果ORDER BY子句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求。

B-Tree索引的限制

• 如果不是按照索引的最左列开始查找，则无法使用索引。例如，示例索引中无法用于查找名字为Bill的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似的，也无法查找姓氏以某个字母结尾的人。
• 不能跳过索引中的列。例如，示例索引无法用于查找姓为Smith并且在某个特定日期出生的人。如果不指定first_name，则mysql只能使用索引的第一列。
• 如果索引中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如，示例索引中有如下查询语句WHERE last_name = ‘Smith’ AND first_name LIKE ‘J%’ AND dob = ‘1976-12-23’，此查询只能使用索引的前两列，因为这里LIKE是一个范围条件。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。

1.2 哈希索引

哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
在mysql中，只有Memory引擎显式支持哈希索引。这也是Memory引擎表的默认索引类型，Memory引擎同时也支持B-Tree索引。Memory引擎支持非唯一哈希索引，如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，也让哈希索引查找的速度非常快。

哈希索引的限制：

• 只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行（不能使用哈希索引来做覆盖索引，后面会讲）。但由于在内存中，对性能的影响并不明显。
• 不是按照索引值顺序存储的，所以无法用于排序。
• 不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值。
• 只支持等值比较查询，不支持任何范围查询。
• 访问哈希索引的数据非常快，除非有很多哈希冲突(不同索引列值却有相同的hash值)。当出现哈希冲突时，存储引擎必须遍历所有的行指针，逐行比较，直到找到对应的行。

InnoDB能够自动在内存中创建hash索引以加速读操作的自适应哈希索引，当InnoDB注意到某些值被使用的非常频繁时，会在内存中基于B-Tree索引之上在创建一个hash索引，从而拥有hash索引的优点，如快速的hash查找。这是一个完全自动的，内部的行为，用户无法控制或者配置，但可以关闭。

根据InnoDB中自适应哈希索引的思路，如果存储引擎不支持哈希索引，就可以像InnoDB一样创建哈希索引，思路就是在B-Tree基础上创建一个伪哈希索引，即将要索引的列删除索引，对其创建一个被索引哈希列，里面存放原索引列每一行数据的哈希值。可以参考P149实现自定义哈希索引的实例，需要注意的是要避免冲突，必须在WHERE条件中带入哈希值和对应的列值。

1.3 空间数据索引（R-Tree)

MyISAM表支持空间索引，可以用做地理数据存储。与B-Tree不同，这类索引无需前缀查询，会从所有维度来索引数据。空间索引会从所有维度来索引数据。查询时可以使用任意维度来组合查询。

1.4 全文索引

全文索引是一种特殊类型的索引，她查找的是文本中的关键词，而不是直接比较索引中的值。在相同的列上可以同时创建全文索引和B-Tree索引，全文索引适合于MATCH AGAINST操作，而不是普通的WHERE条件操作。

2 索引的优点

索引可以让服务器快速定位到表的指定位置，但这不是所以呢的唯一作用，根据索引的数据结构的不同，索引也有一些其他的附加作用。如B-Tree索引，按照顺序存储数据，可以用来做ORDER BY 和GROUP BY操作，因为索引是有序的，所以B-Tree也会将相关列值存储在一起。最后因为索引中存储了实际的列值，某些查询只使用索引就能完全全部查询，而不用去查找表（覆盖索引）。

索引的优点总结如下：

• 索引大大减少了存储引擎需要扫描的数据量（索引文件的大小远小于数据文件的大小）
• 索引可以帮助我们排序以避免排序和使用临时表（B树索引按键值的顺序进行排放，可以利用B树索引进行排序，这样就可以避免使用磁盘临时表进行排序）
• 索引可以把随机I/O变为顺序I/O（B树索引的键值是按照顺序排放的，数据行一般是随机分布的，充分发挥磁盘的IO性能）

需要思考一个问题：索引是不是越多越好？

• 索引会增加写操作的成本（在进行数据更新，插入合作和删除操作的时候必须要对索引和相关信息进行维护，索引越多，修改数据所需要的时间就越长；为了解决这个问题，innodb引入插入缓存，将多次插入合并为一次）
• 太多的索引会增加查询优化器的选择时间（MySQL的查询优化器会根据索引的统计信息和查询条件为查询选择合适的索引）

因此只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的开销的时候索引才是有效的。

3 高性能的索引策略

书上的很多例子使用了示例数据库Sakila，可以参考博客MySQL之示例数据库Sakila下载及安装进行安装

3.1 独立的列

如果查询中的列不是独立的，则MySQL不会使用索引，即索引列不能是表达式的一部分，也不能是函数的参数，应该始终将索引列单独的放在比较符号的一侧。

3.2 前缀索引和索引的选择性

当索引是很长的字符列时，会让索引变得大且慢。一个策略是模拟的哈希索引；另一个策略是前缀索引，只索引开始的部分字符，这样可以大大节约索引空间，从而提高索引的效率，但是这样做也会降低索引的选择性。

索引的选择性是不重复的索引值（基数）和数据表的总记录的比值，索引的选择性越高则查询效率也越高，选择性高的索引可以让MySQL在查找时过滤掉更多的行。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，选择前缀索引时保持较高的选择性(接近于索引完整列)，同时又不能太长。

实现前缀索引的方法：
CREATE INDEX index_name ON table(col_name(n));
在创建索引时指定列的宽度，宽度是有限制的，对于InnoDB来说列的宽度最大是767个字节，MyISAM索引的最大宽度是1000个字节，如果超过最大的宽度限制是无法建立前缀索引的。前缀索引是一种让索引更小、更快的有效办法，但是MYSQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

3.3 多列索引

在多个列上建立独立的的单列索引大部分情况下不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫索引合并的策略，在一定程度上可以使用表上的多个单列索引来定位指定的行。如下图所示，表film_actor在字段film_id和actor_id上各有一个单列索引，在老的版本中会使用全表扫描，但是在MySQL5.0和更新版本，查询能够使用这两个单列索引进行扫描，并将结果进行合并。

索引合并有时候是一种优化的结果，但实际上更多时候说明了表上的索引建的很糟糕，如果在EXPLAIN中看到有索引合并，应该检查一下查询和表的结构，看是不是已经是最优的。

explain为mysql提供语句的执行计划信息，explain的执行计划，只是作为语句执行过程的一个参考，实际执行的过程不一定和计划完全一致，但是执行计划中透露出的讯息却可以帮助选择更好的索引和写出更优化的查询语句。具体的查询优化以及sql执行过程将在下一篇博客中讲述。
如果想了解explain输出信息的详情，可以参考博客：Mysql优化之explain详解

3.4 选择合适的索引列顺序

正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列等等。因此可以按照升序或者降序进行扫描，以满足精确符合列顺序的OREDER BY、 GROUP BY 、DISTINCT等子句的查询需求。

选择索引列顺序的经验法则： 当不需要考虑排序和分组时，将选择性最高的列放到索引最前列；性能不只是依赖于所有索引列的选择性，也和查询条件的具体值有关，也就是和值的分布有关。需要根据不同的应用场景和SQL语句中WHERE子句的排序、分组和范围条件来选择合适的索列顺序

3.5 聚簇索引

聚簇索引不是一种单独的索引类型，而是一种数据存储的方式，InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

当表有聚簇索引时，它的数据行实际上存放在索引中的叶子页(leaf page)中，但节点也只包含了索引列。术语的“聚簇”表示数据行和相邻的键值紧凑地存放在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。如下图展示了聚簇索引中的记录是如何存放的，叶子页包含了行的全部数据，节点页只包含了索引列。

注意:InnoDB通过主键挤一挤数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引

聚簇索引的优点

• 可以把相关数据保存在一起。如实现电子邮箱时，根据用户ID来聚集数据，这样只需从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封电子邮件都可能导致一次磁盘IO。
• 数据访问更快。因为索引和数据都保存在同一个B-Tree中。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点

• 最大限度地提高了I/O密集型应用的性能，但如果数据全部存放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势。
• 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。如果不是按照主键顺序加载数据，加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂(page split)”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这会导致表占用更多的磁盘空间。
• 可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
• 二级索引(非聚簇索引)可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
• 二级索引访问需要两次索引查找，而不是一次。因为二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。

InnoDB和MyISAM的数据分布

聚簇索引和费聚簇索引的数据分布有区别，以及对应的主键索引和二级索引得数据分布也有区别：
（1）MyISAM存储引擎的主键索引是按照主键索引顺序排列，索引中的叶子节点保存的是指向行的物理位置的指针；二级索引和主键索引在结构上没有区别，主键索引就是一个为PRIMARY的唯一非空索引。
（2）InnoDB支持聚簇索引，每个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有剩余列，如果主键是一个前缀索引，InnoDB也会包含完整的主键列和剩下的其他列；InnoDB的二级索引的叶子节点是主键值，这样是为了减少当出现行移动或者数据分裂时二级索引的维护工作。

在InnoDB表中按照主键顺序插入

如果没有数据需要聚集，建议定义一个代理键作为主键，并且主键的数据应该和应用无关。最简单是使用AUTO_INCREMENT自增列，这样可以保证数据行是按顺序写入的，对于根据主键做关联操作的性能更好。

最好避免随机的(不连续且值的分布范围非常大)聚簇索引，特别是对于I/O密集型的应用，比如使用UUID作为聚簇索引可能会带来糟糕的性能，它使得聚簇索引的插入完全随机，使得插入行的时间更长，而且索引占用的空间更大（主键的字段更长，页分裂和碎片）

对于聚簇索引的存储引擎，数据的物理位置与索引位置一致，即：只要索引是相邻的，数据也一定是相邻在存放在磁盘空间上；如果主键不是自增id，会不断的调整数据的物理地址、分页；如果是自增的，只需要一页一页的写，索引结构相对紧凑，磁盘碎片也更少，效率更高。下面两张图展示了向聚簇索引中插入顺序的索引值和无序值的过程：


向聚簇索引中插入无序的值的缺点：

• 写入的目标页可能已经刷新到磁盘并从缓存中移除，或是还没有被加载到缓存中，InnoDB在插入之前需先从磁盘读取目标页到内存中，这将导致大量的随机IO。
• 因为写入是乱序的，需要频繁地做页分裂操作，以便为新行分配空间。因为页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
• 由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

总结：由上面的分析可以知道，使用InnoDB时应该尽可能按照主键顺序插入数据，并且尽量使用单调增加的聚簇键的值来插入。但是对于并发的工作场景，顺序的主键可能造成明显的锁争用，导致性能变差。

3.6 覆盖索引

如果一个索引的叶子节点中包含了所需要查询的字段的值，就是覆盖索引。

覆盖索引的优点：

• 索引条目通常小于数据行大小，如果只需要读取索引会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花在数据拷贝上。覆盖索引对I/O密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中(尤其是MyISAM能压缩索引)索引是按照列值顺序
• 索引是按照列值顺序存储的(至少在单个页内是如此 )，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的IO要少得多。
• 一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销。
• 覆盖索引对使用了聚簇索引的InnoDB的表非常有用。InnoDB的二级索引在叶子节点保存了行的主键值，所以如果二级节点能够覆盖查询，则可以避免对主键索引的二次查询。

无法使用覆盖索引的情况：

• 存储引擎不支持覆盖索引：覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引都不能存储索引列的值，因此MySQL只能使用B-Tree索引做覆盖索引。
• 查询中使用了太多的列
• 使用了双%号的like查询

3.7 使用索引扫描来做排序

MySQL有两种方式生成有序的结果：通过排序操作或者按照索引顺序扫描。如果EXPLAIN出来的type列的值为“index”，则说明MySQL使用的了索引扫描来做排序，如果type是Using filesort说明MySQL使用了文件排序。

使用索引扫描来做排序需要满足一定的条件：索引的顺序和Order By子句的顺序完全一致，索引中所有的列的方向（升序、降序）和Order By子句完全一致，如果查询需要关联多张表则只有ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。

3.8 压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引大小，从而让更多索引可以放入内存中，在某些情况下能极大地提高性能。默认只压缩字符串，通过设置也能压缩整数。压缩每个索引块的方法：先完全保存索引块的第一个值，然后将其他值和第一个值比较得到相同的前缀字节数和剩余的不同后缀部分，再把这部分存储起来。MyISAM对指针也采用类似的压缩方式。

压缩块使用更少的空间，代价是某些操作可能更慢。因为每个值都依赖前面的值，无法使用二分查找只能从头开始扫描，而对倒序的扫描性能更差。

3.9 冗余和重复索引

重复索引：在相同列上按照相同顺序创建的相同类型的索引。应该避免这种操作，常见错误做法是对一个主键添加唯一限制和查询索引，这属于三个重复的索引。(如果索引的类型不同，并不算重复索引)

**冗余索引：**在相同列上创建多个索引。MySQL需要单独维护重复的索引，并且优化器在查询时也需要逐个考虑，可能会影响性能。

在InnoDB存储引擎中由于二级索引包含了主键值，因此(A)相当于(A,ID)，对WHERE A=5 ORDER BY ID这样的查询很有用。但如果(A)扩展为(A,B)相当于(A,B,ID)，前面的查询就无法使用该索引排序，而只能用文件排序。

3.10 未使用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引，应该使用一些方法找到不用的索引并且删除。

3.11 索引和锁

InnoDB存储引擎使用的是行级锁，只有在修改行的时候给需要的行加锁，而索引可以减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层可以过滤掉不需要的行时才会有效，如果存储引擎层不能过滤掉不需要的行就需要锁定所有的行然后在内存中通过条件来进行过滤。

利用索引优化锁索引：可以减少锁定的行数索引可以加快处理速度，同时加快了锁的释放。

InnoDB在二级索引上使用共享(读)锁，但访问主键索引需要排他(写)锁，这消除了使用覆盖索引的可能性，并且使得SELECT FOR UPDATE比LOCK IN SHARE MODE或非锁定查询要慢得多。