高级DBA运维
成长之路

MySQL辅助索引(S)和聚簇索引(C),回表查询及索引问题

聚簇索引(C)B树索引

前提

簇就是段区页中的区,64个page=1M
(1)表中设置了主键,主键列就会自动被作为聚集索引.比如ID not null primary key
(2)如果没有主键,会选择唯一键unique作为聚集索引.
(3)以上都没有,生成隐藏聚簇索引

过程

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

作用

有了聚簇索引之后,将来插入的数据行,在同一个区内,都会按照ID值的顺序,有序在磁盘存储数据。
innodb表指聚簇索引组织存储数据表

构建过程画图说明

辅助索引BTREE结构

说明

使用普通列作为条件构建的索引,需要后期人为创建。
将辅助索引列值+ID主键值,构建辅助索引B树结构。

作用

优化非聚簇索引列之外的查询条件,比如name列

构建过程画图说明

辅助索引细分

1.普通的单列辅助索引
2.联合索引:多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表查询
 说明:使用多列组合一个索引,最左原则。idx(a,b,c)
 a.查询条件中,必须要包含最左列a列
 b.建立索引时,一定要选择重复值少的列,作为最左列
例如:idx(a,b,c)相当于a,ab,abc三个索引都被创建

3.前缀索引
 前缀索引时针对于,我们所选择索引列值长度过长,会导致索引树的高度增高。会导致索引应用时,需要读取更多的索引数据页。
 所以可以选择大字段的前面部分字符作为索引列生成条件。
 mysql中建议索引树高度3-4层

B+TREE索引树高度影响因素

1.索引字段较长->前缀索引
2.数据行过多->分区表(分区表在更新数据的时候容易锁表),现在更多使用归档表,归档多张表,按照日期做历史数据(手工或者pt-arvhive),
 或者分布式结构
3.数据类型->选择合适的数据类型(比如varchar() char() enmu() 变长长度字符串)
解决方案:变长字符串使用varchar,enum类型的使用enum ('山东','河北','黑龙江','吉林','辽宁','陕西'......)
高度影响性能,千万行以下的一般3-4层为最佳

回表查询

MySQL用来存储数据行的逻辑结构,表的数据行最终存储到了很多的PAGE上。
Innodb存储引擎,会按照聚簇索引有序的组织存储表数据到各个区的连续的页上。
这些连续数据页,成为了聚簇索引的叶子节点,你可以认为聚簇索引就是原表数据。
所以,回表即是回聚簇索引。
用户使用辅助索引列作为条件查询时,首先扫描辅助索引的B树,如果辅助索引能够完全覆盖我们的查询结果时,就不需要回表。
如果不能完全覆盖到,只能通过得出的ID主键列,回到聚簇索引(回表)扫描,最终得到想要的结果。
影响:
1.IO量级表大
2.IOPS增大导致磁盘达到瓶颈
3。随机IO变多
减少回表:
1.将查询尽可能用ID主键列查询
2.设计合理的辅助索引(联合索引)
3.更精确地查询条件+联合索引
4.优化器算法:MGRR

MGRR相关参数

我们可以通过参数 optimizer_switch 的标记来控制是否使用MRR,当设置mrr=on时,表示启用MRR优化。mrr_cost_based 表示是否通过 
cost base的方式来启用MRR.如果选择mrr=on,mrr_cost_based=off,则表示总是开启MRR优化。
参数read_rnd_buffer_size 用来控制键值缓冲区的大小。

索引失效

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

更新数据时,会对索引又影响吗?

比如insert,update,delete一行数据,对于聚簇索引会立即更新,对于辅助索引,不是实时更新的.
在Innodb内存中,加入了insert buffer(会话),现在版本叫change buffer
change buffer 功能是临时缓冲辅助索引需要的数据更新。
当我们需要查询新insert的数据,会在内存中进行merge(合并)操作,此时辅助索引是全新的。
怎么知道用户在访问的时候,走了我们设置的索引,遇到双11那种大量访问时,索引设置不及时,应该如何解决,如何知道用户经常访问的数据信息是哪些?
双11的时候,并发度太高,提前1~2周将热点商品数据灌入到Tair(radis,memcached)集群中,KAFKA处理队列问题。核心就是对列和缓存的使用。

——————————————————————–

MySQL 将根据辅助索引获取的结果集根据主键进行排序,将乱序化为有序,可以用主键顺序访问基表,将随机读转化为顺序读,
多页数据记录可一次性 读入或根据此次的主键范围分次读入,以减少IO操作

,提高查询效率。
辅助索引又称为二级索引 
(1). 索引是基于表中列的值(索引键值)生成的B树结构 
(2). 首先提取此列所有的值,进行自动排序 
(3). 将排好序的值,均匀的分布到索引树的叶子节点中(16K) 
(4). 然后生成此索引键值所对应得磁盘数据页的指针 
(5). 生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度 
已知表有id、name、age、gender四列,select * from t1 where id=10; 
问题: 基于索引键做where查询,对于id列是顺序IO,但是对于其他列的查询,可能是随机IO? 
因为没有主键或者唯一键,B树根据索引键值无法找到唯一数据行
辅助索引-->ID-->聚集索引,带来大量的随机IO。 减少回表次数,可以用联合索引和MRR来解决问题。 联合索引减少回表次数的表现就是精准的锁定ID。 
MRR:辅助索引-->回表-->聚集索引,在回表之前自动将主键值集中后排序,一次性回表查询,减少回表次数,
随机IO可能转换为顺序IO MRR的全称是Multi-Range Read Optimization,是优化器将随机IO转化为顺序IO以降低查询过程中IO开销的一种手段, 
这对IO-bound类型的SQL语句性能带来极大的提升,适用于range eq_ref类型的查询 MGRR优化的几个好处 使数据访问有随机变为顺序,查询辅助索引,
首先把查询结果按照主键进行排序,按照主键的顺序进行书签查找, 减少缓冲池中页被替换的次数,批量处理键值的操作

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

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

 

赞(1)

评论 抢沙发

评论前必须登录!

 

LNMP社群 不仅仅是技术

关于我们网站地图