MySQL索引详解

1.详解B+树索引

树,二叉树,平衡二叉树,B树,B+树的区别

1.树与二叉树的区别

二叉树是指树中节点的度不大于2的有序树

2.平衡二叉树

左右子树的高度相差不超过 1 的树为平衡二叉树。

3.B树

B树与平衡二叉树的区别在于B数的每个节点下可以有多个子数

3.B+树

1.B+跟B树不同B+树的非叶子节点不保存关键字记录的指针，只进行数据索引，这样使得B+树每个非叶子节点所能保存的关键字大大增加
2.B+树叶子节点保存了父节点的所有关键字记录的指针，所有数据地址必须要到叶子节点才能获取到。所以每次数据查询的次数都一样
3.B+树叶子节点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针
4.非叶子节点的子节点数=关键字数

4.为何使用B+树

1.B树应用到数据库中的时候，数据库充分利用了磁盘块的原理,磁盘数据存储是采用块的形式存储的，每个块的大小为4K，每次IO进行数据读取时，同一个磁盘块的数据可以一次性读取出来,把节点大小限制和充分使用在磁盘快大小范围；把树的节点关键字增多后树的层级比原来的二叉树少了，减少数据查找的次数和复杂度
2.B+树的层级更少：相较于B树B+每个非叶子节点存储的关键字数更多，树的层级更少所以查询数据更快
3.B+树查询速度更稳定：B+所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同所以查询速度要比B树更稳定
4.B+树天然具备排序功能：B+树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比B树高
5.B+树全节点遍历更快：B+树遍历整棵树只需要遍历所有的叶子节点即可，，而不需要像B树一样需要对每一层进行遍历，这有利于数据库做全表扫描\

5.B树的优点

B树相对于B+树的优点是，如果经常访问的数据离根节点很近，而B树的非叶子节点本身存有关键字其数据的地址，所以这种数据检索的时候会要比B+树快

2.索引种类

1.普通索引

仅加速查询

2.唯一索引

加速查询 + 列值唯一（可以有null）

3.主键索引

加速查询 + 列值唯一（不可以有null）+ 表中只有一个组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

4.全文索引

对文本的内容进行分词，进行搜索

5.其他说明

索引合并，使用多个单列索引组合搜索
覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

3.索引类型

1.全文索引

FULLTEXT即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引

2.HASH索引

由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。 HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高

3.BTREE索引

BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中，每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型

4.RTREE索引

RTREE在MySQL很少使用，仅支持geometry数据类型。相对于BTREE，RTREE的优势在于范围查找。

4.索引使用

1.创建普通索引

CREATE INDEX index_name ON table_name(col_name);

2.创建唯一索引

CREATE UNIQUE INDEX index_name ON table_name(col_name);

3.创建普通组合索引

CREATE INDEX index_name ON table_name(col_name_1,col_name_2);

4.创建唯一组合索引

CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

5.修改表结构时创建索引

ALTER TABLE table_name ADD INDEX index_name(col_name);

6.创建表时指定索引

CREATE TABLE table_name (
    ID INT NOT NULL,col_name VARCHAR (16) NOT NULL,INDEX index_name (col_name)
);

7.直接删除索引

DROP INDEX index_name ON table_name;

8.修改表结构删除索引

ALTER TABLE table_name DROP INDEX index_name;

9.查看索引

show index from table_name;

5.索引未命中的情况

1.like关键字情况

使用like关键字时,以%开头时不会命中索引

示例

select * from student where userName like '%三'

2.字符串类型需要添加引号

当列类型是字符串类型时,需要在字符串前后添加引号,不然会被识别成其他类型,从而不走索引

示例

select * from student where phone=183333;

3.使用函数,也不会走索引

示例

select * from student where age-1=19

4.取反操作也不会走索引

采用 not in, not exist，!=, <> , is null , is not null 不会命中索引

更新时间 2022年3月7日
更新时间 2022年3月7日