操作环境:

• MySQL: 5.7.26
• Navicat for MySQL

目的: MySQL千万级数据的优化查询

1. 创建1000w数据

1.1 建表

建表SQL语句

CREATE TABLE `big_data` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(16) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX idx_name(`name`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

big_data表一共三个字段id(主键),name(创建索引),age,存储引擎使用的INNODB,INNODB有一个注意的地方:INNODB引擎数据量超过2000W,读写性能会有很大下降。

1.2 插入1000W条数据

向MySQL中插入数据我能想到的只有2种方式;

• 使用编程语言连接MySQL然后插入1000W数据
• 使用MySQL存储过程
  为了方便我使用存储过程:

创建存储过程

CREATE PROCEDURE `insert_data`(IN num INT)
BEGIN
DECLARE n INT DEFAULT 1;
WHILE n <= num DO
INSERT INTO big_data(name,age,email)values(concat('alex',n),rand()*50);
set n=n+1;
end while;
ENd;

执行存储过程插入1000W数据

CALL insert_data(10000000);

我使用Navicat这个MySQL可视化工具,插入1000W数据比较耗时，这里说一下如何通过Navicat工具查看执行时间与插入了多少条数据。

1.png 1.png

插入数据耗时很长，我没记准确时间，但是我通过了一波死神VS火影3.3.

2. 分页查询

2.1 普通分页查询

编号	SQL语句	耗时(s)
1	select * from big_data limit 3000000,10;	1.381
2	select id from big_data limit 3000000,10;	1.107
3	SELECT * from big_data LIMIT 9000000,10;	4.764

上面5条SQL我们逐一对比：

• 1号与2号对比: 1号2号都是用limit a,b 这种方式，MySQL执行时候会进行全表扫描，从开头扫描，直到扫到300W，然后取后面10条数据。2号耗时比1号长是因为，2号只取了id，而1号还要取其他所有的字段。两者时间差为274ms，虽然减少了，但是相差页不是很明显。

• 2号与3号对比: 上面介绍了对于limit a,b 这种形式会根据主键id进行全表扫描，从1一直查到a，a值越大，耗时越久。

2.2 主键连续查询(WHERE优化分页查询)

编号	SQL语句	耗时(s)
1	SELECT * FROM big_data WHERE id>=9000000 LIMIT 10;	0.226
2	SELECT * FROM big_data WHERE id>=9000000 AND id<=9000000+10;	0.119

1号SQL通过WHERE可以通过主键索引快速定位到第900W条记录处，然后取10条记录(注意这里包括第900W条记录，与 limit a,b不一样,limit 不包括第a条记录)。2号和1号相同。与普通查询相比快了很多。

但是这种WHERE分页查询只能够在主键id连续的情况下使用，如果主键id不是连续，那么它的性能会大大下降(这个复现，我手工试了以下把第900W条记录删除，在查询速度没有减慢，再把第900W条记录后面的10条删除了，在查询速度还是没有减慢，复现不出来，此处暂时搁着)。

// TODO id不连续使用WHERE查询

---

---

2.3 主键不连续查询(关联查询)

在2.1普通分页查询种，只查询id的速度也是可以接收，所以我们可以先把id查询出来，然后再根据id查询数据记录。

编号	SQL语句	耗时(s)
1	select id from big_data limit 3000000,10;	1.127
2	SELECT t.* FROM big_data t JOIN ( SELECT id FROM big_data LIMIT 3000000,10 ) tmp ON t.id = tmp.id;	1.209
3	SELECT t.* FROM big_data t JOIN ( SELECT id FROM big_data LIMIT 9000000,10 ) tmp ON t.id = tmp.id;	3.554

SQL 解析：子查询( SELECT id FROM big_data LIMIT 9000000,10 ) tmp用来查找分页数据id，将结果集保存到tmp临时表种，再通过JOIN ON 连接查询记录。可以看到还是有一定优化效果，但是如果分页查询的数据靠后的话(如3号第900W条开始)，查询需要的时间也不是很理想。所以对于查询靠后的数据通常再业务处理，用户只能查询前面的数据。
比如百度搜索，只显示前面的76页数据。

1.png