1、 背景

在新創(chuàng)建mysql數(shù)據(jù)表的時候。不太確定表能創(chuàng)建多少個字段，多少個索引。索引多少有限制么？mysql的數(shù)據(jù)是怎么存儲的存在在哪里。

2、基本個數(shù)限制

• 在MySQL5.6.9以后的版本，一個表的最大列個數(shù)（包含虛擬列，虛擬列是MySQL5.7的新特性）為1017，在之前的版本是1000
• 一個表的最大索引數(shù)量（非主鍵索引）為64個
• 復(fù)合索引最多可以包括16個列，超過會報錯：ERROR 1070 (42000): Too many key parts specified; max 16 parts allowed

3、索引字段大小限制

• 關(guān)于innodb_large_prefix這個配置的限制： show variables like 'innodb_large_prefix' ; -- on
  • 對于MySQL5.7.7之前，這個值默認(rèn)是False，之后（包含5.7.7），這個值默認(rèn)為True
  • 對于使用DYNAMIC或者COMPRESSED的Row Format，并且innodb_large_prefix為True，最大所以字段前綴的限制大小是3072 bytes（也就是字段的前3072bytes才會被索引）。如果這個配置是false，最大是767bytes。如果索引的字段，總長度超過這個限制，建表或者修改表就會報錯。
  • 如果更改了配置或者行格式，導(dǎo)致最大限制從3072 bytes變?yōu)?67bytes。對于現(xiàn)有的表這個限制是會生效的，但是已有的索引不會受影響，就是不能新建而已。
  • 這個配置未來過期掉，也就是說，在之后的版本中，默認(rèn)索引字段前綴最大值為3072Bytes（不考慮Row Format）

4、innodb_page_size

• 關(guān)于innodb_page_size這個數(shù)據(jù)庫實(shí)例初始化配置： show VARIABLES like 'innodb_page_size' ; -- 16384
  • 如果在創(chuàng)建數(shù)據(jù)庫實(shí)例的時候修改了innodb_page_size這個參數(shù)（默認(rèn)16KB），那么字段前綴的限制大小是3072 bytes這個限制也會改變。3072bytes對應(yīng)16KB的innodb_page_size，1563bytes對應(yīng)8KB，767對應(yīng)4KB
  • innodb_page_size是一個初始化數(shù)據(jù)庫實(shí)例的參數(shù)，在目前的版本中（>=5.7.6），可以選擇的值有4096, 8192, 16384, 32768, 65536。默認(rèn)是16KB

一般越小，內(nèi)存劃分粒度越大，使用率越高，但是會有其他問題，就是限制了索引字段還有整行的大小。innodb引擎讀取內(nèi)存還有更新都是一頁一頁更新的，這個innodb_page_size決定了，一個基本頁的大小。常用B+Tree索引，B+樹是為磁盤及其他存儲輔助設(shè)備而設(shè)計一種平衡查找樹（不是二叉樹）。B+樹中，所有記錄的節(jié)點(diǎn)按大小順序存放在同一層的葉子節(jié)點(diǎn)中，各葉子節(jié)點(diǎn)用指針進(jìn)行連接。MySQL將每個葉子節(jié)點(diǎn)的大小設(shè)置為一個頁的整數(shù)倍，利用磁盤的預(yù)讀機(jī)制，能有效減少磁盤I/O次數(shù)，提高查詢效率。 如果一個行數(shù)據(jù)，超過了一頁的一半，那么一個頁只能容納一條記錄，這樣B+Tree在不理想的情況下就變成了雙向鏈表。

這也是為什么要限制住，每一行的大小，也就是下面要說的行長度與行大小限制

5、行長度與行大小限制

• 行長度（Row Length，就是一個表去掉可變長度的類型字段 (VARBINARY, VARCHAR, BLOB and TEXT)，定長字段的占用空間大小），這個行長度是一行數(shù)據(jù)至少要占用的長度。上一節(jié)提到了這個長度最好不要超過innodb_page_size的一半。對于LONGBLOB還有LONGTEXT字段，長度不能超過4GB，包含所有字段的總長度，不能超過4GB。InnoDB 表數(shù)據(jù)行（數(shù)據(jù)庫頁本地存儲的數(shù)據(jù)）的最大大小略小于 innodb_page_size（4KB、8KB、16KB 以及 32KB）的一半。例如，對于默認(rèn)的 16KB 頁大小配置，數(shù)據(jù)行的最大大小為略少于 8KB。對于 64KB 數(shù)據(jù)頁，最大的數(shù)據(jù)行大小略小于 16KB。
  如果一行中的變長字段超過了 InnoDB 數(shù)據(jù)行大小限制，InnoDB 會使用頁外（off-page）存儲的方式保存某些變長字段，直到數(shù)據(jù)行能夠滿足 InnoDB 數(shù)據(jù)行大小限制。對于頁外存儲的變長字段，本地存儲的數(shù)據(jù)內(nèi)容取決于數(shù)據(jù)行的格式，詳細(xì)信息可以參考“InnoDB 數(shù)據(jù)行格式”。

• 長大?。≧ow Size，這個是MySQL的限制，不是InnoDB的）限制。雖然InnoDB支持長度不超過4GB，但是MySQL限制了默認(rèn)所有column（不包括TEXT和BLOB，因為不和數(shù)據(jù)記錄存儲在一起）占用空間不能超過65535

• 不同存儲引擎使用不同的頁頭和尾部數(shù)據(jù)，從而會影響到數(shù)據(jù)行實(shí)際可用的存儲空間。

• 示例過程：

  CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
         c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
         f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;
  ERROR 1118 (42000): Row size too large. The maximum row size for the used
  table type, not counting BLOBs, is 65535. This includes storage overhead,
  check the manual. You have to change some columns to TEXT or BLOBs
  

  InnoDB 表可以創(chuàng)建成功是因為將字段修改為 TEXT 可以避免超過 65535 字節(jié)的限制，同時 InnoDB 頁外存儲可以避免超過 InnoDB 數(shù)據(jù)行大小的限制。

  CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
         c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
         f VARCHAR(10000), g TEXT(6000)) ENGINE=InnoDB CHARACTER SET latin1;
  Query OK, 0 rows affected (0.02 sec)
  

  變長字段的存儲包含了長度信息，這個內(nèi)容也會被計算到數(shù)據(jù)行大小中。例如，一個 VARCHAR(255) CHARACTER SET utf8mb3 字段需要使用 2 個字節(jié)存儲數(shù)據(jù)的長度，因此每個數(shù)值最多可能占用 767 個字節(jié)。

  以下語句能夠成功創(chuàng)建表 t1，因為它的字段需要 32765 + 2 字節(jié)加上 32766 + 2 字節(jié)，能夠滿足 65535 字節(jié)的限制：

  CREATE TABLE t1
         (c1 VARCHAR(32765) NOT NULL, c2 VARCHAR(32766) NOT NULL)
         ENGINE = InnoDB CHARACTER SET latin1;
  Query OK, 0 rows affected (0.02 sec)
  

  以下語句創(chuàng)建表 t2 失敗，因為雖然字段的長度沒有超過 65535 字節(jié)的限制，但是增加 2 個記錄長度的字節(jié)之后超過了該限制.

  改成65535之后就成功了

  CREATE TABLE t2(c1 VARCHAR(65535) NOT NULL)
  ENGINE = InnoDB CHARACTER SET latin1;
  [Err] 1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
  
  CREATE TABLE t2(c1 VARCHAR(65533) NOT NULL)
         ENGINE = InnoDB CHARACTER SET latin1;
  Query OK, 0 rows affected (0.01 sec)
  

  InnoDB 限制行大?。〝?shù)據(jù)庫頁內(nèi)存儲的本地數(shù)據(jù)）為略小于數(shù)據(jù)庫頁的一半。以下語句失敗的原因是全部字段長度超過了一個 InnoDB 頁 16 KB 的數(shù)據(jù)行大小限制。

  CREATE TABLE t4 (
         c1 CHAR(255),c2 CHAR(255),c3 CHAR(255),
         c4 CHAR(255),c5 CHAR(255),c6 CHAR(255),
         c7 CHAR(255),c8 CHAR(255),c9 CHAR(255),
         c10 CHAR(255),c11 CHAR(255),c12 CHAR(255),
         c13 CHAR(255),c14 CHAR(255),c15 CHAR(255),
         c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
         c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
         c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
         c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
         c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
         c31 CHAR(255),c32 CHAR(255),c33 CHAR(255)
         ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET latin1;
  ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help.
  In current row format, BLOB prefix of 0 bytes is stored inline.
  

  • 官方給出的解決方式：

    • 優(yōu)化表結(jié)構(gòu)。變長字段設(shè)置是否合理，變長字段過長建議更改為blob或是text

    • 垂直表字段拆分或者大字段合并（大字段最多不超過768，業(yè)務(wù)進(jìn)行合并＋拆分），divide your table into small ones. If one table contain more than 10 text colums, and the data contain is a little bit long. this error will be thrown out.

    • 修改表的存儲引擎，modify InnoDB to MyISAM.

    • innodb的Barracuda(梭魚)存儲格式。這種格式對blob字段的處理方式是在page里面只存儲一個20byte大小的指針，其他完全存在溢出區(qū)，所以輕易不會超過8K.修改row_format為COMPRESSED或者DYNAMIC，當(dāng)然前提需保證innodb_file_format =Barracuda

      如何修改方法：
      1、修改mysql配置文件my.cnf

      innodb_file_per_table
      innodb_file_format = Barracuda
      

      2、修改造成這個問題的表屬性

      ALTER TABLE $TABLE
      ENGINE=InnoDB
      ROW_FORMAT=COMPRESSED 
      KEY_BLOCK_SIZE=8;
      

      3、重啟mysql服務(wù)
      4、通過寫sql語句修改mysql環(huán)境配置，可以免重啟服務(wù)。當(dāng)然，配置文件中也必須配置上面兩個參數(shù)，這個操作只是避免此次重啟mysql服務(wù)

      set global innodb_file_per_table =ON;
      set global innodb_file_format = barracuda;
      

6、文件大小限制

• InnoDB所有日志文件加在一起不能超過512GB
• 表空間（tableSpace）最小微微大于10MB，最大由innodb_page_size決定：

參考：

https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_page_size

https://www.cnblogs.com/zhiqian-ali/p/5037317.html

本文由博客一文多發(fā)平臺 OpenWrite 發(fā)布！