mysql - 什么会导致查询 A 而不是查询 B 中的性能问题？

问题描述

我有一个带有 apx 的表（MYSQL 8）。100M 条记录存储各种位的股票数据（价格、日期等），下面的查询 A 运行时间 < 1 秒，但查询 B 需要 2 分钟。在其他索引中，我有一个关于的索引date，并且该表的主键是 ( symbol, date)。什么会导致两个查询之间存在如此显着的差异，什么可能会加速表现不佳的人？

查询一：

SELECT symbol, MIN(date)
FROM Stocks
WHERE date BETWEEN '2015-01-01' AND '2020-01-01'
GROUP BY symbol

查询 B

SELECT symbol, MIN(date)
FROM Stocks
WHERE date BETWEEN '2015-01-01' AND '2020-01-01' AND market_cap > 20
GROUP BY symbol

我面临的另一个挑战是，有时我想按过滤market_cap，但有时按其他数字字段（gross_profit、total_assets等）过滤。该查询是由一个表单生成的，该表单具有许多与参数相关的可选输入。

表架构

CREATE TABLE IF NOT EXISTS cri_v0_995 (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id MEDIUMINT UNSIGNED NOT NULL, 
  dt DATE NOT NULL,
  price DECIMAL(18, 2),
  market_cap DECIMAL(12, 4),
  div_yield DECIMAL(4,2), -- this is always 0
  price_to_nte DOUBLE,
  price_to_mte DOUBLE,
  nte_to_price DECIMAL(16, 10),
  ante_to_price DECIMAL(16, 10),
  ate_to_price DECIMAL(18, 10),
  price_to_sales DOUBLE,
  price_to_earnings DOUBLE,
  cur_liq_to_mcap DECIMAL(4, 2), -- this is always 0
  net_liq_to_mcap DOUBLE,
  g3_rev_growth_and_trend DECIMAL(14, 10),
  p_cri_score DECIMAL(14, 10),
  f_cri_score DECIMAL(10, 7),
  cri_score DECIMAL(14, 10),
  PRIMARY KEY (id),
  FOREIGN KEY (company_id) REFERENCES companies (id),
  UNIQUE KEY (company_id, dt)
);

请注意，我不确定有几个列。他们一直是零，但我不知道他们背后的意图是什么。

（编辑 1 以解决缺少GROUP BY的 s）（编辑 2 添加表模式）

标签： mysqlsql

解决方案

第一个查询可以简单地遍历表：

对于每个符号（方便地是 PK 的开始）
date找到也是 >= 开始日期的第一行（“第一”，因为索引的第二部分是）
如果该日期不是 <= 结束日期，则抛出结果

第二个查询需要查看每一行来检查market_cap；它不能跳过桌子。

相反，如果您current_market_cap在Symbols表格中有您可以过滤到此表格market_cap 之前。 JOINing

子句中的两个范围WHERE使优化变得非常困难。索引是一维的。

使用PARTITION BY TODAYS(date)需要对表进行重大结构更改。它可能（也可能不会！）帮助您的查询运行得更快——通过使用“分区修剪”来限制需要检查的行数。（我说“可能不会”，因为查询正在查看 5 年的范围，这可能是整个数据的很大一部分。）

更多关于分区的讨论：http: //mysql.rjweb.org/doc.php/partitionmaint和http://mysql.rjweb.org/doc.php/find_nearest_in_mysql -- 后一个链接讨论了一个不同的 2D 问题（地理 'find最近的'); 将其应用于您的查询有点牵强。

由于您有很多最终用户可能过滤的列和 100M 行，让我们从另一个方向着手：最小化表大小。如果表不能完全缓存在 buffer_pool 中，这一点尤其重要——导致 I/O 绑定。给我们看SHOW CREATE TABLE；让我们讨论每一列，以及它是否可以缩小。

更多的

更改symbol VARCHAR...为company_id MEDIUMINT UNSIGNED可能在数据和索引之间节省了 1GB。
摆脱id升级UNIQUE(company_id, dt)为PK。通过消除唯一的二级索引，这将节省几 GB。（你的改变可能是有益的。
大多数DOUBLEs都是矫枉过正？ FLOAT每个将节省 4 个字节，但仍会为您提供 6-7 个有效数字。
您可能需要INDEX(dt)其他一些查询。
上的过滤器market_cap可能会妨碍分组最大优化。
根据磁盘空间和其他查询，它可能对有益PARTITION BY RANGE(TO_DAYS(dt))，但按年分组。（5 年 + 1 天）跨度将达到 6 个分区。（参见“分区修剪”）这实际上不会对性能产生太大影响。
（大约 18 年前，我使用过这样的数据集。）
price DECIMAL(18, 2)占用 9 个字节。它允许数十亿美元，尚未达到。它只有 2 位小数，因此在转换为小数（从 /2、/4、/8、/16 等）之前，它不会精确地保存金额。
market_cap DECIMAL(12, 4)（6 字节）对于某些公司可能不够大，对于索引当然也不够。小数点后 4 位可能是一种浪费。
建议跑去SELECT MAX(market_cap), MAX(price), ...看看现在的数字有多大。

mysql - 什么会导致查询 A 而不是查询 B 中的性能问题？

问题描述

表架构

解决方案

推荐阅读