mysql前缀索引是什么

这篇“mysql前缀索引是什么”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“mysql前缀索引是什么”文章吧。

在mysql中，前缀索引是一种特殊索引类型，是对文本的前几个字符建立索引；这种索引类型可以在一定程度上减少索引的大小，可以更加高效地处理一些特定的查询操作。通常情况下，索引长度应该越短越好，因为短索引可以减小索引的大小，但是如果索引长度太短，可能会出现索引失效的情况，导致查询效率反而变慢；因此，在使用前缀索引时，需要根据具体情况选择适当的索引长度。

一、什么是前缀索引

MySQL是一款广泛使用的关系型 数据库管理 系统，它在 数据存储 和检索方面具有很高的效率和灵活性。在MySQL中，索引是一种用于加速查询操作的数据结构，它可以大大提高查询的速度和效率。而前缀索引则是MySQL中的一种特殊索引类型，它可以更加高效地处理一些特定的查询操作。

所谓前缀索引，说白了就是对文本的前几个字符建立索引（ 具体是几个字符在建立索引时去指定 ），比如以产品名称的前 10 位来建索引，这样建立起来的索引更小，查询效率更快！

前缀索引是指只索引字符串的前缀部分，而不是整个字符串。这种索引类型可以在一定程度上减少索引的大小，例如，如果有一个包含一千万个字符串的表，每个字符串的长度为100个字符，如果使用完整字符串作为索引，那么索引大小将会非常大，而且查询效率也会很低。但如果只索引字符串的前10个字符，那么索引大小就会大大减小，而且查询效率也会明显提高。

有点类似于 *** 中对字段使用 Left 函数来建立函数索引，只不过 MySQL 的这个前缀索引在查询时是内部自动完成匹配的，并不需要使用 Left 函数。

在MySQL中，使用前缀索引需要指定索引的长度。通常情况下，索引长度应该越短越好，因为短索引可以减小索引的大小，但是如果索引长度太短，可能会出现索引失效的情况，导致查询效率反而变慢。因此，在使用前缀索引时，需要根据具体情况选择适当的索引长度。

二、为什么要用前缀索引

可能有的同学会发出疑问，为什么不对整个字段建立索引呢？

一般来说，当某个字段的数据量太大，而且查询又非常的频繁时，使用前缀索引能有效的减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度。

比如，客户店铺名称，有的名称很长，有的很短，如果完全按照全覆盖来建索引，索引的存储空间可能会非常的大， 有的表如果索引创建的很多，甚至会出现索引存储的空间都比数据表的存储空间大很多 ，因此对于这种文本很长的字段，我们可以截取前几个字符来建索引，在一定程度上，既能满足数据的查询效率要求，又能节省索引存储空间。

但是另一方面，前缀索引也有它的缺点，MySQL 中无法使用前缀索引进行 ORDER BY 和 GROUP BY，也无法用来进行覆盖扫描，当字符串本身可能比较长，而且前几个字符完全相同，这个时候前缀索引的优势已经不明显了，就没有创建前缀索引的必要了。

因此这又回到一个概念，那就是关于 索引的选择性 ！

关于 数据库 表索引的选择性，我会单独开篇来讲解，大家只需要记住一点： 索引的选择性越高则查询效率越高 ，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行，数据查询速度更快！

当某个字段内容的前几位区分度很高的时候，这个时候采用前缀索引，可以在查询性能和空间存储方面达到一个很高的性价比 。

那么问题来了，怎么创建前缀索引呢？

三、怎么创建前缀索引

建立前缀索引的方式，方法很简单，通过如下方式即可创建！

ALTER TABLE table_name ADD KEY

(column_name(prefix_length));

其中prefix_length这个参数，就是前缀长度的意思，通常通过如下方式进行确认，步骤如下：

第一步，先计算某字段全列的区分度。

SELECT COUNT ( DISTINCT column_name) / COUNT (*) FROM

table_name;

第二步，然后再计算前缀长度为多少时和全列的区分度最相似

SELECT COUNT ( DISTINCT LEFT (column_name, prefix_length)) / COUNT (*) FROM

table_name;

最后，不断地调整prefix_length的值，直到和全列计算出区分度相近，最相近的那个值，就是我们想要的值。

下面以某个测试表为例，数据体量在 100 万以上，表结构如下！

CREATE TABLE `tb_test`

(

`id` bigint ( 20 ) unsigned NOT NULL

AUTO_INCREMENT,

`name` varchar ( 100 ) DEFAULT NULL

,

PRIMARY KEY ( `id`

)

) ENGINE = InnoDB AUTO_INCREMENT= 1 DEFAULT CHARSET

=utf8mb4;

测试一下正常的带name条件查询，效率如下：

select * from tb_test where name like 1805.59281427%

我们以name字段为例，创建前缀索引，找出最合适的prefix_length值 。

首先，我们大致计算一下name字段全列的区分度。

可以看到，结果为 0.9945，也就是说全局不相同的数据率在99.45%这个比例。

下面我们一起来看看，不同的prefix_length值下，对应的数据不重复比例。

当prefix_length为5，区分度为0.2237

当prefix_length为10，区分度为0.9944

当prefix_length为11，区分度为0.9945

通过对比，我们发现当prefix_length为11，最接近全局区分度，因此可以为name创建一个长度为11的前缀索引，创建索引语句如下：

alter table tb_test add key ( name ( 11

下面，我们再试试上面那个语句查询！

创建前缀索引之后，查询效率倍增 ！

四、使用前缀索引需要注意的事项

是不是所有的字段，都适合用前缀索引呢？

答案显然不是，在上文我们也说到了，当某个索引的字符串列很大时，创建的索引也就变得很大，为了减小索引体积，提高索引的扫描速度，使用索引的前部分字符串作为索引值，这样索引占用的空间就会大大减少，并且索引的选择性也不会降低很多，这时前缀索引显现的作用就会非常明显，前缀索引本质是索引查询性能和存储空间的一种平衡。

对于 BLOB 和 TEXT 列进行索引，或者非常长的 VARCHAR 列，就必须使用前缀索引，因为 MySQL 不允许索引它们的全部长度。

但是如果某个字段内容，比如前缀部分相似度很高，此时的前缀索引显现效果就不会很明显，采用覆盖索引效果会更好！

以上就是关于“mysql前缀索引是什么”这篇文章的内容，相信大家都有了一定的了解，希望小编分享的内容对大家有帮助，若想了解更多相关的知识内容，请关注行业资讯频道。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们[email protected] 处理，核实后本网站将在24小时内删除侵权内容。