Mysql 索引原理及分类
介绍
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
本质 通过不断地缩小想要获取数据的范围来筛选出最终想要的结果
b树 传统的二叉树并不好,因为层数太多,会导致我们的查找次数过多
b-树 会把数据存储在中间节点中,这样导致一个中间节点能存储的数据太小了
b+树 Innodb myisam的查询速度加快的主要办法
在这棵树中是什么决定了你的io查找次数 树的高度
什么是索引,怎么能够加快查询速度呢?
就是我们建立的这个树形结构 把数据的某一个字段按照特殊的算法计算成为一个树型结构
再根据这个树形结构提供给我们的指针缩小范围,找到对应的磁盘快
通过这颗树,可以将每一次的查找范围缩小1/3
能够快速的找到我要查找的内容
就能够增快查询效率
1.innodb myisam的索引是基于b+树完成
2.b+树是一棵平衡树
b+树的优势 : 所有的数据都存储在叶子节点
中间节点 每个block块可以存储更多的指针
叶子节点之间添加了双向链表 : 寻找范围的时候非常快
b+树的特点 : 影响搜索时间的主要原因,是树的高度
所以我们在设计的时候要尽量的让索引字段的宽度降下来
3.添加索引会一定程度上加快速度,但是会拖慢删除和增加的速度
读写速度不平衡也是我们要解决的问题
innodb 添加的索引必须是一个聚集索引,一张表只能有一个 默认设置主键为聚簇索引
所有的数据存储物理地址都是根据索引的存储来存的 所以按照聚簇索引列来进行排序非常快
所有的数据都直接存储在叶子节点 所以找到索引就是找到数据
myisam 添加的索引必须是非聚集索引,一张表可以有多个
索引和数据存储的顺序没啥关系
还可以添加多个非聚集索引
导致myisam的查询速度快,但是插入和删除非常慢
myisam的多个索引会占用更多的硬盘和内存
所有的数据不直接存储在叶子节点,而是存数据的地址,到达叶子节点之后还需要再一次IO操作才能拿到数据
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into s1 values(i,'eva','female',concat('eva',i,'@oldboy'));
set i=i+1;
end while;
END$$ #$$结束
delimiter ;
索引
加速查询速度
mysql的存储引擎的索引基于 b+树\hash做的
innodb b+树 聚集索引/聚簇索引 非聚簇索引
myisam b+树 非聚簇索引
memory hash
b+树做了那些事儿?为什么基于b+树的索引会快?
一颗平衡树
在非叶子节点不存储数据相关的信息,只存放指针 : 让树的高度降低了,所有的数据的寻找的时间是固定的
在叶子节点会直接存储数据或者数据的指针 : 能够直接找到对应的数据
叶子节点的各个数据块之间使用双向链表来连接 : 能够更好的进行范围\排序相关的操作
要想缩短树的高度,我们能做的事情
1.就是让每一列的宽度尽量的小
聚簇索引和非聚簇索引
聚簇索引
一张表只能有一个
只在innodb存储引擎中存在
b+树 : 在叶子节点会直接存储数据
所有的数据存储的物理地址是根据索引顺序 : 排序快
非聚簇索引
每张表可以有多个
在myisam,innodb中都存在
b+树 : 在叶子节点会存储数据的指针
所有的数据存储的物理地址和索引顺序无关
如果创建多个非聚簇索引,那么insert delete数据都会非常慢,并且占用更多的硬盘和内存、
MySQL的索引分类
索引分类
1.普通索引index :加速查找
2.唯一索引
主键索引：primary key ：加速查找+约束（不为空且唯一）
唯一索引：unique：加速查找+约束 （唯一）
3.联合索引
-primary key(id,name):联合主键索引
-unique(id,name):联合唯一索引
-index(id,name):联合普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
5.空间索引spatial :了解就好，几乎不用
