3.5 索引
3.5.1 索引介绍
数据库索引，是数据库管理系统中一个排序的数据结构，用于协助快速查询、更新数据库表中的数据。它类似于书本上的索引，通过索引可以更便捷地找到书里面的内容而不需要查阅整本书。对于海量数据的检索，索引往往是最有效的。
目前MySQL主要支持的几种索引有：B树索引（B-tree）、散列索引（hash）、空间索引（R-tree）和全文索引（full-text）。如果没有特别指明，本书指的就是B-Tree索引。由于索引是在存储引擎层实现的，所以不同的存储引擎的索引实现会有一些差异。以下所述的是一些较通用的索引知识。
逻辑上又可以分为：单列索引、复合索引（多列索引）、唯一（Unique）索引和非唯一（Non Unique）索引。
如果索引键值的逻辑顺序与索引所服务的表中相应行的物理顺序相同，那么该索引被称为簇索引（cluster index），也称为聚集索引、聚簇索引，也就是说数据和索引（B+树）在一起，记录被真实地保存在索引的叶子中，簇索引也称为索引组织表，反之为非聚集索引。我们常用的InnoDB表其实使用的就是聚集索引。
簇索引是一个很重要的概念，InnoDB作为最常使用的引擎，只有在熟悉了它的数据存储方式之后，才可能有针对性地对它进行调优。
簇索引的一些优点如下。
将相关的的数据保持在一起，叶子节点内可保存相邻近的记录。
因为索引和数据存储在一起，所以查找数据通常比非簇索引更快。由于主键是有序的，很显然，对于InnoDB表，最高效的存取方式是按主键存取唯一记录或进行小范围的主键扫描。
如果充分利用簇索引，它可以极大地提升性能，但簇索引也有许多不足之处。
簇索引对I/O密集型的负荷性能提升最佳，但如果数据是在内存中（访问次序不怎么重要），那么簇索引并没有明显益处。
插入操作很依赖于插入的顺序，按primary key的顺序插入是最快的。
更新簇索引列的成本比较高，因为InnoDB不得不将更新的行移动到新的位置。
全表扫描的性能不佳，尤其是数据存储得不那么紧密时，或者因为页分裂（page split）而导致物理存储不连续。
二级索引的叶节点中存储了主键索引的值，如果主键采用的是较长的字符，那么索引可能会很大，且通过二级索引查找数据也需要进行两次索引查找。
3.5.2 使用索引的场景及注意事项
1.何种查询可以应用索引
（1）MySQL目前仅支持前导列
筛选记录的条件应能组成复合索引最左边的部分，即按最左前缀的原则进行筛选。随着日后技术的发展，MySQL或许能够更有效率地利用复合索引多字段中的非前导列信息。
下面来看个例子，对于如下的复合索引idx_a_b_c：CREATE INDEX idx_a_b_c ON tb1(a,b,c);
只有使用如下条件才可能应用到这个复合索引。
WHERE a=?
WHERE a=? AND b=?
WHERE a=? AND b=? AND c=?
WHERE a=? AND c=? #注意这个查询仅仅利用了MySQL索引的a列信息```
（2）索引列上的范围查找
在对某个条件进行范围查找时，如果这个列上有索引，且使用的是WHERE…BETWEEN…
AND …、>、<等范围操作符时，那么可能就会用到索引范围查找。一般应该避免大范围的索引范围查找，如果索引范围查找的成本太高，那么数据库可能会选择全表扫描的方式。
<div style="text-align: center">
<img src="https://yqfile.alicdn.com/f2e050935b03a3a76c21a157228099429752d811.png" >
</div>
（3）JOIN列
在联合查询两个表时，比如查询语句为“SELECT a.col1, b.col2 FROM a JOIN b ON a.id=b.id”，其中id为主键，若a表是驱动表，那么数据库可能全表扫描a表，并用a表的每个id去探测b表的索引查找匹配的记录。
（4）WHERE子句
WHERE子句的条件列是复合索引前面的索引列再加上紧跟的另一个列的范围查找。
比如，对于如下的复合索引idx_a_b_c_d：
``CREATE INDEX idx_a_b_c_d ON tb1(a,b,c,d);``
只有使用如下条件才可能应用到这个复合索引。
``WHERE a=? AND b=? AND c > 10000;``
``WHERE a=? AND b=? AND c=? AND d<10000;``
<div style="text-align: center">
<img src="https://yqfile.alicdn.com/ad8c26ea7b80543be09f313ec33e6566d5a6600a.png" >
</div>
对于复合索引idx_a_b_c_d，我们来看如下的两个查询。
``WHERE a=? AND b=? AND c > 10000 AND d<100000;``
上面的例子中，d<100000这个筛选操作并不会走索引。
上面的例子中，a列上有范围查找，那么b、c、d等列上的索引信息将都不能被利用。
即对于复合索引，如果某部分索引已经用到了范围查找，那么这个列之后的索引信息将不能被利用。
所以如果想要创建索引，应该考虑把复合索引的范围查找列放到最后。
（5）MySQL优化器
MySQL优化器会做一些特殊优化，比如对于索引查找MAX（索引列），那么可以进行直接定位，在EXPLAIN输出的Extra信息里可以看到语句“Select tables optimized away”，意思是这个查询所包含的MIN、MAX操作可以直接利用索引信息来解决，而不需要去检索物理记录。优化器确定只需要返回一行结果即可。
2.注意事项和建议
1）WHERE条件中的索引列不能是表达式的一部分，MySQL也不支持函数索引。
2） InnoDB的非主键索引存储的不是实际记录的指针，而是主键的值，所以主键最好是整型值，如自增ID，基于主键存取数据是最高效的，使用二级索引存取数据则需要进行两次索引查找。
3）最好是按主键的顺序导入数据，如果导入大量随机id的数据，那么可能需要运行OPTIMIZE TABLE命令来优化表。
4）索引应尽量是高选择性的，而且需要留意“基数（cardinality）”值，基数指的是一个列中不同值的个数，显然，最大基数意味着该列中的每个值都是唯一的，最小基数意味着该列中的所有值都是相同的。索引列的基数相对于表的行数较高时（也就是说重复值更少），索引的工作效果更好。
一些基数很小的列，如性别可能就不适合建立索引。也存在这样一种特殊的情况，有些列虽然基数很小，但由于数据分布很不均匀因此也会导致某些值的记录数很少，那么这种情况也适合创建索引加速查找这部分数据。
5）使用更短的索引。可以考虑前缀索引，前缀索引仅索引前面一部分字符（值），但应确保所选择的前缀的长度可以保证大部分值是唯一的。
示例如下：
``ALTER TABLE test.test1 ADD KEY (col(6))``
如下的SQL衡量了不同前缀索引的唯一值比例。
SELECT COUNT(DISTINCT LEFT(col_name, 3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(col_name, 4))/COUNT(*) AS sel4,
COUNT(DISTINCT LEFT(col_name, 5))/COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(col_name, 6))/COUNT(*) AS sel6,
COUNT(DISTINCT LEFT(col_name, 7))/COUNT(*) AS sel7
FROM table_name;`
6）索引太多时可能会浪费空间，且降低修改数据的速度。所以，不要创建过多的索引，也不要创建重复的索引。MySQL允许在同样的列上创建多个索引而不会提示报错，一些其他分支的版本有统计信息可以甄别出没有被使用的索引。而对于官方版本，你可能需要借助工具清理掉过多的重复索引。
7）如果是唯一值的列，创建唯一索引会更佳，也可以确保不会出现重复数据。
8）使用覆盖索引（covering index）也可以大大提高性能。
所谓“覆盖索引”是指所有数据都可以从索引中得到，而不需要去读取物理记录。例如某个复合索引idx_a_b_c建立在表tb1的a、b、c列上，那么对于如下的SQL语句
MySQL可以直接从索引idx_a_b_c中获取所有数据。使用覆盖索引也可以避免第2点所说的二次索引查找。
在EXPLAIN命令输出的查询计划里，如果Extra列是“using index”，那就表示使用的是覆盖索引。EXPLAIN的使用在下节详述。
9）利用索引来排序。MySQL有两种方式可以产生有序的结果。一种是使用文件排序（filesort）来对记录集进行排序，另一种是扫描有序的索引。我们应尽量利用索引来排序。
在文件排序方式中，由于没有可以利用的有序索引来取得有序的数据，因此MySQL只能将取得的数据在内存中进行排序，然后再将数据返回给客户端。使用文件排序的方式，对小结果集进行排序会很快，但是如果是对大量的数据排序，速度将会很慢。此外，还有如下注意事项。
尽量保证索引列和ORDER BY的列相同，且各列均按相同的方向排序。
如果要连接多张表，那么ORDER BY引用的列需要在表连接的顺序的首张表内。
如果不满足以上条件，则不能利用索引进行排序，那么MySQL将使用文件排序，可以用EXPLAIN工具确认查询是否使用了文件排序，文件排序是一个成本比较高的操作，应尽量避免。利用索引来排序同样要遵循最左前缀的规则，前导列（等于确定值）加上排序列（ORDER BY的列）可以组合成最左前缀的也行。比如，对于创建在表table1上的复合索引idx_a_b_c（创建在列a、b、c上）：
以上查询都可以利用有序索引来加速检索数据。
10）添加冗余索引，需要权衡。
什么是冗余索引？如果已有一个索引（columnA），那么一个新的索引（columnA，columnB）就是冗余索引，因为后面的索引包含了前面索引的所有信息。
冗余索引一般发生在添加索引的时候，有些人可能会选择添加一个新索引（columnA，columnB），而不是更改原来的（columnA）为（columnA，columnB）。一般来说，应该扩展原来的索引，而不是添加新的索引。但在某些情况下，因为扩展索引会导致索引变得非常大，比如原来的索引是创建在一个整型列上的，要是再添加一个很长的字符列，那么索引会变得很大，从而影响性能。这种情况下，可能不得不选择添加新的复合索引，保留原来的索引，这样做的不利之处是增加了索引维护的开销，而且一个新的索引也需要占据内存空间。
3.5.3 索引的错误用法
以下是生产环境中常犯的一些错误，而且由于表结构不易调整，因此往往会导致严重的性能影响。
1）创建了太多的索引或无效的索引。比如在WHERE条件的每个列上都建立单独的索引，当单个索引效率不高的时候，MySQL往往就会选择全表扫描，太多的索引可能会导致索引所占用的磁盘空间比实际数据还大得多。
2）对于复合索引，如果不考虑ORDER BY、GROUP BY这样的一些操作，那么把最具选择性的列放在前面是合适的，复合索引主要用于优化WHERE查找。但如果是排序之类的操作，把最具选择性的列放在前面则不一定最有效，因为避免随机I/O和排序可能才是我们更值得考虑的。
3） 忽略了值的分布。某些值只有少量记录，查询对这些值的筛选执行就会很快，而某些值即使经过了索引筛选，满足条件的仍然还有大量的记录，这样索引效果就会很差。一般来说，数据表内值的分布应该尽量均匀，由于MySQL的统计信息不完善，数据分布不均匀很可能会产生很差的执行计划，导致严重的性能问题。
4） InnoDB主键过长，导致二级索引过大。主键的选择，一般建议是整型。
以上介绍了索引的使用规则和建议。接下来介绍EXPLAIN工具。互联网应用的开发，索引的调整往往是调优的一个重点，特别是对数据库技术不熟练的团队，在数据量增长后可能会碰到各种性能问题，这通常是因为索引不佳而引起的。EXPLAIN工具的应用不局限于索引的检查，但由于它和索引关系密切，下面将详细介绍下此工具。
3.5.4 如何使用EXPLAIN工具
无论是做研发还是DBA，都有必要学会EXPLAIN工具的使用。使用EXPLAIN工具可以确认执行计划是否良好，查询是否走了合理的索引。不同版本的MySQL优化器各有不同，一些优化规则随着版本的发展可能会有变化，查询的执行计划随着数据的变化也可能会有变化。对于这类情况可以使用EXPLAIN来验证自己的判断。
以下是对EXPLAIN工具的使用说明。我们首先来介绍一下MySQL执行计划的调用方式，然后对执行计划显示的内容进行解读，最后来说一说MySQL执行计划的局限。
1. MySQL执行计划调用方式
我们使用EXPLAIN命令查看执行计划，语法形式类似如下语句。
EXPLAIN命令还有如下两种变体。
1）EXPLAIN EXTENDED SELECT ……
以上命令将执行计划“反编译”成SELECT语句，运行SHOW WARNINGS可得到被MySQL优化器优化后的查询语句。
2）EXPLAIN PARTITIONS SELECT ……
以上命令用于分区表的EXPLAIN命令。
2.执行计划包含的信息及解读
如下是一个显示执行计划的例子。
该例中EXPLAIN命令的输出信息可以告诉我们MySQL访问了哪些表，以及它是如何访问数据的。里面有很重要的索引使用信息，我们可以据此判断我们的索引是否需要优化。
下面将详细阐述EXPLAIN输出的各项内容。
（1）id
id包含一组数字，表示查询中执行SELECT子句或操作表的顺序。
如果id相同，则执行顺序由上至下，例如：
如果是子查询，id的序号会递增，id值越大则优先级越高，越先被执行。例如： 
如果id相同，则可以认为它们是一组，从上往下顺序执行。在所有组中，id值越大，优先级就越高，越先执行。例如：
（2）select_type
select_type表示查询中每个SELECT子句的类型（是简单还是复杂）。输出结果类似如下：
下面是对select_type的详细说明。
SIMPLE：查询中不包含子查询或UNION。
查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
在SELECT或WHERE列表中若包含了子查询，则该子查询被标记为SUBQUERY。
在FROM列表中包含的子查询将被标记为DERIVED（衍生）。
若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在 FROM子句的子查询中，则外层SELECT将被标记为DERIVED。
从UNION表中获取结果的SELECT将被标记为UNION RESULT。
下面我们通过一个例子来说明查询的类型和执行的顺序。
第一行：id列为1，表示第一个SELECT，select_type列的PRIMARY表示该查询为外层查询，table列被标记为，表示查询结果来自于一个衍生表，其中3代表该查询衍生自第3个SELECT查询，即id为3的SELECT。
第二行：id为3，表示该查询的执行次序为2（4→3），是整个查询中第3个SELECT的一部分。因为查询语句包含在FROM子句中，所以为DERIVED。
第三行：SELECT列表中的子查询，select_type为SUBQUERY，为整个查询中的第2个SELECT。
第四行：select_type为UNION，说明第4个SELECT是UNION里的第2个SELECT，最先执行。
第五行：代表从UNION的临时表中读取行的阶段，table列的表示对第1个和第4个SELECT的结果进行UNION操作。
（3）type
type表示MySQL在表中找到所需行的方式，又称“访问类型”，常见的类型如下：
以上类型，由左至右，由最差到最好。下面我们来详述每种类型。
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行。如下是一个type为All的例子。
index：Full Index Scan，index与ALL区别为index类型只遍历索引树。如下是一个type为index的例子。
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询。如下是两个type为range的例子。
一般来说，索引范围扫描要检索的记录更少，因而成本也更低。大量的索引扫描，可能还会导致性能问题。例如，对于如下的两个查询，后一个查询需要检索的记录数就比前一个查询多得多（参考rows列）。
ref：非唯一性索引扫描，将返回匹配某个单独值的所有行。常见于使用非唯一索引或唯一索引的非唯一前缀进行的查找。如下是type为ref的几个例子。
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。如下是type为eq_ref的一个例子。
const、system：当MySQL对查询的某部分进行优化，并转换为一个常量时，可使用这些类型进行访问。如将主键置于WHERE列表中，MySQL就能将该查询转换为一个常量。system是const类型的特例，当查询的表只有一行的情况下，即可使用system。如下是type为const和system的一个例子。
NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引。如下是type为NULL的一个例子。
（4）possible_keys
possible_keys将指出MySQL能使用哪个索引在表中找到行，查询涉及的字段上若存在索引，则该索引将被列出，但不一定会被查询使用。
（5）key
key将显示MySQL在查询中实际使用到的索引，若没有使用索引，则显示为NULL。查询中若使用了覆盖索引，则该索引仅出现在key列表中。如下是使用覆盖索引的一个例子。
（6）key_len
key_len表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。下面我们通过一个例子来说明。
上面的t1表col1和col2字段使用的是utf8字符集，utf8字符集的最大字符长度为3个字节，也就是说，它们可能需要12个字节存储一个值。复合索引idx_col1_col2是创建在col1、col2列上的索引。如下两个查询中，第一个查询我们可以看到key_len为13，它只用到了复合索引idx_col1_col2的前半部分信息。第二个查询的key_len为26，它是完整的索引长度，由此可知t1表的索引idx_col1_col2已被充分使用。
注意，key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得的，而不是通过表内检索得出的。
（7）ref
ref表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。如下的例子中，col1匹配t2表的col1，col2匹配了一个常量，即ac：
（8）rows
rows表示MySQL根据表统计信息及索引选用的情况，估算地找到所需的记录所需要读取的行数。如下的例子中，我们可以看到，在创建索引后，执行计划发生改变，所需要读取的行数减少了。
（9）Extra
Extra包含不适合在其他列中显示但十分重要的额外信息。它可能包含如下4种信息。
1）Using index。该值表示相应的SELECT操作中使用了覆盖索引。包含满足查询需要的所有数据的索引称为覆盖索引。如下的查询就使用到了覆盖索引。
2）Using where。该值表示MySQL服务器在存储引擎收到记录后进行“后过滤”（Post-filter）。
如果查询未能使用索引，则Using where的作用只是提醒我们MySQL将用where子句来过滤结果集。如下的查询同时使用到了覆盖索引和过滤。
3）Using temporary。该值表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。
如下是一个使用临时表的例子。
如上查询的EXPLAIN输出中Extra列同时有Using temporary和Using filesort，因此性能可能不佳。如果我们更改了GROUP BY子句，利用索引进行排序，则可以看到EXPLAIN输出里没有了Using temporary和Using filesort，示例如下。
4）Using filesort。Using filesort 即文件排序。MySQL中将无法利用索引完成的排序操作称为“文件排序”。如下便是一个使用到了文件排序的例子。
如果我们更改查询，利用索引进行排序，则可以优化掉文件排序，例如如下的查询，已经没有了filesort（文件排序）。
3. MySQL执行计划的局限
EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况。
EXPLAIN不考虑各种Cache。
EXPLAIN不能显示MySQL在执行查询时所做的优化工作。
部分统计信息是估算的，并非精确值。
MySQL 5.6之前EXPALIN只能解释SELECT操作，其他操作需要重写为SELECT后才能查看执行计划。
如果FROM子句里有子查询，那么MySQL可能会执行这个子查询，如果有昂贵的子查询或使用了临时表的视图，那么EXPLAIN其实会有很大的开销。
3.5.5 优化索引的方法学
以上介绍了索引的结构和使用索引的一些规则，随着项目经验的增长，开发人员对于数据库都有一个从不熟悉到熟悉的过程，但由于开发人员的专注领域并不是数据库设计开发，而且不同的数据库产品也有差异，因此导致了部分开发人员对索引产生了一些误解。生产环境中数据库出现性能问题，有80%的原因是索引策略导致的，表结构不易变动，而调整索引或SQL往往可以很快就能解决问题，在开发或上线后，可遵循以下的方法和步骤进行优化。
（1）有性能测量
在应用程序中记录访问数据库的性能日志，这样就可以对整体的访问吞吐有一个很直观很全面的统计，我们应该优化对数据库操作最频繁、最耗资源的那些SQL，但由于性能统计框架的缺位，大部分中小公司更多地依赖于数据库自身的慢查询日志来定位耗时较长的SQL，由慢查询日志入手也是一个很好的出发点，但可能存在一些滞后，不能及时发现性能问题，MySQL的慢查询日志默认记录查询时间超过1s的查询，4.3节将详细介绍慢查询日志。
（2）查看执行计划
找到消耗资源最多的查询请求后，可以使用EXPLAIN工具查看其执行计划，检查是否走的是合适的索引。
（3）优化索引
我们应该熟悉数据量、数据类型等信息及表之间的关系，按照自己的索引经验，调整或增加索引。
（4）测试验证
如果是线上生产环境，那么请不要在线上环境进行测试验证，除非是非常紧急的情况。应该选择在开发环境中尽量使用和线上环境一样的数据规模，来进行验证测试。
（5）上线
当确认优化达到了预期的效果后，就可以安排上线了。
有一个错误的观念是定期重建索引，这种方式在早期的传统数据库中用得很多，基于的主要理由是经过长期的生产运行，索引变得越来越不平衡，但是否需要定期重建索引是有争议的。MySQL在互联网行业一般是OLTP应用，索引重建将导致服务变得不可用，更重要的是，在绝大部分情况下，重建了和没有重建索引，性能上并没有什么区别，唯一可能的场景是在大量删除导入数据后，会导致数据表严重变形。如果需要重建索引，首先要证明，重建索引真的能够大大改善性能，否则建议不要做这种费力又不讨好的事情，数据库索引本来就应该是“不好不坏”的状态，不要期望它始终以一种理想的状态在运行。