SQL语句优化、explain分析

---

目录

前言

一、select 语句执行顺序？

二、MySql性能分析之explain

三、SQL 语句优化

四、SELECT语句优化

五、增删改优化

总结

---

---

前言

Mysql数据库和SQL语句优化：

数据库优化：

• 优化成本：硬件>系统配置>数据库表结构>SQL及索引。
• 优化效果：硬件<系统配置<数据库表结构<SQL及索引。

SQL语句优化：

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

---

一、select 语句执行顺序？

FROM
<表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。

笛卡尔积：先确定数据要用到哪些表，将多个表先通过笛卡尔积变成一个表，然后去除不符合逻辑的数据（根据两个表的关系去掉），最后当做是一个虚拟表一样来加上条件即可
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <join, left join, right join...> 
<join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外
DISTINCT
# 数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>

二、MySql性能分析之explain

1、id：这是SELECT的查询序列号
2、select_type：select_type就是select的类型，可以有以下几种：

• SIMPLE：简单SELECT(不使用UNION或子查询等)
• PRIMARY：最外面的SELECT
• UNION：UNION中的第二个或后面的SELECT语句
• DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
• UNION RESULT：UNION的结果。
• SUBQUERY：子查询中的第一个SELECT
• DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
• DERIVED：导出表的SELECT(FROM子句的子查询)

3、 table：显示这一行的数据是关于哪张表的

4、 type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。

结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

5、possible_keys：列指出MySQL能使用哪个索引在该表中找到行

6、key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL

7、key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好

8、ref：显示使用哪个列或常数与key一起从表中选择行。

9、rows：显示MySQL认为它执行查询时必须检查的行数。

10、filtered:该列表示将被表条件过滤的表行的估计百分比。即rows显示了估计的检查行数, rows*filtered/100 显示与前面的表做join的行数

10、Extra：包含MySQL解决查询的详细信息，也是关键参考项之一。

Record（index map:#）
没有找到理想的索引，因此对于从前面表中来的每一 个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

Using filesort
看 到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来 排序全部行

Using index
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表 的全部的请求列都是同一个索引的部分的时候

Using temporary
看到这个的时候，查询需要优化了。这 里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index， 这就会发生，或者是查询有问题

 转载：https://www.cnblogs.com/Eva-J/articles/10145093.html

---

三、SQL 语句优化

 1.避免在字段开头模糊查询

EXPLAIN SELECT `Sname` FROM student WHERE Sname LIKE '%李'

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

EXPLAIN SELECT `Sname` FROM student WHERE Sname LIKE '李%'

2. 尽量避免使用in 和not in

EXPLAIN SELECT * FROM course WHERE Cno IN (2,3)

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

EXPLAIN SELECT * FROM course WHERE Cno BETWEEN 2 and 3

3.如果是子查询，可以用exists代替：

-- 不走索引
 explain select * from student where student.Sid in (select Sno from sc);
-- 走索引
explain select * from student where exists (select * from sc where sc.Sno = student.Sid);

4. 尽量避免使用 or

EXPLAIN SELECT * FROM Student WHERE Sid = 1 OR Sid = 2

--优化方式 可以用union代替or

explain SELECT * FROM Student WHERE Sid = 1
   UNION
 SELECT * FROM Student WHERE Sid = 3

 5.尽量避免进行null值的判断

 优化方式：可以给字段添加默认值0，对0值进行判断

 6.尽量避免在where条件中等号的左侧进行表达式、函数操作

-- 全表扫描
EXPLAIN SELECT * FROM sc WHERE Grade/10 = 8

-- 走索引
EXPLAIN SELECT * FROM sc WHERE Grade = 10*8

7.当数据量大时，避免使用where 1=1的条件 

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

8. 查询条件不能用 <> 或者 !=

使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

 9. 隐式类型转换造成不使用索引 

索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。 

explain select Sid from student where Sid=1; 

--优化方式：跟索引的类型一致

explain select Cno from course where Cno=1; 

 10. order by 条件要与where中条件一致

explain SELECT * FROM course order by Cno;

--优化方式

explain SELECT * FROM course where Cno>0 ORDER BY Cno

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

第一步：根据where条件和统计信息生成执行计划，得到数据。
第二步：将得到的数据排序。当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则重新进行排序操作。
第三步：返回排序后的数据。
当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。

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

11、采用临时表暂存结果

　　　　采用临时表暂存中间结果好处：

　　　　　　（1）避免程序中多次扫描主表，减少程序执行“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。

　　　　　　（2）尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

　　　　　　（3）避免频繁创建和删除临时表，以减少系统资源的浪费。

　　　　　　（4）尽量避免向客户端返回大数据量，若数据量过大，应考虑相应需求是否合理。

12、尽量用 union add 替换 union

　　　union和union all的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的cpu运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all而不是union

 13、Inner join 和 left join、right join、子查询

　第一：inner join内连接也叫等值连接是，left/rightjoin是外连接。

        　推荐：能用inner join连接尽量使用inner join连接

   第二：子查询的性能又比外连接性能慢，尽量用外连接来替换子查询。

　第三：使用JOIN时候，应该用小的结果驱动大的结果（left join 左边表结果尽量小，如果有条件应该放到左边先处理）

四、SELECT语句优化

1.避免出现select *

• 增加查询分析器解析成本。
• 增减字段容易与 resultMap 配置不一致。
• 无用字段增加网络 消耗，尤其是 text 类型的字段。

• 失去MySQL优化器“覆盖索引”策略优化的可能性

2.避免出现不确定结果的函数

特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句，使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。

3.多表关联查询时，小表在前，大表在后。

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

例如：表1有50条数据，表2有30亿条数据；如果全表扫描表2，你品，那就先去吃个饭再说吧是吧。

4. 使用表的别名

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

5. 用where字句替换HAVING字句

避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。

where和having的区别：where后面不能使用组函数

6.调整Where字句中的连接顺序

MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

五、增删改优化

1. 大批量插入数据

//方法一

insert into T values(1,2); 
 
insert into T values(1,3); 
 
insert into T values(1,4);

//方法二

Insert into T values(1,2),(1,3),(1,4); 

选择后一种方法的原因有三。 

• 减少SQL语句解析的操作，MySQL没有类似Oracle的share pool，采用方法二，只需要解析一次就能进行数据的插入操作；
• 在特定场景可以减少对DB连接次数
• SQL语句较短，可以减少网络传输的IO。

2. 适当使用commit

适当使用commit可以释放事务占用的资源而减少消耗，commit后能释放的资源如下：

• 事务占用的undo数据块；
• 事务在redo log中记录的数据块； 
• 释放事务施加的，减少锁争用影响性能。特别是在需要使用delete删除大量数据的时候，必须分解删除量并定期commit。

3.查询优先还是更新（insert、update、delete）优先

MySQL 还允许改变语句调度的优先级，它可以使来自多个客户端的查询更好地协作，这样单个客户端就不会由于锁定而等待很长时间。改变优先级还可以确保特定类型的查询被处理得更快。我们首先应该确定应用的类型，判断应用是以查询为主还是以更新为主的，是确保查询效率还是确保更新的效率，决定是查询优先还是更新优先。下面我们提到的改变调度策略的方法主要是针对只存在表锁的存储引擎，比如 MyISAM 、MEMROY、MERGE，对于Innodb 存储引擎，语句的执行是由获得行锁的顺序决定的。MySQL 的默认的调度策略可用总结如下：

1）写入操作优先于读取操作。

2）对某张数据表的写入操作某一时刻只能发生一次，写入请求按照它们到达的次序来处理。

3）对某张数据表的多个读取操作可以同时地进行。MySQL 提供了几个语句调节符，允许你修改它的调度策略：

LOW_PRIORITY关键字应用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATE；
HIGH_PRIORITY关键字应用于SELECT和INSERT语句；
DELAYED关键字应用于INSERT和REPLACE语句。

4. 使用truncate代替delete

当删除全表中记录时，使用delete语句的操作会被记录到undo块中，删除记录也记录binlog，当确认需要删除全表时，会产生很大量的binlog并占用大量的undo数据块，此时既没有很好的效率也占用了大量的资源。

使用truncate替代，不会记录可恢复的信息，数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外，使用truncate可以回收表的水位，使自增字段值归零

总结

SQL优化路漫漫，自己悟吧你；

SQL优化最干货总结 - MySQL（2020最新版）__陈哈哈的博客-CSDN博客_sql优化