前言：调优路上的 "血泪史"

在上一篇文章中，我给大家分享了 MySQL 调优的基本方法，很多小伙伴私信我说很实用。今天，我想换个角度，给大家分享一些我在调优路上踩过的坑。

我从事后端开发已经有 8 年了，从一个连索引是什么都不知道的小白，到现在能独立负责千万级数据量的数据库架构，这一路走来，踩过的坑不计其数。

有因为一条 SQL 语句导致整个系统崩溃的，有因为索引建错了导致数据查询异常的，还有因为配置错误差点删库跑路的...

这些坑让我付出了惨痛的代价，但也让我快速成长。今天，我就把这些 "血泪史" 分享出来，希望大家能够引以为戒，少走弯路。

坑一：联合索引的 "最左前缀原则"，我居然理解错了！

这是我刚工作不久踩的一个坑，至今记忆犹新。

当时我负责一个用户管理系统，有一个用户表，结构大概是这样的：

系统中有一个查询需求：根据姓名和年龄查询用户。于是我创建了一个联合索引：

然后我写了这样一条 SQL 语句：

我以为这条 SQL 会使用我创建的联合索引，结果用 EXPLAIN 一看，居然是全表扫描！

我当时就懵了，这是为什么呢？我明明创建了联合索引啊！

后来我才知道，联合索引遵循 "最左前缀原则"，也就是说，只有当查询条件中包含联合索引的第一个列时，索引才会生效。

在上面的例子中，联合索引是(name, age)，第一个列是name。而我的查询条件是WHERE age = 20 AND name = '张三'，虽然条件中包含了name，但是顺序不对，所以索引没有生效。

不过，MySQL 5.7 及以上版本引入了 "索引条件下推" 和 "查询优化器"，现在即使条件顺序不对，MySQL 也会自动调整顺序，使用联合索引。但是为了保险起见，还是建议大家按照联合索引的顺序来写查询条件。

坑二：我给性别列建了索引，结果性能更差了！

这是我工作第二年踩的一个坑，当时我对索引的理解还很肤浅，以为 "只要是查询条件就应该建索引"。

当时我们有一个订单表，里面有一个status列，表示订单的状态，有 0（未支付）、1（已支付）、2（已发货）、3（已完成）四个值。

系统中有一个查询需求：查询所有已完成的订单。于是我想当然地给status列建了一个索引：

结果上线后，我发现这条查询的性能不仅没有提升，反而更差了！

这是为什么呢？因为status列的区分度太低了。区分度指的是列中不同值的数量与总行数的比值。比值越高，区分度越高，索引的效果越好。

在上面的例子中，status列只有 4 个不同的值，区分度非常低。MySQL 在执行查询时，会先扫描索引，找到所有status=3的记录，然后再根据主键回表查询完整的数据。

如果已完成的订单占总订单数的比例很高（比如 30% 以上），那么回表查询的开销会非常大，甚至比全表扫描还要慢。

所以，永远不要为区分度低的列创建单独的索引。如果确实需要查询，可以考虑创建联合索引，把区分度高的列放在前面。

坑三：一条 SQL 语句，居然把数据库搞崩了！

这是我职业生涯中最难忘的一个坑，差点让我丢了工作。

当时我们公司做了一个促销活动，活动开始后，系统突然变得非常慢，最后直接崩溃了。

我紧急排查问题，发现慢查询日志中有一条 SQL 语句执行时间超过了 30 秒：

这条 SQL 语句的意思是：查询购买了商品 12345 的所有订单。

我用 EXPLAIN 分析了一下，发现 MySQL 居然把这条 SQL 转换成了一个相关子查询，相当于：

这意味着，对于 orders 表中的每一行，MySQL 都会执行一次子查询。如果 orders 表有 100 万行，那么子查询就会执行 100 万次！这性能能好才怪！

我当时吓得一身冷汗，赶紧把这条 SQL 改成了 JOIN：

修改后，这条 SQL 的执行时间从 30 秒降到了 10 毫秒，系统也恢复了正常。

从那以后，我再也不敢随便使用 IN 子查询了。在 MySQL 中，JOIN 的性能通常比子查询好得多。

坑四：我把 max_connections 设成了 10000，结果数据库挂了！

这是我在做性能测试时踩的一个坑。

当时我们要上线一个新系统，需要做压力测试。我想让系统支持更多的并发连接，于是就把max_connections参数从默认的 151 改成了 10000。

结果压力测试刚开始，数据库就挂了！

我当时很纳闷，为什么把最大连接数调大了，数据库反而挂了呢？

后来我才知道，每个 MySQL 连接都会占用一定的内存。如果连接数太多，内存就会被耗尽，导致系统使用交换分区，性能急剧下降，甚至崩溃。

MySQL 官方建议，max_connections参数不要设置得太大，一般在 1000-2000 之间比较合适。如果确实需要支持更多的并发连接，可以考虑使用连接池或者读写分离。

坑五：我开启了查询缓存，结果性能更差了！

这是我在 MySQL 5.6 版本踩的一个坑。

当时我听说查询缓存可以提高查询性能，于是就兴冲冲地开启了查询缓存：

query_cache_type=1 query_cache_size=1G

结果上线后，我发现系统的性能不仅没有提升，反而变得更不稳定了，经常出现卡顿。

这是为什么呢？因为查询缓存有一个致命的缺点：只要表中的数据发生了任何修改，所有与这个表相关的查询缓存都会被清空。

如果你的系统读多写少，并且数据更新不频繁，那么查询缓存可能会有一定的效果。但是如果你的系统写操作比较频繁，那么查询缓存的命中率会非常低，而且频繁的缓存失效会带来很大的开销。

正因为如此，MySQL 在 8.0 版本中彻底移除了查询缓存功能。

坑六：我用了 SELECT COUNT (*)，结果慢得要死！

这是一个很多人都会踩的坑。

当我们需要统计表中的行数时，通常会写这样一条 SQL 语句：

SELECT COUNT(*) FROM users;

如果表中的数据量很小，这条 SQL 语句执行得很快。但是如果表中的数据量很大（比如超过 1000 万行），这条 SQL 语句就会变得非常慢。

这是为什么呢？因为 InnoDB 是事务性存储引擎，它不会像 MyISAM 那样直接存储表的行数。当你执行SELECT COUNT(*)时，InnoDB 需要扫描整个表来统计行数，这在大表上会非常耗时。

那么，我们应该如何优化呢？这里有几个方法：

1. 使用近似值：如果不需要精确的行数，可以使用EXPLAIN SELECT * FROM users来获取近似的行数。

2. 使用计数器表：创建一个专门的计数器表，每次插入或删除数据时更新计数器。

3. 使用 Redis 缓存：把行数缓存在 Redis 中，定期更新。

调优的 "避坑指南"

总结一下我这些年踩过的坑，我整理了一份 MySQL 调优的 "避坑指南"，希望对大家有所帮助：

1. 正确理解联合索引的 "最左前缀原则"

2. 不要为区分度低的列创建单独的索引

3. 优先使用 JOIN 代替子查询

4. 不要把 max_connections 设置得太大

5. MySQL 8.0 以下版本不要开启查询缓存

6. 大表不要使用 SELECT COUNT (*) 统计行数

7. 不要在索引列上使用函数或运算

8. \\ 不要使用 SELECT \\*

9. 不要使用前导通配符的 LIKE 查询

10. 做好备份，在测试环境验证调优效果

结语

MySQL 调优是一个不断踩坑、不断学习、不断成长的过程。没有人天生就会调优，所有的经验都是从坑里面爬出来的。

希望我分享的这些坑能够帮助大家少走弯路。如果你也有过类似的经历，欢迎在评论区留言分享，让我们一起学习，共同进步。

最后，送给大家一句话：纸上得来终觉浅，绝知此事要躬行。MySQL 调优没有捷径，只有多实践、多总结，才能真正掌握这门技能。

如果你觉得这篇文章写得不错，别忘了点赞、收藏、关注三连哦！我会持续分享更多有趣又实用的技术干货。