MySQL的limit分页性能测试加优化

日常我们分页时会用到MySQL的limit字段去处理，那么使用limit时，有什么需要优化的地方吗？
我们来做一个试验来看看limit的效率问题：
环境：CentOS 6 & MySQL 5.7
1、建议一个实验表：

collect(id[主键], title[varchar], info[text], vtype[tinyint]);
Engine: MyISAM

2、关闭查询缓存：
MySQL中的 query_cache_size 和 query_cache_type 参数。

mysql> show variables like 'query_cache%';                                                                 
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
5 rows in set (0.06 sec)

查询缓存命中情况：Qcache_hits

mysql> show status like '%qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 81      |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
8 rows in set (0.00 sec)

关闭查询缓存：

set global query_cache_size=0
set global query_cache_type=0

Note：

select SQL_NO_CACHE * from table_name;
使用SQL_NO_CACHE参数并不代表不使用缓存，而是此次查询不会缓存，MySQL中如有缓存还是会返回缓存数据，
也就是说，可能同一条sql，第一次查询时间很长，第二次就很短。

我在实验中，先是使用的xampp中的MariaDB，发现无论我怎么设置(包括关闭缓存，清除缓存)，都能使用到缓存，也就是我第二次查询时间很短。
所以之后我使用了CentOS 6内网机中的MySQL 5.7做实验。

3、开始试验：

3.a、无索引情况
collect表插入10万条数据。【此时表无索引】

mysql> select count(*) from collect;
+----------+
| count(*) |
+----------+
|   100001 |
+----------+
1 row in set (0.00 sec)

select id,title字段，limit 1000,1 非常快

mysql> FLUSH QUERY CACHE;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> select SQL_NO_CACHE id,title from collect limit 1000,10;
10 rows in set, 1 warning (0.07 sec)

select id,title字段，limit 90000,1 慢了

mysql> FLUSH QUERY CACHE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select SQL_NO_CACHE id,title from collect limit 90000,10;
10 rows in set, 1 warning (2.02 sec)

select id字段，limit 90000,1 非常快

mysql> FLUSH QUERY CACHE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select SQL_NO_CACHE id from collect limit 90000,10;      
10 rows in set, 1 warning (0.02 sec)

那么我们想查询title怎么快呢？
网上有的解决方式为：用id做条件去查 【可用，效率可以】

mysql> FLUSH QUERY CACHE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select SQL_NO_CACHE id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;
10 rows in set (0.01 sec)

mysql> select SQL_NO_CACHE id,title from collect order by id limit 90000,10;
10 rows in set (2.07 sec)

以上可以看出来，用id做limit，然后再以id为条件查询，效率比直接id,title做limit快的多。

3.b 加单个索引
那么我们以其他字段做where条件呢？如vtype字段。
为vtype建立索引：【该方法很慢】

ALTER TABLE collect ADD INDEX search(vtype);

用vtype做where条件做limit 90000,10

mysql> FLUSH QUERY CACHE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select SQL_NO_CACHE id from collect where vtype=1 order by id limit 90000,10;
Empty set (2.50 sec)

vtype有索引为何这么慢呢？个人猜测可能是做了全表扫描而没走索引
试验一下 limit 1000,10呢？

mysql> FLUSH QUERY CACHE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select SQL_NO_CACHE id from collect where vtype=1 order by id limit 1000,10;
10 rows in set (0.03 sec)

计算一下 0.03*90 = 2.7 和 limit 90000,10 差不多

那么加复合索引呢？会不会提高效率呢？

3.c、(vtype,id)复合索引 【只查主键非常快】

mysql> alter table collect add index search(vtype,id);
Query OK, 100001 rows affected (8.72 sec)
Records: 100001  Duplicates: 0  Warnings: 0

mysql> FLUSH QUERY CACHE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select SQL_NO_CACHE id from collect where vtype=1 order by id limit 90000,10;
Empty set (0.01 sec)

mysql> FLUSH QUERY CACHE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select SQL_NO_CACHE id,title from collect where vtype=1 order by id limit 90000,10;
Empty set (2.56 sec)

可以看出来，(vtype,id)复合索引下，limit偏移量大的情况下，只查询主键id，是非常快的。

3.d、(id,vtype)复合索引 【没有vtype,id快】

mysql> drop index search on collect;
Query OK, 100001 rows affected (4.31 sec)
Records: 100001  Duplicates: 0  Warnings: 0

mysql> select SQL_NO_CACHE id from collect where vtype=1 order by id limit 90000,10;      
Empty set (2.28 sec)

mysql> alter table collect add index search(id,vtype);    
Query OK, 100001 rows affected (6.46 sec)
Records: 100001  Duplicates: 0  Warnings: 0

mysql> select SQL_NO_CACHE id from collect where vtype=1 order by id limit 90000,10;
Empty set (0.07 sec)

mysql> select SQL_NO_CACHE id,title from collect where vtype=1 order by id limit 90000,10;
Empty set (1.99 sec)

(id,vtype)复合索引下，只查询主键id的limit看起来好像和(vtype,id)复合索引没什么区别，但是我试验中，将数据加到160万，就能看出来，(vtype,id)复合索引几乎不受影响，(id,vtype)复合索引却开始变慢了。

那么现在查询到id了，我们通过id去找title字段值。使用in去找，你会发现还是很快的。

select SQL_NO_CACHE * from collect where id in(901744,901772,901773,901794);
4 rows in set (0.10 sec)

4、最后

再测试100万，160万，结论为(vtype,id)复合索引查id，MySQL in()去查多个id的值，完全可以，查询效率没问题，还是很快，不过再往上，我就没再测试过了。

5、总结

以collect(id[主键], title[varchar], info[text], vtype[tinyint]); Engine: MyISAM表，为例：

数据160万，查询条件vtype，查title字段

优化Limit步骤：

1、加(vtype,id)复合索引

2、select id from collect where vtype=1 limit 900000,10;

3、select id,title from collect where id in(1,2,3,4,5,6,7,8,9,10);