假设分页为每页10条数据：
方式一
mysql> select * from user limit 0,10;
10 rows in set (0.00 sec)
查询500万以上的数据试试
mysql> select * from user limit 5000000,10;
...
10 rows in set (4.08 sec)
结果耗时十分明显
特点：·全表扫描,速度会很慢·
方式二
mysql> select * from user where id > 5000000 limit 10;
10 rows in set (0.00 sec)
方式三
mysql> select * from user where id > 5000000 and id <= 5000000+10 ;
10 rows in set (0.00 sec)
执行情况分析:
使用explain命令：
rows：显示MySQL执行查询时检查的行数。
mysql> explain select * from user limit 5000000,10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 6264504
Extra:
1 row in set (0.00 sec)
mysql> explain select * from user where id > 5000000 limit 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 1486004
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select * from user where id > 5000000 and id <= 5000000+10 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 10
Extra: Using where
1 row in set (0.00 sec)
按照row结果性能排序：
方式一 (6264504) < 方式二(1486004) < 方式三(10)
正常情况以上方式都可以满足需求,但还有一种情况
看一下测试数据:
查询ID：
mysql> select id from user where id > 5000000 and id <= 5000000+10 ;
+---------+
| id |
+---------+
| 5000001 |
| 5000002 |
| 5000003 |
| 5000004 |
| 5000005 |
| 5000006 |
| 5000007 |
| 5000008 |
| 5000009 |
| 5000010 |
+---------+
10 rows in set (0.00 sec)
删除其中一行：
mysql> delete from user where id='5000008';
Query OK, 1 row affected (0.06 sec)
mysql> select id from user where id > 5000000 and id <= 5000000+10 ;
+---------+
| id |
+---------+
| 5000001 |
| 5000002 |
| 5000003 |
| 5000004 |
| 5000005 |
| 5000006 |
| 5000007 |
| 5000009 |
| 5000010 |
+---------+
9 rows in set (0.00 sec)
我们可以看到，在主键不连续的情况下，查询出现了问题，数据成了9行。
mysql> select id from user limit 5000000,10;
+---------+
| id |
+---------+
| 5000001 |
| 5000002 |
| 5000003 |
| 5000004 |
| 5000005 |
| 5000006 |
| 5000007 |
| 5000009 |
| 5000010 |
| 5000011 |
+---------+
10 rows in set (2.62 sec)
此时数据正常,但是就不能采用"> <"这种方式进行处理了。所以大数据量的时候，最好不好进行物理删除。
查询优化：
mysql> select user.id from user inner join ( select id from user limit 5000000,10 ) as tmp on tmp.id=user.id;
+---------+
| id |
+---------+
| 5000001 |
| 5000002 |
| 5000003 |
| 5000004 |
| 5000005 |
| 5000006 |
| 5000007 |
| 5000009 |
| 5000010 |
| 5000011 |
+---------+
10 rows in set (3.19 sec)
大量数据的话，优化的结果并不能令人满意。
如果历史数据存在物理删除，而且不再发生物理删除的情况，此时可以采用另外一张表来维护id：
创建另一张表：
CREATE TABLE `user_id_list` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULT CHARSET=utf8；
把数据导入：
INSERT INTO user_id_list (user_id) SELECT id FROM user;
查询数据：
mysql> select user_id from user_id_list where id > 5000000 and id <= 5000000+10;
+---------+
| user_id |
+---------+
| 5000001 |
| 5000002 |
| 5000003 |
| 5000004 |
| 5000005 |
| 5000006 |
| 5000007 |
| 5000009 |
| 5000010 |
| 5000011 |
+---------+
10 rows in set (0.00 sec)
数据又恢复10条，其中5000008不存在，此时可以进行表优化：
mysql> select user_id from user left join user_id_list on user.id=user_id_list.user_id where user_id_list.id > 5000000 and user_id_list.id <= 5000000+10;
+---------+
| user_id |
+---------+
| 5000001 |
| 5000002 |
| 5000003 |
| 5000004 |
| 5000005 |
| 5000006 |
| 5000007 |
| 5000009 |
| 5000010 |
| 5000011 |
+---------+
10 rows in set (0.00 sec)
当然这个处理方式缺点也很明显。所以物理删除请慎用，数据很重要。
