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存储引擎