LIMIT 分页查询为什么会有重复数据?怎么解决?

系统版本 & kernel 版本

CentOS Linux release 7.6.1810 (Core)

4.20.10-1.el7.elrepo.x86_64

TiDB 版本

3.0.5

我做了什么

1 创建一张存放 3000万数据的 分区表

CREATE TABLE `dc_flowdata_deliver_sale_range` (
  `seller_date` date NOT NULL COMMENT '销售日期',
  `paas_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
  PRIMARY KEY (`paas_id`,`seller_date`)
) 

PARTITION BY RANGE ( year(`seller_date`) ) (
  PARTITION p0 VALUES LESS THAN (2016),
  PARTITION p1 VALUES LESS THAN (2020),
  PARTITION p2 VALUES LESS THAN (2026)
);
MySQL [sbtest]> show tables;
+--------------------------------+
| Tables_in_sbtest               |
+--------------------------------+
| dc_flowdata_deliver_sale_range |
| table_1                        |
+--------------------------------+
2 rows in set (0.00 sec)

MySQL [sbtest]>
MySQL [sbtest]>
MySQL [sbtest]>
MySQL [sbtest]> SELECT COUNT( 1 ) num FROM dc_flowdata_deliver_sale_range PARTITION(p0);
+----------+
| num      |
+----------+
| 30000000 |
+----------+
1 row in set (7.71 sec)

MySQL [sbtest]>
MySQL [sbtest]>
MySQL [sbtest]> SELECT COUNT( 1 ) num FROM dc_flowdata_deliver_sale_range PARTITION(p0) GROUP BY paas_id HAVING count( 1 ) > 1;
Empty set (18.22 sec)

MySQL [sbtest]>
MySQL [sbtest]>
MySQL [sbtest]>

2 测试分页查询的数据是否会有重复数据, 分页查询 100万数据; 写入到 dist/all.txt文件中

    def local_write_file(self):

        for i in range(0, 100):
            offset = 10000
            sql = "SELECT paas_id FROM dc_flowdata_deliver_sale_range PARTITION(p0) LIMIT %d, %d" % ((i * offset), offset)
            # 执行SQL语句
            self.cursor.execute(sql)
            # 获取所有记录列表
            rows = self.cursor.fetchall()
            # 将所有内容写入到本地
            for row in rows:
                self.save_file('./dist/', 'all.txt', row[0] + '\n')
            print (sql)

        # 关闭数据库连接
        self.db.close()

生成的文件结果 image

3 将文件中的数据导入到一张新的 空表中 table_1

CREATE TABLE `table_1` (
  `paas_id` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
MySQL [sbtest]>
MySQL [sbtest]>
MySQL [sbtest]> SELECT COUNT( 1 ) num FROM table_1;
+---------+
| num     |
+---------+
| 1000000 |
+---------+
1 row in set (0.64 sec)

MySQL [sbtest]>
MySQL [sbtest]>
MySQL [sbtest]> SELECT
    -> COUNT( t2.num )
    -> FROM
    -> ( SELECT COUNT( 1 ) num FROM table_1 GROUP BY paas_id HAVING count( 1 ) > 1 ) t2;
+-----------------+
| COUNT( t2.num ) |
+-----------------+
|           10000 |
+-----------------+
1 row in set (3.68 sec)

MySQL [sbtest]>
MySQL [sbtest]>
MySQL [sbtest]>

问题

为什么我查询出来的数据会有 10000条的重复数据

SELECT paas_id FROM dc_flowdata_deliver_sale_range PARTITION(p0) LIMIT %d, %d" 这个是你的插入数据。你 dc_flowdata_deliver_sale_range 这个 paas_id 本来就是有大量重复数据的吧?

        sql = "SELECT paas_id FROM dc_flowdata_deliver_sale_range PARTITION(p0) LIMIT %d, %d" % ((i * offset), offset)

原表查询的时候加 order by 即可

感谢老师的回答,我还有几个问题

1 这是什么原因导致的?

2 加上order by 会解决问题,但是同样会有性能上的影响吧?

3 还有没有更好的解决办法呢?比如说调整一下TiDB的配置?

你好这个行为和 MySQL 是一样的 ,MySQL 使用 limit 进行分页时,也可能会出现重复数据,需要通过加入 order by 子句可以解决,但是需要注意的是,如果排序字段有相同值的情况下,由于排序字段数据重复,可能会导致每次查询排序后结果顺序不同,分页还是会出现重复数据,这时可以加入第二个排序字段,提高排序的唯一性,最好保证排序的字段在表中的值是唯一的,这样就可以少写一个排序字段,增加查询效率,因为 order by 后面有多个排序字段时,无法用到索引。

1赞

老师:我看了一下这个网站,http://mysql.taobao.org/monthly/2015/06/04/ 看完以后我 的理解是,mysql 数据重复是因为 order by limit 同时使用的时候才出现的这个问题,不过我觉得如果 order by 是索引,就不会出这个问题了吧!, 老师你这边有没有更权威一点儿资料来说明这件事儿呢?我想学习一下,感谢:handshake:

可以参考 mysql 官方的介绍 https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

我理解了谢谢老师

1 从 MySQL 5.6 以后 对 LIMIT 进行了优化,使用了堆排序

2 尽量保证要分页的数据列数据唯一,添加唯一索引(增加查询效率),添加排序(防止数据错乱)

:+1::+1::+1: