15 - MySQL分页性能相关

# max_id
# min_id
-- 下一页：
  select * from userinfo3 where id > max_id limit 10;
-- 上一页：
  select * from userinfo3 where id < min_id order by id desc limit 10;

-- max_id 
-- min_id
-- 在196页面调到199  (199-196)*页码最大显示数 = 30
select * from userinfo3 where id in (
select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10)

-- 直接使用limit不走索引比较慢

mysql> explain select * from user  limit 2000000,10;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4762840 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)

-- 记录当前页最大或最小id 走range类型索：引对索引列进行范围查找
mysql> explain select * from user where id >2000000 limit 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 2381420 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


-- 查看对比 使用索引几乎是毫秒级

mysql> select * from user where id >4000000 limit 10;
+---------+------------+--------+-------------------+
| id      | name       | gender | email             |
+---------+------------+--------+-------------------+
| 4000001 | lin4000001 | 男     | lin4000001@qq.com |
| 4000002 | lin4000002 | 女     | lin4000002@qq.com |
| 4000003 | lin4000003 | 男     | lin4000003@qq.com |
| 4000004 | lin4000004 | 女     | lin4000004@qq.com |
| 4000005 | lin4000005 | 男     | lin4000005@qq.com |
| 4000006 | lin4000006 | 女     | lin4000006@qq.com |
| 4000007 | lin4000007 | 男     | lin4000007@qq.com |
| 4000008 | lin4000008 | 女     | lin4000008@qq.com |
| 4000009 | lin4000009 | 男     | lin4000009@qq.com |
| 4000010 | lin4000010 | 女     | lin4000010@qq.com |
+---------+------------+--------+-------------------+
10 rows in set (0.00 sec)

-- 未使用索引,秒级

mysql> select * from user  limit 4000000,10;
+---------+------------+--------+-------------------+
| id      | name       | gender | email             |
+---------+------------+--------+-------------------+
| 4000001 | lin4000001 | 男     | lin4000001@qq.com |
| 4000002 | lin4000002 | 女     | lin4000002@qq.com |
| 4000003 | lin4000003 | 男     | lin4000003@qq.com |
| 4000004 | lin4000004 | 女     | lin4000004@qq.com |
| 4000005 | lin4000005 | 男     | lin4000005@qq.com |
| 4000006 | lin4000006 | 女     | lin4000006@qq.com |
| 4000007 | lin4000007 | 男     | lin4000007@qq.com |
| 4000008 | lin4000008 | 女     | lin4000008@qq.com |
| 4000009 | lin4000009 | 男     | lin4000009@qq.com |
| 4000010 | lin4000010 | 女     | lin4000010@qq.com |
+---------+------------+--------+-------------------+
10 rows in set (2.18 sec)