MySQL性能优化 利用limit分页

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

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

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

测试： 表t_test_data 存130万条数据

1. select * from t_test_data limit 0,10 

2. select * from t_test_data limit 100000,10 

3. select * from t_test_data limit 1000000,10

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

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

如何优化？？？

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

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

      select id from t_test_data limit 1000000, 1

耗时近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

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