• 首页
  • 国产小呦泬泬99精品
  • 最近2019中文字幕在线高清
  • 免费看少妇作爱视频
  • 曰批全过程免费视频在线观看网站
  • 国色天香在线观看全集免费播放
  • 婆岳同床双飞呻吟
  • 婆岳同床双飞呻吟你的位置:三级小说 > 婆岳同床双飞呻吟 > 超长字符串字段,前缀索引两宗罪

    超长字符串字段,前缀索引两宗罪

    发布日期:2022-06-18 17:11    点击次数:148

    超长字符串字段,前缀索引两宗罪

    本文转载自微信公众号「飞天小牛肉」,作家小牛肉。转载本文请研究飞天小牛肉公众号。

    前缀索引并不是一个难默契的东西,然则这内部触及到的一些细节,我信托好多同学都莫得去潜入了解过。

    老章程,前缀索引关连口试题的背诵版在文末。点击阅读原文不错直达我收录整理的各大厂口试真题

    InnoDB 表中每一列索引的最大长度弗成逾越 767 字节,是以,关于某些比拟长的字段,如若如实有树立索引的必要,使用前缀索引不仅粗略幸免索引长度逾越放弃,而且联系于宽泛索引来说,占用的空间和查询资本更小。

    至于为什么说前缀索引占用的空间和查询资本更小,咱们来告成上个例子:

    假定表中存在一个邮箱 email 字段,咱们在这个字段上头分别创建宽泛索引和前缀索引:

    1)宽泛索引,包含了每行 email 记载的的悉数这个词字符串:alter table user add index index1(email);

    2)前缀索引,取每行 email 记载的前 6 个字节:alter table user add index index2(email(6));

    你不错看到,由于 email(6) 这个索引结构中每个 email 字段都只取前 6 个字节 zhangs,是以占用的空间比宽泛索引更小,这即是使用前缀索引的上风。

    很好默契,对吧。

    前缀索引一宗罪

    然则,前缀索引这个占用空间更小的上风可能会带来极端的记载扫描次数。

    举个例子,实行如下 sql 语句:

    select * from user where email = 'zhangs2001';  

    1)关于宽泛索引 email 来说,实行法举例下:

    从 index1 索引树找到第一个豪迈索引值是 'zhangs2001' 的这札记载,并获取到主键 ID2 的值; 证据主键值回表查询,获取其他相应的记载,然后将获取到的收尾加入收尾集; 取 index1 索引树上刚刚查到的位置的下一札记载,发现也曾不豪迈 email='zhangs2001' 的条目了,实本质行

    这个历程中,只需要回表一次

    2)关于前缀索引 email(6) 来说,实行法举例下:

    从 index1 索引树找到第一个豪迈索引值是 'zhangs' 的这札记载,并获取到主键 ID1 的值; 证据主键值回表查询,判断 email 的值到底是不是 'zhangs2001',发现并不是,这行记载丢弃 取 index1 索引树上刚刚查到的位置的下一札记载,发现 email 前缀仍然豪迈 'zhangs',则获取到主键 ID2 的值;然后证据主键值回表查询,返现 email 的值如实是 'zhangs2001',则将这行记载加入收尾集 如斯重迭,直到 email 前缀不再是 'zhangs',则实行实现

    不错看到,这个历程中,需要回表四次

    这即是前缀索引的第一宗罪:使用前缀索引可能会加多记载扫描次数与回表次数,影响性能

    不外呢,咱们做一些隐微的窜改,就能让这个前缀索引回表次数大大减少。

    把 index2-email(6) 这个前缀索引改成 index3-email(7):

    再来看上头这个例子,实行法举例下:

    从 index1 索引树找到第一个豪迈索引值是 'zhangs2' 的这札记载, 男女无遮挡羞羞视频免费网站并获取到主键 ID2 的值; 证据主键值回表查询,判断 email 的值到底是不是 'zhangs2001',发现如实是,则将这行记载加入收尾集 取 index1 索引树上刚刚查到的位置的下一札记载,发现 email 前缀不豪迈 'zhangs2',则实行实现

    不错看到,联系于宽泛索引,email(7) 这个前缀索引相同只需要回表一次,何况占用更少的索引空间。

    前缀索引二宗罪

    看底下这条 SQL 语句:

    select id,email from user where email = 'zhangs2001'; 

    如若使用 index1 索引(即 email 悉数这个词字符串的索引结构)的话,不错期骗上隐敝索引,从 index1 索引树上查到收尾后就不错复返了,不需要进行回表。

    而如若使用 index2(即 email(6) 前缀索引结构)的话,就不得不再次证据主键值去回表判断 email 字段的值是否的确是 'zhangs2001'。也即是说,使用前缀索引就用不上隐敝索引对查询性能的优化了。

    那有同学就要问了,如若是 email(10) 呢,这个前缀索引不就完满包含了 zhangs2001 的悉数信息了嘛,还需要回表吗?

    谜底是并弗成进击 InnoDB 的回表,因为 InnoDB 并弗成细现在缀索引的界说是否截断了完美信息。谁澄澈你会不会又加多一个 'zhangs20012' 的记载呢,对吧。

    如何界说前缀索引的长度

    索引登第的越长,占用的磁盘空间就越大,琢磨的数据页能放下的索引值就越少,搜索的后果也就会越低。

    在上头的例子中咱们提到,婆岳同床双飞呻吟只需要把前缀索引从 email(6) 改成 email(7),就不错大大减少记载扫描和回表的次数,是以,在界说前缀索引的时分,咱们需要在占用空间和搜索后果之间做一个量度 trade-off。

    事实上,咱们在树立前缀索引时热心的是分别度,分别度越高,意味安宁迭的键值越少,是以分别度越高越好。

    关于索引来说,什么是分别度呢,很浅易,即是这个索引上有几许个不同的值。树立出来的索引上领有越多不同的值,那么这个索引的分别度就越高。

    因此,咱们不错通过统计索引上有几许个不同的值来判断要使用多长的前缀。不错使用底下这个语句,预备出 email 列上有几许个不同的值,记作 email_length:

    select count(distinct email) as email_length from user; 

    然后,挨次登第不同长度的前缀来看分别度,比如咱们要看前缀索引的长度是 6~10 时分的分别度,不错用这个语句:

    select  count(distinct left(email,6))as email_length_6,     count(distinct left(email,7))as email_length_7,     count(distinct left(email,8))as email_length_8,     count(distinct left(email,9))as email_length_9,     count(distinct left(email,10))as email_length_10, from user; 

    诚然了,既然咱们使用了前缀索引,那么就不可幸免的会赔本分别度,就像咱们前边所说的,谁也不澄澈会不会又新增出一札记载完满踩中前缀然则又不豪迈判断条目。是以咱们需要事前设定一个不错接纳的分别度赔本比例,比如 5%。然后找出不小于 email_length * (1 - 5%) 的值,假定这里 email_length_8、email_length_9 都豪迈,咱们就不错选拔前缀长度为 8。

    前缀索引的分别度不够高如何办

    我那时实习的时分就碰到过这个问题,字段(假定这个字段名是 a)超等超等长,弘大于 InnoDB 的放弃 767 字节,宽泛索引笃信是不可能了,前缀索引就算是长度界说成 767 都如故存在分别度不高的情况,然则又存在证据这个字段进行查询的挺平凡的一个需求。

    一个很常见的惩处时刻即是 Hash。

    对这个超长字段 a 进行 hash(假定定名为 a_hash) 存入数据库,然后对这个 hash 值树立索引,由于 hash 值相同可能存在打破,也即是说两个不同的 a 通过 Hash 函数得到的收尾可能是琢磨的,是以咱们在查询语句的 where 部分还需要进行一次精准判断

    # 假定输入的字段是 input_a select * from user where hash(input_a) = a_hash and input_a = a; 

    不外使用 Hash 这种形状有个人所共知的纰谬,那即是不救援鸿沟查询了,只可等值查询。

    终末放上这道题的背诵版:

    口试官:前缀索引了解吗,为什么要建前缀索引

    小牛肉:前缀索引即是登第字段的前几个字节树立索引。领先,InnoDB 放弃了每列索引的最大长度弗成逾越 767 字节,是以,关于某些比拟长的字段,如若如实有树立索引的必要,使用前缀索引不仅粗略幸免索引长度逾越放弃,而且联系于宽泛索引来说,占用的空间和查询资本更小。

    不外前缀索引可能会导致两个问题:

    第一个,使用前缀索引可能会加多记载扫描次数与回表次数,影响性能。针对这少许呢,其实前缀索引长度的登第如故很紧迫的,可能前缀界说的长少许,就粗略大幅减少记载扫描次数和回表次数,是以,在树立前缀索引的时分,咱们需要在占用空间和搜索后果之间做一个量度

    第二个,使用前缀索引其实就没法用隐敝索引对查询性能的优化了,因为 InnoDB 并弗成细现在缀索引的界说是否截断了完美信息,就算是完满踩中了前缀索引,InnoDB 还取得表说明一次到底是不是豪迈条目了。

     



    Powered by 三级小说 @2013-2022 RSS地图 HTML地图