MySql的覆盖索引

基本概念

聚集索引、非聚集索引

  • 聚集索引(clustered index):聚集索引的叶节点就是最终的数据节点;聚集索引一个表只能存在一个;物理上连续存在(??? 这一点存疑,如果物理上连续,维护成本会非常高); InnoDB存储引擎中,其聚集索引相当于整张表,主键必然是聚集索引,而聚集索引可以不是主键
  • 非聚集索引(nonclustered index):非聚集索引的叶节仍然是索引节点,但它有一个指向最终数据块的指针(不是数据的物理地址 ,而是主键值)

一般建表时都要建立一个自增的主键,例如id,这样mysql会有基于id的聚集索引, 主键自增,会使数据自动向后插入,在磁盘上物理顺序排列
如果聚集索引的值可以改动的话,会造成磁盘上数据的移动,可能出现page分裂, 出现表碎片

InnoDB引擎表是(聚集)索引组织表(clustered index organize table)MyISAM引擎表则是堆组织表(heap organize table),没有聚集索引的概念。
两种存储形式的不同简单来说,IOT表里数据物理存储顺序和主键索引的顺序一致,所以如果新增数据是离散的,会导致数据块趋于离散,而不是趋于顺序;而HOT表数据写入的顺序是按写入时间顺序存储的。
IOT表相比HOT表的优势是:
- 范围查询效率更高
- 数据频繁更新(聚集索引本身不更新)时,更不容易产生碎片
- 特别适合有一小部分热点数据频繁读写的场景
- 通过主键访问数据时快速可达
IOT表的不足则有:
- 数据变化如果是离散为主的话,那么效率会比HOT表差
HOT表的不足有:
- 索引回表读开销很大;
- 大部分数据读取时随机的,无法保证被顺序读取,开销大;
- 每张InnoDB表只能创建一个聚集索引,聚集索引可以由一列或多列组成
-
InnoDB的聚集索引选择规则如下:
- 首先选择显式定义的主键索引做为聚集索引
- 如果没有,则选择第一个不允许NULL的唯一索引
- 还是没有的话,就采用InnoDB引擎内置的ROWID作为聚集索引

覆盖索引

解释1、 覆盖索引(covering index)指一个查询语句的执行只需要从辅助索引就可以得到查询记录,而不需要查询聚集索引,也就是说的不需要回表操作。 回表简单解释就是数据库根据索引找到了指定的记录所在行后,还需要根据rowid再次到数据块里取数据。
解释2:索引覆盖是这样一种索引策略:当某一查询中包含的所需字段皆包含于一个索引中,此时索引将大大提高查询性能。即非聚集索引的叶结点包含所有数据行中的索引列值,使用这些结点即可返回真正的数据,这种情况称之为“索引覆盖”。覆盖索引是mysql dba常用的一种SQL优化手段

要想使用索引覆盖:
1、 返回的字段尽量少,字段包含在复合索引中
2、使用where事, 条件要包含索引的前导字段

参考:
回表:http://blog.csdn.net/simon_steve_sun/article/details/16864949

MySQL索引背后的数据结构及算法原理 : http://blog.codinglabs.org/articles/theory-of-mysql-index.html
mysql索引分析 : http://www.jianshu.com/p/31eb5e4483c8

Mysql聚集索引和非聚集索引(堆组织表和索引组织表) :
http://blog.csdn.net/oChangWen/article/details/53997366
https://www.cnblogs.com/aspnethot/articles/1504082.html

聚集索引与非聚集索引: https://my.oschina.net/osenlin/blog/287558

MySQL索引 : http://solverpeng.com/2017/06/25/MySQL%E7%B4%A2%E5%BC%95/

MySQL 覆盖索引:https://yq.aliyun.com/articles/62419
理解MySQL–索引与优化:http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html
mysql高效索引之覆盖索引:http://www.cnblogs.com/chenpingzhao/p/4776981.html
MySQL索引使用笔记 : http://www.kuqin.com/database/20130529/334520.html

MySql 官方文档:https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_clustered_index

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页