SQL：如何快速取1000w行的后100个数据

原创已于 2024-07-21 18:11:11 修改 · 820 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

于 2024-07-17 12:20:11 首次发布

SQL 专栏收录该内容

4 篇文章

订阅专栏

建表，并用navicat生成1000w条表数据：

在数据库中生成大量的测试数据时，可以利用内存表插入速度快的特点，先利用函数和存储过程在内存表中生成数据，然后再从内存表插入普通表中，以提高生成速度。

内存表（Memory Table）：在某些数据库管理系统（如 MySQL）中，又称为堆表（Heap Table），是一种将数据存储在内存中的表。这种表的特点是读写速度非常快，因为它们直接在内存中进行操作，而不是在磁盘上。然而，内存表的数据是临时的，一旦数据库重启或崩溃，表中的数据将会丢失。

参考：MySQL如何快速生成千万数据量？

DROP TABLE IF EXISTS `test_user`;
CREATE TABLE `test_user`(
	`id` INT  NOT NULL AUTO_INCREMENT COMMENT '主键',
	`name` VARCHAR(50) DEFAULT '' COMMENT '用户名称',
	`email` VARCHAR(50) NOT NULL COMMENT '邮箱',
	`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
	`password` VARCHAR(100) NOT NULL COMMENT '密码',
	`age` TINYINT DEFAULT '0' COMMENT '年龄',
	`create_time` DATETIME DEFAULT NOW(),
	`update_time` DATETIME DEFAULT NOW(),
	PRIMARY KEY (`id`) 
)ENGINE = INNODB DEFAULT CHARSET=utf8mb4;

Navicat数据生成

注：如果报错 Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again，可以把 max_binlog_cache_size 设置大一点(2M)，具体如下：

SET GLOBAL max_binlog_cache_size = 2*1024*1024*1024;
SHOW VARIABLES LIKE 'max_binlog_cache_size';

测试不同的查询方式：

参考：mysql性能优化：单表1400w查询最后十条数据（耗时0.036s）

# 常规查询
SELECT * FROM test_user 
LIMIT 9999900, 100; # 6.285 s

# 先查id，利用主键索引加速查询
SELECT * FROM test_user a, (
	SELECT id FROM test_user
	LIMIT 9999900, 100
) b 
WHERE a.id = b.id; # 2.477 s

# 如果主键自增，则直接按主键降序排列后取前100行
SELECT * FROM test_user 
ORDER BY id DESC
LIMIT 100; # 0.024 s