mysql - 我该如何优化这个查询？

Question

我有两个 mysql 表，其结构如下:

mysql> describe symbol;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| symbol      | varchar(250) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> describe price;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| id_symbol | int(11) | NO   | MUL | NULL    |                |
| date      | date    | NO   | MUL | NULL    |                |
| price     | double  | NO   |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

我必须查询价格表来获取包含两个交易品种的 N 个价格的列表(以比较它们)

这是示例结果:

DATE        SYMBOL 1 SYMBOL 2
2011-01-01    100      23
2011-01-02    92       26
2011-01-03    89       50

所以我需要找到两者相同的数据并返回它们的价格。我创建了一个查询，看起来它可以完成工作，但可能不是很优化。

SELECT *
FROM price AS a
  JOIN price AS b ON a.date = b.date
WHERE a.id_symbol = 1 AND b.id_symbol = 2
ORDER BY a.date DESC
LIMIT 100

你觉得怎么样？

我可以优化查询执行某些操作(或者更改表结构上的某些操作)吗？

编辑:

这是 EXPLAIN {query} 的结果

mysql> EXPLAIN SELECT *
    -> FROM price AS a
    ->   JOIN price AS b ON a.date = b.date
    -> WHERE a.id_symbol = 1 AND b.id_symbol = 2
    -> ORDER BY a.date DESC
    -> LIMIT 100;
+----+-------------+-------+------+----------------+-----------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys  | key       | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+----------------+-----------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | a     | ref  | date,id_symbol | id_symbol | 4       | const |  921 | Using where; Using filesort |
|  1 | SIMPLE      | b     | ref  | date,id_symbol | id_symbol | 4       | const |  966 | Using where                 |
+----+-------------+-------+------+----------------+-----------+---------+-------+------+-----------------------------+
2 rows in set (0.03 sec)

你觉得怎么样？

Then... what is 921 and 966 on the EXPLAIN "ROW" column ? If i count the prices i have for symbol 1 and symbol 2 i get: 912 and 912 :-| what is that?

(P.S.是的，我的错误，我必须用我想要的列更改*)

Answer 1

基于有限的信息，您所能做的就是在 id_symbol ASC、date DESC 上创建索引，并可能将 * 限制为仅您需要的列。

最大的“加速”可能是将两个查询合并为一个查询，然后只运行该查询并解析结果集，而不是运行一个查询来获取所有符号对，然后为每个符号对运行一个不同的查询符号对。您不提供原始查询，因此无法建议此组合查询。