MySQL之索引结构与增删查

MySQL之索引

索引结构

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

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

---

索引	InnoDB	MyISAM	Memory
B+tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
B-tree索引	不支持	支持	不支持
Full-text	5.6版本后支持	支持	不支持

我们平时所说的索引，如果没有特别说明，都是指B+树组织的索引。

---

二叉树

二叉树缺点：

顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。 红黑树: 大数据量情况下，层级较深，检索速度慢。

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

以一颗最大度数(max-degree)为5(5阶)的b-tree为例(每个节点最多存储4个key，5个指针):

 

注意：树的度数指的是一个节点的子节点个数。

B+Tree

以一颗最大度数(max-degree)为4 (4阶)的b+tree为例:

 

 

相对于B-Tree区别:

1. 所有的数据都会出现在叶子节点

2. 叶子节点形成一个单向链表

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

---

 

Hash

简介：

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中,如果两个(或多个)键值映射到一个相同的槽位上，他们就产生了hash冲突 (也称为hash碰撞)，可以通过链表来解决。

 

Hash索引特点

1. Hash索引只能用于对等比较(=，in)，不支持范围查询 (between，>，<，...)

2. 无法利用索引完成排序操作

3. 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

存储引擎支持

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

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

1. 相较于二叉树，层级更少，搜索效率高。

2. 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。

3. 相较于Hash索引，B+tree支持范围匹配及排序操作。

---

 

索引分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

---

---

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

分类	含义	特点
聚焦索引	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有并且只有一个
二级索引	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

---

 

---

思考

以下SQL语句，那个执行效率高?为什么?

select * from user where id = 10; 
​
select * from user where name = 'Arm';
​

查id的效率高，因为查name的要先查出id，再回表查询row

---

索引语法

创建索引

	CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name.... ) ;

查看索引

　　SHOW INDEX FROM table_name;

删除索引

　　DROP INDEX index_name ON table_name ;

---

按照下列的需求，完成索引的创建

1. name字段为姓名字段，该字段的值可能会重复，为该字段创建索引.

2. phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。

3. name字段为姓名字段，该字段的值可能会重复，为该字段创建索引

4. phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。

CREATE INDEX idx_user_name ON tb_user(name);
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);
CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);
CREATE INDEX idx_email ON tb_user(email);