MySQL varchar 索引长度

我有一个这样的表：

CREATE TABLE `products` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(512) NOT NULL,
  `description` text,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8;

和这样的一个：

CREATE TABLE `product_variants` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(11) unsigned NOT NULL,
  `product_code` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_code` (`product_code`),
  KEY `product_variant_product_fk` (`product_id`),
  CONSTRAINT `product_variant_product_fk` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1037 DEFAULT CHARSET=utf8;

和这样的 SQL 语句

SELECT p.id AS id, p.name AS name, p.description AS description, pv.id AS product_variant_id, pv.product_code AS product_code
FROM products p
INNER JOIN product_variants pv ON pv.product_id = p.id
ORDER BY p.name ASC
LIMIT 300 OFFSET 0;

如果我解释的话会给我这个：

+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
| id | select_type | table | type | possible_keys              | key                        | key_len | ref     | rows   | Extra          |
+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
|  1 | SIMPLE      | p     | ALL  | PRIMARY                    | NULL                       | NULL    | NULL    | 993658 | Using filesort |
|  1 | SIMPLE      | pv    | ref  | product_variant_product_fk | product_variant_product_fk | 4       | db.p.id |      1 |                |
+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
2 rows in set (0.00 sec)

对于一百万行来说，这相当慢。我尝试过添加索引 产品名称：

ALTER TABLE products ADD INDEX `product_name_idx` (name(512));

这给出了：

mysql> show indexes from products;
+----------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name         | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| products |          0 | PRIMARY          |            1 | id              | A         |      993658 |     NULL | NULL   |      | BTREE      |         |               |
| products |          1 | product_manf_fk  |            1 | manufacturer_id | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |               |
| products |          1 | product_name_idx |            1 | name            | A         |         201 |      255 | NULL   |      | BTREE      |         |               |
+----------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

我认为 Sub_part 列显示了已经存在的前缀 索引（以字节为单位），如所述这一页.

当我重新解释查询时，我得到：

+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
| id | select_type | table | type | possible_keys              | key                        | key_len | ref     | rows   | Extra          |
+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
|  1 | SIMPLE      | p     | ALL  | PRIMARY                    | NULL                       | NULL    | NULL    | 993658 | Using filesort |
|  1 | SIMPLE      | pv    | ref  | product_variant_product_fk | product_variant_product_fk | 4       | db.p.id |      1 |                |
+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
2 rows in set (0.00 sec)

看起来新索引没有被使用。如上所述这一页，如果索引是，则不会用于排序 前缀索引。事实上，如果我用以下方法截断数据：

alter table products modify `name`  varchar(255) not null;

解释给出：

+----+-------------+-------+-------+----------------------------+----------------------------+---------+----------------------------------------------+------+-------+
| id | select_type | table | type  | possible_keys              | key                        | key_len | ref                                          | rows | Extra |
+----+-------------+-------+-------+----------------------------+----------------------------+---------+----------------------------------------------+------+-------+
|  1 | SIMPLE      | p     | index | PRIMARY                    | product_name_idx           | 767     | NULL                                         |  300 |       |
|  1 | SIMPLE      | pv    | ref   | product_variant_product_fk | product_variant_product_fk | 4       | oh_2c98c233_69fe_4f06_ad0d_fe6f85a5beac.p.id |    1 |       |
+----+-------------+-------+-------+----------------------------+----------------------------+---------+----------------------------------------------+------+-------+

我认为这支持了这一点。然而，它说这一页那 InnoDB 表最多可以有 767 字节的索引。如果长度在 字节，为什么它拒绝超过255？如果它在 字符，它是如何决定每个UTF-8字符的长度的？是 它只是假设3？

另外，我正在使用这个版本的 MySQL：

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.27-log |
+------------+
1 row in set (0.00 sec)

2021 年 9 月编辑： 我已经使用 MySQL 8.0 几年了，所以这里有一些更新的信息。

MySQL手册现在有信息丰富的页面关于之间的转换utf8mb3（目前也称为utf8) and utf8mb4. utf8mb3 已弃用并将被删除最终;当它被删除时，它当前的别名，utf8, 将参考utf8mb4反而。

已弃用utf8mb3，您可以在索引中存储最多 255 个字符，同时使用utf8mb4，使用时最多 191COMPACT or REDUNDANT行格式。

With COMPRESSED or DYNAMICrow 格式，索引键前缀最多可达 3072 字节。使用它们，您可以索引最多 1024 个字符utf8mb3，以及 768 个字符utf8mb4.

以下是我之前的回答，它解释了数字背后的一些逻辑人物你可以索引与数量bytes.

---

由于我的研究，我必须修改我的答案。我最初发布了这个（引用我自己）：

我相信答案是你无法知道有多少个字符 位于索引中，因为您无法知道字符有多少字节 将会（除非您执行某些操作来排除多字节字符）。

我不确定，但它可能仍然是正确的，但不是我想的那样。

这是正确的答案：

MySQL 假定每个 utf8 字符占 3 个字节。 255 个字符是您可以为每列指定的最大索引大小，因为 256x3=768，这打破了 767 字节的限制。

如果您不指定索引大小，MySQL 将选择最大大小（即每列 255）。 不能对长度大于 255 的 utf8 列设置 UNIQUE 约束，因为唯一索引必须包含整个单元格值。但可以使用常规索引 - 它只会索引前 255 个字符（或前 767 个字节？）。这对我来说仍然是个谜。

谜： 我明白为什么 MySQL 出于安全考虑假设每个字符 3 个字节，因为否则 UNIQUE 约束可能会被破坏。但文档似乎表明索引的大小实际上以字节为单位，而不是字符。所以，假设你输入了 255varchar(25 上的 char (765 字节) 索引6） 柱子。如果您存储的字符都是 ASCII、1 字节字符，例如 A-Z、a-z、0-9，那么您可以将整个列放入 767 字节索引中。看起来这就是实际发生的事情。

下面是我原来的答案中有关字符、字节等的更多信息。

---

根据维基百科, UTF-8 字符的长度可以是 1、2、3 或 4 个字节。 但是，根据这个 mysql 文档，最大字符大小为 3 个字节，因此任何超过 255 个字符的列索引都可能达到该字节限制。但据我了解，可能不是。如果大部分字符都在 ASCII 范围内，那么平均字符大小将接近 1 个字节。例如，如果您的平均字符大小为 1.3 字节（大部分为 1 字节，但也有大量 2-3 字节字符），则您可以指定索引为 767/1.3

因此，如果您主要存储 1 字节字符，则实际字符限制将更像是： 767 / 1.3 = 590。但事实证明，事实并非如此。 255 个字符是限制。

正如中提到的这个 MySQL 文档,

前缀限制以字节为单位，而前缀长度以字节为单位 CREATE INDEX 语句被解释为字符数 非二进制数据类型（CHAR、VARCHAR、TEXT）。考虑到这一点 为使用多字节的列指定前缀长度时 字符集。

MySQL 似乎建议人们像我刚才那样进行计算/猜测，以确定 varchar 列的键大小。但事实上你cannot为 utf8 列指定大于 255 的索引。

最后，如果你再次参考我的第二个链接，还有这个：

当启用 innodb_large_prefix 配置选项时，这 对于使用的 InnoDB 表，长度限制提高到 3072 字节 动态和压缩行格式。

因此，如果您愿意的话，通过一些调整，您似乎可以获得更大的索引。只需确保行格式是动态的或压缩的。在这种情况下，您可以指定 1023 或 1024 个字符的索引。

---

By the way, it turns out that you can store 4-byte characters using [the utf8mb4 character set][4]. The utf8 character set apparently stores only ["plane 0" characters][5].

EDIT:

我只是尝试在 varchar(511) 列上使用 tinyint(1) 列创建复合索引，并收到错误消息，指出最大索引大小为 767 字节。这让我相信 MySQL 假设 utf8 字符集列将包含每个字符 3 个字节（最大值），并允许您最多使用 255 个字符。但也许这仅适用于复合索引。当我了解更多信息时，我会更新我的答案。但现在我将其保留为编辑。