linux mysql 创建索引,Linux_MySQL数据库_5.数据库索引（B树和B+树）

目录：

索引的概念

索引数据结构

二分查找

二叉树索引

哈希索引

B树-多叉平衡搜索树

B+树

主键索引

普通索引

唯一索引(允许空值)

组合索引(多字段)

删除索引

索引的概念

• MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获 取数据的数据结构。

• 查询是数据库最主要的功能之一，我们都希望查询的速度尽可能快。

• 优化：查询算法

索引是什么？

数据结构

作用？

是为了帮助我们高效获取数据

索引数据结构

• 顺序查找

• 二分查找

• 二叉树查找

• 哈希

• B树 B+树

假设有一张数据表：

数据库按行存储

二分查找

O(logn)

要求：

1. 数据有序排放

2. 连续的存储空间

缺点：消耗空间，耗时，需要连续存储，空间磁盘IO次数太多

二叉树索引

O(logn)

通过访问地址拿到一整行数据

特点：消耗空间，但不要求连续空间 ，磁盘IO次数仍然太多(瓶颈)，深度太深

哈希索引

连续存储空间，问题和二分查找类似

索引的检索可以一次定位(O(1))，速度快，效率高于树索引

但是Hash索引本身由于其特殊性也带来了很多限制和弊端，

限制和弊端主要有：

哈希索引只支持等值比较查询 包括 =，in，不支持范围查询

哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序

Hash索引遇到大量Hash值相等的情况后性能并不一定就会比BTree索引高。

在大多数场景下，都会有范围查询、排序、分组等查询特征，用B+树索引就可以了

B树-多叉平衡搜索树

O(logN)

每一个节点最多有 m 个子节点(m阶，m不小于3)

每一个内部节点最少有 ⌈m/2⌉ 个，最多m-1个元素

如果根节点不是叶子节点，那么它至少有两个子节点

有 k 个子节点的非叶子节点拥有 k − 1 个元素

所有的叶子节点都在同一层

其搜索性能等价于在关键字集合内做一次二分查找，因为每个节点中的关键字和左右子树都是有序的，所以只要比较节点中的关键字，或者沿着指针就能很快地找到指定的关键字，如果查找失败，则会返回叶子节点，即空指针

B树的插入

所有的插入都从根节点开始。要插入一个新的元素，首先搜索这棵树找到新元素应该被添加到的叶子节点。将新元素插入到这一节点中的步骤如下：

1. 如果节点未满，将新元素插入到这一节点，且保持节点中元素有序。

2. 否则的话这一节点已经满了，将它平均地分裂成两个节点：

从叶子节点的元素和新的元素中选择出中位数

小于这一中位数的元素放入左边节点，大于这一中位数的元素放入右边节点，中位数作为分隔值。

分隔值(中位数)上提，插入到父节点中，这可能会造成父节点分裂，分裂父节点时可能又会使它的父节点分裂，以此类推。

B树的查找

B树的删除

B+树

非叶子节点不存储data，只存储key

所有的叶子节点存储完整的一份key信息以及key对应的data

每一个父节点都出现在子节点中，是子节点的最大或者最小的元素

每个叶子节点都有一个指针，指向下一个数据，形成一个链表

B+树m阶m子节点，关键字数=指针数

B+树的插入

B+树的查找过程

与B树类似，只不过查找时，如果在非叶子节点上的关键字等于给定值，并不终止，而是继续沿着指针直到叶子节点位置。因此在B+树，不管查找成功与否，每次查找都是走了一条从根到叶子节点的路径。

B+树的删除过程

因为叶子节点有指针的存在，向兄弟节点借元素时，不需要通过父节点了，而是可以直接通过兄弟节移动即可(前提是兄弟节点的元素大于m/2)，然后更新父节点的索引；如果兄弟节点的元素不大于m/2(兄弟节点也没有多余的元素)，则将当前节点和兄弟节点合并，并且删除父节点中的key，

【B树和B+树介绍】

主键索引

主键索引建立的规则是 int 优于varchar，一般在建表的时候创建,最好是与表的其他字段不相关的列或者是业务不相关的列.一般会设为 int 而且是 AUTO_INCREMENT自增类型的。

可以通过以下方式查看索引

//查看索引

SHOW INDEX FROM tablename;

普通索引

1. CREATE INDEX

CREATE INDEX index_name ON `table_name`(`field_name`);

例：

CREATE INDEX index_score ON person(score);

2. ALTER TABLE

ALTER TABLE table_name ADD INDEX index_name(`field_name`);

例：

ALTER TABLE person ADD INDEX index_score2(score);

唯一索引

与普通索引类似，但是不同的是唯一索引要求所有的类的值是唯一的，这一点和主键索引一样。但是他允许有空值

CREATE UNIQUE INDEX index_name ON `table`(`field`);

组合索引

一个表中含有多个单列索引不代表是组合索引，通俗一点讲 组合索引是：包含多个字段但是只有索引名称

CREATE INDEX index_name ON table_name(field1, field2 , ...... );

例：

CREATE INDEX at_sc_index ON person(score, attribute, ...... );

删除索引

ALTER TABLE table_name DROP INDEX index_name；

例：

ALTER TABLE person DROP INDEX at_sc_index;

或者

DROP INDEX at_sc_index on person;