问题背景
舍友在面试时碰到一道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语句分析走的是哪个索引。当时我跑出来的是走联合索引,验证了自己的分析。可是问题真的已经解决了么?
打算就此结束时,舍友他朋友跑出来的结果是走主键索引。
仔细比对后发现我们插入数据不一样,他有负数我没有,而且他测的sql语句则是select * from table where a<0 and b<0
这很奇怪
再次测试
实在想不明白,后面我又重新测了一次,这次我尝试了多种情况,发现用哪个索引受主键值的影响,联合索引字段对应的数值变化对其无影响。
主要目的是测试主键数值对走索引的影响,所以这里变量是主键字段中负数/非正数数据的个数。
当主键字段对应数据值没有负数和0时:
b>0 | b<0 | |
---|---|---|
a>0 | U | U |
a<0 | P | P |
这里用大写字母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个0 | 2个负数 | 2个负数1个0 | 3个负数 | |
---|---|---|---|---|---|
测试结果 | UU PP | PU PP | PU UU | UU UU | UU PU |
当然走的type都是range类型,但是不知道为什么走的索引会根据数据不同变来变去,希望有大佬能不吝赐教,指点迷津。