社区所有版块导航
Python
python开源   Django   Python   DjangoApp   pycharm  
DATA
docker   Elasticsearch  
aigc
aigc   chatgpt  
WEB开发
linux   MongoDB   Redis   DATABASE   NGINX   其他Web框架   web工具   zookeeper   tornado   NoSql   Bootstrap   js   peewee   Git   bottle   IE   MQ   Jquery  
机器学习
机器学习算法  
Python88.com
反馈   公告   社区推广  
产品
短视频  
印度
印度  
Py学习  »  DATABASE

聊聊MySql优化神器:前缀索引!

MacroZheng • 1 周前 • 24 次点击  

mall学习教程官网:macrozheng.com

一、什么是前缀索引?

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

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

二、为什么要用前缀索引?

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

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

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

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

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

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

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

那么问题来了,怎么创建前缀索引呢?

这或许是一个对你有用的开源项目,mall项目是一套基于 SpringBoot3 + JDK 17 + Vue 实现的电商系统(Github标星60K),采用Docker容器化部署,后端支持多模块和微服务架构。包括前台商城项目和后台管理系统,能支持完整的订单流程!涵盖商品、订单、购物车、权限、优惠券、会员、支付等功能!

  • Boot项目:https://github.com/macrozheng/mall
  • Cloud项目:https://github.com/macrozheng/mall-swarm
  • 视频教程:https://www.macrozheng.com/video/

项目演示:

三、怎么创建前缀索引?

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

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(20unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100DEFAULT 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_length5,区分度为0.2237
  • prefix_length10,区分度为0.9944
  • prefix_length11,区分度为0.9945

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

alter table tb_test add key(name(11));

下面,我们再试试上面那个语句查询!

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

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

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

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

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

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

五、小结

好了,本文主要围绕前缀索引做了一次初步的知识讲解,具体数据库表索引的选择性,还需要结合业务实际需求来考虑!

今天就说这么多,后面的问题,我们继续再扯!


Github上标星60K的电商实战项目mall,全套 视频教程(2023最新版) 已更新完毕!全套教程约40小时,共113期,通过这套教程你可以拥有一个涵盖主流Java技术栈的完整项目经验,同时提高自己独立开发一个项目的能力,下面是项目的整体架构图,感兴趣的小伙伴可以点击链接 mall视频教程 加入学习。

整套 视频教程 的内容还是非常完善的,涵盖了mall项目最佳学习路线、整体框架搭建、业务与技术实现全方位解析、线上Docker环境部署、微服务项目学习等内容,你也可以点击链接 mall视频教程 了解更多内容。

推荐阅读


Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/171116
 
24 次点击