mysql - 1行表之间的MySQL连接非常慢
问题描述
我正在对我的数据子集测试一些连接查询。我加入了下面指定的 5 个表。最初我在表中只有 1 行，B表中有超过 5500 万行A，表 C、D、E 少于 1000 行。连接查询瞬间连接了表 A、C、D、E，但是当我将 B 添加到连接时，它需要很长时间。当我在表 B 中添加另一行（现在为 2 行）时，它能够立即加入。所有表在连接的列上都有相关索引。我以前从未见过这种行为，有什么解释吗？
MariaDB [test]> desc A;
+--------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+----------------+
| uid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| BID | int(10) unsigned | NO | MUL | NULL | |
| CID | int(10) unsigned | NO | MUL | NULL | |
| DID | int(10) unsigned | NO | MUL | NULL | |
| EID | int(10) unsigned | NO | MUL | NULL | |
+--------------+------------------+------+-----+---------+----------------+
MariaDB [test]> desc B;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| uid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(250) | NO | UNI | NULL | |
+-------+------------------+------+-----+---------+----------------+
MariaDB [test]> desc C;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| uid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(250) | NO | UNI | NULL | |
+-------+------------------+------+-----+---------+----------------+
MariaDB [test]> desc D;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| uid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(250) | NO | UNI | NULL | |
+-------+------------------+------+-----+---------+----------------+
MariaDB [test]> desc E;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| uid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(250) | NO | UNI | NULL | |
+-------+------------------+------+-----+---------+----------------+
以下是这两种情况的解释计划：
######### with 1 rows in B
MariaDB [test]> EXPLAIN EXTENDED select * from A use index (PRIMARY), B, C, D, samples as E where A.BID = B.uid and A.CID=C.uid and A.DID = D.uid and A.EID = E.uid and A.uid>0 order by A.uid limit 10;
+------+-------------+-------+--------+----------------+---------+---------+-----------------+----------+----------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+--------+----------------+---------+---------+-----------------+----------+----------+-------------------------------------------------+
| 1 | SIMPLE | B | index | PRIMARY,ix_uid | name | 752 | NULL | 1 | 100.00 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | A | range | PRIMARY | PRIMARY | 4 | NULL | 27965017 | 100.00 | Using where; Using join buffer (flat, BNL join) |
| 1 | SIMPLE | E | eq_ref | PRIMARY,ix_uid | PRIMARY | 4 | hg38.A.EID | 1 | 100.00 | |
| 1 | SIMPLE | C | eq_ref | PRIMARY,ix_uid | PRIMARY | 4 | hg38.A.CID | 1 | 100.00 | |
| 1 | SIMPLE | D | eq_ref | PRIMARY,ix_uid | PRIMARY | 4 | hg38.A.DID | 1 | 100.00 | |
+------+-------------+-------+--------+----------------+---------+---------+-----------------+----------+----------+-------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)
MariaDB [test]> EXPLAIN EXTENDED select * from A use index (PRIMARY), C, D, E where A.CID=C.uid and A.DID = D.uid and A.EID = E.uid and A.uid>0 order by A.uid limit 10;
+------+-------------+-------+--------+----------------+---------+---------+-----------------+----------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+--------+----------------+---------+---------+-----------------+----------+----------+-------------+
| 1 | SIMPLE | A | range | PRIMARY | PRIMARY | 4 | NULL | 27965017 | 100.00 | Using where |
| 1 | SIMPLE | E | eq_ref | PRIMARY,ix_uid | PRIMARY | 4 | hg38.A.EID | 1 | 100.00 | |
| 1 | SIMPLE | C | eq_ref | PRIMARY,ix_uid | PRIMARY | 4 | hg38.A.CID | 1 | 100.00 | |
| 1 | SIMPLE | D | eq_ref | PRIMARY,ix_uid | PRIMARY | 4 | hg38.A.DID | 1 | 100.00 | |
+------+-------------+-------+--------+----------------+---------+---------+-----------------+----------+----------+-------------+
4 rows in set, 1 warning (0.01 sec)
######### with 2 rows in B
MariaDB [test]> EXPLAIN EXTENDED select * from A use index (PRIMARY), B, C, D, samples as E where A.BID = B.uid and A.CID=C.uid and A.DID = D.uid and A.EID = E.uid and A.uid>0 order by A.uid limit 10;
+------+-------------+-------+--------+----------------+---------+---------+---------------------+----------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+--------+----------------+---------+---------+---------------------+----------+----------+-------------+
| 1 | SIMPLE | A | range | PRIMARY | PRIMARY | 4 | NULL | 27965017 | 100.00 | Using where |
| 1 | SIMPLE | D | eq_ref | PRIMARY,ix_uid | PRIMARY | 4 | hg38.A.DID | 1 | 100.00 | |
| 1 | SIMPLE | E | eq_ref | PRIMARY,ix_uid | PRIMARY | 4 | hg38.A.EID | 1 | 100.00 | |
| 1 | SIMPLE | C | eq_ref | PRIMARY,ix_uid | PRIMARY | 4 | hg38.A.CID | 1 | 100.00 | |
| 1 | SIMPLE | B | eq_ref | PRIMARY,ix_uid | PRIMARY | 4 | hg38.A.BID | 1 | 100.00 | |
+------+-------------+-------+--------+----------------+---------+---------+---------------------+----------+----------+-------------+
MariaDB [test]> EXPLAIN EXTENDED select * from A use index (PRIMARY), C, D, E where A.CID=C.uid and A.DID = D.uid and A.EID = E.uid and A.uid>0 order by A.uid limit 10;
+------+-------------+-------+--------+----------------+---------+---------+-----------------+----------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+--------+----------------+---------+---------+-----------------+----------+----------+-------------+
| 1 | SIMPLE | A | range | PRIMARY | PRIMARY | 4 | NULL | 27965017 | 100.00 | Using where |
| 1 | SIMPLE | E | eq_ref | PRIMARY,ix_uid | PRIMARY | 4 | hg38.A.EID | 1 | 100.00 | |
| 1 | SIMPLE | C | eq_ref | PRIMARY,ix_uid | PRIMARY | 4 | hg38.A.CID | 1 | 100.00 | |
| 1 | SIMPLE | D | eq_ref | PRIMARY,ix_uid | PRIMARY | 4 | hg38.A.DID | 1 | 100.00 | |
+------+-------------+-------+--------+----------------+---------+---------+-----------------+----------+----------+-------------+
解决方案
推荐阅读
- python - 在 for 循环中删除 dict 条目不起作用，为什么？
- css - 消除背景图像旁边的空白
- r - 在自定义 JupyterHub 用户笔记本图像中包含额外的 R pkg？
- php - 重写获取 URL 的 PHP
- java - Spring：将String List的所有值注入bean
- javascript - 使用 React 表单生成唯一 ID
- unit-testing - 我无法运行单元测试（Python）
- excel - 查找并删除一行 Excel VBA 代码
- swift - SwiftUI - 在预览中模拟 @EnvironmentObject 数据
- excel - 如何有条件地选择数学公式来计算 Excel 中的值？