sql语句优化
本文最后更新于：2025年7月3日 上午
从各博客以及实践经验中总结的sql优化方法
一、SQL语句优化
1.索引
索引 在数据库中十分常见。最常见的 主键（primary key） 就是一种索引。许多人都将索引比作字典的目录，以此解释其为什么可以加快搜索。
从更深层次讲，大部分索引（例如 primary key, index, unique index 等）都是基于 B+树 存储管理的。 B+树 的查找时间复杂度为 O(logn) ，这比没有索引直接遍历的 O(n) 时间复杂度显然要快得多。因此对于 WHERE 等查询语句中经常要使用的列，对其创建索引可以提高查询效率。而 B+树 结构也解释为什么联合索引在使用时要遵循最左原则。
以下几种情况中涉及的列适合建立索引：
WHERER语句中需要判断的列。例如WHERE age > 18中age列就可以创建索引加快该语句查询速度。注意！一些操作例如使用!=，is null等会使WHERE中的列不会通过索引加速，因此添加索引前务必确认。JOIN子句中的列。例如JOIN customers c ON o.customer_id = c.id中的o.customer_id和c.id可以添加索引来加速。ORDER BY子句中的列。例如ORDER BY created_time DESC中的created_time。GROUP BY子句中的列。DISTINCT去重的列。HAVING子句中的聚合字段。
但是，也有文章提到数据库会估计索引查询和遍历查询的速度，如果估计使用索引比全表扫描还慢，则不会使用索引。因此，通常不对重复率高的列创建索引，可以使用 explain 查看是否命中索引。
由于索引需要维护额外的结构，所以它其实是牺牲 空间 和 写速度 来获取更快的 读速度 。因此，索引并非越多越好，滥用索引会产生写入缓慢，索引碎片等问题。
2.IN 和 NOT IN
2.1常量序列情况
如果 in 的子语句为一个常量序列，例如：
select id from User where degree in (1, 2, 3)若常量序列较大 ，会导致 索引失效 ，从而降低搜索速率，可以用 degree between 1 and 3 优化。
NOT IN无论大小，均不通过索引
2.2子查询语句
select num from a where num in(select num from b)这是 IN 中为子查询语句的情况。
此时有一个问题：该语句会先处理子查询语句，获得所有符合结果，如果该集合较大，会导致性能下降。
因此，如果子查询规模较大，推荐使用 EXIST 或 JOIN 优化，来避免过于庞大的子查询。
select num from a where exists(select 1 from b where num=a.num)只要找到第一条子查询中符合的结果便会立即返回，配合将 b.num 设置成索引可以较快完成查询。
在 in 的序列或查询较为庞大时考虑优化，也有 文章 提出 in 在小规模时开销优于 or 和 union
3.少用select *
如果不是必须返回全部列，可以通过 select 需要的字段减少CPU、IO、内存、网络带宽等消耗。
4.善用limit 1
limit 1 可以在找到一条符合条件记录后立即返回，而不再继续查询。因此，如果确定查询结果最多只有一条，可以加上 limit 1 优化。
5.WHERE语句优化
前文提到 WHERE 中的一些操作会导致数据库不通过索引查询。
5.1避免null
null 无论是作为数据还是sql语句中的判断条件都可能带来隐患，数据表中尽可能不要使用 null 作为默认空值，而是用 0 等值代替。
5.2避免负向条件
负向条件指那些强调 不是 的条件，例如：不等于， 所有的 not (not in, not like)。
负向条件均不通过索引查询，优化时可考虑避免。
5.3慎用or
SELECT * FROM users
WHERE username = 'Tom' OR email = 'tom@example.com';使用 or 时，只有当 username 和 email 两个字段均设置索引时，该查询才会通过索引查询。
可以考虑使用 UNION 优化。
select * from users where username = 'Tom'
union
select * from users email = 'tom@example.com'5.4避免前导模糊查询
前导模糊查询 指查询数据开头模糊不固定， LIKE 模式以 % 开头 的查询，例如：
SELECT * FROM users WHERE username LIKE '%tom';该查询也会使得索引失效
5.5避免隐式转换
当某个字段为字符串类型，而sql语句传递的是数值类型时，数据库会自动进行隐式类型转换，例如：
SELECT * FROM user WHERE name=1该情况会使得name的索引失效，因此建议类型描述准确，尽可能避免隐式转换：
SELECT * FROM user WHERE name='1'5.6联合索引牢记最左原则
大部分索引的数据结构为 B+树 ，联合索引实际上是先针对第一个字段进行排序，第一个字段相同再依据第二个字段排序，以此类推来构建树。
所以，如果不遵循最左原则，（a, b, c）三者的联合索引，跳过a去查询b，索引树是无序的，无法查询搜索，数据库将进行全局遍历搜索。
5.7避免（不）等式左侧计算函数
SELECT * FROM user WHERE substring(name,1,3)='Tom' --索引失效
SELECT * FROM user WHERE age + 1 > 18 --索引失效建议处理逻辑全部放在业务处理中，而不要在sql中出现，避免索引失效
6.尽量用UNION ALL代替UNION
UNION 会进行过滤操作，去掉重复数据，这其中涉及排序，会消耗一定资源。
如果确定两个数据集不存在重复数据，或者可以接受重复，尽量使用 UNION ALL 代替
7.偏移优化
当需要第n条开始的m条数据时，可能会使用 limit 进行偏移操作，但是这实际上查询了 n+m 条数据，然后抛弃了前 n-1 条。当n较大时这会造成不必要的开销。
select id,name,age from user limit 10000, 20; --多余查询了10000条记录
select id,name,age from user id>10000 limit 20; --优化