Mysql索引過長怎么辦

本文主要給大家介紹Mysql索引過長怎么辦，希望可以給大家補充和更新些知識，如有其它問題需要了解的可以持續在億速云行業資訊里面關注我的更新文章的。

mysql 索引過長1071-max key length is 767 byte
問題
create table: Specified key was too long; max key length is 767 bytes

原因
數據庫表采用utf8編碼，其中varchar(255)的column進行了唯一鍵索引
而mysql默認情況下單個列的索引不能超過767位(不同版本可能存在差異)

于是utf8字符編碼下，255*3 byte 超過限制

解決
1  使用innodb引擎；
2  啟用innodb_large_prefix選項，將約束項擴展至3072byte；
3  重新創建數據庫；

my.cnf配置：
default-storage-engine=INNODB
innodb_large_prefix=on

一般情況下不建議使用這么長的索引，對性能有一定影響；

這是網上的一遍文章的解決辦法，但是我沒有修改成功
下面我參考了一些其他的文章并結合自己的操作一步步去確定問題在哪。

1. 有同學問到InnoDB的索引長度問題，簡單說幾個tips。

    關于3072
   
    大家經常碰到InnoDB單列索引長度不能超過767bytes，實際上聯合索引還有一個限制是3072。

2. 

Sql代碼  收藏代碼
mysql> CREATE TABLE tb (  
->   a varchar(255) DEFAULT NULL,  
->   b varchar(255) DEFAULT NULL,  
->   c varchar(255) DEFAULT NULL,  
->   d varchar(255) DEFAULT NULL,  
->   e varchar(255) DEFAULT NULL,  
->   KEY a (a,b,c,d,e)  
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

可以看到，由于每個字段占用255*3, 因此這個索引的大小是3825>3072，報錯。

為什么3072

     我們知道InnoDB一個page的默認大小是16k。由于是Btree組織，要求葉子節點上一個page至少要包含兩條記錄（否則就退化鏈表了）。

   所以一個記錄最多不能超過8k。
    又由于InnoDB的聚簇索引結構，一個二級索引要包含主鍵索引，因此每個單個索引不能超過4k （極端情況，pk和某個二級索引都達到這個限制）。
     由于需要預留和輔助空間，扣掉后不能超過3500，取個“整數”就是(1024*3)。 

單列索引限制

     上面有提到單列索引限制767，起因是256×3-1。這個3是字符最大占用空間（utf8）。但是在5.5以后，開始支持4個字節的uutf8。255×4>767, 于是增加了一個參數叫做 innodb_large_prefix。

     這個參數默認值是OFF。當改為ON時，允許列索引最大達到3072。
        **   我又參考了上邊這篇文章確定了這個最大3072是可以的，那下面我們找方法把它弄成3072.**

1. 又參考了一篇文章，終于有點眉目了
   創建一張表，其中有個varchar 大字段，并且在這個字段上建索引，結果發現MySQL報錯：
   ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
   以下為建表語句：
   create table piratebay(
   SYS_ID      int ,
   FILE_NAME   VARCHAR(200),
   FILE_ID     VARCHAR(30),
   NUM1        VARCHAR(30),
   NUM2        VARCHAR(30),
   MAGNET_LINK VARCHAR(500),
   PRIMARY KEY (sys_id),
   KEY         piratebay_n1 (FILE_NAME))
   engine=innodb;

MySQL 環境配置：
Server version: 5.6.28-log MySQL Community Server (GPL)

Server characterset: utf8mb4
Db       characterset: utf8mb4

解決辦法：

（1）查看相關配置并作出如下設置

innodb_large_prefix = ON
innodb_file_format = Barracuda
innodb_file_per_table = ON

（2）修改建表語句，加入 row_format=DYNAMIC

create table piratebay(
SYS_ID      int ,
FILE_NAME   VARCHAR(200),
FILE_ID     VARCHAR(30),
NUM1        VARCHAR(30),
NUM2        VARCHAR(30),
MAGNET_LINK VARCHAR(500),
PRIMARY KEY (sys_id),
KEY         piratebay_n1 (FILE_NAME))
engine=innodb row_format=dynamic;

原因：

   MySQL 索引只支持767個字節，utf8mb4 每個字符占用4個字節，所以索引最大長度只能為191個字符，即varchar(191)，若想要使用更大的字段，mysql需要設置成支持數據壓縮，并且修改表屬性 row_format ={DYNAMIC|COMPRESSED}

         大家看明白了吧，吧row_formatl類型修改為這兩種模式。

下面是我做的過程圖：

可以看到row_formatl類型，下面修改類型

CREATE TABLE test2 ( id int(11) NOT NULL AUTO_INCREMENT, date varchar(25) DEFAULT NULL, sess_id varchar(255) DEFAULT NULL, keyword varchar(25) NOT NULL, url_n varchar(3) DEFAULT NULL, s_n varchar(3) DEFAULT NULL, select_url varchar(255) DEFAULT NULL, UNIQUE KEY (id,keyword) ) ENGINE=innodb DEFAULT row_format=dynamic;

看了以上關于Mysql索引過長怎么辦，希望能給大家在實際運用中帶來一定的幫助。本文由于篇幅有限，難免會有不足和需要補充的地方，如有需要更加專業的解答，可在官網聯系我們的24小時售前售后，隨時幫您解答問題的。