limit查询，偏移量相差1的情况下性能相差20倍

章鱼大王
177 阅读2分钟

0、环境

mysql 5.7
table1 300w数据
table2 2000w数据

1、问题描述

在开发过程中发现，列表数据分页查询的时候，偏移量为0或1时查询的语句执行很慢（约2s），而偏移量为2时查询语句都相对保持很快的速度（约80ms），性能差别达到了二十倍。附上sql如下：

SELECT a.* FROM table1 a
    LEFT JOIN table2 b ON a.f_user_id = b.f_id
WHERE b.mobile='130xxxxxxxx'
    ORDER BY a.f_time DESC LIMIT 0 , 10;

企业微信截图_a1d57446-74ac-4973-b913-d50f60190af7.png

2、排查过程

2.1、explain

惯例，在数据库中做性能分析，对两种情况的sql分别explain，情况如下：

2.2、分析

type表示MySQL在表中找到所需行的方式，又称“访问类型”，常用的类型有： ALL, index,  range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

type描述
ALLFull Table Scan， MySQL将遍历全表以找到匹配的行
indexFull Index Scan，index与ALL区别为index类型只遍历索引树
range表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
ref只检索给定范围的行，使用一个索引来选择行
eq_ref类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
NULLMySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

粗略的可以看出，较慢的语句使用index遍历了全索引树，而较快的语句使用ref。并且在偏移量从1改为2的时候，mysql优化器的决定发生了突变，索引由原先的idx_issue_ts改变为了idx_one_id。这就导致了整个查询语句的性能发生了改变。

2.3、解决办法

在能够预估业务数据量的情况下，可以人为地指定mysql优化器使用的索引，方法如下：

SELECT a.* FROM table1 a force index(idx_user_id)
    LEFT JOIN table2 b ON a.f_user_id = b.f_id
WHERE b.mobile='130xxxxxxxx'
    ORDER BY a.f_time DESC LIMIT 0 , 10;
2.4、原因探究

知道了索引的选择不同导致了查询语句的性能差异，那么为什么MYSQL在偏移量仅有微小差别的情况下会选择不同的索引呢？并且查询语句在执行时的执行顺序是怎样的？

avatar
文章
阅读
粉丝