MySQL 分区和临时表

Question

一个大表(~10.5M 行)最近引起了问题。 我之前修改了我的应用程序以使用临时表进行更快的选择，但由于 UPDATE 语句，我仍然遇到问题。 今天我实现了分区，以便更快地进行写入，但现在我的临时表出错了。 其目的是对事件进行分组，将集合的第一个事件 ID 放在EVENT_ID列中。 示例：编写 4 个从 1000 开始的事件将导致事件 1000、1001、1002、1003，所有事件EVENT_ID均为 1000。 我试图取消 UPDATE 语句，但这需要太多的重构，所以这不是一个选择。 下面是表定义：

CREATE TABLE `all_events` (
`ID` bigint NOT NULL AUTO_INCREMENT,
`EVENT_ID` bigint unsigned DEFAULT NULL,
`LAST_UPDATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`EMPLOYEE_ID` int unsigned NOT NULL,
`QUANTITY` float unsigned NOT NULL,
`OPERATORS` float unsigned NOT NULL DEFAULT '0',
`SECSEARNED` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'for all parts in QUANTITY',
`SECSBURNED` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
`YR` smallint unsigned NOT NULL DEFAULT (year(curdate())),
PRIMARY KEY (`ID`,`YR`),
KEY `LAST_UPDATE` (`LAST_UPDATE`),
KEY `EMPLOYEE_ID` (`EMPLOYEE_ID`),
KEY `EVENT_ID` (`EVENT_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=17464583 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
/*!50100 PARTITION BY RANGE (`YR`)
(PARTITION p2015 VALUES LESS THAN (2016) ENGINE = InnoDB,
PARTITION p2016 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p2017 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p2018 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p2019 VALUES LESS THAN (2020) ENGINE = InnoDB,
PARTITION p2020 VALUES LESS THAN (2021) ENGINE = InnoDB,
PARTITION p2021 VALUES LESS THAN (2022) ENGINE = InnoDB,
PARTITION p2022 VALUES LESS THAN (2023) ENGINE = InnoDB,
PARTITION p2023 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

现在在我的应用程序中运行报告时，语句：

CREATE TEMPORARY TABLE IF NOT EXISTS ape ENGINE=MEMORY AS
SELECT * FROM all_events
WHERE LAST_UPDATE BETWEEN '2022-05-01 00:00:00' AND CURRENT_TIMESTAMP()

产生错误：'Specified storage engine' is not supported for default value expressions.

有没有办法仍然使用带有ENGINE=MEMORY的临时表，或者有没有另一个我可以使用的高性能引擎？ 该语句一直有效，直到实现分区。 由于MySQL实现，InnoDB是我的表可以使用的唯一引擎，并且在分区之前一直是InnoDB。

编辑：删除ENGINE=MEMORY时，它确实有效，但运行SHOW CREATE TABLE告诉我它正在使用InnoDB。 我更喜欢MEMORY与InnoDB的性能提升。

第二次编辑： MySQL 服务器每天崩溃 2 到 3 次，每次我发现它时都会发现此错误：

TRANSACTION 795211228, ACTIVE 0 sec fetching rows
mysql tables in use 13, locked 13
LOCK WAIT 866 lock struct(s), heap size 106704, 4800 row lock(s), undo log entries 1
MySQL thread id 5032986, OS thread handle 140442167994112, query id 141216988 myserver 192.168.1.100 my-user Searching rows for update
UPDATE `all_events` SET `EVENT_ID`=LAST_INSERT_ID() WHERE `EVENT_ID` IS NULL
RECORD LOCKS space id 30558 page no 16 n bits 792 index EVENT_ID of table `mydb`.`all_events` trx id 795211228 lock_mode X

它运行具有3个节点的Galera集群。 节点 3 是主节点，变得不可用，1 脱机以重新同步 3。 我故障转移到 2，我们通常很好，直到它赶上，但它会导致停机。 我使用的临时表是为了加快读取速度，分区是我提高写入性能的尝试。

第三次编辑： 添加了示例 SELECT - 请注意，表定义中没有字段，为了帖子的简单性，我减少了显示的内容，但 SELECT 中的所有字段实际上都存在。

CREATE TEMPORARY TABLE IF NOT EXISTS allpe AS
SELECT * FROM all_events
WHERE LAST_UPDATE BETWEEN ? AND ?;
CREATE TEMPORARY TABLE IF NOT EXISTS ap1 AS SELECT * FROM allpe;
CREATE TEMPORARY TABLE IF NOT EXISTS ap2 AS SELECT * FROM allpe;
SELECT PART_NUMBER, WORKCENTER_NAME, SUM(SECSEARNED) AS EARNED, SUM(SECSBURNED) AS BURNED, SUM(QUANTITY) AS QUANTITY, (
SELECT SUM(ap1.SECSEARNED)
FROM ap1
WHERE ap1.PART_NUMBER = ape.PART_NUMBER AND ap1.WORKCENTER_ID = ape.WORKCENTER_ID
) AS EARNEDALL, (
SELECT SUM(ap2.SECSBURNED)
FROM ap2
WHERE ap2.PART_NUMBER = ape.PART_NUMBER AND ap2.WORKCENTER_ID = ape.WORKCENTER_ID
) AS BURNEDALL
FROM allpe ape
WHERE EMPLOYEE_ID = ?
GROUP BY PART_NUMBER, WORKCENTER_ID, WORKCENTER_NAME, EMPLOYEE_ID
ORDER BY EARNED;
DROP TEMPORARY TABLE allpe;
DROP TEMPORARY TABLE ap1;
DROP TEMPORARY TABLE ap2;

第四次编辑： 在存储过程内部写入 - 这不是在循环中，但多行可以从多个连接到employee_presence，所以我无法获取 ID 并将其存储以写入后续行。

INSERT INTO `all_events`(`EVENT_ID`,`LAST_UPDATE`,`PART_NUMBER`, `WORKCENTER_ID`,`XPPS_WC`, `EMPLOYEE_ID`,`WORKCENTER_NAME`, `QUANTITY`, `LEVEL_PART_NUMBER`,`OPERATORS`,`SECSEARNED`,`SECSBURNED`)
SELECT NULL,NOW(),NEW.PART_NUMBER,NEW.ID,OLD.XPPS_WC,ep.EMPLOYEE_ID,NEW.NAME,(NEW.PARTS_MADE-OLD.PARTS_MADE)*WorkerContrib(ep.EMPLOYEE_ID,OLD.ID),IFNULL(NEW.LEVEL_PART_NUMBER,NEW.PART_NUMBER),WorkerCount(NEW.ID)*WorkerContrib(ep.EMPLOYEE_ID,OLD.ID),WorkerContrib(ep.EMPLOYEE_ID,OLD.ID)*CreditSeconds,WorkerCount(NEW.ID)*WorkerContrib(ep.EMPLOYEE_ID,OLD.ID)*IFNULL(TIMESTAMPDIFF(SECOND, GREATEST(NEW.LAST_PART_TIME,NEW.JOB_START_TIME), now()),0)
FROM employee_presence ep WHERE ep.WORKCENTER_ID=OLD.ID;
UPDATE `all_events` SET `EVENT_ID`=LAST_INSERT_ID() WHERE `WORKCENTER_ID`=NEW.ID AND `EVENT_ID` IS NULL;

Answer 1

我想从 dev.MySQL.com 阅读以下链接

您不能使用创建临时表...喜欢创建一个空的 基于驻留在 MySQL 中的表的定义 表空间、InnoDB 系统表空间 (innodb_system) 或通用 表空间。此类表的表空间定义包括 定义表空间的表空间属性，其中的表空间 驻留，并且上述表空间不支持临时 表。基于此类 表中，请改用以下语法：

创建临时表new_tbl从限制 0 中选择 orig_tbl

;

因此，似乎适合您的情况的正确语法是：

CREATE TEMPORARY TABLE ape
SELECT * FROM all_events
WHERE... 

Answer 2

在当前问题中，有问题的列是YR smallint unsigned NOT NULL DEFAULT (year(curdate()))。此默认值对于分区表达式中使用的列是不合法的。错误将是"不允许(子)分区函数中的常量、随机或时区相关表达式......"。

---

只有当您通过删除分区来解决此问题时，您才会收到错误"默认值表达式不支持'指定的存储引擎'"。

CREATE TABLE .. SELECT从源表继承主列属性。

在当前问题中，问题列再次YR smallint unsigned NOT NULL DEFAULT (year(curdate()))。temptable 中的列必须继承主要属性，包括 DEFAULT 表达式 - 但MEMORY引擎不允许使用此表达式。

Answer 3

正如错误所暗示的那样，表达式默认值不适用于 MEMORY 存储引擎。

一种解决方案是从all_events.yr列中删除该默认值。

另一种解决方案是最初创建一个空的临时表作为InnoDB表，然后使用ALTER TABLE删除表达式默认值并转换为MEMORY引擎，然后再填充数据。

例：

mysql> create temporary table t as select * from all_events where false;
mysql> alter table t alter column yr drop default, engine=memory;
mysql> insert into t select * from all_events;

Answer 4

足够吗？如果我没记错的话，这相当于您的SELECT找到的内容(不需要临时表)：

SELECT  PART_NUMBER, WORKCENTER_ID, WORKCENTER_NAME, EMPLOYEE_ID,
SUM(SECSEARNED) AS TOT_EARNED,
SUM(SECSBURNED) AS TOT_BURNED,
SUM(QUANTITY) AS TOT_QUANTITY
FROM  all_events
WHERE  EMPLOYEE_ID = ?
AND  LAST_UPDATE >= '2022-05-01'
GROUP BY  PART_NUMBER, WORKCENTER_ID, WORKCENTER_NAME;

为了性能，它需要这个。

INDEX(EMPLOYEE_ID, LAST_UPDATE)

此外，删除分区可能会加快速度。

else(关于您所采用路径的其他修复的说明)

由于不需要yr，因此通过将"*"更改为所需列的列表来避免它

CREATE TEMPORARY TABLE IF NOT EXISTS ape ENGINE=MEMORY AS
SELECT * FROM all_events
WHERE LAST_UPDATE BETWEEN '2022-05-01 00:00:00' AND CURRENT_TIMESTAMP()

其中 AP2.PART_NUMBER = 猿。PART_NUMBER和 AP2。WORKCENTER_ID = 猿。WORKCENTER_ID

将此复合索引添加到all_events：

INDEX(PART_NUMBER, WORKCENTER_ID)

这可能足以在没有临时表的情况下使查询足够快。Also add that建成后就全部了。

如果您运行的是MySQL 8.0，则可以使用WITH，而不需要两个额外的临时表。