MySQL~B+树索引(聚集索引与非聚集索引)、联合索引、覆盖索引、哈希索引、全文索引

索引

• 在数据库中，表、数据、索引之间的关系就类似于书籍、书籍内容、书籍目录。

• 倘若不使用索引，则MySQL必须遍历整个表，直到找到数据，而表越大，查询的时间则越长，则数据库的效率也就越低。而索引就类似于书籍的目录，可以帮助我们快速的定位、检索到需要的数据行，对提高数据库的性能有着很大的帮助。

• 在MySQL中，索引是一种特殊的文件，其中包含着对数据表中所有记录的引用指针。各类索引有各自的数据结构实现。

索引使用场景

• 索引不是越多越好, 他只会提高查找效率, 删除修改, 增加的效率只会降低
• 不要给经常变更的数据加索引
• 小数据量有没有必要加索引
• 索引一般用在查询上, 在增加删除修改方便索引的效率是降低的, 所以用在差多改少的情况
• 如果某一个字段选择性很少, 比如性别, 类型, 他们的取值范围很小, 也不适合添加索引

B+树索引

• B+树索引是最为常见的索引, 想要理解B+树索引先体会一下其B+树的发展由来
• 最初是我们最熟悉的二分查找, 又称为折半查找, 对排好序的数据, 我们采用折半比较的方法可以极大的减少数据比较的次数, 后来就是二叉查找树,也就是二叉搜索树, 其中序遍历是有序的, 但是由于是二叉, 如果数据较多的时候树的深度太大, 而且如果数据极端其效率很低, 不利于数据库中使用, 后来使用平衡二叉树防止了极端数据但是树的深度还是很大, 而且维护平衡很麻烦, 再就是产生B树, B树应其为n叉, 有效的降低了树的深度, 而且还能保证数据的有效性, 对B树再次优化就产生了B+树, B+树的数据使用双向链表连接, 对范围查找非常优好,所以InnoDB采用B+树作为最主要的索引数据结构.
• InnoDB中的B+树索引有俩种形式, 一个是聚集性一个是非聚集型, 其最主要的区别就是在叶子节点上存放的是不是完整的行记录数据(完整的行记录数据就是一张表中的完整的一行数据)

聚集索引

• 聚集索引其实本质是叶子节点中存放的是整张表的行记录数据, 叶子节点那一层存储的是页，而具体的某一个叶子节点才是存储行记录数据
• 由于InnoDB存储引擎中表的数据按照主键顺序存放，所以聚集索引也就是按照每张表的主键来构造出一颗B+树。由于数据真正的排序方式只能有一种，所以在每张表中只能存在着一个聚集索引。
• 这颗B+树的非叶子节点存放的是数据的索引，而叶子节点存放的即为整张表的行记录数据，所以我们通常也将叶子节点称为数据页，并且每个叶子节点之间用双向链表进行连接。
  
• 聚集索引对于主键的排序查找和范围查找速度非常快，并且由于叶子节点就是数据，所以只需要查找一次就可以得到结果

非聚集索引(辅助索引)

• 对于非聚集索引（辅助索引）来说，叶子节点并不包含行记录的全部数据，而是包含了键值和一个指针，这个指针所指向的就是我们需要的数据在聚集索引中的位置, 我们需要在非聚集索引中查找到对应键值和其指针，再通过指针在聚集索引中查找到具体的完整数据，也就是需要两次查找。
  
• 基于以上特性，由于辅助索引的存在并不会影响数据在聚集索引中的组织，因此每张表中可以有多个辅助索引
• 创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。

B+树索引管理

• 创建和删除索引的方式有俩种, 一种是ALTER TABLE, 还有一种是CREATE / DROP INDEX
• 查看索引

SHOW INDEX FROM 表名

• 创建

CREATE INDEX 索引名 ON 表名（字段名）
或者
ALTER TABLE 表名 ADD {KEY | INDEX} 索引名

• 删除

DROP INDEX 索引名 ON 表名

Cardinality值

• 在我们show index from XXX\G; 可以看到Cardinality的值, 这个值表示索引唯一值的数据估计数, 如果这个数据很小, 就表示这个索引下的数据的重复度很高, 不应该设置这个索引

联合索引

• 联合索引即对表上的多个列进行索引，例如下图
  
• 联合索引和普通的B+树不同的地方在于它具有多个键值，虽然键值有序，但是是按照从左往右的优先级以此对键值的大小进行排序，例如上图有两个键值，首先会先按照第一个键值的大小进行排序，当第一个键相同时，再按照第二个键的大小进行排序。
• 对于查询语句为 select * from student where a=XXX and b= XXX;很有帮助, 但是上面说到叶子中键值的排序是以第一个数据为主, 第二个数据为辅, 那么如果只查第二个数据就会出现前缀匹配规则

前缀匹配规则

• 就以上述数据进行举例，此时以键值（a, b）构建联合索引
• 三条对比匹配查询

SELECT * FROM TABLE WHERE a = xxx AND b = xxx 、
SELECT * FROM TABLE WHERE a = xxx 
SELECT * FROM TABLE WHERE b = xxx 

• 对于以上几条查询语句来说，虽然看起来差不多，但是效率却大相径庭。

• 对于前两句来说，它们是可以使用联合索引的，因为无论是按照a作为条件，或者是a和b作为条件，都可以利用索引进行搜索，因为前面也说过了，联合索引的排序是按照从左往右优先的，所以当查找条件中包含a的时候则不会出现问题。

• 但对于b=xxx则无法适用以上性质，因为在联合索引中，后面的主键只有在前面的主键相同时才会具有有序性，而单独适用它的时候显然数据是无序的，所以这时只能进行行全索引扫描。

• 对于范围查找也是这么一个道理，假设此时联合索引的主键为（a, b, c)

SELECT * FROM TABLE WHERE a > 3 AND b > 3
SELECT * FROM TABLE WHERE a > 3 AND b > 3 AND c > 3 
SELECT * FROM TABLE WHERE a > 3 AND c > 3

• 同样按照上面的规则，前两种查询都可以适用联合索引，因为其遵循了从左至右的匹配原则，而第三条因为跳过了b，此时的数据是无序的，无法适用索引。

覆盖索引

• 覆盖索引即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处就是由于辅助索引中不包含整行的所有记录，所以它的大小要远远小于聚集索引，因此可以减少大量的I/O操作。

• 由于辅助索引中叶子节点存放的数据就是键值，所以当我们要查找键值，或者通过键值来统计数量的时候，就可以使用覆盖索引来完成。

• 但是在实际开发中, 使用范围查找或者join连接查找的时候, 如果查找的数据很小, 还是会使用辅助索引, 但是在数据较大的时候,优化器会选择通过聚集索引来进行查找数据,因为使用辅助索引的时候如果在需要大量指针进行映射到聚集索引的时候, 效率是很低的, 毕竟殊勋查询比离散查询要好很多

哈希索引

• 哈希索引是基于哈希算法实现, 哈希算法是很常见的一种算法, 其时间复杂度为O(1), 对于查找效率来说是很高效的.
• 由于哈希是直接通过哈希值来将数据映射到对应位置，所以哈希索引对于等值查询的效率特别高，但是也正因为这个特性，使得哈希查找在面对范围查找的时候就毫无用武之地了。
• 哈希索引的原理其实就是通过除留余数法将键值转换为哈希值，并将数据存储对应的槽中，如果出现了哈希冲突，则使用链地址法进行解决，将数据插入对应槽中的链表。

自适应哈希索引

• 在InnoDB存储引擎会监控对表上各个索引页的查询，如果它观察到建立哈希索引可以带来速度提升，则会自行建立哈希索引，这也就是自适应哈希索引。即会自动根据访问频率和模式来为热点数据建立哈希索引。

• 由于哈希索引是数据库自身自动创建并使用的，人工无法对其进行干预

全文索引

• 全文索引即根据部分段落、句、词从数据库中查询除全文的技术，即根据部分查询词获取对应的文档。其主要使用的是倒排索引实现

倒排索引

• InnoDB使用关联数组拥有两种倒排索引的表现形式

1. inverted file index（倒排文件索引）
   表现形式 {单词， 单词所在的文档ID）
2. full inverted index（全文倒排索引）
   表现形式{单词, (单词所在文档ID，在具体文档的位置）

• 例如我们存在以下数据
  
• 并将其构建成inverted file index（倒排文件索引）的形式
  
• 接着建立full inverted index（全文倒排索引）
  
• 此时在之前的基础上，我们不仅确定了单词所在的文章，还确定了其所在文章中的对应位置，虽然比起inverted file index来说更加复杂，占据的空间也更多，但是却能更好的定位数据，并且扩充一些其它的搜索特性
• 上面俩张表也叫全文索引的辅助表, 要清除, 数据是放在原文索引表中的, 而关联数组只是复制实现倒排索引的

全文索引的使用

• 建表

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| hobby | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

• 创建全文索引

mysql> create fulltext index ix_ft_student_name_hobby on student(name,hobby) WITH PARSER ngram;
Query OK, 0 rows affected, 1 warning (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 1

CREATE FULLTEXT INDEX 索引名 ON 表名(字段名) WITH PARSER ngram;

• 查找

MATCH(要匹配的列) AGAINST(要查找的内容) 

• 自然语言查询（Natural Language）
  自然语言查询即普通的包含关键词的搜索，例如

mysql> select * from  student where MATCH(name, hobby) AGAINST ('ke' IN Natural Language);
+----+--------+------------+
| id | name   | hobby      |
+----+--------+------------+
|  3 | flake  | swim       |
|  4 | mike   | football   |
|  6 | listen | basketball |
|  7 | bike   | pingPong   |
|  8 | flake  | swim       |
+----+--------+------------+
5 rows in set (0.00 sec)

• Boolean
  这个模式允许使用IN BOOLEAN MODE修饰符来进行全文检索，当使用该修饰符时，查询字符串的前后字符都会有特殊含义。例如+和-分别代表了该单词必须出现或者一定没出现

这句查询即匹配包含查询词1并且不包含查询词2的结果

SELECT * FROM 表名 
WHERE MATCH (要匹配的列) AGAINST ('+查询词1 -查询词2' IN BOOLEAN MODE);

• 该模式所有选项如下
  
• 全文查询的结果是依据相关度来进行降序排序，相关度计算依据如下

查询词是否在文档中出现过
查询词在文档中出现的次数
查询词在索引列中的数量
包含查询词的文档数