学院网站群建设,公司个人怎么制作网站,面向企业的电子商务,提升学历的好处有哪些前言 Mysql 我随手造200W条数据#xff0c;给你们讲讲分页优化 MySql 索引失效、回表解析 今天再聊聊一些我想分享的查询优化相关点。
正文 准备模拟数据。
首先是一张 test_orde 表#xff1a;
CREATE TABLE test_order (id INT(11) NOT NULL AUTO_INCREMENT,p_sn VARCHA…前言 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 % 70 AND a.idb.id SET a.TYPE 5 UPDATE test_order a INNER JOIN test_order b ON b.id % 50 AND a.idb.id SET a.TYPE 2 UPDATE test_order a INNER JOIN test_order b ON b.id % 30 AND a.idb.id SET a.TYPE 4 UPDATE test_order a INNER JOIN test_order b ON b.id % 20 AND a.idb.id SET a.TYPE 3 看看效果
统计出 表里面 不同 type 类型 的 数据分别有多少条 且看看时间用了多久 看看 EXPLAIN : Using filesort : 通过表的索引或全表扫描读取满足条件的数据行然后在排序缓冲区sort buffer中完成排序操作所以并不是通过索引直接返回排序结果的排序都叫 FileSort 排序 可以看到分析里面 出现了一个 using filesort 这个玩意就是慢的原因。 可以看到 用到了 group by type 返回来的数据 TYPE 是 12345 默认 升序排好的。
是的相当于 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 % 70 AND a.idb.id SET a.i_amount 99;
UPDATE test_order a
INNER JOIN test_order b ON b.id % 50 AND a.idb.id SET a.i_amount 66;
UPDATE test_order a
INNER JOIN test_order b ON b.id % 30 AND a.idb.id SET a.i_amount 588;
UPDATE test_order a
INNER JOIN test_order b ON b.id % 20 AND a.idb.id SET a.i_amount 88; 可以看到现在 数据 有那么一些些乱了可以来讲讲 order by 排序了 这时如果我们 进行 组合 排序 按照 i_amount 排序 然后再按照 type 排序 我们会发现 引擎有脾气没有中索引但是 在 extra上面 有说 用了 using filesort 。 时间肯定是没有 直接用上 index 快的 所以我们给它整活 我们升级成组合索引 这时候我们再执行发现 可以命中了index 了 好了就先讲到这吧 有空再讲其他。
