1. 那些年我们写过的坑同事SQL刚入行那会儿我接手过一个报表系统优化项目。打开数据库监控一看有个定时任务每次运行都要花47分钟而它只是简单统计了当日订单量。检查SQL后发现开发者在300万数据的表上用了OR连接6个模糊查询条件还没加索引。这就是典型的坑同事写法——单看功能没问题上线后却能让整个系统瘫痪。2. 性能杀手TOP8解析2.1 无索引的ORDER BY狂欢见过最夸张的案例是在10GB的日志表上执行SELECT * FROM access_log ORDER BY request_time DESC LIMIT 20这个查询需要全表扫描排序我曾亲眼见证它把16核服务器的CPU跑满15分钟。正确的做法是给request_time建索引或者改用分页查询配合WHERE时间范围过滤。关键教训排序操作会占用大量内存当sort_buffer_size不够时会转为磁盘临时文件性能呈指数级下降2.2 LIKE双通配符暴击某电商系统搜索功能用了SELECT product_name FROM items WHERE product_name LIKE %手机%保护套%这种双面通配符查询会让索引完全失效。实测在500万商品数据中有索引时查询耗时从8秒降到23毫秒优化方案是改用专门的分词搜索引擎。2.3 子查询连环套财务系统里有段经典杰作SELECT * FROM ( SELECT user_id FROM ( SELECT DISTINCT user_id FROM orders WHERE create_time 2023-01-01 ) t1 WHERE user_id IN (SELECT id FROM users WHERE vip1) ) t2 JOIN user_details ON t2.user_iduser_details.id这种嵌套子查询在MySQL 5.7下要扫描1200万次数据。改用JOIN重写后执行时间从11秒降到0.3秒。2.4 隐式类型转换陷阱某次排查发现这样的查询SELECT * FROM transactions WHERE account_id 10086account_id本是整型字段但前端传了字符串。这个隐式转换导致全表扫描在Alibaba开发规范中明令禁止。添加CAST或保证类型一致即可解决。2.5 失控的JOIN派对有个报表SQL连接了8张表SELECT * FROM a JOIN b ON a.idb.a_id JOIN c ON b.idc.b_id JOIN d ON c.idd.c_id ...省略4个JOIN执行计划显示产生了240万中间结果。通过拆分成多个查询程序内存关联耗时从78秒降到3秒。2.6 分页查询深坑常见的错误分页SELECT * FROM big_table LIMIT 1000000, 20MySQL会先读取100万条再丢弃。优化方案是用WHERE id last_max_id方式某社交APP改用此方案后分页API响应时间从2.1秒降到90毫秒。2.7 函数操作索引字段某CRM系统夜间跑批SQLSELECT * FROM customers WHERE DATE(create_time) 2023-06-01对索引列使用函数会导致索引失效。改为create_time BETWEEN 2023-06-01 00:00:00 AND 2023-06-01 23:59:59后执行时间从47秒降到0.5秒。2.8 事务不提交的灾难最严重的一次事故是开发写了BEGIN; UPDATE account SET balancebalance-100 WHERE user_id123; -- 忘记COMMIT这个未提交事务持有锁长达6小时导致所有资金操作全部阻塞。DBA最后只能强制kill会话。3. 性能优化实战手册3.1 EXPLAIN深度解读拿到问题SQL后我必做三件事执行EXPLAIN FORMATJSON看执行计划检查type列是否出现ALL全表扫描关注Extra列中的Using filesort、Using temporary某次优化案例中通过EXPLAIN发现虽然用了索引但key_len显示只用了联合索引的第一列调整列顺序后性能提升8倍。3.2 索引设计黄金法则我的索引设计checklist区分度高的列在前如手机号优于性别常用WHERE条件列必建索引避免在更新频繁的列建索引联合索引不超过5列定期使用pt-index-usage分析索引使用率曾为某P2P系统设计过(user_id,status,create_time)的联合索引使核心查询速度提升120倍。3.3 连接池调优要点高并发下连接池配置不当也会背锅# 错误配置示例 maxActive500 maxWait60000这会导致500个连接打满数据库。我们的最佳实践是设置maxActive为CPU核心数*2 有效磁盘数maxWait不超过3秒启用testWhileIdle定期检测连接4. 避坑指南与急救方案4.1 慢查询应急处理当收到数据库告警时我的应急流程执行SHOW PROCESSLIST找阻塞会话用pt-kill终止长时间运行的查询临时增加tmp_table_size和sort_buffer_size考虑设置max_execution_time去年双11大促通过这个流程在3分钟内解决了因GROUP BY导致的集群负载飙升。4.2 防坑开发规范我们团队现在强制执行的规范所有SQL必须通过SQLAdvisor审核查询结果集超过1000条必须分页事务内操作不超过5个SQL禁止在循环内执行查询所有表必须有create_time索引4.3 监控体系搭建推荐的核心监控指标慢查询率超过1秒的查询占比锁等待时间临时表创建频率索引命中率连接池等待数使用PrometheusGrafana搭建的监控系统曾提前30分钟预警了因OR查询导致的CPU瓶颈。5. 真实案例复盘5.1 电商搜索优化某母婴电商的搜索接口原来响应时间2.4秒分析发现使用LIKE %奶瓶%查询对category_id做了隐式转换按sales_count排序但无索引优化方案改用Elasticsearch分词查询添加(category_id,sales_count)联合索引引入结果缓存最终将平均响应时间降到180毫秒转化率提升17%。5.2 金融对账系统改造原有对账SQL每月要跑6小时SELECT * FROM transactions WHERE account_id IN (SELECT account_id FROM vip_accounts) AND create_time BETWEEN ... GROUP BY account_id优化手段用JOIN代替IN子查询按时间范围做分区表建立(account_id,create_time)索引改造后执行时间缩短到22分钟夜间批处理窗口从此够用。6. 高级优化技巧6.1 查询重写艺术面对这个统计SQLSELECT COUNT(*) FROM orders WHERE statuscompleted AND create_time DATE_SUB(NOW(), INTERVAL 1 YEAR)可以改写成SELECT COUNT(*) FROM orders WHERE id (SELECT MAX(id) FROM orders WHERE create_time DATE_SUB(NOW(), INTERVAL 1 YEAR)) AND statuscompleted利用ID自增特性减少扫描范围在亿级数据表上速度提升300倍。6.2 分区表实战策略某IoT平台的数据表按RANGE分区CREATE TABLE sensor_data ( id BIGINT, sensor_id INT, log_time DATETIME, value DECIMAL(10,2) ) PARTITION BY RANGE (TO_DAYS(log_time)) ( PARTITION p202301 VALUES LESS THAN (TO_DAYS(2023-02-01)), PARTITION p202302 VALUES LESS THAN (TO_DAYS(2023-03-01)), ... );配合WHERE log_time BETWEEN ...查询使月度报表生成从45分钟降到3分钟。6.3 内存优化秘籍关键参数调整经验# 专用数据库服务器配置 innodb_buffer_pool_size 12G # 总内存的70% innodb_log_file_size 2G innodb_flush_log_at_trx_commit 2 # 非金融业务可放宽 query_cache_size 0 # MySQL 8.0已移除某次将innodb_buffer_pool_size从4G调到8G后TPS从1200提升到2100。 SQL性能优化实战：8大常见陷阱与解决方案
1. 那些年我们写过的坑同事SQL刚入行那会儿我接手过一个报表系统优化项目。打开数据库监控一看有个定时任务每次运行都要花47分钟而它只是简单统计了当日订单量。检查SQL后发现开发者在300万数据的表上用了OR连接6个模糊查询条件还没加索引。这就是典型的坑同事写法——单看功能没问题上线后却能让整个系统瘫痪。2. 性能杀手TOP8解析2.1 无索引的ORDER BY狂欢见过最夸张的案例是在10GB的日志表上执行SELECT * FROM access_log ORDER BY request_time DESC LIMIT 20这个查询需要全表扫描排序我曾亲眼见证它把16核服务器的CPU跑满15分钟。正确的做法是给request_time建索引或者改用分页查询配合WHERE时间范围过滤。关键教训排序操作会占用大量内存当sort_buffer_size不够时会转为磁盘临时文件性能呈指数级下降2.2 LIKE双通配符暴击某电商系统搜索功能用了SELECT product_name FROM items WHERE product_name LIKE %手机%保护套%这种双面通配符查询会让索引完全失效。实测在500万商品数据中有索引时查询耗时从8秒降到23毫秒优化方案是改用专门的分词搜索引擎。2.3 子查询连环套财务系统里有段经典杰作SELECT * FROM ( SELECT user_id FROM ( SELECT DISTINCT user_id FROM orders WHERE create_time 2023-01-01 ) t1 WHERE user_id IN (SELECT id FROM users WHERE vip1) ) t2 JOIN user_details ON t2.user_iduser_details.id这种嵌套子查询在MySQL 5.7下要扫描1200万次数据。改用JOIN重写后执行时间从11秒降到0.3秒。2.4 隐式类型转换陷阱某次排查发现这样的查询SELECT * FROM transactions WHERE account_id 10086account_id本是整型字段但前端传了字符串。这个隐式转换导致全表扫描在Alibaba开发规范中明令禁止。添加CAST或保证类型一致即可解决。2.5 失控的JOIN派对有个报表SQL连接了8张表SELECT * FROM a JOIN b ON a.idb.a_id JOIN c ON b.idc.b_id JOIN d ON c.idd.c_id ...省略4个JOIN执行计划显示产生了240万中间结果。通过拆分成多个查询程序内存关联耗时从78秒降到3秒。2.6 分页查询深坑常见的错误分页SELECT * FROM big_table LIMIT 1000000, 20MySQL会先读取100万条再丢弃。优化方案是用WHERE id last_max_id方式某社交APP改用此方案后分页API响应时间从2.1秒降到90毫秒。2.7 函数操作索引字段某CRM系统夜间跑批SQLSELECT * FROM customers WHERE DATE(create_time) 2023-06-01对索引列使用函数会导致索引失效。改为create_time BETWEEN 2023-06-01 00:00:00 AND 2023-06-01 23:59:59后执行时间从47秒降到0.5秒。2.8 事务不提交的灾难最严重的一次事故是开发写了BEGIN; UPDATE account SET balancebalance-100 WHERE user_id123; -- 忘记COMMIT这个未提交事务持有锁长达6小时导致所有资金操作全部阻塞。DBA最后只能强制kill会话。3. 性能优化实战手册3.1 EXPLAIN深度解读拿到问题SQL后我必做三件事执行EXPLAIN FORMATJSON看执行计划检查type列是否出现ALL全表扫描关注Extra列中的Using filesort、Using temporary某次优化案例中通过EXPLAIN发现虽然用了索引但key_len显示只用了联合索引的第一列调整列顺序后性能提升8倍。3.2 索引设计黄金法则我的索引设计checklist区分度高的列在前如手机号优于性别常用WHERE条件列必建索引避免在更新频繁的列建索引联合索引不超过5列定期使用pt-index-usage分析索引使用率曾为某P2P系统设计过(user_id,status,create_time)的联合索引使核心查询速度提升120倍。3.3 连接池调优要点高并发下连接池配置不当也会背锅# 错误配置示例 maxActive500 maxWait60000这会导致500个连接打满数据库。我们的最佳实践是设置maxActive为CPU核心数*2 有效磁盘数maxWait不超过3秒启用testWhileIdle定期检测连接4. 避坑指南与急救方案4.1 慢查询应急处理当收到数据库告警时我的应急流程执行SHOW PROCESSLIST找阻塞会话用pt-kill终止长时间运行的查询临时增加tmp_table_size和sort_buffer_size考虑设置max_execution_time去年双11大促通过这个流程在3分钟内解决了因GROUP BY导致的集群负载飙升。4.2 防坑开发规范我们团队现在强制执行的规范所有SQL必须通过SQLAdvisor审核查询结果集超过1000条必须分页事务内操作不超过5个SQL禁止在循环内执行查询所有表必须有create_time索引4.3 监控体系搭建推荐的核心监控指标慢查询率超过1秒的查询占比锁等待时间临时表创建频率索引命中率连接池等待数使用PrometheusGrafana搭建的监控系统曾提前30分钟预警了因OR查询导致的CPU瓶颈。5. 真实案例复盘5.1 电商搜索优化某母婴电商的搜索接口原来响应时间2.4秒分析发现使用LIKE %奶瓶%查询对category_id做了隐式转换按sales_count排序但无索引优化方案改用Elasticsearch分词查询添加(category_id,sales_count)联合索引引入结果缓存最终将平均响应时间降到180毫秒转化率提升17%。5.2 金融对账系统改造原有对账SQL每月要跑6小时SELECT * FROM transactions WHERE account_id IN (SELECT account_id FROM vip_accounts) AND create_time BETWEEN ... GROUP BY account_id优化手段用JOIN代替IN子查询按时间范围做分区表建立(account_id,create_time)索引改造后执行时间缩短到22分钟夜间批处理窗口从此够用。6. 高级优化技巧6.1 查询重写艺术面对这个统计SQLSELECT COUNT(*) FROM orders WHERE statuscompleted AND create_time DATE_SUB(NOW(), INTERVAL 1 YEAR)可以改写成SELECT COUNT(*) FROM orders WHERE id (SELECT MAX(id) FROM orders WHERE create_time DATE_SUB(NOW(), INTERVAL 1 YEAR)) AND statuscompleted利用ID自增特性减少扫描范围在亿级数据表上速度提升300倍。6.2 分区表实战策略某IoT平台的数据表按RANGE分区CREATE TABLE sensor_data ( id BIGINT, sensor_id INT, log_time DATETIME, value DECIMAL(10,2) ) PARTITION BY RANGE (TO_DAYS(log_time)) ( PARTITION p202301 VALUES LESS THAN (TO_DAYS(2023-02-01)), PARTITION p202302 VALUES LESS THAN (TO_DAYS(2023-03-01)), ... );配合WHERE log_time BETWEEN ...查询使月度报表生成从45分钟降到3分钟。6.3 内存优化秘籍关键参数调整经验# 专用数据库服务器配置 innodb_buffer_pool_size 12G # 总内存的70% innodb_log_file_size 2G innodb_flush_log_at_trx_commit 2 # 非金融业务可放宽 query_cache_size 0 # MySQL 8.0已移除某次将innodb_buffer_pool_size从4G调到8G后TPS从1200提升到2100。