mysql优化——4.limit,join,in(exsits)及count优化

叁歲伎倆 2024-04-17 06:00 33阅读 0赞

## limit,join,in(exsits)及count优化 ##

每天多学一点点~  
话不多说，这就开始吧…进击的爆裂无球~

### 文章目录 ###

*   *  limit,join,in(exsits)及count优化
     *  1.前文
     *  2.准备工作
     *  3.limit优化
     *  4.Join关联查询优化
     *  5.in和exsits优化
     *  6.count查询优化
     *  7.结语

## 1.前文 ##

承接上文 [mysql优化——3.trace工具以及排序优化(order by 以及文件排序filesort)][mysql_3.trace_order by _filesort] ，今天再来研究研究其他sql语句的优化方式。

## 2.准备工作 ##

执行下列sql

employees表结构和上文一样，往里面插入1000条数据。
    DROP PROCEDURE IF EXISTS insert_emp; 
    delimiter ;;
    CREATE PROCEDURE insert_emp ()
    BEGIN
    	DECLARE
    		i INT ;
    	SET i = 1 ;
    	WHILE (i <= 1000) DO
    		INSERT INTO employees (NAME, age, position)
    	VALUES
    		(CONCAT('baoliewuqiu', i), i, 'dev') ;
    	SET i = i + 1 ;
    	END WHILE ;
    END;; 
    delimiter ; 
    CALL insert_emp ();   ---调用存储过程
    
    
    创建t1表，往里面插入1万条数据（插入数据省略）
    CREATE TABLE `t1` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `a` int(11) DEFAULT NULL, 
    `b` int(11) DEFAULT NULL, 
    PRIMARY KEY (`id`), 
    KEY `idx_a` (`a`) 
    ) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;
    
    create table t2 like t1;   ----创建t2表
    
    INSERT INTO t2 SELECT * FROM t1 LIMIT 0,100   ---从t1表中复制100条数据到t2

## 3.limit优化 ##

很多时候我们业务系统实现分页功能可能会用如下sql实现

select * from employees limit 900,10;

表示从表 employees 中取出从 901 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 910 条记录，然后抛弃前 900 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。

**常见的分页场景优化技巧：**

*  **根据自增且连续的主键排序的分页查询**  
    select \* from employees limit 900,5;  
    ![在这里插入图片描述][20190825174054750.png]  
    ![在这里插入图片描述][20190825174416852.png]  
    该 SQL 表示查询从第 900开始的五行数据，没添加单独 order by，表示通过主键排序。我们再看表 employees ，因 为主键是自增并且连续的，所以可以改写成按照主键去查询从第 900开始的五行数据，如下：  
    select \* from employees where id > 900 limit 5;  
    ![在这里插入图片描述][20190825174150397.png]  
    ![在这里插入图片描述][20190825174439459.png]结果是一样的，但是看查询计划，显然改写后的 SQL 走了索引，而且扫描的行数大大减少，执行效率更高。  
    **但是**，这条 改写的SQL 在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致，如下图试验 所示（随便删除前面的一条记录，然后再测试原 SQL 和优化后的 SQL）：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQzNzYzMw_size_16_color_FFFFFF_t_70]  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQzNzYzMw_size_16_color_FFFFFF_t_70 1]  
    两条 SQL 的结果并不一样，因此，如果主键不连续，不能使用上面描述的优化方法。  
    另外如果原 SQL 是 order by 非主键的字段，按照上面说的方法改写会导致两条 SQL 的结果不一致。所以这种改写得满 足以下两个条件：
    
    1.  主键自增且连续
    2.  结果是按照主键排序的
 *  **根据非主键字段排序的分页查询**  
    再看一个根据非主键字段排序的分页查询，SQL 如下：  
    select \* from employees ORDER BY name limit 900,5;  
    ![在这里插入图片描述][20190825174946252.png]  
    EXPLAIN select \* from employees ORDER BY name limit 900,5;  
    ![在这里插入图片描述][20190825175011500.png]  
    发现并没有使用 name 字段的索引（key 字段对应的值为 null），具体原因上篇博客（传送门： [mysql优化——3.trace工具以及排序优化(order by 以及文件排序filesort)][mysql_3.trace_order by _filesort] ）讲过：**扫描整个索引并查找到没索引 的行(可能要遍历多个索引树)的成本比扫描全表的成本更高**，所以优化器放弃使用索引。 知道不走索引的原因，那么怎么优化呢？  
    其实关键是**让排序时返回的字段尽可能少**，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL 改写如下：  
    select \* from employees e inner join (select id from employees order by name limit 900,5) ed on e.id = ed.id;  
    ![在这里插入图片描述][20190825175515871.png]  
    需要的结果与原 SQL 一致，执行时间减少了一半以上，并且原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序。

## 4.Join关联查询优化 ##

**mysql的表关联常见有两种算法**

*  Nested-Loop Join 算法
 *  Block Nested-Loop Join 算法

1.  **嵌套循环连接 Nested-Loop Join(NLJ) 算法**  
    一次一行循环地从第一张表（称为**驱动表，也是小表**）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（**被驱动表，即大表**）里取出满足条件的行，然后取出两张表的结果合集。  
    EXPLAIN select\*from t1 inner join t2 on t1.a= t2.a; （索引关联）  
    ![在这里插入图片描述][20190825175800593.png]  
    **从执行计划中可以看到这些信息：：**  
    1 驱动表是 t2，被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；优 化器一般会优先选择小表做驱动表。**所以使用 inner join 时，排在前面的表并不一定就是驱动表。**  
    2 使用了 NLJ算法。一般 join语句中，如果执行计划 Extra 中未出现 **Using join buffer** 则表示使用的 join 算 法是 NLJ。
    
    **上面sql的大致流程如下：**
    
    1 从表 t2 中读取一行数据；  
    2 从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；  
    3 取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；  
    4 重复上面 3 步。  
    整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表 中的对应行(**扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100 行ps:因为走的是索引数，所以时间可以忽略不计**)。因此整个过程扫描了 200 行。 如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低(下面有详细解释)，mysql会选择Block Nested-Loop Join 算法。
2.  **基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法**  
    把**驱动表**的数据读入到 **join\_buffer** 中，然后扫描**被驱动表**，把**被驱动表**每一行取出来跟 join\_buffer 中的数据做对比。  
    EXPLAIN select\*from t1 inner join t2 on t1.b= t2.b;  
    ![在这里插入图片描述][20190825181618100.png]Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 **BNL** 算法。  
    **上面sql的大致流程如下：**
    
    1 把 t2 的所有数据放入到 join\_buffer 中  
    2 把表 t1 中每一行取出来，跟 join\_buffer 中的数据做对比  
    3.返回满足 join 条件的数据  
    整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为**10000**(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。并且 join\_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 \* 10000= **100万次**。  
    被驱动表的关联字段没索引为什么要选择使用 **BNL** 算法而不使用 **Nested-Loop Join** 呢？ 如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 \* 10000 = 100万次，这个是磁盘扫描。放入join\_buffer中，可以当作内存操作，速度比磁盘块多了。

因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有 索引的情况下 NLJ 算法比 BNL算法性能更高。

**对于关联sql的优化**

*  **关联字段加索引**，让mysql做join操作时尽量选择NLJ算法。
 *  **小标驱动大表**，写多表连接sql时如果**明确知道**哪张表是小表可以用**straight\_join**写法固定连接驱动方式，省去 mysql优化器自己判断的时间。

那么问题来了，straight\_join又是啥？  
**straight\_join解释**：straight\_join功能同join类似，但能让左边的表来驱动右边的表，能改表**优化器**对于联表查询的执行顺序。举个例子，如下：  
比如：select \* from t2 straight\_join t1 on t2.a = t1.a; 代表制定mysql选着 t2 表作为驱动表。

*  **straight\_join**只适用于**inner join**，并不适用于**left join**，**right join**。（因为left join，right join已经代表指 定了表的执行顺序）
 *  尽可能让**优化器**去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight\_join一定要慎重，因 为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

## 5.in和exsits优化 ##

上面已经讨论了，小表驱动大表（即小的数据集驱动大的数据集），下面我们来研究额下in和exsits

*  in：当B表的数据集小于A表的数据集时，in优于exists

select * from A where id in (select id from B) 
    #等价于： 
     for(select id from B){ 
     	select * from A where A.id = B.id 5
      }

*  exists：当A表的数据集小于B表的数据集时，exists优于in

select * from A where exists (select 1 from B where B.id = A.id) 
    #等价于: 
     for(select * from A){ 
     	select * from B where B.id = A.id 
      } 
      #A表与B表的ID字段应建立索引

1.  EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的**SELECT \*\*\* 也可以用**SELECT 1\*\*替换,官方说法是实际执行时会 忽略SELECT清单,因此没有区别
2.  EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
3.  EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析

## 6.count查询优化 ##

不知道小伙伴们有没有这个感受，公司的dba建议我们写count语句时候，用count(1)代替count(\*),说是count(1)查询速度快。那么今天博主就来验证一下，事实是否如此。

# 临时关闭mysql查询缓存，为了查看sql多次执行的真实时间
    set global query_cache_size=0; 
    set global query_cache_type=0;

下面我们来对比下这四条sql语句

1.  EXPLAIN select count(1) from employees; —这没啥好说的，工作中用的最多的
2.  EXPLAIN select count(id) from employees; —id是主键，自增
3.  EXPLAIN select count(name) from employees; —name是联合所以(name, age, position)中的一部分
4.  EXPLAIN select count(\*) from employees; —dba叫我们不要用这个

![在这里插入图片描述][20190825183745913.png]  
四个sql的执行计划一样，说明这四个sql执行效率应该差不多，区别在于根据某个字段count(**如count(name)**)不会统计字段为null值的数据行，所以一般建议表中**不要有null值**，可随意设置个默认值。  
那么小伙伴们有没有疑问，**为何mysql最终选择辅助索引而不是主键聚集索引？**（**这里再重申一次**，博主表中没有创建主键索引，但是InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引，InnoDB就会定义隐藏的主键然后在上面进行聚集。 所以，对于 聚集索引 来说，你创建主键的时候，自动就创建了主键的聚集索引。）

其实小伙伴们可以自己看一下博主mysql优化第一篇博文（传送门：[mysql优化——1.索引底层原理][mysql_1.]），对于innodb引擎表来说，主键索引叶子节点存储的是一行数据，而辅助索引存储的是主键的id，**二级索引相对主键索引存储数据更少，检索性能应该更高。**

**常见优化方法**

1.  查询mysql自己维护的总行数  
    对于**myisam**存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被 mysql存储在磁盘上，查询不需要计算  
    ![在这里插入图片描述][20190825185144808.png]  
    对于**innodb**存储引擎的表mysql不会存储表的总记录行数，查询count需要实时计算
2.  show table status  
    如果只需要知道表总行数的估计值可以用如下sql查询，性能很高·  
    ![在这里插入图片描述][20190825185302546.png]
3.  将总数维护到Redis里  
    插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难 保证表操作和redis操作的事务一致性
4.  增加计数表  
    插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作

## 7.结语 ##

**世上无难事，只怕有心人，每天积累一点点，fighting！！！**

[mysql_3.trace_order by _filesort]: https://blog.csdn.net/weixin_42437633/article/details/100026188
[20190825174054750.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/d205606973c5469cac041f2783ddb838.png
[20190825174416852.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/6c1c8814c9c04d6b9fd1b92b9c8cb9dd.png
[20190825174150397.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/b6653cdb2dac406da8f3b58e029bed47.png
[20190825174439459.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/e25a79ddf26948acbcaf8ebd975b4237.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQzNzYzMw_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/ad156509b84a407fb69155c46006ff58.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQzNzYzMw_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/d1a33516ab884e58b9cde245376ce936.png
[20190825174946252.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/a0515938c537498187689a3ef1f48136.png
[20190825175011500.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/3d5dde4daba24b259c06cc87ade05a59.png
[20190825175515871.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/f7aaac904041480a98e84af951e28938.png
[20190825175800593.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/2399f6edf0f5401ab29b443a80d9731a.png
[20190825181618100.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/a45a1512df7c48a48f6a3dae98bf78c8.png
[20190825183745913.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/a24a8fb7c4614145a7879b44a2db212b.png
[mysql_1.]: https://blog.csdn.net/weixin_42437633/article/details/88652445
[20190825185144808.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/f2b2a7c9ab8c42ebaf06e44171f223a4.png
[20190825185302546.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/bb0d8cd10af94d78939b34accf9cd480.png