SQL优化

原文链接：

SQL优化最干货总结（2020最新版） - 小知的文章 - 知乎 https://zhuanlan.zhihu.com/p/299051996

SQL优化策略的前提：

  1. 声明：以下SQL优化策略适用于数据量较大的场景下，如果数据量较小，没必要以此为准，以免画蛇添足。

总结SQL优化，就三点:

  1. 最大化利用索引；
  2. 尽可能避免全表扫描；
  3. 减少无效数据的查询；

1） order by条件 要与 where中条件一致
  1. 不要做无谓的排序操作，而**应尽可能在索引中完成排序**。
  3. order by 条件要与where中条件一致，否则order by不会利用索引进行排序。
  1. -- 不走age索引
  5. SELECT * FROM t order by age;
  6. -- 走age索引
  10. SELECT * FROM t where age > 0 order by age;

对于上面的语句，数据库的处理顺序是：

第一步：根据where条件和统计信息生成执行计划，得到数据。

第二步：将得到的数据排序。当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。
如果是，则可以利用索引顺序而直接取得已经排好序的数据。
如果不是，则重新进行排序操作。

第三步：返回排序后的数据。
当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。

  1. 这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct等。

2）首先考虑在 where 及 order by 涉及的列上建立索引：
  1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

3） 避免在 where 子句中对字段进行 null 值判断：
  1. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，

如：

  1. select id from t where num is null
  1. 可以在num上设置默认值0，确保表中num列没有null值，

然后这样查询：

  1. select id from t where num=0

4）避免在 where 子句中使用!=或<>操作符
  1. 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将导致引擎放弃使用索引而进行全表扫描。
  3. 使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。
  5. 如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

5）避免在 where 子句中使用 or 来连接条件
  1. 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
  1. SELECT * FROM t WHERE id = 1 OR id = 3
  1. 优化方式：可以用union代替or。

如下：

  1. SELECT * FROM t WHERE id = 1
  5.    UNION
  9.         SELECT * FROM t WHERE id = 3

6）in 和 not in 要慎用
  1. in 和 not in 也要慎用，否则会导致全表扫描；
  3. 能用 between 就不要用 in 了；

如下：

  1. SELECT * FROM t WHERE id IN (2,3)

优化方式：如果是连续数值，可以用between代替。如下：

  1. SELECT * FROM t WHERE id BETWEEN 2 AND 3

如果是子查询，很多时候用 exists 代替 in 是一个好的选择：

比如：

  1. -- 不走索引
  5. select * from A where A.id in (select id from B);

用下面的语句替换：

  1. -- 走索引
  5. select * from A where exists (select * from B where B.id = A.id);

使用not in会导致数据库放弃索引，进行全表扫描。

7）不要使用%开头的like，比如：like ‘%xxx’

下面的查询将导致全表扫描：

  1. select id from t where name like '%abc%'

尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下：

  1. SELECT * FROM t WHERE username LIKE '%陈%'

优化方式：尽量在字段后面使用模糊查询。如下：

  1. SELECT * FROM t WHERE username LIKE '陈%'

8）不要在where条件中等号的左侧进行表达式、函数操作
  1. 尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。

可以将表达式、函数操作移动到等号右侧。如下：

  1. select id from t where num/2=100

应改为:

  1. select id from t where num=100*2

9）组合索引要遵循最左前缀匹配原则

10）索引并不是越多越好
  1. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。<br />    一个表的索引数最好不要超过6个。

11）避免使用 select *
  1. 应尽量避免使用 select _ from t ，用具体的字段列表代替“_”，不要返回用不到的任何字段。

12）避免使用游标
  1. 尽量避免使用游标，因为游标的效率较差。

13）避免使用where 1=1的条件
  1. 当数据量大时，避免使用where 1=1的条件。
  3. 通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：
  1. SELECT username, age, sex FROM T WHERE 1=1

优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and。

14）组合索引代替多个单列索引（经常使用多个条件查询时）

15）多表关联查询时，小表在前，大表在后。
  1. 在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），
  3. **第一张表会涉及到全表扫描，所以将小表放在前面**，先扫小表，扫描快效率较高，
  5. 再扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。

16）使用表的别名
  1. 当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。