索引

索引在存储引擎层实现,所以并没有统一的索引标准:不同的存储引擎的索引工作方式并不一样,也不是所有的存储引擎都支持所有类型的索引,即时多个存储引擎支持同一类型的索引,其底层实现也可能不同。

如果索引包含多个列,MySql只能最左前缀索引列

B+Tree,每个叶子节点都包含指向下一个叶子节点的指针,从而方便叶子节点的范围遍历,B-Tree的指针指向的是被索引的数据,而不是其他节点页(不同存储引擎的“指针”类型不同)。InnoDB使用的是B+Tree

MyISAM使用前缀压缩技术使得索引更小,InnoDB按照原数据格式进行存储。

MyISAM索引通过数据的物理位置引用被索引得行,InnoDB根据主键引用被索引的行。

B+Tree索引

B+Tree索引适用于全键值、范围和键前缀查找。

全值匹配、匹配最左前缀、匹配列前缀(以...开头)、范围、精确匹配某一列并范围匹配另一列

BTree索引的限制:

若不是按照索引的最左列开始查找,则无法使用索引

不能跳过索引中的列。

若查询中有某个列的范围查询,则其右边所有列无法使用索引优化查找。

哈希索引

哈希索引中存储关键值的哈希码和对应数据行的指针,只有精确匹配索引所有列的查询才有效。

MySql中,只有Memory引擎显式支持哈希索引,这也是Memory索引表的默认索引类型,它也支持B+Tree索引。同时Memory引擎支持非唯一哈希索引,若多个列的哈希值形同,索引会以链表的方式存储多个记录指针到同一个哈希条目中。

哈希索引在存储时先得到关键值的哈希值,再将哈希值于对应数据行的指针存入哈希表,哈希表的哈希值是有序的,但数据行不是。

哈希索引的限制:

哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引的值来避免读取行。

哈希索引数据并不是按照索引值顺序存储的(有序的是索引的哈希值),所以也就无法排序

不支持部分索引列匹配查找,因为哈希索引始终使用索引列的全部内容计算哈希值。例如在(A,B)列上建立哈希索引,若查询只有数据列A,则无法使用该索引

只支持等值查新,包括=、in()、<=>

哈希冲突多的情况下,索引的维护和查找代价都会比较高。查找出现哈希冲突,存储引擎必须遍历链表中所有的行指针逐行比较,直到找到所有符合条件的行。删除时同理

InnoDB中的“自适应哈希索引”

InnoDB注意到某些索引值被使用的非常频繁时,会在内存中基于B+tree索引之上再创建一个哈希索引,让B+tree索引页具有哈希索引的优点。这是一个完全自动、内部的行为,用户可以关闭该功能。

自定义哈希索引:若存储引擎不支持哈希索引,可模拟像InnoDB一样创建哈希索引,用很小的索引就可以为超长的键创建索引。

不要使用SHA1()和MD5()作为哈希函数,因为这两个函数计算出来的哈希值是非常长的字符串,会浪费大量的空间,比较时也会更慢。

自定义哈希索引举例:

哈希冲突

在出现哈希冲突的时候,MySql会返回大量哈希值相同但关键值不同的数据行,要避免冲突,必须在where条件中带入哈希值和对应列值。

索引优点

减少服务器需要扫描的数据量、帮助服务器避免排序和临时表(why??)、将随机I/O变为顺序I/O

索引是否适合某个查询的“三星系统”:

1、索引将相关记录放在一起获一星

2、索引的数据顺序和查询中的排序顺序获一星

3、索引中的列包含了查询中需要的全部列获一星(覆盖索引)

 

索引是最好的解决方案吗?

高性能的索引策略

若查询中的列不是独立的,MySql不会使用索引“独立的列”是指索引列不能是表达式的一部分,也不能是函数的参数。

 

前缀索引:只索引开始的部分字符,在使用前缀索引是要注意索引的选择性。

前缀索引是一种能使索引更小、更快的方法,但也有缺点:MySql无法使用前缀索引做order by和group by,也无法使用索引做覆盖扫描。

索引的选择性:不重复的索引数 数据表的总记录。索引的选择性越高则查询效率越高,因为选择性高的索引可以让MySql在查找时过滤掉更多的行。对于BLOB、TEXT或很长的VARCHAR类型的列,必须使用前缀索引,因为MySql不允许索引这些列的完整长度

 

MySql 5.0引入“索引合并”,在一定程度上可以使用表上的多个单列索引来定位指定的行,更早版本的MySql只能使用其中某一个单列索引。

出现索引合并更多时候说明表上的索引建得很糟糕:

1、当出现多个索引做相交操作时(通常有多个and),通常意味需要一个包含相关列的多列索引,而不是多个独立的单列索引。

2、当出现多个索引做联合操作时(通常有多个or),通常会耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。

3、优化器不会将这些计算到“查询成本”中,使得查询的成本被“低估”,导致该执行计划还不如直接走全表扫描

聚簇索引

并不是一种单独的索引类型,而是一种数据存储方式。具体的细节依赖于其实现方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

不是所有的存储引擎都支持聚簇索引

一些数据库服务器允许选择哪个索引作为聚簇索引,但目前还没有MySql内建的存储引擎支持这一点(可以查一下现最新版本是否支持了)。InnoDB通过主键聚集数据,若没有定义主键,InnoDB会选择一个唯一的非空索引代替,若没有这样的索引,InnoDB会隐式定义一个主键来作为聚簇索引(所以聚簇索引的键值都是唯一的?)。InnoDB只聚集在同一个页面的记录,包含相邻键值的页面可能会相距很远。?这句如何理解

聚簇索引中,叶子页包含了行的全部数据,节点页只包含了索引列

 

聚簇索引优点:

1、可以将相关数据保存在一起,这样只需要从磁盘读取少数数据页就能获得需要的数据,减少磁盘I/O。(例如实现电子邮箱时可根据用户ID来聚集数据,这样只需从磁盘读取少量数据也就能获得某用户的全部邮件。但是聚簇索引的键值不是不能重复吗??多个邮箱可能对应着同一个用户ID,如何使用用户的ID聚集数据呢??)

2、数据访问更快。聚簇索引将索引和数据保存在同一个B+Tree树中,因此从聚簇索引取数据通常比在非聚簇索引中快

缺点:

1、聚簇数据提高了I/O密集型应用的性能,但如果数据全部都放在内存中,则访问顺序就没那么重要了,它也没什么优势了。

2、非聚簇索引可能比想象的更大,因为它的叶子几点包含了引用行的主键列

3、非聚簇索引需要两次索引查找(非聚簇索引叶子节点保存的不是只想行的物理位置的指针,而是行的主键值。通过二级指针查找行,需要先找到二级索引的叶子节点得到对应的主键值,再根据这个值去聚簇索引中查找对应的行。这需要两次B+Tree查找而并不是一次。对于InnoDB,自适应哈希索引能够减少这样的重复工作why?

MyISAM与InnoDB数据分布对比

MyISAM按照数据插入的顺序存储在磁盘上,MyISAM的索引和数据是分开存储的,索引文件(.MYI)和数据文件(.MYD)是相互独立的,叶子节点中存储的是索引值及对应数据的行号(物理地址)。

MyISAM中的主键索引和其他索引在结构上没有什么不同,主键索引就是一个名未PRIMARY的唯一非空索引

MyISAM数据分布图

 

 

InnoDB数据分布

 

 

InnoDB的聚簇索引包含了索引和表数据,聚簇索引就是表,不像MyISAM那样需要独立的行存储。

还有一点和MyISAM不同,InnoDB的二级索引的叶子节点中存储的不是“行指针”,而是主键值,以此作为指向行的“指针”。

这样减少了出现数据行移动或数据页分裂时二级索引的维护(因为如果二级索引中存的是数据行的物理地址,那么行移动后物理地址会发生变化,那么二级索引中对应行的物理地址也会发生变化。但是二级索引中存储的是主键值,尽管数据行的物理位置变化,主键值不会变,通过主键去找物理地址再找到数据)。使用主键值当作指针会让二级索引占更多的空间,换来的好处是,InnoDB在移动行时无须更新二级索引中的这个“指针”

MyISAM与InnoDB存放数据与索引的区别

从性能角度考虑,最好避免随机的聚簇索引,它使得索引的聚簇索引的插入完全变得随机,使数据没有任何聚集特性,特别是对于I/O密集型的应用。

顺序主键与随机主键对比

顺序主键:InnoDB把每一条记录都存储在上一条记录的后面,当达到页的最大填充因子时,下一条记录就会写入新的页中,按照这种方式,主键页就会近似与被顺序的记录填满。

随机主键:因为键值大小随机,新一行的数据位置可能是在以有数据的中间,这需要重新分配空间,频繁产生页分裂操作。页分裂会导致大量数据移动,一次插入至少需要修改三个页而不是一个页。由于频繁的页分裂,页会变得稀疏并不规则地被填充,随意最终数据会有碎片。

覆盖索引

如果索引包含了所有需要查询的字段的值,就称之为“覆盖索引”。可以通过索引来直接获得列的数据,不用再关联读取数据行,极大地提高性能。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值,而哈希索引、全文索引等都不存储索引列的值,所以MySql只能使用B-Tree索引做覆盖索引。不同存储引擎实现覆盖索引方式不同,也不是所有的引擎都支持覆盖索引。

当某个查询是索引覆盖查询时,在explain的extra列可以看到“Using index”的信息。

 

 

关于LIKE操作使用索引的问题

MySql能在索引中做最左前缀匹配的LIKE比较,但如果是通配符开头的LIKE查询,存储引擎就无法做比较匹配,只能通过提取数据行的值做比较。

使用索引扫描来做排序

MySql有两种方式可以生成有序的结果:1、排序操作 2、按照索引顺序扫描。若explain出来的type列的值为“index”,则说明MySql使用了索引扫描来排序。

索引扫描本身很快,但如果索引不能覆盖查询所需要的全部列,则每扫描一条索引记录就要回表查询一次对应的行,这基本上都是随机I/O,因此案索引顺序读取数据的速度通常比顺序地全表扫描慢,尤其在I/O密集型场景。

只有当索引列的顺序与order by子句顺序完全一致(因为当前索引只有下一个索引的指针,没有前一个索引的指针,无法找到前一个索引值),且所有排序列的排序方向都一致时(why?万一一个索引顺序是升序,另一个索引为降序呢?),MySql才能使用索引进行排序。

当查询关联多张表时,只有当order by子句引用的字段全部是第一个表时,才能用索引排序。(能相同大概)

使用索引排序和和使用索引查找的限制时一样的,需要满足索引的饿最左前缀要求,否则无法利用索引排序。(除非左前缀是常量)

 

MySql中where中的过滤条件是在存储引擎返回数据行后在服务器层才进行的。应该使用索引尽可能过滤掉无效行,使存储引擎返回尽可能少的数据再进行where过滤。

explain中的extra出现“Using where”,表示MySql服务器将存储引擎返回行以后再应用过滤条件

索引总结

 

 

 

理解:

哈希索引不会像B+Tree索引一样按顺序存储数据。

B+Tree索引是如何顺序存储数据的。

MySQL的btree索引和hash索引的区别

Hash 索引结构的特殊性,其检索效率非常高,索引的检索可以一次定位,不像B-Tree 索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以 Hash 索引的查询效率要远高于 B-Tree 索引。
可 能很多人又有疑问了,既然 Hash 索引的效率要比 B-Tree 高很多,为什么大家不都用 Hash 索引而还要使用 B-Tree 索引呢?任何事物都是有两面性的,Hash 索引也一样,虽然 Hash 索引效率高,但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端,主要有以下这些。

(1)Hash 索引仅仅能满足"=","IN"和"<=>"查询,不能使用范围查询。

由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值,所以它只能用于等值的过滤,不能用于基于范围的过滤,因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系,并不能保证和Hash运算前完全一样。

(2)Hash 索引无法被用来避免数据的排序操作。

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值,而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样,所以数据库无法利用索引的数据来避免任何排序运算;

(3)Hash 索引不能利用部分索引键查询。

对于组合索引,Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值,而不是单独计算 Hash 值,所以通过组合索引的前面一个或几个索引键进行查询的时候,Hash 索引也无法被利用。

(4)Hash 索引在任何时候都不能避免表扫描。

前面已经知道,Hash 索引是将索引键通过 Hash 运算之后,将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中,由于不同索引键存在相同 Hash 值,所以即使取满足某个 Hash 键值的数据的记录条数,也无法从 Hash 索引中直接完成查询,还是要通过访问表中的实际数据进行相应的比较,并得到相应的结果。

(5)Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

对于选择性比较低的索引键,如果创建 Hash 索引,那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦,会浪费多次表数据的访问,而造成整体性能低下。

问题:

group by如何实现的?

范围查询如何使用索引?

如果where和order by的字段上都有索引,MySql如何选择使用的索引

count查询如何使用索引?

可以让索引以降序的顺序排列吗?

B+Tree索引在找到索引和是沿着索引继续往下找到叶子节点中的数据(如果是这种的话具体是怎么找的)还是节点中存有数据的地址,直接在叶子链中找到对应数据就可以了??

只有B+Tree才能作聚簇索引吗,哈希索引或其数据结构可以吗。哪些数据结构可以作聚簇索引

MVCC是什么

筛选出来再排序,还是排完序再筛选

可以选择InnoDB使用哈希索引吗?

在索引上order by是先将索引order by再去关联数据行吗?

MySql是如何做group by的(有索引和无索引)

哈希索引支持多列索引吗

多值索引是如何存储的?

B+Tree适合范围查找?

全表扫描在聚簇索引上进行吗?

B+Ttree的插入以及在叶子节点对数据的插入?

索引如何进行范围查找?

百度聚簇索引与非聚簇索引区别。

既然聚簇索引的数据是按照索引的数据存储的

非聚簇索引不存储数据,那叶子节点存的是什么,叶子节点的键值不是按照索引排队的吗?

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值