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

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

MySQL 5.6,5.7版本的差别

首先我们看一下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

(((1,'user1')),((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

(((1,'user1')),((2,'user2'))))通过上面的方式可很明显看到在MySQL 5.7中有了改进。

Oracle中的行值表达式

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