一、背景

最近在准备面试，重新梳理了 MySQL 索引相关的知识。结合实习中遇到的一个真实慢查询，写一篇实战总结。

场景：数据地图项目中的资产查询，11 万条数据，分页查询要 1.2 秒。

SELECT * FROM go_catalog_service 
WHERE en_name = 'xxx' AND status = 1 
ORDER BY create_time DESC 
LIMIT 20;

优化后降到 10 毫秒，提升 120 倍。

大家可以按照以下代码，自行建表插入数据测试

CREATE DATABASE face_test;
-- 1、建表
CREATE TABLE `go_catalog_service` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `en_name` VARCHAR(100) NOT NULL COMMENT '资产英文名',
  `status` TINYINT NOT NULL COMMENT '状态 1-上线 2-下线',
  `create_time` DATETIME NOT NULL,
  `data_source` VARCHAR(50),
  `owner` VARCHAR(50),
  `description` TEXT,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

-- 2、用存储过程插入 11 万条测试数据
DELIMITER $$
CREATE PROCEDURE insert_test_data()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 110000 DO
    INSERT INTO go_catalog_service (en_name, STATUS, create_time, data_source, OWNER)
    VALUES (
      CONCAT('asset_', FLOOR(1 + RAND() * 1000)),  -- 1000 种不同的 en_name
      FLOOR(1 + RAND() * 2),                        -- 状态 1 或 2
      DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY),  -- 随机日期
      CONCAT('source_', FLOOR(1 + RAND() * 10)),
      CONCAT('user_', FLOOR(1 + RAND() * 100))
    );
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

CALL insert_test_data();

二、联合索引的最左前缀原则

2.1 什么是联合索引？

联合索引 (a, b, c) 在 B+Tree 中的排序规则：

• 先按 a 排序

• a 相同再按 b 排序

• b 相同再按 c 排序

2.2 最左前缀原则

只有命中索引的最左列，才能用上索引。

查询	能否用索引	原因
WHERE a = 1	能用	命中最左列
WHERE a = 1 AND b = 2	能用	命中 a, b
WHERE a = 1 AND c = 3	部分用到	只有 a 用索引，c 用不到（跳过 b）
WHERE b = 2	不能	没命中最左列 a

2.3 范围查询的“分水岭”

遇到第一个范围查询（>、<、IN、BETWEEN），后面的列就失效了。

-- a 等值，b 范围 → c 用不到索引
WHERE a = 1 AND b > 2 AND c = 3

2.4 ORDER BY 能用索引排序吗？

条件：ORDER BY 前面的列必须全是等值。

查询	能否用索引排序	原因
WHERE a = 1 ORDER BY b	能	a 等值，b 在索引中有序
WHERE a = 1 ORDER BY c	不能	跳过 b，c 不连续
WHERE a = 1 AND b = 2 ORDER BY c	能	a、b 等值，c 有序
WHERE a = 1 AND b > 2 ORDER BY c	不能	b 是范围，c 不连续

三、索引失效的常见场景

场景	示例	原因
函数操作	WHERE DATE(create_time) = '2024-01-01'	对索引列用了函数
隐式类型转换	WHERE phone = 13800138000（phone 是 varchar）	类型不匹配
左模糊查询	WHERE name LIKE '%张三'	不知道开头是什么
区分度太低	status 只有 0 和 1，查 status=1 占 80%	优化器选全表扫描

区分度低的例子：

-- status 只有 0 和 1，status=1 占全表 80%
-- 优化器认为全表扫描比回表更快
-- 全表扫描是顺序IO，回表扫描是随机IO
SELECT * FROM orders WHERE status = 1;

四、慢查询定位与排查

4.1 开启慢查询日志

-- 临时开启
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;  -- 超过 2 秒记录

-- 查看日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

4.2 分析慢日志

# 按时间排序，取前 10 条
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

4.3 临时定位（没开日志时）

-- 查看当前执行超过 10 秒的 SQL
SELECT id, user, host, db, command, time, state, info 
FROM information_schema.PROCESSLIST 
WHERE command != 'Sleep' AND time > 10
ORDER BY time DESC;

4.4 用 EXPLAIN 分析

EXPLAIN SELECT * FROM go_catalog_service 
WHERE en_name = 'asset_500' AND status = 1 
ORDER BY create_time DESC;

重点看：

• type：ALL（全表扫描）→ 有问题；ref/range → 正常

• key：NULL → 没走索引

• Extra：Using filesort（额外排序）、Using temporary（临时表）→ 需要优化

type 从好到差：

system > const > eq_ref > ref > range > index > ALL

五、实战优化案例

5.1 优化前

-- 11 万数据，耗时 1.2 秒
EXPLAIN SELECT * FROM go_catalog_service 
WHERE en_name = 'asset_500' AND status = 1 
ORDER BY create_time DESC;

type	key	rows	Extra
ALL	null	110000	Using where; Using filesort

5.2 建联合索引

CREATE INDEX idx_en_name_status_time ON go_catalog_service(en_name, status, create_time);

5.3 优化后

type	key	rows	Extra
ref	idx_en_name_status_time	~50	Backward index scan

• rows 从 11 万降到 50

• Using filesort 消失

• 耗时降到 10 毫秒

六、总结

知识点	要点
最左前缀原则	跳过列 → 后面列失效；范围查询 → 后面列失效
索引排序	ORDER BY 前面的列必须全是等值
索引失效场景	函数、隐式转换、左模糊、区分度低
慢查询定位	开启日志 → mysqldumpslow → EXPLAIN
type 排序	ref > range > index > ALL
回表 vs 全表扫描	区分度低时，全表扫描（顺序 I/O）可能比回表（随机 I/O）更快

---

本文是自己的学习笔记，如有错误欢迎指正。