面试题：MySQL性能调优工具之EXPLAIN的深度应用

1. EXPLAIN命令各输出字段含义

id：
- 表示查询中执行select子句或操作表的顺序。
- 相同id值，执行顺序由上至下；不同id值，id值越大越先执行。
select_type：
- SIMPLE：简单查询，不包含子查询或union。
- PRIMARY：最外层的查询。
- SUBQUERY：子查询中的第一个select。
- DERIVED：在from子句中包含的子查询，会被物化。
- UNION：union中第二个及以后的select。
- UNION RESULT：union操作的结果。
table：
- 显示这一行的数据是关于哪张表的。
partitions：
- 显示查询将匹配的分区。如果查询不涉及分区表，该值为NULL。
type：
- ALL：全表扫描，性能最差。
- index：索引全扫描，扫描整个索引树。
- range：范围扫描，通常出现在where语句中有between、<、>、in等操作时。
- ref：使用非唯一索引进行单表查询，通过索引找到匹配的行。
- eq_ref：对于前表的每一个行组合，从该表中读取一行。常用于多表join中使用主键或唯一键的情况。
- const：通过一次索引查找就能找到数据，通常出现在将主键或唯一索引与常量值比较时。
- system：表只有一行记录（系统表），这是const类型的特例。
possible_keys：
- 显示可能应用在这张表上的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
key：
- 实际使用的索引。如果为NULL，则没有使用索引。
key_len：
- 表示索引中使用的字节数，可通过该值评估索引使用情况。值越短越好。
ref：
- 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
rows：
- MySQL估计为了找到所需的行而要读取的行数，值越小越好。
filtered：
- 表示存储引擎返回的数据在server层过滤后，剩下满足查询的记录数量的比例。
Extra：
- Using index：表示使用了覆盖索引，查询所需的数据只需要在索引中就可以获取，无需回表。
- Using where：表示MySQL服务器将在存储引擎检索行后再进行过滤。
- Using temporary：表示MySQL需要创建临时表来处理查询，常见于排序和分组操作。
- Using filesort：表示MySQL无法利用索引完成排序操作，需要进行文件排序。

2. 结合复杂多表查询语句利用EXPLAIN分析性能优化

假设有以下三张表：

CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `order_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
);

CREATE TABLE `order_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`)
);

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
);

复杂查询语句：

EXPLAIN SELECT p.product_name, SUM(oi.quantity), o.order_date
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.user_id = 1
GROUP BY p.product_name, o.order_date;

性能分析及优化：
- type字段：如果orders表的type为ALL，说明全表扫描，可通过在user_id字段上添加索引来优化，当前已添加idx_user_id索引。若order_items表type为ALL，可考虑在order_id字段添加索引，当前已添加idx_order_id索引。
- Extra字段：若出现Using temporary和Using filesort，可尝试优化查询，例如调整GROUP BY字段顺序，或者添加合适的复合索引。
- key字段：确保使用了预期的索引。若key为NULL，需要检查查询条件和索引设置是否合理，比如条件字段是否有索引，或者是否由于数据类型不匹配导致索引失效。
- rows字段：若rows值过大，说明扫描的行数过多，可进一步优化索引，使查询能够更精准地定位数据。

星途面试题库

面试题：MySQL性能调优工具之EXPLAIN的深度应用

知识考点

面试题答案

1. EXPLAIN命令各输出字段含义

2. 结合复杂多表查询语句利用EXPLAIN分析性能优化