Mysql subselect 非常慢(似乎没有使用索引)

Question

此查询未在合理时间内完成:

mysql> select * from prices where symbol='GOOG' and date in
(select max(date) from prices where symbol='GOOG' and yearweek(date) > 201001
 group by yearweek(date));

'prices' 以 id 为键，并具有(符号，日期)的辅助唯一索引:

mysql> show index from prices;
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| prices |          0 | PRIMARY         |            1 | id          | A         |      468915 |     NULL | NULL   |      | BTREE      |         |
| prices |          0 | SECONDARY_INDEX |            1 | date        | A         |       10905 |     NULL | NULL   | YES  | BTREE      |         |
| prices |          0 | SECONDARY_INDEX |            2 | symbol      | A          |      468915 |     NULL | NULL   | YES  | BTREE      |         |
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

从 EXPLAIN 输出来看，似乎 MySql 没有在 (date, symbol) 上使用索引。 WHERE symbol='GOOG' 和 date IN (...)) 的组合是否需要全表扫描而不使用二级唯一索引？

mysql> EXPLAIN select * from prices where symbol='GOOG' and date in (select max(date) from prices where symbol='GOOG' and yearweek(date) > 201001 group by yearweek(date));
+----+--------------------+--------+-------+---------------+-----------------+---------+------+--------+-----------------------------------------------------------+
| id | select_type        | table  | type  | possible_keys | key             | key_len | ref  | rows   | Extra                                                     |
+----+--------------------+--------+-------+---------------+-----------------+---------+------+--------+-----------------------------------------------------------+
|  1 | PRIMARY            | prices | ALL   | NULL          | NULL            | NULL    | NULL | 468915 | Using where                                               |
|  2 | DEPENDENT SUBQUERY | prices | index | NULL          | SECONDARY_INDEX | 17      | NULL | 468915 | Using where; Using index; Using temporary; Using filesort |
+----+--------------------+--------+-------+---------------+-----------------+---------+------+--------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

Answer 1

尝试加入:

SELECT 
    prices.* 
FROM 
    prices 
        JOIN
            (SELECT MAX(date) AS maxdate FROM prices WHERE symbol='GOOG' AND yearweek(date) > 201001 GROUP BY yearweek(date)) 
        AS sub ON prices.date = sub.maxdate
WHERE 
    symbol='GOOG' ;

但是 yearweek() 仍然是一个问题，MySQL 不能在这个上使用索引。预先计算此值(使用触发器)，将其存储在额外的列中并为该列编制索引，可能会有所帮助。