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

Play Games

本文首發於公眾號:Hunter後端 原文鏈接:MySQL面試必備一之索引 在面試過程中,會有一些關於 MySQL 索引相關的問題,以下總結了一些: MySQL 的數據存儲使用的是什麼索引結構 B+ 樹的結構是什麼樣子 什麼是複合索引、聚簇索引、覆蓋索引 什麼是最左匹配原則 數據 B+ 樹中是如何查詢 ...

1. 概述

The online DDL feature provides support for instant and in-place table alterations and concurrent DML. Benefits of this feature include:

• Improved responsiveness【rɪ'spɒnsɪvnəs 響應性;靈敏度;敏感性;響應度;易起反應;】 and availability【əˌveɪlə'bɪləti 可利用性;可利用;可用性;有用(效)性;使用價值;(有效)利用率;工作效率;】 in busy production environments, where making a table unavailable for minutes or hours is not practical【ˈpræktɪkl 實際的;適用的;切實可行的;有用的;真實的;明智的;幾乎完全的;客觀存在的;心靈手巧的;】----在實際生產中,讓一個表在分鐘級別\小時級別 不可用,都是不現時的,不符合要求的.

• For in-place operations, the ability to adjust the balance between performance and concurrency during DDL operations using the LOCK clause.

• Less disk space usage and I/O overhead【ˌoʊvərˈhed 開銷;經常費用;經常開支;】 than the table-copy method. --更少的磁碟空間使用和更低的IO消耗

註意:ALGORITHM=INSTANT support is available for ADD COLUMN and other operations in MySQL 8.0.12.

Typically, you do not need to do anything special to enable online DDL. By default, MySQL performs the operation instantly or in place, as permitted, with as little locking as possible. ---一般會自動選擇好的方法

You can control aspects of a DDL operation using the ALGORITHM and LOCK clauses of the ALTER TABLE statement. These clauses are placed at the end of the statement, separated from the table and column specifications by commas. For example:---也可以顯示指定


 The LOCK clause may be used for operations that are performed in place and is useful for fine-tuning the degree of concurrent access to the table during operations. Only LOCK=DEFAULT is supported for operations that are performed instantly. The ALGORITHM clause is primarily intended for performance comparisons【kəmˈpɛrəsənz 比較;對比;相比;】 and as a fallback to the older table-copying behavior in case you encounter any issues. For example:

• To avoid accidentally【æksəˈdɛntəli 意外地;出其不意地;不知不覺地;】making the table unavailable for reads, writes, or both, during an in-place ALTER TABLE operation, specify a clause on the ALTER TABLE statement such as LOCK=NONE (permit reads and writes) or LOCK=SHARED (permit reads). The operation halts【hɔːlts (使)停止,停下;】immediately if the requested level of concurrency is not available.

• To compare performance between algorithms, run a statement with ALGORITHM=INSTANT, ALGORITHM=INPLACE and ALGORITHM=COPY. You can also run a statement with the old_alter_table configuration option enabled to force the use of ALGORITHM=COPY.

• To avoid tying up【tying up 捆綁;欲望捆綁;】 the server with an ALTER TABLE operation that copies the table, include ALGORITHM=INSTANT or ALGORITHM=INPLACE. The statement halts immediately if it cannot use the specified algorithm.----不行的話,就會立即報錯退出的

2.Index Operations

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

Operation Instant In Place Rebuilds Table Permits Concurrent DML Only Modifies Metadata
Creating or adding a secondary index NO YES NO YES NO
Dropping an index NO YES NO YES YES
Renaming an index NO YES NO YES YES
Adding a FULLTEXT index NO YES* NO* NO NO
Adding a SPATIAL index NO YES NO NO NO
Changing the index type YES YES NO YES YES


• Creating or adding a secondary index

The table remains available for read and write operations while the index is being created. The CREATE INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table.

Online DDL support for adding secondary indexes means that you can generally speed the overall process of creating and loading a table and associated indexes by creating the table without secondary indexes, then adding secondary indexes after the data is loaded.

A newly created secondary index contains only the committed data in the table at the time the CREATE INDEX or ALTER TABLE statement finishes executing. It does not contain any uncommitted values, old versions of values, or values marked for deletion but not yet removed from the old index.

Some factors affect the performance, space usage, and semantics of this operation.

 • Dropping an index

The table remains available for read and write operations while the index is being dropped. The DROP INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects【rɪˈflekts 反映;反射(聲、光、熱等);顯示,表明,表達(事物的自然屬性或人們的態度、情感等);映出(影像)】 the most recent【ˈriːsnt 最近的;近來的;新近的;】 contents of the table.

• Adding a FULLTEXT index

Adding the first FULLTEXT index rebuilds the table if there is no user-defined FTS_DOC_ID column. Additional FULLTEXT indexes may be added without rebuilding the table.

3.Primary Key Operations

The following table provides an overview of online DDL support for primary key operations. An asterisk【ˈæstərɪsk 星號(置於詞語旁以引起註意或另有註釋)】 indicates additional information, an exception, or a dependency.

Operation Instant In Place Rebuilds Table Permits Concurrent DML Only Modifies Metadata
Adding a primary key NO Yes* Yes* Yes NO
Dropping a primary key NO No Yes NO NO
Dropping a primary key and adding another NO Yes Yes Yes NO

• Adding a primary key

Rebuilds the table in place. Data is reorganized substantially【səbˈstænʃəli 基本上;大體上;非常;大大地;總的來說;】, making it an expensive operation. ALGORITHM=INPLACE is not permitted under certain conditions if columns have to be converted to NOT NULL.

 Restructuring the clustered index always requires copying of table data. Thus, it is best to define the primary key when you create a table, rather than issuing ALTER TABLE ... ADD PRIMARY KEY later.

When you create a UNIQUE or PRIMARY KEY index, MySQL must do some extra work. For UNIQUE indexes, MySQL checks that the table contains no duplicate values for the key. For a PRIMARY KEY index, MySQL also checks that none of the PRIMARY KEY columns contains a NULL.

When you add a primary key using the ALGORITHM=COPY clause, MySQL converts NULL values in the associated【əˈsoʊsieɪtɪd 相關的;(用於聯合企業的名稱)聯合的;有關聯的;有聯繫的;】 columns to default values: 0 for numbers, an empty string for character-based columns and BLOBs, and 0000-00-00 00:00:00 for DATETIME. This is a non-standard behavior that Oracle recommends you not rely on. Adding a primary key using ALGORITHM=INPLACE is only permitted when the SQL_MODE setting includes the strict_trans_tables or strict_all_tables flags; when the SQL_MODE setting is strict, ALGORITHM=INPLACE is permitted, but the statement can still fail if the requested primary key columns contain NULL values. The ALGORITHM=INPLACE behavior is more standard-compliant.

If you create a table without a primary key, InnoDB chooses one for you, which can be the first UNIQUE key defined on NOT NULL columns, or a system-generated key. To avoid uncertainty and the potential space requirement for an extra hidden column, specify the PRIMARY KEY clause as part of the CREATE TABLE statement.

MySQL creates a new clustered index by copying the existing data from the original table to a temporary table that has the desired index structure. Once the data is completely copied to the temporary table, the original table is renamed with a different temporary table name. The temporary table comprising the new clustered index is renamed with the name of the original table, and the original table is dropped from the database.

The online performance enhancements that apply to operations on secondary indexes do not apply to the primary key index. The rows of an InnoDB table are stored in a clustered index organized based on the primary key, forming what some database systems call an “index-organized table”. Because the table structure is closely tied to the primary key, redefining the primary key still requires copying the data.

When an operation on the primary key uses ALGORITHM=INPLACE, even though the data is still copied, it is more efficient than using ALGORITHM=COPY because:

• No undo logging or associated redo logging is required for ALGORITHM=INPLACE. These operations add overhead to DDL statements that use ALGORITHM=COPY.

• The secondary index entries are pre-sorted, and so can be loaded in order.

• The change buffer is not used, because there are no random-access inserts into the secondary indexes.

• Dropping a primary key


Only ALGORITHM=COPY supports dropping a primary key without adding a new one in the same ALTER TABLE statement.

• Dropping a primary key and adding another


Data is reorganized substantially, making it an expensive operation.



Play Games
  • 實驗介紹: 活動目錄簡稱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 ...
  • 對於生活中最常見的小游戲——走迷宮,相信大家都不陌生,人為走相信大家都會走,但能不能用代碼實現,我們認為是可以的,以下是我們對如何走迷宮的一些看法和代碼實現(cz負責隊列解決,mml負責用棧解決): 1.關於用隊列解決: 先簡單介紹一下隊列:隊列是一種操作受限的線性表,只允許在表的一端進行插入,在表 ...
  • 華為雲數倉GaussDB(DWS)研發專家高若岳老師,深入解析GaussDB(DWS)數據倉庫如何與大數據生態快速對接。 隨著智能數據時代的到來,數據量爆髮式增長,數據形態呈海量化和多樣化發展,不再是單一的結構化數據。從海量和多樣化的數據做融合分析,創造更多業務價值的訴求日益強烈。在本期《Gauss ...
  • GreatSQL 優化技巧:將 MINUS 改寫為標量子查詢 前言 minus 指令運用在兩個 SQL 語句上,取兩個語句查詢結果集的差集。它先找出第一個 SQL 所產生的結果,然後看這些結果有沒有在第二個 SQL 的結果中,如果在,那這些數據就被去除,不會在最後的結果中出現,第二個 SQL 結果集 ...
  • 本文分享自華為雲社區《GeminiDB Cassandra介面新特性FLASHBACK發佈:任意時間點秒級閃回》,作者: GaussDB 資料庫。 技術背景 資料庫作為現代信息系統的核心組成部分,承擔著存儲、管理和檢索大量數據的重要任務。然而,在實際的業務運行過程中,由於各種原因,資料庫可能會出現異 ...
    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文件,下載地址 ...
  • 經過前面幾篇的學習,我們瞭解到指令的大概分類,如:參數載入指令,該載入指令以 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分析工具和業務代碼來判斷具體是哪 ...