MySQL 8.0 Reference Manual(讀書筆記80節-- InnoDB Row Formats)

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

什麼是哈希桶 Redis中的哈希桶是一種數據結構,用於在Redis的哈希表(如字典結構)中存儲鍵值對。哈希桶是哈希表數組中的每個元素,可以視為一個容器或槽位,用於存放數據。在Redis中,當插入一個新的鍵值對時,會根據鍵的哈希值計算出一個索引,該索引指向特定的哈希桶。 每個哈希桶可以存儲多個鍵值對, ...


1. 概述

The row format of a table determines how its rows are physically stored, which in turn can affect the performance of queries and DML operations. As more rows fit into a single disk page, queries and index lookups can work faster, less cache memory is required in the buffer pool, and less I/O is required to write out updated values.

The data in each table is divided into pages. The pages that make up each table are arranged in a tree data structure called a B-tree index. Table data and secondary indexes both use this type of structure. The B-tree index that represents【reprɪˈzents 代表;意味著;相當於;等於;】 an entire table is known as the clustered index, which is organized according to the primary key columns. The nodes of a clustered index data structure contain the values of all columns in the row. The nodes of a secondary index structure contain the values of index columns and primary key columns.

Variable-length columns【可變長度的欄位】 are an exception to the rule that column values are stored in B-tree index nodes. Variable-length columns that are too long to fit on a B-tree page are stored on separately allocated disk pages called overflow pages. Such columns are referred to as off-page columns. The values of off-page columns are stored in singly-linked【ˈsɪŋɡli lɪŋkt  單向鏈接】 lists 【單鏈】of overflow pages, with each such column having its own list of one or more overflow pages. Depending on column length, all or a prefix【ˈpriːfɪks 首碼(綴於單詞前以改變其意義的字母或字母組合);(人名前的)稱謂;前置代號(置於前面的單詞或字母、數字)】 of variable-length column values are stored in the B-tree to avoid wasting storage and having to read a separate page.

The InnoDB storage engine supports four row formats: REDUNDANT【rɪˈdʌndənt 冗餘的;多餘的;不需要的;】, COMPACT【ˈkɑːmpækt 緊湊的;小型的;緊密的;體積小的;袖珍的;堅實的;矮小而健壯的;】, DYNAMIC【daɪˈnæmɪk] 動態的;動力的;力的;充滿活力的;發展變化的;精力充沛的;個性強的;】, and COMPRESSED【kəmˈprest 壓縮(文件等);(被)壓緊;濃縮;精簡;

Row Format Compact Storage Characteristics Enhanced Variable-Length Column Storage Large Index Key Prefix Support Compression Support Supported Tablespace Types
REDUNDANT No No No No system, file-per-table, genera
COMPACT Yes No No No system, file-per-table, genera
DYNAMIC Yes Yes Yes No system, file-per-table, genera
COMPRESSED Yes Yes Yes Yes file-per-table, general

2.REDUNDANT Row Format

The REDUNDANT format provides compatibility with older versions of MySQL.

Tables that use the REDUNDANT row format store the first 768 bytes of variable-length column values (VARCHAR, VARBINARY, and BLOB and TEXT types) in the index record within the B-tree node, with the remainder stored on overflow pages. Fixed-length columns greater than or equal to 768 bytes are encoded as variable-length columns, which can be stored off-page. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.

If the value of a column is 768 bytes or less, an overflow page is not used, and some savings in I/O may result, since the value is stored entirely in the B-tree node. This works well for relatively short BLOB column values, but may cause B-tree nodes to fill with data rather than key values, reducing their efficiency. Tables with many BLOB columns could cause B-tree nodes to become too full, and contain too few rows, making the entire index less efficient than if rows were shorter or column values were stored off-page.

3.REDUNDANT Row Format Storage Characteristics

The REDUNDANT row format has the following storage characteristics:

• Each index record contains a 6-byte header. The header is used to link together consecutive【kənˈsekjətɪv 連續的;連續不斷的】 records, and for row-level locking.

• Records in the clustered index contain fields for all user-defined columns. In addition, there is a 6-byte transaction ID field and a 7-byte roll pointer field.

• If no primary key is defined for a table, each clustered index record also contains a 6-byte row ID field.

• Each secondary index record contains all the primary key columns defined for the clustered index key that are not in the secondary index.

• A record contains a pointer to each field of the record. If the total length of the fields in a record is less than 128 bytes, the pointer is one byte; otherwise, two bytes. The array【əˈreɪ 陣列;數組;大量;大堆;大群】 of pointers is called the record directory. The area where the pointers point is the data part of the record.

• Internally【ɪnˈtɜrnəli 在內部;內部的;內部地】, fixed-length character columns such as CHAR(10) in stored in fixed-length format. Trailing【treɪlɪŋ 尾隨;尾部;拖尾;後續】 spaces are not truncated from VARCHAR columns.

• Fixed-length columns greater than or equal to 768 bytes are encoded as variable-length columns, which can be stored off-page. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.

• An SQL NULL value reserves one or two bytes in the record directory. An SQL NULL value reserves zero bytes in the data part of the record if stored in a variable-length column. For a fixed-length column, the fixed length of the column is reserved in the data part of the record. Reserving fixed space for NULL values permits columns to be updated in place from NULL to non-NULL values without causing index page fragmentation.

4.COMPACT Row Format

The COMPACT row format reduces row storage space by about 20% compared to the REDUNDANT row format, at the cost of increasing CPU use for some operations. If your workload is a typical one that is limited by cache hit rates and disk speed, COMPACT format is likely to be faster. If the workload is limited by CPU speed, compact format might be slower.

Tables that use the COMPACT row format store the first 768 bytes of variable-length column values (VARCHAR, VARBINARY, and BLOB and TEXT types) in the index record within the B-tree node, with the remainder stored on overflow pages. Fixed-length columns greater than or equal to 768 bytes are encoded as variable-length columns, which can be stored off-page. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.

If the value of a column is 768 bytes or less, an overflow page is not used, and some savings in I/O may result, since the value is stored entirely in the B-tree node. This works well for relatively【ˈrelətɪvli 相對地;相當地;相當程度上】 short BLOB column values, but may cause B-tree nodes to fill with data rather than key values, reducing their efficiency. Tables with many BLOB columns could cause B-tree nodes to become too full, and contain too few rows, making the entire index less efficient than if rows were shorter or column values were stored off-page.

5. COMPACT Row Format Storage Characteristics

The COMPACT row format has the following storage characteristics:

• Each index record contains a 5-byte header that may be preceded by a variable-length header. The header is used to link together consecutive records, and for row-level locking.

• The variable-length part of the record header contains a bit vector for indicating NULL columns. If the number of columns in the index that can be NULL is N, the bit vector occupies CEILING(N/8) bytes. (For example, if there are anywhere from 9 to 16 columns that can be NULL, the bit vector uses two bytes.) Columns that are NULL do not occupy space other than the bit in this vector. The variable-length part of the header also contains the lengths of variable-length columns. Each length takes one or two bytes, depending on the maximum length of the column. If all columns in the index are NOT NULL and have a fixed length, the record header has no variable-length part.

• For each non-NULL variable-length field, the record header contains the length of the column in one or two bytes. Two bytes are only needed if part of the column is stored externally in overflow pages or the maximum length exceeds 255 bytes and the actual length exceeds 127 bytes. For an externally stored column, the 2-byte length indicates the length of the internally stored part plus the 20-byte pointer to the externally stored part. The internal part is 768 bytes, so the length is 768+20. The 20-byte pointer stores the true length of the column.

• The record header is followed by the data contents of non-NULL columns.

• Records in the clustered index contain fields for all user-defined columns. In addition, there is a 6-byte transaction ID field and a 7-byte roll pointer field.

• If no primary key is defined for a table, each clustered index record also contains a 6-byte row ID field.

• Each secondary index record contains all the primary key columns defined for the clustered index key that are not in the secondary index. If any of the primary key columns are variable length, the record header for each secondary index has a variable-length part to record their lengths, even if the secondary index is defined on fixed-length columns.

• Internally, for nonvariable-length character sets, fixed-length character columns such as CHAR(10) are stored in a fixed-length format.Trailing spaces are not truncated from VARCHAR columns.

• Internally, for variable-length character sets such as utf8mb3 and utf8mb4, InnoDB attempts to store CHAR(N) in N bytes by trimming trailing spaces. If the byte length of a CHAR(N) column value exceeds N bytes, trailing spaces are trimmed to a minimum of the column value byte length. The maximum length of a CHAR(N) column is the maximum character byte length × N.

A minimum of N bytes is reserved for CHAR(N). Reserving the minimum space N in many cases enables column updates to be done in place without causing index page fragmentation. By comparison, CHAR(N) columns occupy the maximum character byte length × N when using the REDUNDANT row format.

Fixed-length columns greater than or equal to 768 bytes are encoded as variable-length fields, which can be stored off-page. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.

6. DYNAMIC Row Format

The DYNAMIC row format offers the same storage characteristics as the COMPACT row format but adds enhanced storage capabilities for long variable-length columns and supports large index key prefixes.

When a table is created with ROW_FORMAT=DYNAMIC, InnoDB can store long variable-length column values (for VARCHAR, VARBINARY, and BLOB and TEXT types) fully off-page, with the clustered index record containing only a 20-byte pointer to the overflow page. Fixed-length fields greater than or equal to 768 bytes are encoded as variable-length fields. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.

Whether columns are stored off-page depends on the page size and the total size of the row. When a row is too long, the longest columns are chosen for off-page storage until the clustered index record fits on the B-tree page. TEXT and BLOB columns that are less than or equal to 40 bytes are stored in line.

The DYNAMIC row format maintains the efficiency of storing the entire row in the index node if it fits (as do the COMPACT and REDUNDANT formats), but the DYNAMIC row format avoids the problem of filling B-tree nodes with a large number of data bytes of long columns. The DYNAMIC row format is based on the idea that if a portion of a long data value is stored off-page, it is usually most efficient to store the entire value off-page. With DYNAMIC format, shorter columns are likely to remain in the B-tree node, minimizing the number of overflow pages required for a given row.

The DYNAMIC row format supports index key prefixes up to 3072 bytes.

Tables that use the DYNAMIC row format can be stored in the system tablespace, file-per-table tablespaces, and general tablespaces. To store DYNAMIC tables in the system tablespace, either disable innodb_file_per_table and use a regular CREATE TABLE or ALTER TABLE statement, or use the TABLESPACE [=] innodb_system table option with CREATE TABLE or ALTER TABLE. The innodb_file_per_table variable is not applicable to general tablespaces, nor is it applicable when using the TABLESPACE [=] innodb_system table option to store DYNAMIC tables in the system tablespace.

 

補充:【DYNAMIC Row Format Storage Characteristics】The DYNAMIC row format is a variation of the COMPACT row format. For storage characteristics, see COMPACT Row Format Storage Characteristics.

7. COMPRESSED Row Format

The COMPRESSED row format offers the same storage characteristics and capabilities as the DYNAMIC row format but adds support for table and index data compression.

The COMPRESSED row format uses similar internal details for off-page storage as the DYNAMIC row format, with additional storage and performance considerations from the table and index data being compressed and using smaller page sizes. With the COMPRESSED row format, the KEY_BLOCK_SIZE option controls how much column data is stored in the clustered index, and how much is placed on overflow pages.

The COMPRESSED row format supports index key prefixes up to 3072 bytes.

Tables that use the COMPRESSED row format can be created in file-per-table tablespaces or general tablespaces. The system tablespace does not support the COMPRESSED row format. To store a COMPRESSED table in a file-per-table tablespace, the innodb_file_per_table variable must be enabled. The innodb_file_per_table variable is not applicable to general tablespaces. General tablespaces support all row formats with the caveat that compressed and uncompressed tables cannot coexist in the same general tablespace due to different physical page sizes.

補充:【Compressed Row Format Storage Characteristics】 The COMPRESSED row format is a variation of the COMPACT row format. For storage characteristics, see COMPACT Row Format Storage Characteristics.

8.Defining the Row Format of a Table 【怎麼去定義 Row Format】

The default row format for InnoDB tables is defined by innodb_default_row_format variable, which has a default value of DYNAMIC. The default row format is used when the ROW_FORMAT table option is not defined explicitly or when ROW_FORMAT=DEFAULT is specified.

The row format of a table can be defined explicitly【ɪkˈsplɪsətli 明確地;明白地】 using the ROW_FORMAT table option in a CREATE TABLE or ALTER TABLE statement. For example:

CREATE TABLE t1 (c1 INT) ROW_FORMAT=DYNAMIC;

An explicitly【ɪkˈsplɪsətli 明確地;明白地】 defined ROW_FORMAT setting overrides【oʊvərˈraɪdz 推翻,不理會;超馳控制,超控;重寫】 the default row format. Specifying ROW_FORMAT=DEFAULT is equivalent to using the implicit【ɪmˈplɪsɪt 含蓄的;完全的;內含的;無疑問的;不直接言明的;成為一部分的】 default.

The innodb_default_row_format variable can be set dynamically:

mysql> SET GLOBAL innodb_default_row_format=DYNAMIC;

Valid innodb_default_row_format options include DYNAMIC, COMPACT, and REDUNDANT. The COMPRESSED row format, which is not supported for use in the system tablespace, cannot be defined as the default. It can only be specified explicitly in a CREATE TABLE or ALTER TABLE statement. Attempting to set the innodb_default_row_format variable to COMPRESSED returns an error:

不能將COMPRESSED設置為預設的row format

mysql> SET GLOBAL innodb_default_row_format=COMPRESSED;
ERROR 1231 (42000): Variable 'innodb_default_row_format'
can't be set to the value of 'COMPRESSED'

Newly created tables use the row format defined by the innodb_default_row_format variable when a ROW_FORMAT option is not specified explicitly, or when ROW_FORMAT=DEFAULT is used. For example, the following CREATE TABLE statements use the row format defined by the innodb_default_row_format variable.

CREATE TABLE t1 (c1 INT);

CREATE TABLE t2 (c1 INT) ROW_FORMAT=DEFAULT;

When a ROW_FORMAT option is not specified explicitly, or when ROW_FORMAT=DEFAULT is used, an operation that rebuilds a table silently【ˈsaɪləntli 默默地;靜靜地;悄悄地;無聲地;不用言語表達地;不說話地】 changes the row format of the table to the format defined by the innodb_default_row_format variable.

Table-rebuilding operations include ALTER TABLE operations that use ALGORITHM=COPY or ALGORITHM=INPLACE where table rebuilding is required. OPTIMIZE TABLE is also a table-rebuilding operation.  ----表的重建,新的表用採用預設的ROW_FORMAT,應特別小心新舊表,可能ROW_FORMAT不一致了。

Consider the following potential issues before changing the row format of existing tables from REDUNDANT or COMPACT to DYNAMIC.--小心自動切換

• The REDUNDANT and COMPACT row formats support a maximum index key prefix length of 767 bytes whereas DYNAMIC and COMPRESSED row formats support an index key prefix length of 3072 bytes. In a replication environment, if the innodb_default_row_format variable is set to DYNAMIC on the source, and set to COMPACT on the replica, the following DDL statement, which does not explicitly define a row format, succeeds on the source but fails on the replica: ---主從架構下,小心主從表的ROW_FORMAT不一致的情況

CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 VARCHAR(5000), KEY i1(c2(3070)));

• Importing a table that does not explicitly define a row format results in a schema mismatch error if the innodb_default_row_format setting on the source server differs from the setting on the destination server.

9.查看 the Row Format of a Table

To determine the row format of a table, use SHOW TABLE STATUS IN tablename.

Alternatively, query the Information Schema INNODB_TABLES table:

SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='????';

 

-----https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html

 


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

-Advertisement-
Play Games
更多相關文章
  • 對於生活中最常見的小游戲——走迷宮,相信大家都不陌生,人為走相信大家都會走,但能不能用代碼實現,我們認為是可以的,以下是我們對如何走迷宮的一些看法和代碼實現(cz負責隊列解決,mml負責用棧解決): 1.關於用隊列解決: 先簡單介紹一下隊列:隊列是一種操作受限的線性表,只允許在表的一端進行插入,在表 ...
  • 華為雲數倉GaussDB(DWS)研發專家高若岳老師,深入解析GaussDB(DWS)數據倉庫如何與大數據生態快速對接。 隨著智能數據時代的到來,數據量爆髮式增長,數據形態呈海量化和多樣化發展,不再是單一的結構化數據。從海量和多樣化的數據做融合分析,創造更多業務價值的訴求日益強烈。在本期《Gauss ...
  • GreatSQL 優化技巧:將 MINUS 改寫為標量子查詢 前言 minus 指令運用在兩個 SQL 語句上,取兩個語句查詢結果集的差集。它先找出第一個 SQL 所產生的結果,然後看這些結果有沒有在第二個 SQL 的結果中,如果在,那這些數據就被去除,不會在最後的結果中出現,第二個 SQL 結果集 ...
  • 本文分享自華為雲社區《GeminiDB Cassandra介面新特性FLASHBACK發佈:任意時間點秒級閃回》,作者: GaussDB 資料庫。 技術背景 資料庫作為現代信息系統的核心組成部分,承擔著存儲、管理和檢索大量數據的重要任務。然而,在實際的業務運行過程中,由於各種原因,資料庫可能會出現異 ...
  • 本文首發於公眾號:Hunter後端 原文鏈接:MySQL面試必備一之索引 在面試過程中,會有一些關於 MySQL 索引相關的問題,以下總結了一些: MySQL 的數據存儲使用的是什麼索引結構 B+ 樹的結構是什麼樣子 什麼是複合索引、聚簇索引、覆蓋索引 什麼是最左匹配原則 數據 B+ 樹中是如何查詢 ...
  • 本文分享自華為雲社區《GaussDB(DWS)的cgroup、資源池、用戶的關係》,作者: nullptr_。 1. 前言 本文主要展示了DWS中cgroup、資源池、用戶之間的關係,從而對DWS的資源設置情況有個初步瞭解。 2. 相關對象創建腳本 gs_ssh -c "gs_cgroup -cS ...
  • 作者 | Shawn Gordon 翻譯 | Debra Chen 原文鏈接 | What the Heck is Apache SeaTunnel? 我在2023年初開始註意到Apache SeaTunnel的相關討論,一直低調地關註著。該項目始於2017年,最初名為Waterdrop,在Apac ...
  • 目錄一、環境搭建1、關閉防火牆firewalld,selinux2、每台主機安裝MySQL二、基於GTID的主從複製1、修改/etc/my.cnf文件2、檢查GTID狀態3、配置主從複製4、從庫設置三、部署MHA1、準備環境(所有節點)2、部署管理節點(可以部署在任何機器上)3、配置ssh信任4、啟 ...
一周排行
    -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分析工具和業務代碼來判斷具體是哪 ...