mysql 子查询 查询时长_mysql – 简单子查询需要太长时间,有什么选择吗？

我无法弄清楚如何让这个子查询在10k记录中获取数据非常慢

table_code：

+--------+-----------+------------+

| 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 |

+--------+-----------+------------+

table_reg：

+--------+------------+------------+

| 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 |

+-----------+------------+------------|------------+

DDL：

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`

还有其他选项查询吗？

任何帮助,将不胜感激.谢谢

EXPLAIN结果示例表：