关于MySQL的主键索引与联合索引疑问

文章探讨了在MySQL中,当主键和联合索引同时存在时,SQL查询根据主键值和负数的使用选择不同索引的问题。作者通过实验发现索引选择与主键中的负数数量有关,引发读者对索引策略的深入理解需求。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

问题背景

舍友在面试时碰到一道sql索引题,当时没回答上来,结束后越研究越奇怪。当时的问题:
当a是主键索引,b,c是联合索引,select * from table where a>0 and b>0走的是主键索引还是联合索引?
由于没说还有没有非索引列,所以我们假设表内只有a,b,c三列。后面的实验测试也是基于这一假设进行的。

测试结果

理论分析

我们一开始讨论后分析认为,在背景中提到的假设为前提下,表内所有列通过索引都能查到,所以数据库查询应该优先走的是联合索引;如果还有非索引列,则走的是主键索引。
talk is cheap。 后面我们在mysql上进行了测试分析,然后就发现了奇怪的现象。

实际测试

测试条件: 数据库用的是MySQL8.0,在windows平台下,数据简单的插入了四行,都是整数类型。
一开始我们创建了一个简单的数据表test, 当时为了方便三列设置的都是整数类型,数据随便塞了一些正数(就是这里引发了后续的怪事)。之后便运行explain语句分析走的是哪个索引。当时我跑出来的是走联合索引,验证了自己的分析。可是问题真的已经解决了么?
mysql 中explain分析语句
打算就此结束时,舍友他朋友跑出来的结果是走主键索引
仔细比对后发现我们插入数据不一样,他有负数我没有,而且他测的sql语句则是select * from table where a<0 and b<0
这很奇怪

再次测试

实在想不明白,后面我又重新测了一次,这次我尝试了多种情况,发现用哪个索引受主键值的影响,联合索引字段对应的数值变化对其无影响

主要目的是测试主键数值对走索引的影响,所以这里变量是主键字段中负数/非正数数据的个数
主键字段对应数据值没有负数和0时:

b>0b<0
a>0UU
a<0PP

这里用大写字母P表示走的主键,U表示走的联合索引。
横列和纵列表示select *from table where condition中的condition组合。即从左往右,从上往下依次表示where后的查询条件:a>0 and b>0; a>0 and b<0; a<0 and b>0; a<0 and b<0;

汇总

为了方便比较和总结规律,对多次实验合并到一个表格里。
测试结果如下:

没有负数1个负数/1个负数1个02个负数2个负数1个03个负数
测试结果UU
PP
PU
PP
PU
UU
UU
UU
UU
PU

当然走的type都是range类型,但是不知道为什么走的索引会根据数据不同变来变去,希望有大佬能不吝赐教,指点迷津。

<think>嗯,用户想了解MySQL主键索引主键索引的区别。我之前学过一些数据库的知识,但需要仔细回忆整理。首先,主键索引应该表的物理存储有关,我记得InnoDB的主键索引是聚簇索引,数据直接存在叶子节点里。而非主键索引,也就是二级索引,它们的叶子节点存储的是主键的值。这样的话,当使用非主键索引查询时,可能需要回表,也就是通过主键再去主键索引里找数据,这样会增加IO操作,影响性能。 然后,主键索引还有唯一性非空的约束,每个表只能有一个主键索引。而非主键索引可以有多个,比如普通索引、唯一索引联合索引等。它们的结构可能也不同,比如MyISAM引擎的主键索引InnoDB的不同,但用户的问题可能集中在InnoDB上,因为这是常用的引擎。 还需要考虑存储内容的不同。主键索引的叶子节点存的是整行数据,而非主键索引存的是主键的值。这样在查询的时候,如果查询的列都包含在非主键索引中,可能不需要回表,这叫做覆盖索引。比如,如果有一个索引是(c,a),而查询的字段正好是ca,那么可以直接从索引中获取数据,不需要回表。 另外,索引的维护成本也是一个区别。主键索引因为涉及数据的物理顺序,插入或删除数据时可能会引起页分裂或合并,影响性能。而非主键索引的更新可能更轻量,但如果有多个二级索引,更新操作也会影响这些索引,导致写操作变慢。 联合索引的情况也要注意,比如在引用[5]中的例子,索引是(a,b),那么查询条件只有a的时候可以用到索引,但单独用b的话不行。这可能涉及到最左前缀原则。此外,主键如果是联合主键的话,非主键索引的叶子节点存储的是联合主键的所有字段值,这样在回表的时候需要根据所有主键字段去查找。 还有,主键索引通常会被用作其他索引的指针,比如外键关联的时候,可能会依赖主键索引。而非主键索引不会有这样的约束。 总结下来,主键索引主键索引的主要区别在于存储内容、是否聚簇、唯一性约束、查询是否需要回表以及维护成本等方面。需要把这些点有条理地整理出来,用用户能理解的语言解释,同时结合引用中的例子,比如覆盖索引联合索引的情况,来具体说明区别。</think>### MySQL主键索引主键索引区别分析 #### 1. **存储结构数据定位** - **主键索引(聚簇索引)**: 叶子节点直接存储整行数据,数据行的物理存储顺序索引顺序一致[^3]。例如表`geek`的主键索引`(a,b)`会按`a,b`的值排序存储整行数据。 $$ \text{主键索引结构} \rightarrow [ (a,b) : (a,b,c,d) ] $$ - **非主键索引(二级索引)**: 叶子节点存储主键的值(若主键联合主键,则存储所有主键字段的值),通过主键值回表查询完整数据。例如索引`c`的叶子节点存储`c`的值对应的主键`(a,b)`。 #### 2. **查询性能差异** - **主键索引**: 通过主键查询时可直接获取数据,时间复杂度为$O(\log n)$。 ```sql SELECT * FROM geek WHERE a=1 AND b=2; -- 直接命中主键索引 ``` - **非主键索引**: 需先通过二级索引找到主键值,再回表查询主键索引获取数据(两次索引查找)。例如: ```sql SELECT * FROM geek WHERE c=3; -- 先查c索引得(a,b),再查主键索引 ``` #### 3. **覆盖索引优化** 若查询字段全部包含在二级索引中,可避免回表操作。例如索引`ca(c,a)`: ```sql SELECT c, a FROM geek WHERE c=3; -- 直接从ca索引获取数据,无需回表 ``` #### 4. **唯一性约束** - 主键索引强制唯一且非空,每个表仅有一个[^4]。 - 非主键索引允许重复值空值,且一个表可创建多个(如普通索引、唯一索引)。 #### 5. **索引维护代价** - 主键索引的更新可能引发数据页分裂或合并(影响物理存储顺序)。 - 非主键索引的更新仅需调整索引树,但多个二级索引会放大写操作成本。 #### 6. **联合索引的最左前缀原则** 以联合索引`cb(c,b)`为例: ```sql WHERE c=3 AND b=2 -- 可使用索引 WHERE b=2 -- 无法使用索引(违反最左前缀) ``` #### 7. **引擎差异(InnoDB vs MyISAM)** - **InnoDB**:主键索引为聚簇索引,非主键索引依赖主键值回表。 - **MyISAM**:主键索引主键索引均为非聚簇索引,数据索引分离存储[^1]。 --- ### 总结对比表 | 特性 | 主键索引 | 非主键索引 | |---------------------|-------------------------|-------------------------| | 存储内容 | 整行数据 | 主键值 | | 是否聚簇 | 是(InnoDB) | 否 | | 唯一性 | 强制唯一 | 可重复 | | 查询性能 | 直接定位数据 | 可能需回表 | | 索引数量 | 每个表仅一个 | 可多个 | | 外键关联 | 支持 | 不支持 | ---
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值