mysql innodb索引原理

时间: 2019-03-04阅读: 1128标签: mysql

聚集索引(clustered index)

innodb存储引擎表是索引组织表,表中数据按照主键顺序存放。其聚集索引就是按照每张表的主键顺序构造一颗B+树,其叶子结点中存放的就是整张表的行记录数据,这些叶子节点成为数据页。

聚集索引的存储并不是物理上连续的,而是逻辑上连续的,叶子结点间按照主键顺序排序,通过双向链表连接。多数情况下,查询优化器倾向于采用聚集索引,因为聚集索引能在叶子结点直接找到数据,并且因为定义了数据的逻辑顺序,能特别快的访问针对范围值的查询。

聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。由于表里的数据只能按照一颗B+树排序,因此一张表只能有一个聚簇索引。

在Innodb中,聚簇索引默认就是主键索引。如果没有主键,则按照下列规则来建聚簇索引:

  • 没有主键时,会用一个非空并且唯一的索引列做为主键,成为此表的聚簇索引;
  • 如果没有这样的索引,InnoDB会隐式定义一个主键来作为聚簇索引。

由于主键使用了聚簇索引,如果主键是自增id,那么对应的数据也会相邻地存放在磁盘上,写入性能较高。如果是uuid等字符串形式,频繁的插入会使innodb频繁地移动磁盘块,写入性能就比较低了。


B+树(多路平衡查找树)

我们知道了innodb引擎索引使用了B+树结构,那么为什么不是其他类型树结构,例如二叉树呢?

计算机在存储数据的时候,有最小存储单元,这就好比人民币流通最小单位是分一样。文件系统的最小单元是块,一个块的大小是4k(这个值根据系统不同并且可设置),InnoDB存储引擎也有自己的最小储存单元—页(Page),一个页的大小是16K(这个值也是可设置的)。

文件系统中一个文件大小只有1个字节,但不得不占磁盘上4KB的空间。同理,innodb的所有数据文件的大小始终都是16384(16k)的整数倍。


所以在MySQL中,存放索引的一个块节点占16k,mysql每次IO操作会利用系统的预读能力一次加载16K。这样,如果这一个节点只放1个索引值是非常浪费的,因为一次IO只能获取一个索引值,所以不能使用二叉树。

B+树是多路查找树,一个节点能放n个值,n = 16K / 每个索引值的大小。
例如索引字段大小1Kb,这时候每个节点能放的索引值理论上是16个,这种情况下,二叉树一次IO只能加载一个索引值,而B+树则能加载16个。

B+树的路数为n+1,n是每个节点存在的值数量,例如每个节点存放16个值,那么这棵树就是17路。

从这里也能看出,B+树节点可存储多个值,所以B+树索引并不能找到一个给定键值的具体行。B+树只能找到存放数据行的具体页,然后把页读入到内存中,再在内存中查找指定的数据。

附:B树和B+树的区别在于,B+树的非叶子结点只包含导航信息,不包含实际的值,所有的叶子结点和相连的节点使用链表相连,便于区间查找和遍历。


辅助索引

也称为非聚集索引,其叶子节点不包含行记录的全部数据,叶子结点除了包含键值以外,每个叶子结点中的索引行还包含一个书签,该书签就是相应行的聚集索引键。

如下图可以表示辅助索引和聚集索引的关系(图片源自网络,看大概意思即可):


当通过辅助索引来寻找数据时,innodb存储引擎会通过辅助索引叶子节点获得只想主键索引的主键,既然后再通过主键索引找到完整的行记录。

例如在一棵高度为3的辅助索引树中查找数据,那需要对这颗辅助索引树进行3次IO找到指定主键,如果聚集索引树的高度同样为3,那么还需要对聚集索引树进行3次查找,最终找到一个完整的行数据所在的页,因此一共需要6次IO访问来得到最终的数据页。

创建的索引,如联合索引、唯一索引等,都属于非聚簇索引。


联合索引

联合索引是指对表上的多个列进行索引。联合索引也是一颗B+树,不同的是联合索引的键值数量不是1,而是大于等于2。

例如有user表,字段为id,age,name,现发现如下两条sql使用频率最多:

Select * from user where age = ? ;
Select * from user where age = ? and name = ?;

这时候不需要为age和name单独建两个索引,只需要建如下一个联合索引即可:

create index idx_age_name on user(age, name)

联合索引的另一个好处已经对第二个键值进行了排序处理,有时候可以避免多一次的排序操作。


覆盖索引

覆盖索引,即从辅助索引中就可以得到查询所需要的所有字段值,而不需要查询聚集索引中的记录。覆盖索引的好处是辅助索引不包含整行记录的所有信息,故其大小要远小于聚集索引,因此可以减少大量的IO操作。

例如上面有联合索引(age,name),如果如下:

select age,name from user where age=?

就能使用覆盖索引了。

覆盖索引的另一个好处是对于统计问题,例如:

select count(*) from user

innodb存储引擎并不会选择通过查询聚集索引来进行统计。由于user表上还有辅助索引,而辅助索引远小于聚集索引,选择辅助索引可以减少IO操作。


注意事项

  • 索引只建合适的,不建多余的
因为每当增删数据时,B+树都要进行调整,如果建立多个索引,多个B+树都要进行调整,而树越多、结构越庞大,这个调整越是耗时耗资源。如果减少了这些不必要的索引,磁盘的使用率可能会大大降低。
  • 索引列的数据长度能少则少。
索引数据长度越小,每个块中存储的索引数量越多,一次IO获取的值更多。
  • 匹配列前缀可用到索引 like 9999%,like %9999%、like %9999用不到索引;
  • Where 条件中in和or可以使用索引, not in 和 <>操作无法使用索引;
如果是not in或<>,面对B+树,引擎根本不知道应该从哪个节点入手。
  • 匹配范围值,order by 也可用到索引;
  • 多用指定列查询,只返回自己想到的数据列,少用select *;
不需要查询无用字段,并且不使用*可能还会命中覆盖索引哦;
  • 联合索引中如果不是按照索引最左列开始查找,无法使用索引;
最左匹配原则;
  • 联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引;
  • 联合索引中如果查询中有某个列的范围查询,则其右边的所有列都无法使用索


本文作者:信~仰
阅读原文


站长推荐

1.云服务推荐: 国内主流云服务商,各类云产品的最新活动,优惠券领取。地址:阿里云腾讯云华为云

链接: http://www.fly63.com/article/detial/2205

MySQL技术内幕:InnoDB存储引擎

本文绝大部分内容来源《MySQL技术内幕:InnoDB存储引擎》一书。InnoDB存储引擎是多线程模型,其后台有多个不同的后台线程,负责处理不同的任务。

Mysql常用函数

MySQL数据库中提供了很丰富的函数。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。

MySQL索引分类,90%的开发都不知道

MySQL的索引分类问题一直让人头疼,几乎所有的资料都会给你列一个长长的清单,给你介绍什么主键索引、单值索引,覆盖索引,自适应哈希索引,全文索引,聚簇索引,非聚簇索引等

Mysql使用索引可能失效的场景

WHERE字句的查询条件里有不等于号(WHERE column!=…),MYSQL将无法使用索引;类似地,如果WHERE字句的查询条件里使用了函数(如:WHERE DAY(column)=…),MYSQL将无法使用索引

MySQL查漏补缺

唯一索引比普通索引快吗, 为什么?MySQL由哪些部分组成, 分别用来做什么.MySQL查询缓存有什么弊端, 应该什么情况下使用, 8.0版本对查询缓存有什么变更.

MySQL中Explain初识

MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化。EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 Explain 就可以了。

mysql图形化管理工_ mysql可视化工具哪个好?

MySQL是一个非常流行的小型关系型数据库管理系统。目前MySQL被广泛地应用在Internet上的 中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据 库

nodejs如何连接mysql?

nodejs连接mysql的方法:先使用命令$cnpm install mysql安装驱动,然后根据实际配置的数据库用户名、密码及数据库名在js文件中编写nodejs与mysql数据库的交互代码。

一条sql语句在mysql中是如何执行的

最近开始在学习mysql相关知识,自己根据学到的知识点,根据自己的理解整理分享出来,本篇文章会分析下一个sql语句在mysql中的执行流程,包括sql的查询在mysql内部会怎么流转,sql语句的更新是怎么完成的。

前端开发对MySql使用总结

数据库引擎使用,这里仅仅只介绍常用的两种引擎,而InnoDB是从MySQL 5.6.版本以后InnoDB就是作为默认启动使用的存储引擎。那么这里下面既是对上面几个概念进行基本的日常操作。

点击更多...

内容以共享、参考、研究为目的,不存在任何商业目的。其版权属原作者所有,如有侵权或违规,请与小编联系!情况属实本人将予以删除!