Mysq数据库索引(B-Tree索引)

一、B-Tree索引的底层结构

  • 所有的值都是按顺序存储的,而且每一个叶子页到根的距离相同,如图所示,B-Tree索引的底层数据结构一样平常是B+树,反映了MyISAM索引是若何事情的。

 
Mysq数据库索引(B-Tree索引)  
二、B-Tree索引的使用规则  
Mysq数据库索引(B-Tree索引)  
B-Tree索引适用于全键值、键值局限和键前缀查找,其中键前缀查找只适用于凭据最左前缀查找。B-Tree索引支持的查询原则如下所示:

  1. 全值匹配:全值匹配指的是和索引中的所有列举行匹配。
  1. 匹配最左前缀:前边提到的索引可以用于查找所有姓Allen的人,即只使用索引中的第一列。
  1. 匹配列前缀:也可以只匹配某一列的值的开头部门。例如前面提到的索引可用于查找所有以J开头的姓的人。这里也只用到了索引的第一列。
  1. 匹配局限值:例如前边提到的索引可用于查找姓在Allen和Barrymore之间的人。这里也只使用了索引的第一列。
  1. 正确匹配某一列并局限匹配另外一列:前边提到的索引也可用于查找所有姓为Allen,而且名字是字母K开头(好比Kim,Karl等)的人。即第一列last_name全匹配,第二列first_name局限匹配。

 
由于索引树的节点是有序的,以是除了按值查找之外,索引还可以用于查询中的ORDER BY操作(按顺序查找),若是ORDER BY子句知足前面列出的几种查询类型,则这个索引也可以知足对应的排序需求。
下面是一些关于B-Tree索引的限制:

  • 若是不是根据索引的最左列开始查找,则无法使用索引。例如上面例子中的索引无法查找名字为Bill的人,也无法查找某个特定生日的日,由于这两列都不是最左数据列。
  • 若是查询中有某个列的局限查询,则其右侧所有列都无法使用索引优化查找。

三、聚簇索引
    聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。详细的细节依赖于其实现方式,然则InnoDB的聚簇索引实际上在同一个结构中保留了B-Tree索引和数据行
    当表有聚簇索引时,它的数据行实际上存放在索引的叶子页中,这也就是说数据行和相邻的键值紧凑地存储在一起。
    下图展示了聚簇索引中的纪录是若何存放的。注意到,叶子页包罗了行的所有数据行,然则节点页只包罗了索引列。  
|
Mysq数据库索引(B-Tree索引)  
    聚簇索引可能对性能有辅助,但也可能导致严重的性能问题。

  1. 聚簇索引的优点:
    1. 数据接见更快,聚簇索引将索引和数据保留在同一个B-Tree中,因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
    1. 使用笼罩索引扫描的查询可以直接使用页节点中的主键值。
  1. 聚簇索引的瑕玷:
    1. 插入顺序严重依赖插入顺序。根据主键的顺序插入是向InnoDB表中插入数据速率最快的方式,需要制止主键键值随机的(不延续且值得漫衍局限异常大)聚簇索引,好比使用UUID作为主键,而应该使用类似AUTO_INCREMENT的自增列。
    1. 更新聚簇索引列的价值很高,由于会强制InnoDB将每个被更新的行移动位置到新的位置。
    1. 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行时,可能面临“页盘据”的问题。当行的主键值要求必须将这行插入到某个已满的页中时,存储引擎会将该页盘据成两个页面来容纳该行,这就是一次页盘据操作。页盘据会导致表占用更多的磁盘空间。
    1. 二级索引可能比想象的更大,由于在二级索引中的叶节点包罗了引用行的主键列。
    1. 二级索引接见需要两次索引查找,而不是一次。

四、InnoDB和MyISAM引擎索引的差异
    聚簇索引和非聚簇索引的数据漫衍有区别,以及对应的主键索引和二级索引的数据漫衍也有区别,通常会让人感应疑心和意外。下图展示了MyISAM和InnoDB的差别索引和数据存储方式。
    MyISAM的数据漫衍异常简朴,根据数据插入的顺序存储在磁盘上,

Docker数据管理与挂载管理

主键索引和二级索引的叶节点存储着指针,指向对应的数据行。
InnoDB中,聚簇索引“就是”表,以是不会像MyISAM那样需要自力的行存储。聚簇索引的每个叶节点都包罗了主键值和所有的剩余列(在此例中是col2)。
    InnoDB的二级索引和聚簇索引很差别。InnoDB二级索引的叶节点中存储的不是“行指针”,而是主键值,并以此作为指向行的“指针”。  
|
Mysq数据库索引(B-Tree索引)  
五、松散索引
 MySQL并不支持松散索引扫描,也就是无法根据不延续的方式扫描一个索引。通常,MySQL的索引扫描需要先界说一个起点和终点,纵然需要的数据只是这段索引中很少数的几个,MySQL仍然需要扫描这段索引中的每个条目。
    下面,我们通过一个示例说明这点,假设我们有如下索引(a,b),有下面的查询:  
|
Mysq数据库索引(B-Tree索引)    
    由于索引的前导字段是列a,然则在查询中只指定了字段b,MySQL无法使用这个索引,从而只能通过全表扫描找到匹配的行,如下图所示。  
|
Mysq数据库索引(B-Tree索引)  
    领会索引的物理结构的话,不难发现还可以有一个更快的设施执行上面的查询。索引的物理结构(不是存储引擎的API)是的可以先扫描a列第一个值对应的b列的局限,然后再跳到a列第二个差别值扫描对应的b列的局限。下图展示了若是由MySQL来实现这个历程会怎样。  
|
Mysq数据库索引(B-Tree索引)  
    注意到,这时就无须再使用WHERE子句过滤,由于松散索引扫描已经跳过了所有不需要的纪录。
    MySQL 5.0之后的版本,在某些特殊的场景下是可以使用松散索引扫描的,例如,在一个分组查询中需要找到分组的最大值和最小值:  
|
Mysq数据库索引(B-Tree索引)  
    在EXPLAIN中的Extra字段显示”Using index for group-by”,示意这里将使用松散索引扫描。
六、笼罩索引
索引除了是一种查找数据的高效方式之外,也是一种列数据的直接获取方式。MySQL可以使用索引来直接获取列的数据,这样就不需要读取数据行。若是一个索引包罗所有需要查询的字段的值,我们就称之为“笼罩索引”
    笼罩索引是异常有用的工具,能够极大地提高性能。SQL查询只需要扫描索引而无需回表,会带来许多利益:

  • 索引条目数目和巨细通常远小于数据行的条目和巨细,以是若是只需要读取索引,那么MySQL就会极大地削减数据接见量。
  • 由于索引是根据列顺序存储的,以是对于I/O密集型的局限查找会比随机从磁盘读取每一行数据的I/O要少的多。
  • 由于InnoDB的聚簇索引,笼罩索引对InnoDB表稀奇有用。InnoDB的二级索引在叶子节点中保留了行的主键,索引若是二级主键能够笼罩查询,则制止对主键索引的第二次查询。

 
    当提议一个被笼罩索引的查询(也叫索引笼罩查询)时,在EXPLAIN的Extra列可以看到”Using Index”的信息。例如,表sakila.inventory有一个多列索引(store_id, film_id)。MySQL若是只需要接见这两列,就可以使用这个索引做笼罩索引,如下所示:  
Mysq数据库索引(B-Tree索引)    
参考:

  • 《高性能MySQL》

   

原创文章,作者:28x29新闻网,如若转载,请注明出处:https://www.28x29.com/archives/16572.html