MySQL慢速查询-使用Filesort

Question

我正试图在我的MySQL数据库上运行一个查询，该查询需要运行70+秒，并且我正在绞尽脑汁地思考为什么没有使用索引。

以下是查询：

SELECT PriceId, InstrumentId, Date, Open, High, Low, Close, Volume, UnadjustedClose
FROM price
ORDER BY InstrumentId, Date DESC

价目表有一个包含InstrumentId、Date (以及其他索引)的索引。该表本身有8000万行，由2个ints、一个日期、一个长小数和5个小数组成。

explain命令有ALL、Null表示可能的键、key和ref，并告诉我系统使用的是文件。

这是我能从系统中得到的最好结果吗？我预计索引将被用来使排序更快。

添加：

以下是表的定义：

PriceId int PK, NN, AI
InstrumentId int NN
Date Date NN
Open Decimal(12,4)
High Decimal(12,4)
Low Decimal(12,4)
Close Decimal(12,4)
UnadjustedClose Decimal(12,4)
Volume BigInt

Indexes:

Primary -> PriceId
IX_InstrumentId -> InstrumentId
IX_Date -> Date
IX_InstrumentDate -> InstrumentId, Date

解释输出如下：

id: 1
select_type: Simple
table: price
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 77926335
Extra: using filesort

Answer 1

优化器将不使用索引，因为您正在检索所有行，而且索引并不包含您试图获取的所有列。这意味着，指数不是覆盖指数。

在大多数情况下，使用索引和基于索引的记录查找来检索额外的列比扫描整个表(当您检索所有内容时)都没有那么有效。

你有一些选择：

• 在索引中包含所有必要的列:这需要更多的空间并减缓写入操作。
• 根据索引中的第一列向查询添加筛选器。如果筛选器具有足够的选择性(将所需的行数缩小到合理的水平)，服务器将使用您的索引。
• 将数据过滤到合理的大小。
• 在应用程序中进行排序
• 将主键(群集)修改为(InstrumentID ASC, Date DESC)

编辑关于最后一个选项的更多信息

你的桌子看起来像个日志表。在日志表中，在每个记录中添加一个唯一的整数ID似乎是一个很好的做法，以消除重复(但在大多数情况下并非如此)。然而，在大多数情况下，您不使用该ID。在MySQL中，主键也是群集键(这意味着数据将按磁盘上的顺序排序--或多或少，现在请原谅碎片)。

在日志表中，最好使用日志实体的ID和时间戳(在您的例子中是InstrumentID，Date )作为聚集索引(MySQL中的主键)。当您这样做时，数据的顺序将适合于常见的业务需求，这意味着查询的性能会更好。

如果InstrumentID和Date是唯一的(我认为它应该是，一个工具不可能同时有多个价格，并且在不到一秒钟内改变价格是非常罕见的)，那么一个综合指数可能会更好。(并添加一个比自动生成的整数值更好的分区选项)。

附带注意:如果您按日期进行筛选或排序的频率比按仪器ID进行的频率更高，则可以更改PK中列的顺序。

编辑的端

为了找到更好的方法来实现你的目标，你应该回答一些问题：

• 你为什么要从桌子上检索所有的80M记录？
• 你的应用程序真的全部使用了吗？
• 如果是，是否可以在应用程序级别而不是数据库级别进行排序？
• 记录的顺序真的算数吗？

Answer 2

你不能加快速度，因为有很多行。从这个查询创建一个Materialized View，一旦它被创建，访问就会更快。

MySQL不支持Materialized View，因此您可以使用教程这里自己实现它。