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_codeFROM 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)
回答：
由于我的研究，我必须修改答案。我最初发布了这个（引用自己）：
我相信答案是您不知道索引中将包含多少个字符，因为您不知道您的字符将有多少个字节（除非您执行某些操作以排除多字节字符）。
我不确定，但这可能仍然是正确的，但并非完全按照我的想法进行。
这是正确的答案：
如果您不指定索引大小，则MySQL选择最大大小（即每列255）。不能将UNIQUE约束放在长度大于255的utf8列上，因为唯一索引必须包含整个单元格值。但是可以使用常规索引-
它只会索引前255个字符（或前767个字节？）。那对我来说仍然是个谜。
谜案：为了安全起见，我可以理解为什么MySQL为每个字符假设3个字节，因为否则可能会破坏UNIQUE约束。但是文档似乎建议索引实际上以字节为单位，而不是字符。因此，假设您在varchar（25
）列上放置了25
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个字符。但这也许仅适用于复合索引。我会在发现更多信息时更新我的答案。但是现在我将其保留为编辑内容。
以上是 MySQL varchar索引长度 的全部内容， 来源链接： utcz.com/qa/425770.html
