谈谈MySQL索引

什么是索引

存储引擎中一种用于快速找到记录的数据结构。索引对于良好性能非常关键，尤其是当表中的数据量越来越大的时候，索引对性能的影响愈发重要。

索引类型

B-Tree索引

毋庸置疑，B-Tree索引使用的是B-Tree数据结构来存储数据。索引类型在创建表的过程中是可以指定的。

B-Tree意味着所有的值都是按照顺序存储的，并且每一个叶子页到根的距离相同。

所以B-Tree索引能加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引根节点开始进行搜索。

B+Tree

MySQL中最常用的索引数据结构是B+树，存在以下特点：

1. 所有数据记录节点按照键值大小存放在同层叶子节点上，非叶子结点只存储key的信息，这样可以大大减少每个节点存储key的数量，降低B+树的高度；
2. 叶子节点的关键字从小到大排序，左边结尾数据会保存右边节点开始数据的指针；
3. 层级更少意味着查询数据更快；
4. 查询速度稳定，所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同；
5. 天然具备排序功能，所有叶子节点就是一个有序链表，所以在查询大小取件的数据更方便，数据紧密性好，缓存的命中率也比B树高；
6. 全节点遍历更快，有利于做数据库全表扫描；

主键目录

• MySQL是以数据页为最小单位，数据页中的数据是连续存储的；
• 数据页的数据是按照主键排序的（若无主键由MySQL中的ROW_ID来排序）；
• 数据页之间使用双向链表关联；
• 数据之间使用单链表进行关联；

索引页

为了避免单个主键目录过大（例如1000万条记录，5000万条记录）。使用前面说的二分法查找效率就十分低下了。所以为了解决这个问题MySQL又提出了一种新的数据结构，索引页。

索引页的记录是每页数据页的页号和该数据页中最小的主键记录，也就是说最小主键和数据页号不是单纯的维护在主键目录中，而是演变成了索引页，索引页和数据页类似。一张不够就分裂到下一张；

因此：此时MySQL应该需要维护索引页的，MySQL也是这样设计的。也就是说MySQL同时也设计出用于维护索引页的数据结构。也称为索引页。类似下面的结构：

也就是说维护索引的索引页是真正存储记录和数据页的索引页的上一层，如果想要查找就从最上层的索引页开始查找。通过二分法，很快就能定位到该条记录在索引页的具体位置上。

索引页分层

同样的，索引页一大也会出现类似的问题，那怎么办呢？好说。继续分裂

综上所述，MySQL的查找流程如下，假设我们要找的记录是37：

• 先从最顶层的索引页开始查找，根据id = 37定位到索引页16；
• 在索引页16中继续搜索，此时定位到id = 37在索引页3中；
• 最终定位到数据在数据页8中，遍历数据页8中的数据链表，发现元素37；
• 查找完毕

最后需要注意的是：索引页 + 数据页组成的B+树称之为聚簇索引。

聚簇索引是MySQL基于主键索引结构创建的

非主键索引

非主键索引和主键索引的引用原理一样，都是去维护一颗B+树，建立多少个索引，就会帮忙维护多少个B+树。因为索引会占用磁盘空间，不能盲目建索引；

它是怎么工作的呢？

如果对name + age建立索引，此时MySQL会根据name + age维护一个单独的B+树结构。数据依然存放在数据页中。类似于下图：

插入数据时，MySQL会根据name进行排序，如果name一样。就根据联合索引中的age字段排序，如果还一样就根据主键字段排序。

什么是回表？

根据非主键索引查询的结果并没有查找的字段，此时就需要再次根据主键从聚簇索引的根节点开始查找，这样再次查找到的记录才是完整的。

针对上面的结构，假设存在如下查询：

SELECT name FROM student WHERE name = 'xx';

由于name字段是索引，所以查询是非常完美的；

假设是这样子：

SELECT * FROM student WHERE name = 'xy';

虽然通过name很快定位到了索引，但由于name + age并不是聚簇索引，所以B+树的数据页存放的仅仅是和自己关联的索引和主键字段，并不存在其他字段。这个时候MySQL会根据定位记录的id再次进行聚簇索引查找，这个过程就叫做回表。

索引的优缺点

说了这么多，索引总结下来有三个优点：

• 大大减少服务器需要扫描的数据量；
• 帮助服务器避免排序和临时表；
• 将随机I/O变为顺序I/O（因为数据页本身就是有序的）；

当然再好的工具也并不一定是解决问题的通用方案，索引也有缺点：

• 对于非常小的数据表，全表扫描更加高效，不需要使用索引，对于中大型的表来说索引比较高效；
• 对于特大型的表，应当直接区分查询需要的数据组，例如分区技术；
• 如果表的数量特别大，可以建立一个元数据信息表，用来查询需要用到的特性；

高性能索引策略

独立列

索引列不能是表达式的一部分，也不能是函数参数，比如下面这两种写法会令索引失效；

因此我们必须养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧；

SELECT actor_id FROM actor WHERE actor_id + 1 = 5;
SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

前缀索引

索引很长的字符列会使得索引变得大且慢，这时候可以索引开始的部分字符，这样可以大大节约索引空间，提高索引效率。

诀窍在于：选择足够长的前缀以保证较高的选择性（也就是可以过滤掉绝大多数不相关的数据），同时又不能太长（节约空间）。因此前缀应该足够长，以使得前缀索引的选择性接近与索引整个列；

计算完整列选择性

用查询条件的索引字段除以总记录数，通常如果前缀选择性能够接近0.031基本上就可以用了。例如：

SELECT COUNT(DISTINCT LEFT(city, 3)) / COUNT(*) AS sel3,
	   COUNT(DISTINCT LEFT(city, 4)) / COUNT(*) AS sel4,
	   COUNT(DISTINCT LEFT(city, 5)) / COUNT(*) AS sel5,
	   COUNT(DISTINCT LEFT(city, 6)) / COUNT(*) AS sel6,
	   COUNT(DISTINCT LEFT(city, 7)) / COUNT(*) AS sel7
FROM city_demo;

多列索引（这个看不懂，先放着）

在多个列上建立独立索引大部分情况下并不能提高MySQL的查询性能，好在MySQL提供了一种“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。

合适的索引列顺序

依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这样索引确实可以够快过滤出需要的行。

还有一个方法，在你不确定怎样排列查询的效果最好时，可以跑一下查询确定在这个表中值的分布情况，并确定哪个列的选择性更高。

SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;

如法炮制，我们可以计算一下索引列选择性：

SELECT COUNT(DISTINCT staff_id) / COUNT(*) AS staff_id_selectivity,
	   COUNT(DISTINCT customer_Id) / COUNT(*) AS customer_id_selectivity,
	   COUNT(*)
	   FROM payment\G;

最后数字大的那一个就是索引列的第一项；尽管关于选择性和基数的经验法则值得去研究和分析，但一定要注意WHERE字句中的排序、分组和范围条件等其他因素，这些因素可能对查询的性能会造成非常大的影响。

聚簇索引

聚簇索引：找到了索引就找到了需要的数据，因此主键就是一种聚簇索引；

非聚簇索引：结合上面的意思，找到了索引但没找到数据，需要根据索引的主键再次进行回表查询；

聚簇索引一般是主键，前面已经介绍过了相关概念，就不再啰嗦。它也有优缺点：

优点

• 相关数据聚合在一起，只需要从磁盘读取少数数据就可以拿到全部信息，减少磁盘I/O；
• 数据访问快，在聚簇索引中获取数据通常比非聚簇索引查找要快；
• 索引覆盖扫描的查询可以直接使用页节点中的主键值；

缺点

• 数据全在内存中，聚簇索引的访问顺序性就没那么重要了；
• 插入速度严重依赖插入顺序，按照主键顺序插入是速度最快的，否则会特别慢；
• 更新聚簇索引代价很高，会强制InnoDB将每个被更新的行移动到新的位置；
• 当主键被更新导致需要移动行时会触发“页分裂”操作，这样会使得表占用更多的磁盘空间；
• 导致全表扫描变慢，尤其在数据比较稀疏或者由于页分裂导致数据存储不连续的时候；
• 二级索引可能比想象的要大，因为二级索引的叶子节点包含了引用行的主键列；
• 二级索引访问需要两次索引查找；

注意的点

使用InnoDB表中按主键顺序插入行，最简单的办法是使用AUTO_INCREMENT自增列。这样既可以保证数据行是按照顺序写入，对于根据主键做关联操作的性能也会更好。

最好避免随机的聚簇索引（比如使用UUID来做聚簇索引会很糟糕），它会使得索引插入变得完全随机，这是最坏的情况。

覆盖索引

即从非主键索引中就能查询到的记录，而不需要查询主键索引（回表操作）中的记录。避免回表的产生减少了对树的搜索次数，从而提升性能。

由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。二级索引（非聚簇索引）的叶子节点如果保存了行的主键值，所以如果二级主键能够覆盖查询就可以避免回表操作的产生。

如何查看使用了覆盖查询？

使用explain关键字解释查询，如果在Extra行显示”Using index”则说明使用了覆盖索引。

例子

假设inventory中存在多列索引(store_id, film_id)，并且MySQL只需要这两列数据，那么此时就是使用了覆盖索引；

EXPLAIN SELECT store_id, film_id FROM inventory\G;

假设是下面这种情况，索引就无法实现查询覆盖：

EXPLAIN SELECT * FROM products WHERE actor = 'SEAM CARREY'
	    AND title like '%APOLLO%'\G

原因有二：

• 没有任何索引能够覆盖查询，因为查询从表中选择了所有列，且没有任何索引覆盖了所有列；
• MySQL不能在索引中执行LIKE操作；

如何优化呢？

将索引列扩展覆盖至三个数据列（artist, title, prod_id），然后重写查询：

EXPLAIN SELECT *
	    FROM products
	    	JOIN(
            	SELECT prod_id
                FROM products
                WHERE actor = 'SEAM CARREY'
                AND title LIKE '%APOLLO%'
            ) AS t1 ON (t1.prod_id = products.prod_id)\G

这种方式成为延迟关联，由于延迟了对列的访问，因此在第一阶段可以使用覆盖索引，然后再根据prod_id的值在外层查询匹配获得需要的所有列值。

索引扫描排序(这个不是很理解)

MySQL可以通过两种方式做排序：

• 排序操作
• 索引顺序扫描（EXPLAIN后的结果为“index”则是索引扫描）

索引本身是有序的，所以索引扫描应该很快，但如果索引无法覆盖查询所需的全部列，就不得不每扫描一跳记录都回表查询一次对应的行。

只有当索引列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样，MySQL才能够使用索引来对结果做排序

冗余和重复索引

在 MySQL 中允许在相同的列上创建多个索引。

• 重复索引是指在相同列上按照相同的顺序创建的相同类型的索引。

• 冗余索引和重复索引有些不同，例如创建了索引（A,B），在创建索引（A）就是冗余索引。

下面的例子是重复索引：

create table test(
    id int not null primary key,
    A int not null,
    B int not null,
    UNIQUE(id),
    INDEX(id)
 ) ENGINE=InnoDB;

TIP：

1. MySQL需要单独维护重复索引和冗余索引
2. 优化器在优化查询时，也需要对每个索引进行过滤，也会影响性能；
3. 表中索引多，会影响对数据进行CRUD的速度；

未使用的索引

未使用的索引应当删除，最简单的办法是打开userstates服务器变量（默认关闭），然后让服务器正常运行一段时间，再通过INFORMATION_SCHEMA.INDEX_STATISTICS就可以查到每个索引的使用频率；

索引和锁

索引可以让查询锁定更少的行，如果查询从不访问那些不需要的行，就会锁定更少的行。虽然InnoDB的行锁效率很高，内存使用也少，但是锁定行的时候也会带来额外的开销，其次锁定超过需要的行会增加锁争用并减少并发性。

MySQL索引失效

为什么会出现索引失效

索引可以加快查找速度是因为，在每层兄弟节点之间索引是有序的，因此可以通过二分查找快速定位到相应的位置。假如有一些操作破坏了索引排列顺序的有序性或者不能利用索引的有序性，那么这个索引自然而然就失效了；

哪些情况会出现索引失效

• 不符合最左匹配前缀原则导致索引失效;
• 对索引字段做函数操作；优化器会放弃树的搜索功能；
• 存在NULL值条件；
• 使用模糊搜索时，尽量采用后置通配符，因为走索引的过程中，其会从前去匹配索引列；

总结

索引失效是优化器不能很好的利用索引的有序性，因此在使用索引的过程中要尽量满足最左前缀匹配原则，范围查询放在最后，不使用%like、%like%等模糊查询；