database - MySQL 我想进一步优化这个

Question

所以我从这个查询开始:

SELECT * FROM TABLE1 WHERE hash IN (SELECT id FROM temptable);

它花了很长时间，所以我运行了一个解释:

mysql> explain SELECT * FROM TABLE1 WHERE hash IN (SELECT id FROM temptable);
+----+--------------------+-----------------+------+---------------+------+---------+------+------------+-------------+
| id | select_type        | table           | type | possible_keys | key  | key_len | ref  | rows       | Extra       |
+----+--------------------+-----------------+------+---------------+------+---------+------+------------+-------------+
|  1 | PRIMARY            | TABLE1          | ALL  | NULL          | NULL | NULL    | NULL | 2554388553 | Using where | 
|  2 | DEPENDENT SUBQUERY | temptable       | ALL  | NULL          | NULL | NULL    | NULL |       1506 | Using where | 
+----+--------------------+-----------------+------+---------------+------+---------+------+------------+-------------+
2 rows in set (0.01 sec)

它没有使用索引。所以，我的第二遍:

mysql> explain SELECT * FROM TABLE1 JOIN temptable ON TABLE1.hash=temptable.hash;
+----+-------------+-----------------+------+---------------+----------+---------+------------------------+------+-------------+
| id | select_type | table           | type | possible_keys | key      | key_len | ref                    | rows | Extra       |
+----+-------------+-----------------+------+---------------+----------+---------+------------------------+------+-------------+
|  1 | SIMPLE      | temptable       | ALL  | hash          | NULL     | NULL    | NULL                   | 1506 |             | 
|  1 | SIMPLE      | TABLE1          | ref  | hash          | hash     | 5       | testdb.temptable.hash  |  527 | Using where | 
+----+-------------+-----------------+------+---------------+----------+---------+------------------------+------+-------------+
2 rows in set (0.00 sec)

我可以做任何其他优化吗？

Answer 1

您可以通过使用 covering index 获得更多速度，以额外的空间消耗为代价。覆盖索引是一种可以满足查询中所有请求的列而无需对聚簇索引执行进一步查找的索引。

首先摆脱 SELECT * 并明确选择您需要的字段。然后，您可以将 SELECT 子句中的所有字段添加到复合索引的右侧。例如，如果您的查询如下所示:

SELECT  first_name, last_name, age 
FROM    table1 
JOIN    temptable ON table1.hash = temptable.hash;

然后你可以有一个看起来像这样的覆盖索引:

CREATE INDEX ix_index ON table1 (hash, first_name, last_name, age);