MySQL和Oracle行值表达式对比(r11笔记第74天)

行值表达式也叫作行值构造器，在很多SQL使用场景中会看到它的身影，一般是通过in的方式出现，但是在MySQL和Oracle有什么不同之处呢。我们做几个简单的测试来说明一下。

首先我们看一下MySQL 5.6, 5.7版本中的差别，在这一方面还是值得说道说道的。

我们创建一个表users,然后就模拟同样的语句在不同版本的差别所在。

在MySQL 5.6版本中。

create table users(
userid int(11) unsigned not null,
username varchar(64) default null,
primary key(userid),
key(username)
)engine=innodb default charset=UTF8;插入20万数据。

delimiter $$
drop procedure if exists proc_auto_insertdata$$
create procedure proc_auto_insertdata()
begin
    declare
    init_data integer default 1;
    while init_data<=20000 do
    insert into users values(init_data,concat('user'    ,init_data));
    set init_data=init_data+1;
    end while;
end$$
delimiter ;
call proc_auto_insertdata();

创建一个复合索引。

 create index idx_users on users(userid,username);然后我们使用explain来看看计划,下面的红色部分可以发现没有可用的索引。
>explain select userid,username from users where (userid,username) in ((1,'user1'),(2,'user2'))\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
         type: index
possible_keys: NULL
          key: username
      key_len: 195
          ref: NULL
         rows: 19762
        Extra: Using where; Using index
1 row in set (0.00 sec)我们可以使用extended的方式得到更细节的信息,在此其实看不到太多的信息。

explain extended select userid,username from users where (userid,username) in ((1,'user1'),(2,'user2'))\G
>show warnings;
| Note  | 1003 | select `test`.`users`.`userid` AS `userid`,`test`.`users`.`username` AS `username` from `test`.`users` where ((`test`.`users`.`userid`,`test`.`users`.`username`) in (<cache>((1,'user1')),<cache>((2,'user2'))))在MySQL 5.7中表现如何呢。

我们使用同样的方式创建表users,插入数据,可以看到使用了range的扫描方式，使用了索引。

> explain select userid,username from users where (userid,username) in ((1,'user1'),(2,'user2'))\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: range
possible_keys: PRIMARY,username,idx_users
          key: username
      key_len: 199
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 3 warnings (0.00 sec)使用extended的方式得到的信息。

| Warning | 1739 | Cannot use ref access on index 'username' due to type or collation conversion on field 'username'
| Warning | 1739 | Cannot use ref access on index 'username' due to type or collation conversion on field 'username'    Note    | 1003 | select `test`.`users`.`userid` AS `userid`,`test`.`users`.`username` AS `username` from `test`.`users` where ((`test`.`users`.`userid`,`test`.`users`.`username`) in (<cache>((1,'user1')),<cache>((2,'user2'))))通过上面的方式可很明显看到在MySQL 5.7中有了改进。

Oracle中我们就直接使用11gR2的环境来进行测试。

创建表users,插入数据。

create table users(
userid number   primary key,
username varchar2(64) default null
);额外创建几个索引，看看最后会使用哪个

create index idx_username on users(username);
create index idx_usres on users(userid,username);插入数据，收集统计信息
insert into users select level userid,'user'||level username from dual connect by level<=20000;
commit;
exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'USERS',cascade=>true);我们使用explain plan for的方式得到执行计划。可以很明显看出使用了复合索引，而且通过如下标红的谓词信息，语句做了查询转换。
SQL> select *from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
Plan hash value: 1425496436
-----------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     2 |    26 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR  |           |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_USRES |     2 |    26 |     2   (0)| 00:00:01 |
----------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
   2 - access(("USERID"=1 AND "USERNAME"='user1' OR "USERID"=2 AND
              "USERNAME"='user2'))可见这个部分，Oracle是已经实现了，也能够通过这些方面来对比学习。