我有一个MySQL查询需要大量的时间大约10.4毫米的记录。它需要很多时间

Question

SELECT ticker.ticker_id, 
     ticker.ticker_code, 
     inter_day_ticker_candle_price_history.close AS previousDayClose 
FROM inter_day_ticker_candle_price_history 
INNER JOIN 
    (SELECT MAX(inter_day_ticker_candle_price_history.candle_price_history_id) AS candle_price_history_id 
    FROM inter_day_ticker_candle_price_history 
    WHERE inter_day_ticker_candle_price_history.close>0 
    GROUP BY inter_day_ticker_candle_price_history.ticker_id) derivedTable 
    ON inter_day_ticker_candle_price_history.candle_price_history_id = derivedTable.candle_price_history_id 
RIGHT JOIN ticker ON ticker.ticker_id = inter_day_ticker_candle_price_history.ticker_id 
WHERE ticker.is_active = 1 

请提出我任何其他技术，我可以在这里申请，以减少时间。

这是表结构

Field     Type   Null Key  Default Extra   
----------------------- ------------- ------ ------ ------- ---------------- 
candle_price_history_id int(8)   NO  PRI  (NULL) auto_increment 
ticker_id    bigint(11)  NO  MUL  (NULL)     
candle_interval   int(11)  YES    1       
trade_date    datetime  YES    (NULL)     
trade_price    decimal(16,2) YES    (NULL)     
trade_size    decimal(30,2) YES    (NULL)     
open      decimal(16,2) YES    (NULL)     
high      decimal(16,2) YES    (NULL)     
low      decimal(16,2) YES    (NULL)     
close     decimal(16,2) YES    (NULL)     
volume     bigint(30)  YES    (NULL)     
creation_date   datetime  YES    (NULL)     
is_ebabled    bit(1)   YES    b'1' 

Answer 1

它看起来更自然首先从股票表中选择，然后找到最新的历史记录条目，然后加入认为：

SELECT 
    t.ticker_id, 
    t.ticker_code, 
    h.close AS previousDayClose 
FROM ticker t 
LEFT JOIN 
(
    SELECT ticker_id, MAX(candle_price_history_id) AS candle_price_history_id 
    FROM inter_day_ticker_candle_price_history 
    WHERE close > 0 
    GROUP BY ticker_id 
) m on m.ticker_id = t.ticker_id 
LEFT JOIN inter_day_ticker_candle_price_history h 
    ON h.candle_price_history_id = m.candle_price_history_id 
WHERE t.is_active = 1; 

但是，您的查询也应该工作。

确保有适当的索引。我建议：

​​

和

create index idx_history on inter_day_ticker_candle_price_history(ticker_id, 
                    close, 
                    candle_price_history_id); 

或

create index idx_history on inter_day_ticker_candle_price_history(close, 
                    ticker_id, 
                    candle_price_history_id); 

（列的顺序可以有所作为，所以你可能想尝试这两个版本的履历指数。那么，你当然可以用不同的名称同时创建两个索引，并查看使用哪一个索引。）

Answer 2

通常创建apropiate索引会加速你的querys多个过滤条件。

例如，在ticker_id上创建索引可能是查询速度更快的关键。

在另一方面，在密切和IS_ACTIVE创建索引可以帮助，但只有当IS_ACTIVE = 1其高达喜欢10％以下，在表中的记录。

而且，由于该表已经被candle_price_history_id

Answer 3

这似乎是一个 “的GroupWise最大” 的问题命令你可以改变MAX功能的ORDER BY candle_price_history_id DESC LIMIT 1。有关该模式的优化技术，请参阅http://mysql.rjweb.org/doc.php/groupwise_max。

查看我对缩小桌子大小的评论。我认为这可能是一张巨大的桌子，可能比RAM更大？

如果这是InnoDB，innodb_buffer_pool_size需要大约70％的可用RAM。

如果(ticker_id, tradedate)是唯一的，则将其设置为PRIMARY KEY并完全去掉id。顺序非常重要 - 它将给定代码的所有行集中在一起，从而减少I/O。 （如果您目前I/O限制，这可能给你10倍的加速。）

提供EXPLAIN SELECT ...。您需要查询（写入）开始与派生查询。 LEFT JOIN不会允许。

考虑让摆脱不活跃一排一排的紧密< = 0