准备工作
# 总记录数为500000mysql> select count(id) from edu_test;+-----------+| count(id) |+-----------+| 500000 |+-----------+1 row in set (0.05 sec)分析过程
从0开始查询10条：
mysql> select * from edu_test limit 0, 10;10 rows in set (0.05 sec)从20万开始查询10条：
mysql> select * from edu_test limit 200000, 10;10 rows in set (0.14 sec)从50万开始查询10条：
mysql> select * from edu_test limit 499000, 10;10 rows in set (0.21 sec)
mysql> explain select * from edu_test limit 200000, 10;+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+| 1 | SIMPLE | edu_test | NULL | ALL | NULL | NULL | NULL | NULL | 499483 | 100.00 | NULL |+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+1 row in set (0.09 sec)
- 思考：limit分页做了一个全表扫描，扫描后将从200000开始往后取10条记录返回。
优化
思路：
- 快速定位到要访问的数据行，缩小扫描范围。
方案1
- 延迟查询（先定位再查询）.
方案2、方案3
方案1：通过有序唯一索引缩小扫描范围
前提必须要id有序，要不然结果会漏掉一部分数据的。
mysql> select * from edu_test where id > 499000 order by id asc limit 10;10 rows in set (0.14 sec)mysql> explain select * from edu_test where id > 499000 order by id asc limit 10;+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| 1 | SIMPLE | edu_test | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1000 | 100.00 | Using where |+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+1 row in set (0.16 sec)# 再缩小扫描范围mysql> select * from edu_test where id between 499000 and 499020 order by id asc limit 10;10 rows in set (0.09 sec)mysql> explain select * from edu_test where id between 499000 and 499020 order by id asc limit 10;+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| 1 | SIMPLE | edu_test | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 21 | 100.00 | Using where |+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+1 row in set (0.08 sec)方案2：子查询
mysql> SELECT * FROM edu_test WHERE id >= (SELECT id FROM edu_test ORDER BY id LIMIT 499000, 1) LIMIT 10;10 rows in set (0.16 sec)mysql> explain SELECT * FROM edu_test WHERE id >= (SELECT id FROM edu_test ORDER BY id LIMIT 499000, 1) LIMIT 10;+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+| 1 | PRIMARY | edu_test | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1000 | 100.00 | Using where || 2 | SUBQUERY | edu_test | NULL | index | NULL | PRIMARY | 4 | NULL | 499001 | 100.00 | Using index |+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+2 rows in set (0.14 sec)方案3：join查询
mysql> select * from edu_test s, (select id from edu_test order by id limit 499000, 10) t where s.id = t.id;10 rows in set (0.16 sec)mysql> explain select * from edu_test s, (select id from edu_test order by id limit 499000, 10) t where s.id = t.id;+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 499010 | 100.00 | NULL || 1 | PRIMARY | s | NULL | eq_ref | PRIMARY | PRIMARY | 4 | t.id | 1 | 100.00 | NULL || 2 | DERIVED | edu_test | NULL | index | NULL | PRIMARY | 4 | NULL | 499010 | 100.00 | Using index |+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+3 rows in set (0.10 sec)实际业务场景
- 场景：
在我们设计数据库id的时候，可能采用字符串格式、有顺序的id，带有一定的业务逻辑这样的分布式id。
- 解决：
如果我们分页想要优化时候，根据减少扫描思路，可以通过where id like '10289%' 方式，先缩小范围再分页。
- 启示：
在设计数据库id主键的时候，尽量保持主键唯一且有序，最好能解决热点业务问题（如果依赖很多的非主键值，那么我们可能还需要回表操作），而且主键本身就是一种唯一索引，这种唯一有序特性可以便于帮助我们后期优化，减少扫描记录范围。
|
