mysql中查询优化的基本思路与实践

SELECT * 在大表上危险，因强制读取全部列导致IO激增且覆盖索引失效；应明确指定字段、用COUNT(1)或SELECT 1+LIMIT 1，并避免函数/隐式转换/左模糊等使索引失效的WHERE写法。

为什么 SELECT * 在大表上特别危险

它强制 MySQL 读取所有列的全部数据，即使你只用其中一两个字段。更关键的是，这会让覆盖索引失效——哪怕 WHERE 条件能走索引，MySQL 仍得回表查完整行，IO 成倍增加。

• 把 SELECT * 换成明确列出需要的字段，例如 SELECT id, name, status
• 如果只做统计或判断存在性，优先用 SELECT COUNT(1) 或 SELECT 1 配合 LIMIT 1
• 确认执行计划：用 EXPLAIN 看 type 是否为 index 或

  

  range，Extra 是否含 Using index

WHERE 条件里哪些写法会直接让索引失效

不是加了索引就一定走。常见“隐形拒绝”包括对索引列使用函数、隐式类型转换、以及在左侧使用模糊匹配。

• WHERE YEAR(create_time) = 2025 → 改成 WHERE create_time >= '2025-01-01' AND create_time
• WHERE user_id = '123'（user_id 是 INT）→ 字符串引号会触发隐式转换，去掉引号
• WHERE name LIKE '%abc' → 左模糊无法利用 B+ 树索引，考虑全文索引或倒排表替代
• 联合索引 (a, b, c) 中，WHERE b = 2 不走索引；必须满足最左前缀，如 WHERE a = 1 AND b = 2

如何判断是否该加索引，而不是盲目堆索引

索引不是越多越好。每多一个索引，写操作（INSERT/UPDATE/DELETE）都要同步更新 B+ 树，同时占用更多内存和磁盘空间。

• 优先给高频 WHERE、JOIN、ORDER BY、GROUP BY 中出现的字段建索引
• 用 SHOW INDEX FROM table_name 查看现有索引，结合 information_schema.STATISTICS 观察 Cardinality（基数），低基数字段（如 status 只有 0/1）单独建索引收益极小
• 避免冗余索引：已有 (a, b) 就不必再建 (a)；(a, b, c) 能覆盖 (a, b) 的查询
• 用 pt-duplicate-key-checker 或 sys.schema_unused_indexes（MySQL 8.0+）识别长期未被使用的索引

ORDER BY 和 LIMIT 组合为什么容易慢，怎么破

典型场景：SELECT * FROM orders ORDER BY created_at DESC LIMIT 10。如果 created_at 没索引，MySQL 得全表排序；即使有索引，若没覆盖查询字段，仍要回表取数据，尤其当偏移量大时（如 LIMIT 10000, 10）性能断崖下跌。

• 确保 ORDER BY 字段有索引，且方向一致（ASC/DESC 匹配索引定义）
• 用游标分页替代偏移分页：记录上一页最后一条的 created_at 值，下一页查 WHERE created_at
• 如果必须用 LIMIT m,n，且 m 很大，先用子查询定位主键再关联：

  SELECT o.* FROM orders o
  INNER JOIN (
    SELECT id FROM orders ORDER BY created_at DESC LIMIT 10000, 10
  ) AS tmp ON o.id = tmp.id;

索引策略和执行路径的细节，往往比 SQL 写法本身更影响性能。一个没走索引的 WHERE，或者一次没意识的全字段查询，可能让响应时间从毫秒级跳到秒级——而这种问题，在测试数据量小时完全暴露不出来。