SQL索引优化实战：从慢查询到高效执行

1. 一条SQL引发的职场危机那天早上我像往常一样提交了代码没想到半小时后经理发来消息小张周末有空吗一起去爬山吧。看到这条消息我后背一凉——上周刚听说测试环境因为一条SQL把数据库拖垮了。打开Git记录一看果然是我写的查询语句出了问题。事情是这样的我们需要统计用户最近三个月的订单数据我写了条看似简单的查询SELECT * FROM orders WHERE user_id 12345 AND create_time DATE_SUB(NOW(), INTERVAL 3 MONTH) ORDER BY amount DESC;在测试环境只有几万条数据时运行良好但上了预发布环境有2000万订单数据后数据库CPU直接飙到100%。更糟的是这个查询被放在用户中心首页每次打开都会触发。2. EXPLAIN诊断揭开慢查询的真面目2.1 初识EXPLAIN工具经理教我的第一课就是使用EXPLAIN。在SQL语句前加上这个关键字就能看到MySQL的执行计划EXPLAIN SELECT * FROM orders WHERE user_id 12345 AND create_time DATE_SUB(NOW(), INTERVAL 3 MONTH) ORDER BY amount DESC;结果让我大吃一惊-------------------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | -------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders| ALL | user_index | NULL | NULL | NULL | 21840316| Using where; Using filesort | --------------------------------------------------------------------------------------------------------2.2 关键指标解读typeALL最糟糕的全表扫描数据库逐行检查了2184万条记录keyNULL没有使用任何索引ExtraUsing filesort在内存中对结果集进行了昂贵的外部排序原来我们虽然在user_id字段上有索引(user_index)但由于同时使用了create_time条件MySQL优化器认为使用索引效率不高转而选择了全表扫描。3. 索引失效的六大陷阱3.1 隐式类型转换我的第一个错误是user_id字段定义是varchar但查询时用了数字WHERE user_id 12345 -- 应该用12345这会导致索引失效解决方案很简单WHERE user_id 123453.2 函数操作索引列第二个问题是create_time的条件写法WHERE DATE_FORMAT(create_time,%Y-%m) 2023-04对索引列使用函数会导致索引失效。应该改为WHERE create_time 2023-04-01 00:00:003.3 最左前缀原则我们的复合索引是(user_id, create_time)但以下查询仍然用不到索引WHERE create_time 2023-01-01 -- 缺少user_id条件复合索引就像电话簿必须先按姓氏查找才能按名字筛选。3.4 OR条件陷阱这样的查询会让索引失效WHERE user_id 12345 OR amount 1000应该拆分为两个查询用UNION ALL合并SELECT * FROM orders WHERE user_id 12345 UNION ALL SELECT * FROM orders WHERE amount 1000 AND user_id ! 123453.5 不等于(!/)问题WHERE status ! completed这种否定条件通常会导致全表扫描。可以改为WHERE status IN (pending,processing,cancelled)3.6 LIKE通配符开头WHERE product_name LIKE %手机%前导通配符使索引失效。如果必须这样查考虑使用全文索引。4. 优化方案设计与实施4.1 重建合适的索引我们最终建立了复合索引ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time, amount);这样设计是因为user_id作为第一条件区分度高create_time范围查询放在第二位置amount用于排序避免filesort4.2 改写SQL语句优化后的查询SELECT id, user_id, amount, create_time FROM orders FORCE INDEX(idx_user_time) WHERE user_id 12345 AND create_time DATE_SUB(NOW(), INTERVAL 3 MONTH) ORDER BY amount DESC LIMIT 1000;关键改进明确指定使用索引(FORCE INDEX)只查询必要字段增加LIMIT限制结果集4.3 执行计划对比优化后的EXPLAIN结果---------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders| range | idx_user_time | idx_user_time| 767 | NULL | 532 | Using where | ----------------------------------------------------------------------------------------------扫描行数从2184万降到了532行5. 高级优化技巧5.1 覆盖索引优化如果查询只需要索引包含的字段可以避免回表操作-- 原始查询需要回表 SELECT * FROM orders WHERE user_id 12345; -- 优化为只查索引字段 SELECT user_id, create_time, amount FROM orders WHERE user_id 12345;5.2 分页查询优化常见的LIMIT分页在大偏移量时很慢-- 低效写法 SELECT * FROM orders LIMIT 1000000, 20; -- 优化方案记住上次的最大ID SELECT * FROM orders WHERE id 1000000 LIMIT 20;5.3 批量插入优化单条INSERT循环改为批量INSERT-- 低效写法 INSERT INTO orders(user_id,amount) VALUES(1001,99); INSERT INTO orders(user_id,amount) VALUES(1002,88); ... -- 高效写法 INSERT INTO orders(user_id,amount) VALUES (1001,99), (1002,88), ...;5.4 连接查询优化确保JOIN字段有索引小表驱动大表-- 用户表(小)驱动订单表(大) SELECT * FROM users u JOIN orders o ON u.id o.user_id WHERE u.register_time 2023-01-01;6. 监控与预防措施6.1 慢查询日志配置在my.cnf中开启慢查询日志slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 # 超过1秒的查询 log_queries_not_using_indexes 16.2 定期执行计划检查建立SQL审查机制对所有新上线的SQL进行EXPLAIN分析重点关注type是否为ALLkey是否为NULLExtra是否出现Using filesort/temporary6.3 压力测试验证使用sysbench等工具模拟生产数据量进行测试sysbench --db-drivermysql --mysql-host127.0.0.1 \ --mysql-port3306 --mysql-usertest --mysql-passwordtest \ --mysql-dbsbtest --tables10 --table-size1000000 \ --threads8 --time300 --report-interval10 oltp_read_write run那次爬山邀请最终变成了一场深刻的SQL优化课。现在我养成了习惯写完SQL先EXPLAIN上生产前用真实数据量测试。记住数据库不会说谎执行计划就是你的体检报告。