MySQL和Oracle中的半连接测试总结（一）

SQL中的半连接在MySQL和Oracle还是存在一些差距，从测试的情况来看，Oracle的处理要更加全面。
首先我们来看看在MySQL中怎么测试，对于MySQL方面的测试也参考了不少海翔兄的博客文章，自己也完整的按照他的测试思路练习了一遍。
首先创建下面的表:
create table users(
userid int(11) unsigned not null,
user_name varchar(64) default null,
primary key(userid)
)engine=innodb default charset=UTF8;

如果要插入数据，可以使用存储过程的方式。比如先插入20000条定制数据。
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();
初始化的过程会很快，最后一步即插入数据花费了近6秒的时间。
[test]>source insert_proc.sql
Query OK, 0 rows affected (0.12 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (5.63 sec)

然后我们使用如下的半连接查询数据，实际上执行了6秒左右。
select u.userid,u.user_name from users u where u.user_name in (select t.user_name from users t where t.userid<2000);
1999 rows in set (6.36 sec)
为了简化测试条件和查询结果，我们使用count的方式来完成对比测试。
[test]>select count(u.userid) from users u where u.user_name in (select t.user_name from users t where t.userid<2000);
+-----------------+
| count(u.userid) |
+-----------------+
|            1999 |
+-----------------+
1 row in set (6.38 sec)
然后使用如下的方式来查看，当然看起来这种结构似乎有些多余，因为userid<-1的数据是不存在的。
select count(u.userid) from users u
where (u.user_name in (select t.user_name from users t where t.userid<2000) or u.user_name in (select t.user_name from users t where userid<-1) )；
+-----------------+
| count(u.userid) |
+-----------------+
|            1999 |
+-----------------+
1 row in set (0.06 sec)
但是效果却好很多。
当然两种方式的执行计划差别很大。
第一种效率较差的执行计划如下：
[test]>explain select count(u.userid) from users u where u.user_name in (select t.user_name from users t where t.userid<2000);
+----+--------------+-------------+-------+---------------+---------+---------+------+-------+----------------------------------------------------+
| id | select_type  | table       | type  | possible_keys | key     | key_len | ref  | rows  | Extra                                              |
+----+--------------+-------------+-------+---------------+---------+---------+------+-------+----------------------------------------------------+
|  1 | SIMPLE       | | ALL   | NULL          | NULL    | NULL    | NULL |  NULL | NULL                                               |
|  1 | SIMPLE       | u           | ALL   | NULL          | NULL    | NULL    | NULL | 19762 | Using where; Using join buffer (Block Nested Loop) |
|  2 | MATERIALIZED | t           | range | PRIMARY       | PRIMARY | 4       | NULL |  1998 | Using where                                        |
+----+--------------+-------------+-------+---------------+---------+---------+------+-------+----------------------------------------------------+
3 rows in set (0.02 sec)
第二个执行效率较高的执行计划如下：
[test]>explain select count(u.userid) from users u where (u.user_name in (select t.user_name from users t where t.userid<2000) or u.user_name in (select t.user_name from users t where userid<-1) );  
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra                                               |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------------------------------+
|  1 | PRIMARY     | u     | ALL   | NULL          | NULL    | NULL    | NULL | 19762 | Using where                                         |
|  3 | SUBQUERY    | NULL  | NULL  | NULL          | NULL    | NULL    | NULL |  NULL | Impossible WHERE noticed after reading const tables |
|  2 | SUBQUERY    | t     | range | PRIMARY       | PRIMARY | 4       | NULL |  1998 | Using where                                         |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------------------------------+
3 rows in set (0.00 sec)

我们在这个测试中先不解释更多的原理，只是对比说明。
如果想得到更多的执行效率对比情况，可以使用show status 的方式。
首先flush status
[test]>flush status;
Query OK, 0 rows affected (0.02 sec)
然后执行语句如下：
[test]>select count(u.userid) from users u where u.user_name in (select t.user_name from users t where t.userid<2000);
+-----------------+
| count(u.userid) |
+-----------------+
|            1999 |
+-----------------+
1 row in set (6.22 sec)
查看状态信息，关键词是Handler_read.
[test]>show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 2     |
| Handler_read_key      | 2     |
| Handler_read_last     | 0     |
| Handler_read_next     | 1999  |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 22001 |
+-----------------------+-------+
7 rows in set (0.04 sec
Handler_read_key这个参数的解释是根据键读一行的请求数。如果较高，说明查询和表的索引正确。
Handler_read_next这个参数的解释是按照键顺序读下一行的请求数。如果用范围约束或如果执行索引扫描来查询索引列，该值增加。
Handler_read_rnd_next这个参数的解释是在数据文件中读下一行的请求数。如果正进行大量的表扫描，该值较高。通常说明表索引不正确或写入的查询没有利用索引。
这是一个count的操作，所以Handler_read_rnd_next的指标较高，这是一个范围查询，所以Handler_read_next 的值也是一个范围值。

然后运行另外一个子查询，可以看到show status的结果如下：

[test]>show status like 'Handler_read%';                                                   
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 2     |
| Handler_read_key      | 20002 |
| Handler_read_last     | 0     |
| Handler_read_next     | 1999  |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 20001 |
+-----------------------+-------+
7 rows in set (0.00 sec)
可以和明显看到Handler_read_key这个值很高，根据参数的解释，说明查询和表的索引使用正确。也就意味着这种方式想必于第一种方案要好很多。
而对于此，MySQL其实也有一些方式方法可以得到更细节的信息。
一种就是explain extended的方式。
[test]>explain extended select count(u.userid) from users u where u.user_name in (select t.user_name from users t where t.userid<2000);
。。。。
3 rows in set, 1 warning (0.00 sec)
然后show warnings就会看到详细的信息。
[test]>show warnings;
| Note  | 1003 | /* select#1 */ select count(`test`.`u`.`userid`) AS `count(u.userid)` from `test`.`users` `u` semi join (`test`.`users` `t`) where ((`test`.`u`.`user_name` = ``.`user_name`) and (`test`.`t`.`userid` < 2000)) |
1 row in set (0.00 sec)
第二个语句的情况如下：
[test]>explain extended select count(u.userid) from users u where (u.user_name in (select t.user_name from users t where t.userid<2000) or u.user_name in (select t.user_name from users t where userid<-1) );
3 rows in set, 1 warning (0.00 sec)

[test]>show warnings;
| Note  | 1003 | /* select#1 */ select count(`test`.`u`.`userid`) AS `count(u.userid)` from `test`.`users` `u` where ((`test`.`u`.`user_name`,`test`.`u`.`user_name` in ( (/* select#2 */ select `test`.`t`.`user_name` from `test`.`users` `t` where (`test`.`t`.`userid` < 2000) ), (`test`.`u`.`user_name` in on where ((`test`.`u`.`user_name` = `materialized-subquery`.`user_name`))))) or (`test`.`u`.`user_name`,`test`.`u`.`user_name` in ( (/* select#3 */ select `test`.`t`.`user_name` from `test`.`users` `t` where 0 ), (`test`.`u`.`user_name` in on where ((`test`.`u`.`user_name` = `materialized-subquery`.`user_name`)))))) |
1 row in set (0.00 sec)
还有一种方式就是使用  optimizer_trace,在5.6可用 
    set optimizer_trace="enabled=on";    
    运行语句后，然后通过下面的查询得到trace信息。
    select *from information_schema.optimizer_trace\G

当然可以看出半连接的表现其实还不够好，能不能选择性的关闭呢，有一个参数可以控制，即是optimizer_switch,其实我们也可以看看这个参数的情况。
| optimizer_switch                                       | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on |
关闭半连接的设置
>set optimizer_switch="semijoin=off";
Query OK, 0 rows affected (0.00 sec)
再次运行原本执行时间近6秒的SQL，执行时间大大降低。
[test]> select count(u.userid) from users u where u.user_name in (select t.user_name from users t where t.userid<2000);
+-----------------+
| count(u.userid) |
+-----------------+
|            1999 |
+-----------------+
1 row in set (0.05 sec)
执行第二个语句，情况如下：
[test]>select count(u.userid) from users u where (u.user_name in (select t.user_name from users t where t.userid<2000) or u.user_name in (select t.user_name from users t where userid<-1) );
+-----------------+
| count(u.userid) |
+-----------------+
|            1999 |
+-----------------+
1 row in set (0.07 sec)

参考内容如下：
http://dbaplus.cn/news-11-133-1.html
http://blog.chinaunix.net/uid-16909016-id-214888.html

而在Oracle中表现如何呢。
创建测试表
create table users(
userid number not null,
user_name varchar2(64) default null,
primary key(userid)
);
初始化数据，其实一句SQL就可以搞定。递归查询可以换种方式来用，效果杠杠的。
insert into users select level,'user'||level from dual connect by level<=20000;
收集一下统计信息
exec dbms_stats.gather_table_stats(ownname=>'CYDBA',tabname=>'USERS',cascade=>true);      
然后执行和MySQL中同样的语句。
我们使用trace的方式来查看,我们仅列出trace的情况。
SQL> set autot trace exp stat
SQL> select u.userid,u.user_name from users u where u.user_name in (select t.user_name from users t where t.userid<2000);
1999 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 771105466
---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |  2003 | 52078 |    21   (5)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI        |              |  2003 | 52078 |    21   (5)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| USERS        |  1999 | 25987 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C0042448 |  1999 |       |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | USERS        | 20000 |   253K|    17   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("U"."USER_NAME"="T"."USER_NAME")
   3 - access("T"."USERID"<2000)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        205  consistent gets
          0  physical reads
          0  redo size
      52196  bytes sent via SQL*Net to client
       1983  bytes received via SQL*Net from client
        135  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1999  rows processed

SQL> select u.userid,u.user_name from users u where (u.user_name in (select t.user_name from users t where t.userid<2000) or u.user_name in (select t.user_name from users t where userid<-1) );
1999 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1012235795
------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |  2004 | 94188 |    22   (5)| 00:00:01 |
|*  1 |  HASH JOIN                      |              |  2004 | 94188 |    22   (5)| 00:00:01 |
|   2 |   VIEW                          | VW_NSO_1     |  2000 | 68000 |     4   (0)| 00:00:01 |
|   3 |    HASH UNIQUE                  |              |  2000 | 26000 |     4  (25)| 00:00:01 |
|   4 |     UNION-ALL                   |              |       |       |            |          |
|   5 |      TABLE ACCESS BY INDEX ROWID| USERS        |     1 |    13 |     1   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | SYS_C0042448 |     1 |       |     1   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| USERS        |  1999 | 25987 |     3   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN          | SYS_C0042448 |  1999 |       |     2   (0)| 00:00:01 |
|   9 |   TABLE ACCESS FULL             | USERS        | 20000 |   253K|    17   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("U"."USER_NAME"="USER_NAME")
   6 - access("USERID"<(-1))
   8 - access("T"."USERID"<2000)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        207  consistent gets
          0  physical reads
          0  redo size
      52196  bytes sent via SQL*Net to client
       1983  bytes received via SQL*Net from client
        135  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1999  rows processed
从Oracle的表现来看，支持的力度要全面很多。当然半连接的玩法还有很多，比如exists，这些限于篇幅暂没有展开。而且对于对比测试中的更多知识点分析，我们后期也会逐步补充。

时间： 2024-09-20 20:47:39

MySQL和Oracle中的半连接测试总结（一）的相关文章

MySQL和Oracle中的隐式转换

今天在处理一个问题的时候,需要根据其他部门提供的sql语句对一个表中的数据进行了筛查. 语句类似下面的形式 > SELECT MAX_LEVEL,LOGOUT_TIME,CURRENT_DATE AS NOWTIME,cn_master FROM t_test_october_back_a WHERE ID in ( 100, 200, 300, 400, 500) ; +-----------+---------------+------------+-----------+ | MAX_LE

由一条create语句的问题对比mysql和oracle中的date差别

今天开发的同事提交过来一个sql变更,在部署的时候发现了一个问题. 语句是一个简单的create语句 CREATE TABLE `test_user` (   `openid` varchar(64) NOT NULL,   `amount` varchar(11) DEFAULT 0,   `create_time` datetime DEFAULT CURRENT_TIMESTAMP,   `update_time` datetime DEFAULT CURRENT_TIMESTAMP,  

简单对比MySQL和Oracle中的一个sql解析细节

SQL的语法解析器是一个很强大的内置工具集,里面会涉及到很多的编译原理的相关知识,语法分析,词法分析..一大堆看起来很理论的东东,不过看起来枯燥之余,它们的价值也更加明显. 借用一下网络中的原话:如果我们考究一下历史,就会发现很多被称为程序设计大师的人都是编译领域的高手.写出第一个微型机上运行的Basic语言的比尔盖茨,设计出Delphi的Borland的"世界上最厉害的程序员", Sun的JAVA之父, 贝尔实验室的C++之父 起点提得有些高了,今天和大家分享的案例是一个很简单的sq

MySQL和Oracle中的delete,truncate对比

在MySQL和Oracle中的delete,truncate还是存在着一些差别,明白了这些差别可能对于处理问题,理解问题会有一些帮助. 我们来简单通过一些测试来说明.我们创建两个表test_del,test_tru来对比delete,truncate的操作.我们有一个临时表t_fund_info大概有几百万的数据量. 创建test_del > create table test_del select *from t_fund_info; Query OK, 1998067 rows affect

从Java的类型转换看MySQL和Oracle中的隐式转换(二)

说起数据类型转换,在开发中如此,在数据库中也是如此,之前简单对比过MySQL和Oracle的数据类型转换情况,可以参见MySQL和Oracle中的隐式转换 http://blog.itpub.net/23718752/viewspace-1787973/ 不过当时写完之后,有个读者随口问了一句为什么,为什么呢?似乎自己还是一知半解,说是规则,无规矩不成方圆,倒也无可非议,不过我觉得还是要再看看,看看还能有哪些收获,接下来的内容我就不能保证正确性了,希望大家明辨,也希望提出意见,毕竟就是希望把问题

MySQL和Oracle中的唯一性索引从差别(r12笔记第83天)

   今天在修复MySQL数据的时候,发现一个看起来"奇怪"的问题.   有一个表里存在一个唯一性索引,这个索引包含3个列,这个唯一性索引的意义就是通过这3个列能够定位到具体1行的数据,但是在实际中却发现这个唯一性索引还是有一个地方可能被大家忽略了.  我们先来看看数据的情况.  CREATE TABLE `test_base_data` (   `servertime` datetime DEFAULT NULL COMMENT '时间',   `appkey` varchar(64

关于oracle中的半连接

表的连接在sql语句中尤为重要.外连接,内连接,半连接,反连接等等各种连接,看似简单的一个连接里面还是有不少的细节的.对于sql调优来说也是很重要的. 像下面的形式的sql就属于半连接,使用了in子句,对于exists的实现也是属于半连接. --in半连接 SQL> select dname from dept dept where deptno in (select deptno from emp emp); DNAME -------------- RESEARCH SALES ACCOUN

关于oracle中的反连接

在之前的章节中见到讨论过oracle中的半连接 http://blog.itpub.net/23718752/viewspace-1334483/ 与半连接相对应的是反连接,简而言之半连接就是查询条件中的in,exists,反连接就是not in, not exists这种类型的连接. 在asktom中,tom也对大家关心的in,exists,not in, not exists的问题进行了大量的佐证和解释.因为问题是在2001年左右提出来的,当时还是oracle 8的时代,帖子也沉里许久,在2

在Mysql，SqlServer及Oracle中设置主键自动增长

1.把主键定义为自动增长标识符类型 在mysql中,如果把表的主键设为auto_increment类型,数据库就会自动为主键赋值.例如: create table customers(id int auto_increment primary key not null, name varchar(15)); insert into customers(name) values("name1"),("name2"); select id from customers;