MySQL中列數(shù)量及長度各是多少

由于MySQL設(shè)計的一些核心限制。答案不是單一的數(shù)字，而是分層和復(fù)雜的，取決于多個因素，包括存儲引擎、行格式以及MySQL版本。

簡單來說，最主要的限制來自于行的最大大小，而不是單純的列數(shù)量。

以下是詳細的分解說明：

1. 硬限制：絕對最大列數(shù)

無論使用何種存儲引擎和行格式，MySQL每個表的絕對最大列數(shù)是4096。

但是，這只是一個理論上的上限。在實際中，幾乎永遠無法達到這個數(shù)字，因為會受到下面“行大小限制”的約束。

2. 實際限制：行大小限制

這才是最關(guān)鍵的限制。一行的所有列的數(shù)據(jù)加起來，不能超過一個特定的值。

• 默認最大行大?。?5,535字節(jié)。

這個限制是共享給所有列的。這意味著，即使有100個列，每個列只占用100字節(jié)，總長度10,000字節(jié)，這也是可以的。但如果有一個VARCHAR(30000)的列，它自己就可能占用30,000字節(jié)，那么剩下的空間就很少了，無法再創(chuàng)建很多其他大字段。

示例：
創(chuàng)建兩個VARCHAR(30000)的列，理論上需要60,000字節(jié)，這小于65,535字節(jié)。

CREATE TABLE test (
    col1 VARCHAR(30000),
    col2 VARCHAR(30000)
) ENGINE=InnoDB;

但會遇到一個錯誤：
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535...

為什么？
因為VARCHAR使用額外的一到兩個字節(jié)來存儲字符串的實際長度。所以兩個VARCHAR(30000)列，最大可能占用 30000 * 2 + 2 * 2 = 60004 字節(jié)，這看起來是夠的。但實際上，MySQL的計算方式更為復(fù)雜，還會考慮字符集（如utf8mb4一個字符最多占4字節(jié)）等因素，導致實際計算出的最大可能長度超過65,535字節(jié)。

3. 存儲引擎和行格式的影響

不同的存儲引擎和行格式對行大小的限制有不同的處理方式，這尤其影響可變長度數(shù)據(jù)（如VARCHAR, TEXT, BLOB） 和大對象數(shù)據(jù)。

InnoDB 引擎

InnoDB是MySQL最常用、默認的存儲引擎。它的行為如下：

• 默認行格式（DYNAMIC 或 COMPACT）：
  • 這意味著，只要有很多TEXT或BLOB列，或者非常長的VARCHAR列，實際上可以擁有遠超65,535字節(jié)的總數(shù)據(jù)，因為大部分數(shù)據(jù)被“卸載”到了別處。但是，仍然受限于每行最多4096列的絕對限制。
  • 對于可變長度列（如VARCHAR, VARBINARY），如果單個列的長度超過 768 字節(jié)，InnoDB會將其前768字節(jié)存儲在頁面中，而將剩余部分存儲在溢出頁中。
• BLOB和TEXT類型 幾乎總是將其主要內(nèi)容存儲在溢出頁中，只在主行中保留一個20字節(jié)的指針。
  • 關(guān)鍵點：存儲在溢出頁中的數(shù)據(jù)不計入 65,535 字節(jié)的行大小限制。只有存儲在主行中的數(shù)據(jù)（768字節(jié)的前綴+指針+固定長度列）才受限于另一個更小的限制（約為8,000字節(jié)，具體取決于頁面大小）。
  • 冗余行格式（REDUNDANT）：
  • 處理方式較老，所有內(nèi)容都更傾向于存儲在主行中，更容易達到行大小限制。

MyISAM 引擎

• MyISAM也有65,535字節(jié)的行大小限制。
• 但與InnoDB不同，它沒有溢出頁的概念。所有數(shù)據(jù)都必須放在一行內(nèi)（但BLOB和TEXT類型仍然只計部分長度到行限制中）。
• 因此，對于MyISAM，65,535字節(jié)的限制更加嚴格和直接。

總結(jié)表格

實踐建議

1. 不要試圖接近極限：即使理論上允許4096列，設(shè)計擁有成百上千列的表通常是一個糟糕的數(shù)據(jù)庫設(shè)計（“寬表”），會導致性能問題和管理困難。考慮使用關(guān)系規(guī)范化（拆分成多個表）。
2. 使用正確的數(shù)據(jù)類型：選擇最合適、最小的數(shù)據(jù)類型。例如，用INT而不是BIGINT（如果值足夠?。?，用VARCHAR(n)而不是總是VARCHAR(255)。
3. 考慮溢出行為：如果必須存儲大量文本或二進制數(shù)據(jù)，了解InnoDB的溢出頁機制。這有助于理解為什么查詢這些大字段可能會更慢（需要額外的磁盤讀取）。
4. 檢查行格式：使用 SHOW TABLE STATUS LIKE ‘table_name’; 來查看表的行格式。現(xiàn)代MySQL版本默認使用DYNAMIC，這是處理寬表或有大字段表的最佳選擇。

總而言之，能創(chuàng)建的列數(shù)量主要取決于這些列的類型和長度，因為它們共同決定了是否會突破“行大小”這個最主要的限制。

行格式

好的，這是一個非常核心的MySQL（特別是InnoDB）概念。理解行格式對于數(shù)據(jù)庫設(shè)計、性能優(yōu)化和存儲效率至關(guān)重要。

什么是行格式？

行格式（Row Format） 指的是數(shù)據(jù)庫表中每一行數(shù)據(jù)在磁盤上存儲時的物理組織結(jié)構(gòu)?？梢园阉胂蟪梢粋€數(shù)據(jù)結(jié)構(gòu)或模板，定義了如何將一行中的各個列值、元數(shù)據(jù)（如事務(wù)ID、回滾指針等）以及索引信息打包并寫入磁盤頁。

不同的行格式采用不同的策略來處理：

1. 數(shù)據(jù)存儲：如何緊湊地存放數(shù)據(jù)以節(jié)省空間。
2. 溢出處理：當遇到非常長的可變長度列（如VARCHAR, TEXT, BLOB）時，如何處理超出頁面大小的部分。
3. 壓縮：是否支持數(shù)據(jù)壓縮以進一步減少磁盤占用。
4. 性能影響：如何影響查詢（特別是SELECT *）和DML（INSERT, UPDATE, DELETE）操作的性能。

InnoDB的行格式

InnoDB是MySQL最常用的存儲引擎，它提供了四種主要的行格式。從MySQL 5.7及以后版本，默認的行格式是 DYNAMIC。

以下是這四種行格式的詳細說明：

1.REDUNDANT（冗余）

• 時代：這是InnoDB最早支持的行格式，為了向后兼容而保留。
• 特點：
  • 存儲效率低：它會存儲一些冗余信息（故名“冗余”），例如字段偏移量列表會存儲兩次。
  • NULL處理：NULL值會在NULL位圖和數(shù)據(jù)區(qū)域中都占用空間（對于可變長度列），效率不高。
  • 溢出處理：對于長于768字節(jié)的可變長度列，它會將前768字節(jié)存儲在數(shù)據(jù)頁中，其余部分存儲在溢出頁中。
  • 沒有壓縮：不支持表壓縮。
• 使用場景：基本不再使用，除非是為了兼容非常老的版本。

2.COMPACT（緊湊）

• 改進：相較于REDUNDANT，它優(yōu)化了存儲結(jié)構(gòu)，減少了大約20%的行空間消耗。
• 特點：
  • 更高效：更緊湊地存儲行數(shù)據(jù)，尤其是對NULL值和可變長度字段的處理更高效。
  • 溢出處理：和REDUNDANT一樣，長可變長度列的前768字節(jié)存儲在數(shù)據(jù)頁中。
  • 沒有壓縮：同樣不支持表壓縮。
• 使用場景：在MySQL 5.6之前是默認格式，現(xiàn)在已被更先進的格式取代。

3.DYNAMIC（動態(tài)）（默認）

• 現(xiàn)代默認選擇：從MySQL 5.7.9及以后版本開始，成為默認的行格式。
• 核心特點：
  • 先進的溢出處理：這是它與COMPACT最大的區(qū)別。對于長可變長度列（如VARCHAR, TEXT, BLOB），DYNAMIC格式幾乎將整個值都存儲在溢出頁中，在主行數(shù)據(jù)中只存儲一個20字節(jié)的指針。這極大地提高了數(shù)據(jù)頁的利用率，避免了因為少數(shù)幾個大字段就填滿整個數(shù)據(jù)頁的情況。
  • 更高效的大字段處理：非常適合包含TEXT、BLOB或超長VARCHAR列的表。
  • 支持壓縮：支持COMPRESSED壓縮（但DYNAMIC本身不壓縮數(shù)據(jù)）。
• 使用場景：絕大多數(shù)現(xiàn)代應(yīng)用的默認推薦選擇，特別是那些可能包含大對象數(shù)據(jù)的表。

4.COMPRESSED（壓縮）

• 特點：
  • 包含所有DYNAMIC特性：同樣使用指針方式處理大字段溢出。
  • 額外數(shù)據(jù)壓縮：使用zlib算法對表和索引數(shù)據(jù)進行壓縮，可以顯著減少磁盤空間占用（通常能減少50%或更多）。
  • CPU開銷：壓縮和解壓操作會帶來額外的CPU計算開銷。
  • 緩沖池效率：因為數(shù)據(jù)在磁盤上是壓縮的，讀入內(nèi)存（緩沖池）后也是壓縮狀態(tài)，這意味著同樣大小的緩沖池可以緩存更多的數(shù)據(jù)頁，可能會提高緩存命中率。
• 使用場景：
  • 磁盤空間非常寶貴（例如SSD雖然快但貴）。
  • 表非常大，且數(shù)據(jù)讀多寫少，CPU資源不是瓶頸。
  • 典型的例子：歸檔數(shù)據(jù)、歷史日志表、只讀或讀多寫少的業(yè)務(wù)數(shù)據(jù)。

如何查看和設(shè)置行格式

查看表的行格式

-- 查看某個表的行格式等信息
SHOW TABLE STATUS LIKE 'your_table_name';
-- 或從信息模式中查詢
SELECT TABLE_NAME, ROW_FORMAT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

設(shè)置行格式

可以在創(chuàng)建表時指定：

CREATE TABLE your_table (
    ...
) ROW_FORMAT=DYNAMIC;

也可以在修改表時變更：

ALTER TABLE your_table ROW_FORMAT=COMPRESSED;

注意：修改現(xiàn)有表的行格式是一個代價較高的操作（會重建表），請在業(yè)務(wù)低峰期進行。

總結(jié)與選擇建議

簡單決策流程：

1. 如果沒有特殊需求，堅持使用默認的 DYNAMIC。
2. 如果表中有很多TEXT或BLOB列，強烈推薦使用 DYNAMIC。
3. 如果需要節(jié)省磁盤空間，并且愿意用CPU資源來交換，同時讀寫壓力不大，可以考慮 COMPRESSED。

到此這篇關(guān)于MySQL中列數(shù)量及長度的限制的文章就介紹到這了,更多相關(guān)mysql列數(shù)量和長度內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論