千万大表怎么优化分页

延迟关联

通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。

# 演示表 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.


知识的价值不在于占有,而在于使用