精华内容
下载资源
问答
  • 以下回答全部是基于MySQL的InnoDB引擎例如对于下面这一张表如果我们按照 name 字段来建立索引的话,采用B+树的结构,大概的索引结构如下如果我们要进行模糊查找,查找name 以“张"开头的所有人的ID,即 sql 语句为...

    1. 什么是最左前缀原则?

    以下回答全部是基于MySQL的InnoDB引擎

    例如对于下面这一张表

    a018ed1d70b4ad5f49611c7a92436c74.png

    如果我们按照 name 字段来建立索引的话,采用B+树的结构,大概的索引结构如下

    fc02304eaf546822091e66fe576cda82.png

    如果我们要进行模糊查找,查找name 以“张"开头的所有人的ID,即 sql 语句为

    select ID from table where name like '张%'

    由于在B+树结构的索引中,索引项是按照索引定义里面出现的字段顺序排序的,索引在查找的时候,可以快速定位到 ID 为 100的张一,然后直接向右遍历所有张开头的人,直到条件不满足为止。

    也就是说,我们找到第一个满足条件的人之后,直接向右遍历就可以了,由于索引是有序的,所有满足条件的人都会聚集在一起。

    而这种定位到最左边,然后向右遍历寻找,就是我们所说的最左前缀原则。

    2. 为什么用 B+ 树做索引而不用哈希表做索引?

    1、哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置,这样的话,如果我们要进行模糊查找的话,显然哈希表这种结构是不支持的,只能遍历这个表。而B+树则可以通过最左前缀原则快速找到对应的数据。

    2、如果我们要进行范围查找,例如查找ID为100 ~ 400的人,哈希表同样不支持,只能遍历全表。

    3、索引字段通过哈希映射成哈希码,如果很多字段都刚好映射到相同值的哈希码的话,那么形成的索引结构将会是一条很长的链表,这样的话,查找的时间就会大大增加。

    3. 主键索引和非主键索引有什么区别?

    例如对于下面这个表(其实就是上面的表中增加了一个k字段),且ID是主键。

    8614e41ea6b9fd95accedc9288ffb5cc.png

    主键索引和非主键索引的示意图如下:

    77ee2e3cda1156586eed50e359bf971b.png

    其中R代表一整行的值。

    从图中不难看出,主键索引和非主键索引的区别是:非主键索引的叶子节点存放的是主键的值,而主键索引的叶子节点存放的是整行数据,其中非主键索引也被称为二级索引,而主键索引也被称为聚簇索引。

    根据这两种结构我们来进行下查询,看看他们在查询上有什么区别。

    1、如果查询语句是 select * from table where ID = 100,即主键查询的方式,则只需要搜索 ID 这棵 B+树。

    2、如果查询语句是 select * from table where k = 1,即非主键的查询方式,则先搜索k索引树,得到ID=100,再到ID索引树搜索一次,这个过程也被称为回表。

    现在,知道他们的区别了吧?

    4. 为什么建议使用主键自增的索引?

    对于这颗主键索引的树

    04ebd3a20f911e6a4f99f54105966cbc.png

    如果我们插入 ID = 650 的一行数据,那么直接在最右边插入就可以了

    6d47aa2a76276044dd77b031b38aa9cf.png

    但是如果插入的是 ID = 350 的一行数据,由于 B+ 树是有序的,那么需要将下面的叶子节点进行移动,腾出位置来插入 ID = 350 的数据,这样就会比较消耗时间,如果刚好 R4 所在的数据页已经满了,需要进行页分裂操作,这样会更加糟糕。

    但是,如果我们的主键是自增的,每次插入的 ID 都会比前面的大,那么我们每次只需要在后面插入就行, 不需要移动位置、分裂等操作,这样可以提高性能。也就是为什么建议使用主键自增的索引。

    展开全文
  • 主键索引和非主键索引

    千次阅读 2019-09-07 22:38:40
    非主键索引和主键索引的区别是:主键索引的叶子节点存放的是主键的值,主键索引的叶子节点存放的是整行数据。其中,主键索引又称为二级索引,主键索引又称为聚簇索引。 根据这两种结构我们来进行下查询,看看...

    对于一张数据表,ID为主键
    在这里插入图片描述

    在这里插入图片描述
    非主键索引和主键索引的区别是:非主键索引的叶子节点存放的是主键的值,主键索引的叶子节点存放的是整行数据。其中,非主键索引又称为二级索引,主键索引又称为聚簇索引。

    根据这两种结构我们来进行下查询,看看他们在查询上有什么区别。
    1、如果查询语句是 select * from table where ID = 100,如果是主键索引则只需要搜索ID这颗B+树。
    2.如果查询语句是 select * from table where k = 1,即非主键的查询方式,要先搜索K索引树,然后再对ID索引树搜索一遍。

    参考:
    https://www.cnblogs.com/heishuichenzhou/p/10813463.html

    展开全文
  • MySQL索引之主键索引

    2020-12-15 16:43:01
    上次的分享我们介绍了聚集索引和非聚集索引的区别,本次我们继续介绍主键索引和辅助索引的区别。 1、主键索引 主键索引,简称主键,原文是PRIMARY KEY,由一个或多个列组成,用于唯一性标识数据表中的某一条记录。一...
  • 聚簇索引和非聚簇索引(主键索引和非主键索引) 主键索引也被称为聚簇索引。 聚簇索引:聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。聚簇索引将索引和数据行放到了一块,找到索引也就找到了数据。...

    聚簇索引和非聚簇索引(主键索引和非主键索引)

    • 主键索引也被称为聚簇索引。
    • 聚簇索引: 聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。聚簇索引将索引和数据行放到了一块,找到索引也就找到了数据。因为无需进行回表操作,所以效率很高。
      • 回表查询:InnoDB 中,对于主键索引,只需要走一遍主键索引的查询就能在叶子节点拿到数据。而对于普通索引,叶子节点存储的是 key + 主键值,因此需要再走一次主键索引,通过主键索引找到行记录,这就是所谓的回表查询,先定位主键值,再定位行记录
    • InnoDB中必然且只会有一个聚集索引,一般是主键,如果没有主键,就会优先选择非空的唯一索引,如果唯一索引页没有,就会创建一个隐藏的 row_id 作为聚集索引。
      • 优点:由于它将索引和数据保存在同一个B+树中,所以查找效率高,又由于叶子节点间双向链表的存在,是它对主键的排序查找和范围查找速度也非常快;
      • 缺点:对表进行修改的速度较慢,因为为了保证表中记录的物理顺序和索引的顺序一致,会把记录插到数据页(叶子节点)的相应位置,所以会产生数据重排,而且插入新记录时为了维持B+树的特性,会频繁的分裂调整,影响了整体插入效率。
      • 所以建议使用聚簇索引的场景是需要进行排序或范围查找的场景,比如实现电子邮箱获取用户邮件时,如果不使用聚簇索引,则每封邮件都可能产生一次磁盘IO,如果采用聚簇索引,根据用户ID来聚集数据,那么只需要从磁盘读取少量的数据页就可以获得某个用户全部的邮件了。(where id > #{maxId})
        (聚簇索引的存储不是物理上连续的,而是逻辑上连续的,这样可以降低维护成本。每张表只能有一个聚簇索引,因为数据页只能按照一棵B+树来排序)
    • 非聚簇索引(非主键索引) :它将数据存储和索引分开,找到索引后,需要通过对应的地址找到对应的数据行。MyISAM 的索引方式就是非聚簇索引。
      • 缺点:当通过非聚簇索引来查找数据时,首先遍历找到对应的叶子节点,也就找到了聚簇索引的主键,然后通过聚簇索引找到对应的数据。所以它需要两次索引查找,效率相对较低,这是它的。
      • 优点:插入记录时不会引起数据顺序的重组。所以建议使用非聚簇索引的场景是频繁更新的列。

    走普通索引,一定会出现回表查询吗?

    不一定,如果查询语句所要求的字段全部命中了索引,那么就不必再进行回表查询就能拿到所有的请求数据。
    很容易理解,有一个 user 表,主键为 id,name 为普通索引,则再执行:select id, name from user where name = ‘joonwhee’ 时,通过name 的索引就能拿到 id 和 name了,因此无需再回表去查数据行了。

    展开全文
  • 聚集索引和非聚集索引 聚集索引: 按照每张表的主键构造B+树,中间节点用来存放索引,叶子节点(数据页)用来存放行的全部数据,按照主键的顺序排序,每个数据页都通过一个双向链表来连接,因此,聚集索引能够在B+...

    聚簇索引和非聚簇索引(主键索引和非主键索引)

    • 主键索引也被称为聚簇索引。
    • 聚簇索引: 聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。聚簇索引将索引和数据行放到了一块,找到索引也就找到了数据。因为无需进行回表操作,所以效率很高。
      • 回表查询:InnoDB 中,对于主键索引,只需要走一遍主键索引的查询就能在叶子节点拿到数据。而对于普通索引,叶子节点存储的是 key + 主键值,因此需要再走一次主键索引,通过主键索引找到行记录,这就是所谓的回表查询,先定位主键值,再定位行记录
    • InnoDB中必然且只会有一个聚集索引,一般是主键,如果没有主键,就会优先选择非空的唯一索引,如果唯一索引页没有,就会创建一个隐藏的 row_id 作为聚集索引。
      • 优点:由于它将索引和数据保存在同一个B+树中,所以查找效率高,又由于叶子节点间双向链表的存在,是它对主键的排序查找和范围查找速度也非常快;
      • 缺点:对表进行修改的速度较慢,因为为了保证表中记录的物理顺序和索引的顺序一致,会把记录插到数据页(叶子节点)的相应位置,所以会产生数据重排,而且插入新记录时为了维持B+树的特性,会频繁的分裂调整,影响了整体插入效率。
      • 所以建议使用聚簇索引的场景是需要进行排序或范围查找的场景,比如实现电子邮箱获取用户邮件时,如果不使用聚簇索引,则每封邮件都可能产生一次磁盘IO,如果采用聚簇索引,根据用户ID来聚集数据,那么只需要从磁盘读取少量的数据页就可以获得某个用户全部的邮件了。(where id > #{maxId})
        (聚簇索引的存储不是物理上连续的,而是逻辑上连续的,这样可以降低维护成本。每张表只能有一个聚簇索引,因为数据页只能按照一棵B+树来排序)
    • 非聚簇索引(非主键索引) :它将数据存储和索引分开,找到索引后,需要通过对应的地址找到对应的数据行。MyISAM 的索引方式就是非聚簇索引。
      • 缺点:当通过非聚簇索引来查找数据时,首先遍历找到对应的叶子节点,也就找到了聚簇索引的主键,然后通过聚簇索引找到对应的数据。所以它需要两次索引查找,效率相对较低,这是它的。
      • 优点:插入记录时不会引起数据顺序的重组。所以建议使用非聚簇索引的场景是频繁更新的列。

    走普通索引,一定会出现回表查询吗?

    不一定,如果查询语句所要求的字段全部命中了索引,那么就不必再进行回表查询就能拿到所有的请求数据。
    很容易理解,有一个 user 表,主键为 id,name 为普通索引,则再执行:select id, name from user where name = ‘joonwhee’ 时,通过name 的索引就能拿到 id 和 name了,因此无需再回表去查数据行了。

    展开全文
  • Mysql主键索引非主键索引区别

    千次阅读 2020-05-05 01:18:05
    什么是索引 MySql官方索引的定义:索引(Index)是帮助MySql高效获取数据的...1.主键索引索引着数据,然而普通索引索引着主键ID值(这是在innodb中,但是如果是myisam中,主键索引和普通索引是没有区别的都是直接索...
  • MySQL重新建立主键索引和非主键索引的方法 重新建立主键索引方法 这是错误的写法 alter table T drop primary key; alter table T add primary key(id); mysql官方文档写了三种措施: 整个数据库迁移,先dump出来...
  • mysql主键索引非主键索引区别

    千次阅读 2019-03-06 18:19:54
    主键是逻辑键,索引是物理键。主键不实际存在,而索引实际存在于数据库中。 索引会真正产生文件。数据会真正产生文件。 redo log 记录的内容:物理日志,"某个数据页上做了什么修改" ,循环使用。 bin log...
  • 主键索引和普通索引有什么区别?

    千次阅读 2020-01-17 09:57:19
      在 MySQL 中, 索引是在存储...在 InnoDB 中, 表都是根据主键顺序以索引的形式存放的, InnoDB 使⽤了 B+ 树索引模型,所以数据都是存储在 B+ 树中的, 如图所示: 从图中可以看出, 根据叶子节点内容不同,索引类...
  • 一、 创建主键(主键=主键索引=聚集索引) 主键是什么? 答:拿主键可以唯一确定一条数据,它物理存储排序一致,不能为空,一个表只能有一个。 原本没有创建的主键的表在磁盘上存储为: Id=0;username=username0;sex...
  • 聚簇索引与主键的选择一、什么是聚簇索引?二、什么是非聚簇索引?1. InnoDB引擎中2. MyISAM引擎中三、聚簇索引的优劣与主键选择的关系 ...主键索引就是一种聚簇索引,而其他创建出来的前缀索引、联合索引,唯
  • 主要介绍MySQL 主键索引的联系与区别,使用mysql的朋友可以看下
  • 很多人会把Primary Key聚集索引搞混起来,或者认为这是同一个东西。这个概念是非常错误的。 主键是一个约束(constraint),他依附在一个索引上,这个索引可以是聚集索引,也可以是非聚集索引。 所以在一个(或一...
  • 主键、聚集索引聚集索引区别

    千次阅读 2020-07-14 10:59:36
    主键 聚集索引 用途 强制表的实体完整性 对数据行的排序,方便查询用 一个表多少个 一个表最多一个主键 一个表最多一个聚集索引 是否允许多个字段来定义 一个主键可以多个字段来定义 ...
  • 下面本篇文章就来给大家介绍一下主键索引和普通索引之间的区别 ,希望对你们有所帮助。普通索引普通索引是最基本的索引类型,而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建:创建索引,例如CREATE ...
  • 主键索引和普通索引的区别

    万次阅读 2019-08-07 17:46:28
    主键索引和普通索引的区别 主键索引的叶子结点存放了整行记录,普通索引的叶子结点存放了主键ID,查询的时候需要做一次回表查询 一定要回表查询么? 不一定,当查询的字段刚好是索引的字段或者索引的一部分,就可以...
  • 聚簇索引与聚簇索引 假设存在这样一个表,表t1,主键为int类型的id,还有一个int类型的num。 create table t1( int id primary key auto_increment;...可以看出,主键索引的叶子节点存放的是整行数
  • 主键索引 主键索引,简称主键,原文是PRIMARY KEY,由一个或多个列组成,用于唯一性标识数据表中的某一条记录。一个表可以没有主键,但最多只能有一个主键,并且主键值不能包含NULL。 在MySQL中,InnoDB数据表的主键...
  • 上次的分享我们介绍了聚集索引和非聚集索引的区别,本次我们继续介绍主键索引和辅助索引的区别。1、主键索引主键索引,简称主键,原文是PRIMARY KEY,由一个或多个列组成,用于唯一性标识数据表中的某一条记录。一个...
  • 聚簇索引和主键索引聚簇索引并不是一种单独的索引类型,而是一种数据存储方式,具体细节依赖于其实现方式。MySQL数据库中innodb存储引擎,B+树索引可以分为:聚簇索引(也称聚集索引,clustered index)辅助索引(有时...
  • 今天给大家介绍mysql InnoDB 下3种常用的索引:主键索引(聚集索引)、聚集索引、覆盖索引 下面已一张表具体的表为例:来演示数据查找的过程。 譬如:一张用户表 User表 Id是主键 主键索引,也被称为”聚集...
  • 总结,对于主键与唯一索引约束: • 执行insertupdate时,会触发约束检查 • InnoDB违反约束时,会回滚对应SQL • MyISAM违反约束时,会中断对应的SQL,可能造成不符合预期的结果集 • 可以使用 insert … on ...
  • 文章目录MySQL学习笔记-主键索引和二级索引1.笔记图2.索引的常见模型3.InnoDB 的索引模型4.主键索引和普通索引查询的区别5.如何选择主键字段6.笔记图7.回表过程8.最左前缀原则 MySQL学习笔记-主键索引和二级索引 在 ...
  • 主键与聚集索引

    2020-09-11 08:57:38
    表通常具有包含唯一标识表中每一行的值的一列或一组列。这样的一列或多列称为表的主键 (PK),用于强制表的实体完整性。在创建或修改表时,您可以通过定义 PRIMARY KEY 约束来创建主键
  • 主要给大家介绍了关于当Mysql行锁遇到复合主键与多列索引的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Mysql具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
  • 各种树的应用场景 AVL树: 最早的平衡二叉树之一。...B/B+树: 用在磁盘文件组织 数据索引和数据库索引。 Trie树(字典树): 用在统计和排序大量字符串,如自动机。 索引分类 1, 全文索引 2, 哈希索引 3, b+树索引 ...
  • 主键索引和唯一索引的区别

    千次阅读 2018-09-26 19:09:54
    主键约束(PRIMARY KEY): 1.主键用于唯一的标识表中的每一...4.主键可作外键,唯一索引不可。 唯一约束(UNIQUE): 1.唯一约束用来限制不受主键约束的列上的数据的唯一性,用于作为访问某行的可选手段,一个表...
  • (一)原理  只要用户定义的索引字段中包含了主键中的字段,那么这个字段不会再被InnoDB自动加到索引中。但如果用户的索引字段中没有完全...  idx1idx2两个索引内部大小完全一样,没有区别  例子二: CREA

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 285,653
精华内容 114,261
关键字:

主键索引和非主键索引