在平时业务中我们会发现当分页数据特别大的时候,会出现SQL很慢的情况,下面我们来分析下为什么会出现这种情况以及如何去解决
一、limit深分页问题解析
我们有如下一张表
CREATE TABLE account ( id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id', name varchar(255) DEFAULT NULL COMMENT '账户名', balance int(11) DEFAULT NULL COMMENT '余额', create_time datetime NOT NULL COMMENT '创建时间', update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (id), KEY idx_name (name), KEY idx_create_time (create_time) //索引 ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';
当我们进行一下分页查询时
select id,name,balance from account where create_time> '2020-09-19' limit 100000,10;
SQL的执行流程大致如下:
1、首先通过非族簇索引(idx_create_time ),过滤条件,查询出符合要求数据的主键id。
2、在通过主键Id索引,通过Id主键索引树。找到满足记录的行,然后取出需要展示的列(此过程发生了回表)
3、扫描到满足条件的100010
行,然后扔掉前100000
行,返回。
从以上流程我们就能理解为什么limit深分页问题了。这里主要2个流程导致查询慢
1、发生了回表查询,我们查询的数据越多,那么需要回表查询的次数也就也多。
2、limit 100000,10;在执行时,limit
语句会先扫描offset+n
行,然后再丢弃掉前offset
行,返回后n
行数据。也就是说limit 100000,10
,就会扫描100010
行,而limit 0,10
,只扫描10
行。
二、limit深分页优化方案
我们可以通过减少回表次数来优化,一般有种方法:标签记录法和延迟关联法
1、标签记录法
就是标记一下上次查询到哪一条了,下次再来查的时候,从该条开始往下扫描。就好像看书一样,上次看到哪里了,你就折叠一下或者夹个书签,下次来看的时候,直接就翻到了。
实现方案呢就是记录我们上次查询到的记录id,在这次查询时把id作为查询条件带入
select id,name,balance FROM account where id > 100000 limit 10;
这样的话,后面无论翻多少页,性能都会不错的,因为命中了id索引
。但是这种方式有局限性:需要一种类似连续自增的字段。
2、延迟关联法
延迟关联法,就是把条件转移到主键索引树,然后减少回表。如下:
select acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.create_time > '2020-09-19' limit 100000, 10) AS acct2 on acct1.id= acct2.id;
优化思路就是,先通过idx_create_time
二级索引树查询到满足条件的主键ID
,再与原表通过主键ID
内连接,这样后面直接走了主键索引了,同时也减少了回表。