MySQL数据库的索引

目录

1.介绍

1.1概念

1.2例子

1.2.1没有索引的情况

1.2.2有索引的情况

1.3索引的优缺点

2.结构

2.1B+Tree（多路平衡搜索树）

2.1.1特点:

2.1.2b+树的搜索流程：

3.sql语法

---

提升数据库查询效率最有效的方式是索引。

1.介绍

1.1概念

索引（index）是帮助数据库高效获取数据的数据结构。

1.2例子

首先看看有索引和无索引的情况，数据库分别是怎么搜寻数据的：

对于这样一张表：

搜寻年龄大于45的员工信息：       select from emp where age=45

1.2.1没有索引的情况

从第一行数据开始匹配查找，首先判断第一个age的值是不是45，如果不是，继续判断第二行的age是不是45，如果还不是，继续往后找，直到找到age是45的那一行数据；如果这个age字段不是唯一的，还需要继续往后找有没有45的数据，直到找到最后一行数据。

以上这种情况称为全表扫描，性能非常低，数据量越大，性能越低。

1.2.2有索引的情况

如果为age年龄这个字段建立了索引，就会维护一个索引对应的数据结构，这个数据结构会大大提高查询数据的效率。

在数据结构中，树形结构似乎是合适的选择。

接下来，用二叉搜索树为例讲解一下数据库是如何通过树形结构提高查询效率的。

在构建树形结构的时候，树形结构的每一个结点都会去关联数据库中的原始数据，比如36这个结点就会关联36对应的这一行的原始数据。

对于二叉树，左子树的结点要小于根节点，右子树的结点大于根结点，从而构建出这样一个二叉树

这样，查询的时候，就不需要再遍历整张表了。比如要搜索age为45的数据，那么只需要拿45与根节点进行比较，45>36，所以会走右侧找到36的右子树；然后再和48进行对比，45<48，走左侧，就找到了45这个结点，而45这个结点又和原始的数据进行过关联，最终就找到了原始的数据记录。也就是说，有索引之后，不用进行全表扫描，而只需要在索引结构中进行少量的对比来找到对应的数据，这个过程非常高效。

虽然构建索引也会耗时，但这是一次性的操作，索引建好后索引结构也就建立好了，之后查询的时候只需要进行数据的比对即可。

这里演示的二叉搜索树仅仅是一个示意图，用于帮助大家理解，并不是真正的MySQL底层的索引结构。

1.3索引的优缺点

索引是一种数据结构，这种数据结构是要存储在磁盘文件中的，占用磁盘空间。

索引虽然提高了查询的效率，但是却降低了增删改的效率。原因是增删改操作会对数据造成变化，导致要重新维护索引结构。

但以上这两个缺点都可以忽略不计。1.对于现在的企业服务器，磁盘空间都是以T计数，索引本身占用的磁盘空间也不算大。2.对正常的业务系统来说，查询占到90%以上，增删改的频率是很低的，所以在进行业务操作的时候主要考虑的是对查询功能的优化。

2.结构

MySQL数据库支持的索引结构有很多，如:Hash索引、B+Tree索引、Ful-Text索引等。我们平常所说的索引，如果没有特别指明，都是指默认的 B+Tree 结构组织的索引。

数据库并没有采用二叉搜索树或者二叉平衡树（红黑树是一种二叉平衡树），因为大数据量的情况下，会导致层级较深，检索速度慢，因为它们一个结点下都只能有2个结点而已，树的高度一旦过高，检索速度就会降低，而B+树可以比较好地规避这个问题。

2.1B+Tree（多路平衡搜索树）

2.1.1特点:

1.在一个结点中可以存储多个key，每一个key对应一个指针，n个key就有n个指针，也就表面这个结点会有n个子结点，所以多路就是指多个子结点。

2.非叶子结点（至少有一个子结点的结点）只起到索引数据、查找数据的作用，并不保存具体的数据。所有的数据都是在叶子结点进行保存的，所有的key也都会出现在叶子结点（比如6、38、67都能在叶子结点中找到），而且在叶子结点中会保存这个key对应的数据，绿色的部分就是对应的数据。

3.叶子结点的key是按从小到大的顺序进行排序的，而且形成了一个双向链表，便于数据的排序和区间范围查询。

2.1.2b+树的搜索流程：

比如查找53，从根结点开始对比，介于38和67之间，会走p2指针；再到p2指针指向的磁盘块进行查找，介于47到55之间，还是走p2指针；之后进入p2指针指向的磁盘块进行查找，最终定位到53以及53对应的数据。

比起二叉树或者是红黑树，树的高度就会矮很多，最终形成的是一个矮胖矮胖的树，一般是3~4层。注意，无论要找哪一条数据，最终都要到叶子结点中才能拿到对应的数据。

3.sql语法

1.创建索引
create [unique] index 索引名 on 表名(字段名,... );

索引名一般写为idx_表名_字段名，创建唯一索引可以加上关键字unique

如：create index idx_emp_name on tb_emp(name);
2.查看索引
show index from 表名;

若指定了字段为主键，数据库会默认创建主键索引，所有也会查出表中的主键索引，主键索引是所有所有当中性能最高的。

若指定了字段为唯一的，数据库会默认创建唯一索引，所有也会查出表中的唯一索引。

添加的unique唯一约束，本质就是添加了一个唯一的索引。

如：show index from tb_emp;
3.删除索引
drop index 索引名 on 表名;

如：drop index idx_emp_name on emp;