数据库索引

#  ###数据库索引
part 1 索引(index)概念:

是一个树状的数据结构,即(B树结构,分支节点>2)
相当于字典的目录,功效是加快查询速度;
常用树: B树(balance-tree) , 二叉树,红黑树,hash树

# 树节点:
根节点(最顶级节点)
分支节点(父节点,子节点)
叶子节点(最后一层存储数据的节点)
树的高度(树的层级,理想是三层)

[b+树] : 在相邻的叶子节点上,加入了双向链表(指针),当前叶子节点不但保存当前值,还保存了下一个叶子节点的地址[小范围数据,查询速度很快]
[b*树] : 在相邻的分支节点上,加入了双向链表(指针),当前分支节点不但保存当前值,还保存了下一个分支节点的地址[在大范围里,找数据速度加快]

B树结构

 

# (4) innodb 和 myisam 的索引结构
(1) 聚集索引(聚簇索引) [innodb存储引擎的特点,myisam没有]
如果有主键,自动以主键作为聚集索引列(字段)
如果没有主键,选择唯一键
都没有,自动生成隐藏聚集索引,该字段是6个字节,类型为长整型;

分支节点是存储下一层节点的最小值,用来划分范围,追求的矮胖的数据结构
在数据量变大的时候,尽量在树层级高度不变的情况下,横向发展,好处:可以减少io次数,提升查询效率
真实的数据,直接在叶子节点上存储,所以速度快.

聚集索引

 

(2) 辅助索引(非聚簇索引,二级索引,普通索引)
对这一列的数据先排序,划分区间,把索引值分布到叶子节点上
辅助索引存储的是加了索引的字段值和对应映射的主键id(primary key=>pk),没有存储真实的数据
通过找出这个主键id,再去聚集索引树状结构中查询真实数据;

辅助索引辅助聚集索引找数据的,辅助索引叶子节点重复值过多,会导致回表的次数增多,随机产生的io减慢查询效率
如果想要解决重复问题,使用联合索引,更加精确找出对应唯一的那个id.

 辅助索引

 

(3) 两者区别:
myisam 和 innodb 使用的索引数据结构都是B+树,但是在叶子节点上存储的数据不同
innodb的文件结构中只有.frm 和 .ibd , 直接把数据存在了叶子节点上
myisam的文件结构中有.frm .myd .myi , 叶子节点上存储的索引列,通过索引列映射对应的地址,在去通过这个地址找到实际的数据

innodb 一个表只有一个聚集索引,和多个辅助索引,排序速度更快
myisam 只能有多个辅助索引,没有聚集索引

(4) 性能优化:
利用索引查询时,速度很快,相反,增删改速度会变慢,会改变树状结构;
追求:让每一个分支节点存储的数据尽量小,减少树状结构纵向值高度上的增加

# ### part2 : 索引
# 1.常用索引种类:
普通索引(index)
-提高查询的效率

唯一索引:
-主键索引 primary key : 在创建主键约束的同时,创建索引(不为空,唯一)
-唯一索引 unique : 在创建唯一约束的同时,创建索引(允许为空,唯一)

联合索引:
-primary key() : 联合主键索引
-unique() : 联合唯一索引 
-index() : 联合普通索引

# 不同的存储引擎支持的索引种类
innodb : 支持事务,行级锁, 支持 B-Tree ,fulltext ,不支持hash类型索引结构
myisam : 支持表级锁,不支持事务 支持 B-Tree , fulltext ,不支持hash类型索引结构
memory : 不支持事务,支持表级锁 支持 B-Tree ,hash类型 ,不支持fulltext索引结构