MySQL面试题：亿级数据量连表查询，取结果第10000到11000条

1. 题目

表A通过外键tid关联表B，表A有10亿条数据，表B有4亿条数据，连表查询A和B，取查询结果的第10000到11000条数据

2. 解决方案

（非标准答案，参考网上资料结合自己的思考，参考资料：使用子查询优化分页查询）

1. 先查找表A的第10000到11000条数据：不要limit直接分页查，第10000条记录前的记录会产生I/O开销——先查第10000条记录的主键（SELECT id FROM A LIMIT 9999, 1），分页时通过where语句跳过前10000条记录（SELECT * FROM A WHERE id >=  第10000条记录的主键 LIMIT 1000）
2. 连表查询（没想到SQL层面的优化，但如果机器允许，可考虑加大join buffer）

-- 查询表A第10000条记录的主键值
create temporary table `offset` as
select id from A order by id limit 9999, 1;
-- 连表
select a.id as aid, B.id as bid, a.tid as tid
from (
    select * from A where id >= (
        select id from `offset` limit 1)
    order by id limit 1000
) as a
inner join B on a.tid = B.id;

3. 模拟测试

3.1 建表

受限于硬件，表A大小1000万，表B大小400万

3.2 查询时间

（1）直接连表分页查询

SELECT A.id, A.tid, B.id FROM A
INNER JOIN B ON A.tid = B.id
ORDER BY A.id LIMIT 9999, 1000;

（2）优化后

差别不明显，下面尝试取从第20万条开始的1000条数据

PS：上面结果可能因为缓存并不准确，因此下面先查优化后的结果，在查优化前的结果（这里我第一次先查优化前的结果，时间是达到了17秒的）

（1）优化后

（2）直接连表分页查询

SELECT A.id, A.tid, B.id FROM A
INNER JOIN B ON A.tid = B.id
ORDER BY A.id LIMIT 199999, 1000; -- 从第20万条开始的1000条

差别还是蛮大的……