MySQL varchar索引长度

Question

我有一张这样的桌子：

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)

对于一百万行，这非常慢。我尝试使用以下方法在products.name上添加索引：

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)

Answer 1

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

我相信答案是您不知道索引中将包含多少个字符，因为您不知道您的字符将有多少个字节（除非您执行某些操作以排除多字节字符）。

我不确定，但这可能仍然是正确的，但并非完全按照我的想法进行。

这是正确的答案：

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

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

谜案：为了安全起见，我可以理解为什么MySQL为每个字符假设3个字节，因为否则可能会破坏UNIQUE约束。但是文档似乎建议索引实际上以字节为单位，而不是字符。因此，假设您在varchar（25
6 ）列上放置了25 5
char（765字节）索引。如果您存储的字符都是ASCII，1字节字符，例如AZ，az，0-9，则可以将整个列放入767字节索引中。看来这是实际发生的情况。

以下是我的原始答案中有关字符，字节等的更多信息。

根据Wikipedia的说法，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列的键大小。但是实际上，您 不能 为utf8列指定大于255的索引。

最后，如果您再次参考我的第二个链接，则还有以下内容：

启用innodb_large_prefix配置选项后，对于使用DYNAMIC和COMPRESSED行格式的InnoDB表，此长度限制增加到3072字节。

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

顺便说一下，事实证明您可以使用utf8mb4字符集存储4字节字符。utf8字符集显然仅存储“ plane
0”字符。

编辑：

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