(mysql查询性能问题)大型历史股价数据库的索引

Question

对于你们中的一些人来说，这可能是一个微不足道的问题，但我还没有找到/理解以下问题的解决方案：

我有一个大型的c 60 GB数据库，其结构如下：

| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| date       | datetime | YES  | MUL | NULL    |       |
| chgpct1d   | double   | YES  |     | NULL    |       |
| pair       | text     | YES  |     | NULL    |       |

该数据库存储了过去10年c 200k不同配对交易的每日百分比变化。因此，日期和对都不是唯一的键(日期+对的组合就是)。有c 2600个不同的日期条目和c 200k个不同的对，它们生成> 520 MM行。

下面的查询需要c几分钟才能返回一个结果。

SELECT date, chgpct1d, pair FROM db WHERE date = '2018-12-20';

我能做些什么来加快速度呢？

我读过关于多列索引的文章，但我不确定这对我的情况是否有帮助，因为所有的WHERE查询都只指向'date‘字段。

Answer 1

MySQL可能会执行全表扫描来满足您的查询。这就像在字典中查找一个单词，它的词条是随机排列的:非常慢。

两件事：

1. 在这些列上创建索引：(date, chgpct1d, pair).
2. Because名为date的列具有DATETIME数据类型，它可能包含像2018-12-20 10:17:20这样的值。当你说WHERE date = '2018-12-20'时，它实际上是指WHERE date = '2018-12-20 00:00:00'。因此，请使用下面的代码

其中日期为'2018-12-20‘，日期< '2018-12-21`

它将在您选择的日期的任何时间捕获所有date值。

为什么这会有帮助？因为多列索引以date开头，所以MySQL可以在给定WHERE语句的情况下对其进行范围扫描。而且，因为索引包含查询所需的所有内容，所以数据库服务器不必查找其他任何地方，而是可以直接从索引中满足查询。该索引被认为覆盖了查询。

请注意，表中有半个千兆行，创建索引将需要一段时间。一夜之间或者别的什么。