数据库索引详解-CSDN博客

本文链接：https://blog.csdn.net/StringBuff/article/details/107641048

听同事提起索引，只知道是一种类似树的结构，可以大幅提高查询效率，但是具体原理却不懂，所以搜索资料学习一下。

一、索引分类

1.主键索引：根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；

ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col');

2.唯一索引：用来建立索引的列的值必须是唯一的，允许空值；

ALTER TABLE 'table_name' ADD UNIQUE index_name('col');

3.普通索引：用表中的普通列构建的索引，没有任何限制

ALTER TABLE 'table_name' ADD INDEX index_name('col');

4.全文索引：用大文本对象的列构建的索引

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；

5.组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值；

-- 遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减，
-- 组合索引相当于建立了col1,col1col2,col1col2col3三个索引
-- 而col2或者col3是不能使用索引的。
ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

如果组合索引列，列名比较长，可以采用截取的方式：

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；

二、索引原理

MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，B+Tree索引，哈希索引，全文索引等等。

1、哈希索引：

只在memory（内存）存储引擎支持，哈希索引使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。

2、全文索引：

FULLTEXT（全文）索引，只能用于MyISAM 和 InnoDB（5.6版本之后）。

具体参考：全文索引的深入理解

-- 1.创建表的时候添加FULLTEXT索引
CTREATE TABLE my_table(
    id INT(10) PRIMARY KEY,
    name VARCHAR(10) NOT NULL,
    my_text TEXT,
    FULLTEXT(my_text)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 2.创建表以后，在需要的时候添加FULLTEXT索引
ALTER TABLE my_table ADD FULLTEXT INDEX ft_index(column_name);

-- 3.查询方式
SELECT * FROM table_name MATCH(ft_index) AGAINST('查询字符串');

3、B-Tree索引

BTree又叫平衡多路查找树，一棵m（即任何一个节点中元素的数量<m）阶的B 树 (m叉树)的特性如下：

每个叶子结点的高度一样；
树中每个结点最多含有m-1个孩子（m>=2）；
除根结点和叶子结点外，其它每个结点至少有[ceil(m / 2)]个孩子（其中ceil(x)是一个取上限的函数）；
若根结点不是叶子结点，则至少有2个孩子（特殊情况：没有孩子的根结点，即根结点为叶子结点，整棵树只有一个根节点）；
所有叶子结点都出现在同一层，叶子结点不包含任何关键字信息(可以看做是外部接点或查询失败的接点，实际上这些结点不存在，指向这些结点的指针都为null)；
每个非终端结点中包含有n个关键字信息： (P1，K1，P2，K2，P3，......，Kn，Pn+1)。其中：
a) Ki (i=1...n)为关键字，且关键字按顺序升序排序K(i-1)< Ki。
b) Pi为指向子树根的接点，且指针P(i)指向子树种所有结点的关键字均小于Ki，但都大于K(i-1)。
c) 关键字的个数n必须满足： [ceil(m / 2)-1]<= n <= m-1（这个特别重要，记住了）

详情参考：BTree深入学习

以下是我学习这篇博客的总结：

1.添加元素时，无非是满了就分裂（将一个中间元素送给父节点）

2.删除时需要考虑的比较多，但是需要遵循几个原则：

叶节点元素删除完之后，依然满足要求，只需正常删除，然后把该元素后边的元素迁移即可；
非叶节点删除，删除元素时，需要将该节点下边子节点的对应元素挪上来补上空缺，因为我们要维护父节点到子节点的指针。
叶节点删除时，先借用相邻丰满节点数据（从父节点借数据，然后将相邻节点数据补给父节点）；
如果叶节点不满足条件三，走该逻辑。两个节点合并。
1. 向父节点借一个元素，补给缺少数据的节点
2. 将该节点和相邻节点合并，并将父节点的指针指向合并后的节点。