数据库-常见索引类型

索引是存储引擎用于快速找到记录的一种数据结构,这是索引的基本功能,相当于字典目录。索引对于良性的性能非常关键,尤其是当表中的数据量越来越大时,索引对性能的影响愈发重要。在数据较小且负载较低时,不恰当的索引对性能的影响可能还不明显,但当数据量逐漸增大时,不恰当的索引会使性能急剧下降
索引类型
索引有很多种类型,可以为不同的场景提供更好的性能。在 MYSQL中,索引是在存储引擎层而不是服务器层实现的。所以,并没有统一的索引标准:不同存储引擎的索引的工作方式并不一样,也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引,其底层的实现也可能不同。造成这些的原因就是索引是存储在引擎层中!例如Hash索引,在MySQL中只有Memory引擎支持,InnoDB不支持Hash)
涉及 列索引(普通索引,唯一索引,主键索引)、组合索引、全文索引、空间索引

索引分四类:

index ----普通的索引,数据可以重复

fulltext----全文索引,用来对大表的文本域(char,varchar,text)进行索引。语法和普通索引一样。

unique ----唯一索引,唯一索引,要求所有记录都唯一

primary key ----主键索引,也就是在唯一索引的基础上相应的列必须为主键

第一种:index unique scan

索引唯一扫描,当可以优化器发现某个查询条件可以利用到主键、唯一键、具有外键约束的列,或者只是访问其中某行索引所在的数据的时候,优化器会选择这种扫描类型。

第二种:index range scan

索引范围扫描,当优化器发现在UNIQUE列上使用了大于、小于、大于等于、小于等于以及BETWEEN等就会使用范围扫描,在组合列上只使用部分进行查询,导致查询出多行数据。对非唯一的索引列上进行任何活动都会使用index range scan。

第三种:index full scan

全索引扫描,如果要查询的数据可以全部从索引中获取,则使用全索引扫描。

第四种:index fast full scan

索引快速扫描,扫描索引中的全部的数据块,与全索引扫描的方式基本上类似。两者之间的明显的区别是,索引快速扫描对查询的数据不进行排序,数据返回的时候不是排序的。“在这种存取方法中,可以使用多块读功能,也可以使用并行读入,从而得到最大的吞吐量和缩短执行时间”。

一、BTree索引
索引是在存储引擎中实现的,也就是说不同的存储引擎,会使用不同的索引,同一种索引,在不同的存储引擎中实现也会有差别
我们使用术语“B-Tree”,是因为mysql很多地方都使用到了这个关键字,但是不同的存储引擎在底层中也是使用了不同的存储结构,例如:NDB集群存储引擎内部实际上使用的是T-Tree,即使其名字是BTree;InnoDB则使用的是B+Tree
InnoDB的BTree图:


InnoDB存储引擎中有页(Page)的概念,页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB,可通过参数innodb_page_size将页的大小设置为4K、8K、16K
页设置的大小和表数据大小会影响到BTree的高度,一页最多为16k,数据越大,需要更多的分级,所有叶节点具有相同的深度,等于树高h
键值,即聚簇索引的主键,一般为自增id
key和指针互相间隔,节点两端是指针。
一个节点中的key从左到右非递减排列,可以不连续,但是左边的一定会比右边的小,连续的key会降低IO性能的消耗
InnoDB的B+Tree在结构经典B+Tree的基础上进行了优化,增加了顺序访问指针。在每个叶子节点之间,会有一个指针指向下一个叶子节点,这样排序的时候也可以使用到索引
InnoDB和MyISAM的B-Tree中,MyISAM叶子节点的data域存放的是数据记录的地址,在硬盘中会有一张实际的表与之对应,InnoDB表数据文件本身就是按B+Tree组织的一个索引结构,主键索引中叶节点data域保存了完整的数据记录,二级索引(除主键索引外其他的索引)保存的是索引列的值
InnoDB引擎中,查询使用非主键索引,都需要遍历2次B-Tree树,第一次遍历二级索引,二级索引的B+Tree的叶子节点只保存主键的值而不是行的地址值。第二次再通过主键的值遍历主键索引获取到完整的数据记录,但如果查询的返回结果列就是只有索引的列,那就遍历二级索引就可以获得最终的结果。即查询只需要访问索引,而无须访问数据行
B-Tree组合索引生效的情况
CREATE TABLE `t_demo` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`first_name` varchar(20) NOT NULL,
`last__name` varchar(20) NOT NULL,
`age` int(3) NOT NULL,
PRIMARY KEY (`id`),
KEY `inx_demo_group1` (`first_name`,`age`,`last__name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4;

添加组合索引


注意:B-Tree索引适用于全键值、范围键值或建键前缀查找,其中键前缀查找只适用于根据最左前缀的查找,并且索引生效和WHERE条件中,条件的列顺序没有关系,只和创建索引时列的排序有关。mysql的查询优化器会帮你优化成索引可以识别的形式
1. 匹配最左前缀
在inx_demo_group1(first_name, age, last__name)组合索引中,WHERE条件使索引生效的情况包含:

SELECT * FROM t_demo WHERE first_name = ‘张’

SELECT * FROM t_demo WHERE age = 25 AND first_name = ‘张’

SELECT * FROM t_demo WHERE first_name = ‘张’ AND age = 25 AND last__name = ‘飞’

2. 全值匹配
全值匹配指的是和索引中的所有列进行匹配

3. 匹配列前缀
匹配列前缀就是使用 like ‘张%’ 右匹配,但是左匹配和全匹配是使用不到索引的。在日常中,千万不要对索引列使用左匹配或者全匹配。索引失效查询性能降低
SELECT * FROM t_demo WHERE first_name = ‘张%’

4. 匹配范围值
范围查找也是可以使用索引的,‘like’也属于范围匹配的一种范围值指的是 “>”、"<"、“in”、“like %”、">="、"< ="
5. 精确匹配某一列并范围匹配另外一列
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如:

SELECT * FROM t_demo WHERE first_name = '张' AND age > 25 AND last__name = '飞'
1

t_demob表中建立的组合索引 inx_demo_group1(first_name, age, last__name)顺序的索引,last__name 是用不到索引的,如果建立(first_name, last__name,age)的索引则都可以用到,WHERE条件的顺序不影响索引的使用,可以乱序。但是建议最好按照顺序写

B-Tree组合索引的限制
1. 在组合索引中,如果不是按照索引顺序的最左列开始查找,则无法使用到索引
2. 不能跳过索引中的列
3. 如果查询中有某个列的范围查询,则其右边所有列都无法适用索引优化查找

单例索引对比组合索引
如果我们的查询where条件只有一个,我们完全可以用单列索引,这样的查询速度较快,索引也比较瘦身。
不要在一张表上建立太多的单例索引,一个索引就是一个B-Tree树,
如果我们的业务场景是需要经常查询多个组合列,不要试图分别基于单个列建立多个单列索引,一个简单查询sql中(非关联查询),只会有一个索引生效(如果能同时使用多个单列索引,那还需要组合索引干什么呢?),并且组合索引的效率也要高于单例索引,组合索引匹配的列越多,性能越高。因为它索引的行数会更少更精确
在表t_demo中,给first_name和last__name分别添加一个索引

查询结果:

在EXPLAIN计划中,possible_keys表示的是可能使用到的索引,因为查询条件first_name和last__name都有索引,但是mysql只会使用它认为性能最优到一个。并且中间会有一个因为mysql选择使用哪个索引的过程的性能消耗


二、Hash索引
哈希索引( Hash Index)基于哈希表实现,只有精确匹配索引所有列的查询才有效。对于每一行数据存储引都会对所有的索引列计算一个哈希码( Hash Code),哈希码是一个较小的值。哈希索引将哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针(索引列的值的哈希 +每行数据的行号指针)
因为哈希索引自身只需存储对应的哈希值,所以索引的结构十分紧湊,这也让哈希索引查找的速度非常快,然而哈希索引也有它的限制,目前Mysql中只有Memory引擎支持Hash索引,其他的建立Hash保存也会失效

- 哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行
Hash索引在硬盘中会有一张实际的表与之对应,行指针指向的就是哈希值在对应表中的行地址。在BTree索引中,如果查询返回的结果被包含在索引列之中,可以避免去读取行,直接读索引可以大大的提高执行的效率。但是Hash索引上储存的是哈希码值是无法解开还原的。
- 哈希索引数据井不是按照索引值顺序存储的,所以也就无法用于排序
由于Hash索引中存放的是经过Hash计算之后的Hash值,而且Hash值的大小关系并不一定和Hash运算前的键值完全一样,所以数据库无法利用索引的数据来避免任何排序运算;
- 哈希索引只有精确匹配索引所有列的查询才有效,不支持部分索引列匹配查找
哈希索引保存的是索引列的值组合后的哈希码,例如inx_demo_group1(first_name, age, last__name)组合索引,索引保存的就是first_name+age+last__name 的哈希值和数据行指针,如果WHERE只使用其中first_name+age,很显然first_name+age的结果和索引上的哈希值关联不起来。所以Hash索引需要把索引所有的列使用起来才能生效
- 哈希索引只支持等值比较,包括=、in0、<=>,也不支持任何范围查询
由于Hash索引比较的是进行Hash运算之后的Hash值,所以它只能用于等值的过滤,不能用于基于范围的过滤,因为经过相应的Hash算法处理之后的Hash值的大小关系,并不能保证和Hash运算前完全一样。
- 访问哈希索引的数据非常快,除非有很多哈希冲突
哈希的计算效率和检索速度只需要一次计算就能定位到数据的位置,但如果哈希冲突,就需要去遍历链表,链表越长,性能越低
- 如果哈希冲突很多的话,一些索引维护操作的代价也会很高

InnoDB自适应hash索引
Innodb存储引擎会监控对表上二级索引的查找,如果发现某二级索引被频繁访问,二级索引成为热数据,建立哈希索引可以带来速度的提升。经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据),自适应哈希索引通过缓冲池的B+树构造而来,因此建立的速度很快。
自适应hash索引功能被打开

mysql> show variables like '%ap%hash_index';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON |
+----------------------------+-------+
1 row in set (0.01 sec)

特点
  1、无序,没有树高
  2、降低对二级索引树的频繁访问资源
    索引树高<=4,访问索引:访问树、根节点、叶子节点
  3、自适应
缺陷
  1、hash自适应索引会占用innodb buffer pool;
  2、自适应hash索引只适合搜索等值的查询,如select * from table where index_col=‘xxx’,而对于其他查找类型,如范围查找,是不能使用的;
  3、极端情况下,自适应hash索引才有比较大的意义,可以降低逻辑读。

三、聚簇索引和非聚簇索引
聚簇索引是一种数据存储方式,因为是储存引擎负责实现索引,所以不是所有的存储引擎都支持聚簇索引。B+Tree结构都可以用在MyISAM和InnoDB上,InnoDB使用的是聚簇索引,MyISAM的是非聚簇索引

聚簇索引
InnoDB通过主键聚集数据,如果表没有定义主键,InnoDB会选择一个唯一的非空索引替代,如果也没有这样的索引,InnoDB会隐式定义一个主键来作为表的聚簇索引。

将主键组织到一棵B+树中,聚集索引就是表,叶子节点data域保存了完整的数据记录。若使用"where id = 14"这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。若对name列进行条件搜索,则需要两个步骤:第一步在二级索引(inx_demo(name)B+树中检索name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。
聚集索引的优点:
  A:可以把相关数据保存在一起,如:实现电子邮箱时,可以根据用户ID来聚集数据,这样只需要从磁盘读取少量的数据页就能获取某个用户全部邮件,如果没有使用聚集索引,则每封邮件都可能导致一次磁盘IO
  B:数据访问更快,聚集索引将索引和数据保存在同一个btree中,因此从聚集索引中获取数据通常比在非聚集索引中查找要快
  C:使用覆盖索引扫描的查询可以直接使用页节点中的主键值
聚集索引的缺点:
  A:聚簇数据最大限度地提高了IO密集型应用的性能,但如果数据全部放在内存中,则访问的顺序就没有那么重要了,聚集索引也没有什么优势了
  B:插入速度严重依赖于插入顺序,按照主键的顺序插入是加载数据到innodb表中速度最快的方式,但如果不是按照主键顺序加载数据,那么在加载完成后最好使用optimize table命令重新组织一下表
  C:更新聚集索引列的代价很高,因为会强制innodb将每个被更新的行移动到新的位置
  D:基于聚集索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临页分裂的问题,当行的主键值要求必须将这一行插入到某个已满的页中时,存储引擎会将该页分裂成两个页面来容纳该行,这就是一次页分裂操作,页分裂会导致表占用更多的磁盘空间
  E:聚集索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候
  F:二级索引可能比想象的更大,因为在二级索引的叶子节点包含了引用行的主键列。
  G:二级索引访问需要两次索引查找,而不是一次
  推荐使用主键自增来作为聚集索引,因为BTree树上主键的值是顺序的,所Innodb把每一条记录都存储在上一条记录(key大小排序)的后面,当达到页的最大填充因子时(innodb默认的最大填充因子是页大小的十六分之十五,留出部分空间用于以后修改),下一条记录就会写入新的页中,一旦数据按照这种顺序的方式加载,主键页就会近似被顺序的记录填满
  但是在UUID主键下,因为新插入行的主键值不一定比前面的大,所以innodb无法简单地总是把新行插入到索引的最后,而是需要为新的行寻找合适的位置,通常是已有数据的中间位置,例如一个页中存储两个两个分别为17,19的主键数据,新插入的key =1818,那么key=18的数据必须存放在key = 17的后面,如果这个时候页大小不够储存key = 18 的数据,就需要分配新的空间,再将key = 19 数据移动到新空间中。这会增加很多额外的工作,并导致数据分布不够连续,下面是使用UUID作为主键的一些缺点:
A:写入的目标页可能已经刷到磁盘上并从缓存中移除,或者是还没有被加载到缓存中,innodb在插入前不得不先找到并从磁盘读取目标页到内存中,这将导致大量的随机IO
B:因为写入是乱序的,innodb不得不频繁地做页分裂操作,以便为新的行分配空间,页分裂会导致移动大量数据,一次插入最少需要修改三个页不是一个页
C:由于频繁的页分裂,页会变得稀疏并被不规则地填充,所以最终数据会有碎片,导致IO读取性能下降
把这些随机值载入到聚集索引之后,也许需要做一次optimize table来重建表并优化页的填充。使用innodb时应该尽可能地按照主键顺序插入数据,并且尽可能地使用简单增加的聚簇键的值来插入新行。

非聚簇索引
MyISAM的是非聚簇索引,结构如图:

B+Tree的叶子节点上的data,并不是数据本身,而是数据存放的地址。主索引和辅助索引没啥区别,只是主索引中的key一定得是唯一的,辅助索引可以是随便的列。非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键列,辅助键索引B+树存储了辅助键列。表数据存储在独立的地方,这两颗B+树的叶子节点都保存一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。

 

 参考:https://blog.csdn.net/weixin_45108959/article/details/128064293

 

posted @ 2024-03-19 17:20  konglingbin  阅读(56)  评论(0编辑  收藏  举报