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

前言

Mysql 我随手造200W条数据，给你们讲讲分页优化 

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

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

开始。 

① 使用 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 类型 的 数据分别有多少条 ，且看看时间用了多久：

 

 看看 EXPLAIN :

 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 ， 强制禁止排序  ，

看看效果 ：

那有没有更加快的优化？ 

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

 加完索引效果：

---

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

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

A表即是 我们的 test_order 表 200W条数据：

而B 表 是 test_order_detail 表  5W 条数据：

这两个表通过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万条数据 
 

看看EXPLAIN分析情况： 

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

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

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

 看看EXPLAIN分析情况： 

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

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

为什么 ？

其实这个道理很简单， 驱动表 和 被 驱动表 ， 就相当于 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+秒 （因为索引失效了）：

  看看EXPLAIN分析情况： 

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

  看看EXPLAIN分析情况： 

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

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

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

其实并不然 。

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

直接眼见为实 ：

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

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

我们看看 使用 in的 EXPLAIN ：

 再看看 使用 or  的 EXPLAIN  ：

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

那看看   union all 的  EXPLAIN ：
 

可以看到命中了 索引的。

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

原因 ：

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

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

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

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

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

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

继续看看 查询 三个 type ：

使用 OR  ：

 使用  union all  ：

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

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

---

⑤ order by  的效能 提升 

先改造一下表  ：

平时我们写代码，很多时候，我们一些复杂的业务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 排序了 ：

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

 

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

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

 

 这时候我们再执行，发现 可以命中了index 了：

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