首先,要知道的一点是,并不是在所有的查询条件中出现的列都需要添加索引值,对于什么时候添加B+树索引,一般的经验是,在访问表中很少一部分时的数据使用B+树索引才会有意义(也就是说,索引的列要有很高的筛选性,筛选出绝大部分的数据才是有效的,要有高选择性)
举个栗子
对于性别字段、地区字段、类型字段等可取值的范围很小,筛选出来的数据占明显一部分的总数据,那么这些就被称为低选择性。
按性别去进行查询时,可取值的范围就是男或者女,因此查询出的结果很有可能占该表的50%的数据(假设男女比例为1:1),这时给性别列添加B+树索引是完全没有必要的(因为低选择性,会导致走更多的页去找数据,倘若每张页都有几条匹配的数据,那么为什么不用全表扫描呢?这样甚至减少了IO次数,不用读取索引页),而且MySQL在较低选择性时也是默认不会走索引的。
那么如何去判断索引是否是高选择性呢?可以通过SHOW INDEX结果中的Cardinary列来观察,这个Cardinary值是非常关键的,它表示的是索引中不重复记录数量的预估值,这里要注意的是,这只是一个预估值,而不是一个准确值,实际上,获取准确值也是很难实现的。在实际应用中,Cardinary/n_rows_intable应尽可能地区接近1(用不重复记录数量除以表的行数,如果结果为1,不就代表着每一行的数据都是不重复的咯),如果非常小,就代表Cardinary小,也就是不重复记录数量比较少,重复数据多,就要考虑要不要去建索引了
通过下面的SQL语句可以查询Cardinality值
SHOW INDEX FROM 表;
MySQL中有不同的存储引擎,而每种引擎对于B+树索引的实现是不同的,对Cardinary的统计是放在存储引擎层去进行的,也就是不同存储引擎的Cardinary的计算是不同的
Cardinary的统计是通过采样(Sample)的方法来完成的,因为在生产环境中,索引的更新操作比较频繁,如果每次索引在发生操作时就对其进行Cardinary统计,会给数据库带来很大的负担**(频繁统计会加大数据库压力)。而且假如数据量很大,统计一次Cardinary的信息所需要的时间可能非常长,这是不能接受的(统计时间长)**
首先,在InnoDB存储引擎中,Cardinary统计信息的自动更新(即自动重新计算)是发生在DML语句中的(也就是Insert、Update、Delete都可能会触发,这是要根据限制条件的)
下面我们通过参数去看Cardinality是怎么变化的
show variables like “innodb_stats%”;
InnoDB对于Cardinality信息的更新是有两种策略的,分别为持久化与非持久化
当innodb_stats_persistent为ON时,表示将统计信息持久化,也就是是开启持久化,为OFF时,就代表使用废旧话策略
持久化策略
持久化策略是将优化器统计信息保存在硬盘中的(数据保存在mysql.innodb_index_stats和mysql.innodb_table_stats表中,在information_schema.statistics和information_schema.tables也有记录),MySQL服务器重启的时候就会加载信息进来,同时使Cardinality在加载时不发生变化来提高计划的稳定性,让优化器更有可能为给定的一致查询做出一致的判断
总的来说,好处就是,当重启MySQL服务器时,可以减少重新计算每个索引的Cardinality值
对于使用了持久化进行更新Cardinality信息的话,就涉及到另一个参数innodb_stats_auto_recalc,这个参数定义了统计信息自动更新的规则,如下
-
当表上的记录修改超过10%时,会对统计信息重新计算
-
两次统计收集信息的时间间隔要超过10S
-
收集信息的动作是异步的,在执行完比较大的DML操作后,可能会过一段时间才要重新收集统计信息(因为有统计收集时间延迟),如果想要及时信息的话,使用analyze命令去收集
非持久化策略
非持久化策略是原先低版本的MySQL就有的。
非持久化策略是将数据保存在information_schema.statistics和information_schema.tables中
在非持久化策略,更新Cardinality信息的策略为
-
表中十六分之一的数据发生过变化
-
stat_modified_counter > 2000000000(200亿)时,发生变化
第一种情况考虑的是,自上次统计Cardinality信息之后,如果有十六分之一的数据已经发生过变化,这时就需要更新Cardinality值,第二种情况针对的是,假如就对几行数据进行修改,在十六分之一以内的数据不断进行修改,这样不就永远无法触及到第一种情况,所以要加一个stat_modified_counter计数器,这个计数器表示发生变化的次数,当超过一定值时,也要更新信息
采样计算
前面提到过,计算Cardinality的值是使用采样计算的,因为当数据量很大的时候,如果全部计算会很慢很慢
自我介绍一下,小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数Java工程师,想要提升技能,往往是自己摸索成长或者是报班学习,但对于培训机构动则几千的学费,着实压力不小。自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
因此收集整理了一份《2024年Java开发全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,真正体系化!
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!
如果你觉得这些内容对你有帮助,可以扫码获取!!(备注Java获取)
![img](https://img-blog.csdnimg.cn/img_convert/133c4fd5ab5f53082fca5a2a0a1494a0.jpeg)
1200页Java架构面试专题及答案
小编整理不易,对这份1200页Java架构面试专题及答案感兴趣劳烦帮忙转发/点赞
百度、字节、美团等大厂常见面试题
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取!
见面试题
[外链图片转存中…(img-EtrQ0yal-1713291490439)]
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取!