mysql 以优化的方式向大表添加多列

Question

我想将 8 个新列添加到具有数百万条记录的 innodb 的大型 mysql(版本 5.6)表中。我试图以最优化的方式实现这一目标。

与在 8 个不同查询中添加 8 列相比，使用单个查询添加所有列是否有任何优势。如果是这样想知道为什么。

关于指定 算法=原位，锁定=无 ，我需要注意什么，以免导致任何数据损坏或应用程序故障!

我正在通过查询测试 ALGORITHM=INPLACE, LOCK=NONE。

ALTER TABLE table_test ADD COLUMN test_column TINYINT UNSIGNED DEFAULT 0 ALGORITHM=INPLACE LOCK = NONE;

但它花费的时间与使用 ALGORITHM=DEFAULT 运行的查询相同。可能是什么原因。

我正在更改的表只有主键索引而没有其他索引。从应用程序进入该表的查询是:

insert into table;
select * from table where user_id=uid;
select sum(column) from table where user_id=id and date<NOW();

Answer 1

“优化”是指“最快”吗？或者“对其他查询的影响最小”？

在旧版本中，最佳方式(不使用附加组件)是将所有 ADD COLUMNs在单个 ALTER TABLE ;然后等到它完成。

在任何版本中，pt-online-schema-change将添加所有列，只有短暂的停机时间。

既然你提到ALGORITHM=INPLACE, LOCK=NONE ，我假设您使用的是较新版本？所以，可能是 8 ALTERs是最优的。会有一些干扰，但也许不会“太多”。
ALGORITHM=DEFAULT让服务器选择“最好的”。这几乎总是真正的“最佳”。也就是说，除了 DEFAULT 之外，几乎不需要说任何其他的话。 .

你永远不会得到数据损坏。在最坏的情况下，由于 ALTER(s) 的干扰，查询可能会因某种超时而失败。 .您应该始终检查错误(包括超时)，并在您的应用程序中处理它。

讨论查询...

insert into table;

一次一行？还是分批的？ (批处理效率更高——也许要好 10 倍。)

select * from table;

肯定不是!这将为您提供数百万行的所有列。你为什么要这样做？

select count(column) from table where pk=id and date<NOW();

COUNT(col)支票 col因为是 NOT NULL ——你需要吗？如果没有，那么只需执行 COUNT(*) .
WHERE pk=id只给你一行；那么为什么也有资格获得 date<NOW() ? PRIMARY KEY使查询尽可能快。

唯一的索引是 PRIMARY KEY ?这对于百万行的表来说似乎不寻常。它是“数据仓库”应用程序中的“事实”表吗？

内件

(警告:对 Internals 的大部分讨论都是间接得出的，可能是不正确的。)

对于一些 ALTERs ，工作本质上只是在模式中。例如:在 ENUM 的末尾添加选项;增加 VARCHAR 的大小.

对于一些 ALTERs与 INPLACE ，处理本质上是在适当的地方修改数据——而不必复制它。例如:在最后添加一列。
PRIMARY KEY更改(在 InnoDB 中)必然涉及重建包含数据的 BTree；他们无法做到 INPLACE .

很多次要 INDEX操作可以在不接触(除了读取)数据的情况下完成。 DROP INDEX扔掉一个 BTree 并进行一些元更改。 ADD INDEX读取整个表，在旁边建立索引 BTree，然后宣布它的存在。 CHARACTER SET和 COLLATION更改需要重建索引。

如果必须复制表，则表上有一个重要的锁。任意 ALTER由于 I/O 和/或 CPU 和/或对块/行/等的短暂锁定，需要读取所有数据具有间接影响。

目前尚不清楚代码是否足够智能以处理多任务 ALTER以最有效的方式。将 8 列合二为一 INPLACE pass 应该是可能的，但如果它使代码过于复杂，则该操作可能会转换为 COPY。

可能是多任务 ALTER将做“最坏”的情况。例如，更改 PRIMARY KEY并增加 ENUM将简单地在一个 COPY 中完成.自 COPY是做所有事情的原始方式 ALTERs ，它现在已经很好地调试和优化了。 (但它缓慢且具有侵入性。)
COPY实现起来真的很简单，主要涉及现有的原语:

锁real所以没有人写信给它

CREATE TABLE new LIKE real;

ALTER TABLE new ... -- 无论您要什么

从 real 复制所有行至 new -- 这是缓慢的部分

RENAME TABLE real TO old, new TO real; -- 快速、原子等

解锁

DROP TABLE old;

INPLACE更复杂，因为它必须在许多不同的算法和锁定级别之间做出决定。 DEFAULT必须下船到 COPY如果做不到 INPLACE .