基准测试过程中有趣的mysql行为

我试图理解mysql在数据填充之后选择数据和填充之后几分钟选择数据在性能上有很大的不同。
我写的代码是：
创建五个表，其中包含一个整数主键、一些varchar（50）列，对于其中的四个表，还包含上一个表的整数外键。
用随机数据填充表，例如，每个表有10k行。
然后，它通过两种方法从所有表中选择数据：
方法#1:使用左外连接，例如：

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的响应更大）。
现在是有趣的部分。性能上似乎有很大的差别，这取决于数据填充后执行选择的时间。
如果我填充数据，等待10分钟，然后运行基准测试，我会得到非常一致的结果，其中join方法比多个查询慢大约40%。
但是，如果我填充数据，然后立即运行基准测试，那么连接会比多个查询慢几百倍（通常慢500倍，我也看到慢1000倍以上）。
我还将提到，多查询的性能似乎不会（显著）受到填充之后等待的时间的影响。这似乎只影响连接。
我还尝试反转选择的顺序（即，在连接之前进行多查询选择）——这没有什么区别。
我能够在本地MySQL5.7安装和AWSRDSMySQL（在ec2上运行代码时）上重现这种行为。
有人能解释这种行为吗？在插入大量数据后的几分钟内会发生什么，这些数据会严重影响性能？
我考虑过一些后台重建或优化索引的方法，但如果是这样的话，为什么不影响multiple querys方法呢？这些查询依赖于相同的索引。。。

更新：

附加信息：创建表、显示表状态、innodb\u缓冲区\u池\u大小和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\u buffer\u pool\u size”等变量

变量名称值
innodb\缓冲区\池\大小25769803776

闸板尺寸

32gb（使用aws rds db.m4.2xlarge）

更新2：

附加信息：解释

方法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：

（我缩短了时间。） IN() 上的查询的参数列表 Bench2 （还有其他的），但是 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

基准测试过程中有趣的mysql行为

更新：

创建表

显示表格状态

显示“innodb\u buffer\u pool\u size”等变量

闸板尺寸

更新2：

方法1：

方法2：

1条答案

相关问题

热门标签

最新问答