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

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

    首先在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 只要是复合索引中的任何一个字段都走索引