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

Mysql 我随手造200W条数据&＃xff0c;给你们讲讲分页优化 

MySql 索引失效、回表解析

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

准备模拟数据。

首先是一张 test_orde 表&＃xff1a;

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

然后是一个存储过程 &＃xff1a;

BEGIN DECLARE num INT DEFAULT 2000000; DECLARE i INT DEFAULT 0; WHILE i END

 执行存储过程&＃xff0c;看下模拟数据&＃xff1a;

开始。 

① 使用 count 、 group by  注意点

比如&＃xff0c; 我们想统计一下 当前 表里面&＃xff0c; 根据type维度 分别有多少 数据 &＃xff1a;
 

SELECT COUNT(*) ,type
FROM test_order GROUP BY TYPE ;

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

真实场景&＃xff0c;我们 肯定不止一个type。 

改造出模拟数据(尽量使数据更随机&＃xff0c;真实业务场景也许会更加更加散乱)&＃xff1a;
 

将数据里面 id 是 7的 倍数的数据  的type 改成  5&＃xff1b;

将数据里面 id 是 5 的 倍数的数据  的type 改成  2&＃xff1b;

将数据里面 id 是 3 的 倍数的数据  的type 改成  4&＃xff1b;

将数据里面 id 是 2 的 倍数的数据  的type 改成  3&＃xff1b;

sql&＃xff1a; 

UPDATE test_order a
INNER JOIN test_order b ON b.id % 7&＃61;0 AND a.id&＃61;b.id SET a.TYPE &＃61;5

UPDATE test_order a
INNER JOIN test_order b ON b.id % 5&＃61;0 AND a.id&＃61;b.id SET a.TYPE &＃61;2

UPDATE test_order a
INNER JOIN test_order b ON b.id % 3&＃61;0 AND a.id&＃61;b.id SET a.TYPE &＃61;4

UPDATE test_order a
INNER JOIN test_order b ON b.id % 2&＃61;0 AND a.id&＃61;b.id SET a.TYPE &＃61;3

看看效果 &＃xff1a;

统计出 表里面 不同 type 类型 的 数据分别有多少条 &＃xff0c;且看看时间用了多久&＃xff1a;

 看看 EXPLAIN :

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

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

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

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

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

优化技巧 &＃xff1a;

order by null 

我们在 group by 后面 加上 ORDER BY  NULL &＃xff0c; 强制禁止排序  &＃xff0c;

看看效果 &＃xff1a;

那有没有更加快的优化&＃xff1f; 

有的&＃xff0c; 加索引。 group by 是能命中索引的。

 加完索引效果&＃xff1a;

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

关联查询&＃xff0c; 比如 有 A 、 B  两个表 。

A表即是 我们的 test_order 表 200W条数据&＃xff1a;

而B 表 是 test_order_detail 表  5W 条数据&＃xff1a;

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

注意点&＃xff1a;

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

我们尽量要保证 小表 驱动 大表&＃xff0c; 大小指的是数据量。

那么我们看 left join 来看看效果&＃xff0c; A表 test_order 目前是大表  B表  test_order_detail是小表 效果&＃xff1a;

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

发现用了13秒&＃xff0c;返回的是 200万条数据 
 

看看EXPLAIN分析情况&＃xff1a; 

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

那么如果我们反过来&＃xff0c; 左小驱右大 &＃xff1a;
 

 发现用了0.29秒&＃xff0c;返回的是 5万条数据 

 看看EXPLAIN分析情况&＃xff1a; 

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

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

为什么 &＃xff1f;

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

比如 大表200万数据 驱动 小表 5万数据 &＃xff0c;就是 &＃xff1a;

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

那可能很多初学者还是不明白&＃xff0c; 放外面是 200W 循环&＃xff0c;里面再嵌套 5W  是 200 乘以 5 &＃xff1f;

那跟反过来5 乘以 200 有什么区别&＃xff1f;  

简析&＃xff1a;

可以看到上述的  EXPLAIN 大表驱动小表 或是 小表驱动大表&＃xff0c; 可以看到 驱动表的索引都是不生效的&＃xff0c; 生效的是 被驱动表的索引 。 

索引是b&＃43;树&＃xff0c;在索引上等值查询的时间复杂度为logN。

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

若小表驱动大表&＃xff0c;则时间复杂度为 5W*log200W
若大表驱动小表&＃xff0c;则时间复杂度为 200W*log5W
 

所以 为什么 时间耗时久 &＃xff0c;也就显然得知了。

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

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

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

当我们 把 字段 type 融入到 表达式 里面时&＃xff0c;可以看到 耗时 是 2.45&＃43;秒 &＃xff08;因为索引失效了&＃xff09;&＃xff1a;

  看看EXPLAIN分析情况&＃xff1a; 

而我们把 type 字段 抽出来&＃xff0c;不参与 表达式操作&＃xff0c;我们发现效果一样&＃xff0c;但是耗时只有 1.3 秒&＃xff08;因为能命中索引&＃xff09; &＃xff1a;

  看看EXPLAIN分析情况&＃xff1a; 

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

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

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

其实并不然 。

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

直接眼见为实 &＃xff1a;

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

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

我们看看 使用 in的 EXPLAIN &＃xff1a;

 再看看 使用 or  的 EXPLAIN  &＃xff1a;

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

那看看   union all 的  EXPLAIN &＃xff1a;
 

可以看到命中了 索引的。

但是为什么这时候   union all 反而慢呢&＃xff1f; 

原因 &＃xff1a;

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

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

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

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

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

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

继续看看 查询 三个 type &＃xff1a;

使用 OR  &＃xff1a;

 使用  union all  &＃xff1a;

 再再再顺便再贴一个 示例 &＃xff08;查询不同字段条件值的场景&＃xff09;&＃xff0c;让大家知道 or 和  union all  就是需要看实际情况调试使用的 &＃xff1a;

所以 什么时候用 or  什么时候 用  union all  &＃xff0c; 非绝对&＃xff0c; 要调试为准&＃xff08;特别是当你的union all 条件的字段也没索引的时候 &＃xff0c;你想想扫描多次表的效率&＃xff09;&＃xff01; 

⑤ order by  的效能 提升 

先改造一下表  &＃xff1a;

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

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

那么 这就有说法了。

模拟点数据 :

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

 可以看到现在 数据 有那么一些些乱了&＃xff0c;可以来讲讲 order by 排序了 &＃xff1a;

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

时间肯定是没有 直接用上 index 快的 &＃xff1a;

 所以我们给它整活&＃xff0c; 我们升级成组合索引 &＃xff1a;

 这时候我们再执行&＃xff0c;发现 可以命中了index 了&＃xff1a;

好了&＃xff0c;就先讲到这吧&＃xff0c; 有空再讲其他。