一、为什么会产生慢SQL1.1 慢SQL的本质慢SQL 需要访问的数据量÷每秒能处理的数据量太大要么分子太大扫描了太多无用数据要么分母太小处理能力不足。1.2 六大产生原因详解原因1全表扫描最常见占慢SQL的70%场景表有1000万行查询只返回10行但扫描了1000万行-- 典型案例无索引或索引失效 SELECT * FROM user WHERE phone 13800138000; -- phone无索引全表扫描1000万行5秒 EXPLAIN结果 type: ALL -- 全表扫描 rows: 10000000 -- 扫描1000万行 Extra: Using where -- 在Server层过滤为什么慢磁盘IO1000万行数据假设每行1KB需要读取10GB数据内存消耗Buffer Pool被大量无用数据占满热点数据被挤出CPU消耗逐行比较WHERE条件原因2索引使用不当场景1索引失效-- 有索引 idx_phone但这样写失效 SELECT * FROM user WHERE CAST(phone AS UNSIGNED) 13800138000; -- 对索引列做函数操作优化器无法使用索引 EXPLAIN结果 type: ALL -- 还是全表扫描 possible_keys: NULL key: NULL场景2不符合最左前缀-- 索引 idx_name_age_status (name, age, status) SELECT * FROM user WHERE age 25 AND status 1; -- 跳过了name索引无法使用 EXPLAIN结果 type: ALL -- 没走索引场景3LIKE以%开头-- 索引 idx_name (names) SELECT * FROM user WHERE name LIKE %刘%; -- BTree 索引是有序的从左到右匹配。%刘% 中间有通配符MySQL无法确定从哪里开始匹配只能全表扫描 EXPLAIN结果 type: ALL -- 没走索引场景4索引选择性差-- 性别字段只有男女选择性极低 SELECT * FROM user WHERE gender 1; -- 返回500万行 -- 即使有索引优化器可能选择全表扫描回表成本太高 EXPLAIN结果 type: index -- 用了索引但扫描所有行比ALL好点但也慢 rows: 10000000原因3大表JOIN场景两个大表JOIN产生笛卡尔积或Nested Loop效率低-- 用户表1000万订单表5000万 SELECT u.*, o.order_no FROM user u LEFT JOIN order o ON u.id o.user_id WHERE u.status 1; -- 执行过程 -- 1. 扫描user表1000万行status无索引 -- 2. 对每行user去order表查user_id有索引但执行1000万次索引查找 -- 3. 总耗时1000万 × 单次索引查找时间 几十分钟JOIN算法选择算法适用场景时间复杂度内存需求Nested Loop小表驱动大表O(M × N)低Block Nested Loop无索引JOINO(M × N)中Join BufferHash Join大表JOINMySQL8.0O(M N)高哈希表Merge Join有序数据O(M N)低原因4深分页LIMIT OFFSET场景翻页到后面越来越慢-- 第1页快 SELECT * FROM user ORDER BY id LIMIT 10 OFFSET 0; -- 扫描10行 -- 第10万页慢 SELECT * FROM user ORDER BY id LIMIT 10 OFFSET 1000000; -- 扫描1000010行扔掉前100万行返回10行 -- 时间0.1ms → 30秒差30万倍原理图LIMIT 10 OFFSET 0: [取10行] → 返回结束 共扫描10行 LIMIT 10 OFFSET 1000000: [跳过100万行] → [取10行] → 返回 共扫描1000010行内存排序100万行原因5锁等待与死锁场景并发更新导致锁竞争-- 事务A执行10秒 BEGIN; UPDATE account SET balance balance - 100 WHERE id 1; -- 锁住id1 -- 做其他事情... UPDATE account SET balance balance 100 WHERE id 2; -- 等待事务B释放id2 -- COMMIT; -- 事务B同时执行 BEGIN; UPDATE account SET balance balance - 50 WHERE id 2; -- 锁住id2 -- 做其他事情... UPDATE account SET balance balance 50 WHERE id 1; -- 等待事务A释放id1 -- 死锁MySQL强制回滚其中一个锁等待监控-- 查看当前锁等待 SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread FROM information_schema.innodb_lock_waits w JOIN information_schema.innodb_trx b ON b.trx_id w.blocking_trx_id JOIN information_schema.innodb_trx r ON r.trx_id w.requesting_trx_id;原因6服务器资源瓶颈瓶颈类型现象诊断方法CPU瓶颈复杂计算、大量并发查询top看CPU使用率SHOW PROCESSLIST看大量QueryIO瓶颈磁盘繁忙iowait高iostat -x 1看%util接近100%说明磁盘饱和内存瓶颈频繁换页Buffer Pool不够vmstat 1看si/so换入换出SHOW STATUS LIKE Innodb_buffer_pool%网络瓶颈返回数据量太大SELECT *返回百万行网卡打满二、慢SQL的完整诊断流程2.1 发现慢SQL-- 1. 开启慢查询日志 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 超过1秒记录 SET GLOBAL log_queries_not_using_indexes ON; -- 记录没走索引的 -- 查看日志 tail -f /var/log/mysql/slow.log -- 2. 实时查看正在执行的慢查询 SELECT * FROM information_schema.PROCESSLIST WHERE TIME 5 AND COMMAND ! Sleep; -- 执行超过5秒的 -- 3. MySQL 8.0性能Schema SELECT SQL_TEXT, TIMER_WAIT/10^9 AS exec_time_ms, LOCK_TIME/10^9 AS lock_time_ms, ROWS_SENT, ROWS_EXAMINED, CREATED_TMP_TABLES, NO_INDEX_USED FROM performance_schema.events_statements_history_long WHERE TIMER_WAIT 1 * 10^12 -- 超过1秒 ORDER BY TIMER_WAIT DESC;2.2 EXPLAIN深度分析-- 示例SQL SELECT u.username, o.order_no, o.amount FROM user u INNER JOIN order o ON u.id o.user_id WHERE u.status 1 AND o.create_time 2024-01-01 ORDER BY o.create_time DESC LIMIT 10; EXPLAIN分析 ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | u | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1000000| 10.00 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | o | NULL | ALL | NULL | NULL | NULL | NULL | 5000000| 33.33 | Using where; Using join buffer | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- -- 解读 -- 1. typeALL两个表都是全表扫描 -- 2. rows100万500万扫描600万行 -- 3. ExtraUsing temporary用了临时表排序需要 -- 4. ExtraUsing filesort文件排序内存不够放磁盘排序 -- 5. Using join bufferBlock Nested Loop JOIN无索引 -- 预估时间600万行 × 磁盘IO时间 ≈ 几分钟三、慢SQL的六大根治方案方案1索引优化解决80%的慢SQL索引设计原则-- 原则1WHERE、JOIN、ORDER BY的列建索引 -- 原SQL慢是因为没索引现在建立 -- user表WHERE status1但selectivity低放前面可能不好 -- 实际status1可能占80%选择性差不如让JOIN字段做前缀 -- 但这里ORDER BY o.create_time所以user表主要优化JOIN ALTER TABLE user ADD INDEX idx_status_id (status, id); -- 覆盖WHERE status1 和 JOIN ON u.id -- order表JOIN user_id WHERE create_time ORDER BY create_time ALTER TABLE order ADD INDEX idx_user_time (user_id, create_time, order_no, amount); -- 覆盖JOIN、WHERE、ORDER BY且覆盖SELECT列覆盖索引 -- 验证EXPLAIN type: ref -- 使用索引引用 key: idx_status_id -- 用了索引 rows: 1000 -- 从100万降到1000 Extra: Using index -- 覆盖索引不回表索引优化WHERE条件等值查询列放最左 JOIN条件被驱动表的JOIN字段必须有索引 ORDER BY尽量用索引避免filesort 覆盖索引SELECT列都在索引里Extra: Using index 避免冗余定期pt-duplicate-key-checker检查重复索引 定期维护ANALYZE TABLE更新统计信息OPTIMIZE整理碎片方案2SQL改写不改变索引改变写法案例1OR改UNION-- 慢SQLOR导致索引失效 SELECT * FROM user WHERE phone 13800138000 OR email testtest.com; -- phone和email都有索引但OR只能用一个另一个全表扫描 -- 改写UNION ALL利用两个索引 SELECT * FROM user WHERE phone 13800138000 UNION ALL SELECT * FROM user WHERE email testtest.com; -- 两个索引都用上总扫描2行快100倍 -- 注意UNION会去重额外排序确定无重复用UNION ALL案例2子查询改JOIN-- 慢SQL相关子查询每行执行一次 SELECT * FROM user u WHERE score (SELECT AVG(score) FROM user WHERE dept_id u.dept_id); -- 100万用户 100万次子查询 -- 改写JOIN 派生表只执行1次 SELECT u.* FROM user u INNER JOIN ( SELECT dept_id, AVG(score) as avg_score FROM user GROUP BY dept_id ) d ON u.dept_id d.dept_id WHERE u.score d.avg_score; -- 子查询物化后只执行1次快100倍案例3大IN改EXISTS或JOIN-- 慢SQLIN列表太大几千个ID SELECT * FROM order WHERE user_id IN (1,2,3,...,5000); -- MySQL优化器可能选择全表扫描 -- 改写JOIN临时表 SELECT o.* FROM order o INNER JOIN ( SELECT 1 as user_id UNION ALL SELECT 2 UNION ALL ... -- 或把ID写入临时表 ) t ON o.user_id t.user_id; -- 或EXISTS有时更好 SELECT * FROM order o WHERE EXISTS ( SELECT 1 FROM temp_user_ids t WHERE t.user_id o.user_id );方案3分页优化深分页专项方案3.1延迟关联-- 原SQL深分页慢 SELECT * FROM user WHERE status 1 ORDER BY id LIMIT 1000000, 10; -- 优化子查询先查ID覆盖索引再JOIN取数据 SELECT u.* FROM user u INNER JOIN ( SELECT id FROM user WHERE status 1 ORDER BY id LIMIT 1000000, 10 ) tmp ON u.id tmp.id; -- 子查询只扫描索引id, status快100倍方案3.2游标分页推荐-- 第一页 SELECT * FROM user WHERE status 1 ORDER BY id LIMIT 10; -- 记录最后一条id 100 -- 下一页业务层传last_id SELECT * FROM user WHERE status 1 AND id 100 -- 用索引范围查询 ORDER BY id LIMIT 10; -- 永远是O(log n) O(10)无深分页问题方案3.3业务限制-- 淘宝做法只让看前100页 if (page 100) { return 结果太多请细化搜索条件; } // 超过100页强制加更多筛选条件方案4表结构优化方案4.1字段类型优化-- ❌ 浪费空间影响索引大小 VARCHAR(255)存手机号实际11位 TEXT存短文本实际100字 BIGINT存状态0/1 -- ✅ 优化后 CHAR(11)存手机号定长索引更小 VARCHAR(200)存短文本实际长度1字节 TINYINT存状态1字节 vs 8字节 -- 效果索引体积小Buffer Pool缓存更多页查询更快方案4.2垂直拆分-- 原表100个字段每行10KB1000万行 100GB -- 查询经常只用到其中10个字段 -- 拆分 -- 主表 user热数据常用字段id, username, status, create_time -- 扩展表 user_detail冷数据大字段id, address, profile, config_json -- 查询主表行大小1KBBuffer Pool缓存100页 vs 原10页性能提升10倍方案4.3水平拆分分表分库-- 单表5000万拆10张表每张500万 user_0, user_1, ..., user_9 -- 分片键user_id % 10 -- 查询时根据user_id路由到对应表 -- 或按时间分区更适合日志类 CREATE TABLE user_202401 PARTITION OF user FOR VALUES FROM (2024-01-01) TO (2024-02-01); -- 查询1月数据只扫1个分区DROP旧分区瞬间完成方案5架构层优化方案5.1读写分离-- 主库写操作INSERT/UPDATE/DELETE -- 从库读操作SELECT -- 实现 -- 1. 数据库中间件ShardingSphere, MyCAT -- 2. 应用层路由Spring动态数据源 -- 注意主从延迟 -- 写主库后立即读从库可能读不到延迟几百毫秒 -- 解决方案强制走主库hint或业务容忍方案5.2缓存层-- Redis缓存热点数据 -- 查询流程 1. 查Redis1ms 2. 命中则返回 3. 未命中则查MySQL写入Redis设置过期时间 -- 注意缓存穿透、击穿、雪崩 -- 解决方案布隆过滤器、互斥锁、随机过期时间方案5.3搜索引擎-- 复杂搜索全文检索、多维度过滤用Elasticsearch -- 同步机制 -- 1. Canal监听MySQL Binlog实时同步ES -- 2. 定时任务批量同步 -- 查询先走ES拿到ID后再回MySQL查详情方案6数据库参数优化-- 连接池配置HikariCP推荐 maximum-pool-size: 20 -- 根据CPU核数调整通常10-50 minimum-idle: 5 connection-timeout: 30000 idle-timeout: 600000 max-lifetime: 1800000 -- MySQL配置 innodb_buffer_pool_size 8G -- 服务器内存的50-70% innodb_log_file_size 512M -- redo log大小大事务需要大 innodb_flush_log_at_trx_commit 2 -- 每秒刷盘平衡性能和安全 query_cache_type 0 -- MySQL 8.0已移除之前建议关闭锁竞争严重 max_connections 500 -- 根据连接池总数调整四、真实案例从30秒到10ms的优化实录案例背景业务电商订单搜索SQL查询某用户某时间段的订单按时间倒序支持分页数据量订单表5000万行原SQL执行时间30秒深分页时原SQL与执行计划SELECT * FROM order WHERE user_id 12345 AND create_time BETWEEN 2024-01-01 AND 2024-01-31 AND status IN (1,2,3) AND is_deleted 0 ORDER BY create_time DESC LIMIT 100000, 10; EXPLAIN分析 type: range -- 用了索引但范围太大 key: idx_user_id -- 只用了user_id索引 rows: 500000 -- 扫描50万行该用户1月有50万单 Extra: Using where; Using filesort -- 内存排序50万行问题诊断索引问题只有单列索引idx_user_idcreate_time过滤在Server层做数据问题该用户是大客户1月50万单数据分布不均分页问题LIMIT 100000,10需要排序后扔掉前10万行优化过程第一步建立组合索引-- 覆盖WHERE、ORDER BY避免回表 ALTER TABLE order ADD INDEX idx_user_time_status (user_id, create_time, status, is_deleted, order_no, amount); -- 注意把SELECT需要的列都放进来实现覆盖索引 EXPLAIN type: range key: idx_user_time_status rows: 1000 -- 从50万降到1000索引下推范围优化 Extra: Using index condition; Using where; Using index -- 还是filesort因为status IN (1,2,3)是多个等值排序不稳定第二步改写SQL消除filesort-- 原SQL的ORDER BY create_time在IN条件下可能filesort -- 优化用UNION ALL拆分IN每个子查询独立排序后合并 (SELECT * FROM order WHERE user_id 12345 AND status 1 AND create_time BETWEEN 2024-01-01 AND 2024-01-31 AND is_deleted 0 ORDER BY create_time DESC LIMIT 100000, 10) UNION ALL (SELECT * FROM order WHERE user_id 12345 AND status 2 AND create_time BETWEEN 2024-01-01 AND 2024-01-31 AND is_deleted 0 ORDER BY create_time DESC LIMIT 100000, 10) UNION ALL ... -- status3 -- 每个子查询独立走索引无filesort最后应用层合并排序取前10第三步深分页终极优化游标分页-- 业务改造不支持跳页只支持上一页/下一页 -- 第一页 SELECT * FROM order WHERE user_id 12345 AND status IN (1,2,3) AND create_time 2024-01-31 AND create_time 2024-01-01 AND is_deleted 0 ORDER BY create_time DESC, id DESC -- 加id保证唯一性 LIMIT 10; -- 记录最后一条create_time2024-01-15 10:20:30, id123456789 -- 下一页 SELECT * FROM order WHERE user_id 12345 AND status IN (1,2,3) AND is_deleted 0 AND (create_time 2024-01-15 10:20:30 OR (create_time 2024-01-15 10:20:30 AND id 123456789)) ORDER BY create_time DESC, id DESC LIMIT 10; -- 时间复杂度永远是O(log n)与页码无关优化效果对比指标优化前优化后提升执行时间30秒10ms3000倍扫描行数50万行10行5万倍内存使用排序50万行无排序极大降低磁盘IO大量随机IO索引顺序读10倍五、慢SQL预防体系1. 开发阶段SQL ReviewEXPLAIN检查每个查询索引设计WHERE/JOIN/ORDER BY字段必须有索引分页限制禁止深分页最大页码100联表限制JOIN不超过3个表单表数据量预估2. 测试阶段压测模拟生产数据量检查慢查询日志边界测试大数据量用户、高并发场景3. 生产阶段监控Prometheus Grafana监控慢查询率告警慢查询1秒且频率10/分钟立即告警巡检每日慢查询日志分析TOP 10优化预案大促前索引预热、主从延迟监控核心口诀慢SQL的根源是访问了太多数据解决方案要么是减少访问数据量索引、改写、分页优化要么是提升处理能力缓存、读写分离、分库分表。
慢SQL深度剖析：从产生原因到根治方案
发布时间：2026/7/3 16:50:13
一、为什么会产生慢SQL1.1 慢SQL的本质慢SQL 需要访问的数据量÷每秒能处理的数据量太大要么分子太大扫描了太多无用数据要么分母太小处理能力不足。1.2 六大产生原因详解原因1全表扫描最常见占慢SQL的70%场景表有1000万行查询只返回10行但扫描了1000万行-- 典型案例无索引或索引失效 SELECT * FROM user WHERE phone 13800138000; -- phone无索引全表扫描1000万行5秒 EXPLAIN结果 type: ALL -- 全表扫描 rows: 10000000 -- 扫描1000万行 Extra: Using where -- 在Server层过滤为什么慢磁盘IO1000万行数据假设每行1KB需要读取10GB数据内存消耗Buffer Pool被大量无用数据占满热点数据被挤出CPU消耗逐行比较WHERE条件原因2索引使用不当场景1索引失效-- 有索引 idx_phone但这样写失效 SELECT * FROM user WHERE CAST(phone AS UNSIGNED) 13800138000; -- 对索引列做函数操作优化器无法使用索引 EXPLAIN结果 type: ALL -- 还是全表扫描 possible_keys: NULL key: NULL场景2不符合最左前缀-- 索引 idx_name_age_status (name, age, status) SELECT * FROM user WHERE age 25 AND status 1; -- 跳过了name索引无法使用 EXPLAIN结果 type: ALL -- 没走索引场景3LIKE以%开头-- 索引 idx_name (names) SELECT * FROM user WHERE name LIKE %刘%; -- BTree 索引是有序的从左到右匹配。%刘% 中间有通配符MySQL无法确定从哪里开始匹配只能全表扫描 EXPLAIN结果 type: ALL -- 没走索引场景4索引选择性差-- 性别字段只有男女选择性极低 SELECT * FROM user WHERE gender 1; -- 返回500万行 -- 即使有索引优化器可能选择全表扫描回表成本太高 EXPLAIN结果 type: index -- 用了索引但扫描所有行比ALL好点但也慢 rows: 10000000原因3大表JOIN场景两个大表JOIN产生笛卡尔积或Nested Loop效率低-- 用户表1000万订单表5000万 SELECT u.*, o.order_no FROM user u LEFT JOIN order o ON u.id o.user_id WHERE u.status 1; -- 执行过程 -- 1. 扫描user表1000万行status无索引 -- 2. 对每行user去order表查user_id有索引但执行1000万次索引查找 -- 3. 总耗时1000万 × 单次索引查找时间 几十分钟JOIN算法选择算法适用场景时间复杂度内存需求Nested Loop小表驱动大表O(M × N)低Block Nested Loop无索引JOINO(M × N)中Join BufferHash Join大表JOINMySQL8.0O(M N)高哈希表Merge Join有序数据O(M N)低原因4深分页LIMIT OFFSET场景翻页到后面越来越慢-- 第1页快 SELECT * FROM user ORDER BY id LIMIT 10 OFFSET 0; -- 扫描10行 -- 第10万页慢 SELECT * FROM user ORDER BY id LIMIT 10 OFFSET 1000000; -- 扫描1000010行扔掉前100万行返回10行 -- 时间0.1ms → 30秒差30万倍原理图LIMIT 10 OFFSET 0: [取10行] → 返回结束 共扫描10行 LIMIT 10 OFFSET 1000000: [跳过100万行] → [取10行] → 返回 共扫描1000010行内存排序100万行原因5锁等待与死锁场景并发更新导致锁竞争-- 事务A执行10秒 BEGIN; UPDATE account SET balance balance - 100 WHERE id 1; -- 锁住id1 -- 做其他事情... UPDATE account SET balance balance 100 WHERE id 2; -- 等待事务B释放id2 -- COMMIT; -- 事务B同时执行 BEGIN; UPDATE account SET balance balance - 50 WHERE id 2; -- 锁住id2 -- 做其他事情... UPDATE account SET balance balance 50 WHERE id 1; -- 等待事务A释放id1 -- 死锁MySQL强制回滚其中一个锁等待监控-- 查看当前锁等待 SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread FROM information_schema.innodb_lock_waits w JOIN information_schema.innodb_trx b ON b.trx_id w.blocking_trx_id JOIN information_schema.innodb_trx r ON r.trx_id w.requesting_trx_id;原因6服务器资源瓶颈瓶颈类型现象诊断方法CPU瓶颈复杂计算、大量并发查询top看CPU使用率SHOW PROCESSLIST看大量QueryIO瓶颈磁盘繁忙iowait高iostat -x 1看%util接近100%说明磁盘饱和内存瓶颈频繁换页Buffer Pool不够vmstat 1看si/so换入换出SHOW STATUS LIKE Innodb_buffer_pool%网络瓶颈返回数据量太大SELECT *返回百万行网卡打满二、慢SQL的完整诊断流程2.1 发现慢SQL-- 1. 开启慢查询日志 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 超过1秒记录 SET GLOBAL log_queries_not_using_indexes ON; -- 记录没走索引的 -- 查看日志 tail -f /var/log/mysql/slow.log -- 2. 实时查看正在执行的慢查询 SELECT * FROM information_schema.PROCESSLIST WHERE TIME 5 AND COMMAND ! Sleep; -- 执行超过5秒的 -- 3. MySQL 8.0性能Schema SELECT SQL_TEXT, TIMER_WAIT/10^9 AS exec_time_ms, LOCK_TIME/10^9 AS lock_time_ms, ROWS_SENT, ROWS_EXAMINED, CREATED_TMP_TABLES, NO_INDEX_USED FROM performance_schema.events_statements_history_long WHERE TIMER_WAIT 1 * 10^12 -- 超过1秒 ORDER BY TIMER_WAIT DESC;2.2 EXPLAIN深度分析-- 示例SQL SELECT u.username, o.order_no, o.amount FROM user u INNER JOIN order o ON u.id o.user_id WHERE u.status 1 AND o.create_time 2024-01-01 ORDER BY o.create_time DESC LIMIT 10; EXPLAIN分析 ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | u | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1000000| 10.00 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | o | NULL | ALL | NULL | NULL | NULL | NULL | 5000000| 33.33 | Using where; Using join buffer | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- -- 解读 -- 1. typeALL两个表都是全表扫描 -- 2. rows100万500万扫描600万行 -- 3. ExtraUsing temporary用了临时表排序需要 -- 4. ExtraUsing filesort文件排序内存不够放磁盘排序 -- 5. Using join bufferBlock Nested Loop JOIN无索引 -- 预估时间600万行 × 磁盘IO时间 ≈ 几分钟三、慢SQL的六大根治方案方案1索引优化解决80%的慢SQL索引设计原则-- 原则1WHERE、JOIN、ORDER BY的列建索引 -- 原SQL慢是因为没索引现在建立 -- user表WHERE status1但selectivity低放前面可能不好 -- 实际status1可能占80%选择性差不如让JOIN字段做前缀 -- 但这里ORDER BY o.create_time所以user表主要优化JOIN ALTER TABLE user ADD INDEX idx_status_id (status, id); -- 覆盖WHERE status1 和 JOIN ON u.id -- order表JOIN user_id WHERE create_time ORDER BY create_time ALTER TABLE order ADD INDEX idx_user_time (user_id, create_time, order_no, amount); -- 覆盖JOIN、WHERE、ORDER BY且覆盖SELECT列覆盖索引 -- 验证EXPLAIN type: ref -- 使用索引引用 key: idx_status_id -- 用了索引 rows: 1000 -- 从100万降到1000 Extra: Using index -- 覆盖索引不回表索引优化WHERE条件等值查询列放最左 JOIN条件被驱动表的JOIN字段必须有索引 ORDER BY尽量用索引避免filesort 覆盖索引SELECT列都在索引里Extra: Using index 避免冗余定期pt-duplicate-key-checker检查重复索引 定期维护ANALYZE TABLE更新统计信息OPTIMIZE整理碎片方案2SQL改写不改变索引改变写法案例1OR改UNION-- 慢SQLOR导致索引失效 SELECT * FROM user WHERE phone 13800138000 OR email testtest.com; -- phone和email都有索引但OR只能用一个另一个全表扫描 -- 改写UNION ALL利用两个索引 SELECT * FROM user WHERE phone 13800138000 UNION ALL SELECT * FROM user WHERE email testtest.com; -- 两个索引都用上总扫描2行快100倍 -- 注意UNION会去重额外排序确定无重复用UNION ALL案例2子查询改JOIN-- 慢SQL相关子查询每行执行一次 SELECT * FROM user u WHERE score (SELECT AVG(score) FROM user WHERE dept_id u.dept_id); -- 100万用户 100万次子查询 -- 改写JOIN 派生表只执行1次 SELECT u.* FROM user u INNER JOIN ( SELECT dept_id, AVG(score) as avg_score FROM user GROUP BY dept_id ) d ON u.dept_id d.dept_id WHERE u.score d.avg_score; -- 子查询物化后只执行1次快100倍案例3大IN改EXISTS或JOIN-- 慢SQLIN列表太大几千个ID SELECT * FROM order WHERE user_id IN (1,2,3,...,5000); -- MySQL优化器可能选择全表扫描 -- 改写JOIN临时表 SELECT o.* FROM order o INNER JOIN ( SELECT 1 as user_id UNION ALL SELECT 2 UNION ALL ... -- 或把ID写入临时表 ) t ON o.user_id t.user_id; -- 或EXISTS有时更好 SELECT * FROM order o WHERE EXISTS ( SELECT 1 FROM temp_user_ids t WHERE t.user_id o.user_id );方案3分页优化深分页专项方案3.1延迟关联-- 原SQL深分页慢 SELECT * FROM user WHERE status 1 ORDER BY id LIMIT 1000000, 10; -- 优化子查询先查ID覆盖索引再JOIN取数据 SELECT u.* FROM user u INNER JOIN ( SELECT id FROM user WHERE status 1 ORDER BY id LIMIT 1000000, 10 ) tmp ON u.id tmp.id; -- 子查询只扫描索引id, status快100倍方案3.2游标分页推荐-- 第一页 SELECT * FROM user WHERE status 1 ORDER BY id LIMIT 10; -- 记录最后一条id 100 -- 下一页业务层传last_id SELECT * FROM user WHERE status 1 AND id 100 -- 用索引范围查询 ORDER BY id LIMIT 10; -- 永远是O(log n) O(10)无深分页问题方案3.3业务限制-- 淘宝做法只让看前100页 if (page 100) { return 结果太多请细化搜索条件; } // 超过100页强制加更多筛选条件方案4表结构优化方案4.1字段类型优化-- ❌ 浪费空间影响索引大小 VARCHAR(255)存手机号实际11位 TEXT存短文本实际100字 BIGINT存状态0/1 -- ✅ 优化后 CHAR(11)存手机号定长索引更小 VARCHAR(200)存短文本实际长度1字节 TINYINT存状态1字节 vs 8字节 -- 效果索引体积小Buffer Pool缓存更多页查询更快方案4.2垂直拆分-- 原表100个字段每行10KB1000万行 100GB -- 查询经常只用到其中10个字段 -- 拆分 -- 主表 user热数据常用字段id, username, status, create_time -- 扩展表 user_detail冷数据大字段id, address, profile, config_json -- 查询主表行大小1KBBuffer Pool缓存100页 vs 原10页性能提升10倍方案4.3水平拆分分表分库-- 单表5000万拆10张表每张500万 user_0, user_1, ..., user_9 -- 分片键user_id % 10 -- 查询时根据user_id路由到对应表 -- 或按时间分区更适合日志类 CREATE TABLE user_202401 PARTITION OF user FOR VALUES FROM (2024-01-01) TO (2024-02-01); -- 查询1月数据只扫1个分区DROP旧分区瞬间完成方案5架构层优化方案5.1读写分离-- 主库写操作INSERT/UPDATE/DELETE -- 从库读操作SELECT -- 实现 -- 1. 数据库中间件ShardingSphere, MyCAT -- 2. 应用层路由Spring动态数据源 -- 注意主从延迟 -- 写主库后立即读从库可能读不到延迟几百毫秒 -- 解决方案强制走主库hint或业务容忍方案5.2缓存层-- Redis缓存热点数据 -- 查询流程 1. 查Redis1ms 2. 命中则返回 3. 未命中则查MySQL写入Redis设置过期时间 -- 注意缓存穿透、击穿、雪崩 -- 解决方案布隆过滤器、互斥锁、随机过期时间方案5.3搜索引擎-- 复杂搜索全文检索、多维度过滤用Elasticsearch -- 同步机制 -- 1. Canal监听MySQL Binlog实时同步ES -- 2. 定时任务批量同步 -- 查询先走ES拿到ID后再回MySQL查详情方案6数据库参数优化-- 连接池配置HikariCP推荐 maximum-pool-size: 20 -- 根据CPU核数调整通常10-50 minimum-idle: 5 connection-timeout: 30000 idle-timeout: 600000 max-lifetime: 1800000 -- MySQL配置 innodb_buffer_pool_size 8G -- 服务器内存的50-70% innodb_log_file_size 512M -- redo log大小大事务需要大 innodb_flush_log_at_trx_commit 2 -- 每秒刷盘平衡性能和安全 query_cache_type 0 -- MySQL 8.0已移除之前建议关闭锁竞争严重 max_connections 500 -- 根据连接池总数调整四、真实案例从30秒到10ms的优化实录案例背景业务电商订单搜索SQL查询某用户某时间段的订单按时间倒序支持分页数据量订单表5000万行原SQL执行时间30秒深分页时原SQL与执行计划SELECT * FROM order WHERE user_id 12345 AND create_time BETWEEN 2024-01-01 AND 2024-01-31 AND status IN (1,2,3) AND is_deleted 0 ORDER BY create_time DESC LIMIT 100000, 10; EXPLAIN分析 type: range -- 用了索引但范围太大 key: idx_user_id -- 只用了user_id索引 rows: 500000 -- 扫描50万行该用户1月有50万单 Extra: Using where; Using filesort -- 内存排序50万行问题诊断索引问题只有单列索引idx_user_idcreate_time过滤在Server层做数据问题该用户是大客户1月50万单数据分布不均分页问题LIMIT 100000,10需要排序后扔掉前10万行优化过程第一步建立组合索引-- 覆盖WHERE、ORDER BY避免回表 ALTER TABLE order ADD INDEX idx_user_time_status (user_id, create_time, status, is_deleted, order_no, amount); -- 注意把SELECT需要的列都放进来实现覆盖索引 EXPLAIN type: range key: idx_user_time_status rows: 1000 -- 从50万降到1000索引下推范围优化 Extra: Using index condition; Using where; Using index -- 还是filesort因为status IN (1,2,3)是多个等值排序不稳定第二步改写SQL消除filesort-- 原SQL的ORDER BY create_time在IN条件下可能filesort -- 优化用UNION ALL拆分IN每个子查询独立排序后合并 (SELECT * FROM order WHERE user_id 12345 AND status 1 AND create_time BETWEEN 2024-01-01 AND 2024-01-31 AND is_deleted 0 ORDER BY create_time DESC LIMIT 100000, 10) UNION ALL (SELECT * FROM order WHERE user_id 12345 AND status 2 AND create_time BETWEEN 2024-01-01 AND 2024-01-31 AND is_deleted 0 ORDER BY create_time DESC LIMIT 100000, 10) UNION ALL ... -- status3 -- 每个子查询独立走索引无filesort最后应用层合并排序取前10第三步深分页终极优化游标分页-- 业务改造不支持跳页只支持上一页/下一页 -- 第一页 SELECT * FROM order WHERE user_id 12345 AND status IN (1,2,3) AND create_time 2024-01-31 AND create_time 2024-01-01 AND is_deleted 0 ORDER BY create_time DESC, id DESC -- 加id保证唯一性 LIMIT 10; -- 记录最后一条create_time2024-01-15 10:20:30, id123456789 -- 下一页 SELECT * FROM order WHERE user_id 12345 AND status IN (1,2,3) AND is_deleted 0 AND (create_time 2024-01-15 10:20:30 OR (create_time 2024-01-15 10:20:30 AND id 123456789)) ORDER BY create_time DESC, id DESC LIMIT 10; -- 时间复杂度永远是O(log n)与页码无关优化效果对比指标优化前优化后提升执行时间30秒10ms3000倍扫描行数50万行10行5万倍内存使用排序50万行无排序极大降低磁盘IO大量随机IO索引顺序读10倍五、慢SQL预防体系1. 开发阶段SQL ReviewEXPLAIN检查每个查询索引设计WHERE/JOIN/ORDER BY字段必须有索引分页限制禁止深分页最大页码100联表限制JOIN不超过3个表单表数据量预估2. 测试阶段压测模拟生产数据量检查慢查询日志边界测试大数据量用户、高并发场景3. 生产阶段监控Prometheus Grafana监控慢查询率告警慢查询1秒且频率10/分钟立即告警巡检每日慢查询日志分析TOP 10优化预案大促前索引预热、主从延迟监控核心口诀慢SQL的根源是访问了太多数据解决方案要么是减少访问数据量索引、改写、分页优化要么是提升处理能力缓存、读写分离、分库分表。
)
![华为OD机试2025C卷-字符串变换最小次数[100分]（ Java _ Python3 _ C++ _ C语言 _ JsNode _ Go）实现100%通过率](http://pic.xiahunao.cn/yaotu/华为OD机试2025C卷-字符串变换最小次数[100分]（ Java _ Python3 _ C++ _ C语言 _ JsNode _ Go）实现100%通过率)
![华为OD机试2025C卷-内存资源分配[100分]（ Java _ Python3 _ C++ _ C语言 _ JsNode _ Go）实现100%通过率](http://pic.xiahunao.cn/yaotu/华为OD机试2025C卷-内存资源分配[100分]（ Java _ Python3 _ C++ _ C语言 _ JsNode _ Go）实现100%通过率)
