MySQL前缀索引

时间:2019-11-20 08:52:15  来源:igfitidea点击:

简介:在本教程中,您将学习如何使用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前缀索引示例

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

以下查询查找名称以字符串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前缀索引为字符串列创建索引。