MySQL性能优化篇之索引优化

当谈论SQL优化时，通常都是指对索引的优化。其实不然，SQL优化的目的是让MySQL能更快的响应接收到的请求。所以SQL优化应该是MySQL处理请求的整个流程，而不是只关注对索引的优化。至于MySQL处理请求的整个流程涉及到哪些部分，这里就不作详细解释，只关注索引优化。

索引是什么？

MySQL中的索引是一种有序的数据结构，最常见的是用B+树实现，可以提高检索数据的效率。

为什么索引可以降低MySQL检索数据的时间？

B+树是一种平衡查找树，时间复杂度在O(log n)，如果没有索引在检索数据时最差的情况的时间复杂度会是O(n)，这意味着即使数据量巨大，检索速度也能保持在一个可接受的范围内。
B+树结构的索引的特点如下

• 叶子节点包含实际数据行的地址或数据行的完整信息
• 非叶子节点只包含索引字段的数据和子节点的指针
• 叶子节点之间相互链接，方便范围查找和排序

MySQL支持哪些索引类型？

MySQL支持的索引类型有很多种，常见的有以下几种

• 单列索引
• 多列索引
• 前缀索引
• 全文索引
• 覆盖索引

单列索引

这类索引比较简单，只用一个字段建立索引。如数据表中常用id字段作为主键，主键就是用id字段建立的一个单列索引。
常用于比较简单的查询条件或者查询列的场景。比如只需要查询表中的name字段或者使用distinct去重name字段，只建立一个name字段的索引就可以提高这类查询的执行效率。

多列索引

由多个字段组合建立的一个索引。
常用于比较复杂的查询，如查询条件有多个字段，或者除了查询条件还有group by分组这种复杂查询语句。

前缀索引

主要用于字符串类型的字段，如varchar、text这类。由于字段的内容通常比较多，如果将整个字段的内容都作为索引数据结构中的一部分，则会导致索引本身也变的庞大，B+树的高度也会增加，会降低查询效率。这种字段通常通过前N个字符也能将不符合的大部分数据过滤掉，这就是前缀索引的原理。

那么如何确定这个N的值呢？N的值是通过一个叫做索引选择性决定的。索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高。通常来说只要索引选择性达到0.031就可用了，这样可以兼顾性能和索引大小。

计算索引选择性的公式
SELECT COUNT(DISTINCT city)/COUNT(*) FROM sakila.city_demo;
确定前缀索引的N的值的计算公式，选择最接近0.031的即可

SELECT
COUNT(DISTINCT LEFT(city, 3))/COUNT() AS sel3,
COUNT(DISTINCT LEFT(city, 4))/COUNT() AS sel4
FROM sakila.city_demo;

全文索引

与前缀索引相比，全文索引就是将整个字段的数据作为索引的内容。不过全文索引只支持关键字匹配，不支持精确匹配，类似于搜索引擎的搜索结果。

覆盖索引

索引包含了查询返回的所有字段，这类索引就称为覆盖索引。

在myIsam存储引擎中，数据文件和索引文件是分开的。通常索引文件会缓存到内存中，读取完整数据行时需要经历系统调用到磁盘获取。磁盘IO的读写速度是远小于内存的，使用覆盖索引可以减少MySQL的磁盘IO次数，因此可以提高查询效率。

在InnoDB存储引擎中，它的索引分为两类：聚簇索引和二级索引(非聚簇索引)。聚簇索引的结构是主键和完整数据行在一个索引文件中，二级索引的结构只包含索引字段的数据和主键的地址指针。InnoDB中只有主键是聚簇索引，其他索引都是二级索引。由于聚簇索引的结构特点，当一个查询用到了二级索引，但是它需要返回行的全部字段或者索引之外的字段，此时MySQL还需要从聚簇索引中才能取到完整数据行，这个过程称为回表。

在实际应用中应该如何选择建立高性能的索引？

这里主要讨论的是多列索引的建立。

索引列的顺序

在多列索引中，使用索引的规则是最左匹配，所以索引列的顺序会影响索引能否生效。一般来说会根据列的选择性进行排列，选择性越高的放在前面。如建立一个name、sex、age和status的多列索引，按照前面的原则建立的多列索引应该是idx(name,age,sex,status)。

但是这个索引真的合适吗？如果从真实使用场景来分析，可以发现这样的索引适用的场景很少。只按status查询或者使用status和sex查询，这类查询就无法用到idx索引了。从实际考虑，我们可以将上面的原则反过来应用，将选择性低的放在前面，这样索引就能适应更多的查询场景了。

新的索引顺序为idx(status,sex,age,name)，当按age查询时，我们可以在查询条件中额外补充status in (0,1,2…) and sex in (‘male’, ‘female’) and age=19，这样仍然可以使用到索引。

冗余索引

如果创建了索引（A, B），再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引。因此索引（A, B）也可以当作索引（A）来使用（这种冗余只是对B-Tree索引来说的）。但是如果再创建索引（B, A），则不是冗余索引，索引（B）也不是，因为B不是索引（A, B）的最左前缀列。另外，其他不同类型的索引（例如哈希索引或者全文索引）也不会是B-Tree索引的冗余索引，而无论覆盖的索引列是什么。

大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。

例如，如果在整数列上有一个索引，现在需要额外增加一个很长的VARCHAR列来扩展该索引，那性能可能会急剧下降。

索引的字段比较大会使B+树的高度增加，从而影响查询效率。

B+的每个节点的空间是固定的，由操作系统决定。一个节点会保存多个索引字段的数据，当索引字段的内容越大，一个节点容纳的索引字段就越少，树的高度自然而然就会更高。

假设表有1 000 000行，对每个state_id值大概有20 000条记录。在state_id列有一个索引对下面的查询有用，假设查询名为Q1：
mysql> SELECT count(*) FROM userinfo WHERE state_id=5;
一个简单的测试表明该查询的执行速度大概是每秒115次（QPS）。

还有一个相关查询需要检索几个列的值，而不是只统计行数，假设名为Q2：
mysql> SELECT state_id, city, address FROM userinfo WHERE state_id=5;
对于这个查询，测试结果QPS小于10。

提升该查询性能的最简单办法就是扩展索引为（state_id, city, address），让索引能覆盖查询：
mysql> ALTER TABLE userinfo DROP KEY state_id,ADD KEY state_id_2 (state_id, city, address);
索引扩展后，Q2运行得更快了，但是Q1却变慢了。

如果我们想让两个查询都变得更快，就需要两个索引，尽管这样一来原来的单列索引是冗余的了。

最终的索引应该是idx01(state_id)和idx02(state_id, city, address)
但是冗余索引会导致插入、更新和删除操作变慢，所以冗余索引不是越多越好。