索引最全分析

基本定义

1、普通索引

普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。

查找时，遇到第一个不符合条件的才返回。

更新时，找到位置直接更新。

如果他是一个普通索引我们现在去查找，当查找到符合的数据的时候这个时候他就会在进行第二次查找，因为列里面的数据是唯一的

所以第二次查找肯定是没有数据的，这一次查找通过二分法去查找B+树查找到二叉树的叶子结点，每个叶子结点都会对应一个数据块（数据页），如果这个数据页在内存中那么他也就不用去在磁盘或硬盘去读区这个数据了，直接在内存中读取

我们也知道一个数据块大概是可以存储几千条数据的，所以在第一次查找的时候刚好这个数据块的尾部，在进行第二次查找的时候就要在进行IO读取了 一次IO读取是很低效的。

2、唯一索引

如果是唯一索引的话这样的话就不会再进行第二次读取了，所以在最坏的情况下，普通索引和唯一索引的最大区别是多进行一次IO读取，概率极小

3、唯一索引和普通索引update时的性能

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作change buffer，实际上它是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。

将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率。

（在redlog里面也会存一份，也会存储changeBuffer的才操作数据，ES（Elaticsearch）的一种同步方式是通过redo log 来进行是实时同步的）

什么情况可以使用changeBuffer？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。

比如，要插入(1,100)这个记录，就要先判断现在表中是否已经存在n=1的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。

因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。

当唯一索引进行更新操作的时候 会先进行一次查找是否唯一，再进行和普通索引一样的操作。

所以在符合业务逻辑和无误的前提下：唯一索引比普通索引多一个查找操作。（在进行insert的时候会根据主键来进行查住 主键索引的数据结构发生改变）

多用普通索引，少用唯一索引

还有就是change Buffer的使用 在更新读取少的 情况下放大change buffer的存储量

因为chanbuffer量越大其变更的数量更多 减少的IO次书更多，性能也就提高了。

第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB的处理流程如下：

 

对于唯一索引来说，找到1和3之间的位置，判断到没有冲突，插入这个值，语句执行结束；

对于普通索引来说，找到1和3之间的位置，插入这个值，语句执行结束。

这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的CPU时间。

 

第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB的处理流程如下：

 

对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；

对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了。

4、单列索引

userid 、 mobile两个单例索引

当使用userid or mobile，两个索引都生效；

当使用userid and mobile，只有userid是生效索引

这种建议使用组合索引

5、组合索引

组合索引，对于（1，2，3），只有，123，12，13，有效并用and连接，用or都无效

6、全文索引（Full-text index）

文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。

如果字段里存放的是由几个、甚至是多个单词构成的较大段文字，普通索引就没什么作用了。

这种检索往往以LIKE %word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。

这时候用全文索引

1. 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

2. MySQL 5.6 及以后的版本，InnoDB 存储引擎才支持全文索引;

FULLTEXT KEY content_tag_fulltext(content,tag)  // 创建联合全文索引列

使用全文索引

和常用的模糊匹配使用 like + % 不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如

select * from fulltext_test 
    where match(content,tag) against('xxx xxx');

注意：

match() 函数中指定的列必须和全文索引中指定的列完全相同，否则就会报错，无法使用全文索引，这是因为全文索引不会记录关键字来自哪一列。

如果想要对某一列使用全文索引，请单独为该列创建全文索引。

 

两种全文索引

1、上面的叫自然语言全文索引

2、布尔全文索引

在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索。

MySQL 内置的修饰符，上面查询最小搜索长度时，搜索结果 ft_boolean_syntax 变量的值就是内置的修饰符，下面简单解释几个，更多修饰符的作用可以查手册

• 必须包含该词

• 必须不包含该词

提高该词的相关性，查询的结果靠前 < 降低该词的相关性，查询的结果靠后 (*)星号 通配符，只能接在词后面 对于上面提到的问题，可以使用布尔全文索引查询来解决，使用下面的命令，a、aa、aaa、aaaa 就都被查询出来了。

select * test where match(content) against('a*' in boolean mode);

 

 

还有主键索引，外键索引，比较常用不做介绍