MySQL 前缀索引基本指南（通过例子）

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

MySQL前缀索引简介

当您为列创建二级索引时，MySQL 将列的值存储在单独的数据结构中，例如 B 树和哈希。

如果列是字符串列，索引将消耗大量磁盘空间，并可能减慢INSERT操作。

为了解决这个问题，MySQL 允许您使用以下语法为字符串列的列值的前导部分创建索引：

column_name(length)
Code language: SQL (Structured Query Language) (sql)

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

CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
);
Code language: SQL (Structured Query Language) (sql)

或者向现有表添加索引：

CREATE INDEX index_name
ON table_name(column_name(length));
Code language: SQL (Structured Query Language) (sql)

在此语法中，长度是非二进制字符串类型（例如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表进行演示。

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

SELECT 
    productName, 
    buyPrice, 
    msrp
FROM
    products
WHERE
    productName LIKE '1970%';
Code language: SQL (Structured Query Language) (sql)

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

EXPLAIN SELECT 
    productName, 
    buyPrice, 
    msrp
FROM
    products
WHERE
    productName LIKE '1970%';
Code language: SQL (Structured Query Language) (sql)

这是输出：

如果您经常通过产品名称查找产品，那么您应该为此列创建索引，因为这样搜索效率会更高。

产品名称栏的大小为 70 个字符。我们可以使用列前缀键部分。

下一个问题是如何选择前缀的长度？为此，您可以调查现有数据。目标是在使用前缀时最大限度地提高列中值的唯一性。

为此，您需要执行以下步骤：

步骤 1. 查找表中的行数：

SELECT
   COUNT(*)
FROM
   products;Code language: SQL (Structured Query Language) (sql)

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

SELECT
   COUNT(DISTINCT LEFT(productName, 20)) unique_rows
FROM
   products;Code language: SQL (Structured Query Language) (sql)

如输出所示，在本例中，20 是一个很好的前缀长度，因为如果我们使用产品名称的前 20 个字符作为索引，则所有产品名称都是唯一的。

让我们为productName列创建一个前缀长度为20 的索引：

CREATE INDEX idx_productname 
ON products(productName(20));
Code language: SQL (Structured Query Language) (sql)

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

EXPLAIN SELECT 
    productName, 
    buyPrice, 
    msrp
FROM
    products
WHERE
    productName LIKE '1970%';
Code language: SQL (Structured Query Language) (sql)

现在，查询优化器使用新创建的索引，比以前更快、更高效。

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

本教程有帮助吗？