在 MySQL 中,limit X,Y 的查问中,X 值越大,那么查问速度也就越慢,例如以下示例:
所以,可以看出,limit 中 X 值越大,那么查问速度都越慢。
这个疑问呢其实就是 MySQL 中典型的深度分页疑问。那疑问来了,为什么 limit 越往后查问越慢?如何优化查问速度呢?
在数据库查问中,当经常使用 LIMIT x, y 分页查问时,假设 x 值越大,查问速度或许会变慢。这重要是由于数据库须要扫描和跳过 x 条记载能力前往 y 条结果。随着 x 的参与,须要扫描和跳过的记载数也参与,从而造成性能降低。
关于 MySQL 深度分页比拟典型的优化手腕有以下两种:
起始 ID 定位法指的是 limit 查问时,指定起始 ID。而这个起始 ID 是上一次性查问的最后一条 ID。例如上一次性查问的最后一条数据的 ID 为 6800000,那我们就从 6800001 开局扫描表,间接跳过前面的 6800000 条数据,这样查问的效率就高了,详细成功 SQL 如下:
select name, age, genderfrom personwhere id > 6800000 -- **成功 SQLorder by id limit 10;
为什么起始ID查问效率高呢?
因此这种查问是以上一次性查问的最后 ID 作为起始 ID 启动查问的,而上次的 ID 曾经定位到详细的位置了,所以只要要遍历 B+ 树叶子节点的双向链表(主键索引的底层数据结构)就可以查问到前面的数据了,所以查问效率就比拟高,如下图所示:
假设上次查问结果为 9,之后再查问时,只要要从 9 之后再遍历 N 条数据就能查问出结果了,所以效率就很高。
这种查问模式,只适宜一页一页的数据查问,例如手机 APP 中刷资讯时那种瀑布流模式。
但假设用户是跳着分页的,例如查问完第 1 页之后,间接查问第 250 页,那么这种成功模式就不行了。
此时我们为了查问效率,可以经常使用索引笼罩加子查问的模式,详细成功如下。
假定,我们未优化前的 SQL 如下:
select name, age, genderfrom personorder by createtime desclimit 1000000,10;
此时,我们可以做以下优化:
SELECT p1.name, p1.age, p1.genderFROM person p1JOIN (SELECT id FROM person ORDER BY createtime desc LIMIT 1000000, 10) AS p2 ON p1.id = p2.id;
相比于优化前的 SQL,优化后的 SQL 将不须要频繁回表查问了,由于子查问中只查问主键 ID,这时可以经常使用索引笼罩来成功。那么子查问就可以先查问出一小局部主键 ID,再启动查问,这样就可以大大优化查问的效率了。