mysql select 和 join 非常慢

Question

我有两个表 A 和 B。A 大约有 2900 万行，B 是一个临时表，大约有 1000 行。

查询如下 -

select DISTINCT Table_A.column_a from Table_B join Table_A on Table_B.ID_b = Table_A.ID_a;

我在(ID_a，column_a)上有一个复合索引 key2

查询运行大约需要 20 秒。

解释如下 -

------------+
| id | select_type | table             | type | possible_keys                           | key                       | key_len | ref                                   | rows | Extra           |
+----+-------------+-------------------+------+-----------------------------------------+---------------------------+---------+---------------------------------------+------+-----------------+
|  1 | SIMPLE      | Table_B           | ALL  | NULL                                    | NULL                      | NULL    | NULL                                  | 1507 | Using temporary |
|  1 | SIMPLE      | Table_A           | ref  | key1 ,key2                              | key2                      | 3       | DB_name.Table_B.ID_b                  |  963 | Using index     |
+----+-------------+-------------------+------+-----------------------------------------+---------------------------+---------+---------------------------------------+------+-----------------+

如何优化该查询？

谢谢

desc Table_A 
| id       | bigint(8) unsigned    | NO | PRI | NULL | auto_increment |
| column_a | mediumint(8) unsigned | NO | MUL |      |                | 
| column_1 | mediumint(8) unsigned | NO | MUL |      |                |
| id_a     | mediumint(8) unsigned | NO | MUL |      | 
| column_2 | smallint(5) unsigned  | NO | MUL | NULL |                | 
| column_3 | smallint(5) unsigned  | NO |     | NULL |                | 
| column_4 | smallint(5) unsigned  | NO | MUL | NULL |                | 
| column_5 | smallint(5) unsigned  | NO |     | NULL |                |

Answer 1

在临时表 B 的 ID_b 列上创建索引，之后您应该不会再看到“在“Extra”列中使用临时文件了，应该会更好。使用CREATE INDEX声明。