频道栏目
首页 > 数据库 > MySQL > 正文
oracle查询转换在mysql中实现对比
2016-08-06 09:09:22           
收藏   我要投稿

mysql与oracle的各自适用的场景分别是什么,下面的内容主要再查询转换上进行下比较。先总结下,基本上mysql是不适用于复杂的查询的,哪怕是稍微复杂的查询,在我别的blog中记载了写mysql的sql优化案例也可以看出来。mysql 查询上主要是用在一些简单查询的场景下,关于mysql的事务隔离级别及锁的研究,后续会在写一篇blog,在事务隔离级别和锁的测试中可以看到在mysql的默认隔离级别下,主要的适用场景是大量的简单查询,少量更新的情况,很适合互联网,互联网的大部分业务场景中基本读写比在4比1或更高,查询函数上也是oracle完全占优势,完整大量的分析函数为报表提供了便捷,而对oracle来说,适用的场景很广泛,基本对于任何的场景都可以适用

子查询展开
oracle中有2中策略展开,1 生成内联视图,2展开子查询合并到外层查询中,第二个是命令式的,第一个是基于成本的

对于mysql来说,子查询也是类似,下面第一个是物化了一个视图,因为子查询和外层没有什么关系,第二个是与外层关联了
mysql@t>explain select * from big_table where exists(select 1 from small_table where big_table.table_name=small_table.table_name );
+—-+——————–+————-+——+—————-+—————-+———+—————————+——–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+——————–+————-+——+—————-+—————-+———+—————————+——–+————-+
| 1 | PRIMARY | big_table | ALL | NULL | NULL | NULL | NULL | 902397 | Using where |
| 2 | DEPENDENT SUBQUERY | small_table | ref | idx_table_name | idx_table_name | 194 | test.big_table.TABLE_NAME | 5 | Using index |
+—-+——————–+————-+——+—————-+—————-+———+—————————+——–+————-+
2 rows in set (0.01 sec)

mysql@test>explain extended select * from big_table where exists(select 1 from small_table where big_table.table_name=small_table.table_name );
+—-+——————–+————-+——+—————-+—————-+———+—————————+——–+———-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+——————–+————-+——+—————-+—————-+———+—————————+——–+———-+————-+
| 1 | PRIMARY | big_table | ALL | NULL | NULL | NULL | NULL | 902397 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | small_table | ref | idx_table_name | idx_table_name | 194 | test.big_table.TABLE_NAME | 5 | 100.00 | Using index |
+—-+——————–+————-+——+—————-+—————-+———+—————————+——–+———-+————-+
连接消除
对于下面的一些查询关联是可以消除掉的,就是直接查看一个表就行了,没有必要2个表关联,在o中能消除,但是在mysql中是没有消除的,第一个查询中xiaodi的dage_id外键关联dage的id,对于下面的查询来说,其实就是直接查询xiaodi的表,所以可以消除与dage的关联,对第二个查询,因为是查看的左外关联中左边表的列,所以直接查找左面的表就行了,也没必要关联
mysql@.test>explain extended select x.id,x.name from xiaodi x ,dage d where x.dage_id=d.id;
+—-+————-+——-+——–+—————+———+———+—————-+——+———-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+——–+—————+———+———+—————-+——+———-+————-+
| 1 | SIMPLE | x | ALL | dage_id | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 4 | test.x.dage_id | 1 | 100.00 | Using index |
+—-+————-+——-+——–+—————+———+———+—————-+——+———-+————-+
2 rows in set, 1 warning (0.00 sec)

mysql@test>show warnings;
+——-+——+————————————————————————————————————————————————————————–+
| Level | Code | Message |
+——-+——+————————————————————————————————————————————————————————–+
| Note | 1003 | /* select#1 */ select test.x.id AS id,test.x.name AS name from test.xiaodi x join test.dage d where (test.d.id = test.x.dage_id) |
+——-+——+————————————————————————————————————————————————————————–+
1 row in set (0.01 sec)

mysql@.test>explain extended select x.id,x.name from xiaodi x left join dage d on x.dage_id=d.id;
+—-+————-+——-+——–+—————+———+———+—————-+——+———-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+——–+—————+———+———+—————-+——+———-+————-+
| 1 | SIMPLE | x | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 4 | test.x.dage_id | 1 | 100.00 | Using index |
+—-+————-+——-+——–+—————+———+———+—————-+——+———-+————-+
2 rows in set, 1 warning (0.00 sec)

mysql@test>show warnings;
+——-+——+————————————————————————————————————————————————————————————-+
| Level | Code | Message |
+——-+——+————————————————————————————————————————————————————————————-+
| Note | 1003 | /* select#1 */ select test.x.id AS id,test.x.name AS name from test.xiaodi x left join test.dage d on((test.x.dage_id = test.d.id)) where 1 |
+——-+——+————————————————————————————————————————————————————————————-+
1 row in set (0.00 sec)
谓词推入,在oracle中有简单谓词推入,关联谓词推入,谓词推入主要是要今早的把条件放入视图或子查询中,减少查询结果集,生成高效计划,然而mysql中最基本的简单谓词推入都无法实现

+>explain extended select * from (select table_name,table_schema from v_small_table ) a where a.table_name=’test’;
+—-+————-+————-+——+—————+————-+———+——-+——+———-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+————-+——+—————+————-+———+——-+——+———-+————-+
| 1 | PRIMARY | | ref | | | 194 | const | 10 | 100.00 | Using where |
| 2 | DERIVED | small_table | ALL | NULL | NULL | NULL | NULL | 1752 | 100.00 | NULL |
+—-+————-+————-+——+—————+————-+———+——-+——+———-+————-+

mysql@.test>explain extended select * from small_table where table_name=’test’;
+—-+————-+————-+——+—————-+—————-+———+——-+——+———-+———————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+————-+——+—————-+—————-+———+——-+——+———-+———————–+
| 1 | SIMPLE | small_table | ref | idx_table_name | idx_table_name | 194 | const | 1 | 100.00 | Using index condition |
+—-+————-+————-+——+—————-+—————-+———+——-+——+———-+———————–+
视图合并,就是消除视图,直接用原始表做关联操作,看到下面的查询mysqll还是把where条件直接应用到了表上,做了合并,还可以,但这个只是简单视图合并,对于复杂视图,就只能呵呵了,之前有实验
mysql@.test>explain extended select * from v_small_table where table_name=’test’;
+—-+————-+————-+——+—————-+—————-+———+——-+——+———-+———————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+————-+——+—————-+—————-+———+——-+——+———-+———————–+
| 1 | SIMPLE | small_table | ref | idx_table_name | idx_table_name | 194 | const | 1 | 100.00 | Using index condition |
+—-+————-+————-+——+—————-+—————-+———+——-+——+———-+———————–+
1 row in set, 1 warning (0.00 sec)

mysql@.test>show warnings
-> ;
+——-+——+————————————————————————————————————————————————————————————————————+
| Level | Code | Message |
+——-+——+————————————————————————————————————————————————————————————————————+
| Note | 1003 | /* select#1 */ select test.small_table.TABLE_NAME AS table_name,test.small_table.TABLE_SCHEMA AS table_schema from test.small_table where (test.small_table.TABLE_NAME = ‘test’) |
+——-+——+————————————————————————————————————————————————————————————————————+
1 row in set (0.01 sec)

视图合并和子查询展开是最基本的转换,主要的思想都是转换成连接操作,可见关系型数据库中高效的方式是连接,mysql在基本的查询转换是做的只是刚刚可以。
谓词传递,就是表关联的时候,在一个表的关联列上有过滤条件,这个过滤条件可以传递给别的表,mysql不能做这样的传递,我们看到ta表上是扫了全部的表,没有提前使用id=1进行过滤
mysql@test>explain extended select * from t8,ta where t8.id=ta.id and t8.id=1;
+—-+————-+——-+——+—————+——+———+——+——–+———-+—————————————————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+——+—————+——+———+——+——–+———-+—————————————————-+
| 1 | SIMPLE | t8 | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using where |
| 1 | SIMPLE | ta | ALL | NULL | NULL | NULL | NULL | 896615 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+—-+————-+——-+——+—————+——+———+——+——–+———-+—————————————————-+
2 rows in set, 1 warning (0.00 sec)

mysql@.test>show warnings;
+——-+——+—————————————————————————————————————————————————————————————————————————————————————————+
| Level | Code | Message |
+——-+——+—————————————————————————————————————————————————————————————————————————————————————————+
| Note | 1003 | /* select#1 */ select test.t8.id AS id,test.t8.c_id AS c_id,test.ta.id AS id,test.ta.num AS num,test.ta.value AS value from test.t8 join test.ta where ((test.t8.id = test.ta.id) and (test.t8.id = 1)) |
+——-+——+—————————————————————————————————————————————————————————————————————————————————————————+
1 row in set (0.00 sec)

mysql@>select count(*) from ta where id=1;
+———-+
| count(*) |
+———-+
| 1 |
+———-+
1 row in set (1.37 sec)
物化视图重写,这个mysql没有
or扩展,mysql没有扩展
explain extended select * from big_table,small_table where big_table.table_name=small_table.table_name and (big_table.column_key=’aa’ or small_table.column_key=’bbb’);
+—-+————-+————-+——+——————————-+—————-+———+—————————+——–+———-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+————-+——+——————————-+—————-+———+—————————+——–+———-+————-+
| 1 | SIMPLE | big_table | ALL | idx_TABLE_NAME,idx_column_key | NULL | NULL | NULL | 902397 | 100.00 | NULL |
| 1 | SIMPLE | small_table | ref | idx_table_name | idx_table_name | 194 | test.big_table.TABLE_NAME | 5 | 100.00 | Using where |
+—-+————-+————-+——+——————————-+—————-+———+—————————+——–+———-+————-+
| Note | 1003 | /* select#1 */ select test.big_table.TABLE_CATALOG AS TABLE_CATALOG,test.big_table.TABLE_SCHEMA AS TABLE_SCHEMA,test.big_table.TABLE_NAME AS TABLE_NAME,test.big_table.COLUMN_NAME AS COLUMN_NAME,test.big_table.ORDINAL_POSITION AS ORDINAL_POSITION,test.big_table.COLUMN_DEFAULT AS COLUMN_DEFAULT,test.big_table.IS_NULLABLE AS IS_NULLABLE,test.big_table.DATA_TYPE AS DATA_TYPE,test.big_table.CHARACTER_MAXIMUM_LENGTH AS CHARACTER_MAXIMUM_LENGTH,test.big_table.CHARACTER_OCTET_LENGTH AS CHARACTER_OCTET_LENGTH,test.big_table.NUMERIC_PRECISION AS NUMERIC_PRECISION,test.big_table.NUMERIC_SCALE AS NUMERIC_SCALE,test.big_table.DATETIME_PRECISION AS DATETIME_PRECISION,test.big_table.CHARACTER_SET_NAME AS CHARACTER_SET_NAME,test.big_table.COLLATION_NAME AS COLLATION_NAME,test.big_table.COLUMN_TYPE AS COLUMN_TYPE,test.big_table.COLUMN_KEY AS COLUMN_KEY,test.big_table.EXTRA AS EXTRA,test.big_table.PRIVILEGES AS PRIVILEGES,test.big_table.COLUMN_COMMENT AS COLUMN_COMMENT,test.big_table.id AS id,test.small_table.TABLE_CATALOG AS TABLE_CATALOG,test.small_table.TABLE_SCHEMA AS TABLE_SCHEMA,test.small_table.TABLE_NAME AS TABLE_NAME,test.small_table.COLUMN_NAME AS COLUMN_NAME,test.small_table.ORDINAL_POSITION AS ORDINAL_POSITION,test.small_table.COLUMN_DEFAULT AS COLUMN_DEFAULT,test.small_table.IS_NULLABLE AS IS_NULLABLE,test.small_table.DATA_TYPE AS DATA_TYPE,test.small_table.CHARACTER_MAXIMUM_LENGTH AS CHARACTER_MAXIMUM_LENGTH,test.small_table.CHARACTER_OCTET_LENGTH AS CHARACTER_OCTET_LENGTH,test.small_table.NUMERIC_PRECISION AS NUMERIC_PRECISION,test.small_table.NUMERIC_SCALE AS NUMERIC_SCALE,test.small_table.DATETIME_PRECISION AS DATETIME_PRECISION,test.small_table.CHARACTER_SET_NAME AS CHARACTER_SET_NAME,test.small_table.COLLATION_NAME AS COLLATION_NAME,test.small_table.COLUMN_TYPE AS COLUMN_TYPE,test.small_table.COLUMN_KEY AS COLUMN_KEY,test.small_table.EXTRA AS EXTRA,test.small_table.PRIVILEGES AS PRIVILEGES,test.small_table.COLUMN_COMMENT AS COLUMN_COMMENT,test.small_table.id AS id from test.big_table join test.small_table where ((test.small_table.TABLE_NAME = test.big_table.TABLE_NAME) and ((test.big_table.COLUMN_KEY = ‘aa’) or (test.small_table.COLUMN_KEY = ‘bbb’))) |
star trasformation 就不用说了
对于外连接视图的谓词推入,在oracle中,视图与别的表存在外关联的查询,那么视图是没有办法展开的,oracle可以允许相关的谓词推入到视图内部,筛选过滤数据,mysql中就不用实验le

点击复制链接 与好友分享!回本站首页
相关TAG标签 中实
上一篇:MySQL快速入门10----查询(2)
下一篇:mysql:day1--sql基础(基本语法及常识)
相关文章
图文推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站