分享 MySQL和Oracle行值表达式相比
发布时间：2022-03-26 21:41:36 所属栏目：MySql教程 来源：互联网
导读：行值表达式也叫作行值构造器，在很多SQL使用场景中会看到它的身影，一般是通过in的方式出现，但是在MySQL和Oracle有什么不同之处呢。我们做几个简单的测试来说明一下。 MySQL 5.6,5.7版本的差别 首先我们看一下MySQL 5.6, 5.7版本中的差别，在这一方面还是值
|
行值表达式也叫作行值构造器，在很多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是已经实现了，也能够通过这些方面来对比学习。 （编辑：威海站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
