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_update_time (update_time) //索引
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';
问题sql
假设深分页的执行SQL如下:
select id,name,balance from account where update_time> '2020-09-19' order by id limit 100000,10;
解决方案:
1.标签记录法
每次传入一个值,来作为游标
select id,name,balance from account where update_time> ${value} limit 1000;
注意:尽量不要order by id,使用非主键索引的时候,叶子结点的id不一定连续且单调,数据量很大的时候使用order by id 会导致再走一遍文件排序,十分影响性能,可能查询达到妙级。
2.INNER JOIN 延迟关联
SELECT acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.update_time >= '2020-09-19' ORDER BY a.update_time LIMIT 100000, 10) AS acct2 on acct1.id= acct2.id;
参考文章:
实战!聊聊如何解决MySQL深分页问题-51CTO.COM
MySQL慢查询日志出现commit释疑 - 潇湘隐者 - 博客园