MySQL执行顺序及优化

概要

本文章主要是分析SQL语句关键字的执行顺序，以及在每一个阶段我们有哪些优化，可以去做哪些优化，和注意事项。

---

1. SQL语句关键字的执行顺序

通常我们执行一条SQL语句它的执行顺序如下

1. from
2. .join
3. where
4. group by
5. having
6. order by
7. 聚合函数
8. limit
9. select

---

2. select关键字

通常我们在写SQL时，大部分都是 select 结果集 from 表的用法去进行使用。那么select有哪些优化和注意事项呢？

优化一:避免使用 select*

在进行查询时，尽可能少使用select * ，如果查询什么字段就返回什么字段，这样第一可以减少我们部分关键信息外露；第二如果表的列很多，也会使我们查询效率变慢。

优化二：避免回表，使用覆盖索引

假如我们建立了（a,b）两个联合索引，那么我们查询时候。可以使用 select a,b,主键 from 进行查询，因为（a，b）联合索引B+树也包括主键索引列。

注意事项：全表扫描
当我们查询数据占总数据 30%时。 MYSQL不会再使用索引，因为使用索引的开销反而更大。

至于第一步为什么是select ，大家可以去看一下这篇文章 MySQL原理

---

3. from、join关键字

当我们使用 join， left join ， right join , straight_join时。我们应该怎么选择谁为驱动表（驱动表就是主表，比如我from t表就为驱动表），谁为被驱动表呢（被驱动表就是被join 的表）？

优化一:小表驱动大表*
在使用join进行关联时，我们应该用小表去驱动大表。其实原理也很简单，比如表A有100条数据，表B有20条数据。如果表A去驱动表B。这时候我们查找可能最好情况（最好情况就是假如两个表都有索引，然后每次在索引第一列找到对应关联数据）也就是100次。如果是表B去驱动表A最好情况也就是20次，相对来说就会提升很多性能。

优化二：关联字段建立索引
我们在使用join进行关联时，我们被驱动表的关联字段应建立索引。其实也是和上面举例差不多，因为我们MySQL的B+树是有序的，而且查找很快。建立索引关联时，就可以通过索引块速找到对应字段组成结果集。

优化三：调整join_buffer大小
join_buffer大家可以理解为，在一个MySQL中有一个加工厂，它是专门负责把两个表的数据进行关联，然后返回结果集的加工厂。

现在假如一个场景？ join_buffer每次只能放入t1表60%的数据。

1.扫描表 t1，顺序读取数据行放入 join_buffer 中，在放入60%数据后join_buffer 满了，
2. 扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；
3. 清空 join_buffer；继续扫描表 t1，顺序读取最后的 12 行数据放入 join_buffer 中
4. 扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；
在上述例子中，我们发现t1表扫描了两次。t2表也扫描了两次。根据我们MySQL的LRU算法

处于 old 区域的数据页，每次被访问的时候都要做下面这个判断：

1.若这个数据页在 LRU 链表中存在的时间超过了 1 秒，就把它移动到链表头部；

2.如果这个数据页在 LRU 链表中存在的时间短于 1 秒，位置保持不变。1 秒这个时间，是由参数 innodb_old_blocks_time 控制的。其默认值是 1000，单位毫秒

由于优化机制的存在，一个正常访问的数据页，要进入 young 区域，需要隔 1 秒后再次被访问到。在上述例子中，如果我们join_buffer更小，被驱动表就会被多次扫描，而且这个语句执行时间超过 1 秒，就会在再次扫描t2表的时候，把t2表的数据页移到 LRU 链表头部。这时我们Buffer Pool 的热数据就会被淘汰，影响内存命中率。

优化四：Hashjoin
1.如果我们把驱动表1000条数据存人map，key是字段对应的值。value就是这行数据的值。
2.在把100w条数据读出来，循环遍历这100w条数据。
3.在从100w条数据的每一行拿出对应key字段的值，如果key拿出来有值，就把这行数据结果存入map并返回。
这样我们10亿次比对就会变成100次hash查找，效率会提升很多。如果写的SQL实在没有办法建立索引，并且查询很慢。可以试试用业务代码去实现。

注意事项：
以在MySQL8.0之后版本，MySQL就加入了hash join。如果大家还不理解，可以尝试看看这篇文章MySQL的join你真的了解吗！！！

---

4. where关键字

优化一：检索范围大的条件放前面
在MySQL当中，其条件执行顺序是 从左往右，自上而下；所以在数据多的情况下检索范围大的应放前面执行。

优化二：where 1=1怎么优化
在平时我们使用mybatis时，因为使用动态SQL。在多个条件下，无法判断哪一个会先执行，往往会先执行1=1，在大数据执行情况下会消耗MySQL的性能，在使用mybatis时，可以用where标签代替。

select  id
from user
<where>
<if test="username !=null and username !='' ">
 u.username = #{username} 
</if>
</where> 

优化三：索引失效

1.最左前缀原则

我们建立索引（sex,age,phone）联合索引时，也就相当于我们建立了（sex）单独索引，（sex,age）联合索引，（sex，age，phone）联合索引

SELECT * FROM user where age="1"; #未使用索引
SELECT * FROM user where phone="3"; #未使用索引
SELECT * FROM user where sex="0" and age="5"; #使用索引
SELECT * FROM user where sex="1" and age="3" and phone="4"; #使用索引
SELECT * FROM user where age="1" and phone="4";  #未使用索引
SELECT * FROM user where sex="2" and phone="4";  #使用索引 （只用了sex索引）
SELECT * FROM user where age="1" and sex="2"; #使用索引（MySQL的优化器帮我们做了处理）

但我们在where后面写条件语句时，一定要满足最左前缀原则。否则索引会失效。

2.字符串不加单引号索引会失效

假如数据库有一个字段name是varchar类型，这时我们 where name=张三。这时是不会走索引的，需要写成name=’张三‘。而int数据类型不会受影响。

3.mysql使用不等于(!= 或者<>)的时候，无法使用索引，会导致索引失效

SELECT * FROM tb_user_copy1 where age >=5

4.where 子句里对有索引列使用函数，用不上索引

SELECT * FROM tb_user_copy1 where ABS(age) =5

5.where中索引列有运算

explain SELECT * FROM tb_user_copy1 where age *2 =10

6.is null可以走索引，is not null无法使用索引

SELECT * FROM tb_user_copy1 where name is null

7.条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

具体可以参考这篇文章进行了解一篇文章了解Like用法及常见索引失效情况

---

5.group by、having关键字

where和having区别

where子句将单个行过滤到查询结果中，而having子句将分组过滤到查询结果中 having子句中使用的列名必须出现在group by子句列表中，或包括在聚集函数中。

having子句的条件运算至少包括一个聚集函数，否则可以把查询条件移到where字句中来过滤单个行（注意聚集函数不可以用在where子句中）

通常我们使用group by如果需要进行条件检索时，一般都会配合having一起去使用。那么group by不就是进行分组吗，这个有什么优化？

优化一：group by字段建立索引
大家有没有试过，在MySQL8.0之前。当你执行group by语句时，默认分组查询的数据会是有序的？这是因为在MySQL8.0之前使用group by会默认进行排序，所以当我们在分组字段建立索引后，就无需进行排序。因为B+树相比于B树最显著的特征就是B+树叶子节点是一个有序的双向列表。既然他已经有序了，那么我们是不是可以直接不进行排序了。

优化二：使用order by null
如果你分组查询的数据无需排序时，这个时候MySQL又要强制给你排序，该怎么办？ 这时候你就可以使用order by null，告诉MySQL我不需要进行排序。

优化三：使用SQL_BIG_RESULT
如果可以通过加索引来完成 group by 逻辑就再好不过了。但是，如果碰上不适合创建索引的场景，我们还是要老老实实做排序。这个时候就可以使用SQL_BIG_RESULT告诉MySQL你给我使用磁盘临时表进行排序.
（因为排序先会用内存临时表，内存临时表不够用才会使用磁盘临时表）

如果我们明明知道，一个 group by语句中需要放到临时表上的数据量特别大，却还是要按照“先放到内存临时表，插入一部分数据后，发现内存临时表不够用了再转成磁盘临时表”，这样看上去就有点儿傻。

SELECT  SQL_BIG_RESULT b, count(*) as c from t1 GROUP BY b HAVING b<100

注意事项一： Using temporary 和 Using filesort

Using temporary; 表示使用了临时表；
Using filesort ，表示需要排序。
在查询时，可以通过explain来查看是否使用临时表，是否需要排序

注意事项二：group by 在 MySQL5.7 版本会自动排序，但是在MySQL8 .0之后版本就去掉了排序功能。

具体大家可以参考这篇文章进行了解一篇文章了解MySQL的group by

---

6.order by关键字

order by是我们常用的排序字段，但是我们使用order byMySQL就一定会进行排序吗？

优化一：排序字段加索引
如果我们排序字段有索引，根据B+树的特点：B+树叶子节点是一个有序的双向列表。既然他已经有序了，那么使用order by是不是就可以不用进行排序了。当然如果排序字段过多，可以采用联合索引进行排序。

优化二：调整sort_buffer大小
如果我们排序字段无法建立索引，这个时候应该这么优化？

sort_buffer_size：就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

而使用磁盘临时文件辅助排序是非常消耗性能的。（在数据多的情况下，就会分为多个磁盘临时文件排序。最后在把多个磁盘临时文件进行归并返回）如果查询数据量过大，不考虑MySQL内存大小时。我们可以适当调整sort_buffer_size的大小，让MySQL尽量不使用磁盘临时文件进行辅助排序，而使用sort_buffer进行全字段排序。

优化三：调整max_length_for_sort_data大小
还是和上面情况一样，无法建立索引。

max_length_for_sort_data表示MySQL用于排序行数据的长度的一个参数，如果单行的长度超过这个值，MySQL 就认为单行太大，就换rowid 排序

怎么计算单行长度？
单行长度是根据你查询的数据大小
比如我的数据库表结构 first_name字段varchar（10）、 last_name字段varchar（10）
如果我 SQL查询first_name，last_name两个字段，那么我的行数据大小就是20.
而全字段排序和rowid 排序区别就在于，rowid排序需要进行回表操作。

注意事项：
1.如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

2.如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

具体大家可以参考这篇文章进行了解一篇文章搞懂MySQL的order by

---

7.聚合函数

group by的常规用法是配合聚合函数，利用分组信息进行统计，常见的是配合max等聚合函数筛选数据后分析，以及配合having进行筛选后过滤。
聚合函数：

• count()，返回指定列中数据的个数
• sum()，返回指定列中数据的总和
• avg()，返回指定列中数据的平均值
• min()，返回指定列中数据的最小值
• max()，返回指定列中数据的最大值

我们常用的的聚合函数就如上所示，sum()、avg()、min()、max()这四个其实他们性能上，MySQL已经进行最优调整了，在这里我就不在概述了。重点就给大家讲一下count()的优化。

Count（*）为什么会很慢
首先理解下为什么count(*)会很慢，因为我们MySQL的innodb引擎它支持mvcc，所以我们在多个事务情况下，要保证数据一致性的话。Inndb就只能对查询结果进行一条条的统计。

优化一：redis进行计数
在每次新增数据时，就在redis当中进行加1，每删除一行就减1。理想上是可以的，但是也会存在问题

问题一：如果redis荡机，异常重启怎么办？
reids重启了，可能会导致数据进行丢失。但是我们可以在重启过程中，在去数据库查询一遍总数进行存储，这样也是可以的。

问题二：高并发情况下，怎么保证一致性
比如在高并发下，两个线程同时进行，就会出现不一致的问题。

1.这里主要原因是因为“MySQL插入一行数据”跟“Redis计数加1”这两个操作是分开的，不是原子性的，这就很可能在中间过程因为某些并发出现问题。

2.更抽象一点：MySQL和Redis是两个不同的载体，将关联数据记录到不同的载体，而不同载体要实现原子性很难，由于不是原子性很容易引起并发问题。

3.如果能将数据统一在同个载体即MySQL，并由其保证操作的原子性，即将插入一行数据和计数加1作为一个完整的事务，通过事务的隔离此时外界看到的就是要么全部执行完毕要么全部都没执行，进而保持逻辑一致。

优化二：在数据库保存计数
把总数存储在另外一张表中，新增就+1，减少就-1

问题一：MySQL荡机了怎么办
因为MySQL有我们的binlog日志和redlog日志，他可以保证我们MySQL即使荡机了，数据也不会丢失。通过两阶段提交保证两个日志逻辑上的一致性。

问题二：高并发情况下，怎么保证一致性
将计数器的修改和数据的写表在一个事务中。读取计数器和查询最近数据也在一个事务中。因为在事务可重复读（repeatable read）中，别人改数据的事务已经提交，我在我的事务中也不去读。

可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

注意事项：Select count(*) 、count(主键)、 count(字段)、count(1) 谁更快

count(主键 id)

InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id后。因为主键id判断是不可能为空的，就按行累加。

count(1)

InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

count(字段)

如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；

如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

count(*)

首先count（）肯定是不为null的，所以他不用进行非空判断，并且MySQL已经对count()进行优化了。

总体性能排序
按照效率排序的话，count(字段)<count(主键id)<count(1) 约等于 count(*)。因为mysql对count(*)有优化，认为是取行数，不需要把字段取出来

具体大家可以参考这篇文章进行了解MySQL(Select count(*))为什么这么慢！！！

---

8.limit的优化

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0。

select * from user limit 10, 20   耗时：0.013秒
select * from user limit 100, 20  耗时：0.013秒
select * from user limit 1000, 20  耗时：0.037秒
select * from user limit 10000, 20  耗时：0.089秒 
select * from user limit 600000, 20  耗时：5.251秒 
select * from user limit 1200000, 20  耗时：20.69秒 

当一个数据库表过于庞大，LIMIT offset, length中的offset值过大，则SQL查询语句会非常缓慢，你需增加order by，并且order by字段需要建立索引。

select * FROM user WHERE id > =(select id from user limit 1200000, 1) limit 20 

这个时候你可以用主键索引去进行覆盖索引进行子查询。

---