百万数据量下，使用延迟关联优化超大分页

百万数据量下，使用延迟关联优化超大分页

MySQL 并不是跳过 offset 行，而是取 offset + N 行，然后返回放弃前 offset 行，返回 N 行，那当offset 特别大的时候，效率就非常的低下

延迟关联

根本原理在于利用索引覆盖，避免二次回表，提高性能，延迟关联则是通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。

模拟百万条数据

表结构

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `age` int(0) NULL DEFAULT 0,
  `balance` decimal(12, 2) NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1212 CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

python脚本

# coding:utf-8
import pymysql
import random
import time


# 装饰器，计算插入数据需要的时间
def timer(func):
    def decor(*args):
        start_time = time.time()
        func(*args)
        end_time = time.time()
        d_time = end_time - start_time
        print("the running time is : ", d_time, "秒")
    return decor


@timer
def storage():
    # 连接数据库
    connect = pymysql.connect(host='localhost', port=3306, user="root", password="123456", db="stu",
                              charset='utf8')
    # 使用cursor()方法创建一个游标对象
    values = []
    for i in range(100_00_00):
        user = [generateName(), random.randint(18, 30), random.randint(1, 2), "杭州下沙", generatePhone()]
        values.append(user)
    print(values)
    cursor = connect.cursor()
    # 使用execute()方法执行SQL语句
    try:
        cursor.executemany(
            'INSERT INTO user_info(name,age,gender,address,tel) values (%s,%s,%s,%s,%s)', values)
        # 提交
        connect.commit()
    except Exception as e:
        print(e)
        # 数据回滚
        connect.rollback()


def generateName():
    lastName = ['赵', '钱', '孙', '李', '周', '吴', '郑', '王', '冯', '陈', '褚', '卫', '蒋', '沈', '韩', '杨', '朱', '秦', '尤', '许', '何',
                '吕', '施', '张', '孔', '曹', '严', '华', '金', '魏', '陶', '姜', '戚', '谢', '邹', '喻', '柏', '水', '窦', '章', '云', '苏',
                '潘', '葛', '奚', '范', '彭', '郎', '鲁', '韦', '昌', '马', '苗', '凤', '花', '方', '俞', '任', '袁', '柳', '酆', '鲍', '史',
                '唐', '费', '廉', '岑', '薛', '雷', '贺', '倪', '汤', '滕', '殷', '罗', '毕', '郝', '邬', '安', '常', '乐', '于', '时', '傅',
                '皮', '卞', '齐', '康', '伍', '余', '元', '卜', '顾', '孟', '平', '黄', '和', '穆', '萧', '尹', '姚', '邵', '湛', '汪', '祁',
                '毛', '禹', '狄', '米', '贝', '明', '臧', '计', '伏', '成', '戴', '谈', '宋', '茅', '庞', '熊', '纪', '舒', '屈', '项', '祝',
                '董', '梁', '杜', '阮', '蓝', '闵', '席', '季', '麻', '强', '贾', '路', '娄', '危', '江', '童', '颜', '郭', '梅', '盛', '林',
                '刁', '锺', '徐', '丘', '骆', '高', '夏', '蔡', '田', '樊', '胡', '凌', '霍', '虞', '万', '支', '柯', '昝', '管', '卢', '莫',
                '经', '房', '裘', '缪', '干', '解', '应', '宗', '丁', '宣', '贲', '邓', '郁', '单', '杭', '洪', '包', '诸', '左', '石', '崔',
                '吉', '钮', '龚', '程', '嵇', '邢', '滑', '裴', '陆', '荣', '翁', '荀', '羊', '於', '惠', '甄', '麹', '家', '封', '芮', '羿',
                '储', '靳', '汲', '邴', '糜', '松', '井', '段', '富', '巫', '乌', '焦', '巴', '弓', '牧', '隗', '山', '谷', '车', '侯', '宓',
                '蓬', '全', '郗', '班', '仰', '秋', '仲', '伊', '宫', '甯', '仇', '栾', '暴', '甘', '钭', '厉', '戎', '祖', '武', '符', '刘',
                '景', '詹', '束', '龙', '叶', '幸', '司', '韶', '郜', '黎', '蓟', '薄', '印', '宿', '白', '怀', '蒲', '邰', '从', '鄂', '索',
                '咸', '籍', '赖', '卓', '蔺', '屠', '蒙', '池', '乔', '阴', '鬱', '胥', '能', '苍', '双', '闻', '莘', '党', '翟', '谭', '贡',
                '劳', '逄', '姬', '申', '扶', '堵', '冉', '宰', '郦', '雍', '郤', '璩', '桑', '桂', '濮', '牛', '寿', '通', '边', '扈', '燕',
                '冀', '郏', '浦', '尚', '农', '温', '别', '庄', '晏', '柴', '瞿', '阎', '充', '慕', '连', '茹', '习', '宦', '艾', '鱼', '容',
                '向', '古', '易', '慎', '戈', '廖', '庾', '终', '暨', '居', '衡', '步', '都', '耿', '满', '弘', '匡', '国', '文', '寇', '广',
                '禄', '阙', '东', '欧', '殳', '沃', '利', '蔚', '越', '夔', '隆', '师', '巩', '厍', '聂', '晁', '勾', '敖', '融', '冷', '訾',
                '辛', '阚', '那', '简', '饶', '空', '曾', '毋', '沙', '乜', '养', '鞠', '须', '丰', '巢', '关', '蒯', '相', '查', '后', '荆',
                '红', '游', '竺', '权', '逯', '盖', '益', '桓', '公', '万俟', '司马', '上官', '欧阳', '夏侯', '诸葛', '闻人', '东方', '赫连', '皇甫',
                '尉迟', '公羊', '澹台', '公冶', '宗政', '濮阳', '淳于', '单于', '太叔', '申屠', '公孙', '仲孙', '轩辕', '令狐', '锺离', '宇文', '长孙',
                '慕容', '鲜于', '闾丘', '司徒', '司空', '亓官', '司寇', '仉', '督', '子车', '颛孙', '端木', '巫马', '公西', '漆雕', '乐正', '壤驷',
                '公良', '拓跋', '夹谷', '宰父', '穀梁', '晋', '楚', '闫', '法', '汝', '鄢', '涂', '钦', '段干', '百里', '东郭', '南门', '呼延',
                '归海', '羊舌', '微生', '岳', '帅', '缑', '亢', '况', '後', '有', '琴', '梁丘', '左丘', '东门', '西门', '商', '牟', '佘', '佴',
                '伯', '赏', '南宫', '墨', '哈', '谯', '笪', '年', '爱', '阳', '佟', '第五', '言', '福']
    NAME_BODY_MING = ['壮', '昱杰', '开虎', '凯信', '永斌', '方洲', '长发', '可人', '天弘', '炫锐', '富明', '俊枫']
    NAME_GIRL_MING = ['小玉', '蓝', '琬郡', '琛青', '予舴', '妙妙', '梓茵', '海蓉', '语娜', '馨琦', '晓馥', '佳翊']
    lastNameSize = len(lastName) - 1
    boySize = len(NAME_BODY_MING) - 1
    girlSize = len(NAME_GIRL_MING) - 1
    # 随机选择一个男孩的名字或者女孩的名字
    flag = random.randint(0, 1)
    return lastName[random.randint(1, lastNameSize)] + NAME_BODY_MING[random.randint(0, boySize)] if flag == 0 else \
        lastName[random.randint(1, lastNameSize)] + NAME_GIRL_MING[random.randint(0, girlSize)]


def generatePhone():
    return str(1) + str(random.randint(100000000, 999999999))


if __name__ == "__main__":
    storage()

测试

# 基础命令
select * from user_info limit 800000;

# 延迟关联
SELECT a.* FROM user_info a, (select id from user_info LIMIT 800000) b where a.id=b.id;

# 子查询
SELECT * FROM user_info
WHERE id >= (SELECT id FROM user_info limit 800000, 1)
LIMIT 20; 

结果
普通分页：1.046s，1.009s，1.023s
延迟关联：0.261s，0.293s，0.227s
子查询：0.296s，0.271，0.287s

粗浅的结论
延迟关联 = 子查询 = 3 倍的普通分页

总结

1、在百万级别的数据下，甚至更大的数据量下，利用索引覆盖的优势，可以显著的提高分页的效率。

2、实际过程中，如果数据量真的很大（分页后有上千甚至上万页），我们从业务的角度出发，用户可能并不关心最后几页的数据，也很少回去从后往前翻，所以我们都不需要去提供尾页的功能，而是仅提供下一页，如百度

ps：
个人感觉即使是300毫秒仍然不是特别满意，如果有更好的解决方案，希望不吝赐教！