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

🪐 TiDB 技术问题 部署&运维管理
1

【系统版本 & kernel 版本】

CentOS Linux release 7.6.1810 (Core)

4.20.10-1.el7.elrepo.x86_64

【TiDB 版本】

3.0.5

【我做了什么】

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

CREATE TABLE `dd_range` (
  `seller_date` date NOT NULL COMMENT '销售日期',
  `id` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
  PRIMARY KEY (`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               |
+--------------------------------+
| dd_range |
| table_1                        |
+--------------------------------+
2 rows in set (0.00 sec)

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

MySQL [sbtest]>
MySQL [sbtest]>
MySQL [sbtest]> SELECT COUNT( 1 ) num FROM dd_range PARTITION(p0) GROUP BY 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 id FROM dd_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] + '
')
            print (sql)

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

生成的文件结果
image

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

CREATE TABLE `table_1` (
  `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 id HAVING count( 1 ) > 1 ) t2;
+-----------------+
| COUNT( t2.num ) |
+-----------------+
|           10000 |
+-----------------+
1 row in set (3.68 sec)

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

【问题】

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

2

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

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

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

4

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

1 这是什么原因导致的？

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

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

5

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

1 个赞
6

老师：我看了一下这个网站，数据库内核月报
看完以后我 的理解是，mysql 数据重复是因为 order by limit 同时使用的时候才出现的这个问题，不过我觉得如果 order by 是索引，就不会出这个问题了吧！， 老师你这边有没有更权威一点儿资料来说明这件事儿呢？我想学习一下，感谢:handshake:

7

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

8

我理解了谢谢老师

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

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

9

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

10

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。