⑨ MySQL优化-索引优化/索引失效/EXPLAIN分析

调优目的：响应时间更快、吞吐量更大

1 数据库优化步骤：

2 慢查询工具

2.1 查看服务器性能参数

-- SHOW [GLOBAL|SESSION] STATUS LIKE '参数'
-- SHOW STATUS LIKE 'connections'        #查询连接数
-- SHOW STATUS LIKE 'slow_queries'        #查询慢查询次数

#统计sql的查询成本
SHOW STATUS LIKE 'last_query_cost'

2.2 慢查询日志管理

1.慢查询日志记录mysql执行超过一定时间阈值的语句；一般不是调优需要，不建议启动该功能，开启慢查询日志会带来一定性能影响；

2.管理慢查询日志：
-- SHOW VARIABLES LIKE 'slow_query_log'    查看慢查询日志状态，一般情况为OFF
-- SET GLOBAL slow_query_log='ON'    开启慢查询日志

-- SHOW VARIABLES LIKE 'long_query_time'        #查看慢查询日志阈值,默认10s
-- SET GLOBAL long_query_time=1    #设置慢查询日志阈值

--SHOW VARIABLES LIKE 'slow_query_log%'
slow_query_log_file    /data/local/mysql/data/kwepwebenv14422-slow.log        #慢查询日志存储路径
slow_query_log    OFF        #慢查询日志开启状态

-- SHOW GLOBAL STATUS LIKE 'slow_queries'    #查看慢查询记录条数

2.3 慢查询日志分析工具-mysqldumpslow

1.使用方式：
mysqldumpslow [ OPTS... ] [ LOGS... ]


--verbose    verbose
--debug      debug
--help       write this text to standard output

-v           verbose
-d           debug
-s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

2.常见用法：
mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log # 取出使用最多的10条慢查询
mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log # 取出查询时间最慢的3条慢查询
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log # 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s r -t 10 -g 'left join' /var/run/mysqld/mysqld-slow.log # 按照扫描行数最多的

3 EXPLAIN分析

EXPLAIN可以查看DML的执行计划，返回的执行计划是查询优化器生成的；

3.1 使用方法

EXPLAIN INSERT INTO test_table VALUES(1,2),(3,4)
EXPLAIN DELETE FROM test_table WHERE field1 = 1
EXPLAIN UPDATE test_table SET field1=1,field2=1 WHERE field1 != 1
EXPLAIN SELECT * FROM test_table

3.2 分析结果集详情分析

-- id

查询的标识符，每个查询都有一个唯一的标识符
1.id相同，被认为是一组从上往下按顺序执行；
2.所有组中id越大，优先级越高，越先执行；
3.每个相同的id表名一次独立的查询，一个sql独立查询次数越少越好；

-- table

table字段的值表示查询中涉及的表的名称或别名，需要结合其他字段一起分析才能更好地理解查询的执行计划；

-- select_type

在MySQL的EXPLAIN分析结果中，select_type字段表示了查询的类型，它是一个枚举值，可能的取值及其含义如下：

1. SIMPLE：简单的SELECT查询，不包含子查询或UNION操作。
2. PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
3. SUBQUERY：在SELECT或WHERE列表中包含了子查询。
4. DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，并将结果放入临时表中。
5. UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED。
6. UNION RESULT：从UNION表获取结果的SELECT。

通过查看select_type字段，可以帮助我们更好地理解查询的执行过程，从而优化查询性能。例如，如果查询中包含了子查询，我们可以考虑将其改写为JOIN操作，以减少查询的复杂度

-- type

type字段表示MySQL在执行查询时所使用的访问类型，也就是MySQL在表中查找行的方式。

以下是type字段可能的取值及其含义：

- system：表只有一行记录（等同于系统表），这是const类型的特例。
- const：通过索引一次就找到了，const用于比较primary key或unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
- eq_ref：类似const，使用唯一索引查找，但是在查询时使用了外键关联。
- ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。
- range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。范围条件用于确定哪些行符合要求。
- index：全索引扫描，与ALL类型类似，只不过只扫描索引树。
- all：全表扫描，将遍历全表以找到匹配的行。

一般来说，type的值从最优到最差的顺序是：const、eq_ref、ref、range、index、all。因此，我们应该尽量避免使用all类型的查询，因为它会导致全表扫描，效率非常低下。

-- possible_keys

possible_keys字段表示查询语句可能使用的索引。

具体来说，possible_keys字段列出了查询语句中可以使用的索引列表。这些索引是根据查询语句中的WHERE条件、JOIN条件和ORDER BY子句等信息推断出来的。如果查询语句中的某个条件可以使用索引来加速查询，那么该条件对应的索引就会出现在possible_keys字段中。

需要注意的是，possible_keys字段列出的索引并不一定会被实际使用。实际使用的索引是由MySQL优化器根据查询语句的具体情况和表的统计信息等因素来决定的。因此，即使possible_keys字段中列出了多个索引，实际使用的索引可能只有其中的一部分或者甚至没有。

在优化查询语句时，可以通过分析possible_keys字段来确定查询语句是否使用了正确的索引，以及是否需要创建新的索引来优化查询性能。

-- key

key字段表示MySQL查询优化器选择的索引类型。以下是key字段可能的取值及其含义：

- NULL：没有使用索引。
- PRIMARY：使用了表的主键索引。
- UNIQUE：使用了唯一索引。
- index_name：使用了名为index_name的普通索引。
- ref：使用了非唯一索引，但不是全表扫描，而是通过与另一个表的某个列进行关联查询。
- fulltext：使用了全文索引。
- spatial：使用了空间索引。
- range：使用了范围索引，例如使用了BETWEEN或IN等操作符。
- eq_ref：使用了连接查询中的等值连接索引，例如使用了JOIN ON语句中的WHERE条件。
- const：使用了常量索引，例如使用了主键或唯一索引的等值查询。
- system：使用了系统表的索引，例如MySQL内部的表。

需要注意的是，如果key字段为NULL，则表示查询没有使用索引，这可能会导致查询效率低下。因此，在实际应用中，我们应该尽可能地使用索引来优化查询。

-- key_len

key_len字段表示索引字段的长度，单位为字节。这个字段的值是根据索引中使用的数据类型和长度计算出来的。

具体来说，key_len的计算方式是：对于每个索引字段，如果是定长类型（如整型、日期等），则key_len的值就是该类型的长度；如果是变长类型（如字符串、文本等），则key_len的值就是该字段的最大长度。

例如，如果一个索引包含一个整型字段和一个长度为50的字符串字段，则key_len的值就是4+50=54。

在查询优化中，key_len字段的值可以用来判断索引的使用效率。一般来说，key_len越小，索引的效率就越高，因为MySQL可以使用更少的数据进行比较和排序。同时，key_len也可以用来判断是否需要创建更长的索引，以提高查询效率。

-- ref

ref字段表示连接使用的索引列或常数值，它是一个非唯一的索引，用于匹配来自前一个表的列值。ref列的值越少，查询性能越好。

具体来说，ref列的值可能有以下几种情况：
1. NULL：表示没有使用索引或常数来连接表。
2. const：表示使用常数来连接表，通常出现在连接类型为system或const的查询中。
3. eq_ref：表示使用唯一索引来连接表，对于每个索引键值，表中只有一条记录与之匹配。
4. ref：表示使用非唯一索引来连接表，对于每个索引键值，表中可能有多条记录与之匹配。
5. fulltext：表示使用全文索引来连接表。
6. ref_or_null：表示使用非唯一索引来连接表，但是索引列允许NULL值。
7. index_merge：表示使用多个索引的合并结果来连接表。

需要注意的是，ref列的值并不是越小越好，具体取决于查询的具体情况和表的结构。但是，通常情况下，ref列的值越小，查询性能越好。

-- rows

rows字段表示MySQL执行查询时估计需要扫描的行数。这个值是MySQL优化器根据表的统计信息和查询条件等因素估算出来的，不是实际扫描的行数。

rows字段的值越小，表示查询效率越高。如果查询的表中有索引，MySQL会利用索引来加速查询，从而减少扫描的行数，这样rows字段的值就会更小。

需要注意的是，rows字段只是一个估计值，实际扫描的行数可能会比这个值大或小。如果实际扫描的行数比估计值小很多，说明MySQL优化器的估计比较准确，查询效率比较高；如果实际扫描的行数比估计值大很多，说明MySQL优化器的估计不够准确，查询效率比较低。

总之，rows字段是Explain分析结果中一个非常重要的指标，可以帮助我们评估查询效率，优化查询语句。

-- filtered

filtered字段表示查询结果集中被过滤掉的行数占总行数的比例。该字段的值是一个0到1之间的小数，其中0表示过滤掉的行数占总行数的比例为0%，1表示过滤掉的行数占总行数的比例为100%。

filtered字段的值越小，表示查询结果集中需要检查的行数越少，查询效率越高。通常情况下，如果filtered字段的值小于10%，则表示该查询的效率比较高。

需要注意的是，filtered字段的值并不是一个精确的数值，而是一个估算值。它是根据MySQL对表的统计信息和查询条件进行估算得出的。因此，在某些情况下，filtered字段的值可能会与实际情况有所偏差。

-- Extra

extra字段是一个非常重要的字段，它提供了关于查询执行的额外信息。

下面是一些常见的extra字段的解释：
1. Using index：表示查询使用了覆盖索引，即查询只需要扫描索引而不需要访问表的数据行。
2. Using where：表示查询使用了WHERE子句中的条件进行过滤。
3. Using temporary：表示查询需要创建一个临时表来存储中间结果。
4. Using filesort：表示查询需要对结果集进行排序，但是无法使用索引完成排序，因此需要使用文件排序。
5. Using join buffer：表示查询使用了连接缓存，即MySQL会将连接过程中需要用到的数据缓存到内存中，以提高查询效率。
6. Impossible where：表示查询的WHERE子句中包含了不可能为真的条件，因此查询不会返回任何结果。
7. Select tables optimized away：表示查询可以通过优化直接返回结果，而不需要访问任何表。
8. Range checked for each record：表示查询使用了索引范围扫描，即MySQL会使用索引扫描一定范围内的数据行，然后再进行过滤。
9. Using index condition：表示查询使用了索引条件过滤，即MySQL会使用索引来过滤数据行，而不需要访问表的数据行。

总之，extra字段提供了关于查询执行的额外信息，可以帮助我们更好地理解查询的执行计划，从而优化查询性能。

3.3 EXPLAIN分析建议

1. 确认查询使用了索引：在EXPLAIN结果中，如果Extra列中出现了"Using where"或"Using temporary"，那么就意味着查询没有使用索引。可以通过添加索引或者优化查询语句来解决这个问题。
2. 减少全表扫描：如果查询没有使用索引，那么就会进行全表扫描，这会导致查询性能下降。可以通过添加索引或者优化查询语句来减少全表扫描。
3. 减少JOIN操作：JOIN操作会消耗大量的CPU和内存资源，因此应该尽量减少JOIN操作。可以通过使用子查询或者优化查询语句来减少JOIN操作。
4. 避免使用SELECT *：使用SELECT *会导致查询返回大量的数据，这会消耗大量的网络带宽和内存资源。应该尽量避免使用SELECT *，而是只选择需要的列。
5. 使用覆盖索引：覆盖索引是指查询只需要使用索引就可以返回结果，而不需要访问表的数据行。使用覆盖索引可以减少查询的IO操作，从而提高查询性能。
6. 使用LIMIT：使用LIMIT可以限制查询返回的结果集的大小，从而减少网络带宽和内存资源的消耗。

总之，通过使用EXPLAIN命令来分析查询语句的执行计划，并根据分析结果来优化查询语句和数据库结构，可以提高查询性能和数据库的整体性能。

4 索引使用场景

4.1 适合创建索引的场景

1. 频繁作为查询条件的字段：如果某个字段经常被用作查询条件，那么为该字段创建索引可以大大提高查询效率。
2. 数据具有唯一性的列，像学号、ID等特征字段；
3. 经常进行DISTINCT字段可以创建索引；
4. 多表进行JOIN时可以在JOIN的字段上创建索引，增加连表查询速度；
5. 经常需要排序的列：如果某个列经常需要排序，那么为该列创建索引可以大大提高排序的速度。
6. 经常需要进行分组、聚合操作的列：如果某个列经常需要进行分组、聚合操作，那么为该列创建索引可以大大提高这些操作的速度。
7. 使用字段类型小的适合创建索引，因为数据类型越小，物理存储中B+树的单个数据页节点存储的就会越多，扫描更快，数据类型大的字段可以采用前缀索引；
8. 区分度高的字段列适合创建索引：区分度高的列可以更快地缩小检索范围，提高查询效率；
    列的基数：单个列不重复值的个数；
    区分度计算：SELECT COUNT(DISTINCT(field))/COUNT(*) degree FROM table
    区分度越大，该字段建立索引的效果越好，联合索引将区分度高的字段放在左侧；
9. 使用最频繁的列放在联合索引的左侧；
10. 多个字段都需要创建索引的情况下，联合索引优于单值索引：联合索引是针对多个字段创建的索引，可以加速多个字段的查询操作。联合索引将多个字段组合在一起，形成一个索引，可以在一次查询中完成多个字段的匹配操作，从而提高查询效率。

4.2 不适合创建索引的场景

1. 数据表太小：如果数据表中的数据太少，那么创建索引的效果会非常有限，甚至可能会降低查询性能；
2. 不经常使用WHERE查询的字段；
3. 经常进行大量的数据更新操作：如果数据表中的数据经常被更新，那么创建索引会增加更新操作的时间和复杂度，因为每次更新都需要更新索引；
3. 数据表中的数据类型不适合创建索引：某些数据类型，如BLOB和TEXT类型，不适合创建索引，因为它们的数据量很大，索引会占用大量的磁盘空间。
4. 查询条件中使用了函数或表达式：如果查询条件中使用了函数或表达式，那么创建索引的效果会非常有限，因为MySQL无法使用索引来优化这些查询。
5. 数据表中的数据分布不均匀：如果数据表中的数据分布不均匀，那么创建索引的效果会非常有限，因为MySQL可能会选择不使用索引来优化查询。

5 索引失效

5.1 WHERE条件尽量进行全值匹配

如果我们在WHERE子句中使用全值匹配，MySQL可以直接使用索引定位到符合条件的数据行，而不需要扫描整个索引。这样可以大大减少索引扫描的数据量，提高查询效率。

5.2 联合索引最佳左前缀法则

创建索引时，散列度高的索引要放在最左边，这样使用联合索引时能够快速的定位更少的行，然后再查找后面索引的条件，增加查询效率；联合索引中不常出现在where条件中的列放在最右边，避免索引失效；

MySQL联合索引的索引失效可能有以下几种情况：

（1）索引列不在查询条件中：如果查询条件中没有使用到联合索引的第一列，那么该索引就会失效。例如，如果联合索引是（a,b,c），但是查询条件只使用了b和c列，那么该索引就会失效。
（2）索引列使用了函数或表达式：如果查询条件中使用了函数或表达式，那么该索引也会失效。例如，如果联合索引是（a,b,c），但是查询条件中使用了a+1，那么该索引就会失效。
（3）索引列使用了LIKE操作符：如果查询条件中使用了LIKE操作符，但是通配符在开头，那么该索引也会失效。例如，如果联合索引是（a,b,c），但是查询条件中使用了LIKE '%abc'，那么该索引就会失效。
（4）索引列使用了OR操作符：如果查询条件中使用了OR操作符，那么该索引也会失效。例如，如果联合索引是（a,b,c），但是查询条件中使用了a=1 OR b=2，那么该索引就会失效。
（5）索引列使用了NOT操作符：如果查询条件中使用了NOT操作符，那么该索引也会失效。例如，如果联合索引是（a,b,c），但是查询条件中使用了NOT a=1，那么该索引就会失效。
（6）索引列使用了IS NULL或IS NOT NULL操作符：如果查询条件中使用了IS NULL或IS NOT NULL操作符，那么该索引也会失效。例如，如果联合索引是（a,b,c），但是查询条件中使用了a IS NULL，那么该索引就会失效。
（7）索引列类型不匹配：如果查询条件中使用的数据类型与索引列的数据类型不匹配，那么该索引也会失效。例如，如果联合索引是（a,b,c），但是查询条件中使用了b='abc'，而b列的数据类型是整型，那么该索引就会失效。

5.3 主键尽量按顺序插入

但是，如果数据记录插入的位置比较分散，那么就会导致B+树的节点分散存储在磁盘上，这样就会增加磁盘碎片，降低查询性能。如果主键按顺序插入，那么新记录的位置很可能就在B+树的最后一个叶子节点，这样就可以减少磁盘碎片，提高查询性能。

5.4 索引列涉及计算、函数、类型转换时导致索引失效

计算操作：如果查询语句中的索引列需要进行计算操作，MySQL就无法使用索引来定位符合条件的数据行。例如，如果查询语句中包含“WHERE age + 1 = 20”这样的条件，MySQL就无法使用age列上的索引来定位符合条件的数据行。
函数操作：如果查询语句中的索引列需要进行函数操作，MySQL也无法使用索引来定位符合条件的数据行。例如，如果查询语句中包含“WHERE UPPER(name) = 'JOHN'”这样的条件，MySQL就无法使用name列上的索引来定位符合条件的数据行。
类型转换：如果查询语句中的索引列需要进行类型转换，MySQL也无法使用索引来定位符合条件的数据行。例如，如果查询语句中包含“WHERE age = '20'”这样的条件，MySQL就无法使用age列上的索引来定位符合条件的数据行，因为MySQL需要将字符串'20'转换为整数20才能进行比较。

5.5 联合索引使用中，范围条件右边的列会导致索引失效

MySQL联合索引使用中，范围条件右边的列会导致索引失效，主要是因为B+树索引的数据结构特性所致。
B+树索引是一种多叉树结构，每个节点可以有多个子节点，而且子节点之间是有序的。在MySQL中，联合索引的B+树结构是按照联合索引的顺序建立的，也就是说，联合索引的第一个字段是主键，第二个字段是次要关键字，以此类推。
当使用范围条件时，MySQL会从B+树的根节点开始遍历，找到第一个符合条件的节点，然后遍历该节点下的所有子节点，直到找到所有符合条件的记录。但是，由于B+树的有序性，右边的列的值是无序的，因此MySQL无法利用索引的有序性进行优化，只能遍历整个B+树，导致索引失效。
为了避免这种情况，可以考虑将范围条件放在联合索引的左边，或者将范围条件拆分成多个等值条件，分别对应联合索引的不同列。这样MySQL就可以利用索引的有序性进行优化，提高查询效率。

5.6 索引列进行不等于（!=）判断时索引会失效

这是因为MySQL在执行不等于操作时，需要扫描整个索引，而不是只扫描部分索引。这是因为索引是按照顺序排列的，而不等于操作需要跳过一些值，因此需要扫描整个索引。

5.7 索引列判断IS NULL可以使用索引，IS NOT NULL无法使用索引

当查询条件中包含IS NULL时，MySQL可以直接使用B-Tree索引中的NULL值列表，因为NULL值是可以被索引的。但是，当查询条件中包含IS NOT NULL时，MySQL无法使用B-Tree索引中的NULL值列表，因为B-Tree索引中不包含非NULL值的列表，因此无法加速查询。
另外，当查询条件中包含IS NULL时，MySQL可以使用覆盖索引来避免回表操作，因为B-Tree索引中已经包含了需要查询的列。但是，当查询条件中包含IS NOT NULL时，MySQL无法使用覆盖索引，因为B-Tree索引中不包含非NULL值的列表，因此需要回表操作来获取需要查询的列。

5.8 LIKE模糊匹配，以通配符%开头会导致索引失效

MySQL索引是基于B-Tree数据结构实现的，它是一种有序的数据结构，可以快速定位到某个值。当使用LIKE模糊匹配时，如果通配符%出现在开头，MySQL就无法使用索引进行优化查询，因为它无法确定要匹配的值的范围，只能逐个比较每个记录，这样会导致查询效率非常低下。

5.9 索引列OR连接非索引列，会导致索引失效

因为OR连接非索引列会导致MySQL无法确定哪些行符合查询条件，因此MySQL会放弃使用索引，而采用全表扫描的方式来查询数据。