MySQL之索引篇-聚集索引 非聚集索引 覆盖索引

·  阅读 581

1.MySQL创建索引的方式

Copy Table方式

InnoDB最早支持的创建索引的方式,通过临时表拷贝的方式实现的。新建一个带有新索引的临时表,将原表数据全部拷贝到临时表,然后Rename,完成创建索引的操作。

这个方式创建索引,创建过程中,原表是可读的。但是会消耗一倍的存储空间。

Inplace方式

原生MySQL 5.5,以及innodb_plugin中提供的创建索引的方式。在原表上直接进行,不会拷贝临时表。相对于Copy Table方式,这是一个进步。

Inplace方式创建索引,创建过程中,原表同样可读的,但是不可写。

Online方式

online add index创建语句:create index.....online

MySQL 5.6.7中提供的创建索引的方式。InnoDB的Online Add Index,首先是Inplace方式创建索引,无需使用临时表。在遍历聚集索引,收集记录并插入到新索引的过程中,原表记录可修改。而修改的记录保存在Row Log中。当索引聚集索引遍历完毕,并全部插入到新索引之后,重放Row Log中的记录修改,使得新索引与聚集索引记录达到一致状态。

与Copy Table方式相比,Online Add Index采用的是Inplace方式,无需Copy Table,减少了空间开销;与此同时,Online Add Index只有在重放Row Log最后一个Block时锁表,减少了锁表的时间。

与Inplace方式相比,Online Add Index吸收了Inplace方式的优势,却减少了锁表的时间。 新索引上缺乏版本信息,因此无法为老事务提供快照读。

2.数据库索引原理

数据库索引是一种数据结构,通过额外的写操作和存储空间来维护数据库索引,提高数据读取数据的速度。主流的RDBMS都是把平衡树(B-树、B+树)作为数据库表默认的数据库索引结构,InnoDB与MyISAM引擎使用B+Tree作为索引实现,但与MyISAM不同,InnoDB主键索引是聚集索引,而辅助索引则是非聚集索引,MyISAM所有的索引都是非聚集索引,且索引满足最左前缀匹配原则。也有数据库使用哈希桶作为索引的数据结构。

B+树和B-树区别

  • B+树内节点不存储数据,所有 data 存储在叶节点导致查询时间复杂度固定为 log n。而B-树非叶子节点会存储数据,查询时间复杂度不固定,与 key 在树中的位置有关,最好为O(1)。
  • B+树叶节点两两相连可大大增加区间访问性,可使用在范围查询等,而B-树每个节点 key 和 data 在一起,则无法区间查找。
  • B+树更适合外部存储。由于内节点无 data 域,每个节点能索引的范围更大更精确。

我们平时建表时都会给数据库表加上主键。使用MySQL数据库,如果没有主动设置主键,就会选一个不包含NULL的第一个唯一索引列作为主键列,并把它用作一个聚集索引。如果没有这样的索引就会使用行号生成一个聚集索引,把它当做主键,这个行号6bytes,自增。可以用select _rowid from table来查询。

聚集索引

如果给表上了主键,那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构,即「平衡树」结构,换句话说,就是整个表就变成了一个索引,即聚集索引。所以一个表只能有一个主键,一个聚集索引。主键的作用就是把「表」的数据格式转换成「索引(平衡树)」的格式放置。 聚集索引的叶子节点存放的是数据。

索引能让数据库查询数据的速度上升, 而使写入数据的速度下降, 因为平衡树这个结构必须一直维持在一个平衡状态, 增删改数据都会改变平衡树各节点中的索引数据内容,破坏树结构, 因此,在每次数据改变时, DBMS必须去重新梳理树(索引)的结构以确保它的正确,这会带来不小的性能开销,也就是为什么索引会给查询以外的操作带来副作用的原因。

非聚集索引

非聚集索引是我们平常使用的常规索引,和聚集索引一样, 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段, 如果给表中多个字段加上索引 , 那么就会出现多个独立的索引结构,每个索引(非聚集索引)互相之间不存在关联。 每次给字段建一个新索引, 字段中的数据就会被复制一份出来, 用于生成索引。因此, 给表添加索引,会增加表的体积, 占用磁盘存储空间。

非聚集索引和聚集索引的区别: 通过聚集索引可以查到需要查找的数据, 而通过非聚集索引可以查到记录对应的主键值 , 再使用主键的值通过聚集索引查找到需要的数据。

覆盖索引

覆盖索引与聚集索引和非聚集索引不同,可以不通过主键就能查出所需要的数据。当为字段建立索引以后, 字段中的内容会被同步到索引之中, 如果为一个索引指定两个字段, 那么这个两个字段的内容都会被同步至索引之中。通过覆盖索引直接查找的方式, 可以省略不使用覆盖索引查找的后面两个步骤, 大大的提高了查询性能。

分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改