MYSQL:如何加快 sql 查询以获取数据 [英] MYSQL: how to speed up an sql Query for getting data

问题描述

我使用的是 Mysql 数据库.

I am using Mysql database.

我有一个存储有以下字段的股票价值表 daily_price_history.它有 1100 万+ 行

I have a table daily_price_history of stock values stored with the following fields. It has 11 million+ rows

id
symbolName
symbolId
volume
high
low
open
datetime
close

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

So for each stock SymbolName there are various daily stock values. And the data is now more than 11 million rows,

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

The following sql try to get the last 100 days of daily data for a set of 1500 symbols

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 秒.

For getting 130K (i.e 1500 x 100 ~ 150000) rows of data it takes 20 secs.

我还有 weekly_price_history 和 monthly_price_history 表，我尝试运行类似的 sql，它们在相同数量(130K)的行上花费的时间更少，因为它们表中的数据比每天少.

Also i have weekly_price_history and monthly_price_history tables, and I try to run the similar sql, they take less time for the same number (130K) of rows, because they have less data in the table than daily.

weekly_price_history 获得 150K 行需要 3s.其中总行数为250万

weekly_price_history getting 150K rows takes 3s. The total number of rows in it are 2.5million

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

monthly_price_history getting 150K rows takes 1s. The total number of rows in it are 800K

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

So how to speed up the thing when the size of table is large.

推荐答案

作为初学者:我根本没有看到子查询的意义.据推测，您的查询可以直接在 where 子句中进行过滤:

As a starter: I don't see the point for the subquery at all. Presumably, your query could filter directly in the where clause:

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

然后，您需要 (datetime, symbolname) 的索引:

Then, you want an index on (datetime, symbolname):

create index idx_daily_price_history 
    on daily_price_history(datetime, symbolname)
;

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

The first column of the index matches on the predicate on datetime. It is not very likley, however, that the database will be able to use the index to filter symbolname against a large list of values.

另一种方法是将值列表放入表格中，例如 symbolnames.

An alternative would be to put the list of values in a table, say 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 列添加到索引中:

That should allow the database to use the above index. We can take one step forward and try and add the 4 columns of the select clause to the index:

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

这篇关于MYSQL:如何加快 sql 查询以获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！