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，可以有效提高查询效率；

如果觉得写得不错，点个赞就是对我最大的鼓励！！！