下面是小编为大家整理的老司机总结12条,SQL,优化方案（非常实用）（完整文档）,供大家参考。
的 老司机总结的 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可以采用如下的写法：
select id,name from table_name where id> 866612 limit 20
4. 禁止不必要的 Order By 排序 如果我们对结果没有排序的要求，就尽量少用排序； 如果排序字段没有用到索引，也尽量少用排序； 另外，分组统计查询时可以禁止其默认排序 SELECT goods_id,count(*) FROM t GROUP BY goods_id;
默认情况下，Mysql 会对所有的 GROUP BT col1,col2… 的字段进行排序，也就是说上述会对 goods_id 进行排序，如果想要避免排序结果的消耗，可以指定 ORDER BY NULL 禁止排序：
SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL
5. 总和查询可以禁止排重用 union all union 和 union all 的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 CPU运算，加大资源消耗及延迟。另外，搜索公众号 Java 就该这么学后台回复“低代码”，获取一份惊喜礼包。
当然， union all 的前提条件是两个结果集没有重复数据。所以一般是我们明确知道不会出现重复数据的时候才建议使用 union all
提高速度。
6. 避免随机取记录 SELECT * FROM t1 WHERE 1 = 1 ORDER BY RAND() LIMIT 4; SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;
以上两个语句都无法用到索引 7. 将多次插入换成批量 Insert 插入 INSERT INTO t(id, name) VALUES(1, "aaa"); INSERT INTO t(id, name) VALUES(2, "bbb"); INSERT INTO t(id, name) VALUES(3, "ccc"); —> INSERT INTO t(id, name) VALUES(1, "aaa"),(2, "bbb"),(3, "ccc");
8. 只返回必要的列，用具体的字段列表代替 select * 语句
SELECT * 会增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前者也需要经常更新。所以要求直接在 select 后面接上字段名。
MySQL 数据库是按照行的方式存储，而数据存取操作都是以一个页大小进行 IO 操作的，每个 IO 单元中存储了多行，每行都是存储了该行的所有字段。所以无论取一个字段还是多个字段，实际上数据库在表中需要访问的数据量其实是一样的。
但是如果查询的字段都在索引中，也就是覆盖索引，那么可以直接从索引中获取对应的内容直接返回，不需要进行回表，减少 IO 操作。除此之外，当存在 order by 操作的时候，select 子句中的字段多少会在很大程度上影响到我们的排序效率。
9. 区分 in 和 和 exists select * from 表 A where id in (select id from 表 B)
上面的语句相当于：
select * from 表 A where exists(select * from 表 B where 表 B.id=表 A.id)
区分 in 和 exists 主要是造成了驱动顺序的改变（这是性能变化的关键），如果是 exists，那么以外层表为驱动表，先被
访问，如果是 IN，那么先执行子查询。所以 IN 适合于外表大而内表小的情况；EXISTS 适合于外表小而内表大的情况。
另外，in 查询在某些情况下有可能会查询返回错误的结果，因此，通常是建议在确定且有限的集合时，可以使用 in。如 IN （0，1，2）。
10. 优化 Group By 语句 如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null （group 默认会排序）； 尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary
和 Using filesort ； 如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表； 如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法（直接用磁盘临时表）得到group by 的结果。
使用 where 子句替换 Having 子句：避免使用 having 子句，having 只会在检索出所有记录之后才会对结果集进行过滤，这个处理需要排序分组，如果能通过 where 子句提前过滤查询的数目，就可以减少这方面的开销。
低效: SELECT JOB, AVG(SAL) FROM EMP GROUP by JOB HAVING JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’
高效: SELECT JOB, AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’ GROUP by JOB
11. 尽量使用数字型字段 若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能。引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
12. 优化 Join 语句 当我们执行两个表的 Join 的时候，就会有一个比较的过程，逐条比较两个表的语句是比较慢的，因此可以把两个表中数据依次读进一个内存块中，在 Mysql 中执行：
show variables like ‘join_buffer_size’ ，可以看到 join 在内存中的缓存池大小，其大小将会影响 join 语句的性能。在执行 join 的时候，数据库会选择一个表把他要返回以及需要进行和其他表进行比较的数据放进 join_buffer 。
什么是驱动表，什么是被驱动表，这两个概念在查询中有时容易让人搞混，有下面几种情况，大家需要了解。
1.当连接查询没有 where 条件时 left join 前面的表是驱动表，后面的表是被驱动表 right join 后面的表是驱动表，前面的表是被驱动表 inner join / join 会自动选择表数据比较少的作为驱动表 straight_join(≈join) 直接选择左边的表作为驱动表（语义上与 join 类似，但去除了 join 自动选择小表作为驱动表的特性）
2.当连接查询有 where 条件时，带 where 条件的表是驱动表，否则是被驱动表 假设有表如右边：t1 与 t2 表完全一样，a 字段有索引，b 无索引，t1 有 100 条数据，t2 有 1000 条数据.
牛逼啊！接私活必备的 N 个开源项目！赶快收藏吧
若被驱动表有索引，那么其执行算法为：
Index Nested-Loop Join（NLJ）
，示例如下：
1.执行语句：
select * from t1 straight_join t2 on (t1.a=t2.a)； 由于被驱动表 t2.a 是有索引的，其执行逻辑如下：
从表 t1 中读入一行数据 R； 从数据行 R 中，取出 a 字段到表 t2 里去查找；
取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分； 重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。
如果一条 join 语句的 Extra 字段什么都没写的话，就表示使用的是 NLJ 算法 若被驱动表无索引，那么其执行算法为：
Block Nested-Loop Join（BLJ）
（Block 块，每次都会取一块数据到内存以减少 I/O的开销），示例如下：
2.执行语句：
select * from t1 straight_join t2 on (t1.a=t2.b)； 由于被驱动表 t2.b 是没有索引的，其执行逻辑如下：
把驱动表 t1 的数据读入线程内存 join_buffer （无序数组）中，由于我们这个语句中写的是 select * ，因此是把整个表 t1放入了内存； 顺序遍历表 t2，把表 t2 中的每一行取出来，跟 join_buffer中的数据做对比，满足 join 条件的，作为结果集的一部分返回。
3.另外还有一种算法为 Simple Nested-Loop Join（SLJ）
，其逻辑为：顺序取出驱动表中的每一行数据，到被驱动表去做全表扫描匹配，匹配成功则作为结果集的一部分返回。
另外，Innodb 会为每个数据表分配一个存储在磁盘的 表名.ibd 文件，若关联的表过多，将会导致查询的时候磁盘的磁头移动次数过多，从而影响性能 所以实践中，尽可能减少 Join 语句中的 NestedLoop 的循环次数：“永远用小结果集驱动大的结果集”
用小结果集驱动大结果集，将筛选结果小的表（在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”）首先连接，再去连接结果集比较大的表，尽量减少 join 语句中的 Nested Loop 的循环总次数
优先优化 Nested Loop 的内层循环（也就是最外层的 Join 连接），因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能；
对被驱动表的 join 字段上建立索引；
当被驱动表的 join 字段上无法建立索引的时候，设置足够的Join Buffer Size。
尽量用 inner join(因为其会自动选择小表去驱动大表).避免 LEFT JOIN (一般我们使用 Left Join 的场景是大表驱动小表)和 NULL，那么如何优化 Left Join 呢？
条件中尽量能够过滤一些行将驱动表变得小一点，用小表去驱动大表 右表的条件列一定要加上索引（主键、唯一索引、前缀索引等），最好能够使 type 达到 range 及以上（ref,eq_ref,const,system）
适当地在表里面添加冗余信息来减少 join 的次数
使用更快的固态硬盘
性能优化，left join 是由左边决定的，左边一定都有，所以右边是我们的关键点，建立索引要建在右边。当然如果索引是在左边的，我们可以考虑使用右连接，如下 select * from atable left join btable on atable.aid=btable.bid; -- 最好在 bid 上建索引
Tips：Join 左连接在右边建立索引；组合索引则尽量将数据量大的放在左边，在左边建立索引 索引的优化/ 如何避免索引失效 1. 最佳左前缀法则 如果索引了多列，要遵守最左前缀法则，指...
