优化方案
我们都知道任何的查询想要速度快就必须减少对全表的数据查找,而数据库的索引就可以减少对数据的查找,提高查询效率,对于分页的优化选择方案肯定第一选择也是利用索引。具体索引是怎么提升性能这里不做分析,本文主要是针对利用索引来对分页进行优化。
执行
EXPLAIN SELECT * FROM user LIMIT 900000,20
可以查看到该sql语句具体的实行计划如下图。
id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra
1 | SIMPLE | user | ALL | NULL | NULL| NULL | NULL| 1057532 |
如上图可以看出 SELECT * FROM user LIMIT 900000,20 并没有使用索引进行查找数据。我们知道id字段是主键,自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何:
SELECT id FROM user LIMIT 900000,20
0.289s
在执行EXPLAIN SELECT id FROM user LIMIT 900000,20看下该sql语句的实行计划:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra
1 | SIMPLE | user | index| NULL | id | 302 | NULL| 1049548 | Using index
从上图的可以看到使用了索引id.
现在利用该索引来查出所有的列,可以将
SELECT * FROM user LIMIT 900000,20
语句改造一下
SELECT * FROM user where id>= ( SELECT id FROM user LIMIT 900000,1) LIMIT 20
执行时间为 0.246s。
同时执行
EXPLAIN SELECT * FROM user where id>= ( SELECT id FROM user LIMIT 900000,1) LIMIT 20
执行结果:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra
1 | PRIMARY | user | range| id | id | 302 | NULL| 295666 | Using where
1 | SUBQUERY | user | index| NULL | id | 302 | NULL| 1049548| Using index
很明显也是用到了索引,查询时间才会这么快。
结论
经过实际测试,当数据量越大越复杂的时候,分页查询一定要利用索引来查询,这样才能提高系统性能。