MySql:索引-结构,分类,语法

索引概述

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

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：

• B+Tree：最常见的索引类型，大部分引擎都支持 B+ 树索引
• Hash索引：底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询
• R-tree(空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
• Full-text(全文索引)：是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

注意： 我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引

二叉树和红黑树

B-tree（多路平衡查找树）

B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。
以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5个指针：（树的度数指的是一个节点的子节点个数。）

数据结构可视化的网站演示Btree：https://www.cs.usfca.edu/~galles/visualization/BTree.html

5阶的B树，每一个节点最多存储4个key，对应5个指针。
一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
在B树中，非叶子节点和叶子节点都会存放数据。

比如一个节点（1234 1500 1567 1800）此时再插入1300,1500就变为中间元素，向上分类，这个节点变成两个节点（1234 1300）（1567 1800）

B+Tree

b+树是b树的变种，们以一颗最大度数（max-degree）为4（4阶）的b+tree为例，来看一下其结构示意图：

B+Tree 与 B-Tree相比，主要有以下三点区别：

• 所有的数据都会出现在叶子节点。
• 叶子节点形成一个单向链表。
• 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。

Hash

哈希索引是采用一定hash算法，将键值换算成新的hash值，映射到对应的槽位，然后存储在hash表中.

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

哈希索引特点：

• hash索引只能用于对等比较（=，in），不支持范围查询（between，>,<,.....）
• 无法利用索引完成排序操作
• 查询效率高，通常只需要一次索引就可以了，效率通常高于B+Tree索引

存储引擎支持

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

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

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

索引的分类

索引分类

mysql中，索引的具体类型可以分为以下几类：主键索引，唯一索引，常规索引，全文索引

聚集索引&&二级索引

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

聚集索引选取规则:

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

如上图，聚集索引叶子节点，row存放的是行数据，，，，二级索引，叶子节点关联的是对应的主键！

如果执行 select * from user where name = 'Arm'; ,先在二级索引中查找到 ARM对应的主键为 10 ， 由于查询需要返回的结果是 *  ，所以再到聚集索引中查找10对应的行row. （回表查询）

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

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

索引语法

创建索引：create [unique | fulltext] index_name on table_name (index_col_name,.....);

查看索引：show index from table_name;

删除索引：drop index index_name on table_name;

案例：

show index from tb_user
--1.name字段为姓名字段，该字段的值可能会重复，为该字段创建索引
create index index_user_name on tb_user(name);

--2.phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引.
create unique index index_user_phone on tb_user(phone);

--3.为profession，age, status 创建联合索引
create index index_user_pro_age_sta on tb_user(profession,age, status);

--4.为email建立合适的索引来提升查询效率
create index index_user_email on tb_user(email);