MySQL 8.0 Reference Manual(讀書筆記82節-- InnoDB and Online DDL (2))

来源:https://www.cnblogs.com/xuliuzai/p/18107691
-Advertisement-
Play Games

對於生活中最常見的小游戲——走迷宮,相信大家都不陌生,人為走相信大家都會走,但能不能用代碼實現,我們認為是可以的,以下是我們對如何走迷宮的一些看法和代碼實現(cz負責隊列解決,mml負責用棧解決): 1.關於用隊列解決: 先簡單介紹一下隊列:隊列是一種操作受限的線性表,只允許在表的一端進行插入,在表 ...


1. Column Operations

The following table provides an overview of online DDL support for column operations. An asterisk indicates additional information, an exception, or a dependency.

Operation Instant In Place Rebuilds Table Permits Concurrent DML Only Modifies Metadata
Adding a column Yes* Yes No* Yes* Yes
Dropping a column Yes* Yes Yes Yes Yes
Renaming a column Yes* Yes No Yes* Yes
Reordering columns No Yes Yes Yes No
Setting a column default value Yes Yes No Yes Yes
Changing the column data type No No Yes No No
Extending VARCHAR column size No Yes No Yes Yes
Dropping the column default value Yes Yes No Yes Yes
Changing the auto-increment value  No  Yes  No  Yes  No*
Making a column NULL  No  Yes  Yes*  Yes  No
Making a column NOT NULL No Yes* Yes* Yes No
Modifying the definition of an ENUM or SET column Yes Yes No Yes Yes

• Adding a column

ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INSTANT;

INSTANT is the default algorithm as of MySQL 8.0.12, and INPLACE before that.

The following limitations apply when the INSTANT algorithm adds a column:---場景限制

• A statement cannot combine【kəmˈbaɪn 結合;聯合;合併;混合;使融合;兼做;兼辦;】 the addition of a column with other ALTER TABLE actions that do not support the INSTANT algorithm.

• The INSTANT algorithm can add a column at any position in the table. Before MySQL 8.0.29, the INSTANT algorithm could only add a column as the last column of the table.--因版本不同,指定位置(befor、after)會影響演算法

• Columns cannot be added to tables that use ROW_FORMAT=COMPRESSED, tables with a FULLTEXT index, tables that reside【rɪˈzaɪd 居住在;定居於;】 in the data dictionary tablespace, or temporary tables. Temporary tables only support ALGORITHM=COPY.---這個共關鍵

• MySQL checks the row size when the INSTANT algorithm adds a column, and throws the following error if the addition exceeds the limit.--不能超過長度限制

ERROR 4092 (HY000): Column can't be added with ALGORITHM=INSTANT as
after this max possible row size crosses max permissible row size. Try
ALGORITHM=INPLACE/COPY.

Before MySQL 8.0.29, MySQL does not check the row size when the INSTANT algorithm adds a column. However, MySQL does check the row size during DML operations that insert and update rows in the table.【意思是DDL的時候不檢查,DML的時候檢查】

• The maximum number of columns in the internal【ɪnˈtɜːrnl 裡面的;本身的;內政的;體內的;內心的;(機構)內部的;】 representation【ˌreprɪzenˈteɪʃn 代表;陳述;表現;描述;支持;描繪;表現形式;維護;抗議;有代理人;】 of the table cannot exceed 1022 after column addition with the INSTANT algorithm. The error message is:--不能超過列(欄位)數限制

ERROR 4158 (HY000): Column can't be added to tbl_name with
ALGORITHM=INSTANT anymore. Please try ALGORITHM=INPLACE/COPY

 

Multiple columns may be added in the same ALTER TABLE statement. For example: --支持一次添加多列(欄位)

ALTER TABLE t1 ADD COLUMN c2 INT, ADD COLUMN c3 INT, ALGORITHM=INSTANT;

A new row version is created after each ALTER TABLE ... ALGORITHM=INSTANT operation that adds one or more columns, drops one or more columns, or adds and drops one or more columns in the same operation. The INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS column tracks the number of row versions for a table. The value is incremented each time a column is instantly added or dropped. The initial value is 0.

mysql> SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES 
 WHERE NAME LIKE 'test/t1';
+---------+--------------------+
| NAME | TOTAL_ROW_VERSIONS |
+---------+--------------------+
| test/t1 | 0 |
+---------+--------------------+

When a table with instantly added or dropped columns is rebuilt by table-rebuilding ALTER TABLE or OPTIMIZE TABLE operation, the TOTAL_ROW_VERSIONS value is reset to 0. The maximum number of row versions permitted is 64, as each row version requires additional space for table metadata. When the row version limit is reached, ADD COLUMN and DROP COLUMN operations using ALGORITHM=INSTANT are rejected with an error message that recommends rebuilding the table using the COPY or INPLACE algorithm.---64是臨界值

ERROR 4080 (HY000): Maximum row versions reached for table test/t1. No more
columns can be added or dropped instantly. Please use COPY/INPLACE.

Concurrent DML is not permitted when adding an auto-increment column. Data is reorganized substantially【[səbˈstænʃəli 基本上;大體上;非常;大大地;總的來說;】, making it an expensive operation. At a minimum, ALGORITHM=INPLACE, LOCK=SHARED is required.

 The table is rebuilt if ALGORITHM=INPLACE is used to add a column.

• Dropping a column

ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INSTANT;

INSTANT is the default algorithm as of MySQL 8.0.29, and INPLACE before that.

The following limitations apply when the INSTANT algorithm is used to drop a column:

• Dropping a column cannot be combined in the same statement with other ALTER TABLE actions that do not support ALGORITHM=INSTANT.

• Columns cannot be dropped from tables that use ROW_FORMAT=COMPRESSED, tables with a FULLTEXT index, tables that reside in the data dictionary tablespace, or temporary tables. Temporary tables only support ALGORITHM=COPY.

Multiple columns may be dropped in the same ALTER TABLE statement; for example:

ALTER TABLE t1 DROP COLUMN c4, DROP COLUMN c5, ALGORITHM=INSTANT;

Each time a column is added or dropped using ALGORITHM=INSTANT, a new row version is created. The INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS column tracks the number of row versions for a table. The value is incremented each time a column is instantly added or dropped. The initial value is 0.

mysql> SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES 
 WHERE NAME LIKE 'test/t1';
+---------+--------------------+
| NAME | TOTAL_ROW_VERSIONS |
+---------+--------------------+
| test/t1 | 0 |
+---------+--------------------+

When a table with instantly added or dropped columns is rebuilt by table-rebuilding ALTER TABLE or OPTIMIZE TABLE operation, the TOTAL_ROW_VERSIONS value is reset to 0. The maximum number of row versions permitted is 64, as each row version requires additional space for table metadata. When the row version limit is reached, ADD COLUMN and DROP COLUMN operations using ALGORITHM=INSTANT are rejected with an error message that recommends rebuilding the table using the COPY or INPLACE algorithm.

ERROR 4080 (HY000): Maximum row versions reached for table test/t1. No more
columns can be added or dropped instantly. Please use COPY/INPLACE.

If an algorithm other than ALGORITHM=INSTANT is used, data is reorganized substantially, making it an expensive operation.

• Renaming a column

ALTER TABLE tbl CHANGE old_col_name new_col_name data_type, ALGORITHM=INSTANT, LOCK=NONE;

ALGORITHM=INSTANT support for renaming a column was added in MySQL 8.0.28. Earlier MySQL Server releases support only ALGORITHM=INPLACE and ALGORITHM=COPY when renaming a column.

To permit concurrent DML, keep the same data type and only change the column name.---保持欄位類型不變,更新的只是名字

When you keep the same data type and [NOT] NULL attribute, only changing the column name, the operation can always be performed online.---並且NULL屬性值也沒有調整

Renaming a column referenced from another table is only permitted with ALGORITHM=INPLACE.【此時,只能是INPLACE】 If you use ALGORITHM=INSTANT, ALGORITHM=COPY, or some other condition that causes the operation to use those algorithms, the ALTER TABLE statement fails.

ALGORITHM=INSTANT supports renaming a virtual column; ALGORITHM=INPLACE does not.

ALGORITHM=INSTANT and ALGORITHM=INPLACE do not support renaming a column when adding or dropping a virtual column in the same statement. In this case, only ALGORITHM=COPY is supported.

• Reordering columns

To reorder columns, use FIRST or AFTER in CHANGE or MODIFY operations.

ALTER TABLE tbl_name MODIFY COLUMN col_name column_definition FIRST, ALGORITHM=INPLACE, LOCK=NONE;

Data is reorganized substantially, making it an expensive operation.

• Changing the column data type

ALTER TABLE tbl_name CHANGE c1 c1 BIGINT, ALGORITHM=COPY;

 Changing the column data type is only supported with ALGORITHM=COPY.

• Extending VARCHAR column size

ALTER TABLE tbl_name CHANGE COLUMN c1 c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;

The number of length bytes required by a VARCHAR column must remain【rɪˈmeɪn 保持不變;仍然存在,繼續存在;】 the same. For VARCHAR columns of 0 to 255 bytes in size, one length byte is required to encode the value. For VARCHAR columns of 256 bytes in size or more, two length bytes are required. As a result, in-place ALTER TABLE only supports increasing VARCHAR column size from 0 to 255 bytes, or from 256 bytes to a greater size. In-place ALTER TABLE does not support increasing the size of a VARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the number of required length bytes changes from 1 to 2, which is only supported by a table copy (ALGORITHM=COPY). For example, attempting to change VARCHAR column size for a single byte character set from VARCHAR(255) to VARCHAR(256) using in-place ALTER TABLE returns this error:

ALTER TABLE tbl_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

Decreasing VARCHAR size using in-place ALTER TABLE is not supported. Decreasing VARCHAR size requires a table copy (ALGORITHM=COPY).

• Setting a column default value

ALTER TABLE tbl_name ALTER COLUMN col SET DEFAULT literal, ALGORITHM=INSTANT;

Only modifies table metadata. Default column values are stored in the data dictionary.

• Dropping a column default value

ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT, ALGORITHM=INSTANT;

• Changing the auto-increment value

ALTER TABLE table AUTO_INCREMENT=next_value, ALGORITHM=INPLACE, LOCK=NONE;

Modifies a value stored in memory, not the data file.

In a distributed system using replication or sharding, you sometimes reset the auto-increment counter for a table to a specific value. The next row inserted into the table uses the specified value for its auto-increment column. You might also use this technique in a data warehousing environment where you periodically【ˌpiriˈɑdɪkəli】 empty all the tables and reload them, and restart the auto-increment sequence from 1.

• Making a column NULL

ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NULL, ALGORITHM=INPLACE, LOCK=NONE;

Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation.

• Making a column NOT NULL

ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;

Rebuilds the table in place. STRICT_ALL_TABLES or STRICT_TRANS_TABLES SQL_MODE is required for the operation to succeed. The operation fails if the column contains NULL values. The server prohibits【prəˈhɪbɪts (尤指以法令)禁止;阻止;使不可能;】 changes to foreign key columns that have the potential【pəˈtenʃl 潛在的;可能的;】 to cause loss of referential【refəˈrenʃl】 integrity【參照完整性】.Data is reorganized substantially, making it an expensive operation.

• Modifying the definition of an ENUM or SET column

CREATE TABLE t1 (c1 ENUM('a', 'b', 'c'));
ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a', 'b', 'c', 'd'), ALGORITHM=INSTANT;

Modifying the definition of an ENUM or SET column by adding new enumeration【ɪˌnuːməˈreɪʃn】 or set members to the end of the list of valid member values may be performed instantly or in place, as long as the storage size of the data type does not change. For example, adding a member to a SET column that has 8 members changes the required storage per value from 1 byte to 2 bytes; this requires a table copy. Adding members in the middle of the list causes renumbering of existing members, which requires a table copy【個數和類型長度很重要】.

2. Generated Column Operations

The following table provides an overview of online DDL support for generated column operations.

Operation Instant In Place Rebuilds Table Permits Concurrent DML Only Modifies Metadata
Adding a STORED column No No Yes No No
Modifying STORED column order No No Yes No No
Dropping a STORED column No Yes Yes Yes No
Adding a VIRTUAL column Yes Yes No Yes Yes
Modifying VIRTUAL column order No No Yes No No
Dropping a VIRTUAL column Yes Yes No Yes Yes

• Adding a STORED column

ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) STORED), ALGORITHM=COPY;

ADD COLUMN is not an in-place operation for stored columns (done without using a temporary table) because the expression must be evaluated by the server.

• Modifying STORED column order

ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED FIRST, ALGORITHM=COPY;

Rebuilds the table in place.

• Dropping a STORED column

ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE;

Rebuilds the table in place.

• Adding a VIRTUAL column

ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL), ALGORITHM=INSTANT;

Adding a virtual column can be performed instantly or in place for non-partitioned tables.

Adding a VIRTUAL is not an in-place operation for partitioned tables.

• Modifying VIRTUAL column order

ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL FIRST, ALGORITHM=COPY;

• Dropping a VIRTUAL column

ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;

Dropping a VIRTUAL column can be performed instantly or in place for non-partitioned tables.

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 進程 ID 是唯一的嗎?fork 後子進程記憶體頁會 Copy-On-Write 嗎?vfork 後子進程為何不能使用 return 或 exit?如何在 exec 後保持目錄流打開?解釋器文件首行能支持多於一個參數嗎?切換進程身份時 setuid、setreuid、seteuid 該用哪個?set-... ...
  • Windows Server 2022 Standard / Datacenter 版本說明:該版本包含了 server 2022的標準版、數據中心版,在安裝過程中可以選擇安裝哪個版本,該版本只有64位系統。 下載地址1:https://msproduct.download.prss.microso ...
  • windows 11 家庭版/家庭單語言版/教育版/專業版/專業教育版/專業工作站版 簡體中文 ed2k://|file|zh-cn_windows_11_consumer_editions_version_23h2_updated_march_2024_x64_dvd_bcbf6ac6.iso|6 ...
  • 本系列為《Learning eBPF》一書的翻譯系列。 (內容並非機翻,部分夾帶私貨)筆者學習自用,歡迎大家討論學習。 ...
  • 實驗介紹: 活動目錄簡稱AD,在裡面創建對象包括組織單位對象,用戶對象,電腦對象 實驗環境: 兩台虛擬機包括一臺dns1域控制器,一臺虛擬機 一:在AD管理中心建“組織單位”對象財務部 1.在dns1打開AD管理中心,右鍵本地,新建組織單位 2.輸入財務部,這裡用財務部做例子也可輸入其他內容 二: ...
  • 緩存穿透、擊穿和雪崩是緩存使用中的常見問題,對它們的理解和相應的解決方法對於維護系統性能和穩定性至關重要。 1.緩存穿透 定義:當客戶端請求的數據在緩存中和資料庫中都不存在時,該請求會直接打到資料庫上,這種情況稱為緩存穿透。如果持續請求這樣的數據,會給資料庫帶來不必要的壓力。 解決方法: 緩存空對象 ...
  • 1.安裝Docker - 2024.03.28 官方手冊 清華大學開源軟體鏡像站 | 可從主頁找到Docker資源 # Add Docker's official GPG key: sudo apt-get update sudo apt-get install ca-certificates cu ...
  • 熟悉鍵盤操作可以極大地提高操作效率,熟練的 Linux 用戶幾乎不需要使用滑鼠,僅用鍵盤就可以更快且更方便地完成所有操作。 Linux 命令解釋器(也可以叫 Shell,終端,命令行等)也有很多快捷鍵,熟練掌握可以極大的提高操作效率。在本文整理一些最常用的快捷鍵。 常用命令行操作快捷鍵 : Ctrl ...
一周排行
    -Advertisement-
    Play Games
  • 隨著Aspire發佈preview5的發佈,Microsoft.Extensions.ServiceDiscovery隨之更新, 服務註冊發現這個屬於老掉牙的話題解決什麼問題就不贅述了,這裡主要講講Microsoft.Extensions.ServiceDiscovery(preview5)以及如何 ...
  • 概述:通過使用`SemaphoreSlim`,可以簡單而有效地限制非同步HTTP請求的併發量,確保在任何給定時間內不超過20個網頁同時下載。`ParallelOptions`不適用於非同步操作,但可考慮使用`Parallel.ForEach`,儘管在非同步場景中謹慎使用。 對於併發非同步 I/O 操作的數量 ...
  • 1.Linux上安裝Docken 伺服器系統版本以及內核版本:cat /etc/redhat-release 查看伺服器內核版本:uname -r 安裝依賴包:yum install -y yum-utils device-mapper-persistent-data lvm2 設置阿裡雲鏡像源:y ...
  • 概述:WPF界面綁定和渲染大量數據可能導致性能問題。通過啟用UI虛擬化、非同步載入和數據分頁,可以有效提高界面響應性能。以下是簡單示例演示這些優化方法。 在WPF中,當你嘗試綁定和渲染大量的數據項時,性能問題可能出現。以下是一些可能導致性能慢的原因以及優化方法: UI 虛擬化: WPF提供了虛擬化技術 ...
  • 引言 上一章節介紹了 TDD 的三大法則,今天我們講一下在單元測試中模擬對象的使用。 Fake Fake - Fake 是一個通用術語,可用於描述 stub或 mock 對象。 它是 stub 還是 mock 取決於使用它的上下文。 也就是說,Fake 可以是 stub 或 mock Mock - ...
  • 為.net6在CentOS7上面做準備,先在vmware虛擬機安裝CentOS 7.9 新建CentOS764位的系統 因為CentOS8不更新了,所以安裝7;簡單就一筆帶過了 選擇下載好的操作系統的iso文件,下載地址https://mirrors.aliyun.com/centos/7.9.20 ...
  • 經過前面幾篇的學習,我們瞭解到指令的大概分類,如:參數載入指令,該載入指令以 Ld 開頭,將參數載入到棧中,以便於後續執行操作命令。參數存儲指令,其指令以 St 開頭,將棧中的數據,存儲到指定的變數中,以方便後續使用。創建實例指令,其指令以 New 開頭,用於在運行時動態生成並初始化對象。方法調用指... ...
  • LiteDB 是一個輕量級的嵌入式 NoSQL 資料庫,其設計理念與 MongoDB 類似,但它是完全使用 C# 開發的,因此與 C# 應用程式的集成非常順暢。與 SQLite 相比,LiteDB 提供了 NoSQL(即鍵值對)的數據存儲方式,並且是一個開源且免費的項目。它適用於桌面、移動以及 We ...
  • 1 開源解析和拆分文檔 第三方的工具去對文件解析拆分,去將我們的文件內容給提取出來,並將我們的文檔內容去拆分成一個小的chunk。常見的PDF word mark down, JSON、HTML。都可以有很好的一些模塊去把這些文件去進行一個東西去提取。 優勢 支持豐富的文檔類型 每種文檔多樣化選擇 ...
  • OOM是什麼?英文全稱為 OutOfMemoryError(記憶體溢出錯誤)。當程式發生OOM時,如何去定位導致異常的代碼還是挺麻煩的。 要檢查OOM發生的原因,首先需要瞭解各種OOM情況下會報的異常信息。這樣能縮小排查範圍,再結合異常堆棧、heapDump文件、JVM分析工具和業務代碼來判斷具體是哪 ...