我不知道如何让这个子查询变得非常慢以获取 10k 记录中的数据

表代码:

+--------+-----------+------------+
| code_id| code_name | code_date  |
+--------+-----------+------------+
|    1   |     A1    | 2017-02-01 |
|    2   |     A2    | 2017-02-02 |
|    3   |     A3    | 2017-02-03 |
|    4   |     A4    | 2017-02-04 |
|    5   |     A5    | 2017-02-05 |
|    6   |     A6    | 2017-02-06 |
|    7   |     A7    | 2017-02-07 |
|10000   | A10000    | 2017-02-22 |
+--------+-----------+------------+

表格规则:

+--------+------------+------------+
| reg_id | reg_number | reg_date   |
+--------+------------+------------+
|    1   |   1010     | 2017-02-01 |
|    2   |   1020     | 2017-02-02 |
|    3   |   1030     | 2017-02-03 |
|    4   |   1040     | 2017-02-04 |
|    5   |   1050     | 2017-02-05 |
|    6   |   1060     | 2017-02-06 |
|    7   |   1070     | 2017-02-07 |
|10000   | 101010     | 2017-02-22 |
+--------+-----------+------------+

然后我运行:

SELECT
a.`code_name`,
a.`code_date`,
(SELECT b.`reg_number` FROM `table_reg` b WHERE b.`reg_date` <= a.`code_date` ORDER BY b.`reg_date` DESC LIMIT 1) AS `reg_number`,
(SELECT b.`reg_date` FROM `table_reg` b WHERE b.`reg_date` <= a.`code_date` ORDER BY b.`reg_date` DESC LIMIT 1) AS `reg_date`
FROM `table_code` a

结果:

+-----------+------------+------------+------------+
| code_name | code_date  | reg_number | reg_date   |
+-----------+------------+------------+------------+
|     A1    | 2017-02-01 | 1010       | 2017-02-01 |
|     A2    | 2017-02-02 | 1020       | 2017-02-02 |
|     A3    | 2017-02-03 | 1030       | 2017-02-03 |
|     A4    | 2017-02-04 | 1040       | 2017-02-04 |
|     A5    | 2017-02-05 | 1050       | 2017-02-05 |
|     A6    | 2017-02-06 | 1050       | 2017-02-05 |
|     A7    | 2017-02-07 | 1050       | 2017-02-05 |
| A10000    | 2017-02-22 | 1050       | 2017-02-05 |
+-----------+------------+------------|------------+

数据链接:

CREATE TABLE `table_reg` (
  `reg_id` INTEGER(11) NOT NULL AUTO_INCREMENT,
  `reg_number` INTEGER(11) DEFAULT NULL,
  `reg_date` DATE DEFAULT NULL,
  PRIMARY KEY (`reg_id`) USING BTREE,
  KEY `table_reg_idx1` (`reg_date`) USING BTREE
) ENGINE=InnoDB
AUTO_INCREMENT=4 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'
COMMENT='InnoDB free: 7168 kB; InnoDB free: 6144 kB';

CREATE TABLE `table_code` (
  `code_id` INTEGER(11) NOT NULL AUTO_INCREMENT,
  `code_name` VARCHAR(20) COLLATE latin1_swedish_ci DEFAULT NULL,
  `code_date` DATE DEFAULT NULL,
  PRIMARY KEY (`code_id`) USING BTREE,
  KEY `table_code_idx1` (`code_date`) USING BTREE
) ENGINE=InnoDB
AUTO_INCREMENT=8 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'
COMMENT='InnoDB free: 7168 kB; InnoDB free: 6144 kB';

结果按预期工作，但对于 10k 记录非常慢，

如果 code_date 与 reg_date_date 不匹配，它将使用 reg_number 最新的日期。

(SELECT b.`reg_number` FROM `table_reg` b WHERE b.`reg_date` <= a.`code_date` ORDER BY b.`reg_date` DESC LIMIT 1) AS `reg_number`

还有其他选项查询吗？

此链接 sqlfiddle:[ http://sqlfiddle.com/#!9/f090ad/1]

如有任何帮助，我们将不胜感激。谢谢

EXPLAIN结果样表:

最佳答案

正如其他人所说，如果您添加适当的索引，您当前的方法可能会以可接受的速度运行。但是您当前查询的一种替代方法是使用行号方法。

SET @row_number = 1;

SELECT t.code_name,
       t.code_date,
       t.reg_number,
       t.reg_date
FROM
(
    SELECT @row_number:=CASE WHEN @code_id = t1.code_id
                             THEN @row_number + 1 ELSE 1 END AS rn,
           @code_id:=t1.code_id AS code_id,
           t1.code_name,
           t1.code_date,
           t2.reg_number,
           t2.reg_date
    FROM table_code t1
    INNER JOIN table_reg t2
        ON t2.reg_date <= t1.code_date
    ORDER BY t1.code_id, t2.reg_date DESC
) t
WHERE t.rn = 1

关于mysql - 简单子(monad)查询时间太长，有什么选择吗？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/42405374/