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

红太狼 2022-10-06 08:59 412阅读 0赞

## 前言 ##

查询同样的数据，在使用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 限制的条数，避免因记录数不足导致全表扫描影响查询耗时的情况。

#### 查询耗时情况如下 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaWJvX2x2_size_16_color_FFFFFF_t_70]

#### 查询结果分析 ####

*  **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 巨大，则影响结果集巨大，搜索效率会非常难过低，尽量用其他方式改写为 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**

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaWJvX2x2_size_16_color_FFFFFF_t_70 1]

#### 查询结果分析 ####

![在这里插入图片描述][20210612225754216.png]

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

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaWJvX2x2_size_16_color_FFFFFF_t_70]: /images/20221005/0d2e4acb00ab4e4b92794dc521283b63.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaWJvX2x2_size_16_color_FFFFFF_t_70 1]: /images/20221005/87fbba2d32ab49b9836327aa8a7b36ab.png
[20210612225754216.png]: /images/20221005/52a9b543c80745488ccd6b9ec09bd362.png