一个简单的MySQL查询需要很久才能完成（超过20分钟！）

Question

也许您可以帮我一下。我需要查询3个表以获取金融股票的数据。

思路是先到工具表中找到每种工具的索引，然后将该特定工具的所有价格与单独表上的指标汇总在一起。

表格“stockdata”和“indicators”都有近50,000条记录。“instruments”仅有30条记录。

以下是不起作用的查询：

SELECT
  indicators.ddate,
  instruments.name,
  indicators.sma_14,
  indicators.sma_5,
  stockdata.close
FROM
 indicators
 INNER JOIN instruments ON (indicators.instrument_id=instruments.id)
 INNER JOIN stockdata ON (instruments.name=stockdata.name)

以下是EXPLAIN的结果

+----+-------------+-------------+-------+-----------------------------+---------------------+---------+------+-------------+
| id | select_type | table       | type  | possible_keys               | key                 | key_len | rows | Extra       |
+----+-------------+-------------+-------+-----------------------------+---------------------+---------+------+-------------+
| 1  | SIMPLE      | instruments | index | PRIMARY,instruments_index01 | instruments_index01 |      61 |   25 | Using index |
| 1  | SIMPLE      | indicators  | ref   | indicators_index01          | indicators_index01  |       5 |  973 | Using where |
| 1  | SIMPLE      | stockdata   | ref   | stockdata_index01           | stockdata_index01   |      31 | 1499 | Using where |
+----+-------------+-------------+-------+-----------------------------+---------------------+---------+------+-------------+

非常感谢您能提供任何帮助！

这是我问题涉及的表部分的模式：

TABLE `indicators` (
  `id`             int AUTO_INCREMENT NOT NULL,<br>
  `instrument_id`  int,
  `date`           date,
  `sma_5`          float(10,3),
  `sma_14`         float(10,3),
  `ema_14`         float(10,3),
  /* Keys */
  PRIMARY KEY (`id`)
)

TABLE `instruments` (
  `id`         int AUTO_INCREMENT NOT NULL,
  `name`       char(20),
  `country`    char(50),
  `newsquery`  char(100),
  /* Keys */
  PRIMARY KEY (`id`)
)

TABLE `stockdata` (
  `id`        int AUTO_INCREMENT NOT NULL,
  `name`      char(10),
  `date`      date,
  `open`      float,
  `high`      float,
  `low`       float,
  `close`     float,
  `volume`    int,
  `adjclose`  float,
  /* Keys */
  PRIMARY KEY (`id`)
)

- JordanBelf

---

为什么连接条件周围有括号？ - Fosco

每个涉及的表中有多少行？ - sgriffinusa

你好，数据表 stockdata 和 indicators 都有将近 50,000 条记录。而 instruments 只有 30 条记录。 - JordanBelf

你能发布一张你的数据表模式图吗？ - dockeryZ

如果仪器表格存储的是股票数据表格中的ID而不是名称，那么它将得到改进。 - Fosco

Answer 1

您正在将 indicators 表与 instruments 表进行关联，并且 indicators.instrument_id 列未被索引。

您还将 instruments 表与 stockdata 表使用 instruments.name 和 stockdata.name 列进行关联，两者的类型都是 CHAR。使用 CHAR 或 VARCHAR 进行连接通常比使用 INT 列要慢得多： Using CHAR keys for joins, how much is the overhead? 更糟糕的是，您的 CHAR 列具有不同的大小（分别为 char(20) 和 char(10)），并且它们没有被索引。这使得 MySQL 非常困难！有关更多信息，请参见 How MySQL Uses Indexes。

理想情况下，您应该更改表结构，以便可以使用索引的 INT 字段执行连接。像这样：

CREATE TABLE `instruments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) DEFAULT NULL,
  `country` char(50) DEFAULT NULL,
  `newsquery` char(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `indicators` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `instrument_id` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `sma_5` float(10,3) DEFAULT NULL,
  `sma_14` float(10,3) DEFAULT NULL,
  `ema_14` float(10,3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_instrument_indicators` (`instrument_id`),
  CONSTRAINT `fk_instrument_indicators` FOREIGN KEY (`instrument_id`) REFERENCES `instruments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

 CREATE TABLE `stockdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `instrument_id` int(11) NOT NULL,
  `name` char(20) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `open` float DEFAULT NULL,
  `high` float DEFAULT NULL,
  `low` float DEFAULT NULL,
  `close` float DEFAULT NULL,
  `volume` int(11) DEFAULT NULL,
  `adjclose` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_instrument_stockdata` (`instrument_id`),
  CONSTRAINT `fk_instrument_stockdata` FOREIGN KEY (`instrument_id`) REFERENCES `instruments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

然后在您的连接中使用索引字段：

SELECT
  indicators.date,
  instruments.name,
  indicators.sma_14,
  indicators.sma_5,
  stockdata.close
FROM
 indicators
 INNER JOIN instruments ON (indicators.instrument_id=instruments.id)
 INNER JOIN stockdata ON (instruments.id=stockdata.instrument_id)

通过使用索引的INT列，您的连接速度将更快。使用InnoDB约束可以帮助确保数据完整性。

如果您必须在name列上进行联接，请使它们大小相同并对其进行索引。

- Mike

Answer 2

SELECT 
  ind.ddate,
  ins.name,
  ind.sma_14,
  ind.sma_5,
  sto.close
FROM indicators ind
JOIN instruments ins ON ind.instrument_id = ins.instrument_id
JOIN stockdata sto ON ins.name = sto.name

另一个选项：

select ind.ddate, ins.name, ind.sma_14, ind.sma_5, 
     (select close from stockdata where name = ins.name limit 1) as close
from indicators ind
join instruments ins on ind.instrument_id = ins.instrument_id

- Fosco

---

你好，第二个选项的工作方式如下：选择指标.日期，instruments.name，indicators.sma_14，indicators.sma_5， (select close from stockdata where name = instruments.name limit 1) as close from indicators indicators join instruments instruments on indicators.instrument_id = instruments.id但是仍然需要：获取44619行数据（用时29.42秒）。 - JordanBelf

Answer 3

我对使用 stockdata.name 字段进行连接持怀疑态度。您是否在 stockdata 和 instruments 表的 name 字段上定义了正确的索引？连接名称可能会返回无效结果，您是否可以使用其他 .id 字段进行连接？

- Wil P

---

问题在于stockdata表中没有instrument_id列，所以我需要引入instrument表从中获取给定名称的instrument id，然后与indicators表连接。 - JordanBelf

Answer 4

您正在查询未索引的stockdata字段名称。请创建一个索引或者改为使用id进行连接（我会选择后者，在instruments中将name更改为id）。

- PaulJWilliams