DevOps开发运维
成长之路

MySQL辅助索引(S)和聚集索引(C)构建B树结构的过程

辅助索引(S)怎么构建B树结构的?

辅助索引又称为二级索引
(1). 索引是基于表中列的值(索引键值)生成的B树结构
(2). 首先提取此列所有的值,进行自动排序
(3). 将排好序的值,均匀的分布到索引树的叶子节点中(16K)
(4). 然后生成此索引键值所对应得磁盘数据页的指针
(5). 生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度
已知表有id、name、age、gender四列,select * from t1 where id=10;
问题: 基于索引键做where查询,对于id列是顺序IO,但是对于其他列的查询,可能是随机IO?
因为没有主键或者唯一键,B树根据索引键值无法找到唯一数据行。

聚集索引(C)怎么构建B树结构的?

前提

(1)表中设置了主键,主键列就会自动被作为聚集索引.
(2)如果没有主键,会选择唯一键作为聚集索引.
(3)聚集索引必须在建表时才有意义,一般是表的无关列(ID)

过程

(1) 在建表时,设置了主键列(ID)
(2) 在将来录入数据时,就会按照ID列的顺序存储到磁盘上.(我们又称之为聚集索引组织表)
(3) 将排好序的整行数据,生成叶子节点.可以理解为,磁盘的数据页就是叶子节点

回表查询

辅助索引-->ID-->聚集索引,带来大量的随机IO。
减少回表次数,可以用联合索引和MRR来解决问题。
联合索引减少回表次数的表现就是精准的锁定ID。 
MRR:辅助索引-->回表-->聚集索引,在回表之前自动将主键值集中后排序,一次性回表查询,减少回表次数,随机IO可能转换为顺序IO 
MRR的全称是Multi-Range Read Optimization,是优化器将随机IO转化为顺序IO以降低查询过程中IO开销的一种手段,
这对IO-bound类型的SQL语句性能带来极大的提升,适用于range eq_ref类型的查询
MGRR优化的几个好处
使数据访问有随机变为顺序,查询辅助索引,首先把查询结果按照主键进行排序,按照主键的顺序进行书签查找,
减少缓冲池中页被替换的次数,批量处理键值的操作
MySQL 将根据辅助索引获取的结果集根据主键进行排序,将乱序化为有序,可以用主键顺序访问基表,将随机读转化为顺序读,多页数据记录可一次性
读入或根据此次的主键范围分次读入,以减少IO操作,提高查询效率。
MGRR相关参数
我们可以通过参数 optimizer_switch 的标记来控制是否使用MRR,当设置mrr=on时,表示启用MRR优化。mrr_cost_based 表示是否通过 cost base的方式
来启用MRR.如果选择mrr=on,mrr_cost_based=off,则表示总是开启MRR优化。
参数read_rnd_buffer_size 用来控制键值缓冲区的大小。

聚集索引和辅助索引构成结论

1.聚集索引只能有一个,非空唯一,一般时主键
2.辅助索引,可以有多个,是配合聚集索引使用的,看查询条件用的多的列,建立辅助索引
3.聚集索引叶子节点,就是磁盘的数据行存储的数据页
4.MySQL是根据聚集索引组织存储数据,数据存储时就是按照聚集索引的顺序进行存储数据
5.辅助索引,只会提取索引键值,进行自动排序生成B树结构
6.有聚集索引的时候,辅助索引顺序为叶子节点-->聚集索引叶子节点,没有聚集索引的时候,辅助索引顺序为叶子节点-->磁盘数据页查询
7.聚集索引和辅助索引对有大量的排序的需求的列可以优化排序操作(order by group by join)

索引失效

案例:同一条语句在查询很快的情况下,突然有一天查询特别慢的原因
由于表的数据更新,修改,删除,插入等频繁,索引对于统计信息收集不准确,导致索引和磁盘数据行之间产生不对等的关系,
解决办法就是索引drop掉,重建索引;经常会发生更新的列尽量不做索引,因为经常更新则需要经常维护,对性能的影响会比较大。

辅助索引细分

1.普通的单列辅助索引
2.联合索引:多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表查询
3.唯一索引:索引列的值都是唯一的.

索引树的高度

高度影响性能,千万行以下的一般3-4层为最佳
1. 数据量级, 
解决方法:
分区表:分区表在更新数据的时候容易锁表
归档表:归档多张表,按照日期做历史数据
分库分表
2. 索引列值过长 , 解决方法:前缀索引
3. 数据类型varchar() char() enmu()
变长长度字符串,使用了char,建议?
解决方案:变长字符串使用varchar,enum类型的使用enum ('山东','河北','黑龙江','吉林','辽宁','陕西'......)
赞(1)

评论 抢沙发

评论前必须登录!

 

LNMP社群 不仅仅是技术

关于我们网站地图