MySQL中UNIQUE索引的最大大小

Is there a max size of a UNIQUE index in MySQL? I have six dimensions in my index, which leads to a massive index. Is there a cardinality limit to UNIQUE index?

MySQL中是否有最大的UNIQUE索引？我的索引中有六个维度，这导致了一个庞大的索引。 UNIQUE索引有基数限制吗？

I am getting weird unexplained errors and wonder if there is a limit from the UNIQUE. Could it come from a uniqueness limit? Here is what it looks like (I also don't understand why the cardinality of the last three columns does not increase).

我得到了奇怪的无法解释的错误，并想知道UNIQUE是否有限制。它可能来自唯一性限制吗？这是它的样子（我也不明白为什么最后三列的基数不会增加）。

+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table |          0 | PRIMARY  |            1 | column_1      | A         |          15 |     NULL | NULL   |      | BTREE      |         |               |
| table |          0 | PRIMARY  |            2 | column_2      | A         |       91948 |     NULL | NULL   |      | BTREE      |         |               |
| table |          0 | PRIMARY  |            3 | column_3      | A         |      924889 |     NULL | NULL   |      | BTREE      |         |               |
| table |          0 | PRIMARY  |            4 | column_4      | A         |    15723114 |     NULL | NULL   |      | BTREE      |         |               |
| table |          0 | PRIMARY  |            5 | column_5      | A         |    15723114 |     NULL | NULL   |      | BTREE      |         |               |
| table |          0 | PRIMARY  |            6 | column_6      | A         |    15723114 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1 个解决方案

#1

For InnoDB tables, the limit is 3072 bytes across all indexed columns, presumably taking only the first 767 bytes of each column.

对于InnoDB表，所有索引列的限制为3072字节，可能只占每列的前767个字节。

An index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. See Section 13.1.13, “CREATE INDEX Syntax”.

单列索引的索引键最多可达767个字节。相同的长度限制适用于任何索引键前缀。请参见第13.1.13节“CREATE INDEX语法”。

The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. This limit applies to the length of the combined index key in a multi-column index.

InnoDB内部最大密钥长度为3500字节，但MySQL本身将其限制为3072字节。此限制适用于多列索引中组合索引键的长度。

http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html

So, based strictly on the documentation, I'd say you could have up to 1000 columns in your index (the max number of columns on an InnoDB table), provided that the average size of each is 3 bytes or less.

因此，严格根据文档，我会说您的索引中最多可以有1000列（InnoDB表上的最大列数），前提是每个列的平均大小为3个字节或更少。

For MyISAM tables, it's the lesser of 16 columns or 1000 bytes.

对于MyISAM表，它是16列或1000字节中的较小者。

The maximum number of columns per index is 16.

每个索引的最大列数为16。

The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.

最大密钥长度为1000个字节。这也可以通过更改源和重新编译来更改。对于长度大于250字节的密钥的情况，使用比默认值1024字节更大的密钥块大小。

http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html

#1