要分析mysql索引使用和执行计划，核心是通过explain命令查看查询路径，并结合handler_read%状态变量评估索引效率。1. 使用explain命令分析执行计划，关注type、key、extra等列，判断是否高效利用索引；2. 通过show global status like ‘handler_read%’监控索引读操作，评估整体索引健康状况；3. 利用sys schema视图如schema_table_statistics_with_buffer和statements_with_full_table_scans辅助分析索引使用情况；4. explain中type列从all（最差）到const/system（最优）反映访问效率，应尽量避免all和index，追求range、ref、eq_ref或const；5. extra列出现using filesort或using temporary时需优化排序或group by逻辑，如创建复合索引或重构查询；6. 即使使用索引但性能不佳时，需检查key_len是否完整利用索引前缀，以及rows是否因低选择性导致大量扫描；7. 定期运行analyze table更新统计信息，确保优化器做出正确决策。
mysql中分析索引使用和解读执行计划，核心在于利用EXPLAIN命令查看查询的执行路径，并通过监控Handler_read%状态变量来评估索引的整体效率。这就像是给你的sql语句做一次深度体检，看看它到底有没有“走正道”，还是在“瞎转悠”。
解决方案
要深入理解MySQL如何利用你的索引，并据此优化查询性能，你需要掌握以下几个关键工具和方法：
首先，也是最直接、最常用的，是EXPLAIN命令。在任何select语句前加上EXPLAIN，MySQL就会返回一个执行计划，告诉你它打算如何执行这条查询。这个计划包含了丰富的信息，比如查询会访问哪些表、使用哪个索引、扫描多少行数据，以及是否有额外的操作（如排序或创建临时表）。
例如： EXPLAIN SELECT * FROM products WHERE category_id = 10 AND price > 100 ORDER BY created_at DESC;
解读EXPLAIN的输出是门学问，你需要关注id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra等列。其中，type列是判断索引使用效率的关键，它告诉你MySQL访问数据的方式；key列显示实际使用的索引；而Extra列则揭示了许多幕后操作，比如是否使用了覆盖索引、是否需要文件排序或临时表。
其次，你可以通过观察MySQL的全局状态变量来了解索引的宏观使用情况。SHOW GLOBAL STATUS LIKE ‘Handler_read%’; 这个命令能帮你看到数据库层面索引的读操作。Handler_read_key表示通过索引键读取行的次数，这个值越高通常意味着索引被有效利用；而Handler_read_rnd_next则表示按数据文件顺序读取下一行的次数，如果这个值很高，可能意味着存在大量全表扫描或者索引未能有效避免随机读取。通过周期性地观察这些值，你可以对数据库的整体索引健康状况有个大致的判断。
最后，对于MySQL 5.7及更高版本，sys schema提供了更友好的视图来分析性能问题，包括索引使用情况。例如，sys.schema_table_statistics_with_buffer可以提供表和索引的详细统计信息，而sys.statements_with_full_table_scans则能直接找出那些正在执行全表扫描的语句，这对于发现潜在的索引优化点非常有帮助。
EXPLAIN输出中的”type”列究竟意味着什么，如何判断索引是否高效？
EXPLAIN输出里的type列，在我看来，是理解查询性能的“晴雨表”。它直接告诉你MySQL是如何从表中获取数据的，效率高低一目了然。
从最差到最优，type列通常会显示以下几种值：
- ALL: 这意味着全表扫描。MySQL会遍历表中的每一行来找到匹配的数据。这是最糟糕的情况，尤其对于大表来说，性能会非常差。你得想尽办法避免它。
- index: 全索引扫描。MySQL遍历整个索引来查找数据。虽然比ALL好，因为它避免了直接访问数据行，但如果索引很大，性能依然堪忧。不过，如果你的查询只需要索引中的列（即所谓的“覆盖索引”），那index类型也是可以接受的，因为MySQL不需要回表取数据。
- range: 范围扫描。这表示MySQL利用索引来检索给定范围内的行，比如WHERE id BETWEEN 10 AND 20或WHERE created_at > ‘2023-01-01’。这是一个很常见的、效率不错的类型。
- ref: 非唯一索引查找。当使用非唯一索引或唯一索引的非前缀部分进行等值查找时，会出现这种类型。例如，在一个非唯一的用户名字段上查找WHERE username = ‘john’。效率很高。
- eq_ref: 唯一索引查找。通常在连接操作中，当一个表使用主键或唯一索引与另一个表进行等值连接时出现。例如，ON users.id = orders.user_id，且users.id是主键。这是非常高效的类型。
- const / system: 这两种是最高效的类型。const表示MySQL能将查询优化成一个常量，例如WHERE id = 1，并且id是主键或唯一索引。system是const的特例，当表只有一行数据时出现。
判断索引是否高效，主要就是看type列。目标是让type尽可能地从ALL向range、ref、eq_ref甚至const靠拢。如果你的查询结果是ALL，那么毫无疑问，你需要考虑添加或优化索引。如果是index，则要看是否是覆盖索引，如果不是，可能还需要进一步优化。
举个例子： EXPLAIN SELECT * FROM users WHERE status = ‘active’; 如果status列没有索引，type可能是ALL。 如果给status列加上索引，type可能变成ref或range，性能会大幅提升。
当Extra列出现”Using filesort”或”Using temporary”时，我该怎么办？
当EXPLAIN输出的Extra列出现Using filesort或Using temporary，这通常是性能瓶颈的信号，意味着MySQL在后台做了额外的工作，而且这些工作往往是耗费资源的操作。
Using filesort： 这表示MySQL需要对结果集进行排序，但没有找到一个合适的索引来完成这个排序，所以它必须在内存中（如果结果集小）或磁盘上（如果结果集大）进行文件排序。这非常消耗CPU和I/O资源。 如何应对？ 你的首要任务是尝试创建复合索引来“覆盖”ORDER BY或GROUP BY子句。 比如，你有一个查询：SELECT name, age FROM users WHERE city = ‘Beijing’ ORDER BY age DESC; 如果city列有索引，但age没有，或者city和age没有组合索引，就可能出现Using filesort。 这时候，你可以考虑创建一个复合索引：ALTER TABLE users ADD INDEX idx_city_age (city, age);注意索引的顺序：如果你的WHERE条件和ORDER BY条件是组合的，索引的列顺序至关重要。通常，WHERE条件中的等值匹配列放在前面，然后是范围匹配列，最后是ORDER BY或GROUP BY的列。
Using temporary： 这表示MySQL需要创建一个内部的临时表来处理查询。这通常发生在GROUP BY、DISTINCT、union或某些复杂的子查询中，当MySQL无法通过索引直接得到结果时。临时表可能在内存中，也可能因为数据量大而写入磁盘，后者会严重影响性能。 如何应对？ 解决Using temporary比解决Using filesort可能更复杂一些。
- 优化GROUP BY：如果GROUP BY的列没有索引，或者索引不能完全覆盖，就容易产生临时表。尝试为GROUP BY的列添加索引。
- 优化DISTINCT：DISTINCT操作也可能导致临时表。如果DISTINCT的列可以被某个索引完全覆盖（即构成覆盖索引），那么Using temporary可能会消失。
- 重构查询：有时候，通过重写SQL语句，比如使用JOIN代替子查询，或者拆分复杂查询，可以避免临时表的产生。
- 调整MySQL配置：虽然不是根本解决方案，但适当增加tmp_table_size和max_heap_table_size可以允许更多临时表在内存中创建，减少磁盘I/O，但治标不治本。
看到这两个Extra信息，别犹豫，这基本就是告诉你：“你的查询在做额外功，快来优化我！”
为什么我的查询明明使用了索引，但性能依然不佳？key_len和rows有什么玄机？
你可能遇到过这样的情况：EXPLAIN显示你的查询确实使用了索引（type不是ALL，key列也有值），但实际执行起来还是慢得让人抓狂。这背后往往藏着key_len和rows这两个列的“玄机”，以及一些你可能忽略的细节。
key_len的“部分使用”陷阱： key_len表示MySQL在查询中实际使用了索引的多少字节。如果你有一个复合索引(col1, col2, col3)，key_len能告诉你MySQL用了这个索引的哪个前缀。 比如，如果你的查询是WHERE col2 = ‘X’，即使possible_keys里有(col1, col2, col3)，key_len可能只显示了col2部分的长度（如果col1没有在WHERE条件中），这说明索引只被部分利用了。更糟的是，如果查询条件是WHERE col3 = ‘Y’，那这个复合索引可能根本用不上，因为索引遵循“最左前缀原则”。 玄机在于：key_len小，可能意味着你的索引没有被充分利用，或者你的查询条件没有按照索引的正确顺序来写。你可能需要调整索引的列顺序，或者修改查询语句，确保最左前缀被有效使用。
rows的“不选择性”困境： rows列是MySQL估计的为了找到所需行而需要扫描的行数。即使type是range或ref，如果rows的值非常大，那性能依然会很差。 玄机在于：索引的“选择性”问题。一个索引虽然被使用了，但如果它对应的列值分布非常不均匀，导致索引返回了表中很大一部分数据（比如，在一个性别字段上建立索引，WHERE gender = ‘male’可能返回一半的数据），那么MySQL会发现即使走了索引，也得读取大量数据行，甚至可能判断走全表扫描反而更快。这种情况下，索引的优势就不明显了。 如何应对？
- 检查索引选择性：对于低选择性的列（即区分度不高的列，比如性别、状态码等），即使有索引，效果也有限。你需要评估这个索引是否真的有价值，或者是否需要与其他高选择性的列组合成复合索引。
- ANALYZE TABLE更新统计信息：MySQL的查询优化器依赖于表的统计信息来决定是否使用索引以及如何使用。如果这些统计信息过时了，优化器可能会做出错误的决策。定期运行ANALYZE TABLE your_table_name;可以更新这些统计信息，帮助MySQL做出更明智的执行计划。
- 覆盖索引的考量：如果你的查询只涉及到索引中的列，并且Extra列显示Using index（即使用了覆盖索引），那么即使rows稍大，性能也可能不错，因为MySQL不需要回表去取数据行。但如果不是覆盖索引，那么每次读取索引行后还需要进行一次回表操作，这个I/O成本会很高。
总的来说，一个索引是否真的“好用”，不仅要看它是否被EXPLAIN选中，更要看它被利用的程度（key_len），以及它能帮助过滤掉多少数据（rows的绝对值和相对比例）。这是一个综合判断的过程，需要你对数据分布和查询模式有深入的理解。
