mysql索引原理总结

分类:

单列索引

一个索引只包含单个列,一张表可以有多个单列索引

组合索引

一个索引包含多个列

优缺点

查询速度快

更新速度慢,不仅要更新数据,还要更新索引文件

占用磁盘空间

索引

hash、b+树

b+树是对数据进行排序,
再分组记录,可以进行范围查询,支持排序,采用的是b+树,时间是log

hash是直接计算hash值,
因此hash不支持范围查询,不支持排序
因为hash地址会冲突,需要再比较链表的数据内容

当hash冲突少时,hash查找比较快,时间是o(1)+o(logt)

b+:innodb、myisam

myisam表数据文件与索引文件时分离的,索引文件只记录记录再磁盘中的存储地址,可以不需要主键

innodb有表数据的ibd文件,表数据文件就是主索引,叶节点的data域保存了完整的数据信息,按照主键聚集,所以要有主键,如果没有定义的话,会采用隐藏列中的行id来作为主键
因为inndob的数据文件要按照主键聚集,所以innodb要求表必须要有主键(Myisam可以没有),
如果没有显式定义,则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键,
如果不存在这种列,则mysql会自动为innodb表生成一个隐含字段作为主键,这字段的长度为6个字节,类型为长整型.

b+树查找过程

1、从磁盘中加载一页数据,
2、在内存中通过二分查找,
确定数据的范围,锁定第二页的指针,
3、把第二页加载到内存,再二分查找,确定范围,锁定指针,
4、直到找到数据。
  1. 字段要尽量小,当树高度越高,就需要多次载入数据,搜索效率比较低,所以字段要尽量小,存的数据会比较多,减少占的页数
  2. 最左匹配特性,b+树是采用从左到右的顺序来建树的,查找是也是通过第一个比较因子来确定搜索范围,如果没有第一个比较因子,就没办法确定从哪开始搜索。如果有3个比较条件,只给了头尾,可以第一个采用索引确定下一个要搜索的节点,在对这个范围的数据利用第3个比较条件进行匹配

问题整理

索引

索引分成2类:

  • hash类型
  • b+树类型

hash类型

  • 方法:

根据hash值进行定位,因为会有hash冲突,索引最后要把查找的元素跟链表中的元素做一个匹配,得到结果,

  • 效率

当hash冲突比较少时,时间复杂度是o(1)

b+树类型

  • 方法:

将数据进行排序,分组,分组是存在页中,每页中还要页目录,再用页存储页号跟每一页的主键的最小值,同一层的页之间是双向链表,数据页之间也是双向链表

本质:
搜索是通过减少搜索范围以及二分查找可以提高查找速度

  • 效率跟特征:

因为cpu从磁盘每次都读取一页,当树的高度比较高时,读取的次数就会比较多,降低查找速度,所以可以减少字段的大小,使一页中存放更多的数据,减少树的高度

  • 最左匹配

从左到右进行建树,查找时需要通过第一个比较条件确定下一步搜索范围,如果没有第一个比较条件,就无法确定搜索范围,如果有第一个跟最后一个,会先确定搜索范围,然后对这个范围的所有数据根据最后一个比较条件进行匹配(这个就没有索引了)

引擎

#不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
Memory 不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引;
NDB 支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-tree、Full-text 等索引;
Archive 不支持事务,支持表级别锁定,不支持 B-tree、Hash、Full-text 等索引;

聚集索引

采用主键对每张表建立b+树,就是用主键对数据进行排序

同一层的页之间按照主键从左到右是双向链表
数据页之间按照主键从左到右是双向链表
数据页内的记录按照主键从左到右是单向链表
叶子节点存储每一行的记录

不需要用index语句去创建,innodb默认的数据存储方式就是这种方式

优点

查找最后几个时,双向链表可以倒序

查找范围时,叶节点的上层节点可与得到页范围

辅助索引

  1. 表中除了聚集索引外其他索引都是辅助索引(Secondary Index,也称为非聚集索引)
  2. 用我们指定的索引列来排序
  3. 叶节点存储的是主键跟索引列,
  4. 目录页存储的不是主键跟页号,是索引列跟主键、页号
  5. 需要再通过主键到聚集索引查找到记录(回表)

目录页的记录的唯一的
占的空间小,可以减少页,所以计较快

联合索引

采用多个列来作为排序的规则,也是辅助索引

先按照前面的列进行排序,相同的话再考虑后面的列

命令

普通索引

index:加速查找

唯一索引:
主键索引primary key(不为空、不重复):加速查找+约束
唯一索引unique:(不重复):加速查找+约束

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

创建表时创建
unique key id_id(id)
index id_name(name)

在已存在的表上创建
create index id_id on gyy5(message(20));
create index id_id on gyy5(name);

修改表结构时创建
alter table gyy5 add index age int;

删除
alter table gyy5 drop index ix_t;
drop index ix_m on gyy5;

查看表结构
show columns from gyy5

展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 游动-白 设计师: 上身试试
应支付0元
点击重新获取
扫码支付

支付成功即可阅读