mysql - 优化主从插入语句

Question

任务

经过一天的运行(针对近 1 GB 的数据)，一组语句下降到每秒 40 次插入。我希望将其提高一两个数量级。

SQL 代码

插入信息的代码分为两部分:主记录和明细记录。主记录:

INSERT INTO MONTH_REF (DISTRICT_ID, STATION_ID, CATEGORY_ID, YEAR, MONTH) VALUES
('101', '0066', '010', 1984, 07);

详细记录:

INSERT INTO DAILY (MONTH_REF_ID, AMOUNT, DAILY_FLAG_ID, DAY) VALUES ((SELECT ID
FROM MONTH_REF M WHERE M.DISTRICT_ID = '101' AND M.STATION_ID = '0066' AND M.CAT
EGORY_ID = '010' AND M.YEAR = 1984 AND M.MONTH = 07), 0, ' ', 1);

INSERT INTO DAILY (MONTH_REF_ID, AMOUNT, DAILY_FLAG_ID, DAY) VALUES ((SELECT ID
FROM MONTH_REF M WHERE M.DISTRICT_ID = '101' AND M.STATION_ID = '0066' AND M.CAT
EGORY_ID = '010' AND M.YEAR = 1984 AND M.MONTH = 07), 0.5, ' ', 2);

INSERT INTO DAILY (MONTH_REF_ID, AMOUNT, DAILY_FLAG_ID, DAY) VALUES ((SELECT ID
FROM MONTH_REF M WHERE M.DISTRICT_ID = '101' AND M.STATION_ID = '0066' AND M.CAT
EGORY_ID = '010' AND M.YEAR = 1984 AND M.MONTH = 07), 0, 'T', 3);

建议的解决方案

建议的解决方案通过将每个 MONTH_REF_ID 存储在局部变量中来消除查找每个 MONTH_REF_ID，如下所示:

INSERT INTO MONTH_REF (DISTRICT_ID, STATION_ID, CATEGORY_ID, YEAR, MONTH) VALUES
('101', '0066', '010', 1984, 07);

SET @month_ref_id := (SELECT LAST_INSERT_ID());

然后详细语句变成:

INSERT INTO DAILY (MONTH_REF_ID, AMOUNT, DAILY_FLAG_ID, DAY) VALUES (@month_ref_id, 0, ' ', 1);
INSERT INTO DAILY (MONTH_REF_ID, AMOUNT, DAILY_FLAG_ID, DAY) VALUES (@month_ref_id, 0.5, ' ', 2);
INSERT INTO DAILY (MONTH_REF_ID, AMOUNT, DAILY_FLAG_ID, DAY) VALUES (@month_ref_id, 0, 'T', 3);

约束

MONTH_REF 表有一个 AUTO_INCREMENT 主键并在其上建立了索引。 DAILY 表没有索引，也没有主键。如果有帮助，可以将主键添加到 DAILY 表。

问题

什么是比建议的解决方案更有效地执行(十亿左右)插入语句的方法？

谢谢!

Answer 1

此解决方案有效:

INSERT INTO MONTH_REF (DISTRICT_ID,STATION_ID,CATEGORY_ID,YEAR,MONTH) VALUES('101','QFEG','012',1973,08);
SET @month_ref_id := (SELECT LAST_INSERT_ID());
INSERT INTO DAILY (MONTH_REF_ID,AMOUNT,DAILY_FLAG_ID,DAY) VALUES(@month_ref_id,0,' ',1),(@month_ref_id,0,' ',2),(@month_ref_id,0,' ',3);

插入量增加了大约四个数量级。