Mysql 竟然还有这么多不为人知的查询优化技巧，还不看看？

你的名字 2023-09-24 23:04 130阅读 0赞

## 前言 ##

[Mysql 我随手造200W条数据，给你们讲讲分页优化][Mysql _200W]  
  
[MySql 索引失效、回表解析][MySql]

今天再聊聊一些我想分享的查询优化相关点。

##    **正文** ##

准备模拟数据。

首先是一张 test\_orde 表：

CREATE TABLE `test_order` (
    	`id` INT(11) NOT NULL AUTO_INCREMENT,
    	`p_sn` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    	`t_sn` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    	`type` TINYINT(4) NULL DEFAULT NULL,
    	`create_time` DATETIME NULL DEFAULT NULL,
    	PRIMARY KEY (`id`) USING BTREE
    )

然后是一个存储过程 ：

BEGIN                                
        DECLARE num INT DEFAULT 2000000;         
        DECLARE i INT DEFAULT 0; 
        
        WHILE i < num DO             
        INSERT INTO test_order(`p_sn`,`t_sn`,`type`,`create_time`) 
             VALUES(CONCAT('SN',i),UUID(),1,now());
            SET i =  i + 1;
        END WHILE;        
    END

执行存储过程，看下模拟数据：

![628c6d7924394579ab958d82efcfd1ca.png][]

开始。

#### **① 使用 count 、 group by 注意点** ####

比如， 我们想统计一下 当前 表里面， 根据type维度 分别有多少 数据 ：

> SELECT COUNT(\*) ,type  
> FROM test\_order GROUP BY TYPE ;

目前可以看到我们现在数据库表 里面，其实type 就 1个 ， 就是 1 。

真实场景，我们 肯定不止一个type。

**改造出模拟数据(尽量使数据更随机，真实业务场景也许会更加更加散乱)：**

将数据里面 id 是 7的 倍数的数据 的type 改成 5；

将数据里面 id 是 5 的 倍数的数据 的type 改成 2；

将数据里面 id 是 3 的 倍数的数据 的type 改成 4；

将数据里面 id 是 2 的 倍数的数据 的type 改成 3；

sql：

> UPDATE test\_order a  
> INNER JOIN test\_order b ON b.id % 7=0 AND a.id=b.id SET a.TYPE =5

> UPDATE test\_order a  
> INNER JOIN test\_order b ON b.id % 5=0 AND a.id=b.id SET a.TYPE =2

> UPDATE test\_order a  
> INNER JOIN test\_order b ON b.id % 3=0 AND a.id=b.id SET a.TYPE =4

> UPDATE test\_order a  
> INNER JOIN test\_order b ON b.id % 2=0 AND a.id=b.id SET a.TYPE =3

看看效果 ：

统计出 表里面 不同 type 类型 的 数据分别有多少条 ，且看看时间用了多久：

![628011da07844653b946ababe0e27457.png][]

看看 EXPLAIN :  
![05ab4bc70a4246889b8c93d58c2c03b7.png][]

> **Using filesort** : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，**所以并不是通过索引直接返回排序结果的排序都叫 FileSort 排序**

可以看到，分析里面 出现了一个 **using filesort** ， 这个玩意就是慢的原因。

可以看到 用到了 group by type ， 返回来的数据 TYPE 是 1，2，3，4，5 默认 升序排好的。

是的，相当于 mysql 默认帮我们执行了排序， 无疑 这是需要花时间的。

所以说，当我们仅仅要的是 不同 type 数据的 统计数量结果， 那么我们是可以优化掉这个排序的耗时的。

优化技巧 ：

**order by null**

我们在 group by 后面 加上 ORDER BY NULL ， 强制禁止排序 ，

看看效果 ：

![feb6790f6e23454ba534b5e6b2c14508.png][]

![ce3f325f28e3484a9af2176583e5cbaf.png][]

那有没有更加快的优化？

**有的， 加索引。 group by 是能命中索引的。**

![97cf4a7fea52400a949a832e7762b43c.png][]

加完索引效果：  
![594a92152aef4526aaba980755ddf7ca.png][]  
![3ff73854a7ff4aa5a3e0142aeaad094e.png][]

--------------------

#### **②使用 left join / right join 的注意点** ####

关联查询， 比如 有 A 、 B 两个表 。

A表即是 我们的 test\_order 表 200W条数据：

![86ea9f8251684798be987253a94f1db9.png][]

而B 表 是 test\_order\_detail 表 5W 条数据：  
![b8783858d9054029a1f187709c5a0bd8.png][]

这两个表通过id、order\_id 关联(简单举个例子)。

注意点：

1.当使用left join时，左表是驱动表，右表是被驱动表  
2.当使用right join时，右表是驱动表，左表是被驱动表  
3.当使用inner join时，mysql会默认自动选择数据量比较小的表作为驱动表，大表作为被驱动表

**我们尽量要保证 小表 驱动 大表， 大小指的是数据量。**

那么我们看 left join 来看看效果， A表 test\_order 目前是大表 B表 test\_order\_detail是小表 效果：

我们使用 left join ， 故意把 大数据表放在 左， 小数据表放在右， 这时候 左大驱右小 ，

发现用了13秒，返回的是 200万条数据

![c6b04dc2acb743fb80894923ff2367d9.png][]

看看EXPLAIN分析情况：

![61281ada766247adb24c70bf2b0c9ea3.png][]

ps ：  
当查询引擎完成对行的计数时，结果集的其余部分出现。所以Heidi所谓的“网络时间”是计算行数的时间。这对于MyISAM来说实际上是瞬间的，而InnoDB需要一段时间。（heidiSQL编辑器）

那么如果我们反过来， 左小驱右大 ：

发现用了0.29秒，返回的是 5万条数据

![8810b9c1a35d4618ab16629b2ed3de5b.png][]

看看EXPLAIN分析情况：

![cc8ff165243e4b099c510470ddef1a9f.png][]

可以看到 小表驱动大表的情况，时间效果的差距所在。

所以根据业务情况，必须要清晰地使用上 这个优化技巧 ，尽可能保证小表驱动大表。

为什么 ？

其实这个道理很简单， 驱动表 和 被 驱动表 ， 就相当于 2层 for 循环遍历。

比如 大表200万数据 驱动 小表 5万数据 ，就是 ：

for(int  驱动表行数=0 ;  驱动表行数 <20000000; 驱动表行数++){
        
        for (int 被驱动表行数=0 ;  被驱动表行数<50000;  被驱动表行数++){
            
             找出 驱动表行记录 条件  等于  被驱动表行记录 条件值
        }
        
    }

那可能很多初学者还是不明白， 放外面是 200W 循环，里面再嵌套 5W 是 200 乘以 5 ？

那跟反过来5 乘以 200 有什么区别？

简析：

**可以看到上述的 EXPLAIN 大表驱动小表 或是 小表驱动大表， 可以看到 驱动表的索引都是不生效的， 生效的是 被驱动表的索引 。**

**索引是b+树，在索引上等值查询的时间复杂度为logN。**

**因为驱动表不走索引，需要全表扫描，而被驱动表可以建立索引加速查找。**

若小表驱动大表，则时间复杂度为 5W\*log200W  
若大表驱动小表，则时间复杂度为 200W\*log5W

所以 为什么 时间耗时久 ，也就显然得知了。

是因为被驱动表又能命中索引，而且时间查找又快啊。

--------------------

####  **③ 对字段进行表达式操作 的注意点** ####

比如 我们 想查出来 type 是 2 的 2倍 的数据 （这里简单用type举例， 可能业务上更多是 传入一个参数,然后触发某某计算倍数的概念）：

当我们 把 字段 type 融入到 表达式 里面时，可以看到 耗时 是 2.45+秒 （因为索引失效了）：

![3cecdc9b4dac4991beb3da57c739738f.png][]

看看EXPLAIN分析情况：

![06c01d5fcc6d4de7ab73e5ad76edc4ce.png][]

而我们把 type 字段 抽出来，不参与 表达式操作，我们发现效果一样，但是耗时只有 1.3 秒（因为能命中索引） ：

![98ca4f6c70274b569de3d816418b02f1.png][]

看看EXPLAIN分析情况：

![e6fa4f7601b149e4b9290a0a2d4dd867.png][]

####  **④ 对明确知道的条件值 使用 or 查询 还是 UNION ALL ,有说法** ####

比如我们想查出表里面 type 是1 或者 type 是 5的 数据 , 如果我们使用 or 去实现 ，大家知道的，使用or 是命中不了索引的，会全表扫描 。

很多这种时候，大家可能就会想， 遇到or 慢查询， 就换成 **UNION ALL 呗 。**

其实并不然 。

你可以理解为，当你使用or 查询 发现慢的时候， 你可以尝试使用**UNION ALL 去替代调试 ， 注意，是调试， 如果性能确实优化了，你就可以替代。**

直接眼见为实 ：

![2ce07929b21140d2adc00898ccfc43f3.png][]

首先可以看到 union all 比 or 还要慢 。

甚至 还可以看看 in 的效果 ，也是跟 or 基本一致 也是 3秒 左右 。

![bf4d2587ded147a8a6f440e180239170.png][]

我们看看 使用 in的 EXPLAIN ：  
![3ac046cce955438795f98de1b2845c63.png][]

再看看 使用 or 的 EXPLAIN ：  
![32df3ea5c9d14bf8b5e9a25bf6a87598.png][]

or 和 in 几乎是一样的 在不中索引的时候。

那看看 union all 的 EXPLAIN ：

可以看到命中了 索引的。

![1aa9fa4b791f4fdeaa9a812899e86b92.png][]

但是为什么这时候 union all 反而慢呢？

原因 ：

1. 其实我们可以关注到 rows 和 filtered

2. 数据量情况 以及散乱程度

![fb1feda487c74d708ff8db35403286a7.png][]

当全表扫描 98% 的数据 都是需要的， 一次扫描拿出结果。

而 union all 进行了 2次 扫描，虽然扫的是索引，但是扫了96万 + 99 万 数据， 我们一共才200W数据。

2次加起来 跟我们 全部扫描看到的row 199万 基本没区别。

这时候就是看 数据的分布情况了。

继续看看 查询 三个 type ：

使用 OR ：![f88af090295c451a87f70dbafe87f95b.png][]

使用 **union all ：**  
![3a39927b7c41402aa45081acd147a89e.png][]

![eb1148e048bd402abc7a44fe14cbf835.png][]

再再再顺便再贴一个 示例 （查询不同字段条件值的场景），让大家知道 or 和 **union all**  就是需要看实际情况调试使用的 ：  
  
![e7aa810191e040d28937fb8cdbfc030b.png][]

**所以 什么时候用 or 什么时候 用 union all ， 非绝对， 要调试为准（特别是当你的**union all 条件的字段也没索引的时候 ，你想想扫描多次表的效率**）！**

--------------------

#### ⑤ order by 的效能 提升 ####

先改造一下表 ：

![d561c84134b74fb6abcf69cc01d9f7f6.png][]

平时我们写代码，很多时候，我们一些复杂的业务sql拆分，我们很愿意去拆，提高效率。

但是遇到排序， 我个人就很懒，基本 就是 丢到sql上面 order by 了。

那么 这就有说法了。

模拟点数据 :

UPDATE test_order a
    INNER JOIN test_order b ON b.id % 7=0 AND a.id=b.id SET a.i_amount =99;
    UPDATE test_order a
    INNER JOIN test_order b ON b.id % 5=0 AND a.id=b.id SET a.i_amount =66;
    UPDATE test_order a
    INNER JOIN test_order b ON b.id % 3=0 AND a.id=b.id SET a.i_amount =588;
    UPDATE test_order a
    INNER JOIN test_order b ON b.id % 2=0 AND a.id=b.id SET a.i_amount =88;

可以看到现在 数据 有那么一些些乱了，可以来讲讲 order by 排序了 ：  
![e59ef5d1c6bb40cb8c932d60344aa82b.png][]

这时，如果我们 进行 组合 排序， 按照 i\_amount 排序 然后再按照 type 排序， 我们会发现 ，引擎有脾气，没有中索引，但是 在 extra上面 有说 **用了 using filesort** **。**

时间肯定是没有 直接用上 index 快的 ：

![8e661db7ed264dec99412356728110df.png][]

所以我们给它整活， 我们升级成组合索引 ：

![5a3ed42da0344e3a83d08a9cd078c774.png][]

这时候我们再执行，发现 可以命中了index 了：  
![650f67a23c9849f6be4ad11ad171ff5d.png][]

好了，就先讲到这吧， 有空再讲其他。

[Mysql _200W]: https://blog.csdn.net/qq_35387940/article/details/125816667
[MySql]: https://blog.csdn.net/qq_35387940/article/details/108618424
[628c6d7924394579ab958d82efcfd1ca.png]: https://img-blog.csdnimg.cn/628c6d7924394579ab958d82efcfd1ca.png
[628011da07844653b946ababe0e27457.png]: https://img-blog.csdnimg.cn/628011da07844653b946ababe0e27457.png
[05ab4bc70a4246889b8c93d58c2c03b7.png]: https://img-blog.csdnimg.cn/05ab4bc70a4246889b8c93d58c2c03b7.png
[feb6790f6e23454ba534b5e6b2c14508.png]: https://img-blog.csdnimg.cn/feb6790f6e23454ba534b5e6b2c14508.png
[ce3f325f28e3484a9af2176583e5cbaf.png]: https://img-blog.csdnimg.cn/ce3f325f28e3484a9af2176583e5cbaf.png
[97cf4a7fea52400a949a832e7762b43c.png]: https://img-blog.csdnimg.cn/97cf4a7fea52400a949a832e7762b43c.png
[594a92152aef4526aaba980755ddf7ca.png]: https://img-blog.csdnimg.cn/594a92152aef4526aaba980755ddf7ca.png
[3ff73854a7ff4aa5a3e0142aeaad094e.png]: https://img-blog.csdnimg.cn/3ff73854a7ff4aa5a3e0142aeaad094e.png
[86ea9f8251684798be987253a94f1db9.png]: https://img-blog.csdnimg.cn/86ea9f8251684798be987253a94f1db9.png
[b8783858d9054029a1f187709c5a0bd8.png]: https://img-blog.csdnimg.cn/b8783858d9054029a1f187709c5a0bd8.png
[c6b04dc2acb743fb80894923ff2367d9.png]: https://img-blog.csdnimg.cn/c6b04dc2acb743fb80894923ff2367d9.png
[61281ada766247adb24c70bf2b0c9ea3.png]: https://img-blog.csdnimg.cn/61281ada766247adb24c70bf2b0c9ea3.png
[8810b9c1a35d4618ab16629b2ed3de5b.png]: https://img-blog.csdnimg.cn/8810b9c1a35d4618ab16629b2ed3de5b.png
[cc8ff165243e4b099c510470ddef1a9f.png]: https://img-blog.csdnimg.cn/cc8ff165243e4b099c510470ddef1a9f.png
[3cecdc9b4dac4991beb3da57c739738f.png]: https://img-blog.csdnimg.cn/3cecdc9b4dac4991beb3da57c739738f.png
[06c01d5fcc6d4de7ab73e5ad76edc4ce.png]: https://img-blog.csdnimg.cn/06c01d5fcc6d4de7ab73e5ad76edc4ce.png
[98ca4f6c70274b569de3d816418b02f1.png]: https://img-blog.csdnimg.cn/98ca4f6c70274b569de3d816418b02f1.png
[e6fa4f7601b149e4b9290a0a2d4dd867.png]: https://img-blog.csdnimg.cn/e6fa4f7601b149e4b9290a0a2d4dd867.png
[2ce07929b21140d2adc00898ccfc43f3.png]: https://img-blog.csdnimg.cn/2ce07929b21140d2adc00898ccfc43f3.png
[bf4d2587ded147a8a6f440e180239170.png]: https://img-blog.csdnimg.cn/bf4d2587ded147a8a6f440e180239170.png
[3ac046cce955438795f98de1b2845c63.png]: https://img-blog.csdnimg.cn/3ac046cce955438795f98de1b2845c63.png
[32df3ea5c9d14bf8b5e9a25bf6a87598.png]: https://img-blog.csdnimg.cn/32df3ea5c9d14bf8b5e9a25bf6a87598.png
[1aa9fa4b791f4fdeaa9a812899e86b92.png]: https://img-blog.csdnimg.cn/1aa9fa4b791f4fdeaa9a812899e86b92.png
[fb1feda487c74d708ff8db35403286a7.png]: https://img-blog.csdnimg.cn/fb1feda487c74d708ff8db35403286a7.png
[f88af090295c451a87f70dbafe87f95b.png]: https://img-blog.csdnimg.cn/f88af090295c451a87f70dbafe87f95b.png
[3a39927b7c41402aa45081acd147a89e.png]: https://img-blog.csdnimg.cn/3a39927b7c41402aa45081acd147a89e.png
[eb1148e048bd402abc7a44fe14cbf835.png]: https://img-blog.csdnimg.cn/eb1148e048bd402abc7a44fe14cbf835.png
[e7aa810191e040d28937fb8cdbfc030b.png]: https://img-blog.csdnimg.cn/e7aa810191e040d28937fb8cdbfc030b.png
[d561c84134b74fb6abcf69cc01d9f7f6.png]: https://img-blog.csdnimg.cn/d561c84134b74fb6abcf69cc01d9f7f6.png
[e59ef5d1c6bb40cb8c932d60344aa82b.png]: https://img-blog.csdnimg.cn/e59ef5d1c6bb40cb8c932d60344aa82b.png
[8e661db7ed264dec99412356728110df.png]: https://img-blog.csdnimg.cn/8e661db7ed264dec99412356728110df.png
[5a3ed42da0344e3a83d08a9cd078c774.png]: https://img-blog.csdnimg.cn/5a3ed42da0344e3a83d08a9cd078c774.png
[650f67a23c9849f6be4ad11ad171ff5d.png]: https://img-blog.csdnimg.cn/650f67a23c9849f6be4ad11ad171ff5d.png