MYSQL大数据表 分页查询SQL语句 效率对比
测了下 360多万数据
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
分页效率测试 SELECT * FROM `xvideos` Limit方式 =========================== SELECT * FROM xvideos LIMIT 3643710 , 30 查询花费 19.3808 秒 Where ID 方式 =========================== SELECT * FROM xvideos WHERE id > 3643710 ORDER BY id LIMIT 30 查询花费 0.0379 秒 Between 方式 =========================== SELECT * FROM xvideos WHERE id BETWEEN 3643710 AND 3643710 + 30 - 1 查询花费 0.0007 秒 SubQuery 子查询方式 =========================== SELECT * FROM xvideos WHERE id > (SELECT id FROM xvideos LIMIT 3643709, 1) ORDER BY id asc LIMIT 30 查询花费 1.8362 秒 JOIN 方式 =========================== SELECT * FROM xvideos AS t1 JOIN (SELECT id FROM xvideos ORDER BY id asc LIMIT 3643709, 1) AS t2 WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT 30; 查询花费 1.1632 秒 |
哪个最好 一目了然 […]