| 【得物技术】MySQL深分页优化 | 您所在的位置：网站首页 › thinkPHP5分页类 › 【得物技术】MySQL深分页优化 |
【得物技术】MySQL深分页优化
|
「本文已参与好文召集令活动，点击查看：后端、大前端双赛道投稿，2万元奖池等你挑战！」 前言分页是一个很普通的功能，只要是个后端开发就要写分页，那为什么要分页？ 从业务上来讲，即使系统返回所有数据，用户绝大多数情况下是不会看后面的数据的。 技术上，因为要考虑取数据的成本，目标服务器磁盘、内存、网络带宽，以及请求发起方自身是否能承受大批量数据。MySQL分页语法 select * from table limit 0, 20 复制代码思考: 使用分页，上面提到的第2点，这些成本真的能降低吗？ 建表，造数据 CREATE TABLE t1 ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键', m_id BIGINT NOT NULL COMMENT '其他id', `name` VARCHAR ( 255 ) COMMENT '用户名称', identity_no VARCHAR ( 30 ) COMMENT '身份证号', address VARCHAR ( 255 ) COMMENT '地址', create_time TIMESTAMP NOT NULL COMMENT '添加时间', modify_time TIMESTAMP NOT NULL COMMENT '修改时间', PRIMARY KEY `id` ( `id` ) ) ENGINE INNODB DEFAULT CHARSET = 'utf8' COMMENT '深分页测试表'; -- 先初始化一条数据 INSERT INTO t1 VALUES ( 1, 1, '这里是随机中英文的名字—1', '100000000000000000', '这里是随机中英文的地址—1', '2010-01-01 00:00:00', '2010-01-01 00:00:00' ); -- 执行个十几次 set @i=1; insert into t1(m_id, name, identity_no, address, create_time, modify_time) select @i:=@i+1 as m_id, #随机生成1~10的整数 concat('这里是随机中英文的名字—',@i), #按序列生成不同的name 100000000000000000+@i, concat('这里是随机中英文的地址—',@i), date_add(create_time,interval +@i*cast(rand()*100 as signed) SECOND), date_add(date_add(create_time,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND) from t1; #注: 该方法来自网络, 方法来源: https://blog.csdn.net/mysqltop/article/details/105230327select count(1) from t1; 复制代码数据总量400W+：
100w之后的数据，耗时： 0.613s elapsed 加主键排序 select id,m_id, name, identity_no, address, create_time, modify_time from t1 order by id limit 1000000, 20; 复制代码耗时：有所降低 0.417s elapsed 执行计划对比： 1:
可以看到带主键排序使用了主键索引，且只读取了需要的前n条数据，所以快。 因此, 结论1：即使业务上看起来没有任何条件还不需要排序，也加上order by主键。 这里其实有另一个问题：如果不带排序条件，MySQL默认是什么排序？ 通常认为是主键，但通过查资料发现并不一定，这里有个物理顺序和逻辑顺序的区别，如：删除原有数据后再插入复用旧id的数据，可能会由于存放在不同页上造成物理顺序与逻辑顺序不一致，此时可以通过优化表改善：optimize table table_name。 2： 带排序 - 排序字段没有索引 select id,m_id, name, identity_no, address, create_time, modify_time from t1 order by create_time desc limit 10000, 20; 复制代码执行时间： 2.015s elapsed 排序字段有索引：（注：t2表数据是直接从t1复制得到，再添加create_time索引）相同的sql查询t2。 select id,m_id, name, identity_no, address, create_time, modify_time from t2 order by create_time desc limit 10000, 20; 复制代码执行时间： 0.937s elapsed 执行计划对比： 1:
可以看到有索引的表，直接走索引取前n条数据，不需要全表扫描，也不会用到filesor。 结论2：给常用字段加索引，包括排序字段。 新的问题： 以上2个场景看似已可以解决大部分分页问题，但： 排序字段有索引就一定快吗？1w的时候速度较快，换成查询100w之后的数据呢？ 如果当前表已经有多个索引，不适合再添加索引了呢？ 3： 排序字段有索引，但分页再深一点： 从100w开始取20条 select id,m_id, name, identity_no, address, create_time, modify_time from t2 order by create_time desc limit 1000000, 20; 复制代码耗时：非常慢 18.350s elapsed 执行计划：
通过执行计划发现，并没有走索引，为什么没有走索引？ 因为mysql优化器发现这条sql查询行数超过一定比例（据说是30%，但测试下来并不完全是）就会自动转换为全表扫描，能不能强制走索引呢？ 可以的，加force index(idx)。 4：强制索引 select id,m_id, name, identity_no, address, create_time, modify_time from t2 force index(idx_create_time) order by create_time desc limit 1000000, 20; 复制代码强制索引后的执行计划： 看下执行时间： 15.197s elapsed 有效果，但效果不明显，即使强制索引，mysql要取出100w+条完整数据也是非常消耗资源的，需要读取大量的索引页，频繁回表等随机IO。 结论3：即使有索引，再深一点的分页也会有问题，要避免。 以上尝试结果都没有很好的解决深分页性能问题，那是否有更好的解决方案？ 有！ 5： 带last_条件查询 select id,m_id, name, identity_no, address, create_time, modify_time from t2 where id > #{last_id},create_time > #{last_create_time} order by create_time desc limit 0, 20; 复制代码性能与正常浅分页没差别，但前提是last_*字段有索引。 同时，该方案受使用场景限制，如跳页、多排序字段等，last_*都将无法使用。 推荐使用场景：没有页码的应用，如：滑动加载下一页、只有上下页按钮等。 6：联表子查询将场景4中强制索的sql改为子查询，先测试排序字段有索引的t2表。 select id,m_id, name, identity_no, address, create_time, modify_time from t2 force index(idx_create_time) order by create_time desc limit 1000000, 20; -- 改为： SELECT id, m_id, NAME, identity_no, address, create_time, modify_time FROM t2 JOIN ( SELECT id FROM t2 ORDER BY create_time desc LIMIT 1000000, 20 ) x USING ( id ); 复制代码执行时间：0.742 s elapsed 效果明显。（原sql执行时长：15s+） 再测试create_time没有索引的t1，看下效果如何？ -- 在t1执行： SELECT id, m_id, NAME, identity_no, address, create_time, modify_time FROM t1 JOIN ( SELECT id FROM t1 ORDER BY create_time desc LIMIT 1000000, 20 ) x USING ( id ); 复制代码执行时间：2.866s elapsed 效果明显。（原sql执行时长：18s+） 改成子查询关联后，无论有没有索引都节省了大量时间，下面来分析下原因。 执行计划：
这两条关联查询的执行计划对比差距还是在于子查询是否使用索引排序，1使用索引所以快。 对比子查询与非子查询执行计划：
区别： 全表扫描与强制索引区别上面已经讲过，那强制索引跟子查询区别是什么？ 看起来就只是多了一个Using index，那什么是Using index呢? 简单说就是直接通过索引树就能拿到查询字段的值，所以快的原因是子查询方式减少了回表查询操作，进而减少了大量数据的回表IO，因此更高效。 不带索引的t1：
区别： 乍一看发现这两条查询没有区别，不仅没区别，子查询还比直接查询复杂了，但它反而快了，为什么？ 这里的关键其实就在于Using filesort。 Using filesort时，mysql的两种排序策略。 一、单路排序 根据条件将所有查询字段数据取出到sort buffer缓冲区。 缓冲区满了根据排序字段执行一次排序（快排）把然后把排序后的数据写到临时文件。 将所有数据取出排序后，对所有临时文件按顺序做合并（归并排序）再写回到文件，直到最后所有文件合并完成。 从临时文件中读取满足分页条件所需数据返回，如果首次归并就可以取到分页数据则直接返回（浅分页）。 双路排序 根据查询条件将row_id和排序字段取出放到sort buffer （区别1）。 缓冲区满了根据排序字段执行一次排序（快排）把然后把排序后的数据写到临时文件。 将所有数据取出排序后，对所有临时文件按顺序做合并（归并排序）再写回到文件，直到最后所有文件合并完成。 从临时文件中读取满足分页条件的row_id，再通过row_id读取对应行数据返回（区别2）。MySQL在4.1之前都是双路排序，之后优化改为满足条件默认单路排序，条件为：查询字段数据大小小于max_length_for_sort_data值，但改到最小值测试也没有看到变化。 因此：子查询方式快的原因可以确定了，子查询只取了create_time+id到sort buffer（相当于双路排序的做法）, 相比直接查询，省去了绝大部分字段，减少大量临时文件IO操作，因此提高查询效率。 另一个方法调整sort_buffer_size大小，上下对比。
调整后，在个人电脑上没有测试出明显效果。通过网上资料了解是可以有提升的，但该方法只能做为锦上添花，不能做为深分页的优化方案。 总结 对比文/Jacy 关注得物技术，携手走向技术的云端 |
【本文地址】