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#1 */ 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#1 */ 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是已经实现了，也能够通过这些方面来对比学习。