老司机总结的12条 SQL 优化方案（非常实用）

文章目录

一、SQL语句及索引的优化

SQL语句的优化

1. 尽量避免使用子查询
2. 用IN来替换OR
3. 读取适当的记录LIMIT M,N，而不要读多余的记录
4. 禁止不必要的Order By排序
5. 总和查询可以禁止排重用union all
6. 避免随机取记录
7. 将多次插入换成批量Insert插入
8. 只返回必要的列，用具体的字段列表代替 select * 语句
9. 区分in和exists
10. 优化Group By语句
11. 尽量使用数字型字段
12. 优化Join语句

索引的优化/如何避免索引失效

二、数据库表结构的优化：使得数据库结构符合三大范式与BCNF

三、系统配置的优化

四、硬件的优化

---

在开始介绍如何优化sql前，先附上mysql内部逻辑图让大家有所了解

（1）连接器： 主要负责跟客户端建立连接、获取权限、维持和管理连接

（2）查询缓存： 优先在缓存中进行查询，如果查到了则直接返回，如果缓存中查询不到，在去数据库中查询。

MySQL缓存是默认关闭的，也就是说不推荐使用缓存，并且在MySQL8.0 版本已经将查询缓存的整块功能删掉了。这主要是它的使用场景限制造成的：

• 先说下缓存中数据存储格式：key（sql语句）- value（数据值），所以如果SQL语句（key）只要存在一点不同之处就会直接进行数据库查询了；
• 由于表中的数据不是一成不变的，大多数是经常变化的，而当数据库中的数据变化了，那么相应的与此表相关的缓存数据就需要移除掉；

（3）解析器/分析器： 分析器的工作主要是对要执行的SQL语句进行词法解析、语法解析，最终得到抽象语法树，然后再使用预处理器对抽象语法树进行语义校验，判断抽象语法树中的表是否存在，如果存在的话，在接着判断select投影列字段是否在表中存在等。

（4）优化器： 主要将SQL经过词法解析、语法解析后得到的语法树，通过数据字典和统计信息的内容，再经过一系列运算 ，最终得出一个执行计划，包括选择使用哪个索引

• 在分析是否走索引查询时，是通过进行动态数据采样统计分析出来；只要是统计分析出来的，那就可能会存在分析错误的情况，所以在SQL执行不走索引时，也要考虑到这方面的因素

（5）执行器： 根据一系列的执行计划去调用存储引擎提供的API接口去调用操作数据，完成SQL的执行。

一、SQL语句及索引的优化

SQL语句的优化

1. 尽量避免使用子查询

例：

SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name = 'chackca');

其子查询在Mysql5.5版本里，内部执行计划是这样：先查外表再匹配内表，而不是先查内表t2，当外表的数据很大时，查询速度会非常慢。

在MariaDB10/Mysql5.6版本里，采用join关联方式对其进行了优化，这条SQL语句会自动转换为：SELECT t1.* FROM t1 JOIN t2 on t1.id = t2.id

但请注意的是：优化只针对SELECT有效，对UPDATE/DELETE子查询无效，固生产环境应避免使用子查询

由于MySQL的优化器对于子查询的处理能力比较弱，所以不建议使用子查询，可以改写成Inner Join，之所以 join 连接效率更高，是因为 MySQL不需要在内存中创建临时表

2. 用IN来替换OR

• 低效查询： SELECT * FROM t WHERE id = 10 OR id = 20 OR id = 30;
• 高效查询：S ELECT * FROM t WHERE id IN (10,20,30);

另外，MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from table_name where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了；再或者使用连接来替换。

3. 读取适当的记录LIMIT M,N，而不要读多余的记录

select id,name from t limit 866613, 20

使用上述sql语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。

对于 limit m, n 的分页查询，越往后面翻页（即m越大的情况下）SQL的耗时会越来越长，对于这种应该先取出主键id，然后通过主键id跟原表进行Join关联查询。因为MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。

优化的方法如下：可以取前一页的最大行数的id（将上次遍历到的最末尾的数据ID传给数据库，然后直接定位到该ID处，再往后面遍历数据），然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。sql可以采用如下的写法：