时间戳

首页 > 解决方案 > MYSQL：如何加快获取数据的 sql 查询

mysql - MYSQL：如何加快获取数据的 sql 查询

问题描述

我正在使用 Mysql 数据库。

我有一个daily_price_history存储有以下字段的库存值表。它有11 million+行

id
symbolName
symbolId
volume
high
low
open
datetime
close

因此，对于每只股票SymbolName，都有不同的每日股票价值。而数据现在已经超过 1100 万行，

下面的 sql 尝试获取一组 1500 个品种的最近 100 天的每日数据

SELECT `daily_price_history`.`id`,
       `daily_price_history`.`symbolId_id`,
       `daily_price_history`.`volume`,
       `daily_price_history`.`close`
FROM `daily_price_history`
WHERE (`daily_price_history`.`id` IN
         (SELECT U0.`id`
          FROM `daily_price_history` U0
          WHERE (U0.`symbolName` = `daily_price_history`.`symbolName`
                 AND U0.`datetime` >= 1598471533546))
       AND `daily_price_history`.`symbolName` IN (A,AA, ...... 1500 symbols Names)

我有索引表symbolName，还有datetime

获取 130K（即 1500 x 100 ~ 150000）行数据需要 20 秒。

我也有weekly_price_history和monthly_price_history表，我尝试运行类似的 sql，对于相同数量（130K）的行，它们花费的时间更少，因为它们在表中的数据比每天少。

weekly_price_history获取150K行需要3s. 其中的总行数是2.5million

monthly_price_history获取150K行需要1s. 其中的总行数是800K

那么当表的大小很大时如何加快速度。

标签： mysqlsqlquery-optimizationwhere-clausesql-in

解决方案

作为初学者：我根本看不到子查询的意义。据推测，您的查询可以直接在where子句中过滤：

select id, symbolid_id, volume, close
from daily_price_history
where datetime >= 1598471533546 and symbolname in ('A', 'AA', ...)

然后，你想要一个索引(datetime, symbolname)：

create index idx_daily_price_history 
    on daily_price_history(datetime, symbolname)
;

索引的第一列与 上的谓词匹配datetime。然而，数据库不太可能使用索引来过滤symbolname大量值。

另一种方法是将值列表放在一个表中，例如symbolnames。

create table symbolnames (
    symbolname varchar(50) primary key
);
insert into symbolnames values ('A'), ('AA'), ...;

然后你可以这样做：

select p.id, p.symbolid_id, p.volume, p.close
from daily_price_history p
inner join symbolnames s on s.symbolname = p.symbolname
where s.datetime >= 1598471533546

那应该允许数据库使用上述索引。我们可以向前迈出一步，尝试将select子句的 4 列添加到索引中：

create index idx_daily_price_history_2 
    on daily_price_history(datetime, symbolname, id, symbolid_id, volume, close)
;

推荐阅读