MySql语句优化总结以及解析验证，超详细（二）

最新推荐文章于 2022-09-17 15:17:53 发布

Fly丶X

最新推荐文章于 2022-09-17 15:17:53 发布

阅读量221

点赞数

分类专栏： javaEE mysql 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/weixin_44947701/article/details/121043203

版权

javaEE 同时被 2 个专栏收录

21 篇文章 2 订阅

订阅专栏

mysql

5 篇文章 0 订阅

订阅专栏

接着上一篇，上篇主要分享了sql优化的技巧，这些在我们平时写sql养成这样的习惯即可，当我们需要对慢sql分析的时候，我们要如何sql语句判断是否命中索引以及执行过程中那部分瓶颈？
答案是使用sql执行计划分析，这里利用mysql的关键字explain来实现

1. sql执行计划

执行计划简单来说就是把sql在数据库中执行的一步一步的过程的信息列出来，在mysql中使用explain关键字查看sql执行计划
例如：查询
SELECT good_id,good_code,title,sku_title,sub_title,cname1,
cname2,cname3,brand_name,saleable,images,price
FROM good
WHERE cname3 = ‘智能手机’
它的执行计划如图：在这里插入图片描述
这里每一列的信息具体可以参考这篇文章，这里就不一一介绍；
我们这里主要讲type，key，key_len，rows这几列，而sql执行计划的分析也是主要参考这几列的信息

type：表示该sql用到了哪种查询的类型，这一列最直观的反映sql执行效率，有以下几种类型，从上到下效率一次减慢

system：表只有一行：system表。这是const连接类型的特殊情况。
const：表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待。
eq_ref：在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用。
ref：这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好。
range：这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况。
index：这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）。
ALL：这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免。

key：表示该sql执行中用到了哪些的索引，如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
rows：查询sql执行结果记录总共扫描记录数，扫描的记录数越少则效率越高

1.1 sql执行计划分析举例

我们看下面这条sql的执行计划

SELECT good_id,good_code,title,sku_title,sub_title,cname1,
       cname2,cname3,brand_name,saleable,images,price
FROM `good` 
WHERE brand_name = 'TCL'

查询结果：
在这里插入图片描述

执行计划：
在这里插入图片描述
从执行计划中可以看到key一列的值为good_index_brand，即使用到了名称为good_index_brand的索引，该索引正是brand字段上建立的；rows的值为7392，而从查询结果中可以看到一共查询出来了7392条记录，这说明这条sql执行中只扫描了符合条件的记录并返回，所以该条sql执行速度比较快，从查询结果上看出只用了0.078s

再看这一条sql：

SELECT good_id,good_code,title,sku_title,sub_title,cname1,
       cname2,cname3,brand_name,saleable,images,price
FROM `good` 
WHERE brand_name LIKE '%CL%'

查询结果：
在这里插入图片描述

执行计划：
在这里插入图片描述
从执行计划中可以看到key一列的值为null，即没有用到任何索引，虽然brand_name列上建立了索引，但是由于使用全模糊查询使得brand_name索引失效，所以rows达到了1148517，扫描了整张表的记录，整条sql执行时间也达到了0.642s，和上面一条sql相比，虽然查询的记录数相同，但是效率差了很多，这也体现了索引能够大幅度提升效率的特性

综合上述，当我们需要对sql进行优化时，首先rows尽可能接近实际查询记录，其次是ken_len越短越好，再次是type尽量不要让sql执行type类型，尽可能地优化到ref类型，如果index类型也尽可能再优化

2. in和exists合理使用

我们写sql常常会用到in或exists关键字来进行范围查询，那么这两者有什么区别呢？
在mysql5.7以前，in是不走索引的，而exists一直都会走索引，所以网上可以看到说sql优化尽量不要用in关键字；但5.7版本以后in也会走索引了，那么in或exists是否效果差不多？答案是需要合理的使用

假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下SQL:

select * from A where deptId in (select deptId from B);

这样写等价于：

先查询部门表B
select deptId from B
再由部门deptId，查询A的员工
select * from A where A.deptId = B.deptId
可以抽象成这样的一个循环：

List<> resultSet ;        
for(int i=0;i<B.length;i++) {
  for(int j=0;j<A.length;j++) {
    if(A[i].id==B[j].id) {    
      resultSet.add(A[i]);  
      break;          
    }  
  }
}

我们也可以用exists实现一样的查询功能，如下：

select * from A where exists (select 1 from B where A.deptId = B.deptId);

因为exists查询的理解就是，先执行主查询，获得数据后，再放到子查询中做条件验证，根据验证结果（true或者false），来决定主查询的数据结果是否得意保留。

那么，这样写就等价于：

select * from A		/*先从A表做循环*/
select * from B where A.deptId = B.deptId	/*再从B表做循环.*/

同理，可以抽象成这样一个循环：

List<> resultSet ;        
for(int i=0;i<A.length;i++) {
  for(int j=0;j<B.length;j++) {         
    if(A[i].deptId==B[j].deptId) {      
      resultSet.add(A[i]); 
      break;      
    } 
  }
}

从抽象循环中可以看出两个关键字查询原理都是采用双层循环，并且内层循环会匹配到符合的记录结束本次循环；不同的是in是外层查询在外层的的循环中，in的括号里的条件在内层循环，而exists正好反过来；这样该如何合理运用，其实类似sql优化的小表驱动大表的优化策略，即在抽象循环中记录数少的查询放外层循环，记录数多查询的放内层循环
因此，我们要选择最外层循环小的，也就是，如果B的数据量小于A，适合使用in，如果B的数据量大于A，即适合选择exist。如果两者数据量相差不大则就不用选择根据自己习惯运用

3. mysql分页查询优化

为了验证结果更显著，接着上一篇的good表，我们在cname1上建立一个索引
在这里插入图片描述
我们来看这两条sql：

/*查询第一页的数据*/
SELECT * FROM `good` WHERE cname1 IN ('电脑办公','生鲜','整车','家用电器','数码','运动健康','汽车用品','玩具乐器') 
LIMIT 0, 10

/*查询第50000页的数据*/
SELECT * FROM `good` WHERE cname1 IN ('电脑办公','生鲜','整车','家用电器','数码','运动健康','汽车用品','玩具乐器') 
LIMIT 500000, 10

执行时间：
在这里插入图片描述

从图上可以看出第二条sql明显比第一条慢，那么为什么同一条sql，当对结果进行分页的时候，页数越大执行越慢呢？这原因也很简单，因为在查询后面的页的记录时会把前面的记录也扫描一遍，随着页数的增大扫描的记录也越多，效率也越来越慢，此操作可以抽象为我们在翻书找需要的信息，从第一页开始一页一页找，如果我们需要信息越在靠后的页数，我们查找到需要的信息花的时间就更多

那么分页查询是否能够优化？答案是可以的，在特定的查询方式中可以优化，比如看以下两条sql：

/*第一条sql*/
SELECT * FROM `good` WHERE cname1 
IN ('电脑办公','生鲜','整车','家用电器','数码','运动健康','汽车用品','玩具乐器') 
LIMIT 500000, 10

/*第二条sql*/
SELECT t.* FROM good t
JOIN (
	SELECT good_id FROM good WHERE 
	cname1 IN ('电脑办公','生鲜','整车','家用电器','数码','运动健康','汽车用品','玩具乐器') 
	LIMIT 500000, 10) tt
ON t.good_id = tt.good_id

执行时间：
在这里插入图片描述
两条sql我们可以看出都是分页查询查询50000页的数据，查询结果也相同，只是写法不同而已，但是从执行时间来看，第二条远比第一条快，这是为什么呢？答案是因为第二条利用了覆盖索引的优化策略，关于覆盖索引，可以参考我之前写的这篇文章的覆盖索引部分。

该优化手段适用于以下分页查询写法

/*优化前sql*/
select 各种字段 from `table_name` 
where 各种条件（必须有且只命中普通索引）
limit startIndex, endIndex

/*优化后sql*/
select 各种字段 from `table_name` t
right join 
(
	select 子查询只查主键
	from `table_name`
	where 各种条件（必须有且只命中普通索引）
	limit startIndex, endIndex
）tt on t.主键 = tt.主键

这其中优化原理是利用了覆盖索引的手段，由于只命中了普通索引，在mysql中普通索引都是非聚簇索引，整个查询可以抽象理解为，先根据普通索引查询到所有符合条件的记录所在行的主键（如果没有主键，msql会自动给每一条记录创建唯一标识，这时就是拿到该行的唯一标识），再根据主键索引再回表查询整行的各个需要返回的字段，整个过程大致如图所示：
在这里插入图片描述
而第二条sql先做子查询查询符合条件指定分页所有记录的主键，再根据主键关联本表返回需要返回记录的指定的字段，效率有了明显的提升，这其中原因结合上面第一条sql大致查询的过程我们不难看出，查询主键的子查询相当于上面图上的第二轮索引树查询，在这一步就会取指定分页的主键，再根据这些主键查询返回指定的记录，相比第一条sql在第二轮索引树查询把指定分页以及该分页之前的记录的主键都拿过来再根据主键查询每行需要返回的字段再筛选指定分页的记录返回（即排在最后多少行的记录返回），效率自然有了明显的提升，并且分页越靠后效率提升越明显，整个过程大致如图所示：
在这里插入图片描述

Fly丶X

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySql语句优化总结以及解析验证，超详细（二）

接着上一篇，上篇主要分享了sql优化的技巧，这些在我们平时写sql养成这样的习惯即可，当我们需要对慢sql分析的时候，我们要如何sql语句判断是否命中索引以及执行过程中那部分瓶颈？答案是使用sql执行计划分析，这里利用mysql的关键字explain来实现1. sql执行计划执行计划简单来说就是把sql在数据库中执行的一步一步的过程的信息列出来，在mysql中使用explain关键字查看sql执行计划例如：查询SELECT good_id,good_code,title,sku_title,su
复制链接

扫一扫