mysql索引的最大字节3072_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?

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).

+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| 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 | | |

+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

解决方案

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

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”.

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.

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.

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

The maximum number of columns per index is 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.