MySQL前缀索引示例

我们将使用样本数据库中的产品表进行演示。

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

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

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

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

这是输出：

如果您经常按产品名称查找产品，则应为此列创建一个索引，因为它对于搜索更有效。

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

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

为此，请按照下列步骤操作：

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

SELECT
   COUNT(*)
FROM
   products;

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

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

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

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

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

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

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

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

在本教程中，您学习了如何使用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字节。
但是，对于具有动态或压缩行格式的InnoDB表，前缀长度为3,072字节。
MyISAM表的前缀长度最多为1,000个字节。

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