我试图理解MySQL性能在数据填充后立即选择数据和几分钟后选择数据之间的巨大差异。
我写的代码是：
-
创建五个表，其中有一个整数主键，一些varchar(50)列，对于其中四个表，创建上一个表的整数外键。
-
用随机数据填充表格，例如每个表格10k行。
然后，它用两种方法从所有表中选择数据：
方法#1：使用LEFT OUTER JOIN，例如：
SELECT SQL_NO_CACHE
Bench1.id AS a_id, a1, a2, a3, a4, a5, a6, a7, a8, a9, a10,
Bench2.id AS b_id, b1, b2, b3, b4, b5, b6, b7, b8, b9, b10,
Bench3.id AS c_id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10,
Bench4.id AS d_id, d1, d2, d3, d4, d5, d6, d7, d8, d9, d10,
Bench5.id AS e_id, e1, e2, e3, e4, e5, e6, e7, e8, e9, e10
FROM Bench1
LEFT OUTER JOIN Bench2 ON Bench2.bench1Id = Bench1.id
LEFT OUTER JOIN Bench3 ON Bench3.bench2Id = Bench2.id
LEFT OUTER JOIN Bench4 ON Bench4.bench3Id = Bench3.id
LEFT OUTER JOIN Bench5 ON Bench5.bench4Id = Bench4.id
WHERE Bench1.id IN (342, 452, 81, 405, ...)
方法#2：使用五个独立的SELECT查询，例如：
SELECT SQL_NO_CACHE id, a1, a2, a3, ... FROM Bench1
WHERE id IN (342, 452, 81, 405, ...)
SELECT SQL_NO_CACHE id, b1, b2, b3, ... FROM Bench2 WHERE bench1Id IN (...)
SELECT SQL_NO_CACHE id, c1, c2, c3, ... FROM Bench3 WHERE bench2Id IN (...)
SELECT SQL_NO_CACHE id, d1, d2, d3, ... FROM Bench4 WHERE bench3Id IN (...)
SELECT SQL_NO_CACHE id, e1, e2, e3, ... FROM Bench5 WHERE bench4Id IN (...)
...
两种方法都会产生相同的信息(但很明显，由于数据重复，JOIN的响应更大)。
现在是有趣的部分。性能上似乎存在巨大的差异，这取决于执行SELECT数据填充后的时间。
如果我填充数据，等待10分钟，然后运行基准测试，我会得到非常一致的结果，其中JOIN方法比多个查询慢大约40%。
然而，如果我填充数据，然后立即运行基准测试，JOIN会比多个查询慢几百倍(通常慢500倍，我也看到它慢1000倍以上)。
我还将提到，多查询性能似乎不会受到填充后等待时间的(显著)影响。这似乎只影响JOIN。
我还试图颠倒SELECTs的顺序(即在JOIN之前进行多查询SELECTs)——这没有什么区别。
我能够在本地MySQL 5.7安装和AWS RDS MySQL上重现这种行为(当在EC2上运行代码时)。
有人能解释这种行为吗？INSERT处理大量数据后的几分钟内会发生什么，这些数据会严重影响性能？
我考虑过对索引进行某种后台重建或优化，但如果是这样的话，为什么它不影响多查询方法？这些查询依赖于相同的索引。。。
更新：
附加信息：CREATE TABLE、SHOW TABLE STATUS、innodb_buffer_pool_size和RAM大小：
创建表
CREATE TABLE Bench1 (
id int(11) NOT NULL,
a1 varchar(50) DEFAULT NULL,
a2 varchar(50) DEFAULT NULL,
a3 varchar(50) DEFAULT NULL,
PRIMARY KEY (id)
)
CREATE TABLE Bench2 (
id int(11) NOT NULL,
bench1Id int(11) DEFAULT NULL,
b1 varchar(50) DEFAULT NULL,
b2 varchar(50) DEFAULT NULL,
b3 varchar(50) DEFAULT NULL,
PRIMARY KEY (id),
KEY bench1Id (bench1Id),
CONSTRAINT Bench2_ibfk_1 FOREIGN KEY (bench1Id) REFERENCES Bench1 (id)
)
CREATE TABLE Bench3 (
id int(11) NOT NULL,
bench2Id int(11) DEFAULT NULL,
c1 varchar(50) DEFAULT NULL,
c2 varchar(50) DEFAULT NULL,
c3 varchar(50) DEFAULT NULL,
PRIMARY KEY (id),
KEY bench2Id (bench2Id),
CONSTRAINT Bench3_ibfk_1 FOREIGN KEY (bench2Id) REFERENCES Bench2 (id)
)
CREATE TABLE Bench4 (
id int(11) NOT NULL,
bench3Id int(11) DEFAULT NULL,
d1 varchar(50) DEFAULT NULL,
d2 varchar(50) DEFAULT NULL,
d3 varchar(50) DEFAULT NULL,
PRIMARY KEY (id),
KEY bench3Id (bench3Id),
CONSTRAINT Bench4_ibfk_1 FOREIGN KEY (bench3Id) REFERENCES Bench3 (id)
)
CREATE TABLE Bench5 (
id int(11) NOT NULL,
bench4Id int(11) DEFAULT NULL,
e1 varchar(50) DEFAULT NULL,
e2 varchar(50) DEFAULT NULL,
e3 varchar(50) DEFAULT NULL,
PRIMARY KEY (id),
KEY bench4Id (bench4Id),
CONSTRAINT Bench5_ibfk_1 FOREIGN KEY (bench4Id) REFERENCES Bench4 (id)
)
`
显示表格状态
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
Bench1 InnoDB 10 Dynamic 500 163 81920 0 0 0 2019-01-06 21:36:39 2019-01-06 21:36:39 utf8_general_ci
Bench2 InnoDB 10 Dynamic 4964 320 1589248 0 147456 4194304 2019-01-06 21:36:39 2019-01-06 21:36:39 utf8_general_ci
Bench3 InnoDB 10 Dynamic 25045 147 3686400 0 540672 4194304 2019-01-06 21:36:39 2019-01-06 21:36:40 utf8_general_ci
Bench4 InnoDB 10 Dynamic 49914 136 6832128 0 1589248 4194304 2019-01-06 21:36:39 2019-01-06 21:36:41 utf8_general_ci
Bench5 InnoDB 10 Dynamic 49259 138 6832128 0 1589248 4194304 2019-01-06 21:36:39 2019-01-06 21:36:42 utf8_general_ci
显示变量，如"innodb_buffer_pool_size">
变量名称值
innodb_buffer_pool_size 25769803776
RAM大小
32GB(使用AWS RDS db.m4.2xlarge时)
更新2:
附加信息：EXPLAINs
方法1：
EXPLAIN
SELECT SQL_NO_CACHE Bench1.id AS a_id, a1, a2, a3, Bench2.id AS b_id, b1, b2, b3, Bench3.id AS c_id, c1, c2, c3, Bench4.id AS d_id, d1, d2, d3, Bench5.id AS e_id, e1, e2, e3 FROM Bench1
LEFT OUTER JOIN Bench2 ON Bench2.bench1Id = Bench1.id
LEFT OUTER JOIN Bench3 ON Bench3.bench2Id = Bench2.id
LEFT OUTER JOIN Bench4 ON Bench4.bench3Id = Bench3.id
LEFT OUTER JOIN Bench5 ON Bench5.bench4Id = Bench4.id
WHERE Bench1.id IN (27, 315, 429, 371, 126, 104, 3, 176, 376, 128)
收益率：
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE Bench1 range PRIMARY PRIMARY 4 10 100.00 Using where
1 SIMPLE Bench2 ref bench1Id bench1Id 5 pm.Bench1.id 9 100.00
1 SIMPLE Bench3 ref bench2Id bench2Id 5 pm.Bench2.id 4 100.00
1 SIMPLE Bench4 ref bench3Id bench3Id 5 pm.Bench3.id 2 100.00
1 SIMPLE Bench5 ALL bench4Id 49860 100.00 Using where; Using join buffer (Block Nested Loop)
方法2：
(我缩短了Bench2(以及其他)上查询的IN()参数列表，但EXPLAIN的结果是完整的查询。
EXPLAIN SELECT SQL_NO_CACHE id, a1, a2, a3 FROM Bench1 WHERE id IN (271, 480, 422, 431, 256, 491, 440, 496, 225, 456);
EXPLAIN SELECT SQL_NO_CACHE id, b1, b2, b3 FROM Bench2 WHERE bench1Id IN (225, 256, 271, 422, 431, 440, 456, 480, 491, 496);
EXPLAIN SELECT SQL_NO_CACHE id, c1, c2, c3 FROM Bench3 WHERE bench2Id IN (323, 402, 1254, 1378, 1965, 2153, 2245, 2518, 2756);
EXPLAIN SELECT SQL_NO_CACHE id, d1, d2, d3 FROM Bench4 WHERE bench3Id IN (3429, 6746, 13014, 18942, 24579, 2269, 6805, 6850);
EXPLAIN SELECT SQL_NO_CACHE id, e1, e2, e3 FROM Bench5 WHERE bench4Id IN (36481, 40044, 11505, 4504, 20798, 4520, 48448, 24305);
收益率：
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE Bench1 range PRIMARY PRIMARY 4 10 100.00 Using where
1 SIMPLE Bench2 range bench1Id bench1Id 5 96 100.00 Using index condition
1 SIMPLE Bench3 range bench2Id bench2Id 5 484 100.00 Using index condition
1 SIMPLE Bench4 range bench3Id bench3Id 5 966 100.00 Using index condition
1 SIMPLE Bench5 ALL bench4Id 49860 100.00 Using where
在5.7.4中，eq_range_index_dive_limit默认值从10(在5.6.5中引入时)提高到200。这会影响IN()。
请将该数字与Bench5的IN(list)中的项目数量进行比较。我想你会发现改变的"原因"。