MySQL学习总结-索引概述

索引是排好序的快速查找数据结构！索引会影响where后⾯的查找，和order by 后⾯的排序。

1、索引分类

全局索引(FULLTEXT)： 目前只有 MyISAM 引擎支持全局索引，它的出现是为了解决针对文本的模糊查询效率较低的问题。

哈希索引(HASH)： 哈希索引是 MySQL 中用到的唯一 key-value 键值对的数据结构，很适合作为索引。HASH 索引具有一次定位的好处，不需要像树那样逐个节点查找，但是这种查找适合应用于查找单个键的情况，对于范围查找，HASH 索引的性能就会很低。

B-Tree 索引： B 就是 Balance 的意思，BTree 是一种平衡树，它有很多变种，最常见的就是 B+ Tree，它被 MySQL 广泛使用。

R-Tree 索引： R-Tree 在 MySQL 很少使用，仅支持 geometry 数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种，相对于 B-Tree 来说，R-Tree 的优势在于范围查找。

2、索引的底层实现

2.1、hash索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每⼀⾏数据，存储引擎都会对所有的索引列计算⼀个哈希码 （hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据⾏的指针。

2.2、B-Tree索引

B-Tree能加快数据的访问速度，因为存储引擎不再需要进⾏全表扫描来获取数据，数据分布在各个节点之中。

2.3、B+Tree索引

是B-Tree的改进版本，同时也是数据库索引索引所采⽤的存储结构。数据都在叶⼦节点上，并且增加了顺序访问指针；

3、存储引擎中的索引

3.1、InnoDB引擎

InnoDB引擎数据文件本身就是个索引文件，就是主键key，然后叶子节点的data就是那个数据的所在行。

所以InnoDB引擎要求必须有主键，会根据主键建立一个默认索引，叫做聚簇索引

如果对某个非主键的字段创建个索引，那么建立的索引表最后那个叶子节点的值就是主键的值，因为可以用主键的值到聚簇索引里根据主键值再次查找到数据，即所谓的回表，

3.2、MyISAM引擎

MyISAM引擎每个叶子节点的data存放的是数据行的物理地址；而数据文件单独放一个文件；所以数据文件和索引文件是分开的。

4、索引使用规则

4.1、B树索引

例如表tableA，有列colA，colB，colC，建立一个3列的联合索引 create index (colA,colB,colC)；

1、全值匹配： 即指和索引中所有的列进行匹配
select * from tableA where colA = 1 and colB =‘a’ and colC = ‘呵呵’;

2、最左前缀匹配： 如果你的sql里，正好就用到了联合索引最左边的一个或者几个列表，那么也可以用上这个索引，在索引里查找的时候就用最左边的几个列就行了；
select * from tableA where colA = 1 and colB =‘a’;

3、最左前缀匹配了，但是中间某个值没匹配：
如果你的sql里，就用了联合索引的第一个列和第三个列，那么会按照第一个列值在索引里找，找完以后对结果集扫描一遍根据第三个列来过滤，第三个列是不走索引去搜索的，就是有一个额外的过滤的工作，但是还能用到索引，所以也还好；
select * from tableA where colA = 1 and colC = ‘呵呵’;

4、没有最左前缀匹配： 一定不会用索引
select * from tableA where colB =‘a’;

5、前缀匹配： 即是like操作，那么必须要是like ‘XX%’这种才可以用上索引；
select * from tableA where colA = 1 and colB =‘a’ and colC like ‘呵%’;

6、范围列匹配: 如果是范围查询，比如>=，<=，between操作，只能是符合最左前缀的规则才可以范围，范围之后的列就不用索引了;
select * from tableA where colA >= 1 and colB =‘a’;
这里就在联合索引中根据colA来查询了;

7、包含函数: 如果对某个列用了函数，比如substring之类的东西，那么那一列不用索引
select * from tableA where colA=1 and 函数(colB) = ‘b’;
上面就根据colA在联合索引中查询

4.2、Hash索引

1、必须二次获取具体数据： hash索引只保存hash值和行指针，所以需要通过行指针再次找到具体数据；

2、无法排序： 因为hash索引数据并不是按照索引值顺序存储的；

3、不支持索引列部分匹配： 因为hash索引始终是使用索引列整体计算hash值的；

4、只支持等值比较查询： 包括=、i()、<=>（用于可能存在null的值等于比较）；不支持任何范围查询；

5、hash冲突： 出现hash冲突时，存储引擎必须遍历链表中的所有行指针，导致效率比较低；如果冲突过多，索引维护的代价也很高。