每天一个知识点：SQL 优化

五原则

减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘 IO。
返回更少的数据：只返回需要的字段和数据分页处理，减少磁盘 IO 及网络 IO。
减少交互次数：批量 DML 操作，函数存储等减少数据连接次数。
减少服务器 CPU 开销：尽量减少数据库排序操作以及全表查询，减少 CPU 内存占用。
利用更多资源：使用表分区，可以增加并行操作，更大限度利用 CPU 资源。

Select 语句执行顺序

从这个顺序中我们可以发现，所有的查询语句都是从 FROM 开始执行的。在实际执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。

SQL 优化策略

避免无索引场景
- 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。
- 尽量避免使用 in 和 not in，会导致引擎走全表扫描。
  - 如果是连续数值，可以用between代替。
  - 如果是子查询，可以用exists代替。
- 尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。
  - 可以用union代替or。
- 尽量避免进行 null 值的判断，会导致数据库引擎放弃索引进行全表扫描。
  - 可以给字段添加默认值 0，对 0 值进行判断。
- 当数据量大时，避免使用 where 1 = 1 的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。
  - 用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and。
- 查询条件不能用 <> 或者 !=
- where 条件仅包含复合索引非前置列
- 隐式类型转换造成不使用索引
- order by 条件要与 where 中条件一致，否则 order by 不会利用索引进行排序
- 正确使用 hint 优化语句
  - MySQL中可以使用hint指定优化器在执行时选择或忽略特定的索引。一般而言，处于版本变更带来的表结构索引变化，更建议避免使用hint，而是通过Analyze table多收集统计信息。但在特定场合下，指定hint可以排除其他索引干扰而指定更优的执行计划。、
SELECT语句其他优化
- 避免出现select *
- 避免出现不确定结果的函数
- 多表关联查询时，小表在前，大表在后。
- 使用表的别名
  - 当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。
- 用 where 字句替换 HAVING 字句
- 调整 Where 字句中的连接顺序
  - MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。
增删改 DML 语句优化
- 大批量插入数据，建议使用多个值的INSERT语句，这比使用分开INSERT语句快，一般情况下批量插入效率有几倍的差别。
- 适当使用 commit 可以释放事务占用的资源而减少消耗，commit后能释放的资源如下：
  - 事务占用的 undo 数据块；
  - 事务在 redo log 中记录的数据块；
  - 释放事务施加的，减少锁争用影响性能。特别是在需要使用delete 删除大量数据的时候，必须分解删除量并定期commit。
- 避免重复查询更新的数据
- 查询优先还是更新（insert、update、delete）优先
  - 写入操作优先于读取操作。
  - 对某张数据表的写入操作某一时刻只能发生一次，写入请求按照它们到达的次序来处理。
  - 对某张数据表的多个读取操作可以同时地进行。
查询条件优化
- 对于复杂的查询，可以使用中间临时表暂存数据。
- 优化 group by 语句，默认情况下，MySQL 会对GROUP BY分组的所有值进行排序，如果查询包括 GROUP BY 但你并不想对分组的值进行排序，你可以指定 ORDER BY NULL禁止排序。
- 优化join语句，连接(JOIN)，之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
- 优化union查询，MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行，否则建议使用union all。原因在于如果没有all这个关键词，MySQL会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高。
- 拆分复杂SQL为多个小SQL，避免大事务
  - 简单的SQL容易使用到MySQL的QUERY CACHE；
  - 减少锁表时间特别是使用MyISAM存储引擎的表；
  - 可以使用多核CPU。
- 使用truncate代替delete
  - 当删除全表中记录时，使用delete语句的操作会被记录到undo块中，删除记录也记录binlog，当确认需要删除全表时，会产生很大量的binlog并占用大量的undo数据块，此时既没有很好的效率也占用了大量的资源。
  - 使用truncate替代，不会记录可恢复的信息，数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外，使用truncate可以回收表的水位，使自增字段值归零。
- 使用合理的分页方式以提高分页效率针对展现等分页需求，合适的分页方式能够提高分页的效率。
建表优化
- 在表中建立索引，优先考虑where、order by使用到的字段。
- 尽量使用数字型字段（如性别，男：1 女：2），若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
- 查询数据量大的表会造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段分页进行查询，循环遍历，将结果合并处理进行展示。
- 用 varchar/nvarchar 代替 char/nchar

参考

SQL优化最干货总结 - MySQL（2020最新版）
sql的执行顺序详解