mysql - mysql select with join很慢

我有两个表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 |                |

最佳答案

在临时表B的列ID b上创建索引，此后您不应再在“其他”列中获得“使用临时”，它应该更好。使用CREATE INDEX语句。