索引

索引（index）是帮助MySQL高效获取数据的数据结构(有序) 。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引优势	索引劣势
提高数据检索的效率，降低数据库的IO成本	索引列也是要占用空间的。（这个可以忽略，占用的空间比较少，并且磁盘相对比较便宜）
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。	索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。（企业大部分的业务都是查询，更新的情况比较少。）

索引结构

一、B+Tree索引

1. 二叉查找树

在最开始学习树的时候，我们一定学习过这样一种结构的二叉树根结点大于它的左节点，小于它的右节点。

2. 平衡二叉树（AVL树）

熟悉二叉树的都知道，在特殊情况下，上面的二叉树可能形成如下结构

平衡二叉树有如下要求：

每个根结点的左节点小于它，右节点大于它
每个结点的左右子树高度差不能超过 1

每次添加元素的时候，代码都会判断当前结构是否还属于平衡的，如果不是就进行调整。调整的代价也是挺大的，所以平衡二叉树一般用于多查询的功能里面。

3. B-Tree

我们所有的数据最终都是存在磁盘上面的。平衡二叉树有一个问题，那就是数据量如果过大的话，那么这个树就会很长很长，这样会导致频繁的进行磁盘IO，这样效率就降低了很多。
在数据库里面数据不是一个个存储，而是按照页来存储，一页是16kb大小。

B-Tree可以解决频繁的IO问题，它把数据按照页进行存储。

假如我们有这样一张表，里面有两个字段 id、name，id是主键

那么它的存储结构如下：

页之间也是双向指向的

从上面的结构我们可以看出，每一页里面存储索引和对应的数据，并且是多条数据，而不是单一的。

一般我们的根页（页1）是存储在内存中的，然后我们进行判断一个个读取页到内存，使用这种结构可以在查询更少的页，就可以查询到我们想要的数据了。

4. B+Tree

上面的结构也存在一种问题，因为每一页的大小是固定的（16KB），如果既要存储索引，又要存储数据，那么我们16KB也存储不了多少索引数据（尤其是在大表中），这样还是会进行频繁的IO处理。

总结

B+Tree 把所有的数据都存储在叶子结点上面，非叶子结点只存储索引，这样可以保证最少次数的IO提高索引查询的性能。
存储的时候不是一个结点一个结点的存储，而是以页的方式进行存储，每一页的大小是16KB。

二、Hash索引

Hash索引就是根据给定的字段，进行创建Hash值。Hash索引可以很快的进行单个匹配度查询，但是无法做到范围查询。

如果你创建组合索引（A、B），它是根据AB俩个字段进行Hash的，所以当你单独使用A进行条件筛选的时候，是无法使用索引的。

哈希冲突

如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

特点

Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）。
无法利用索引完成排序操作。
查询效率高O（1），通常（不存在hash冲突的情况）只需要一次检索就可以了，效率通常要高于B+tree索引。

存储引擎支持

在MySQL中，支持hash索引的是Memory存储引擎。
InnoDB中具有自适应hash功能，hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。

思考题：为什么InnoDB存储引擎选择使用B+tree索引结构?

相对于二叉树，层级更少，搜索效率高；对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页（一页默认是16KB）中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；相对Hash索引，B+tree支持范围匹配及排序操作；

三、全文索引

全文索引是一个比较特殊的索引，一般用的也很少。它查找的是文本中的关键词，而不是比较索引中的值。全文索引更类似于搜索引擎做的事。四、聚簇索引和非聚簇索引聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。在InnoDB的聚簇索引实际上在同一个结构中保存了B+Tree索引和数据行。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。

因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。（不过，覆盖索引可以模拟多个聚簇索引的情况，下面说明）

在InnoDB中会选择主键来作为聚簇索引，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

like问题

1.like关键字‘%obj%’这种写法是不会走索引的，‘obj%’这种使用索引；但是达不到查询效果。
2.FIND_IN_SET（）函数能达到查询效果，但是不会走索引；如果数据量达到几万及以上，效率会非常慢；甚至造成系统无法运行。

问题解决方案：MYSQL全文索引及Match() against()

全文检索在 MySQL 中就是一个 FULLTEXT 类型索引。

一、创建全文索引

格式：
create fulltext index 索引名 on 表名(字段名1,字段名2...);

二、使用全文索引及Match() against()函数

1.基本用法

SELECT a.customer_id,a.customer_no,a.customer_name,a.customer_manager_no,
	   a.customer_manager_name,a.trust_manager_no
from customer_base_info a 
where Match(a.customer_manager_no) Against('wgx2' )

三、使用条件

1.存储引擎必须是MyISAM或者mysql5.6以上的InnoDB
2.字段类型必须是char，varchar，text

四、索引使用分析

1.适用场景
1）表中该字段中的数据量庞大
2）经常被检索，经常出现在where子句中的字段
3）经常被DML操作的字段不建议添加索引

2.优点
1）大大提高检索数据的性能效率
2）在表连接的连接条件，可以加速表与表直接的相连
3）在分组和排序字句进行数据检索,可以减少查询时间中分组和排序时所消耗的时间(数据库的记录会重新排序)

3.缺点 1）创建与维护索引会消耗时间，并随着数据量的增加而增加
2）索引也会占用物理存储空间
3）在进行DML操作的时候，索引也要动态的维护，会降低数据的维护速度

索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

聚集索引、二级索引

而在在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

聚集索引选取规则（无论怎么样都会生成一个聚集索引，不然行数据无法存放）:

如果存在主键，主键索引就是聚集索引。如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。聚集索引和二级索引的具体结构如下：

聚集索引的叶子节点下挂的是这一行的数据 。
二级索引的叶子节点下挂的聚集索引所在字段的值（这里挂的主键id） 接下来，我们来分析一下，当我们执行如下的SQL语句时，具体的查找过程是什么样子的。

具体过程如下:

由于是根据name字段进行查询，所以先根据name='Arm'到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
由于查询返回的数据是*（一行的全部数据），所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。
最终拿到这一行的数据，直接返回即可。
回表查询：这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

思考题：以下两条SQL语句，那个执行效率高? 为什么?

A. select * from user where id = 10;
B. select * from user where name = 'Arm';

备注: id为主键，name字段创建的有索引；
解答：
A 语句的执行性能要高于B 语句。因为A语句直接走聚集索引，直接返回数据。而B语句需要先查询name字段的二级索引，然后再查询聚集索引，也就是需要进行回表查询。

思考题：InnoDB主键索引的B+tree高度为多高呢?

一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键为bigint，占用字节数为8。

高度为2：我们先来计算非叶子节点（只存放key和指针），指针比key多一个。我们设最多可以存储n个key则：

n * 8 + (n + 1) * 6 = 16*1024 , 算出n约为 1170

1171* 16 = 18736

也就是说，如果树的高度为2，则可以存储 18000 多条记录。

高度为3：

1171 * 1171 * 16 = 21939856 也就是说，如果树的高度为3，则可以存储 2200w 左右的记录

参考文献：

MySQl 的B+Tree、哈希、全文、空间数据四种索引介绍

索引