mysql - 我可以优化这种查询吗？

Question

我有这个问题:

SELECT 
    s.last_spread, s.sd, s.mean, s.id
    ,c.id_ticker, c.coef
    ,t.ticker
    ,p.last, p.price

FROM (SELECT * FROM spreads WHERE spreads.id_check=1 LIMIT 100,500 ) as s 

    INNER JOIN coef as c 
        ON c.id_spread = s.id

    INNER JOIN tickers AS t
            ON t.id = c.id_ticker

    LEFT JOIN (SELECT prices.id_ticker, MAX(prices.date) as last, prices.price FROM prices GROUP BY prices.id_ticker) AS p
            ON p.id_ticker = t.id

这些是表的架构:

mysql> desc spreads;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(11) | NO   | PRI | NULL    | auto_increment |
| id_check    | int(11) | YES  | MUL | NULL    |                |
| sd          | double  | YES  |     | NULL    |                |
| mean        | double  | YES  |     | NULL    |                |
| last_spread | double  | YES  |     | NULL    |                |
+-------------+---------+------+-----+---------+----------------+
5 rows in set (0.00 sec)


mysql> desc coef;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| id_spread | int(11) | YES  | MUL | NULL    |                |
| id_ticker | int(11) | YES  |     | NULL    |                |
| coef      | double  | YES  |     | NULL    |                |
| side      | double  | YES  |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+
5 rows in set (0.00 sec)


mysql> desc tickers;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| ticker   | varchar(45)      | NO   |     | NULL    |                |
| name     | varchar(150)     | NO   |     | NULL    |                |
| category | varchar(150)     | NO   |     | NULL    |                |
| issuer   | varchar(150)     | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)


mysql> desc prices;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| id_ticker | int(10) unsigned | NO   | MUL | NULL    |                |
| date      | date             | NO   |     | NULL    |                |
| price     | double           | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

这些是上面表格的索引；

mysql> show indexes from spreads;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| spreads |          0 | PRIMARY   |            1 | id          | A         |        2299 |     NULL | NULL   |      | BTREE      |         |
| spreads |          1 | check_idx |            1 | id_check    | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)


mysql> show indexes from coef;
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| coef  |          0 | PRIMARY           |            1 | id          | A         |        9078 |     NULL | NULL   |      | BTREE      |         |
| coef  |          1 | spread_ticker_idx |            1 | id_spread   | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| coef  |          1 | spread_ticker_idx |            2 | id_ticker   | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)


mysql> show indexes from tickers;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tickers |          0 | PRIMARY  |            1 | id          | A         |         100 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)


mysql> show indexes 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         |       19962 |     NULL | NULL   |      | BTREE      |         |
| prices |          1 | id_ticker |            1 | id_ticker   | A         |       19962 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.15 sec)

这是查询的解释:

+----+-------------+------------+--------+-------------------+-------------------+---------+---------------------------+--------+-------------+
| id | select_type | table      | type   | possible_keys     | key               | key_len | ref                       | rows   | Extra       |
+----+-------------+------------+--------+-------------------+-------------------+---------+---------------------------+--------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL              | NULL              | NULL    | NULL                      |    500 |             |
|  1 | PRIMARY     | c          | ref    | spread_ticker_idx | spread_ticker_idx | 5       | s.id                      |     90 | Using where |
|  1 | PRIMARY     | t          | eq_ref | PRIMARY           | PRIMARY           | 4       | spreadtrading.c.id_ticker |      1 | Using where |
|  1 | PRIMARY     | <derived3> | ALL    | NULL              | NULL              | NULL    | NULL                      |    100 |             |
|  3 | DERIVED     | prices     | index  | NULL              | id_ticker         | 4       | NULL                      | 119774 |             |
|  2 | DERIVED     | spreads    | ref    | check_idx         | check_idx         | 5       |                           |   2298 | Using where |
+----+-------------+------------+--------+-------------------+-------------------+---------+---------------------------+--------+-------------+
6 rows in set (0.27 sec)

我可以优化它吗？

谢谢!

编辑:

我想知道索引和表的结构是否针对我在上面发布的查询进行了优化。我使用此查询获得的结果很好，运行良好，但也许我可以对其进行优化以提高查询的“速度”。

Answer 1

我认为您可以通过删除 spreads 子查询并将 WHERE 子句移动到末尾来有所收获，如以下代码所示。这将失去您的 LIMIT 限制 - 也许您也可以在末尾放置一个 LIMIT 子句，具体取决于您在限制大小方面试图实现的目标输出。

SELECT 
    s.last_spread, s.sd, s.mean, s.id
    ,c.id_ticker, c.coef
    ,t.ticker
    ,p.last, p.price

FROM  spreads as s 

    INNER JOIN coef as c 
        ON c.id_spread = s.id

    INNER JOIN tickers AS t
            ON t.id = c.id_ticker

    LEFT JOIN (SELECT prices.id_ticker, MAX(prices.date) as last, prices.price FROM prices GROUP BY prices.id_ticker) AS p
            ON p.id_ticker = t.id
WHERE s.id_check = 1