MySQL-索引全方位解释

索引是什么？

简单理解就是去看一本书的‘索引’部分，如果想找到一本书的特定部分，一般会先看书的‘索引’，然后找到对应的页码。‘索引’可以理解为‘目录’。

在MYSQL中存储引擎用类似的方法使用索引，先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。

索引有什么用？

加快检索表中数据，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID。

索引怎么用？

在查询时where条件为有索引的字段的情况下以及符合使用规则的时候就会使用索引。

索引的分类(按照类型)

B-Tree索引

特点

B-Tree上的值都是有序存储的，并且每个叶子页到根节点的距离都相同。

叶子节点的指针指向的是被索引的数据而不是其他的节点页。

检索原理

首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或未找到节点返回null指针。

缺点

1.插入删除新的数据记录会破坏B-Tree的性质，因此在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质。会造成IO操作频繁。

2.区间查找可能需要返回上层节点重复遍历，IO操作繁琐。

B+Tree索引

特点

相比B-Tree，B+Tree在原有基础上，还有以下不同点：

非叶子节点不存储data，只存储索引key；只有叶子节点才存储data。

MySQL的B+Tree

在经典B+Tree的基础上增加了顺序访问指针优化。

在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。

提高了范围查询性能：

如果要查询key为从10到50的所有数据记录，当找到10后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，

极大提到了区间查询效率。

因为无需像B-Tree那样返回上层父节点重复遍历查找减少IO操作。
结构如下:

为什么Mysql选择B+TREE索引? B+TREE索引有什么好处?

索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，

所以索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数，提升索引效率，

而相比B-Tree,B+Tree又做了优化，索引选择B+Tree。

B-/+Tree索引的性能优势

一般使用磁盘I/O次数评价索引优劣。

1.结合操作系统存储结构优化处理： mysql巧妙运用操作系统存储结构(一个节点分配到一个存储页中->尽量减少IO次数)

& 磁盘预读(缓存预读->加速预读马上要用到的数据).

2.B+Tree 单个节点能放多个子节点，相同IO次数，检索出更多信息。

3.B+TREE 只在叶子节点存储数据 & 所有叶子结点包含一个链指针 & 其他内层非叶子节点只存储索引数据。

只利用索引快速定位数据索引范围，先定位索引再通过索引高效快速定位数据。

高速定位的原理

Mysql设计利用了磁盘预读原理，将一个B+Tree节点大小设为一个页大小，在新建节点时直接申请一个页的空间，

这样就能保证一个节点物理上存储在一个页里，加之计算机存储分配都是按页对齐的，这样就实现了每个Node节点只需要一次I/O操作。

4.B-Tree索引、B+Tree索引： 单个节点能放多个子节点，查询IO次数相同(mysql查询IO次数最多3-5次-所以需要每个节点需要存储很多数据)

5.B+Tree更适合外存索引，原因和内节点出度d有关。从上面分析可以看到，d越大索引的性能越好，而出度的上限取决于节点内key和data的大小：

6.B+Tree内节点去掉了data域，因此可以拥有更大的出度，拥有更好的性能。只利用索引快速定位数据索引范围，先定位索引再通过索引高效快速定位数据。

dmax=floor(pagesize/(keysize+datasize+pointsize))

B-Tree索引适用于全键值查询、键值范围查询或键前缀查找

能使用索引的查询

有如下表和索引

表结构

索引

数据

全值匹配

指和索引中的所有列进行匹配

SELECT * from `user` t  where t.`user`='1' and t.age= 1;

可见是能使用索引的

匹配最左前缀

只使用索引的第一列

SELECT * from `user` t  where t.`user`='1';

匹配列前缀

只匹配某列的值的开头部分。

SELECT * from `user` t  where  t.`user` like 'd%';

这里优化器走了全表扫描

匹配范围值

可以是一个范围值

SELECT * from `user` t  where  t.age >=1 ;

只访问索引的查询

只访问索引而不需要访问数据行

SELECT * from `user` t  where id=1 ;

B-Tree索引无法使用的情况

1.不是按照索引的最左列开始查找。

2.不能跳过索引中的列。

3.如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引优化查找。

哈希索引

定义

基于哈希表实现，只有精确的匹配索引所有列的查询才会有效。

只在Memory引擎显示支持哈希索引。

实现原理

对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码(hash code),

哈希码是一个较小的值，并且不同的键值的行计算出来的哈希码也不一样。

哈希索引将所有的哈希码存储在索引中，同时在哈希表中保持指向每个数据行的指针。

注意点：如果多列的哈希值相同,索引会以链表的方式存放多个记录指针到同一个哈希条目中。

哈希索引的优缺点

优点

索引结构紧凑，查找的速度非常快。

限制

1.哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。

不过访问内存中的行的速度很快，大部分情况下这一点对性能的影响并不明显。

2.哈希索引数据并不是按照缩影值顺序存储的，所以也就无法用于排序。

3.哈希索引也不支持联合索引的部分索引匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。

4.哈希索引不支持范围查找，只支持等值比较查询，包括=、IN()、<=>。

5.访问哈希索引的数据非常快，除非有很多哈希冲突(不同索引列值却有相同的哈希值)。

这种哈希冲突的情况下存储引擎必须变量链表中所有的行指针，逐行进行比较，直到找到所有的符合条件的行。

6.如果哈希冲突很多，相应的索引维护的代价也就更高了。

InnoDB引擎的自适应哈希索引

当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上在创建一个哈希索引，这样就让B-Tree索引也具备哈希索引的一些优点。
这完全是一哥自动的、内部的行为,用户无法控制

空间数据索引(R-Tree)

MyISAM表支持空间索引，可以用作地理数据存储。

空间索引会从所有维度来索引数据。

全文索引

是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。

其他类别索引

如TokuDB使用分形树索引。这是一种新开发的数据结构，既有B-Tree的优点，又避免了B-Tree的一些缺点。

索引的分类(按照种类)

唯一索引

加速查询 + 列值唯一（可以有null）

主键索引(聚簇索引)

加速查询 + 列值唯一（不可以有null）+ 表中只有一个

普通索引(非聚簇索引)

仅加速查询

全文索引

对文本的内容进行分词，进行搜索

组合索引

多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

聚簇索引和非聚簇索引(又称聚集索引和非聚集索引)

聚簇索引定义

索引 和 数据文件为同一个文件。

并不是一致单独的索引类型，而是一种数据存储方式。

具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

当表有聚簇索引时，它的数据行实际存放在索引的叶子页中。

术语“聚簇”表示数据行和相邻的键值紧凑的存储在一起。

数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

聚簇索引的特点和优势

索引的叶子节点就是对应的数据节点(MySQL的MyISAM除外，此存储引擎的聚集索引和非聚集索引只多了个唯一约束，其他没什么区别)，

可以直接获取到对应的全部列的数据，而非聚集索引在索引没有覆盖到对应的列的时候需要进行二次查询(回表)，后面会详细讲。

因此在查询方面，聚簇索引的速度往往会更占优势。

聚簇索引页分裂问题

基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。

当行的主键值要求必须将这一行插入到某个已满的页中，存储引擎会将该页分裂为两个页面来容纳该行，这就是一次页分裂操作。

页分裂会导致表占用更多的磁盘空间。

非聚簇索引的定义

索引 和 数据文件分开的索引。

该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。类似汉语字典的偏旁查找，乱序的

非聚簇索引的二次查询问题(回表)

非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，如果使用非聚集索引查询，

而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。

解决二次查询的方法

组合索引（覆盖索引）

建立两列以上的索引，即可查询复合索引里的列的数据而不需要进行回表二次查询，如index(a, b)，执行下面的语句

select a, b from t1 where col1 = 'dq';

要注意使用复合索引需要满足最左侧索引的原则，也就是查询的时候如果where条件里面没有最左边的一到多列，索引就不会起作用。

在SQL Server中还有include的用法，可以把非聚集索引里包含的列包含进来，而不一定需要建立复合索引。

myisam和innodb的存储引擎对索引的实现

MyISAM

data存的是数据地址。索引是索引，数据是数据。索引放在XX.MYI文件中，数据放在XX.MYD文件中，所以也叫非聚集索引。

InnoDB

data存的是数据本身。索引也是数据。数据和索引存在一个XX.IDB文件中，所以也叫聚集索引。

聚簇索引和非聚簇索引的使用建议

1.使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。

2.非聚集索引在查询的时候可以的话就避免二次查询，这样性能会大幅提升。

3.不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能会更好。

索引的优点和缺点

优点

1.索引大大的减少了服务器需要扫描的数据量。

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

3.索引可以将随机I/O变为顺序I/O。

缺点

1.多少会占用磁盘空间。

2.对插入和修改会维护对应的索引，会消耗一定的性能

三星索引的判定

一星索引：索引将相关的记录放在一起。

二星索引：索引中的数据顺序和查找中的排列顺序一致。

三星索引：索引中的列包含了查询中需要的全部列。

索引失效的几种情况

1.like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。

2.or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效.

3.组合索引，不是使用第一列索引，索引失效。

4.数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。

5.在索引列上使用 IS NULL 或 IS NOT NULL操作。

索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可。

6.在索引字段上使用not,<>,!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。

优化方法： key<>0 改为 key>0 or key<0。

7.对索引字段进行计算操作、字段上使用函数时索引无效。

8.当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

参考MYSQL-B+TREE索引原理

参考高性能MySQL(第三版)