我中招了……没想到mysql分页查询,还藏有这种玄机……
分页查询
分页查询大家经常用到,如果各位大侠有幸参与后台管理系统第开发,对此更是轻车熟路。不管你用怎样的分页控件,一般来说,最终会回归到mysql层面,最终通过limit
去分页。
举个例子:
1.先创建张表,如下:
名称 | 类型 | 说明 |
---|---|---|
id | bigint(20) | 主键ID |
name | varchar(20) | 姓名 |
age | int(5) | 年龄 |
bill_date | varchar(10) | 账单日 |
# 建表语句
CREATE TABLE `demo_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(5) NOT NULL DEFAULT '0' COMMENT '年龄',
`bill_date` varchar(15) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '账单日',
PRIMARY KEY (`id`),
KEY `idx_bill_date` (`bill_date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
2.造些数据:
id | name | age | bill_date |
---|---|---|---|
1 | 张 | 50 | 20200103 |
2 | 李 | 20 | 20200101 |
3 | 王 | 18 | 20200101 |
4 | 刘 | 38 | 20200101 |
5 | 牛 | 71 | 20200101 |
6 | 神 | 13 | 20200102 |
7 | 鬼 | 5 | 20200101 |
8 | 黄 | 8 | 20200102 |
9 | 赵 | 99 | 20200101 |
3.分页语句(假设每页8条数据):
# 第1页
select t.* from demo_user t
where t.bill_date like '202001%'
limit 0,8;
# 第2页
select t.* from demo_user t
where t.bill_date like '202001%'
limit 8,8;
4.分页结果
结果却很出乎意料,两次查询都没有id=1 张
这条数据,而id=9 赵
却出现了两次!
# 第1页
2 李 20 20200101
3 王 18 20200101
4 刘 38 20200101
5 牛 71 20200101
7 鬼 5 20200101
9 赵 99 20200101
6 神 13 20200102
8 黄 8 20200102
# 第2页
9 赵 99 20200101
问题分析
我之前认为mysql默认是按照id排序的,但从第1页但结果来看,并非如此。实际上,如果有索引参与其中,会按索引列排序。
如果sql中有limit,则凑够limit数就结束查询。(例子中凑够8条就结束)
好,就算是按索引列排序,那无非是把id=1 张 50 20200103
这条数据排在最后才对,在第2页应该能看到它才合理?
嗯,理论上是这样,but……mysql的查询优化好心办了坏事。
来看看这两页查询的执行计划:
- 第1页的执行计划
# 第1页
EXPLAIN select t.* from demo_user t
where t.bill_date like '202001%'
limit 0,8;
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | t | null | range | idx_bill_date | idx_bill_date | 47 | null | 9 | 100 | Using index condition |
- 第2页的执行计划
# 第2页
EXPLAIN select t.* from demo_user t
where t.bill_date like '202001%'
limit 8,8;
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | t | null | ALL | idx_bill_date | null | null | null | 9 | 100 | Using where |
我们发现,第2页的查询没有用到索引。因此,第2页会默认按id排序,得到了id最大的数据——id=9 赵 99 20200101
。
至于第2页为什么不走索引?嗯……俺也不知道
解决方案也很简单,加上order by
强制排序就可以了。
结论
还是太年轻啊……
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。