一、案例分享

1.1 問題描述

以下一例報錯是開發同學通過架構初始化建立一些表結構時出現的報錯，我們需要重點關注“1071 Specified key was too long; max key length is 7671071 Specified key was too long; max key length is 767 bytes”這個提示。該報錯告訴我們索引長度超過的額767，因過長而無法建立索引。這也是為什麼經常在一些MySQL的SQL審批中，DBA同學會經常要求某個表的字段長度盡量不要超過191、某些表的字段長度不要超過255。

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes in /var/www/html/includes/vendor/aura/sql/src/ExtendedPdo.php:748 Stack trace: #0 /var/www/html/includes/vendor/aura/sql/src/ExtendedPdo.php(748): PDOStatement->execute() #1 /var/www/html/includes/functions-install.php(252): Aura\Sql\ExtendedPdo->perform('CREATE TABLE IF...') #2 /var/www/html/admin/install.php(46): yourls_create_sql_tables() #3 {main} thrown in /var/www/html/includes/vendor/aura/sql/src/ExtendedPdo.php on line 748

MySQL中索引長度主要受什麼的限制呢？是否可以把767的限制進行放寬？

1.2 問題處理

1、檢視資料庫innodb_file_format、innodb_large_prefix參數

innodb_file_format=Barracuda
innodb_large_prefix=OFF

2、檢視innodb_default_row_format參數

innodb_default_row_format=Compact
由于業務方使用的MySQL是5.6版本，所有表預設ROW_FORMAT=Compact。

3、通過以上3個參數，況基本明了，目前的解決辦法有3種：

1.改表結構，字段長度可以縮小的話就縮小，utf8255,utf8mbe191.
2.建表語句添加ROW_FORMAT=DYNAMIC
3.線上使用5.7版本的資料庫

二、MySQL字首索引上的一些限制

2.1 Redundant

1、row_format=redundant要求innodb_file_format=Barracuda/Antelope。

2、該行模式下，對于可變長字段，innodb會存儲其起始的768位元組在B-tree的節點中，超出部分存儲在溢出頁中。對于長度小于768位元組的字段，其資訊全部存儲在B-tree節點中。這對于長度較小的大字段來說是比較有利的，可以減小資料查詢時的IO消耗。若表有有較多的大字段，就會造成b-tree中存儲大量的資料，每頁存儲的行數也就相應的變少，進而導緻我們索引查詢效率變低。

3、redundant行模式下，字段索引長度需小于768位元組，不支援對字首索引長度的擴大。對于utf8字元集格式的字段，每3個位元組為一個字元；對于utf8mb4字元集格式的字段，每4個位元組為一個字元；對于latin1字元集格式的字段，每1個位元組為一個字元

+--------------+---------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| table_schema | table_name          | engine | row_format | CHARACTER_SET_NAME | COLUMN_NAME | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+--------------+---------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| db1          | t_redundant_latin1  | InnoDB | Redundant  | latin1             | name        |                      768 |                    768 |
| db1          | t_redundant_utf8    | InnoDB | Redundant  | utf8               | name        |                      256 |                    768 |
| db1          | t_redundant_utf8mb4 | InnoDB | Redundant  | utf8mb4            | name        |                      192 |                    768 |
+--------------+---------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
sansi@mysql 11:41:  [db1]> alter table t_redundant_utf8 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
sansi@mysql 11:41:  [db1]> alter table t_redundant_utf8mb4 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
sansi@mysql 12:09:  [db1]> alter table t_redundant_latin1 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes


+--------------+---------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| table_schema | table_name          | engine | row_format | CHARACTER_SET_NAME | COLUMN_NAME | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+--------------+---------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| db1          | t_redundant_latin1  | InnoDB | Redundant  | latin1             | name        |                      767 |                    767 |
| db1          | t_redundant_utf8    | InnoDB | Redundant  | utf8               | name        |                      255 |                    765 |
| db1          | t_redundant_utf8mb4 | InnoDB | Redundant  | utf8mb4            | name        |                      191 |                    764 |
+--------------+---------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
sansi@mysql 11:43:  [db1]> alter table t_redundant_utf8 add index idx_name(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
sansi@mysql 11:43:  [db1]> alter table t_redundant_utf8mb4 add index idx_name(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
sansi@mysql 12:15:  [db1]> alter table t_redundant_latin1 add index idx_name(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.2 compact

1、compact行模式相對于redundant來說可以節省20%的空間，但是會增加一些CPU的消耗。MySQL5.6的預設行模式，row_format=compact要求innodb_file_format=Barracuda/Antelope。

3、compact行模式下，字段索引長度需小于768位元組，不支援對字首索引長度的擴大。對于utf8字元集格式的字段，每3個位元組為一個字元；對于utf8mb4字元集格式的字段，每4個位元組為一個字元；對于latin1字元集格式的字段，每1個位元組為一個字元

+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| table_schema | table_name        | engine | row_format | CHARACTER_SET_NAME | COLUMN_NAME | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| db1          | t_compact_latin1  | InnoDB | Compact    | latin1             | name        |                      768 |                    768 |
| db1          | t_compact_utf8    | InnoDB | Compact    | utf8               | name        |                      256 |                    768 |
| db1          | t_compact_utf8mb4 | InnoDB | Compact    | utf8mb4            | name        |                      192 |                    768 |
+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
sansi@mysql 12:26:  [db1]> alter table t_compact_latin1 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
sansi@mysql 12:26:  [db1]> alter table t_compact_utf8 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
sansi@mysql 12:26:  [db1]> alter table t_compact_utf8mb4 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes


+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| table_schema | table_name        | engine | row_format | CHARACTER_SET_NAME | COLUMN_NAME | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| db1          | t_compact_latin1  | InnoDB | Compact    | latin1             | name        |                      767 |                    767 |
| db1          | t_compact_utf8    | InnoDB | Compact    | utf8               | name        |                      255 |                    765 |
| db1          | t_compact_utf8mb4 | InnoDB | Compact    | utf8mb4            | name        |                      191 |                    764 |
+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
sansi@mysql 12:24:  [db1]> alter table t_compact_latin1 add index idx_name(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
sansi@mysql 12:25:  [db1]> alter table t_compact_utf8 add index idx_name(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
sansi@mysql 12:25:  [db1]> alter table t_compact_utf8mb4 add index idx_name(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.3 dynamic

1、dynamic在空間存儲上與compact格式相同，額外增加的一個特性是針對可變長度的字段的溢出頁存儲技術，dynamic模式下使用的是完全溢出頁存儲。row_format=dynamic是MySQL5.7的預設值。要求innodb_file_format=Barracuda

2、該行模式下，使用完全溢出頁進行存儲，其聚集索引僅僅儲存一個20位元組的指針指向溢出頁。其存儲是否需要使用溢出頁取決于資料頁大小和行大小，較短的字段位元組存儲在b-tree中，較長的字段将整個值存儲在溢出頁，并使用指針進行關聯。

3、dynamic行模式下，可以将索引字首限制由之前的768提升至3072。對于utf8字元集格式的字段，每3個位元組為一個字元；對于utf8mb4字元集格式的字段，每4個位元組為一個字元；對于latin1字元集格式的字段，每1個位元組為一個字元

1）滿足索引字首提升至3072需要關注以下幾個參數

innodb_file_format=Barracuda
row_format=dynamic
innodb_file_per_table=on
innodb_large_prefix=on

2）示例

+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| table_schema | table_name        | engine | row_format | CHARACTER_SET_NAME | COLUMN_NAME | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| db1          | t_dynamic_latin1  | InnoDB | Dynamic    | latin1             | name        |                     3073 |                   3073 |
| db1          | t_dynamic_utf8    | InnoDB | Dynamic    | utf8               | name        |                     1025 |                   3075 |
| db1          | t_dynamic_utf8mb4 | InnoDB | Dynamic    | utf8mb4            | name        |                      769 |                   3076 |
+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
3 rows in set (0.03 sec)
sansi@mysql 15:20:  [db1]> alter table t_dynamic_utf8 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
sansi@mysql 15:20:  [db1]> alter table t_dynamic_utf8mb4 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
sansi@mysql 15:20:  [db1]> alter table t_dynamic_latin1 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| table_schema | table_name        | engine | row_format | CHARACTER_SET_NAME | COLUMN_NAME | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| db1          | t_dynamic_latin1  | InnoDB | Dynamic    | latin1             | name        |                     3072 |                   3072 |
| db1          | t_dynamic_utf8    | InnoDB | Dynamic    | utf8               | name        |                     1024 |                   3072 |
| db1          | t_dynamic_utf8mb4 | InnoDB | Dynamic    | utf8mb4            | name        |                      768 |                   3072 |
+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+

sansi@mysql 15:17:  [db1]> alter table t_dynamic_latin1 add index idx_name(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
sansi@mysql 15:18:  [db1]> alter table t_dynamic_utf8mb4 add index idx_name(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
sansi@mysql 15:19:  [db1]> alter table t_dynamic_utf8 add index idx_name(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.4 compressed

1、compressed是在dynamic的基礎上，額外增強了對索引和資料的壓縮，row_format=comPRESSED要求innodb_file_format=Barracuda

innodb_file_format=Barracuda
row_format=dynamic
innodb_file_per_table=on
innodb_large_prefix=on

+--------------+----------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| table_schema | table_name           | engine | row_format | CHARACTER_SET_NAME | COLUMN_NAME | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+--------------+----------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| db1          | t_compressed_latin1  | InnoDB | Compressed | latin1             | name        |                     3073 |                   3073 |
| db1          | t_compressed_utf8    | InnoDB | Compressed | utf8               | name        |                     1025 |                   3075 |
| db1          | t_compressed_utf8mb4 | InnoDB | Compressed | utf8mb4            | name        |                      769 |                   3076 |
+--------------+----------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
sansi@mysql 15:33:  [db1]> alter table t_compressed_utf8mb4 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
sansi@mysql 15:35:  [db1]> alter table t_compressed_utf8 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
sansi@mysql 15:35:  [db1]> alter table t_compressed_latin1 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

+--------------+----------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| table_schema | table_name           | engine | row_format | CHARACTER_SET_NAME | COLUMN_NAME | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+--------------+----------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| db1          | t_compressed_latin1  | InnoDB | Compressed | latin1             | name        |                     3071 |                   3071 |
| db1          | t_compressed_utf8    | InnoDB | Compressed | utf8               | name        |                     1024 |                   3072 |
| db1          | t_compressed_utf8mb4 | InnoDB | Compressed | utf8mb4            | name        |                      768 |                   3072 |
+--------------+----------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
sansi@mysql 15:36:  [db1]>
sansi@mysql 15:36:  [db1]> alter table t_compressed_utf8mb4 add index idx_name(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
sansi@mysql 15:36:  [db1]> alter table t_compressed_utf8 modify name varchar(1024);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
sansi@mysql 15:36:  [db1]> alter table t_compressed_latin1 modify name varchar(3071);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

三、MySQL表字段上的一些限制

3.1、列與資料類型間的限制

1） char

char為定長字段，可存儲長度為0～255，當sql_mode為STRICT_TRANS_TABLES時，N大于255時直接傳回報錯

sansi@mysql 15:46:  [db1]> create table t1 (c1 char(256));
ERROR 1074 (42000): Column length too big for column 'c1' (max = 255); use BLOB or TEXT instead

2） varchar

varchar為可變長字段，可存儲的長度為0～65535位元組，N具體大小限制需要根據列字元集格式進行判斷:lantin1=1位元組，utf8=3位元組,utf8mb4=4位元組。

65535/3=21845
sansi@mysql 15:46:  [db1]> create table t1 (c1 varchar(65535) not null) character set utf8;
ERROR 1074 (42000): Column length too big for column 'c1' (max = 21845); use BLOB or TEXT instead   
65535/4=16383
sansi@mysql 15:47:  [db1]> create table t1 (c1 varchar(65535) not null) character set utf8mb4;
ERROR 1074 (42000): Column length too big for column 'c1' (max = 16383); use BLOB or TEXT instead
65533 + 2
sansi@mysql 15:47:  [db1]> create table t1 (c1 varchar(65533) not null) character set latin1;
Query OK, 0 rows affected (0.02 sec)

3.2、列總數限制

MySQL表中所有的列總和不能超過4096，當然一些别的條件的限制下，表的列總數一般不會達到該限制條件

1）受到表的最大row size的限制

2）存儲引擎的限制

3）每個表的.frm檔案的限制

3.3、row size限制

1）MySQL預設一個表的row size最大為65535,即使存儲引擎支援更大的row size。

sansi@mysql 15:49:  [db1]> 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

sansi@mysql 15:49:  [db1]> 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=MyISAM 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

2）bolb和text列僅占row size的9～12 bytes，因為blob和text使用off-page進行存儲。對于Innodb存儲引擎來講，可變長列的存儲超過767 bytes是利用溢出頁來存儲的。

sansi@mysql 15:49:  [db1]>  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=MyISAM CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec)

sansi@mysql 15:53:  [db1]>  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.03 sec)

對于可變長列的存儲，Innodb除了行記錄的存儲外，還需要額外的空間來存儲該行記錄的實際大小。767 bytes以下隻需一個位元組，767 bytes以上需要兩個位元組來存儲。

1、c1列 65535 + 2 = 65537 > 65535    執行報錯
sansi@mysql 15:59:  [db1]> CREATE TABLE t1
    -> (c1 VARCHAR(65535) NOT NULL)
    -> 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

2、c1列 65533 + 2 = 65535 <= 65535    執行成功
sansi@mysql 15:59:  [db1]> CREATE TABLE t1
    -> (c1 VARCHAR(65533) NOT NULL)
    -> ENGINE = InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.03 sec)

MySQL字首索引上限案例分析一、案例分享二、MySQL字首索引上的一些限制三、MySQL表字段上的一些限制

一、案例分享

1.1 問題描述

1.2 問題處理

1、檢視資料庫innodb_file_format、innodb_large_prefix參數

2、檢視innodb_default_row_format參數

3、通過以上3個參數，況基本明了，目前的解決辦法有3種：

二、MySQL字首索引上的一些限制

2.1 Redundant

2.2 compact

2.3 dynamic

2.4 compressed

三、MySQL表字段上的一些限制

3.1、列與資料類型間的限制

1） char

2） varchar

3.2、列總數限制

3.3、row size限制

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述