前言
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 =5UPDATE test_order a
INNER JOIN test_order b ON b.id % 5=0 AND a.id=b.id SET a.TYPE =2UPDATE test_order a
INNER JOIN test_order b ON b.id % 3=0 AND a.id=b.id SET a.TYPE =4UPDATE 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 了：
好了，就先讲到这吧， 有空再讲其他。