MYSQL大数据表 分页查询SQL语句 效率对比

Warning: Undefined variable $toReturn in /www/wwwroot/blog.cnlabs.net/wp-content/plugins/searchterms-tagging-2/searchterms-tagging2.php on line 2007

测了下 360多万数据

分页效率测试
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 秒

哪个最好 一目了然 节省你时间了……

最近通过搜索访问本文章的关键词:

• xvideos (11)