Too many keys specified; max 64 keys allowed 作者: lovingyu_er 时间: 2020-12-24 18:58:56 分类: MYSQL -view 软件版本: ``` mysqladmin Ver 8.42 Distrib 5.7.26, for Linux on x86_64 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Server version 5.7.26-0ubuntu0.18.10.1 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 26 days 46 min 0 sec ``` 系统平台: ``` Ubuntu 18.10 \n \l ``` 使用PHP在命令行中，给项目的一个表执行```add cloumn```的操作，需要增加了200多个列并增加相应的索引，出现了```Too many keys specified; max 64 keys allowed``` 的提示... <!--more--> 我查看了官方的文档： ####### MYSQL 8.0 对于mysql8.0中，mysql默认每张表支持64个```indexes```,而每个索引可以包含```1~16```列，在Innodb table 中，最大索引支持的width是767字节或者3072字节，对于MyISAM table ,索引支持的最大width上1000 bytes,```An index may use a prefix of a column for CHAR, VARCHAR, BLOB, or TEXT column types.``` ####### MYSQL 5.7 该版本中, ``` 1. The maximum number of indexes per table and the maximum index length is defined per storage engine. 2. All storage engines support at least 16 indexes per table and a total index length of at least 256 bytes. Most storage engines have higher limits. ``` 表使用的是```Innodb```的引擎,该索引的官方说明如下: 1. ```A table can contain a maximum of 1017 columns (raised in MySQL 5.6.9 from the earlier limit of 1000). Virtual generated columns are included in this limit.``` ，Innodb 表最大的列数是1017 2. ```A table can contain a maximum of 64 secondary indexes.```, 最大的二级索引数量是64个 3. ```A maximum of 16 columns is permitted for multicolumn indexes. Exceeding the limit returns an error.``` ，每个索引支持的最大的列数是16个. 何为```secondary indexes``` ？ 就是非主键索引的单列索引: ``` A type of InnoDB index that represents a subset of table columns. An InnoDB table can have zero, one, or many secondary indexes. (Contrast with the clustered index, which is required for each InnoDB table, and stores the data for all the table columns.) A secondary index can be used to satisfy queries that only require values from the indexed columns. For more complex queries, it can be used to identify the relevant rows in the table, which are then retrieved through lookups using the clustered index. Creating and dropping secondary indexes has traditionally involved significant overhead from copying all the data in the InnoDB table. The fast index creation feature makes both CREATE INDEX and DROP INDEX statements much faster for InnoDB secondary indexes. ``` 所以，就会出现标题的提示。 解决方案: 重新编译安装MYSQL，并指定参数: ```./configure --prefix=/usr/local/mysql --with-charset=cp1251 --enable-thread-safe-client --with-max-indexes=256 ``` 建议:整理业务，看看是否有必要需要这么多的索引，我的业务需求分析，发现没有必要这么多的索引。 参考文档: ``` https://forums.mysql.com/read.php?22,53666,262189#msg-262189``` 标签: mysql