我有一个这样的表:
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)
2021年9月编辑:我已经使用MySQL 8.0几年了,所以这里有一些更新的信息。
MySQL手册现在有一个关于utf8mb3
(目前也称为utf8
)和utf8mb4
之间转换的信息非常丰富的页面。utf8mb3
已弃用,最终将被删除;当它被删除时,它的当前别名utf8
将改为utf8mb4
。
使用不推荐使用的utf8mb3
时,索引中最多可以存储255个字符,而使用utf8mb4
时,使用COMPACT
或REDUNDANT
行格式时最多可以存储191个字符。
对于COMPRESSED
或DYNAMIC
行格式,索引键前缀最多可以是3072个字节。使用它们,utf8mb3
最多可以索引1024个字符,utf8mb4
最多可以索引768个字符。
以下是我之前的回答,它解释了您可以索引的字符数与字节数 由于我的研究,我必须修改我的答案。我最初发布了这个(引用我自己的话): 我相信答案是你不知道会有多少个字符在索引中,因为您不知道您的字符有多少字节将是(除非您采取措施排除多字节字符)。 我不确定,但它可能仍然是正确的,但不是我当时想的那样。 以下是正确答案: MySQL假设每个utf8字符有3个字节。255个字符是每列可以指定的最大索引大小,因为256x3=768,这打破了767字节的限制 如果没有指定索引大小,MySQL会选择最大大小(即每列255)。不能将UNIQUE约束放在长度大于255的utf8列上,因为唯一索引必须包含整个单元格值。但是可以使用常规索引——它只会对前255个字符(或前767个字节?)进行索引。对我来说,这仍然是一个谜 神秘:为了安全起见,我可以理解为什么MySQL假设每个字符有3个字节,因为否则UNIQUE约束可能会被破坏。但文档似乎表明,索引的大小实际上是以字节为单位,而不是以字符为单位。因此,假设您在varchar(256)列上放置一个255char(765字节)索引。如果存储的字符都是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列的密钥大小。但实际上,不能为utf8列指定大于255的索引。 最后,如果你再次参考我的第二个链接,还有这个: 当innodb_large_prefix配置选项被启用时对于使用动态和压缩行格式。 因此,如果你想的话,似乎可以通过一些调整获得更大的索引。只需确保行格式为DYNAMIC或COMPRESSED即可。在这种情况下,您可以指定1023或1024个字符的索引。 顺便说一下,事实证明,您可以使用[utf8mb4字符集][4]存储4字节字符。utf8字符集显然只存储["平面0"字符][5]。编辑: 我只是试图在varchar(511)列和tinyint(1)列上创建一个复合索引,得到的错误消息是最大索引大小为767字节。这让我相信MySQL假设utf8字符集列每个字符将包含3个字节(最大值),并允许您最多使用255个字符。但这可能只适用于复合索引。当我发现更多信息时,我会更新我的答案。但现在我将把它作为一个编辑。
InnoDB表的限制
警告
不要将MySQL数据库中的MySQL系统表从MyISAM转换为InnoDB表。这是一个不受支持的操作。如果您这样做,MySQL不会重新启动,直到您从备份中恢复旧的系统表或使用MySQL_install_db程序重新生成它们。
警告
将InnoDB配置为使用NFS卷上的数据文件或日志文件不是一个好主意。否则,这些文件可能会被其他进程锁定,从而无法被MySQL使用。
最大值和最小值
- 一个表最多可以包含1000列
- 一个表最多可以包含64个辅助索引
- 默认情况下,单列索引的索引键最多可达767个字节。相同的长度限制适用于任何索引键前缀。例如,如果TEXT或VARCHAR列的列前缀索引超过255个字符,则可能会达到此限制,假设为UTF-8字符集,每个字符最多3个字节。当启用innodb_large_prefix配置选项时,对于使用DYNAMIC和COMPRESSED行格式的innodb表,此长度限制将提高到3072字节
- 如果指定的索引前缀长度大于允许的最大值,则该长度将自动减少到最大长度。在MySQL 5.6及更高版本中,指定索引前缀长度大于最大长度会产生错误
启用innodb_large_prefix时,尝试为REDUNDANT或COMPACT表创建键长度大于3072的索引前缀会导致ER_index_COLUMN_TOO_LONG错误。
InnoDB内部最大密钥长度为3500字节,但MySQL本身将其限制为3072字节。此限制适用于多列索引中组合索引键的长度。
除了可变长度列(VARBINARY、VARCHAR、BLOB和TEXT)之外,最大行长度略小于数据库页面的一半。也就是说,最大行长度大约为8000字节。LONGBLOB和LONGTEXT列必须小于4GB,并且包括BLOB和TEXT列在内的总行长度必须小于4GB。
参考:InnoDB限制