MySQL实战—— Limit与Order by 对查询效率的巨大影响

前言

查询同样的数据,在使用Order by、limit后可能对查询结果 与耗时产生百倍的影响。优化SQL不光是优化那些1秒以上的慢查询,更重要的是那些超高频率的0.1秒的查询SQL。

在这里我模拟创建了一张表 limit_table
并初始化100W行的数据。

-- 表创建
CREATE TABLE `limit_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `index_fie_id_a` int(8) NOT NULL,
  `fie_id_b` int(8) NOT NULL,
  `fie_id_str` varchar(30) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `index_index_fie_id_a` (`index_fie_id_a`) USING BTREE
) ENGINE=InnoDB  CHARSET=utf8;

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE add_limit_table()
BEGIN
  DECLARE num INT;
  SET num = 0;
  WHILE
    num < 1000000 DO
    -- 插入数据
		INSERT INTO limit_table(index_fie_id_a,fie_id_b,created_at) VALUES(CEILING(RAND()*10000),CEILING(RAND()*100000),now());
    SET num = num + 1;
  END WHILE;
END; 
//
-- 执行存储过程
call add_limit_table();
-- 删除存储过程
DROP PROCEDURE add_limit_table;
--初始化String 字符串 
UPDATE limit_table set  fie_id_str= CONCAT('string',inde_x_fie_id_a,CEILING(RAND()*10));

数据初始化完成后我们来看看不同查询条件、不同排序条件下 查询效率比较
字段index_fie_id_a:int类型,有索引,每个数据约有100条重复
字段fie_id_b :int类型,无索引,每个数据约有10条重复
字段fie_id_str :varchar类型,无索引,每个数据约有10条重复,且数据是依据index_fie_id_a 排序生成

验证场景

	-- 无排序、无查询条件 Limit 0,5   Demo :A
	SELECT SQL_NO_CACHE  * from limit_table LIMIT 5;
	-- 无排序、无查询条件 Limit 50000,5   Demo :B
	SELECT SQL_NO_CACHE  * from limit_table LIMIT 50000,5;
	-- 无排序、有查询条件/int类型/无索引 Limit 0,5   Demo :C
	SELECT SQL_NO_CACHE  * from limit_table  WHERE fie_id_b = 66666 LIMIT 5;
	-- 无排序、有查询条件/varchar/无索引 Limit 0,5   Demo :D
	SELECT SQL_NO_CACHE  * from limit_table  WHERE fie_id_str = 'string66666' LIMIT 5;
	-- 无排序、有查询条件/有索引 Limit 0,5   Demo :E
	SELECT SQL_NO_CACHE  * from limit_table  WHERE index_fie_id_a = 100 LIMIT 5;
	-- 有排序/无索引、无查询条件 Limit 0,5   Demo :F
	SELECT SQL_NO_CACHE  * from limit_table  ORDER BY fie_id_b LIMIT 5;
	-- 有排序/无索引、有查询条件/int类型/无索引 Limit 0,5   Demo :G;
	SELECT SQL_NO_CACHE  * from limit_table WHERE fie_id_b = 66666  ORDER BY fie_id_str LIMIT 5;
	-- 有排序/无索引、有查询条件/varchar类型/无索引 Limit 0,5   Demo :H;
	SELECT SQL_NO_CACHE  * from limit_table  WHERE fie_id_str = 'string66666' ORDER BY fie_id_b  LIMIT 5;
	-- 有排序/无索引、有查询条件/有索引 Limit 0,5   Demo :I;
	SELECT SQL_NO_CACHE  * from limit_table  WHERE index_fie_id_a = 100 ORDER BY fie_id_b LIMIT 5;
	-- 有排序/有索引、无查询条件 Limit 0,5   Demo :J;
	SELECT SQL_NO_CACHE  * from limit_table  ORDER BY index_fie_id_a LIMIT 5;
	-- 有排序/有索引、有查询条件/无索引 Limit 0,5   Demo :K1;
	SELECT SQL_NO_CACHE  * from limit_table WHERE fie_id_str = 'string10001'  ORDER BY index_fie_id_a LIMIT 5;
	-- 有排序/有索引、有查询条件/无索引 Limit 0,5   Demo :K2;
	SELECT SQL_NO_CACHE  * from limit_table WHERE fie_id_str = 'string30001'  ORDER BY index_fie_id_a LIMIT 5;
	-- 有排序/有索引、有查询条件/无索引 Limit 0,5   Demo :K3;
	SELECT SQL_NO_CACHE  * from limit_table WHERE fie_id_str = 'string60001'  ORDER BY index_fie_id_a LIMIT 5;
	-- 有排序/有索引、有查询条件/无索引 Limit 0,5   Demo :K4;
	SELECT SQL_NO_CACHE  * from limit_table WHERE fie_id_str = 'string99999'  ORDER BY index_fie_id_a  LIMIT 5;
	-- 有排序/有索引、有查询条件/无索引 Limit 0,5   Demo :K5;
	SELECT SQL_NO_CACHE  * from limit_table WHERE fie_id_str = 'string99999'  ORDER BY index_fie_id_a  desc LIMIT 5;
	-- 有查询条件/无索引   Demo :K6;
	SELECT SQL_NO_CACHE  * from limit_table WHERE fie_id_str = 'string99999';
	-- 有排序/有索引、有查询条件/有索引 Limit 0,5   Demo :L;
	SELECT SQL_NO_CACHE  * from limit_table WHERE index_fie_id_a = 100  ORDER BY index_fie_id_a LIMIT 5;

本文中分析数据均是基于查找的数据绝对能够覆盖 limit 限制的条数,避免因记录数不足导致全表扫描影响查询耗时的情况。

查询耗时情况如下

在这里插入图片描述

查询结果分析
  • Demo A:没有查询条件,且通过Limit限制了查询数量,在查询数量一致的情况下这是 最快 的,因为通常B+Tree上有 两个头指针 ,一个指向根节点,另一个指向关键字最小的叶子节点,直接可以进行 顺序读 ,高效,不浪费IO次数。所以本次查询返回的会是ID:1 ~ 5 的数据。 数据检索行数:5

    忽略其它因素影响情况下数据检索行数越少,查询越快。咱们做SQL优化,核心之一就是减少数据检索行数。这也是索引存在的主要作用。

  • Demo B :Demo B 比 Demo A 更慢因为B查询需要检索的行数更多,因为需要从第一条开始依次找到第50001 ~ 50005 条数据。所以当咱们做翻页功能时,深度翻页 将会导致你的查询效率大大降低!数据检索行数:50005

    涉及 limit $start,$num 的搜索,如果$start 巨大,则影响结果集巨大,最终导致查询耗时较长,推荐大家尽量带入主键ID当查询条件并将SQL改写为 limit 0,$num; 确认无法改写的情况下,先从索引结构中获得 limit $start,$num 或 limit $start,1 ;再用 in 操作或基于索引序的limit 0,$num 二次搜索。
    例如:下一页操作让前端把当前页最大ID传递过来,然后后台加一个weher条件即可。如果是用户手动输入页码跳页,则可以先用查询页面查出该页的最小主键ID值然后再去查询该页数据。

  • Demo C & Demo D :Demo C会比 Demo D快那么多,在都没有索引的情况下,忽略数据分布(假设ID 1 ~ 5 的数据 fie_id_str 都等于 ‘string66666’,这就是极端情况,咱们不考虑)。在数据运算、对比方面,整数得益于原生支持会比varchar 快很多。数据检索行数:>=5 and <总记录数

    因为会从主键ID最小的叶子节点开始依次对比,当满足Limit限制的5行数据后即可停止继续检索数据。所以数据检索行数是未知的。除非符合要求的数据不足5条就会检索完整个表的数据

  • Demo E :该字段建了索引,所以根据非聚簇索引顺序取5行数据对应的主键ID即可,然后根据主键ID回表查询数据。Demo E 会比 Demo A 慢主要原因两点:1、回表查询增加IO次数; 2、数据的主键ID不连续,需要从磁盘加载多个页到内存数据检索行数:5

  • Demo F :排序字段没有索引,没有查询条件,所以无任何索引可用扫全表无疑。慢到飞起,这是绝对错误的玩法。 数据检索行数:总记录数

  • Demo G & Demo H :这两个同样都是根据where条件扫全表,然后符合条件的数据进行排序,所以 Demo G & Demo H 相比 Demo C & Demo D 更慢。Demo G 比 Demo H 快还是因为字符类型的优势。同时 Demo G 还比 Demo F 快一点说明同字符类型下排序会比比较判断要慢。 数据检索行数:总记录数

    细心的同学发现我查询Demo G案例时将排序字段使用了数据库中的str字段,那假如我的排序字段和where查询条件字段完全一致,且查询条件是 ‘=’,那么可以理解为 没有排序下检索5行记录。所以可能会不需要全表扫描哦!!!

  • Demo I :相对还是比较快的,因为查询条件有索引。但是由于排序字段无索引所以需要把所有符合条件的数据找出来,再去排序。数据检索行数:符合where条件的总记录数

  • Demo J :Demo J 和 Demo E是差不多的,但是由于不需要判断比较 和 Demo A一样没有查询条件所以会比 Demo E 快。同时又因为回表查询与主键ID不连续的问题会比Demo A要慢。 数据检索行数:5

  • Demo L :where条件有索引,排序字段相同,在判断条件是 ‘=’ 的情况下可以忽略。所以参考 Demo E。 数据检索行数:5

查询条件无索引但排序有索引

在这里插入图片描述

查询结果分析

在这里插入图片描述

  • Demo K1 ~ Demo K4:这个就非常有意思,因为where条件无索引而排序字段是有索引的,优化器 会选择走索引。于是噩梦就来了,查询时间浮动巨大,全看你的where条件中的数据存在于索引字段的哪一部分。Demo K4中 fie_id_str = ‘string90001’ 的数据,按照 index_fie_id_a 排序后处在,非常靠后的部分于是乎这个查询耗时直接就慢的不行。
  • Demo K5 :我并未修改查询条件和排序字段,只是将排序由 ASC 改为 DESC,于是 fie_id_str = ‘string99999’ 所处的位置瞬间就靠前了,查询时间一下变短了 2700 倍。
  • Demo K6:我直接去掉了排序和Limit,全表匹配查找不走索引,反而相对比有索引情况下靠谱得多。这就是之前有说到过的,回表 和主键ID不连续,导致每一条记录都需要加载一整个页的数据。

总结

  • int类型比较判断会比varchar快,许多数据库会用 1/2 代替 男/女;
  • 深度分页需要注意优化;
  • 常用的查询字段建立索引是非常有必要的;
  • MySQL的优化器不一定会给你最好的结果。但是如果结果不理想,一定是你的索引建的有问题或者SQL写的有问题。以我们的技术功底千万别去怀疑优化器;
  • 在没有查询条件、或查询条件没有索引的情况下严禁使用无索引字段排序;
  • 能靠主键查询/排序尽量靠主键,避免回表查询;
  • 如查询条件没有索引,慎用有索引的字段去排序;
  • 在已知需要查询数据数量情况下使用Limit,可以有效提高查询效率;
如果觉得写得不错,点个赞就是对我最大的鼓励!!!
  • 16
    点赞
  • 23
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 10
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 10
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

zhibo_lv

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值