MySQL中Innodb的聚簇索引和非聚簇索引

Wesley13
• 阅读 849

聚簇索引

数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引(又叫二级索引)两种。Innodb的聚簇索引在同一个B-Tree中保存了索引列和具体的数据,在聚簇索引中,实际的数据保存在叶子页中,中间的节点页保存指向下一层页面的指针。“聚簇”的意思是数据行被按照一定顺序一个个紧密地排列在一起存储。一个表只能有一个聚簇索引,因为在一个表中数据的存放方式只有一种。

一般来说,将通过主键作为聚簇索引的索引列,也就是通过主键聚集数据。下图展示了Innodb中聚簇索引的结构(图片来自《高性能MySQL(第三版)》):

MySQL中Innodb的聚簇索引和非聚簇索引

聚簇索引的结构

这里要特别注意的概念,一个页可以理解为一块具有一定大小的连续的存储区域。相同页内的数据行在物理上是相邻的,因此逻辑上键值相邻的页在物理上可能相隔很远。

在中间的某个节点页中,主键<11的叶子页和11<主键<21的叶子页分别被两个指针所指向,且主键<11的叶子页也有一个指针指向了11<主键<21的叶子页,其余页之间的关系也是一样。

聚簇索引的优点

  1. 聚簇索引将索引和数据行保存在同一个B-Tree中,查询通过聚簇索引可以直接获取数据,相比非聚簇索引需要第二次查询(非覆盖索引的情况下)效率要高。
  2. 聚簇索引对于范围查询的效率很高,因为其数据是按照大小排列的,

聚簇索引的缺点

  1. 聚簇索引的更新代价比较高,如果更新了行的聚簇索引列,就需要将数据移动到相应的位置。这可能因为要插入的页已满而导致“页分裂”。
  2. 插入速度严重依赖于插入顺序,按照主键进行插入的速度是加载数据到Innodb中的最快方式。如果不是按照主键插入,最好在加载完成后使用OPTIMIZE TABLE命令重新组织一下表。
  3. 聚簇索引在插入新行和更新主键时,可能导致“页分裂”问题。
  4. 聚簇索引可能导致全表扫描速度变慢,因为可能需要加载物理上相隔较远的页到内存中(需要耗时的磁盘寻道操作)。

非聚簇索引

非聚簇索引,又叫二级索引。二级索引的叶子节点中保存的不是指向行的物理指针,而是行的主键值。当通过二级索引查找行,存储引擎需要在二级索引中找到相应的叶子节点,获得行的主键值,然后使用主键去聚簇索引中查找数据行,这需要两次B-Tree查找。

总结

下面是Innodb聚簇索引和非聚簇索引的示意图(图片来自《高性能MySQL(第三版)》:

MySQL中Innodb的聚簇索引和非聚簇索引

Innodb聚簇索引和非聚簇索引

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
待兔 待兔
5个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
java常见笔试编程题,深夜思考
一面(一个半小时)1.首先自我介绍2.了解Web层开发?数据库索引了解么?聚簇索引,非聚簇索引?索引分类?3.了解数据库都由哪些引擎?分别有什么区别和使用场景?4.了解分布式?高可用?如何保证节点集群的同步?Nginx了解过么?5.什么是事务,数据库的隔离级别,Mysql默认的隔离级别。6.JVM的内存模型,GC算法7.非递归实现
MYSQL-INNODB索引构成详解
对于MYSQL的INNODB存储引擎的索引,大家是不陌生的,都能想到是B树结构,可以加速SQL查询。但对于B树索引,它到底“长”得什么样子,它具体如何由一个个字节构成的,这些的基础知识鲜有人深究。本篇文章从MYSQL行记录开始说起,层层递进,包括数据页,B树聚簇索引,B树二级索引,最后在文章末尾给出MYSQL索引的建议。文章涉及较多基础知识,内容较为枯燥,因此采用较多的图片补充说明,希望能对读者有帮助。
Easter79 Easter79
3年前
TiDB 5.0 RC Release Notes
TiDB5.0.0rc版本是5.0版本的前序版本。在5.0版本中,我们专注于帮助企业基于TiDB数据库快速构建应用程序,使企业在构建过程中无需担心数据库的性能、性能抖动、安全、高可用、容灾、SQL语句的性能问题排查等问题。在TiDB5.0版本中,你可以获得以下关键特性:开启聚簇索引功能,提升数据库的性能。例如:TPC
Wesley13 Wesley13
3年前
MySql数据库索引
InnoDB存储引擎索引:B树索引:不能找到一个给定键值的具体行,能找到的只是被查找数据行所在的页。然后把页加载到内存,在查询所要的数据。全文索引:哈希索引:InnoDB会根据表的使用情况自动为表生成哈希索引,不能人为的干预是否在一张表中生成哈希索引B树索引在数据库中的高度一般是2~4层,所以查询最多需要2到4次IO。B树索引分为聚
Wesley13 Wesley13
3年前
mysql5.6 分页查询优化
mysql5.6分页查询优化场景:表结构:主键(非自增)contentCode(varchar),过滤条件列为updateTime(timeStamp),已经为timestamp建立索引。搜索sql为:SELECTFROMmy_hello_tableWHEREupdat
Wesley13 Wesley13
3年前
Mysql表设计需要注意的问题
下面探讨的数据库为MySQL存储引擎为innodb因为这是最常见的,使用最多的数据库和引擎什么是页分裂?这是因为聚簇索引采用的是平衡二叉树算法,而且每个节点都保存了该主键所对应行的数据,假设插入数据的主键是自增长的,那么根据二叉树算法会很快的把该数据添加到某个节点下,而其他的节点不用动;但是如果插入的是不规则的数据,那么每次插入都会改变二叉树之前
Wesley13 Wesley13
3年前
MySQL 数据库设计总结
本文由云社区发表作者:漆洪凯规则1:一般情况可以选择MyISAM存储引擎,如果需要事务支持必须使用InnoDB存储引擎。注意:MyISAM存储引擎Btree索引有一个很大的限制:参与一个索引的所有字段的长度之和不能超过1000字节。另外MyISAM数据和索引是分开,而InnoDB的数据存储是按聚簇(cluster)索引有序排
京东云开发者 京东云开发者
6个月前
ClickHouse内幕(3)基于索引的查询优化
ClickHouse索引采用唯一聚簇索引的方式,即Part内数据按照orderbykeys有序,在整个查询计划中,如果算子能够有效利用输入数据的有序性,对算子的执行性能将有巨大的提升。本文讨论ClickHouse基于索引的查询算子优化方式。在整个查询计划中