我中招了……没想到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强制排序就可以了。

结论

还是太年轻啊……


青鱼
268 声望25 粉丝

山就在那里,每走一步就近一些