oracle查询转换在mysql中实现对比

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