一文搞懂什么是MySQL前缀索引

目录
  • 一、什么是前缀索引
  • 二、为什么要用前缀索引
  • 三、怎么创建前缀索引
  • 四、使用前缀索引需要注意的事项
  • 五、小结

一、什么是前缀索引

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

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

二、为什么要用前缀索引

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

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

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

但是另一方面，前缀索引也有它的缺点，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前缀索引的详细内容，更多关于MySQL前缀索引的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • 详解MySQL单列索引和联合索引

    详解MySQL单列索引和联合索引

    目录 一.简介 二.单列索引 三.最左前缀原则 四.同时存在联合索引和单列索引(字段有重复) 五.联合索引本质 六.索引失效 七.其它知识点 八.MySQL存储引擎简介 九.索引结构(方法.算法) 一.简介 利用索引中的附加列,可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单独的索引. 联合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏进行排序,然后按名字对有相同姓氏的人进行排序.如果您知道姓,电话簿将非常有用,如果您知道姓和名,电话簿则更为有用,但如果您只知道名不知道姓

  • MySQL索引介绍及优化方式

    MySQL索引介绍及优化方式

    目录 一.导致sql执行慢的原因 二.分析原因时,一定要找切入点 三.什么是索引? 四.Explain分析 1.id 2.select_type 3.table 4.type(★) 5.possible_key 6.key(★) 7.key_len 8.ref(★) 9.rows(★) 10.extra 五.优化案例 六.是否需要创建索引? 一.导致sql执行慢的原因 硬件条件限制: io吞吐量小,形成瓶颈(读取磁盘数据) 网络传输速度慢 内存不足(读取磁盘数据加载到内存) 程序设计方面: 没有

  • MySql索引原理与操作

    MySql索引原理与操作

    目录 1. 什么是索引 2. 索引的实现原理 3. 添加索引的条件 4. 索引的操作 1. 创建索引 2. 删除索引 3. 查看一个sql语句是否使用了索引进行检索 5. 索引的失效 6. 索引的类型 1. 什么是索引 索引是在数据库表的字段上添加的,是为了提高查询效率存在的一种机制. 一张表的一个字段可以添加一个索引,当然,多个字段联合起来也可以添加索引. 索引相当于一本书的目录,是为了缩小扫描范围而存在的一种机制. 索引相当于一本书的目录 通过索引查询的方式被称为索引查询. 在 mysql

  • MySQL 中这么多索引该怎么选择

    目录 前言 MySQL 单字段索引问题 组合索引 唯一索引和普通索引 总结 前言 索引的本质是存储引擎用于快速查询记录的一种数据结构.特别是数据表中数据特别多的时候,索引对于数据库的性能就愈发重要. 在数据量比较大的时候,不恰当的索引对于数据库的性能的影响是非常大的.在实际的应用中常常会遇见使用错误的索引而导致一系列问题,所以,选择正确的索引对于 MySQL 数据库来说相当重要. 下面我们就来一起聊聊在 MySQL 数据库中该怎么选择正确的索引. 在了解怎么选择索引之前,我先给你举一个例子.如果

  • MySQL索引详细解析

    MySQL索引详细解析

    目录 1. MySQL 索引的最左前缀原则 2. 前缀索引 3. 索引下推(ICP——Index Condition Pushdown) 4. 查看 MySQL 语句是否用到索引 5. 为什么官方建议用自增长主键作为索引 6. 如何创建索引 7. 创建索引注意事项 8. 使用索引一定可以提高查询性能嘛 9. 索引失效 1. MySQL 索引的最左前缀原则 左前缀原则是联合索引在使用时要遵循的原则,查询索引可以使用联合索引的一部分,但是必须从最左侧开始.在创建联合索引时,要根据业务需求,where

  • MySQL中索引的定义以及操作新手教程

    目录 索引的定义 索引的类型 普通.唯一.主键和全文索引 普通索引(INDEX) 唯一索引(UNIQUE INDEX) 主键索引(PRIMARY KEY) 全文索引(FULLTEXT) 单列索引和组合索引 聚集索引和非聚集索引 索引的创建原则 索引操作 创建索引 查看索引 删除索引 总结 索引的定义 数据库中的索引就像一本书的目录,可以据此快速定位数据库中相关数据的所在位置. 在数据库中,索引被定义为一种特殊的数据结构,由数据库中的一列或多列组合而成,可以用来快速查询数据表中某一特定值的记录.

  • 一文搞懂什么是MySQL前缀索引

    一文搞懂什么是MySQL前缀索引

    目录 一.什么是前缀索引 二.为什么要用前缀索引 三.怎么创建前缀索引 四.使用前缀索引需要注意的事项 五.小结 一.什么是前缀索引 所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快! 有点类似于 Oracle 中对字段使用 Left 函数来建立函数索引,只不过 MySQL 的这个前缀索引在查询时是内部自动完成匹配的,并不需要使用 Left 函数. 二.为什么要用前缀索引 可能有的同

  • 一文搞懂MySQL预编译

    1.预编译的好处 大家平时都使用过JDBC中的PreparedStatement接口,它有预编译功能.什么是预编译功能呢?它有什么好处呢? 当客户发送一条SQL语句给服务器后,服务器总是需要校验SQL语句的语法格式是否正确,然后把SQL语句编译成可执行的函数,最后才是执行SQL语句.其中校验语法,和编译所花的时间可能比执行SQL语句花的时间还要多. 如果我们需要执行多次insert语句,但只是每次插入的值不同,MySQL服务器也是需要每次都去校验SQL语句的语法格式,以及编译,这就浪费了太多的时

  • 一文搞懂MySQL XA如何实现分布式事务

    一文搞懂MySQL XA如何实现分布式事务

    目录 前言 XA 协议 如何通过MySQL XA实现分布式事务 前言 MySQL支持单机事务的良好表现毋庸置疑,那么在分布式系统中,涉及多个节点,MySQL又是如何实现分布式事务的呢?比如开发一个业务系统,它接受外部的请求,然后访问多个内部其它系统才能执行该请求.执行时我们需要同时更新多个数据库的值(D1,D2,D3).由于系统必须处于一个一致性,也就是这三个数据库的值要么同时更新成功,要么全部不更新.不然会造成子系统有些指令成功了,有些指令尚未执行.导致对结果理解混乱. 那么,MySQL如何实

  • 一文搞懂MySQL持久化和回滚的原理

    一文搞懂MySQL持久化和回滚的原理

    目录 redo log 为什么要先更新内存数据,不直接更新磁盘数据? 为什么需要redo log? redo log是如何实现的? 为什么一个block设计成512字节? 为什么要两段式提交? crash后是如何恢复的? undo log 什么情况下会生成undo log? undo log是如何回滚的? undo log存在什么地方? redo log 事务的支持是数据库区分文件系统的重要特征之一,事务的四大特性: 原子性:所有的操作要么都做,要么都不做,不可分割. 一致性:数据库从一种状态变

  • 一文搞懂MySQL脏读,幻读和不可重复读

    一文搞懂MySQL脏读,幻读和不可重复读

    目录 MySQL 中事务的隔离 1.READ UNCOMMITTED 2.READ COMMITTED 3.REPEATABLE READ 4.SERIALIZABLE 前置知识 1.事务相关的常用命令 2.MySQL 8 之前查询事务的隔离级别 3.MySQL 8 之后查询事务的隔离级别 4.查看连接的客户端详情 5.查询连接客户端的数量 6.设置客户端的事务隔离级别 7.新建数据库和测试数据 8.名称约定 脏读 1.脏读演示步骤1 2.脏读演示步骤2 3.脏读演示步骤3 4.不可重复读 5.

  • 一文搞懂Mysql中的共享锁、排他锁、悲观锁、乐观锁及使用场景

    一文搞懂Mysql中的共享锁、排他锁、悲观锁、乐观锁及使用场景

    目录 一.常见锁类型 二.Mysql引擎介绍 三.常用引擎间的区别 四.共享锁与排他锁 五.排他锁的实际应用 六.共享锁的实际应用 七.死锁的发生 八.另一种发生死锁的情景 九.死锁的解决方式 十.意向锁和计划锁 十一.乐观锁和悲观锁 总结 一.常见锁类型 表级锁,锁定整张表 页级锁,锁定一页 行级锁,锁定一行 共享锁,也叫S锁,在MyISAM中也叫读锁 排他锁,也叫X锁,在MyISAM中也叫写锁 悲观锁,抽象性质,其实不真实存在 乐观锁,抽象性质,其实不真实存在 常见锁类型 二.Mysql引擎

  • 一文搞懂MySQL元数据锁(MDL)

    一文搞懂MySQL元数据锁(MDL)

    目录 一.什么是metadata lock 二.MDL和行锁有什么区别 三.MDL为什么会造成系统崩溃 四.MDL的生命周期有多长 五.如何快速找到阻塞源头 六.本文开始的案例最终如何解决 小结 某日,路上收到用户咨询,为了清除空间,想删除某200多G大表数据,且已经确认此表不再有业务访问,于是执行了一条命令‘delete from bigtable’,但好长时间也没删完,经过咨询后,获知drop table删除表速度快,而且能彻底释放空间,于是又在另外一个session中执行了‘drop ta

  • 一文搞懂MySQL运行机制原理

    一文搞懂MySQL运行机制原理

    目录 前言 MySQL服务器体系架构 网络连接层 服务层 存储引擎层 系统文件层 服务器处理客户端请求 连接管理 解析与优化 查询缓存 语法解析 查询优化 存储引擎 小结 前言 前文我们了解了MySQL采用客户端/服务器架构,用户通过客户端程序发送增删改查需求,服务器程序收到请求后处理,并且把处理结果返回给客户端.这篇文章主要看下MySQL服务端是如何处理客户端的请求,感兴趣的小伙伴们可以参考借鉴,希望对大家能有所帮助. MySQL服务器体系架构 MySQL Server架构自顶向下大致可以分网

  • 一文搞懂Java的SPI机制(推荐)

    一文搞懂Java的SPI机制(推荐)

    目录 1 简介 缺点 源码 使用 适用场景 插件扩展 案例 1 简介 SPI,Service Provider Interface,一种服务发现机制. 有了SPI,即可实现服务接口与服务实现的解耦: 服务提供者(如 springboot starter)提供出 SPI 接口.身为服务提供者,在你无法形成绝对规范强制时,适度"放权" 比较明智,适当让客户端去自定义实现 客户端(普通的 springboot 项目)即可通过本地注册的形式,将实现类注册到服务端,轻松实现可插拔 缺点 不能按需

  • 一文搞懂Mybatis-plus的分页查询操作

    目录 1. 简单说明 2. 介绍说明 3. 完整配置类代码 4. 示例代码 5. 最后总结 1. 简单说明 嗨,大家好!今天给大家分享的是Mybatis-plus 插件的分页机制,说起分页机制,相信我们程序员都不陌生,今天,我就给大家分享一下Mybatis-plus的分页机制,供大家学习和Copy. 2. 介绍说明 如果你想看代码,可以直接跳到代码区域,这里只是一些简单的说明,如果你想学习,建议可以看看这一块的任容. 本章节将介绍 BaseMapper 中的分页查询,BaseMapper 接口提

随机推荐