首页 > 数据库 >数据库聚簇索引——not null条件对唯一键索引成为聚簇索引的影响

数据库聚簇索引——not null条件对唯一键索引成为聚簇索引的影响

时间:2024-05-27 22:22:39浏览次数:16  
标签:index 聚簇 主键 索引 null id

数据库聚簇索引的规则如下:

  • 如果有主键,则主键是聚簇索引(当然主键也不一定是单个列的);
  • 如果没有主键,但是有not null修饰的唯一键索引,则这个索引是聚簇索引;
  • 都没有,创建一个叫db_row_id的6字节隐藏列为聚簇索引,这个索引程序员不可见(这个列存在一定冲突和性能问题,详见大佬:https://www.cnblogs.com/frankcui/p/15226301.html)。

所以一般还是要定义主键。如果不定义主键,再想通过创建索引的方式提高性能,那无论创建多少个索引,本质上都是二级索引,都要进行回表(本来一个主键就可以解决的事情,现在仍旧有一个聚簇索引,还额外多了那个原来定义主键就可以创建的索引,反而占用空间)。同样的查询,IO次数变多,这是不值当的。

那有没有not null是否真的影响unique列成为聚簇索引?

看起来我们无法直接从explain中找到是否走聚集索引的信息,官方文档中介绍到,extra字段中大抵只是看是否覆盖,而聚集索引是一个“索引即数据”的概念,它就是完整的表了,即便没有覆盖掉所有查询的列,也是真正覆盖了,所以使用聚集索引的extra字段为null。官方文档:

https://www.cnblogs.com/kerrycode/p/9909093.html

Using index (JSON property: using_index)

The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

For InnoDB tables that have a user-defined clustered index, that index can be used even when Using index is absent from the Extra column. This is the case if type is index and key is PRIMARY.

从表中仅使用索引树中的信息就能获取查询语句的列的信息, 而不必进行其他额外查找(seek)去读取实际的行记录。当查询的列是单个索引的部分的列时, 可以使用此策略。(简单的翻译就
是:使用索引来直接获取列的数据,而不需回表)。对于具有用户定义的聚集索引的 InnoDB 表, 即使从Extra列中没有使用索引, 也可以使用该索引。如果type是index并且Key是主键, 则会出现这种情况。

翻译翻译就是你用主键的聚簇索引,可能看起来没有覆盖,但是实际上所有数据都在这里了,默认就是覆盖了。

那怎么判断是否走了聚簇索引呢?就算有唯一键的聚簇索引,用了还是不知道的。我有这样一个思路:

如果唯一键可以为null时,按照理论不应该是聚簇索引,当查询的列有的不在唯一键索引覆盖范围内则应该会有回表(回聚集索引去查),即可以从某些方面佐证发生回表,如索引下推。接下来的目的就是寻找这个证据。

3309036-20240527213243861-1279717842

准备两张表,分别加上not null(按理论为聚簇索引)default null(可以为空)修饰:

CREATE TABLE `test_unique` (
  `id` int NOT NULL,
  `col1` varchar(255),
  `col2` varchar(255) NOT NULL,
  UNIQUE KEY `uni` (`id`) USING BTREE COMMENT '测试没有主键的情况下,唯一键是不是聚簇索引与not null的关系'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
CREATE TABLE `test_unique` (
  `id` int DEFAULT NULL,
  `col1` varchar(255),
  `col2` varchar(255) NOT NULL,
  UNIQUE KEY `uni` (`id`) USING BTREE COMMENT '测试没有主键的情况下,唯一键是不是聚簇索引与not null的关系'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

准备数据:

分别执行这样一个语句:

explain select id, col1 from test_unique where id in (89,98);

结果:

区别就在于Using index condition,找到对这个值的解释:https://www.cnblogs.com/echoppy/p/14247575.html

很幸运得到这个结果,我的mysql的版本支持索引下推,也从侧面佐证了“当唯一键索引为null时,并不能成为聚簇索引,否则就直接查出所有的值,不发生下推”。到此证明结束。

另:

当我在试图寻找佐证时,有一个现象引起了我的好奇:尝试用这样一个语句去判断是否走索引:

explain select id, col1 from test_unique where id > 23;

注意到唯一键索引可以为空时,范围查询会使索引失效:

NOT NULL时,可以发现范围查询走了索引,此时的唯一键索引与主键无二。

小结

实验本身的目的不在于试图推翻现在的结论,而是对现有的结论多一些直观的认识,并通过实践的形式对感兴趣的概念加深认识和理解。本文涉及的知识点有索引以及

标签:index,聚簇,主键,索引,null,id
From: https://www.cnblogs.com/pidanhub/p/18216553

相关文章

  • 搜索引擎ElasticSearch18_Spring Data ElasticSearch 使用6
    一、SpringDataElasticSearch简介1、什么是SpringDataSpringData是一个用于简化数据库访问,并支持云服务的开源框架。其主要目标是使得对数据的访问变得方便快捷,并支持map-reduce框架和云计算数据服务。SpringData可以极大的简化JPA的写法,可以在几乎不用写实现的情况......
  • 搜索引擎ElasticSearch18_ElasticSearch编程操作5
    一、创建工程,导入坐标pom.xml坐标<dependencies><dependency><groupId>org.elasticsearch</groupId><artifactId>elasticsearch</artifactId><version>5.6.8</version></dependency>......
  • 数据结构与算法学习(05)查找(2)索引——BUAA
    文章目录查找(2)——索引介绍索引的基本概念稠密索引非稠密索引——分块索引多级索引查找(2)——索引介绍本文为查找第二部分,主要是整理了本人上课时讲的内容索引的基本概念索引:记录关键字值与记录的存储位置之间的对应关系索引文件:由基本数据与索引表两部分组成的......
  • 磁力多多,搜索引擎大全,如何使用蜘蛛磁力查找磁力链
    磁力链接是一种特殊的下载链接,磁力链接可以理解为一个文件识别码,而并非具体的资源地址,下载软件需要拿着这个识别码去整个互联网(DHT网络)去寻找持有该资源的用户(节点),如果找到则可以进行传输下载。一般年代越久远的磁力链接下载成功的几率越小,因为持有该资源的节点越少。一般......
  • 数据库索引:综合详细指南
    简介数据库索引对于优化数据库性能至关重要。它们通过提供表中行的快速访问路径来帮助加快数据检索速度。了解索引的工作原理、类型及其最佳实践可以显著提高数据库查询的效率。什么是索引?索引是一种数据结构,可以提高数据库表上数据检索操作的速度。它就像书中的索引一样,让您无......
  • 深入《MySQL视图》及《MySQL索引与分区》
    一. MySQL视图#MySQL视图深度指南:从新手到专家欢迎来到这篇全面的MySQL视图教程。在数据库的世界中,视图(View)是一个强大的工具,它允许你以一种安全且用户友好的方式操作数据。本篇博文将带你了解什么是视图、如何创建它们,以及如何有效地使用视图来简化你的数据库操作。##什......
  • mysql 中索引类型有哪些,以及对数据库的性能的影响?
    索引类型普通索引:允许被索引的数据列包含重复的值唯一索引:可以保证数据记录的唯一性主键索引:是一种特殊的唯一素引,在一张表中只能定义一个主键索引,主键用于唯一标识一条记录,使用关键字primarykey来创建联合索引:索引可以覆盖多个数据列全文索引:通过建立倒排索引,可以极大......
  • Oracle创建索引普通索引,唯一索引,复合索引,添加主键
    Oracle创建索引普通索引,唯一索引,复合索引,添加主键创建索引//创建普通索引CREATEINDEX索引名ON表名(列名);//复合索引创建CREATEINDEX索引名ON表名(列名1,列名2,列名3,...);//创建唯一索引CREATEUNIQUEINDEX索引名ON表名(列名);//创建唯一索引CREAT......
  • MySQL-10.索引优化与查询优化
    C-10.索引优化与查询优化都有那些维度可以进行数据库调优?简言之:索引失效,没有充分利用到索引--索引建立关联查询太多JOIN(设计缺陷或不得已的需求)--SQL优化服务器调优及各个参数设置(缓冲,线程数等)--调整my.cnf数据过多--分库分表关于数据库调优的知识点非常分散......
  • mysql中索引的使用
    大家好,我们知道创建索引是要付出时间和空间的代价的,只有合理的创建索引才能提高查询效率。今天我们来聊一聊如何优雅的,合理的使用索引。上篇文章我们讲到了在索引在where查询过程中是如何发挥作用的,今天我们再谈谈索引在排序和分组的时候是如何使用的。为了方便讲解,我们依......