MySQL索引优化与慢查询排查

MySQL索引核心原理与8.0新特性

咱们做后端的都知道，MySQL索引要是没设计好，线上接口分分钟给你卡成PPT。简单来说，索引就是数据库的“目录”，没目录你查一本书得翻遍所有页，有目录直接定位到具体章节。MySQL里最常用的还是B+树索引，这玩意儿从早期版本到现在都是核心，哪怕到了MySQL 8.0.36（2024年1月刚发的LTS版本），B+树依然是默认索引结构，毕竟它平衡了查询、插入、范围扫描的效率，比哈希索引适合更多场景。

B+树的结构你得心里有数：非叶子节点只存索引键和子节点指针，叶子节点才存真实数据（或者主键ID），而且叶子节点之间用双向链表连起来。这就解释了为啥范围查询（比如WHERE id > 10 AND id < 100）用B+树特别快，顺着链表扫就行，不用回溯上层节点。哈希索引虽然等值查询快，但范围查询直接歇菜，所以MySQL只有Memory引擎默认用哈希，InnoDB的自适应哈希索引（AHI）是后台自动给热点数据建的，不用你手动管，8.0版本里AHI的优化还在持续做，对高频访问的小范围数据提速明显。

再聊聊8.0里几个实用的新特性，第一个是函数索引（Functional Indexes）。以前咱们写查询要是用函数包裹字段，比如WHERE YEAR(create_time) = 2024，索引直接失效，因为索引存的是原始值，你套了函数之后MySQL算不出来匹配的值。8.0支持直接给表达式建索引了，比如给YEAR(create_time)建索引，查询的时候就能命中。来看个实际例子，先建个订单表：

-- 创建订单表，模拟电商场景
CREATE TABLE `order_info` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `order_status` tinyint NOT NULL COMMENT '订单状态：1待支付 2已支付 3已取消',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `amount` decimal(10,2) NOT NULL COMMENT '订单金额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- 插入100万条测试数据，这里用存储过程生成，实际测试可以直接跑
DELIMITER //
CREATE PROCEDURE insert_test_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 1000000 DO
    INSERT INTO order_info (user_id, order_status, create_time, amount)
    VALUES (
      FLOOR(RAND() * 10000),  -- 随机用户ID
      FLOOR(RAND() * 3) + 1,  -- 随机状态
      DATE_ADD('2024-01-01', INTERVAL FLOOR(RAND() * 365) DAY),  -- 2024年随机时间
      ROUND(RAND() * 1000, 2)  -- 随机金额
    );
    SET i = i + 1;
  END WHILE;
END //
DELIMITER ;

-- 执行存储过程生成数据，数据量够大才能看出索引效果
CALL insert_test_data();

现在要查2024年创建的订单，之前的写法是SELECT * FROM order_info WHERE YEAR(create_time) = 2024，没函数索引的话，哪怕给create_time建了普通索引也用不上。现在8.0里直接建函数索引：

-- 给YEAR(create_time)建函数索引
CREATE INDEX idx_func_year_create_time ON order_info ((YEAR(create_time)));

建完之后再查同样的语句，EXPLAIN就能看到命中这个索引了，查询速度能快几十倍。打个比方，函数索引就是把表达式的计算结果存到索引里，查询的时候直接匹配结果，不用每次都算一遍。

第二个要提的是不可见索引（Invisible Indexes），这个特性简直是索引灰度验证的神器。以前你想删一个索引，又怕影响线上查询，只能先停掉业务？或者建个新索引，又怕写入性能下降？现在直接把索引设为不可见，优化器就不会用它，但索引本身还在后台维护。比如你觉得idx_user_id这个索引没用了，先把它改成不可见：

-- 把用户ID索引设为不可见，优化器不会选择它，但索引依然保持更新
ALTER TABLE order_info ALTER INDEX idx_user_id INVISIBLE;

观察个两三天，要是没慢查询报警，再删也不迟。要是发现某个查询变慢了，说明这个索引还有用，直接改回来：

-- 发现查询变慢，把索引改回可见
ALTER TABLE order_info ALTER INDEX idx_user_id VISIBLE;

注意，不可见索引不是删除，只是优化器忽略它，所以写入的时候还是会更新这个索引，不会丢数据，比直接删了再建安全多了。8.0.36里这个特性的稳定性已经很高了，线上放心用。

还有个降序索引（Descending Indexes），以前我们ORDER BY create_time DESC的时候，如果索引是升序的，MySQL还得反向扫描，现在可以直接建降序索引。比如经常要查最新的订单，直接建(user_id ASC, create_time DESC)的组合索引，查询的时候不用额外排序：

-- 建用户ID升序、创建时间降序的组合索引，优化按用户查最新订单的场景
CREATE INDEX idx_user_id_create_time_desc ON order_info (user_id ASC, create_time DESC);

现在查某个用户的最新10条订单：SELECT * FROM order_info WHERE user_id = 123 ORDER BY create_time DESC LIMIT 10，执行计划里就不会出现Using filesort了，速度提升非常明显。

📖 学习建议

别一上来就给所有字段建索引，索引不是越多越好。写入的时候每次INSERT、UPDATE、DELETE都要更新索引，索引太多会拖慢写入速度。一般单表索引数量控制在5-7个以内，优先给查询条件、JOIN字段、ORDER BY、GROUP BY的字段建索引。尤其是8.0的函数索引，别滥用，只有确定某个表达式查询频率很高的时候再建，不然反而增加维护成本。

---

慢查询定位实战：从日志配置到Performance Schema对比

线上接口突然变慢，第一反应肯定是查慢查询。很多新手只知道看慢查询日志，其实MySQL 8.0里的Performance Schema比慢查询日志更灵活，能实时抓到正在执行的慢语句，不用等日志刷新。咱们先讲最基础的慢查询日志配置，这个是基本功，所有版本通用，8.0.36里配置方式也没变。

首先得开慢查询日志，默认是关的。你可以临时开，也可以写配置文件永久生效。临时开的话直接执行SQL：

-- 查看慢查询日志是否开启，默认是OFF
SHOW VARIABLES LIKE 'slow_query_log';

-- 开启慢查询日志，1表示开启，0表示关闭
SET GLOBAL slow_query_log = 1;

-- 设置慢查询阈值，单位是秒，这里设为0.1秒，也就是执行超过100ms的语句会被记录
-- 8.0里支持微秒级，比如设为100就是100毫秒，设为0.001就是1毫秒
SET GLOBAL long_query_time = 0.1;

-- 查看慢查询日志文件路径，默认在数据目录下，比如/var/lib/mysql/xxx-slow.log
SHOW VARIABLES LIKE 'slow_query_log_file';

要是想永久生效，得改MySQL配置文件my.cnf（Linux）或者my.ini（Windows），在[mysqld]下面加这几行：

[mysqld]
# 开启慢查询日志
slow_query_log = 1
# 慢查询阈值，单位秒，这里设为0.1秒
long_query_time = 0.1
# 日志文件路径，按需修改
slow_query_log_file = /var/lib/mysql/slow-query.log
# 记录未使用索引的查询，这个很有用，能抓到索引失效的语句
log_queries_not_using_indexes = 1

改完重启MySQL就生效了。现在你执行一个耗时超过0.1秒的查询，比如SELECT * FROM order_info WHERE amount > 500（没建索引的话肯定会慢），就会被记到慢查询日志里。日志内容大概长这样：

# Time: 2024-05-20T10:30:00.123456Z
# User@Host: root[root] @ localhost []  Id: 123
# Query_time: 2.345  Lock_time: 0.001  Rows_sent: 1000  Rows_examined: 1000000
SET timestamp=1716198600;
SELECT * FROM order_info WHERE amount > 500;

这里的Query_time是实际执行时间，Rows_examined是扫描的行数，Rows_sent是返回的行数。如果Rows_examined远大于Rows_sent，说明索引没用上，扫描了全表。

但是慢查询日志有个问题：它是事后记录的，你要等语句执行完才会写进去，而且默认是累积写的，要分析的话得用mysqldumpslow工具，比如查执行时间最长的10条慢查询：

# 分析慢查询日志，按执行时间排序，取前10条
mysqldumpslow -s t -t 10 /var/lib/mysql/slow-query.log

换个角度看，慢查询日志适合离线分析历史问题，要是你想实时抓正在跑的慢查询，就得用Performance Schema。这个是MySQL内置的性能监控工具，8.0里默认是开启的，比慢查询日志更细粒度。比如你想抓当前执行超过0.1秒的语句，直接用下面的查询：

-- 从Performance Schema的语句事件表里查执行时间超过100毫秒的语句
-- 注意：statement_events表默认只保留最近的记录，需要的话可以调整performance_schema的参数
SELECT 
  DIGEST_TEXT AS `查询语句`,
  COUNT_STAR AS `执行次数`,
  AVG_TIMER_WAIT/1000000000 AS `平均执行时间(秒)`,
  MAX_TIMER_WAIT/1000000000 AS `最大执行时间(秒)`,
  SUM_ROWS_EXAMINED AS `总扫描行数`,
  SUM_ROWS_SENT AS `总返回行数`
FROM performance_schema.events_statements_summary_by_digest
WHERE AVG_TIMER_WAIT/1000000000 > 0.1  -- 平均执行时间超过0.1秒
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

这个查询能直接拿到最近执行过的慢语句的摘要，包括执行次数、平均时间、扫描行数，比慢查询日志实时多了。而且Performance Schema还能监控锁等待、IO情况，比如某个语句被锁了多久，都能查到。

现在对比下两者的优缺点：

慢查询日志：适合长期保存慢查询记录，事后分析，配置简单，但是实时性差，需要工具解析。
Performance Schema：实时监控，细粒度性能数据，能抓到正在执行的语句，但是配置复杂，数据默认是内存里的，重启就丢了。

实际工作中我一般两个结合用：慢查询日志开个0.1秒的阈值，长期保存，用来复盘历史慢查询；Performance Schema用来实时排查线上突然出现的慢查询，比如某个接口突然RT升高，直接查events_statements_summary_by_digest就能找到对应的慢语句。

💡 经验总结

慢查询阈值不要设得太低，比如设成0.001秒（1毫秒），会让日志量爆炸，磁盘很快被占满。一般线上业务设为0.1秒（100毫秒）就够了，如果是核心支付接口，可以设成0.05秒。另外，记得定期清理慢查询日志，或者配置日志轮转，避免磁盘满。如果用Performance Schema，记得不要开太多采集项，不然会额外消耗CPU和内存，8.0里默认的配置已经比较合理，不用随便改。

---

EXPLAIN与EXPLAIN ANALYZE：读懂执行计划与成本分析

拿到慢查询之后，下一步就是看执行计划，也就是MySQL打算怎么执行这条语句。最基础的是EXPLAIN，8.0里新增了EXPLAIN ANALYZE，能实际执行语句，给出真实的执行时间和行数，比普通EXPLAIN准多了。咱们先讲普通EXPLAIN怎么看，再讲ANALYZE的升级用法。

先拿之前的订单表举个例子，比如查用户ID为123，状态为2的订单：

-- 先给order_info建个联合索引，方便演示
CREATE INDEX idx_user_status_create ON order_info (user_id, order_status, create_time);

-- 用EXPLAIN看执行计划
EXPLAIN SELECT * FROM order_info WHERE user_id = 123 AND order_status = 2 ORDER BY create_time DESC LIMIT 10;

执行之后会返回一张表，每个字段都有用，我给你挨个说：

id：查询的序列号，如果有子查询，id越大越先执行。
select_type：查询类型，比如SIMPLE是简单查询，PRIMARY是主查询，SUBQUERY是子查询。
table：当前查询的表名。
partitions：匹配的分区，没分区的话是NULL。
type：访问类型，这个最关键，从好到坏大概是：system > const > eq_ref > ref > range > index > ALL。咱们写SQL尽量要到range以上，ALL是全表扫描，绝对要避免。
possible_keys：可能用到的索引。
key：实际用到的索引，如果是NULL，说明没用到索引。
key_len：索引使用的长度，比如联合索引(user_id, order_status)，如果只用了user_id，key_len就是user_id的长度，用了两个就是两者之和，能判断索引有没有完全命中。
ref：哪些列或者常量被用来和key比较。
rows：MySQL估计要扫描的行数，越小越好。
filtered：过滤比例，比如100%就是扫描的行都符合条件，10%就是只有10%符合。
Extra：额外信息，比如Using index是覆盖索引，Using filesort是额外排序，Using temporary是用临时表，这些都是优化点。

上面的查询如果用到了idx_user_status_create索引，type应该是ref，key是idx_user_status_create，rows会很小，Extra里可能有Using index condition或者Using filesort（如果索引没包含排序字段的话）。

但是普通EXPLAIN有个问题：它是基于统计信息的估算，不是实际执行的。比如rows是估计值，可能和实际扫描的行数差很多。这时候就需要EXPLAIN ANALYZE了，这个是MySQL 8.0.18之后才有的功能，8.0.36里已经很稳定了，它会实际执行语句，返回真实的执行时间、行数、成本。用法很简单，在EXPLAIN后面加ANALYZE就行：

-- 实际执行查询，返回真实执行计划
EXPLAIN ANALYZE SELECT * FROM order_info WHERE user_id = 123 AND order_status = 2 ORDER BY create_time DESC LIMIT 10;

返回的结果不是表格了，是树形结构，每个节点都有真实数据：

-> Limit: 10 row(s)  (cost=10.25 rows=10) (actual time=0.345..0.356 rows=10 loops=1)
    -> Index lookup on order_info using idx_user_status_create (user_id=123, order_status=2)  (cost=10.25 rows=10) (actual time=0.343..0.354 rows=10 loops=1)

你看，这里既有估算的cost和rows，也有实际的actual time（执行时间，单位是毫秒）和rows（实际返回的行数）。如果估算的rows是100，实际是1000，说明统计信息不准了，需要更新统计信息：

-- 更新表的统计信息，让执行计划更准确
ANALYZE TABLE order_info;

还有个常见的误区：很多人看EXPLAIN只看key是不是NULL，其实type和Extra更重要。比如type是index，虽然用了索引，但是扫描了整个索引树，和全表扫描差不多，比如SELECT id FROM order_info，如果id是主键，type就是index，因为扫描整个主键索引，虽然比全表扫描快一点，但还是不够好。如果Extra里有Using filesort，说明ORDER BY的字段没用到索引，需要额外排序，比如你建了(user_id, order_status)的索引，但是ORDER BY create_time，就会触发filesort，这时候就需要把create_time加到索引里，变成(user_id, order_status, create_time)。

再举个索引失效的例子，比如你给user_id建了索引，但是查询的时候用了字符串类型的用户ID，比如WHERE user_id = '123'，如果user_id是bigint类型，就会发生隐式转换，索引失效。用EXPLAIN一看，key就是NULL，type是ALL，这时候赶紧改查询条件，把引号去掉：WHERE user_id = 123。

📌 要点提醒

每次写完复杂查询，尤其是JOIN、子查询、带聚合函数的查询，一定要先跑EXPLAIN，再看EXPLAIN ANALYZE。重点看type是不是到了range以上，key是不是你期望的索引，rows是不是过大，Extra里有没有Using filesort、Using temporary这些坏东西。如果是生产环境，EXPLAIN ANALYZE会实际执行语句，要是查询特别慢，可能会占资源，所以可以先在测试环境跑，或者加LIMIT 1先看看执行计划，再去掉LIMIT跑ANALYZE。另外，统计信息过旧会导致执行计划不准，定期跑ANALYZE TABLE更新统计信息，尤其是大表频繁更新的时候。

---

索引优化进阶：覆盖索引、降序索引与索引合并策略

前面讲了基础索引和慢查询定位，现在讲几个进阶优化技巧，都是线上大表优化常用的，尤其是覆盖索引，用好了能减少80%的回表操作，速度提升不是一点半点。咱们先从覆盖索引说起。

覆盖索引（Covering Index）打个比方，索引里包含了查询需要的所有字段，不用回表查主键索引。InnoDB的索引结构是：二级索引（也就是你建的普通索引）叶子节点存的是索引键和主键值，主键索引叶子节点存的是整行数据。所以如果你查的字段都在二级索引里，MySQL就直接返回索引里的内容，不用再去主键索引里查整行数据，这个过程叫“回表”，回表越多，速度越慢。

比如之前的订单表，你要查用户ID为123的订单的ID和创建时间，之前的查询是SELECT id, create_time FROM order_info WHERE user_id = 123，如果你建的是(user_id)的单列索引，那么索引里只有user_id和主键id，没有create_time，所以MySQL得拿到id之后去主键索引里查create_time，这就是回表。现在把create_time加到索引里，建(user_id, create_time)的联合索引，那么索引里就有user_id、create_time、id（主键默认在二级索引里），查询的字段都在索引里，就不用回表了。

来个实际例子：

-- 先删掉之前的联合索引，建新的覆盖索引
DROP INDEX idx_user_status_create ON order_info;
CREATE INDEX idx_user_create ON order_info (user_id, create_time);

-- 查询用户ID为123的订单ID和创建时间，用EXPLAIN看Extra
EXPLAIN SELECT id, create_time FROM order_info WHERE user_id = 123 LIMIT 10;

执行EXPLAIN之后，Extra里会出现Using index，这就是覆盖索引的标志，说明没有回表。如果没出现，说明还是回表了。再对比下回表的查询，比如SELECT id, create_time, amount FROM order_info WHERE user_id = 123，amount不在索引里，所以Extra里就没有Using index，需要回表查amount。

覆盖索引对报表系统的大表聚合查询特别有用，比如你要统计每个用户的订单数量：SELECT user_id, COUNT(*) FROM order_info GROUP BY user_id，如果建了(user_id)的索引，那么索引里已经有所有user_id了，直接扫描二级索引就行，不用回表，速度比扫全表快多了。要是再加个order_status到索引里，SELECT user_id, COUNT(*) FROM order_info WHERE order_status = 2 GROUP BY user_id也能用到覆盖索引。

接下来是降序索引，这个之前提过，8.0才支持的。以前我们要按时间倒序查最新的数据，比如ORDER BY create_time DESC，如果索引是升序的，MySQL就得反向扫描索引，或者额外排序。现在可以直接建降序索引，比如经常要查用户最新的订单，建(user_id ASC, create_time DESC)的索引：

-- 建用户ID升序、创建时间降序的索引
CREATE INDEX idx_user_create_desc ON order_info (user_id ASC, create_time DESC);

-- 查用户123的最新10条订单，看执行计划
EXPLAIN SELECT * FROM order_info WHERE user_id = 123 ORDER BY create_time DESC LIMIT 10;

这时候Extra里就不会有Using filesort了，因为索引本身就是按create_time降序存的，顺着索引扫就行。如果是组合排序，比如ORDER BY user_id ASC, create_time DESC，也可以建(user_id ASC, create_time DESC)的索引，刚好匹配排序顺序，不用额外排序。注意，降序索引只对DESC排序有效，如果你同时有ASC和DESC的排序需求，比如有的查询升序有的降序，可能需要建两个索引，或者看哪个查询更频繁，优先优化高频的。

然后是索引合并（Index Merge），这个是MySQL优化器的一种策略，当查询条件里有多个单列索引的时候，优化器会把多个索引的结果合并起来，不用全表扫描。比如你给user_id建了索引，给order_status建了索引，查询WHERE user_id = 123 AND order_status = 2，如果优化器觉得合并两个索引比用一个索引快，就会用索引合并。

来个例子：

-- 先建两个单列索引
CREATE INDEX idx_user_id ON order_info (user_id);
CREATE INDEX idx_order_status ON order_info (order_status);

-- 查user_id=123且状态为2的订单，看执行计划
EXPLAIN SELECT * FROM order_info WHERE user_id = 123 AND order_status = 2;

如果执行计划里的type是index_merge，key里有两个索引，Extra里有Using intersect(idx_user_id,idx_order_status)，说明用了索引合并的交集（Intersection），也就是两个索引的结果取交集。还有并集（Union），比如WHERE user_id = 123 OR order_status = 2，会用两个索引的结果取并集。

但是索引合并不是银弹，很多时候优化器不会选它，因为合并索引的成本可能比用一个联合索引高。比如上面的查询，如果建(user_id, order_status)的联合索引，肯定比索引合并快，因为联合索引直接就能定位到数据，不用合并结果。所以索引合并一般是临时救急用的，长期还是要建合适的联合索引。

还有一个常见的优化点：索引失效场景，这个也是求职必备知识点。比如隐式转换，前面提过，字段是int类型，查询用字符串，就会失效；还有对字段用函数，比如WHERE YEAR(create_time) = 2024，如果没建函数索引，就会失效；还有LIKE '%xxx'，前缀是通配符，索引失效，LIKE 'xxx%'是可以用索引的；还有OR条件，如果其中一个字段没索引，整个查询都不会用索引；还有IS NULL和IS NOT NULL，如果字段允许NULL，索引是可以用的，但是很多资料说不能用，这个是误区，实际测试8.0里IS NULL是可以用索引的。

📖 学习建议

优先用覆盖索引，建索引的时候尽量把查询需要的字段都包含进去，尤其是SELECT、WHERE、ORDER BY、GROUP BY里的字段，能减少回表。但是别把所有字段都加到索引里，不然索引太大，维护成本太高，一般联合索引的长度控制在30-50个字符以内（看字段类型）。降序索引只建你需要的排序方向，不要为了覆盖所有场景建一堆索引。索引合并能不用就不用，尽量用联合索引代替，因为联合索引的效率比索引合并高。另外，定期用EXPLAIN检查高频查询的执行计划，要是发现索引没用上，及时调整索引，比如之前提的不可见索引，先灰度验证再调整。

5. ：联合索引最左匹配与常见索引失效场景（隐式转换、函数）

简单来说，很多同学建了索引但查询还是慢，大概率就是踩了索引失效的坑。尤其是联合索引，最左匹配原则听起来简单，但真到了写 SQL 的时候，稍微不注意就全表扫描了。加上隐式转换和函数包裹，索引直接“罢工”。这一节咱们就来扒一扒这些常见的坑，顺便用 EXPLAIN 看看 MySQL 8.0.36 到底是怎么执行这些烂 SQL 的。

联合索引的最左匹配陷阱

假设我们在 orders 表上建了一个联合索引 (user_id, status, created_at)。这是电商场景里最常见的索引组合，用来查某个用户的订单状态和时间范围。

-- 建表语句（简化版）
CREATE TABLE `orders` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `status` VARCHAR(20) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `amount` DECIMAL(10, 2),
  PRIMARY KEY (`id`),
  KEY `idx_user_status_date` (`user_id`, `status`, `created_at`)
) ENGINE=InnoDB;

-- 插入一些测试数据
INSERT INTO orders (user_id, status, created_at, amount) VALUES
(1, 'PAID', '2024-01-01 10:00:00', 100.00),
(1, 'SHIPPED', '2024-01-02 11:00:00', 200.00),
(2, 'PAID', '2024-01-01 12:00:00', 150.00);

值得留意的是，联合索引就像电话簿，先按姓氏排，再按名字排。如果你不按这个顺序来，索引就不好使。

| :--- | :--- | :--- | :--- |

咱们来验证一下“无最左列”的情况，看看执行计划：

-- 模拟查询某个状态的订单，但没有指定用户
EXPLAIN SELECT * FROM orders WHERE status = 'PAID';

你会看到 type 是 ALL，key 是 NULL。换个角度看，MySQL 只能傻傻地从头到尾扫一遍表，因为它不知道 status 在索引的哪个位置，索引树的第一层是 user_id。

隐式转换：字符串与数字的暗战

这是最坑的一点。如果你定义的字段是 VARCHAR，但查询的时候传了个数字，或者反过来，MySQL 会进行隐式转换。这一转换，索引就废了。

假设 user_id 是 VARCHAR 类型（虽然通常我们用 INT，这里为了演示），或者我们查 status 时用了错误的数据类型。

-- 假设 status 是 VARCHAR，但我们传了数字（虽然这里 status 是字符串，我们换个场景）
-- 假设我们有一个 phone 字段是 VARCHAR(20)
ALTER TABLE orders ADD COLUMN phone VARCHAR(20);
CREATE INDEX idx_phone ON orders(phone);

-- 插入数据
UPDATE orders SET phone = '13800138000' WHERE id = 1;

-- 坑点来了：字符串字段用数字查
EXPLAIN SELECT * FROM orders WHERE phone = 13800138000;

运行上面的 EXPLAIN，你会发现索引 idx_phone 没用上。原因是 MySQL 会把 phone 列转换成数字来比较（因为等号右边是数字），这就相当于对索引列使用了函数，直接失效。

⚡ 效率提示：永远保证查询条件的数据类型和字段定义的数据类型一致。如果字段是字符串，查询时就用引号包起来。

索引列上的函数运算

这也是经典老坑。如果你对索引列使用了函数（如 DATE(), YEAR(), CONCAT() 等），或者做了运算（+, -, *, /），索引基本上就拜拜了。

-- 常见错误：对索引列使用函数
EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01';

在 MySQL 5.7 时代，这确实没救。但在 MySQL 8.0.36 里，我们有了函数索引（Functional Indexes）！虽然它能解决部分问题，但最好还是别直接在查询里对列用函数。

正确的写法应该是范围查询：

-- 优化后的写法
EXPLAIN SELECT * FROM orders 
WHERE created_at >= '2024-01-01 00:00:00' 
  AND created_at < '2024-01-02 00:00:00';

这样写，created_at 上的索引就能正常发挥威力了。

一个关于 NULL 的小坑

很多同学纠结 NULL 值会不会导致索引失效。简单来说，在 MySQL 的 B+Tree 索引里，NULL 是被当作一个特殊值存储在索引里的。

-- 查询 NULL 值
EXPLAIN SELECT * FROM orders WHERE phone IS NULL;

这个查询是可以走索引的。反而如果你用 phone = NULL，那是查不出结果的（应该用 IS NULL）。不过，值得留意的是，如果某个字段 NULL 值占比极高，索引的区分度就会很差，优化器可能觉得全表扫描更快，从而放弃索引。

6. 线上大表治理：Instant DDL与在线加索引风险应对

做线上大表治理，那真是“如履薄冰”。尤其是给几千万行的大表加索引，以前用 pt-online-schema-change 或者 gh-ost 还得担心主从延迟、负载飙升。现在咱们有 MySQL 8.0 了，情况好了不少，但坑还是有的。这一节咱们聊聊怎么安全地给大表加索引，以及 Instant DDL 到底香在哪里。

大表加索引的痛点

以前给大表加索引，简直是噩梦。表锁、复制延迟、磁盘 IO 打爆，随便一个都能让 DBA 破防。

在 MySQL 5.7 及之前，常用的方案是 pt-online-schema-change。它会创建一个新表，同步数据，然后切过去。虽然对业务影响小，但流程复杂，而且如果表太大，同步时间会非常长。

MySQL 8.0 的 Instant DDL 真香定律

MySQL 8.0 引入了 Instant DDL（尤其是 8.0.12 之后增强了这个功能）。换个角度看，就是加字段或者加索引的时候，不再需要 rebuild 整张表了，只需要在元数据层面做个记录，秒级完成。

不过，这里有个巨大的误区：Instant DDL 并不是对所有操作都 Instant。

加列（尤其是末尾加列）：Instant。
删列：不是 Instant（8.0.29 之后部分支持，但有限制）。
加索引：不是 Instant！

注意，给大表加索引，即使是 MySQL 8.0.36，也是需要 rebuild 表的，只是这个过程变成了 Online 的，不会长时间锁表，但依然会消耗大量的 IO 和 CPU。

实战：大表加索引的正确姿势

假设我们要给一张千万级的 logs 表加个索引。

场景设定：

表名：sys_logs
目标：给 operate_user 和 created_at 加联合索引。
数据库版本：MySQL 8.0.36 (LTS)。

步骤 1：评估影响

在加索引之前，先用 EXPLAIN 确认你的索引逻辑是对的，别加了个没用的索引上去。

步骤 2：选择执行时机

虽然是 Online DDL，但在业务高峰期执行依然危险。因为虽然不锁表，但涉及到数据拷贝，会产生大量的 redo log 和 undo log，可能导致主从延迟，或者把 buffer pool 冲掉。

步骤 3：执行 ALTER 语句

在 MySQL 8.0 中，默认就是 ALGORITHM=INPLACE, LOCK=NONE，但咱们最好显式指定，心里有底。

-- 给大表加索引，显式指定算法和锁策略
ALTER TABLE `sys_logs` 
ADD INDEX `idx_user_time` (`operate_user`, `created_at`),
ALGORITHM=INPLACE, 
LOCK=NONE;

代码解析：

ALGORITHM=INPLACE：表示不拷贝表（逻辑上），直接在原来的表数据上修改。
LOCK=NONE：表示不加锁，允许并发读写。

步骤 4：监控进度

在 8.0 里，你可以通过 performance_schema 来监控 DDL 的进度，这比以前盲等强多了。

-- 开启 stage 监控（如果没开的话）
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES' 
WHERE NAME LIKE 'stage/innodb/alter%';

-- 查看 DDL 进度
SELECT 
    EVENT_NAME, 
    WORK_COMPLETED, 
    WORK_ESTIMATED, 
    (WORK_COMPLETED/WORK_ESTIMATED)*100 as 'Progress%'
FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE 'stage/innodb/alter%';

⚡ 效率提示

磁盘空间预警：加索引需要额外的磁盘空间（大约是索引大小的一倍多）。如果磁盘满了，DDL 会直接失败，甚至导致实例 Read Only。
主从延迟：Online DDL 在大表上执行时，主库虽然不锁表，但产生的 Binlog 在从库回放是单线程的（除非你开了并行复制）。这可能会导致从库延迟几十分钟。如果是读写分离架构，得小心这一点。
使用隐形索引（Invisible Indexes）验证：在 8.0 里，你可以先加一个不可见索引，观察一段时间，确认优化器不会因为这个索引变傻，或者确认索引没用后再删掉，不用直接删掉索引。

-- 加一个不可见索引
ALTER TABLE `sys_logs` 
ADD INDEX `idx_test` (`operate_user`) INVISIBLE;

-- 如果觉得没问题，再改成可见
ALTER TABLE `sys_logs` ALTER INDEX `idx_test` VISIBLE;

避雷经验记录：有一次我直接在业务高峰期给一张 20G 的表加索引，虽然用了 LOCK=NONE，但由于服务器 IO 本身就比较高，DDL 导致大量会话处于 "Waiting for table metadata lock" 状态，最后不得不 Kill 掉进程。所以，哪怕是 Online DDL，也请务必在业务低峰期操作！

7. 总结与展望：AI辅助索引推荐与未来性能优化趋势

写了这么多索引优化的细节，其实咱们的核心目的就是让 SQL 跑得更快。但在 MySQL 8.0.36 这个 LTS 版本之后，数据库的优化已经不单单是“人肉”分析 EXPLAIN 了。未来的趋势是更智能、更自动化，甚至把 AI 和向量搜索都揉进去了。咱们来聊聊接下来的几年，索引和性能优化会往哪儿走。

回顾与现状：8.0 LTS 的基石

咱们现在用的 MySQL 8.0.36（2024年1月发布），已经把索引的基础打得非常扎实了。

不可见索引让我们可以灰度测试索引效果，不用一上来就删。
降序索引终于不再是对 ORDER BY DESC 进行 filesort 了。
函数索引让 WHERE json_extract(...) 这种查询也能用上索引。
EXPLAIN ANALYZE 让我们能看到真实的执行成本和行数，不再是干巴巴的预估。

这些特性已经让“人肉”优化的效率提升了一大截。但面对复杂的业务变化，光靠这些还不够。

趋势一：AI 辅助索引推荐（Workload-based）

这是我最期待的一个点。现在的 DBA 或者开发，往往是根据慢查询日志去反推索引。

未来的趋势是：基于工作负载的自动索引推荐。

想象一下，MySQL 或者云数据库能自动分析你过去一周的 SQL 语句，发现你在 orders 表上经常按 status 和 created_at 查询，但没建索引。系统直接弹出一个建议：“嘿，建议你在 orders 表上建一个 (status, created_at) 的索引，预计能提升 80% 的查询效率。”

这在一些云厂商的 RDS 里已经有了雏形（比如阿里云的索引推荐、AWS 的 Performance Insights），但在社区版 MySQL 里，这绝对是 2024-2026 年的重点。简单来说，就是让数据库自己学会“体检”和“开药”。

趋势二：更智能的优化器（直方图与统计信息）

有时候索引建对了，但 MySQL 就是不用，或者选了个烂执行计划。这通常是统计信息过时了。

MySQL 8.0 已经支持直方图（Histogram）了，用来补充索引统计信息的不足，特别是针对非索引列或者数据分布极不均匀的列。

-- 给某一列创建直方图
ANALYZE TABLE sys_logs 
UPDATE HISTOGRAM ON operate_user WITH 10 BUCKETS;

未来的优化器会更聪明，它会结合直方图、索引、甚至 AI 模型来预测查询成本，而不是死板地用规则。这意味着，咱们以后可能不需要在 SQL 里强行加 FORCE INDEX 这种丑陋的提示了。

趋势三：向量索引与 AI 融合

这是个大杀器。随着 AI/ML 的普及，应用不仅要查结构化数据，还要查向量（比如图片特征、文本 Embedding）。

以前我们得用专门的向量数据库（如 Milvus、Pinecone）。但现在，MySQL 8.0 及未来版本正在探索向量索引支持。

这意味着，你以后可能在一个 SQL 里同时干两件事：

用 B+ 树索引快速过滤出“用户ID=1”的数据。
用向量索引在过滤后的结果里做“相似度搜索”。

-- 这是一个未来可能的语法示例（非当前真实语法，仅示意）
SELECT * FROM products
WHERE user_id = 1
ORDER BY vector_column <-> '[0.1, 0.2, 0.3]'
LIMIT 10;

这种融合会极大地简化技术栈，不用在 MySQL 和向量库之间来回倒腾数据。

趋势四：云原生与 Serverless 下的自动伸缩

在云原生和 Serverless 架构下，数据库的计算节点和存储节点是分离的。

未来的索引管理会更细粒度。比如，索引也能根据访问频率自动“冷热分层”。访问很少的索引，它的元数据可能保留，但实际的数据页可以从高性能 SSD 下沉到对象存储，或者自动压缩。当查询来了，再自动加载回来。

🔧 实战技巧：

虽然这些趋势很酷，但作为开发者，咱们当下的重点还是把基础打牢。

拥抱 8.0 LTS：如果你还在用 5.7，赶紧升级吧，8.0 的索引特性真的能省很多事。
善用工具：多用 EXPLAIN ANALYZE 和 Performance Schema，别光靠猜。
关注 AI 工具：可以尝试用 GitHub Copilot 或者一些专门的 SQL 优化工具来辅助分析，未来这会是标配。

数据库优化这条路，从“经验主义”走向“数据智能”，咱们既是见证者，也是实践者。加油，把那些慢查询干掉！