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上生产前用真实数据量测试。记住数据库不会说谎执行计划就是你的体检报告。

相关新闻

AI投资热潮与企业落地难的矛盾解析

AI投资热潮与企业落地难的矛盾解析

1. AI投资热潮与企业落地现状的矛盾最近两年AI领域的投资热度持续攀升,各大科技巨头和风投机构都在疯狂加码。但有意思的是,根据最新调研数据,真正声称已经部署"成熟"AI解决方案的企业竟然只有1%。这个数字低得令人惊讶&#xff0c…

2026/11/21 17:47:41 阅读更多 →
【BIOS】华硕主板BIOS升级

【BIOS】华硕主板BIOS升级

主板类型和型号:华硕 PRIME X299-A II主板BIOS版本:1801升级遇到的问题:好了,这块主板出厂时的BIOS版本不是1801,2024年升级过一次以后,版本就到了1801。但是,升级到1801以后,就出现…

2026/11/20 10:27:59 阅读更多 →
Vlm-RT-DETR多模态实时检测模型解析与部署实践

Vlm-RT-DETR多模态实时检测模型解析与部署实践

1. Vlm-RT-DETR模型核心特性解析Vlm-RT-DETR作为百度最新推出的实时检测Transformer模型,在传统RT-DETR架构基础上融合了视觉语言多模态能力。其核心创新点在于通过动态查询机制实现视觉特征与语义信息的对齐,使得模型在保持实时性能的同时,能…

2026/11/21 6:40:21 阅读更多 →

最新新闻

Electron桌面应用开发实战:从环境搭建到打包发布全流程

Electron桌面应用开发实战:从环境搭建到打包发布全流程

做前端这些年,我大部分时间都在跟浏览器标签页打交道,直到第一次把桌面应用跑起来,才发现原来自己一直被限制在地址栏和刷新按钮围起来的框框里。Electron这个词,最开始是在某段时间频繁出现在同事的讨论里,一个用HTML…

2026/11/20 23:27:28 阅读更多 →
滑盖智能锁T109深度解析:结构原理、安装调试与故障排查

滑盖智能锁T109深度解析:结构原理、安装调试与故障排查

简介:亚太天能T109滑盖智能锁产品说明书是一份PDF格式的官方技术文档,主要面向智能锁安装人员、终端用户及售后维护者,帮助快速掌握产品安装、配置与常见故障处理。全包仅1个PDF文件,大小4.37MB,内容结构清晰&#xff…

2026/11/20 23:27:28 阅读更多 →
PMSM弱磁控制原理与Simulink建模:高速AGV驱动系统设计指南

PMSM弱磁控制原理与Simulink建模:高速AGV驱动系统设计指南

上个月我在调试一台高速AGV的驱动系统,整车要求空载跑到8m/s,电机转速已经拉到额定点,驱动器的输出电压也顶到了母线电压极限附近。再往上踩速度指令,转速纹丝不动,三相电流反而开始震荡。那会儿我的第一反应就是&…

2026/11/20 23:27:28 阅读更多 →
C++核心知识点:从内存管理到STL的实战指南

C++核心知识点:从内存管理到STL的实战指南

C 这门语言我前前后后折腾了十几年,用它写过嵌入式驱动、做过游戏服务端,也搭过基础组件库。每次看到新人一头扎进"C 核心知识点"这个搜索词,我其实挺感慨的——这门语言的难点从来不是语法多,而是知识点之间的耦合太深…

2026/11/20 21:55:32 阅读更多 →
T109滑盖智能锁怎么装?安装调试与故障排查全攻略

T109滑盖智能锁怎么装?安装调试与故障排查全攻略

简介:亚太天能T109滑盖智能锁产品说明书以PDF格式发布,适合智能锁用户、安装人员与售后维修人员阅读。内容围绕T109系列指纹密码刷卡锁展开,先介绍前面板、后面板、锁体、机械钥匙、电池盒盖等部件名称与功能,再给出完整安装步骤&…

2026/11/20 23:27:28 阅读更多 →
LLM知识降熵:从信息垄断到理解权下放

LLM知识降熵:从信息垄断到理解权下放

你有没有过这种经历:翻开源码注释、论文摘要或者一份专业报告,每个字都认识,连在一起却像天书。想找人聊聊,身边要么没人懂,要么懂的懒得跟你解释。后来我试着把同一段内容丢给一个大语言模型,几秒钟之后&a…

2026/11/20 22:59:12 阅读更多 →

日新闻

周新闻

月新闻