关于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类型,但是不知道为什么走的索引会根据数据不同变来变去,希望有大佬能不吝赐教,指点迷津。

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值