网站源码修复,深圳市宝安区核酸检测点,高端网站建设 骆诗,无锡seo网站排名聚集索引#xff08;clustered index#xff09;
innodb存储引擎表是索引组织表#xff0c;表中数据按照主键顺序存放。其聚集索引就是按照每张表的主键顺序构造一颗B树#xff0c;其叶子结点中存放的就是整张表的行记录数据#xff0c;这些叶子节点成为数据页。
聚集索…聚集索引clustered index
innodb存储引擎表是索引组织表表中数据按照主键顺序存放。其聚集索引就是按照每张表的主键顺序构造一颗B树其叶子结点中存放的就是整张表的行记录数据这些叶子节点成为数据页。
聚集索引的存储并不是物理上连续的而是逻辑上连续的叶子结点间按照主键顺序排序通过双向链表连接。多数情况下查询优化器倾向于采用聚集索引因为聚集索引能在叶子结点直接找到数据并且因为定义了数据的逻辑顺序能特别快的访问针对范围值的查询。
聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。由于表里的数据只能按照一颗B树排序因此一张表只能有一个聚簇索引。
在Innodb中聚簇索引默认就是主键索引。如果没有主键则按照下列规则来建聚簇索引:
没有主键时会用一个非空并且唯一的索引列做为主键成为此表的聚簇索引;如果没有这样的索引InnoDB会隐式定义一个主键来作为聚簇索引。
由于主键使用了聚簇索引如果主键是自增id那么对应的数据也会相邻地存放在磁盘上写入性能较高。如果是uuid等字符串形式频繁的插入会使innodb频繁地移动磁盘块写入性能就比较低了。
B树多路平衡查找树
我们知道了innodb引擎索引使用了B树结构那么为什么不是其他类型树结构例如二叉树呢
计算机在存储数据的时候有最小存储单元这就好比人民币流通最小单位是分一样。文件系统的最小单元是块一个块的大小是4k这个值根据系统不同并且可设置InnoDB存储引擎也有自己的最小储存单元—页Page一个页的大小是16K这个值也是可设置的。
文件系统中一个文件大小只有1个字节但不得不占磁盘上4KB的空间。同理innodb的所有数据文件的大小始终都是1638416k的整数倍。 所以在MySQL中存放索引的一个块节点占16kmysql每次IO操作会利用系统的预读能力一次加载16K。这样如果这一个节点只放1个索引值是非常浪费的因为一次IO只能获取一个索引值所以不能使用二叉树。
B树是多路查找树一个节点能放n个值n 16K / 每个索引值的大小。 例如索引字段大小1Kb这时候每个节点能放的索引值理论上是16个这种情况下二叉树一次IO只能加载一个索引值而B树则能加载16个。
B树的路数为n1n是每个节点存在的值数量例如每个节点存放16个值那么这棵树就是17路。
从这里也能看出B树节点可存储多个值所以B树索引并不能找到一个给定键值的具体行。B树只能找到存放数据行的具体页然后把页读入到内存中再在内存中查找指定的数据。
附B树和B树的区别在于B树的非叶子结点只包含导航信息不包含实际的值所有的叶子结点和相连的节点使用链表相连便于区间查找和遍历。
辅助索引
也称为非聚集索引其叶子节点不包含行记录的全部数据叶子结点除了包含键值以外每个叶子结点中的索引行还包含一个书签该书签就是相应行的聚集索引键。
如下图可以表示辅助索引和聚集索引的关系图片源自网络看大概意思即可
当通过辅助索引来寻找数据时innodb存储引擎会通过辅助索引叶子节点获得只想主键索引的主键既然后再通过主键索引找到完整的行记录。
例如在一棵高度为3的辅助索引树中查找数据那需要对这颗辅助索引树进行3次IO找到指定主键如果聚集索引树的高度同样为3那么还需要对聚集索引树进行3次查找最终找到一个完整的行数据所在的页因此一共需要6次IO访问来得到最终的数据页。
创建的索引如联合索引、唯一索引等都属于非聚簇索引。
联合索引
联合索引是指对表上的多个列进行索引。联合索引也是一颗B树不同的是联合索引的键值数量不是1而是大于等于2。
例如有user表字段为idagename现发现如下两条sql使用频率最多
Select * from user where age ;
Select * from user where age ? and name ?;
这时候不需要为age和name单独建两个索引只需要建如下一个联合索引即可
create index idx_age_name on user(age, name)
联合索引的另一个好处已经对第二个键值进行了排序处理有时候可以避免多一次的排序操作。
覆盖索引
覆盖索引即从辅助索引中就可以得到查询所需要的所有字段值而不需要查询聚集索引中的记录。覆盖索引的好处是辅助索引不包含整行记录的所有信息故其大小要远小于聚集索引因此可以减少大量的IO操作。
例如上面有联合索引agename如果如下
select agename from user where age
就能使用覆盖索引了。
覆盖索引的另一个好处是对于统计问题例如
select count(*) from user
innodb存储引擎并不会选择通过查询聚集索引来进行统计。由于user表上还有辅助索引而辅助索引远小于聚集索引选择辅助索引可以减少IO操作。
注意事项
索引只建合适的不建多余的因为每当增删数据时B树都要进行调整如果建立多个索引多个B树都要进行调整而树越多、结构越庞大这个调整越是耗时耗资源。如果减少了这些不必要的索引磁盘的使用率可能会大大降低。 索引列的数据长度能少则少。
索引数据长度越小每个块中存储的索引数量越多一次IO获取的值更多。
匹配列前缀可用到索引 like 9999%like %9999%、like %9999用不到索引Where 条件中in和or可以使用索引 not in 和 操作无法使用索引
如果是not in或面对B树引擎根本不知道应该从哪个节点入手。
匹配范围值order by 也可用到索引多用指定列查询只返回自己想到的数据列少用select *
不需要查询无用字段并且不使用*可能还会命中覆盖索引哦
联合索引中如果不是按照索引最左列开始查找无法使用索引
最左匹配原则
联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引联合索引中如果查询中有某个列的范围查询则其右边的所有列都无法使用索
#阿里云开年Hi购季#幸运抽好礼 点此抽奖https://www.aliyun.com/acts/product-section-2019/yq-lottery?utm_contentg_1000042901
原文链接 本文为云栖社区原创内容未经允许不得转载。
