liushall 2019-10-28
No.1
按表列属性分类
1.单列索引
以表的单个列字段创建的索引
2.联合索引
以表的多个列字段组合创建的索引,在查询条件使用索引的从左字段顺序才会生效,遵循最左匹配原则。
单列索引和联合索引又包括:
普通索引
非主键,非唯一列的索引
主键索引
基于该表主键自动生成成的索引,如果未给表定义主键,会查找该表中是否存在非空、整形、唯一索引作为其主键(可通过select _rowid from 表名查看),若都不满足会隐式生成一个rowid作为主键(无法直接查到)
唯一索引
基于表的唯一列生成的索引,允许为空值
全文索引
将存储于数据库中的整本书或整篇文章中任意内容信息查找出来,如大量级的文字中如like %关键字%,普通索引的效率与全文索引相比是非常低的。
No.2
按数据结构分类
1.B+tree索引
b+tree基于平衡二叉树的一种多路平衡查找树,所有记录都按照顺序存放在叶子节点中,各个叶子节点直接通过链表相连。与b树不同的是:
非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中。
2.hash索引
基于hash表结构实现的索引,mysql中只有MEMORY/HEAP和NDB存储引擎支持;
InnoDB引擎支持自适应hash索引,但是是数据库自身创建使用的,而不能进行人为定义。当二级索引被频繁的访问时,便会自动创建自适应哈希索引;
通过 命令SHOW ENGINE INNODB STATUS可查看自适应hash索引的使用情况;
通过 命令SHOW VARIABLES LIKE '%ap%hash_index' 查看是否打开自适应hash索引。
对比:
由于hash索引是比较其hash值,hash索引只能进行等值查找而不能进行范围查找
hash索引无法进行排序:原因同上
不支持最左匹配原则,复合索引时合并一起计算hash值
hash索引的检索效率很高可以一次定位,但是当发生大量hash碰撞的时候,链表变长,hash索引效率上是不如b+tree的
由于存在hash碰撞的问题,当需要获得总数时候,hash 索引在任何时候都不能避免表扫描
3.T-tree索引 无锡人流医院哪家好 http://www.wxbhnkyy120.com/
4.R-tree索引
No.3
按存储结构分类
1.聚簇索引(聚集索引)
InnoDB的聚簇索引实际上是在同一个BTree结构中同时存储了索引和整行数据,通过该索引查询可以直接获取查询数据行。
聚簇索引不是一种单独的索引类型,而是一种数据的存储方式,聚簇索引的顺序,就是数据在硬盘上的物理顺序。
在mysql通常聚簇索引是主键的同义词,每张表只包含一个聚簇索引(其他数据库不一定)。
2.辅助索引(非聚集索引,次级索引,二级索引)
非聚集索引在BTree的叶子节点中保存了索引列和主键。如果查询列不在该索引内,只能查到其主键值,还需要回表操作查询聚簇索引进行查询。
聚簇索引的优点:
可以把相关数据保存在一起,如:实现电子邮箱时,可以根据用户ID来聚集数据,这样只需要从磁盘读取少量的数据页就能获取某个用户全部邮件,如果没有使用聚集索引,则每封邮件都可能导致一次磁盘IO。
数据访问更快,聚集索引将索引和数据保存在同一个btree中,因此从聚集索引中获取数据通常比在非聚集索引中查找要快。
使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
聚簇索引的缺点:
聚簇数据最大限度地提高了IO密集型应用的性能,但如果数据全部放在内存中,则访问的顺序就没有那么重要了,聚集索引也没有什么优势了
插入速度严重依赖于插入顺序,按照主键的顺序插入是加载数据到innodb表中速度最快的方式,但如果不是按照主键顺序加载数据,那么在加载完成后最好使用optimize table命令重新组织一下表。
更新聚集索引列的代价很高,因为会强制innodb将每个被更新的行移动到新的位置。
基于聚集索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临页分裂的问题,当行的主键值要求必须将这一行插入到某个已满的页中时,存储引擎会将该页分裂成两个页面来容纳该行,这就是一次页分裂操作,页分裂会导致表占用更多的磁盘空间。
聚集索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候。
二级索引可能比想象的更大,因为在二级索引的叶子节点包含了引用行的主键列。
二级索引访问需要两次索引查找,而不是一次。
ps:看更多干货,加入技术交流微信群可以关注我的公众号360linker