千万大表怎么优化分页
延迟关联
通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。
# 演示表 DDL
create table t_big_table
(
c_id int(11) unsigned auto_increment comment 'id' primary key,
c_uid varchar(32) default '' not null comment 'uid',
c_name varchar(50) default '' not null comment '名称',
c_create_time timestamp default CURRENT_TIMESTAMP not null comment '创建时间',
c_update_time timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间',
c_is_deleted tinyint(1) default 0 not null comment '是否删除 0未删除 1删除'
)
comment '大表';
create index idx_create_time on t_big_table (c_create_time);
# 原分页 sql
select *
from t_big_table
where c_is_deleted = 0
order by c_create_time
limit 10000000, 20;
# 优化后分页sql
select t1.*
from t_big_table t1
join
(select c_id
from t_big_table
where c_is_deleted = 0
order by c_create_time
limit 10000000, 20) t2
on t1.c_id = t2.c_id;
c_id 是主键值,c_create_time 上面有索引。这样每次查询的时候,会先从 c_create_time 索引列上找到 c_id 值,然后回表,查询到所有的数据。可以看到有很多回表其实是没有必要的。完全可以先从 c_create_time 索引上找到 c_id(注意只查询 c_id 是不会回表的,因为非聚集索引上包含的值为索引列值和主键值,相当于从索引上能拿到所有的列值,就没必要再回表了),然后再关联一次表,获取所有的数据。
Q.E.D.