SQL默认排序遇到的坑

默认排序遇到的坑

问题描述

根据id分页查询，每次1000条，默认排序方式（主键）。

在未知情况下，表被加了索引，sql命中新索引，排序方式改变，导致根据id分页查询，数据有遗漏。

问题重现

表结构

SHOW CREATE TABLE com.account_day_snapshot;

CREATE TABLE `account_day_snapshot` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `day` date NOT NULL COMMENT '每日余额日期',
  `uid` int(11) NOT NULL DEFAULT '0',
  `type` int(11) NOT NULL DEFAULT '0',
  `balance` decimal(32,16) NOT NULL DEFAULT '0.0000000000000000' COMMENT '余额',
  PRIMARY KEY (`id`),
  KEY `idx_day` (`day`)
) ENGINE=InnoDB AUTO_INCREMENT=506499565 DEFAULT CHARSET=utf8 COMMENT='账户每日余额快照'

查询数据

数据默认使用id排序

SELECT * from com.account_day_snapshot
 WHERE id >=0 and day = '2021-04-28' 
 and type = '2011002';

添加索引

添加唯一索引：day_type_uid索引

show index from com.account_day_snapshot;
show index from com.account_day_snapshot_copy1;

查询数据

数据命中udx_day_type_uid索引，使用uid排序

 SELECT * from com.account_day_snapshot_copy1
 WHERE id >=0 and day = '2021-04-28' 
 and type = '2011002';

SQL分析

EXPLAIN
SELECT * from com.account_day_snapshot
 WHERE id >=0 and day = '2021-04-28' 
 and type = '2011002';

-- 加了新索引
 EXPLAIN
 SELECT * from com.account_day_snapshot_copy1
 WHERE id >=0 and day = '2021-04-28' 
 and type = '2011002';

由此可以看出，未加新索引之前，查询命中idx_day索引，使用id排序；加新索引之前，查询命中udx_day_type_uid索引，使用uid排序，id不是有序的。因此使用id作为条件分页查询时，数据会有遗漏。

问题解决

• 使用强制索引

  SELECT  * from com.account_day_snapshot_copy1 force index( idx_day )
   WHERE id >=0 and day = '2021-04-28' 
   and type = '2011002';
  

• 指定排序字段

   SELECT  * from com.account_day_snapshot_copy1
   WHERE id >=0 and day = '2021-04-28' 
   and type = '2011002' ORDER BY id;
  

性能对比

 EXPLAIN
   SELECT  * from com.account_day_snapshot_copy1 force index( idx_day )
 WHERE id >=0 and day = '2021-04-28' 
 and type = '2011002';
 
  EXPLAIN
   SELECT  * from com.account_day_snapshot_copy1
 WHERE id >=0 and day = '2021-04-28' 
 and type = '2011002' ORDER BY id;

这里采用指定排序字段更优，当然具体情况需要根据线上实际数据量判断。

更多MySQL问题可以参考：
想知道大厂核心业务系统数据储存方法？ 懂分布式架构设计就够了！

极数云周CEO-周彦伟，带你掌握MySQL的底层运行机制，会学如何处理海量业务数据和高并发请求。