百万数据量下,使用延迟关联优化超大分页
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毫秒仍然不是特别满意,如果有更好的解决方案,希望不吝赐教!