澳门24小时官网 - 欢迎光临

MySQL索引
日期:2024年11月06日     新闻分类: 技术中心      浏览:450次

1. 索引优缺点

优点

●索引大大减小了服务器需要扫描的数据量

●索引可以帮助服务器避免排序和临时表

●索引可以将随机IO变成顺序IO

缺点

●虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存索引文件。

●建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。

●如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。

●对于非常小的表,大部分情况下简单的全表扫描更高效;

1.1. 索引类型

InnoDB支持的几种常见索引类型:B+树索引、哈希索引(InnoDB支持的哈希索引是自适应的,InnoDB存储引擎会根据表的使用情况自动生成哈希索引)、全文索引

2. B+树

B+ 树是一种树数据结构,是一个n叉树,每个节点通常有多个子,一颗B+树包含根节点、内部节点和叶子节点。B+ 树通常用于数据库和操作系统的文件系统中。

注意:B+树索引不能找到给定键值所在的行,B+树索引能找到的只是被查找数据所在的也,然后数据库通过将页读入到内存,然后在内存中进行查找。

2.1. 插入操作

B+树插入必须保证叶子节点记录的排序,以下这三种情况会导致不同的插入算法

叶子节点满 Index Page满 操作

No No 直接将记录插入到叶子节点

Yes No 拆分叶子节点

>将中间节点放到Index Page中

Yes Yes 拆分叶子节点

拆分Index Page

Index Page的中间值放入上层Index Page

可以看出,B+树总是保持平衡,但是为了平衡对于新插入的数据就要做大量的拆分页,为了减少拆分操作,所以B+树提供了类似平滑二叉树的旋转功能。当叶子节点满了,但是其左右节点不满就会将记录移到所在页的兄弟节点

2.2. 删除操作

B+树使用填充因子控制树的删除,50%是填充因子的最小值,也就是数据空间有一半是空闲的。

叶子节点小于填充因子 Index Page小于填充因子 操作

No No 直接将记录从叶子节点删除,如果该节点还是Index Page的节点,则用该节点的右节点代替

Yes No 合并叶子节点

更新Index Page

Yes Yes 合并叶子节点

更新Index Page  

合并Index Page

MySQL填充因子是页大小的1/16,页默认16k就是是预留1k的空间.

3. B+树索引

B+树可以分为聚集索引(主键索引)和非聚集索引

3.1. 聚集索引

索引中键值的逻辑顺序决定了表中相应行的物理顺序。但是实际上维持索引物理顺序一致的成本会非常高,所以聚集索引的才能出并不是物理连续的,而是逻辑连续的。主要依靠:1. 页之间通过双向链表连接,页是有序的 2.页内记录通过双向链表维护,物理存储上并不按照主键顺序存储

InnoDB表是索引组织表,即表中数据按照主键顺序存放,所以每张表只能拥有一个聚集索引。大多数情况下,查询优化器倾向于采用聚集索引,因为聚集索引可以直接在叶子节点上查询到数据。

3.2. 非聚集索引/辅助索引

索引的逻辑顺序与磁盘上的物理存储顺序不同。非聚集索引的键值在逻辑上也是连续的,但是表中的数据在存储介质上的物理顺序是不一致的。索引的记录节点有一个数据指针指向真正的数据存储位置。

3.3. 索引分裂

InnoDB的Page Header保存了插入的顺序信息,通过这些信息InnoDB可以决定是向左还是向右分裂

Page Header 说明

PAGE_LAST_INSERT 指向最后插入记录的指针

PAGE_DIRECTION 最后插入方向: PAGE_LEFT

PAGE_N_DIRECTION 连续插入方向

●随机插入 取页的中间记录作为分裂点

●往同一方向插入 自增插入向右分裂仅插入记录本身

3.4. 索引维护

## 创建删除索引

CREATE/DROP [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type]

ON table_name (index_col_name,...)

ALTER TABLE table_name ADD/DROP [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name (index_col_name,...) [USING index_type]

## 查看表的索引

show index from table

## 更新索引统计信息

analysis table

Cardinality表示索引中唯一值的数量的估计值 特别关键,优化器会根据该值判断使用使用这个索引,该基数越大说明索引的区分度越好

4. 索引使用

4.0.1. 联合索引

指对表中多列数据进行索引。

优点:a.联合索引可以支持最左前缀查找,建立(code,name)的联合索引,下面这两个SQL都可以使用这个索引,可以减少索引数量b. 联合索引已经对第二个字段进行了排序,可以避免多做一次排序操作

alert table table_name add INDEX `index_name` (`a`,`b`)  

select id from table where code = 1

select id from table where code = 1 and name = 'a'

如何选择:第一原则是通过调整索引可以少维护一个索引,其次要考虑索引对空间的占用

4.0.2. 覆盖索引

直接通过索引就可以得到我们想要的数据,就是覆盖索引。此时查询只获取了索引数据页,可以减少大量的IO操作

select id from table where code = 1

4.0.3. 普通索引和唯一索引如何选择

查询过程

执行SQL select id from table where code = 1 ,code有索引

●对应普通索引来说,查找到满足条件的第一条记录,然后继续查找下一个记录,直到不满足code = 1

●对于唯一索引,由于索引有唯一性,查找到满足条件的第一条记录,就会停止

这两者的消耗是差不多的,因为InnoDB是按页读取数据,当读取code=5时,该数据页已经在内存中了,只是多了一次链表查找

更新过程

对于唯一索引,首先需要将数据页读入缓存判断唯一键是否冲突,此时直接将数据更新就行

对于普通索引, 插入记录时,会先将更新操作写入change buffer (写缓冲)[1],等下一次查询访问该数据页时再执行更新操作

通过上面说明看出,普通索引和唯一索引在查询上没有什么区别,主要考虑更新性能的影响,一般来说普通索引就可以了。唯一索引一般用来做重复数据验证

4.0.4. MySQL为什么会选错索引

a. Cardinality统计值与实际严重不符[2]

b. 当查询需要返回记录的大部分字段,索引的过滤后还需要访问表中很大一部分数据(20%左右),优化器可能通过聚集索引查找数据,因为顺序读速度大约离散读...

解决方法

一种方法: 强制MySQL使用指定索引 force index第二种方法:修改语句引导MySQL使用预期的索引第三种方法:新建一个更符合的索引,或者将误用的索引删除

4.0.5. 字符串索引选择

字符串可以选择添加普通索引或者前缀索引,当字符串过长是,前缀索引可以节省索引空间,但是如果前缀的字符不够长时,就会导致过多的回表查询,并且不能使用覆盖索引、无法排序。

alter table SUser add index index1(email);

## 前缀索引

alter table SUser add index index2(email(6));

前缀长度选择可以使用下面方法,判断当长度增加到何时,选择性提升的幅度很小了。

select count(distinct left(code,3))/count(*),count(distinct left(code,4))/count(*) from table

4.1. 建索引的几大原则

1.最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

2.=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。

3.尽量选择区分度高的列作为索引

4.索引列不能参与计算

5. 参考文档

[1] 写缓冲(change buffer),这次彻底懂了:https://www.sohu.com/a/322957463_178889

[2] MySQL为什么有时候会选错索引?:https://www.jianshu.com/p/e1f50ffddc29

[3] MySQL技术内幕-InnoDB存储引擎

版权所有: 澳门24小时官网 备案号:晋ICP备09004627号-2   

邮箱

keda@sxkeda.com

电话

400-0351-150

微信

专属
客服

留言

右侧导航

XML 地图