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 (<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中的行值表示式

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是已經實現了，也能夠透過這些方面來對比學習。