mysql varchar 索引_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)

对于一百万行，这很慢。我已经尝试添加一个索引

products.name with：

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列显示了前缀

索引(以字节为单位)，如this page所述。

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

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

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

看起来像新的索引没有被使用。如上所述

this page，索引不会用于排序

前缀索引。事实上，如果我截断数据：

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

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

我认为这一点。然而，它在this page说

InnoDB表可以具有高达767字节的索引。如果长度在

字节，为什么拒绝超过255？如果它在

字符，它如何决定每个UTF-8字符的长度？是

它只是假设3？

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

mysql> select version();

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

| version() |

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

| 5.5.27-log |

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

1 row in set (0.00 sec)