前言
前面已经对索引的各种类型做了总结
下面,我们需要在实战中使用它,所以我们需要先搞懂在MySQL中,可以加那些索引,然后把这些索引的运行机制进行分析,同志们就可以根据这些运行机制去精确的判断加那些索引合适
不清楚索引的基础知识的同志,请看我前面的文章
目录
MySQL数据库中可以添加那些索引
常见的索引
主键索引、普通索引、唯一索引、联合索引(多个字段的普通索引)、联合唯一索引(多个字段的唯一索引)
特殊索引
全文索引(对文本内容进行模糊搜索使用) 不常用,而且使用环境专一,不在讨论范围内
空间索引(适用空间数据类型) 不常用,而且使用环境专一,不在讨论范围内
前缀索引(用于长字符串建立索引) 使用环境专一,不在讨论范围内
其中,普通索引和唯一索引,联合索引和联合唯一索引这两种对比,本质上是对比的同一件事,就是索引的唯一特性,只是说是否多个字段而已
综上所述,我们需要搞明白以下方面的问题
什么时候使用主键索引
什么时候使用唯一索引
什么时候使用普通索引
什么时候使用多字段特征的索引
什么时候使用主键索引
在上一次的《索引实现原理和索引类型》的文章中,我们已经知道,表中的数据都是通过主键索引的形式存放的,表数据存储到主键索引中,InnoDB使⽤了B+树索引模型，所以数据都是存储在B+树中的
每⼀个索引在InnoDB⾥⾯对应⼀棵B+树
B+树为了维护索引有序性，在插⼊新值的时候需要做必要的维护,如果我们的主键不是有序的，大幅度挪数据,可能会导致一个表跨多个数据页,形成页分裂,而且非主键索引的叶子节点都存储的是主键,所以综上所述,我们要保证主键有序并且主键长度尽量的小,因为我们的所有索引都在存储主键,所以一般情况来说,主键自增就是性能最优的选择
只有及其特殊的情况下,比如:表中必须只能有一个索引,并且有一个字段必须保证是唯一的,这种情况下只能使用必须保证表中唯一的字段做主键索引了
唯一索引和普通索引怎么选择
结论
这两类索引在查询能⼒上是 没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引
唯一索引和普通索引查询过程中的对比
查询过程
普通索引是需要扫描出所有的符合要求的数据的,因为它不知道数据有几条,唯一索引只需要扫描出第一个符合要求的数据的就可以了
看似唯一索引占据了优势,普通索引多扫描数据,但是这些操作在当今的计算机CPU来说基本上没有差别
更新过程
在插入数据的时候,索引就需要更新,所以看索引性能好坏,更新过程中是最重要的。
普通索引的更新步骤
普通索引在更新过程中,会先把需要更新变化的数据直接放入到磁盘中的更改索引缓存区中然后在后续延迟进行对普通索引和更改索引缓存区进行合并
查询的时候,如果普通索引和更改索引缓存区没来的及合并,会直接读取这两个地方的数据,进行及时合并,如果普通索引和更改索引缓存区合并完成,就直接使用普通索引了
唯一索引的更新步骤
因为唯一索引要判断这个数据是否唯一,所以无法使用更改索引缓存区,在更改索引的时候,需要在磁盘中先读取原来的唯一索引数据,检查完毕之后再更改索引,这中间多进行了很多的IO操作,在计算机中IO操作是最耗时的步骤,索引综合下来,唯一索引的效率就下降了很多了
所以唯一索引在查询过程中虽然少扫描了一些东西,占据了性能优势,但是在内存中效果并不明显。普通索引在更新过程中,使用延迟合并机制,大幅度减少了对IO的操作,相比于即时更新的唯一索引,性能提升明显,所以在索引选择上,从性能的角度来看,能用普通索引就用普通索引
什么时候使用多字段特征的索引
对筛选条件字段加联合索引(包含连接条件)
涉及到多个列的条件时,复合索引可以一次性的利用所有条件进行查询,避免单索引或无索引时先查一个条件再遍历另一个条件的低效过程
对分组字段加联合索引
分组时通常会利用排序 聚合相同 分组字段 的行,如果有索引,因为索引本身是有序的,可以省去排序的步骤
对排序字段加联合索引
因为索引本身就是有序的,直接就排成了,读取数据即可
注意: 这里需要创建匹配排序方向的联合索引
覆盖索引
把所有需要查询的字段都加入到联合索引中,那么只需要查询这一条索引就可以直接拿到最终结果,避免回表,最大程度的保证了查询性能
添加联合索引的字段顺序
where查询条件字段、连接表条件字段统一看右边表字段这样漏不了,group by分组条件字段、order by排序条件字段、select查询字段