SQL优化的几个方向 – 一个程序员

对于SQL优化，我这里将其分成：语法优化，索引优化，数据优化，业务优化四个方向来做。

语法优化

避免select *，要使用具体的字段。
避免 where 1=1，
数量多，用limit
避免子查询，改为join
使用EXISTS替代IN

…………

索引优化

sql导致索引失效：这里我总结了几种导致索引失效的点
- 使用函数或表达式：
  - 如UPPER(column) 、column + 1等
  - 函数substr(phone,1,3)='138'不如like '138%'，后者会走索引
  - 函数year(birthday) = 1990不如birthday >= '1990-01-01' and birthday < '1991-01-01'
- 隐式类型转换：varchar_column = 123; -- varchar_column 是字符串类型
- OR条件：OR条件中的列没有索引或无法同时使用索引，也会导致索引失效
- 前导模糊查询：在 LIKE 查询中，如果模式以通配符（如%）开头，索引将失效。
- 不等于操作：使用不等于操作符（如!\=或\<>）通常会导致索引失效。
- 不满足最左前缀原则：对于复合索引，查询条件必须满足最左前缀原则，否则索引将失效。
- 查询条件中包含负向查询：NOT IN、NOT LIKE等负向查询条件会导致索引失效。
联合索引是否命中问题：比如索引ABC对字段where A=? and C=?无法命中
是否有多余索引：随着业务维护，有些索引已经不适用了
是否有字段未加索引
是否有字段不适合加索引：比如数据量较小，频繁更新的字段
join的on条件是否加了索引：on条件中的字段也是可以走索引的

…………

数据优化

有些字段为空时，DB有时候会设为null，有时候又会被设置为空字符串，导致sql条件无法得到正确的数据量，往往要写(col1 == null or col1 = '')
有些字段定义过长，数据库空间冗余

…………

业务优化

即跟业务沟通，进行业务上的变通，为性能让步。。。

比如：业务上的左右模糊查询，能不能跟业务沟通下只用右模糊查询，这样SQL中的like就可以`like ‘xxx%’

…………

几个优化技巧：

针对大表的join优化

用一个 hashMap存储较小的tb2表，使用主键id列当作哈希表的key，只对大表做for循环。
数据冗余，就是把两张表的重要字段在各自表中做冗余，避免关联查询了

针对like慢查询的优化

反向查询：增加一个reverse_colname的字段，表示需要查询的列的逆序，如果需要查询的列数据是abc，那么这个字段就存储cba。

深度分页优化：

inner join：

SELECT c1, c2, ...
FROM table t1
INNER JOIN (
  SELECT id
  FROM table
  WHERE name = "xxx"
  ORDER BY id
  LIMIT 1000000, 10
) AS t2 ON t1.id = t2.id

自增id：

SELECT c1, c2, cn...
FROM table
WHERE name = "xxx"
AND id >= (SELECT id FROM table WHERE name = "xxx" ORDER BY id LIMIT 1000000, 1)
ORDER BY id
LIMIT 10

语法优化

索引优化

数据优化

业务优化

几个优化技巧：

针对大表的join优化

针对like慢查询的优化

深度分页优化：

相关文章