MySQL 查询优化方法

一、索引优化

避免导致全表扫描的写法

• 不要使用 != 或 <>：会导致引擎放弃索引，转为全表扫描。

• 不要在 WHERE 子句中判断 NULL：改用默认值替代 NULL，再以等值条件查询。

  -- ❌ 避免
  SELECT id FROM t WHERE num IS NULL;
  
  -- ✓ 推荐：将 num 默认值设为 0
  SELECT id FROM t WHERE num = 0;
  

• 不要用 OR 连接条件：改用 UNION ALL，各分支可独立走索引。

  -- ❌ 避免
  SELECT id FROM t WHERE num = 10 OR num = 20;
  
  -- ✓ 推荐
  SELECT id FROM t WHERE num = 10
  UNION ALL
  SELECT id FROM t WHERE num = 20;
  

• LIKE 不能前置通配符：LIKE '%abc%' 会触发全表扫描，如需模糊搜索可考虑全文检索。

• 慎用 IN / NOT IN：连续数值改用 BETWEEN；子查询改用 EXISTS。

  -- ❌ 避免
  SELECT id FROM t WHERE num IN (1, 2, 3);
  
  -- ✓ 推荐
  SELECT id FROM t WHERE num BETWEEN 1 AND 3;
  

  -- ❌ 避免
  SELECT num FROM a WHERE num IN (SELECT num FROM b);
  
  -- ✓ 推荐
  SELECT num FROM a WHERE EXISTS (SELECT 1 FROM b WHERE num = a.num);
  

• WHERE 子句中不要对字段做函数或运算：会使索引失效，应将计算移到右侧。

  -- ❌ 避免
  SELECT id FROM t WHERE num / 2 = 100;
  SELECT id FROM t WHERE SUBSTRING(name, 1, 3) = 'abc';
  SELECT id FROM t WHERE DATEDIFF(day, createdate, '2005-11-30') = 0;
  
  -- ✓ 推荐
  SELECT id FROM t WHERE num = 200;
  SELECT id FROM t WHERE name LIKE 'abc%';
  SELECT id FROM t WHERE createdate >= '2005-11-30' AND createdate < '2005-12-01';
  

• WHERE 子句中使用参数会触发全表扫描：如必须使用，可强制指定索引。

  -- ❌ 避免
  SELECT id FROM t WHERE num = @num;
  
  -- ✓ 推荐：强制使用索引
  SELECT id FROM t WITH (INDEX(索引名)) WHERE num = @num;
  

复合索引注意事项

• 使用复合索引时，查询条件必须包含第一个字段，且字段顺序应尽量与索引顺序一致，否则索引不会生效。

索引数量控制

• 索引会降低 INSERT / UPDATE 的效率，单表索引数建议不超过 6 个。
• 低选择性字段（如性别字段 male/female 各占一半）建索引意义不大。
• 避免频繁更新聚集索引（CLUSTERED INDEX）列，因为该列决定数据的物理存储顺序，改变代价极高。

二、查询写法

• 禁止 SELECT *：明确列出所需字段，不返回无用数据。

• 避免无意义查询：生成空表结构应用 CREATE TABLE，而非 SELECT ... WHERE 1=0。

  -- ❌ 避免
  SELECT col1, col2 INTO #t FROM t WHERE 1 = 0;
  
  -- ✓ 推荐
  CREATE TABLE #t (...);
  

• 避免向客户端返回大数据量：数据量过大时，先确认需求是否合理。

• 避免大事务操作：大事务会降低系统并发能力。

三、表设计

• 数字信息不要用字符型字段：字符串比较逐字符进行，数字只需比较一次，性能差距显著，且会增加存储开销。

• 用 VARCHAR / NVARCHAR 代替 CHAR / NCHAR：变长字段节省空间，在较小字段上搜索效率更高。

四、临时表

• 优先使用表变量代替临时表，减少系统表资源消耗（注意表变量仅有主键索引）。
• 避免频繁创建和删除临时表。
• 一次性插入大量数据时，用 SELECT INTO 代替 CREATE TABLE + INSERT，产生日志更少、速度更快；数据量小时，先 CREATE TABLE 再 INSERT。
• 存储过程结束前，务必显式删除所有临时表：先 TRUNCATE TABLE，再 DROP TABLE，避免系统表长时间锁定。

五、游标

• 尽量避免使用游标，效率较差；操作数据超过 1 万行时应考虑改写。
• 优先寻找基于集合的解决方案，通常比游标更高效。
• 小数据集可使用 FAST_FORWARD 游标，尤其是需要关联多张表时。

六、存储过程与触发器

• 在所有存储过程和触发器开头设置 SET NOCOUNT ON，结尾设置 SET NOCOUNT OFF，减少不必要的客户端网络消息。