MySQL - 创建高性能索引
5.1 索引基础
要理解MySQL 中索引是如何工作的，最简单的办法就是去看看一本书的"索引"部分：如果想要在一本书中找到某个特定主题，一般会先看书的"索引"，找到对应的页码
在MySQL中，存储引擎用类似的方法使用索引，其先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。假如要运行下面的查询： 如果在actor_id 列上建有索引，则MySQL 将使用该索引找到actor_id 为5的行，也就是说，MySQL 先在索引上按值进行查找，然后返回所有包含该值的数据行。
索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL 只能高效地使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个只包含一列的索引是大不相同的
5.1.1 索引的类型
索引有很多种类型，可以为不同的场景提供更好的性能。在MySQL中，索引是在存储引擎层而不是服务器层实现的。所以，并没有统一的索引标准：不同存储引擎的索引的工作方式也不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同
MySQL 服务器逻辑架构图：
B-Tree 索引
当人们谈论索引的时候，如果没有特别指明类型，那多半说的是B-Tree 索引，它使用B-Tree 数据结构来存储数据。
我们使用术语"B-Tree"，是因为MySQL 在 CREATE TABLE 和其他语句中也使用了该关键字。不过，底层的存储引擎也可能使用不同的存储结构，例如，NDB 集群存储引擎内部实际上使用了T-Tree 结构存储这种索引，即使其名字是BTREE；InnoDB 则使用的是B+TREE。
存储引擎以不同的方式使用B-TREE 索引，性能也各有不同，各有优劣。例如，MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行
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)
);
对于表中的每一行数据，索引中包含了las_name、first_name 和 dob 列的值，下图显示了该索引是如何组织数据的存储的
可以使用B-Tree索引的查询类型。B-Tree 索引适用于全健值、键值范围或键前缀查找。其中键前缀查找只使用于根据最左前缀的查找。前面所属的索引对如下类型的查询有效
全值匹配
全值匹配指的是和索引中的所有列进行匹配，例如前面提到的索引可用于查找姓名为Cuba Allen、出生于1960-01-01 的人。
匹配最左前缀
前面提到的索引可用于查找所有姓为Allen的人，即只使用索引的第一列
匹配列前缀
也可以只匹配某一列的值的开头部分。例如前面提到的索引可用于查找所有以J开头的姓的人。这里也只使用可索引的第一列。
匹配范围值
例如前面提到的索引可用于查找姓在Allen 和 Barrymore 之间的人。这里也使用了索引的第一列。
精确匹配某一列并范围匹配另外一列
前面提到的索引也可用于查找所有姓为Allen，并且名字是字母K开头(比如Kim、Karl等)的人。即第一列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是一个范围条件(但是服务器可以把其余列用于其他目的)。如果范围查询列值的数据有限，那么可以通过多个等于条件来代替范围条件。
到这里读者应该可以明白，前面提到的索引列的顺序是多么的重要：这些限制都和索引列的顺序有关。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。
也有些限制是并不是B-Tree 本身导致的，而是MySQL 优化器和存储引擎使用索引的方式导致的，这部分限制在未来的版本中可能就不再是限制了。
5.2 索引的优点
索引可以让服务器快速定位到表的指定位置 。但是这并不是索引的唯一作用，到目前为止可以看到，根据创建索引的数据结构不同，索引也有一些其他的附加作用
最常见的B-Tree 索引，按照顺序存储数据，所以 MySQL 可以用来做ORDDER BY 和 GROUP BY 操作。因为数据是有序的，所以B-Tree 也就会将相关的列值都存储在一起。最后因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询，据此特性，总结下来索引有如下三个优点：
- 1.索引大大减少了服务器需要扫描的数据量
- 2.索引可以帮助服务器避免排序和临时表
- 3.索引可以将随机I/O 变为 顺序I/O
数据库索引设计与优化一书介绍了如何评价一个索引是否适合某个查询的"三星系统"：
1.索引将相关记录放到一起则获得一星
2.如果索引中的数据顺序和查找中的排列舒徐一致则获得二星
3.如果索引中的列包含了查询中需要的全部列则获得三星
5.3 高性能索引的策略
5.3.1 独立的列
我们通常会看到一些查询不当地使用索引，或者使得MySQL 无法使用已有的索引。如果查询中的列不是独立的，则MySQL 就不会使用索引。"独立的列" 是指索引列不能是表达式的一部分，也不能是函数的参数
例如，下面这个查询无法使用 actor_id 列的索引：
mysql>SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
凭肉眼很容易看出 WHERE 中的表达式 其实等价于 actor_id = 4， 但是MySQL 无法自动解析这个方程式。这完全是用户行为。我们应该养成简化 WHERE 条件的习惯。始终将索引列单独放在比较符号的一侧。
下面是另一个常见的错误：
mysql>SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;
5.3.2 前缀索引和索引选择性
有时候需要索引很长的字符列，这会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值(也称为基数，cardinality) 和 记录表的记录总数(#T) 的比值，范围从 1/#T 到1 之间。索引的选择性越高，则查询效率越高，因为选择性高的索引可以让MySQL 在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的
如下案例：
查询姓名列,前3个前缀字母开始：
mysql> select count(DISTINCT left(name, 3)) / count(*) from tb_hotel;
+------------------------------------------+
| count(DISTINCT left(name, 3)) / count(*) |
+------------------------------------------+
| 0.3980 |
+------------------------------------------+
1 row in set (0.01 sec)
查询姓名列,前10个前缀字母：
mysql> select count(DISTINCT left(name, 10)) / count(*) from tb_hotel;
+-------------------------------------------+
| count(DISTINCT left(name, 10)) / count(*) |
+-------------------------------------------+
| 0.9602 |
+-------------------------------------------+
1 row in set (0.00 sec)
查询姓名列,前15个前缀字母：
mysql> select count(DISTINCT left(name, 15)) / count(*) from tb_hotel;
+-------------------------------------------+
| count(DISTINCT left(name, 15)) / count(*) |
+-------------------------------------------+
| 1.0000 |
+-------------------------------------------+
经过实现后发先前缀长度为15比较合适
计算合适的前缀长度的另外一个办法就是计算完整列的选择性，并使前缀的选择性接接近于完列的选择性。下面显示了如何计算完整列的选择性：
mysql> select count(DISTINCT NAME) / count(*) from tb_hotel;
+---------------------------------+
| count(DISTINCT NAME) / count(*) |
+---------------------------------+
| 1.0000 |
+---------------------------------+
1 row in set (0.00 sec)
找到合适的前缀长度后，就可以创建前缀索引：
mysql>ALTER TABLE tb_hotel add key (name(15));
前缀索引是一种能使索引更小，更快的有效办法，但另一方面也有其缺点：MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。
前缀索引 具体流程可参考：
5.3.3 多列索引
很多人对多列索引的理解都不够。一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。
在多个列上建立独立的单列索引大部分情况并不能提高MySQL 的查询性能。MySQL 5.0 和更新版本引入了一种叫"索引合并"(index merge)的策略。一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的MySQL 只能使用其中某一个单列索引，然而这种情况下没有哪一个独立的单列索引使非常有效的。例如，表film_actor 在字段 film_id 和 actor_id 上各有一个单列索引。但对于下面这个查询 WHERE 条件，这两个单列索引都不是好的选择：
mysql>SELECT film_id,actor_id FROM sakila.film_actor WHERE film_id OR actor_id =1;
在老的MySQL 版本中，MySQL 会对这个查询使用全表扫描。除非改写成如下方式：
SELECT film_id,actor_id FROM sakila.film_actor WHERE actor_id =1;
UNION ALL
SELECT film_id,actor_id FROM sakila.film_actor WHERE film_id =1 AND actor_id <>1;
但在MySQL 5.0 和 更新版本中，查询能够同时使用这两个单列索引进行扫描，并接结果进行合并。这种算法有三个变种：OR 条件的联合(union)， AND条件的相交(intersection)，组合前两种情况的联合及相交。
添加索引：
alter table tb_hotel add key(price);
alter table tb_hotel add key(score);
下面的查询就使用了两个索引扫描的联合，通过EXPLAIN 中的Extra 列可以看到这点：
mysql> explain select * from tb_hotel where price = 336 or score = 37;
+----+-------------+----------+-------------+---------------+-------------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------------+---------------+-------------+---------+------+------+---------------------------------------+
| 1 | SIMPLE | tb_hotel | index_merge | price,score | price,score | 4,4 | NULL | 10 | Using union(price,score); Using where |
+----+-------------+----------+-------------+---------------+-------------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)
下面的查询就使用了两个索引扫描的相交，通过EXPLAIN 中的Extra 列可以看到这点：
mysql> explain select * from tb_hotel where price = 336 AND score = 37;
+----+-------------+----------+-------------+---------------+-------------+---------+------+------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------------+---------------+-------------+---------+------+------+-------------------------------------------+
| 1 | SIMPLE | tb_hotel | index_merge | price,score | price,score | 4,4 | NULL | 1 | Using intersect(price,score); Using where |
+----+-------------+----------+-------------+---------------+-------------+---------+------+------+-------------------------------------------+
1 row in set (0.00 sec)
索引合并策略有时候是一种优化的结果，但实际上更多时间说明了表上的索引建的很糟糕：
-
当出现服务器对多个索引做相交操作时(通常有多个AND条件)，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
-
当服务器需要对多个索引做联合操作时(通常有多个OR条件)，通常需要耗费大量CPU 和内存资源在算法的缓存、排序和合并操作上。特别时当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候
-
更重要的是，优化器不会把这些计算到"查询成本"(cost) 中，优化器只关心随机页面读取。这会使得查询成本被"低估"，导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的CPU和内存资源，还可能会影响查询的并发性，但如果是单独运行这样的查询则往往会忽略对并发性的影响。通常来说，还不如像在MySQL 4.1 或者更早的时代一样，将查询改些成UNION 的方式往往会更好。
如果在EXPLAIN 中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。也可以通过参数optimizer_switch 来关闭索引合并功能。也可以使用IGNORE INDEX 提示让优化器忽略掉某些索引
5.3.4 选择合适的索引列顺序
我们遇到的最容易引起困惑的问题就是索引列的顺序。正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要
在一个多列B-Tree 索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，所以可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY 和 DISTINCT等子句的查询需求。
所以多列索引的列顺序至关重要。在Lahdenmaki 和 Leach 的 "三星索引" 系统中，列顺序也决定了一个索引是否能够成为一个真正的"三星索引"
对于如何选择索引的列顺序有一个经验法则：将选择性高的列放在前面通常是很好的。这时候索引的作用只是用于优化 WHERE 条件的查找。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于WHERE 子句中只使用了索引部分前缀列的查询来说选择性也更高。
以下面查询为列：
SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;
是应该创建一个(staff_id, customer_id) 还是应该颠倒一下顺序？可以跑一些查询来确定在这个表中值分布情况，并确定哪个列的选择性更高。先用下面的查询预测一下，看看各个WHERE 条件的分支对应的数据基数有多大：
mysql> select sum(staff_id =2),sum(customer_id = 584) from tb_hotel;
+---------------+---------------------------+
| sum(staff_id =2) | sum(customer_id = 584) |
+---------------+---------------------------+
| 7992| 30|
+---------------+---------------------------+
1 row in set (0.00 sec)
根据前面的经验法则，应该将索引列 customer_id 放到前面，因为对应条件值的 customer_id 数量更小。我们再来看看对于这个 customer_id的条件值，对应的staff_id 列的选择性如何：
mysql> select sum(staff_id =2) from tb_hotel;
+------------------+
| sum(staff_id =2) |
+------------------+
| 17 |
+------------------+
1 row in set (0.00 sec)
这样做有一个需要注意的地方，查询的结果非常依赖于选定的具体值。如果按上述办法优化，可能其他一些条件值的查询不公平，服务器的整体性能可能变得更糟，或者其他某些查询的运行变得不如预期。
如果是从诸如pt-query-digest 这样的工具的报告中提取 "最差"查询，那么再按上述办法选定的索引顺序往往是非常高效的。如果没有类似的具体查询来运行，那么最好还是按经验法则来做，因为经验法则考虑的是全局基数和选择性，而不是某个具体的查询：
mysql>SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
>COUNT(DISTINCT customer_id) /COUNT(*) AS customer_id_selectivity,
>COUNT(*)
>FROM payment
***********************************1. row ***********************************
staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
COUNT(*): 16049
customer_id 的选择性更高，所以答案是将其作为索引列的第一列：
mysql>ALTER TABLE payment ADD KEY(customer_id, staff_id);
下面是一个遇到过的真实案例，在一个用户分享购买商品和购买经验的论坛上，这个特殊表上的查询运行得非常慢：
SELECT COUNT(DISTINCT threadid) AS count_value
FROM message
WHERE (groupId = 10137) AND (userId = 1288826) AND (anonymous = 0)
ORDER BY priority DESC, modifiedDate DESC;
这个查询看似没有建立合适得索引，所以客户咨询我们是否可以优化。EXPLAIN 结果如下：
id：1
select_type: SIMPLE
table:message
type: ref
key: ix_groupId_userId
ken_len: 18
ref: const,const
rows: 1251162
Extra: Using where
MySQL 为这个查询选择了 索引(groupId,userId)，如果不考虑列的基数，这看起来是一个非常合理的选择。但如果考虑一下userID 和 groupID 条件匹配的行数，可能就会有不同的想法了：
mysql>SELECT COUNT(*),SUM(groupId = 10137)
>SUM(userId = 1288826),SUM(anonymous = 0)
>FROM message
***********************************1. row ***********************************
count(*): 4142217
sum(groupId = 10137): 4092654
sum(userId = 1288826): 1288496
sum(anonymous = 0): 4141934
从上面的结果来看符合组(groupId) 条件几乎满足表中的所有行，符合用户(userId) 条件的有130 万条记录 ——— 也就是说索引基本上没什么用。因为这些数据是从其他应用迁移过来的，迁移的时候把所有的消息都赋予了管理员组的用户。这个案例的解决办法是修改了应用程序代码，区分这类特殊用户和组。禁止针对这类用户和组执行这个查询。
从这个小案例可以看到经验法则和推论在多数情况是有用的，但要注意不要假设平均情况下的性能也能代表特殊情况下的性能，特殊情况可能会摧毁整个应用的性能。
最后关于选择性和基数的经验法则值得去研究和分析，但一定要记住别忘了WHERE 子句中的排序、分组和范围条件等其他因素，这些因素可能对想查询的性能造成非常大的影响。
5.3.5 聚簇索引
聚簇索引并不是一种单独的索引类型，而是一种数据存储的方式。具体的细节依赖于其实现方式，但InnoDB 的聚簇索引实际上在同一个结构中保存了B-Tree 索引和 数据行
当表有聚簇索引时，它的数据行实际上存放在索引的叶页(left page)中。术语"聚簇" 表示数据行 和 相邻的键值紧凑地存储在一起。因为无法把数据行存在在两个不同的地方，所以一个表只能有一个聚簇索引
因为是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引
下图展示了聚簇索引中的记录时如何存放的。注意到，叶子页包含了行的全部数据，但是节点页只包含了索引列。在这个案例中，索引列包含是整数值
一些数据库服务器允许选择哪个索引作为聚簇索引，但知道本书写作之际，还没有任何一个MySQL内建的存储引擎支持这一点。InnoDB 将通过主键聚集数据，这也就是说上图的"被索引的列"就是主键列
如果没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚簇在同一个页面中的记录，包含相邻键值的页面可能会相距甚远。
聚簇的数据有一些重要优点：
- 可以把相关数据保存在一起。
- 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快
- 使用覆盖索引扫描的查询可以直接使用页节点中共的主键值
如果在设计表和查询时能充分利用上面的优点，那就能极大地提升性能。同时聚簇索引也有以下缺点：
- 聚簇数据最大限度地提高了I/O 密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了
- 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB 表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用 OPTIMIZE TABLE 命令重新组织一下表
- 更新聚簇索引的代价很高，因为会强制InnoDB 将每个被更新的行移动到新的位置
- 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动的时候，可能面临"页分裂(page split)" 的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分类操作。页分裂会导致表占用更多的磁盘空间
- 聚簇索引可能会导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
- 二级索引(非聚簇索引) 可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列
InnoDB 和MyISAM 的数据分布对比
来看看InnoDB 是如何存储下面这个表的：
CREATE TABLE layout_test(
col1 int not null,
col2 int not null,
PRIMARY KEY(col1),
KEY(col2)
);
假设该表的之间取值为1 ~ 10000，按照随机顺序插入并使用OPTIMIZE TABLE 命令做了优化。换句话说，数据在磁盘上的存储方式已经最优，但行的顺序是随机的。列col2 的值是从1 ~ 100 随机取值，所以有很多重复的值
MyISAM 表 layout_test的数据分布：
InnoDB 表 layout_test的数据分布：
该图显示了整个表，而不是只有索引。因为在InnoDB中，聚簇索引"就是"表，索引不像 MyISAM 那样需要独立的行存储
聚簇索引的每个叶子节点都包含了主键值、事务ID(TID)，用于事务和 MVCC(RP) 的回滚指针以及所有的剩余列(在这里例子中是col2)。如果主键是一个列前缀索引，InnoDB 也会包含完整的主键列和剩下的其他列
还有一点和 MyISAM 的不同是，InnoDB 的二级索引和聚簇索引很不相同。InnoDB 二级索引的叶子节点中存储的不是"行指针"，而是主键值，并以此作为指向行的"指针"。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当做指针会让二级索引占用更多空间，换来的好处是，InnoDB 在移动行时无须更新二级索引中的这个"指针"
下图显示了表的col2 索引。每一个叶子节点都包含了索引列(这里是col2)，紧接着是主键值(col1)
5.3.6 覆盖索引
通常大家都会根据查询的WHERE 条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是 WHERE 条件部分。索引确实是一种查找数据的高效方式，但是MySQL 也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。如果索引的叶子节点已经包含要查询的数据，那么还有什么必要再回表查询哪？如果一个索引包含(或者说覆盖)所有需要查询的字段的值，我们就称之为"覆盖索引"。
覆盖索引是非常有用的工具，能够极大地提高性能。考虑一下如果查询只需要扫描索引而无须回表，会带来多少好处：
- 索引条目通常远小于数据行大小，所以如果只需要读取索引，那么MySQL 就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于I/O 密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存种
- 因为索引是按照列值顺序存储的(至少在单个页内是如此)，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O 要少的多。
- 一些存储引擎如MyISAM 在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景
- 由于InnDB 的聚簇索引，覆盖索引对InnoDB 表特别有用。InnoDB 的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询
不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引，空间索引和全文索引等不存储索引列的值，所以MySQL 只能使用B-Tree 索引做覆盖索引。另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎支持覆盖索引
当发起一个被索引覆盖的查询(也叫做索引覆盖查询)时，在EXPLAIN 的 Extra 列可以看到 "Using index" 的信息。例如，表tb_hotel 有一个多列索引(score,price)。MySQL如果只访问这两列，就可以使用整个索引做覆盖索引
mysql> show index from tb_hotel;
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tb_hotel | 0 | PRIMARY | 1 | id | A | 201 | NULL | NULL | | BTREE | | |
| tb_hotel | 1 | idx_score_price | 1 | score | A | 33 | NULL | NULL | | BTREE | | |
| tb_hotel | 1 | idx_score_price | 2 | price | A | 201 | NULL | NULL | | BTREE | | |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql> EXPLAIN select score,price from tb_hotel;
+----+-------------+----------+-------+---------------+-----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+-----------------+---------+------+------+-------------+
| 1 | SIMPLE | tb_hotel | index | NULL | idx_score_price | 8 | NULL | 201 | Using index |
+----+-------------+----------+-------+---------------+-----------------+---------+------+------+-------------+
1 row in set (0.00 sec)
遇到问题:
新建多列索引(name, score)，无法使用覆盖索引
alter table tb_hotel add index idx_score_price(name, score);
mysql> show index from tb_hotel;
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tb_hotel | 0 | PRIMARY | 1 | id | A | 201 | NULL | NULL | | BTREE | | |
| tb_hotel | 1 | idx_score_price | 1 | score | A | 33 | NULL | NULL | | BTREE | | |
| tb_hotel | 1 | idx_score_price | 2 | price | A | 201 | NULL | NULL | | BTREE | | |
| tb_hotel | 1 | idx_name_score | 1 | name | A | 201 | 191 | NULL | | BTREE | | |
| tb_hotel | 1 | idx_name_score | 2 | score | A | 201 | NULL | NULL | | BTREE | | |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
mysql> EXPLAIN select name, score from tb_hotel;
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | tb_hotel | ALL | NULL | NULL | NULL | NULL | 201 | NULL |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
解决：
将 name 字段的长度修改为 155 后执行计划走 覆盖索引
mysql> alter table tb_hotel MODIFY NAME varchar(155);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> EXPLAIN select name, score from tb_hotel;
+----+-------------+----------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+----------------+---------+------+------+-------------+
| 1 | SIMPLE | tb_hotel | index | NULL | idx_name_score | 627 | NULL | 201 | Using index |
+----+-------------+----------+-------+---------------+----------------+---------+------+------+-------------+
1 row in set (0.00 sec)
原因：
猜测是因为name 字段建表时的长度为255，而后创建多列索引时 Sub_part 的值为 191，也就是将 name 255长度中的191拿来做索引，为什么不是NULL(全部，即255)？
建表语句如下：
CREATE TABLE `tb_hotel` (
`id` bigint(20) NOT NULL COMMENT '酒店id',
`name` varchar(255) NOT NULL COMMENT '酒店名称',
`address` varchar(255) NOT NULL COMMENT '酒店地址',
`price` int(10) NOT NULL COMMENT '酒店价格',
`score` int(2) NOT NULL COMMENT '酒店评分',
`brand` varchar(32) NOT NULL COMMENT '酒店品牌',
`city` varchar(32) NOT NULL COMMENT '所在城市',
`star_name` varchar(16) DEFAULT NULL COMMENT '酒店星级，1星到5星，1钻到5钻',
`business` varchar(255) DEFAULT NULL COMMENT '商圈',
`latitude` varchar(32) NOT NULL COMMENT '纬度',
`longitude` varchar(32) NOT NULL COMMENT '经度',
`pic` varchar(255) DEFAULT NULL COMMENT '酒店图片',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_score_price` (`score`,`price`),
KEY `idx_name_score` (`name`(191),`score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT
Sub_part NULL 的含义可以参考：
https://blog.csdn.net/Java_LingFeng/article/details/127684064
name 字段更新为 155 后 查看索引 ，sub_part 为 NULL
个人总结：字段的长度也会影响是否走覆盖索引
索引覆盖查询还有很多陷阱可能会导致无法实现优化。MySQL 查询优化器会在执行查询前判断是否有一个索引能进行覆盖。假设索引覆盖了WHERE 条件中的字段，但不是涉及整个查询的字段。如果条件为假(false)，MySQL 5.5 和更早的版本总是会回表获取数据行，尽管并不需要这一行且最终会被过滤掉
来看看为什么会发生这样的情况，以及如何重写查询以解决问题，从下面的查询开始：
>mysql>EXPLAIN SELECT * FROM products WHERE actor = 'SEAN CARREY' AND title like '%APOLLO%';
***********************************1. row ***********************************
id：1
select_type: SIMPLE
table:products
type: ref
possible_keys: ACTOR,IX_PROD_ACTOR
key: ACTOR
ken_len: 52
ref: const
rows: 10
Extra: Using where
这里的索引无法覆盖该查询，有两个原因：
-
没有任务索引能够覆盖这个查询。因为查询从表中选择了所有的列，而没有任何的索引覆盖了所有的列。不过，理论上MySQL 还有一个捷径可以利用：WHERE 条件中的列是有索引可以覆盖的，因此MySQL 可以使用该索引找到对应的actor 并检查title 是否匹配，过滤之后再读取需要的数据行。
-
MySQL 不能再索引中执行LIKE操作。这是底层存储引擎API的限制，MySQL 5.5 和更早的版本中只允许在索引中做简单比较操作(例如等于、不等于以及大于)。MySQL能在索引中做最左前缀匹配的LIKE比较，因此该操作可以转换为简单的比较操作，但是如果是通配开头的LIKE查询，存储引擎就无法做比较匹配。这种情况下，MySQL 服务器只能提取数据行的值而不是索引值来做比较
也有办法可以解决上面说的问题，需要重写查询并巧妙地设计索引。先将索引扩展至覆盖三个数据列(artist,title,prod_id)，然后按如下方式重写查询：
mysql>EXPLAIN SELECT * FROM products JOIN (SELECT prod_id FROM products WHERE actor = 'SEAN CARREY' AND title like '%APOLLO%') AS t1 ON (t1.product_id = products.product_id)
***********************************1. row ***********************************
id：2
select_type: DERIVED
table:products
type: ref
possible_keys: ACTOR,ACTOR2,IX_PROD_ACTOR
key: ACTOR_2
ken_len: 52
ref:
rows: 11
Extra: Using where;Using index
我们把这种方叫做延迟关联(deferred join), 因为延迟了对列的访问。在查询的第一阶段MySQL 可以使用覆盖索引，在FROM 子句的子查询中找到匹配的prod_id，然后根据这些prod_id值在外层查询匹配获取所需要的所有列值。虽然无法使用索引覆盖整个查询，但总比完全无法利用覆盖索引的好
在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引种部分列的查询。不过，可以更近一步优化InnoDB 。回想一下，InnoDB 的二级索引的叶子节点都包含了主键值，这意味着InnoDB 的二级索引可以有效地利用这些"额外"的主键来覆盖查询
例如，sakila.actor 使用InnoDB 存储引擎，并在last_name 字段有二级索引，虽然该索引的列不包含主键actor_id，但也能够用于对actor_id 做覆盖查询：
EXPLAIN SELECT actor_id, last_name FROM skaila.acotr WHERE last_name = 'HOPPER'
***********************************1. row ***********************************
id： 1
select_type: SIMPLE
table: actor
type: ref
possible_keys: idx_actor_last_name
key: inx_actor_last_name
ken_len: 137
ref: const
rows: 2
Extra: Using where;Using index
5.3.7 使用索引来做排序
MySQL 有两种方式可以生成有序的结果： 通过排序操作；或者按搜索顺序扫描；如果EXPLAIN 出来的type 列的值为"index"，则说明MySQL 使用了索引扫描来做排序(不要和Extra 列的"Using Index" 搞混淆了)
扫描索引本身是很快的，因为只需要从一条记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录都回表查询一次对应的行。这基本上都是随机I/O，因为按索引顺序读取数据的(顺序I/O) 速度通常要比顺序地全表扫描慢，尤其是在I/O密集型的工作负载时
MySQL 可以使用同一个索引即满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能地同时满足这两种任务，这样是最好的
只有当索引的列顺序 和 Order By 子句的顺序完全一致，并且所有列的排序方向(倒序或正序)都一样时，MySQL 才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY 子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY 子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则MySQL 都需要执行排序操作，而无法利用索引排序。
有一种情况下ORDER BY 子句可以不满足索引的最左列前缀的要求，就是前导列为常量的时候。如果WHERE 子句或者JOIN 子句中对这些列指定了常量，就可以"弥补"索引的不足。
例如，Sakila 示例数据库的表 rental 在列(rental_date,inventory_id,customer_id) 上有名为rental_date 的索引。
CREATE TABLE rental(
...
PRIMARY KEY rental_id,
UNIQUE KEY rental_date(rental_date,inventory_id,customer_id),
KEY idx_fk_inventory_id(inventory_id),
KEY idx_fk_customer_id(customer_id),
KEY idx_fk_staff_id(staff_id),
...
);
MySQL 可以使用rental_date 索引为下面的查询做排序，从EXPLAIN 中可以看到没有出现文件排序(filesort) 操作:
```sql
sql>EXPLAIN SELECT rental_id,staff_id FROM sakila.rental WHERE rental_date = '2005-05-25' ORDER BY inventory_id,customer_id
***********************************1. row ***********************************
type: ref
possible_keys: rental_date
key: rental_date
ken_len: 137
ref: const
rows: 1
Extra: Using where
即使ORDER BY 子句不满足索引的最左前缀的要求，也可以用于查询排序，这是因为索引的第一列被指定为一个常数。
还有更多可以使用索引做排序的查询示例。下面这个查询可以利用索引做排序，是因为查询为索引的第一列提供了常量条件，而使用第二列进行排序，将两列组合在一起，就形成了索引的最左前缀：
...WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC;
下面这个查询也没问题，因为ORDER BY 使用的两列就是索引的最左前缀：
...WHERE rental_date > '2005-05-25' ORDER BY rental_date,inventory_id
下面是一些不能使用索引做排序的查询：
# 下面这个查询使用了两种不同的排序方向，但是索引列都是正序排序的
...WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC, customer_id ASC;
# 下面这个查询的ORDER BY 子句中引用了一个不在索引中的列：
...WHERE rental_date = '2005-05-25' ORDER BY inventory_id,staff_id;
# 下面这个查询的WHERE 和 ORDER BY 中的列无法组合成索引的最左前缀：
...WHERE rental_date = '2005-05-25' ORDER BY customer_id;
# 下面这个查询在索引的第一列上是范围条件，所以MySQl 无有使用索引的其余列：
...WHERE rental_date > '2005-05-25' ORDER BY inventory_id, customer_id;
# 这个查询在inventory_id 列上有多个等于条件。对于排序来说，这也是一种范围查询
...WHERE rental_date = '2005-05-25' AND inventory_id IN(1,2) ORDER BY customer_id;
冗余和重复索引
MySQL 允许在相同列上创建多个索引，无论是有意的还是无意的。MySQL 需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。
重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。
有时会在不经意间创建了重复索引，例如下面代码：
CREATE TABLE test(
ID INT NOT NULL PRIMARY KEY,
A INT NOT NULL,
B INT NOT NULL,
UNIQUE(ID),
INDEX(ID)
) ENGINE=InnoDB;
一个经验不足的用户可能是想创建一个主键，先加上唯一限制，然后再加上索引以供查询使用。事实上，MySQL 的唯一限制和主键限制都是通过索引来实现的，因此，上面的写法实际上在相同的列上创建了三个重复的索引。通常并没有理由这样做，除非是在统一列上创建不同类型的索引来满足不同的查询需求。
冗余索引和重复索引有一些不同。如果创建了索引(A,B)，再创建索引(A) 就是冗余索引，因为这只是前一个索引的前缀索引。因此索引(A,B) 也可以当作索引(A) 来使用(这种冗余只是对B-Tree索引来说的)。但是如果再创建索引(B,A)， 则不是冗余索引，索引(B) 也不是，因为B不是索引(A,B) 的最左前缀列。另外，其他不同类型的索引(例如哈希索引或者全文索引)也不会是B-Tree 索引的冗余索引， 而不论覆盖的索引列是什么
冗余索引通常发生再为表添加新索引的时候。例如，有人可能会增加一个新的索引(A,B) 而不是扩展已有的索引(A)。还有一种情况是将一个索引扩展为(A,ID)，其中ID是主键，对于InnoDB 来说主键已经包含在二级索引中了，所以这也是冗余的。
大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。 但也有时候处于性能方面的考需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询性能。
例如，如果在整数列上有一个索引，现在需要额外增加一个很长的VARCHAR 列来扩展该索引，那么性能可能会急剧下降。特别上有查询把这个擦汗寻当作覆盖索引时
本文来自博客园，作者：chuangzhou，转载请注明原文链接：https://www.cnblogs.com/czzz/p/18015586
