SQL优化的几个方向

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

语法优化

• 避免select *，要使用具体的字段。

• 避免 where 1=1，

• 数量多，用limit

• 避免子查询，改为join

• 使用EXISTS替代IN

• 优先使用 INNER JOIN，避免 LEFT JOIN/RIGHT JOIN

• 小表驱动大表：JOIN 时，将数据量小的表作为驱动表（放在 LEFT JOIN 左边或 INNER JOIN 前），减少外层循环次数。

• JOIN 条件必须使用索引字段

• ORDER BY/GROUP BY 字段需加索引

• 避免 SELECT DISTINCT 与 ORDER BY 同时使用

…………

索引优化

• 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