无论是工作还是面试,针对数据库的考察,很多都是从sql性能入手。如何优化一个慢sql,如何写出高质量的sql代码,如何实现高效索引…其中,索引的考察是重点,也是热点话题,本次就针对Mysql来讲讲各种索引的特点。

内容来自《高性能MySql 第三版》

脑图获取地址:-note/blob/master/img/%E8%AF%BB%E4%B9%A6%E7%AC%94%E8%AE%B0.xmind

减少了服务器需要扫描的数据量。

避免排序和临时表。

随机I/O变顺序I/O。

B-Tree顺序存储的,很适合范围查找,Mysql默认的索引类型。

全职匹配:和索引中的所有列进行匹配。

匹配最左前缀。

匹配列前缀。

匹配范围值。

精确匹配某一列并范围匹配另一列。6.只访问索引的查询(覆盖索引):查询只需要访问索引,无需访问数据列。

查询中的order by操作。

不能按照最左列查询,无法使用索引

不能跳过索引中的列。

有某个范围查询,起右边所有列无法使用索引。

基于哈希表的实现,只有精确匹配索引列才能查询有效。(只有在memory引擎显示支持哈希索引)

哈希索引将所有的哈希码存储在索引中,同时哈希表中保存每个数据行的指针。如果多个列有相同的哈希码,索引会以链表的方式存放多个记录指针到同一个哈希条目中。

索引结构紧凑,查找速度快。

索引中只存储哈希值和行指针,需要再一次查询数据行。

不是依据索引顺序的,无法用于排序。

不支持部分索引列匹配查找。

只支持等值比较查询,不支持范围查找。

当有很多哈希冲突时,访问的是一个链表。

哈希冲突较多时,一些索引的维护成本很大。

数仓应用中,典型的“星型”schema。

存储地理位置信息。

无需前缀查询,空间索引会从所有纬度来索引数据。

查找的是文本中的关键字,而不是直接比较索引中的值。类似于搜索引擎做的事。

可以支持各种字符在内的搜索,也支持自然语言搜索和布尔搜索。

是一种数据存储方式,一张表只能又一个聚簇索引。

InnoDB:在同一个结构中保存了B-Tree索引和数据行。隐式定义一个主键来作为聚簇索引。

可以把相关数据保存在一起。

数据访问更快

使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

最大限度的提高了i/o密集型应用性能,但对于存储在内存中的数据而言,就失去了优势

插入速度严重依赖于插入顺序

更新索引列代价很大

基于聚簇索引的表插入新行,或者主键被更新导致需要移动行的时候,可能面临“页分裂”。

可能导致全表扫描(行比较稀疏,页分裂导致数据存储不连续)

二级索引可能比想象中大(二级索引的叶节点包含了引用行的主键列)

二级索引叶子节点保存的是行主键的值,而不是数据行指针。

一个索引中包含所有所需查询的字段值。

索引条目远小雨数据行大小,所以,如果只需要读取索引,极大的减少了数据访问量。

按照列值顺序存储

对InnoDB的聚簇索引有帮助,在二级索引上包含数据行,无需依据主键二次查询数据行。

当索引字段是很长的字符串是,除了使用哈希索引外,前缀索引也是一种优化方式。

索引开始的部分字符,节省了索引的空间,但降低了索引的选择性(选择性:不重复的索引值和数据表的记录总数的比值)。

解决长字符串索引列的空间问题、索引效率问题。

无法适应前缀索引做order by和group by。

无法使用前缀索引做覆盖扫描。