mysql中常见执行流程瓶颈与优化策略

select * 拖慢查询因需读取整行、解析所有列并传输，尤其含TEXT/BLOB时I/O和内存开销大，且阻碍覆盖索引使用；应显式指定字段或加白名单控制。

为什么 SELECT * 会拖慢查询执行流程

不是所有字段都需要，但 mysql 仍要从磁盘或缓冲池读取整行、解析所有列、再网络传输——尤其当表有 TEXT 或 BLOB 字段时，I/O 和内存开销会陡增。更隐蔽的问题是：它让优化器难以利用覆盖索引。

• 用 EXPLaiN 检查 Extra 列是否出现 using where; Using index（说明走了覆盖索引），一旦写成 SELECT *，这个提示大概率消失
• 显式列出需要的字段，比如把 SELECT * 改成 SELECT id, name, status
• 如果应用层依赖动态字段，至少在 DAO 层做字段白名单控制，避免前端传 fields=* 直接拼 SQL

ORDER BY + LIMIT 在无合适索引时的执行瓶颈

MySQL 并不会因为加了 LIMIT 10 就只排序前 10 行；它先按 ORDER BY 对全结果集排序，再截断。若没有能支撑排序的索引，就会触发 Using filesort，严重时消耗大量临时磁盘空间和 CPU。

• 确认排序字段是否在索引最左前缀上，例如 ORDER BY created_at DESC，应建索引 INDEX idx_created (created_at)；若还带 WHERE user_id = ?，则更适合 INDEX idx_user_created (user_id, created_at)
• 避免在排序字段上用函数，如 ORDER BY date(created_at) 会让索引失效
• 对分页深翻场景（如 LIMIT 10000, 20），优先考虑游标分页：WHERE created_at

大事务导致的锁等待与 binlog 写入延迟

一个执行 5 秒的 UPDATE 语句，在 RR 隔离级别下可能持有行锁、间隙锁甚至 Next-Key 锁长达整个事务周期。其他会话在等锁时表现为 Waiting for table metadata lock 或 Locked 状态；同时，大事务还会阻塞 binlog 刷盘，影响主从同步延迟。

• 拆分批量更新：把 UPDATE t SET status=1 WHERE id IN (1..10000) 改为每 500 行一个事务
• 避免在事务里做 http 调用、文件读写等外部耗时操作
• 用 SHOW ENGINE INNODB STATUSG 查看当前锁冲突，重点关注 TRANSACTIONS 和 LOCK WAIT 部分
• 监控 information_schema.INNODB_TRX 表，对 TRX_ROWS_MODIFIED > 10000 或 TRX_DURATION > 60 的事务告警

JOIN 多表时驱动表选择错误引发全表扫描

MySQL 的嵌套循环连接（NLJ）依赖驱动表（外层表）尽可能小。如果优化器误选了大表作驱动表，就会对小表反复全扫描，性能指数级下降。常见于没统计信息、字段类型不一致或隐式转换的 JOIN 条件中。

• 用 EXPLAIN 观察 rows 列：驱动表的预估扫描行数应明显小于被驱动表
• 确保关联字段类型严格一致，比如 user.id 是 BIGINT，order.user_id 也必须是 BIGINT，否则触发隐式转换，索引失效
• 必要时用 STRAIGHT_JOIN 强制指定驱动表顺序，例如 SELECT STRAIGHT_JOIN ... FROM small_table s JOIN large_table l ON s.id = l.small_id
• 对复杂多表 JOIN，优先考虑冗余关键字段（如把 user_name 冗余进 order 表），减少 JOIN 次数

SELECT /*+ USE_INDEX(orders idx_user_status) */    u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE o.status = 'paid' AND u.created_at > '2024-01-01' ORDER BY o.created_at DESC LIMIT 20;

真正卡住的往往不是单条语句，而是多个看似合理的小决策叠加：字段没收敛、索引没覆盖、事务没切分、JOIN 没对齐。这些地方不报错，但会悄悄吃掉吞吐和响应时间。