MYSQL 主键索引和辅助索引

最新推荐文章于 2022-02-26 23:11:11 发布
原创 最新推荐文章于 2022-02-26 23:11:11 发布 · 2.2k 阅读 · 7 ·
CC 4.0 BY-SA版权
版权所属：https://xuemingde.com

本文探讨了InnoDB和MYISAM两种存储引擎下主键索引与辅助索引的区别。在InnoDB中，主键索引的叶子节点直接存储数据，辅助索引存储主键ID；而在MYISAM中，无论是主键还是辅助索引，都存储数据的指针。这影响了查询效率和数据存储的方式。

如上图User表，id为主键，name为普通字段。

如果将id和name设置为索引，id是主键索引，name为普通索引，也叫做辅助索引。他们之间有什么区别呢？

InnoDB 存储引擎情况下

在InnoDB存储引擎的B+树的数据结构下，主键索引下跟着的是数据信息，而辅助索引下跟着的是主键的id。

如图所示：

使用主键索引查询的时候，可直接取到数据。

使用主键索引查询的时候，要先得到主键索引值，再根据索引值去匹配数据。

MYISAM 存储引擎情况下

MYISAM 的索引是非聚集索引，索引信息和数据信息在不同的文件下，无论是主键索引还是辅助索引下跟着的都是数据的指引地址，根据数据的指引地址去数据信息文件搜索所需数据。

 

Mysql-InnoDB索引：普通索引、主键索引、唯一索引、组合索引
qq_40432425的博客
05-06 1271
索引底层实现是B+树。B+树类似折半查找，比如说如果有10亿条数据，一次就能砍到一半，数据量越多，索引的作用越大。索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。
MySQL索引之主键索引
12-15
在MySQL里，主键索引和辅助索引分别是什么意思，有什么区别？ 上次的分享我们介绍了聚集索引和非聚集索引的区别，本次我们继续介绍主键索引和辅助索引的区别。 1、主键索引 主键索引，简称主键，原文是PRIMARY KEY，由一个或多个列组成，用于唯一性标识数据表中的某一条记录。一个表可以没有主键，但最多只能有一个主键，并且主键值不能包含NULL。 在MySQL中，InnoDB数据表的主键设计我们通常遵循几个原则： 1、采用一个没有业务用途的自增属性列作为主键； 2、主键字段值总是不更新，只有新增或者删除两种操作； 3、不选择会动态更新的类型，比如当前时间戳等。 这么做的好处有几点： 1、新增数据
mysql 主索引跟辅助索引_MySQL_MySQL索引之主键索引，在MySQL里，主键索引和辅助索 - phpStudy...
weixin_34365336的博客
01-19 1075
MySQL索引之主键索引在MySQL里，主键索引和辅助索引分别是什么意思，有什么区别？上次的分享我们介绍了聚集索引和非聚集索引的区别，本次我们继续介绍主键索引和辅助索引的区别。1、主键索引主键索引，简称主键，原文是PRIMARY KEY，由一个或多个列组成，用于唯一性标识数据表中的某一条记录。一个表可以没有主键，但最多只能有一个主键，并且主键值不能包含NULL。在MySQL中，InnoDB数据表的...
mysql 主索引跟辅助索引_MySQL索引实验-主键索引一定比辅助索引快吗？
weixin_39606048的博客
01-19 204
一、在一个表执行了分别执行了两条语句：a.SELECT ID FROM MNG_ROLE ORDER BY ID; -- 耗时37秒b.SELECT ID FROM MNG_ROLE ORDER BY ID, NAME; -- 耗时0.01秒c.SELECT ID FROM MNG_ROLE; -- 耗时0.22秒二、表结构如下，插入3万条数据，而且REMARK和RESERVER字段都是填满数据：...
主键索引 or 辅助索引？一文告诉你 Mysql limit 优化时的索引选择！
优快云云计算
04-10 2146
作者 | 吴海存责编| 徐威龙封图|优快云下载于视觉中国导读：本文主要针对limit分页时，是优先基于主键索引还是辅助索引等层面展开分析，对limit及offset的用法以及是否...
主键索引和辅助索引的区别（MyISAM和InnoDB）
qq_42533216的博客
02-23 916
1. MyISAM索引实现： 概述： MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。 1）主键索引： MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。 下图是MyISAM主键索引的原理图： 这里设表一共有三列，假设我们以Col1为主键，图myisam1是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数
MySQL 主键与索引的联系与区别分析
09-10
一个表可以有多个索引，包括普通索引、唯一索引和全文索引等。不同于主键必须唯一且不为NULL的特性，索引列可以包含NULL值，并且可以建立在任何列上，无论是否为主键。通过索引，数据库能够避免进行全表扫描，从而...
MySQL中主键索引与聚焦索引之概念的学习教程
09-10
主键索引和聚焦索引是MySQL中两种重要的索引类型，它们各有特点并适用于不同的场景。 **主键索引（PRIMARY KEY）** 是用来唯一标识数据表中每一行记录的特殊索引。它由一个或多个列组成，确保每个值都是唯一的，...
mysql之辅助索引
weixin_33785108的博客
12-26 261
2019独角兽企业重金招聘Python工程师标准>>> ...
mysql 辅助索引
a361536871的博客
12-16 493
二级索引：叶子节点中存储主键值，每次查找数据时，根据索引找到叶子节点中的主键值，根据主键值再到聚簇索引中得到完整的一行记录。 问题： 1.相比于叶子节点中存储行指针，二级索引存储主键值会占用更多的空间，那为什么要这样设计呢？ 　　InnoDB在移动行时，无需维护二级索引，因为叶子节点中存储的是主键值，而不是指针。 2.那么InnoDB有了聚簇索引，为什么还要有二级索引呢？ 　　聚簇索引的叶子节点存储了一行完整的数据，而二级索引只存储了主键值，相比于聚簇索引，占用的空间要少。当我们需要为表建立多个索
【数据库原理系列】索引存储及分类
菜鸡也有大佬梦
11-27 3855
索引 概述 索引 索引是定义在存储表(Table)基础之上，有助于无需检查所有记录而快速定位所需记录的一种辅助存储结构，由一系列存储在磁盘上的索引项(indexentries)组成，每一索引项又由两部分构成: 索引字段：由Table中某些列(通常是一列)中的值串接而成。索引中通常存储了索引字段的每一个值(也有不是这样的)。 行指针：指向Table中包含索引字段值的记录在磁盘上的存储位置。 存储索引项的文件为索引文件，相对应，存储表又称为主文件 索引文件比主文件小很多。通过检索一个小的索引
【转】myisam和innodb索引的实现
四维空间
09-25 321
        MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：         这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构...
mysql主索引辅助索引_mysql 索引实践(一) 单个索引
weixin_28813401的博客
02-18 266
现有一张表 employees，emp_no 为主键，分别为first_name、last_name各自建一个索引：#emp_no PRIMARY#first_name#last_name查看当前表的索引：show index from employees有如下定义：#等值查询 =#范围查询 in、between ... and ...#前缀匹配 like 'xx%'#不等值查询 !=、no...
MySQL中的聚集索引和辅助索引知识详解
热门推荐
qq_22335577的专栏
08-25 1万+
具体的文档参考官方文档15.8.9 1.当你定义一个主键时，InnnodDB存储引擎则把它当做聚集索引 2.如果你没有定义一个主键，则InnoDB定位到第一个唯一索引，且该索引的所有列值均飞空的，则将其当做聚集索引。 3如果表没有主键或合适的唯一索引INNODB会产生一个隐藏的行ID值6字节的行ID聚集索引， 补充：由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能有一个聚集索引，
mysql中主键索引与普通索引(聚集索引和辅助索引)
weixin_39590058的博客
03-04 1473
B+树索引并不能找到一个给定键的具体行。B+树索引能找到的只是被查找数据行所在的页。 然后数据库通过把页读入内存，再在内存中进行查找，最后得到要查找的数据。 B+树（平衡多路查找树）： B+树是为了磁盘或其它直接存取设备设计的一种平衡多路查找树。在B+树里是，所以记录节点都是键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。 B+树索引在数据库中有高扇出性的特点，因此...
MySQL-主键索引、二级索引、聚簇索引、非聚簇索引、辅助索引、创建索引注意事项
ALONG的博客
02-26 4068
索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。
mysql 辅助索引的例子_精通MySQL之索引篇
weixin_42479284的博客
02-27 1864
索引是什么？在日常开发中常常会遇到查询比较慢的情况，我们的第一反应就是给它加索引，那索引是什么呢？官方介绍索引是帮助MySQL高效获取数据的数据结构，数据库索引好比是一本书的目录，能加快数据库的数据查询速度。那索引的好处有哪些呢？它可以提高数据检索的效率，降低数据库的成本。通过索引对数据进行排序，降低数据排序的成本，降低CPU消耗。任何事情都会有正反面，索引也不例外，那索引的坏处有哪些呢？索引会占...
mysql的主键索引和非主键索引有什么区别
最新发布
11-14
<think> 首先，用户的问题是：“我想了解mysql的主键索引和非主键索引的区别 请问mysql主键索引和非主键索引的区别”。我需要基于提供的引用内容来回答。 从系统级指令，我必须： - 所有行内数学表达式使用 `$...$` 格式。 - 独立公式使用 `$$...$$` 格式并单独成段。 - LaTeX语法正确。 - 使用中文回答。 - 生成相关问题。 - 回答中引用的段落末尾自然地添加引用标识，例如 `[^1]`。 - 回答结构清晰，帮助用户逐步解决问题，保证真实可靠。 用户引用了几个来源： - [^1]: 讨论聚簇索引（主键索引）和非聚簇索引（非主键索引）的区别，包括结构和存储引擎方面。 - [^2]: 强调主键索引是聚簇索引，存储数据行在叶子节点；非主键索引是辅助索引，存储指针或主键值。 - [^3]: 提到辅助索引使用主键作为指针，减少了维护工作。 - [^4]: 讨论插入数据时的影响，如页分裂等。 关键点总结： 1. **存储结构**： - 主键索引（聚簇索引）：数据行直接存储在索引的叶子节点中。索引决定了数据的物理存储顺序。 - 非主键索引（非聚簇索引）：叶子节点存储的是主键值或指针，而不是完整的数据行。检索时需要额外步骤来获取数据。 2. **性能**： - 主键索引：查询速度快，因为数据就在索引中。但插入、更新、删除操作可能导致页分裂或移动数据，影响性能。 - 非主键索引：查询时可能需要回表（即通过主键索引获取完整数据），增加I/O操作。但索引维护相对简单。 3. **其他方面**： - 非主键索引使用主键值作为指针，这有助于在数据移动时减少索引维护。 - 在InnoDB存储引擎中，主键索引默认是聚簇索引。 回答结构： 1. **简介**：简要说明主键索引和非主键索引的定义。 2. **核心区别**：分点解释存储结构、性能影响、维护等。 3. **总结**：概括主要区别。 4. **相关问题**：生成几个相关问题。 使用中文回答，并自然地添加引用标识。 生成相关问题： - 例如："主键索引在什么情况下会导致性能问题？" 或 "如何优化非主键索引的查询性能？" - 基于内容：1. 主键索引的优缺点；2. 非主键索引的使用场景；3. 索引维护的最佳实践。 最终回答框架： - 开头：直接回答问题。 - 主体：逐步解释区别。 - 结尾：总结。 - 添加相关问题部分。 </think> ### MySQL主键索引与非主键索引的区别 在MySQL中，主键索引和非主键索引（也称为辅助索引）是两种核心索引类型，它们在存储结构、检索效率和维护方式上存在显著差异。以下基于InnoDB存储引擎（MySQL默认引擎）详细解释这些区别，帮助您深入理解。 #### 1. **存储结构与数据组织** - **主键索引（聚簇索引）**： 主键索引是聚簇索引（clustered index），这意味着索引的叶子节点直接存储完整的数据行（即表中所有列的值）。因此，数据在磁盘上的物理顺序与主键索引的顺序一致。例如，如果主键是自增ID，数据行会按ID顺序连续存储。这提高了基于主键的范围查询效率，因为相关数据在物理上相邻[^1][^2]。 用数学表达式描述索引结构：设索引树的高度为$h$，主键查询的复杂度为$O(\log n)$，其中$n$是数据量。 - **非主键索引（非聚簇索引）**： 非主键索引是非聚簇索引（non-clustered index），其叶子节点不存储完整数据行，而是存储主键值（在InnoDB中）或指向数据行的指针（在MyISAM中）。这导致检索数据时需额外步骤： 1. 通过非主键索引查找到主键值。 2. 再通过主键索引回表（table lookup）获取完整数据行。 例如，若非主键索引的键为列$k$，查询过程可表示为二次索引扫描：$ \text{查询时间} \propto O(\log m) + O(\log n) $（$m$为非主键索引大小）[^2][^3]。 #### 2. **检索效率** - **主键索引**： - **优点**：查询速度极快，数据与索引一体，无需回表。例如，`SELECT * FROM table WHERE id = 100` 直接定位数据。 - **缺点**：如果主键非自增（如UUID），插入新数据可能导致页分裂（page split），因为B+树需要重新平衡存储顺序，消耗额外I/O资源[^4]。 独立公式表示页分裂概率： $$ P(\text{页分裂}) \propto \frac{\text{插入位置随机性}}{\text{页大小}} $$ - **非主键索引**： - **优点**：索引体积小（仅存储主键值），维护成本低；数据移动（如行迁移）时，索引不直接更新物理指针，而是依赖主键值，减少了维护开销[^3]。 - **缺点**：查询需回表，增加磁盘I/O。例如，`SELECT name FROM table WHERE email = 'a@example.com'` 若`email`是非主键索引，需先查主键值再取数据，效率较低[^2]。 #### 3. **维护与性能影响** - **数据修改操作**： - 插入/更新主键时，主键索引可能触发页分裂或数据移动，影响写入性能（尤其在频繁随机插入时）[^4]。 - 非主键索引的更新通常更快，因为只需修改索引树，而不移动数据行。辅助索引使用主键值作为“逻辑指针”，行移动时索引结构不受影响[^3]。 - **存储引擎差异**： InnoDB强制主键索引为聚簇索引；若表未定义主键，InnoDB会隐式创建隐藏主键。而MyISAM中所有索引均为非聚簇，数据与索引分离存储[^1]。 #### 4. **总结关键区别** | 特性 | 主键索引（聚簇索引） | 非主键索引（非聚簇索引） | |--------------------|------------------------------------------|----------------------------------------| | **数据存储位置** | 叶子节点存储完整数据行 | 叶子节点存储主键值或指针 | | **物理存储顺序** | 决定数据在磁盘上的顺序 | 不影响数据物理顺序 | | **查询效率** | 主键查询高效，无需回表 | 需回表，增加I/O开销 | | **维护成本** | 高（页分裂风险） | 低（依赖主键值，减少移动维护） | | **适用场景** | 主键查询、范围扫描 | 非主键列过滤、覆盖索引（避免回表） | #### 实际应用建议 - **选择主键**：优先使用自增整数主键，减少页分裂风险[^4]。 - **优化查询**：对高频查询的非主键列创建索引，并尽量使用覆盖索引（索引包含所有查询字段），避免回表[^2]。 - **权衡性能**：主键索引适合读密集型操作；非主键索引在写频繁场景更灵活，但需监控回表开销[^3]。 通过以上解释，您可以更清晰地根据业务需求设计索引。如果您有具体场景（如高并发写入），可进一步讨论优化策略。
评论 5
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

丢了尾巴的猴子

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值