MYSQL学习笔记：索引

文章目录

MYSQL学习笔记：索引
- 索引的分类
- 索引的创建删除
- 索引优化
- B树索引
- B+树
- InnoDB主键和二级索引树
- 聚集索引与非聚集索引
- 哈希索引
- INNODB的自适应哈希索引
- 索引和慢查询

用索引也是要涉及磁盘I/O的操作的
索引也是一种数据结构，对某列建索引的时候都要进行一个排序的，这一列的文件有增加减少修改都是要涉及索引数据的改动操作的

在这里插入图片描述

索引的分类

物理上分为聚集索引和非聚集索引

普通索引：数量不限，可以给任意普通字段创建普通索引，一张表的SQL查询只能用一个索引
唯一性索引：使用UNIQUE修饰的字段，值不能重复，主键索引隶属于唯一性索引
主键索引：使用PRIMARY KEY修饰的字段，会自动创建索引（MYISAM,innoDB（如果没设主键，INNODB会自动创建，因为数据和索引存在一起，没有索引没办法存储数据）)
- 单列索引在一个字段上创建索引
多列索引：在表的多个字段上创建索引（uid+cid，多列索引必须使用第一个列才能用到多列索引，否则用不到）
全文索引：使用FULLTEX修饰的字段可以设置全文索引，支支持char varchar和text类型字段，常用于较大的字符串类型上，可以提高查询速度

索引的创建删除

方法1：在建表的时候建立索引

CREATE TABLE index1(id INT,
name varchar(20),
sex enum ('male','famale'),
INDEX(id,name);

方法2：在已创建的表上创建索引

CREATE [UNIQUE] INDEX 索引名 ON 表名(属性名（length）[ASC|DESC]);
create index pwdindex on user(age);

删除索引：

DROP INDEX 索引名 ON 表名；

加索引后只扫一行：不管数据规模如何，花费的时间一样长
在这里插入图片描述

索引优化

给经常用作where过滤条件的字段加索引
给字符串创建索引索引值尽可能短，不然存储文件大，加载慢
如果过滤条件涉及类型转换或者用了mysql的函数调用表达式计算，就不能用索引了。

password是字符串类型，10000是默认INT类型，如果涉及类型转换或者用了mysql的函数就不能用索引了

B树索引

在这里插入图片描述

如果用m=500（一般取300~500）阶B树最多用----》3层：

最理想的情况是一次磁盘IO读取的磁盘块内容刚好存储在B树的一个节点中
在这里插入图片描述

B+树

为啥用B+树而不是B树
首先看B树的特点：

索引和数据分散在不同的节点上，离根节点近搜索快，离根节点远搜索就慢，花费的IO不平均，每一行搜索花费的时间也不平均
每一个非叶子节点上不仅仅要存储索引（key) 还要存索引所在哪一行的data数据。一个节点能存放索引的key的个数比只存储key的节点少得多
B树很不方便进行范围搜索（例如搜索13~18的数据，既要搜索左子树也要搜索右子树），整表遍历看起来不方便

在这里插入图片描述

在这里插入图片描述
非叶子节点只存储key不存储data，每个节点存储的key个数更多，理论上来讲层数更低，搜索效率更高
叶子节点上存储了所有的key值和对应的data，搜索每个节点最后都要到叶子节点上。每一行搜索时间非常平均
叶子节点被串在了一个链表当中形成了一个有序链表，如果对所引树进行搜索只需要遍历有序链表即可。进行范围查询的时候，也可以直接遍历有序链表，效率更高