MySQL进阶--索引详解

索引的详解

索引是一种特殊的数据结构，它包含了对数据表里的记录的指针，添加索引可以加速查询。

类似于字典的目录。我们创建索引，会创建出一个特殊的文件来存储，所以我们对查询频繁的表或者字段添加索引。

（一）索引的种类

根据数据库的功能，可以在数据库设计中创建三种索引：唯一索引、主键索引普通索引。

• 普通索引 index

​ – 普通索引 就是一个普通的索引，可以为空，可以重复。

• 唯一索引 unique (唯一约束)

​ – 可以为空，不可以重复。

• 主键索引 primary key （主键约束）

​ – 不可以为空，不可以重复

（二）索引的优缺点

建立索引的目的是加快对表中记录的查找或排序。

为表设置索引要付出代价的：

​ 一是增加了数据库的存储空间，

​ 二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录 。

*索引的优点：创建索引可以大大提高系统的查询性能。*

​ 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

​ 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

*索引的缺点：增加索引也有许多不利的方面*

​ 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

​ 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

​ 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

降低数据的添加 修改 删除的 效率

什么时候去添加索引：

​ 当数据量比较大的时候

​ 给不经常更新的字段添加索引

​ 给经常用与 查询条件的字段添加索引

​ 保证当前字段的重复数据不能大于总数据的 20%

（三）索引的底层实现原理

从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引，但是MySQL的InnoDB数据库引擎，只支持B+Tree索引。

1、 HASH索引

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

Hash索引能最快速的尽心数据的定位，对于固定数据的查询效率是最高的，但是如果是范围查询的情况，由于Hash索引的不连续性，查询性能会下降。

2、 B-Tree索引

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

降低io,可以以少量的io操作找到我们的数据

3、 B+Tree索引

是B-Tree的改进版本，同时也是MySQL数据库索引索引默认的存储结构。数据都在叶子节点上，并且通过链表增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

数据存储在 叶子节点，每一个叶子节点中会存储下一个叶子节点的地址

（四）索引的使用

普通索引 ALTER TABLE 表名 ADD INDEX(字段名);

唯一索引 ALTER TABLE 表名 ADD UNIQUE(字段名);

主键索引

主键本身就是主键索引，我们创建表，添加了主键，就相当于添加了主键索引

ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

删除索引

DROP INDEX 索引名 ON 表名;

ALTER TABLE 表名 DROP INDEX 索引名;

删除主键索引需要先删除主键的自增属性

ALTER TABLE 表名 DROP PRIMARY KEY

查看索引

SHOW INDEX FROM student;