MySQL 前缀索引

---

简介：在本教程中，您将学习如何使用MySQL前缀索引为字符串列创建索引。

---

MySQL前缀索引简介

当您为列创建二级索引时，MySQL会将列的值存储在单独的数据结构中，例如B-Tree和Hash。 如果列是字符串列，则索引将占用大量磁盘空间并可能减慢INSERT操作速度。 为解决此问题，MySQL允许您使用以下语法为字符串列的列值的前导部分创建索引：

column_name(length)

例如，以下语句在创建表时创建列前缀键部分：

CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
);

或者向现有表添加索引：

CREATE INDEX index_name
ON table_name(column_name(length));

在语法中，长度是字符的用于非二进制字符串类型，如数量CHAR，VARCHAR和TEXT与字节为二进制字符串类型例如，数量BINARY，VARBINARY和BLOB。 MySQL允许您有选择地创建列前缀关键零部件CHAR，VARCHAR，BINARY，和VARBINARY 列。如果为BLOB和TEXT列创建索引，则必须指定列前缀关键部分。 请注意，前缀支持和前缀长度（如果支持）取决于存储引擎。对于带REDUNDANT或COMPACT行格式的InnoDB表  ，最大前缀长度为767字节。但是，对于带有DYNAMIC或COMPRESSED行格式的InnoDB表  ，前缀长度为3,072字节。MyISAM表的前缀长度最多为1,000个字节。

---

MySQL前缀索引示例

我们将使用示例数据库中的products表进行演示。

+--------------------+
| products           |
+--------------------+
| productCode        |
| productName        |
| productLine        |
| productScale       |
| productVendor      |
| productDescription |
| quantityInStock    |
| buyPrice           |
| MSRP               |
+--------------------+
9 rows in set (0.02 sec)

以下查询查找名称以字符串1970开头的产品：

SELECT 
    productName, 
    buyPrice, 
    msrp
FROM
    products
WHERE
    productName LIKE '1970%';

由于productName列没有索引   ，查询优化器必须扫描所有行以返回结果，如EXPLAIN下面语句的输出所示：

EXPLAIN SELECT 
    productName, 
    buyPrice, 
    msrp
FROM
    products
WHERE
    productName LIKE '1970%';

这是输出：

+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | products | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  110 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)

如果您经常按产品名称找到产品，那么您应为此列创建索引，因为它对搜索更有效。 产品名称列的大小为70个字符。我们可以使用列前缀关键部分。 接下来的问题是你如何选择前缀的长度？为此，您可以调查现有数据。目标是在使用前缀时最大化列中值的唯一性。 为此，请按照下列步骤操作： 步骤1.找到表中的行数：

SELECT
   COUNT(*)
FROM
   products;

第2步。评估不同的前缀长度，直到您可以实现行的合理唯一性：

SELECT
   COUNT(DISTINCT LEFT(productName, 20)) unique_rows
FROM
   products;

+-------------+
| unique_rows |
+-------------+
|         110 |
+-------------+
1 row in set (0.30 sec)

如输出中所示，在这种情况下，20是一个良好的前缀长度，因为如果我们使用产品名称的前20个字符作为索引，则所有产品名称都是唯一的。 让我们为productName列创建一个前缀长度为20的索引：

CREATE INDEX idx_productname 
ON products(productName(20));

并执行查询，查找名称以字符串1970开头的产品：

EXPLAIN SELECT 
    productName, 
    buyPrice, 
    msrp
FROM
    products
WHERE
    productName LIKE '1970%';

+----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | products | NULL       | range | idx_productname | idx_productname | 62      | NULL |    4 |   100.00 | Using where |
+----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)

现在，查询优化器使用新创建的索引，索引比以前更快，更高效。 在本教程中，您学习了如何使用MySQL前缀索引为字符串列创建索引。

---