MySQL索引学习笔记

慢查询分析

对于MySQL，最简单的衡量查询开销的三个指标如下：

响应时间：服务时间和排队时间之和。服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间——可能是等I/O操作完成，也可能是等待行锁。
扫描的行数：一条查询，如果性能很差，最常见的原因是访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。有时候也可能是访问了太多的列；（每次看到SELECT*的时候都需要用怀疑的眼光审视，是不是真的需要返回全部的列，很可能不是必需的。取出全部列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O、内存和CPU的消耗）
返回的行数：会给服务器带来额外的I/O、内存和CPU的消耗（使用limit限制返回行数）

1.1 分析示例

我们看一下示例数据库Sakila中的一个查询案例：

select * from film_actor where film_id = 1 -- film_id列有索引

这个查询将返回10行数据，从EXPLAIN的结果可以看到，MySQL在索引idx_fk_film_id上使用了ref访问类型来执行查询：

EXPLAIN的结果还显示MySQL预估需要访问10行数据。换句话说，查询优化器认为这种访问类型可以高效地完成查询。

如果没有合适的索引会怎样呢？MySQL就不得不使用一种糟糕的访问类型，下面来看看如果删除对应的索引再来运行这个查询会发生什么情况：

访问类型变成了一个全表扫描(ALL)，现在MySQL预估需要扫描5462条记录来完成这个查询。这里的“Using where”表示MySQL将通过WHERE条件来筛选存储引擎返回的记录。

一般地，MySQL能够使用如下三种方式应用WHERE条件，从好到坏依次为：

在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须再回表查询记录。

举例说明一下：索引列a，b，c，查询条件时 a = xx and c = xx
从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using where）。这在MySQL服务器层完成，MySQL需要先从数据表中读出记录然后过滤。

三星索引

“三星系统”(three-star system)评价体系，用以判断一个索引是不是适合某个查询语句：

索引将相关的记录放到一起则获得“一星”；
如果索引中的数据顺序和查找中的排列顺序一致则获得“二星”；
如果索引中的列包含了查询中需要的全部列则获得“三星”。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。InnoDB的聚簇索引实际上保存了B-tree索引和数据行（聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针，以及所有的剩余列）。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页(leaf page)中。一个表只能有一个聚簇索引，叶子页包含了一条记录的全部数据。

InnoDB根据主键聚簇数据。如果你没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。聚集的数据有一些重要的优点：

可以把相互关联的数据保存在一起，数据访问更快。聚簇索引将索引和数据保存在同一个B-tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。

同时，聚簇索引也有一些缺点：

插入速度严重依赖于插入顺序。按照主键的顺序插入行是将数据加载到InnoDB表中最快的方式。但如果不是按照主键的顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。
更新聚簇索引列的代价很高，因为它会强制InnoDB将每个被更新的行移动到新的位置。
基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂(page split)的问题。当行的主键值要求必须将这一行插入某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为覆盖索引。覆盖索引是非常有用的工具，能够极大地提高性能。试想一下，如果查询只需要扫描索引而无须回表，会带来多少好处：

索引条目通常远小于数据行大小，所以如果只需要读取索引，那么MySQL就会极大地减少数据访问量。
因为索引是按照列值的顺序存储的（至少在单页内如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。
由于InnoDB的聚簇索引的特点，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了记录的主键值，所以如果二级索引能够覆盖查询，则可以避免对主键索引的二次查询。（二级索引访问需要两次索引查找，而不是一次，通过二级索引查找行，存储引擎需要找到二级索引的叶子节点，以获得对应的主键值，然后根据这个值去聚簇索引中查找对应的行。）

索引列顺序（怎么指定索引列顺序）

不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时索引的作用只是优化查询语句中的WHERE条件。在这种情况下，按这个原则设计的索引确实能够最快地过滤出需要的行。以下面的查询为例：

select * from payment where staff_id = 123 and customer_id = 456

是应该创建一个(staff_id、customer_id)索引还是应该颠倒一下顺序？这时，可以通过运行某些查询来确定在这个表中值的分布情况，并确定哪列的选择性更高。先用下面的查询预测一下，看看各个WHERE条件的分支对应的数据基数有多大：

根据前面的经验法则，应该将索引列customer_id放到前面，因为对应条件值的customer_id数量更小。

5.1 索引最左前缀法则‌

指在MySQL中使用索引时，查询条件需要从索引的最左边开始，并且不能跳过索引中的列。如果查询条件跳过了索引中的某列，那么索引将失效，后续的列也不会被使用。‌

select * from payment where staff_id = 123 -- 索引是customer_id,staff_id，因为漏掉了customer_id，所以走不上索引

5.2 排序是否命中索引

无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，不过MySQL将这个过程统一称为文件排序(filesort)，即使完全是在内存中排序不需要任何磁盘文件时也是如此。

只有当索引的顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能使用索引来对结果做排序。如果查询需要联接多张表，则只有当ORDER BY子句引用的字段全部在第一个表中时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求，否则，MySQL需要执行排序操作，而无法利用索引排序。

有一种特殊情况，如果前导列为常量的时候，ORDER BY子句中的列也可以不满足索引的最左前缀的要求。如果在WHERE子句或者JOIN子句中将这些列指定为了常量，就可以“填补”索引字段的间隙了。

例如，Sakila示例数据库的表rental在列(rental_date，inventory_id，customer_id)上建有名称为rental_date的索引：

不需要文件排序

MySQL可以使用rental_date索引为下面的查询做排序，从EXPLAIN中可以看到没有出现文件排序(filesort)操作：

select * from rental where rental_date = '2005-05-25' order by inventory_id, customer_id -- rental_date是常量，后面的排序列则认为符合最左前缀

select * from rental where rental_date = '2005-05-25' order by inventory_id, customer_id,id -- rental_date是常量，后面的排序列则认为符合最左前缀，虽然索引里面没有显示指定id列，id是隐性的包含在索引中的，所以也无需文件排序

即使ORDER BY子句不满足索引的最左前缀的要求，也可以用于查询排序，这正是因为索引的第一列被指定为了一个常数。

需要文件排序

下面这个查询也没有问题，因为ORDER BY使用的两列就是索引的最左前缀(索引顺序：rental_date，inventory_id，customer_id)：

select * from rental where rental_date > '2005-05-25' order by rental_date, inventory_id -- 符合最左前缀

下面是一些不能使用索引做排序的查询：

下面这个查询使用了两种不同的排序方向，但是索引中的列都是按正序排序的(索引顺序：rental_date，inventory_id，customer_id)：

select * from rental where rental_date = '2005-05-25' order by inventory_id asc, customer_id desc -- 满足最左前缀，但是排序方向不一样

在下面这个查询的ORDER BY子句中，引用了一个不在索引中的列(索引顺序：rental_date，inventory_id，customer_id)：

select * from rental where rental_date = '2005-05-25' order by inventory_id ,staff_id -- staff_id不在索引中

下面这个查询的WHERE和ORDER BY中的列无法组合成索引的最左前缀(索引顺序：rental_date，inventory_id，customer_id)：

select * from rental where rental_date = '2005-05-25' order by customer_id -- 不满足最左前缀，漏掉了inventory_id

下面这个查询在索引列的第一列上是范围条件，所以MySQL无法使用索引的其余列(索引顺序：rental_date，inventory_id，customer_id)：

select * from rental where rental_date > '2005-05-25' order by inventory_id -- 第一列如果是范围查询，则认为不符合最左前缀

这个查询在inventory_id列上有多个等于条件。对于排序来说，这也是一种范围查询(索引顺序：rental_date，inventory_id，customer_id)：

select * from rental where rental_date = '2005-05-25' and inventory_id in (1,2) order by customer_id -- inventory_id 条件是范围查询，则认为不符合最左前缀

想了解更多干货，可通过下方扫码关注

可扫码添加上智启元官方客服微信👇

Leora说道：

2025年3月8日下午10:52

I love the way you explain things; it’s obvious and concise.

See details说道：

2025年3月4日上午1:33

This post is accessible and understandable, well done.

Contact us说道：

2025年2月27日下午8:24

Hello! I’ve been reading your web site for some time
now and finally got the bravery to go ahead and give you a shout out from Houston Texas!
Just wanted to say keep up the good work!