MySQL性能优化 利用limit分页

博客已转至：[url]http://www.muxuanli.com/lmx[/url]

MySQL 数据库的limit支持如下形式的选择:  limit n,m
表示从第n条记录开始选择m条记录。而大多数开发人员喜欢使用这类语句来解决Web中经典的分页问题。对于小规模的数据，这并不会有太大的问题。对于论坛这类可能具有非常大规模数据的应用来说，limit n,m 的效率是十分低的。因为每次都需要对数据进行选取。如果只是选取前5条记录，则非常轻松和容易；但是对100万条记录，选取从80万行记录开始的5条记录，则还需要扫描记录到这个位置。—《MySQL技术内幕》

[i][b]从上文可知，limit 10000,20意味着扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行；问题就在这里，如果是limit 100000,100，需要扫描100100行，在一个高并发的应用里，每次查询需要扫描超过10W行，性能肯定大打折扣。 [/b][/i]

测试： 表t_test_data 存130万条数据

1. select * from t_test_data limit 0,10 

[img]http://dl.iteye.com/upload/picture/pic/135885/6f8f4529-3929-341e-97d3-b06dffdb2ed7.png[/img]

2. select * from t_test_data limit 100000,10 

[img]http://dl.iteye.com/upload/picture/pic/135887/399b0c11-63d8-3b2c-a787-47af6075cda0.png[/img]

3. select * from t_test_data limit 1000000,10

[img]http://dl.iteye.com/upload/picture/pic/135889/daba1522-6688-373d-91da-84b6fa513933.png[/img]

上述测试数据可见，10万条数据耗时约2.8秒，而百万条数据耗时达到18秒！

explain select * from t_test_data limit 1000000,10 type 值为all 全表扫描，影响性能。

如何优化？？？

[i][b]优化的关键是要做到让MySQL每次只扫描10条记录，我们可以使用limit n，这样性能就没有问题，因为MySQL只扫描n行。我们可以通过子查询先获取起始记录的id,然后利用 select * from t_test_data where id > x limit 10 来实现。[/b][/i]

Step1 : 获取起始id，假设起始记录是1000000

      select id from t_test_data limit 1000000, 1

[img]http://dl.iteye.com/upload/picture/pic/135891/91cecd9c-6773-301b-a8cd-a00a6450a332.png[/img]

耗时近27秒！ 给id加索引之后，再测试，耗时02~0.3秒【ps:100多万数据，创建index耗时约37秒】

Step2 : limit 查询

select * from t_test_data where id >= (select id from t_test_data limit 1000000, 1) limit 10

[img]http://dl.iteye.com/upload/picture/pic/135893/16fd3557-5c6c-3371-b82b-b0cef8c7e71a.png[/img]

参考：
http://database.51cto.com/art/201005/200395.htm
http://www.111cn.net/database/mysql/50921.htm