测试复合索引在oracle、sql server 、mysql各种情况下是否使用索引

分手后的思念是犯贱 2022-08-20 06:10 134阅读 0赞

今天没有事验证一下复合索引在不同的数据库中的使用情况，仅是测试而已，以一个例子测试，结果如下

首先在oracle，sqlserver， mysql建立表testpayorderinfo  表结构一样，然后插入20w数据

分别创建复合索引（testpayorderid,connid,customerid的复合索引）

以复合索引中的不同条件字段的组合作为条件进行测试

说明：

testpayorderid   对应a

connid                对应b

customerid       对应c

测试结果如下：

sql server：（版本sql server 2008）

select \* from testpayorderinfo where testpayorderid=194027   --a 走  
select \* from testpayorderinfo where connid=257277          --b 走  
select \* from testpayorderinfo where customerid=1304349     --c 走  
select \* from testpayorderinfo where testpayorderid=194027 and connid=257277 --ab 走  
select \* from testpayorderinfo where testpayorderid=194027 and customerid=1304349 --ac 走  
select \* from testpayorderinfo where testpayorderid=194027 and connid=257277 and customerid=1304349  --abc 走  
select \* from testpayorderinfo where  connid=257277 and customerid=1304349  --bc 走  
select testpayorderid,connid,customerid from testpayorderinfo where connid=257277          --b 走  
select testpayorderid,connid,customerid from testpayorderinfo where customerid=1304349     --c 走  
select testpayorderid,connid,customerid from testpayorderinfo where  connid=257277 and customerid=1304349  --bc 走

oracle：（oracle Release 12.1.0.2.0 ）  
create index ix\_com\_acc  on testpayorderinfo(testpayorderid,connid,customerid)  
select \* from testpayorderinfo where testpayorderid=194027   --a 走  
select \* from testpayorderinfo where connid=257277          --b 不走  
select \* from testpayorderinfo where customerid=1304349     --c 不走  
select \* from testpayorderinfo where testpayorderid=194027 and connid=257277 --ab 走  
select \* from testpayorderinfo where testpayorderid=194027 and customerid=1304349 --ac 走  
select \* from testpayorderinfo where testpayorderid=194027 and connid=257277 and customerid=1304349  --abc 走  
select \* from testpayorderinfo where  connid=257277 and customerid=1304349  --bc 不走  
  
select testpayorderid,connid,customerid from testpayorderinfo where connid=257277          --b 走  
select testpayorderid,connid,customerid from testpayorderinfo where customerid=1304349     --c 走  
select testpayorderid,connid,customerid from testpayorderinfo where  connid=257277 and customerid=1304349  --bc 走  
  
mysql ：（Server version: 5.5.47-log MySQL Community Server (GPL)）  
select \* from testpayorderinfo where testpayorderid=194027   --a 走  
select \* from testpayorderinfo where connid=257277          --b 不走  
select \* from testpayorderinfo where customerid=1304349     --c 不走  
select \* from testpayorderinfo where testpayorderid=194027 and connid=257277 --ab 走  
select \* from testpayorderinfo where testpayorderid=194027 and customerid=1304349 --ac 走  
select \* from testpayorderinfo where testpayorderid=194027 and connid=257277 and customerid=1304349  --abc 走  
select \* from testpayorderinfo where connid=257277 and customerid=1304349  --bc 不走

select testpayorderid,connid,customerid from testpayorderinfo where connid=257277          --b 走  
select testpayorderid,connid,customerid from testpayorderinfo where customerid=1304349     --c 走  
select testpayorderid,connid,customerid from testpayorderinfo where  connid=257277 and customerid=1304349  --bc 走

测试结果：

oracle和mysql 走的索引是一样的，sql server 只要是复合索引中的任何一个字段都走索引