ORACLE MYSQL中join 字段类型不同索引失效的情况

关于JOIN使用不同类型的字段类型，数据库可能进行隐士转换，MYSQL ORACLE都是如此，
下面使用一个列子来看看，脚本如下：


mysql:
drop table testjoin1;
drop table testjoin2;
create table testjoin1(id int, name varchar(20));
create table testjoin2(id varchar(20),name varchar(20),key(id);


oracle:
drop table testjoin1;
drop table testjoin2;
create table testjoin1(id int,name varchar2(20));
create table testjoin2(id varchar(20),name varchar2(20));
create index test_id_2 on testjoin2(id);


insert into testjoin1 values(1,'gaopeng');
insert into testjoin1 values(2,'gaopeng');
insert into testjoin1 values(3,'gaopeng');
insert into testjoin1 values(4,'gaopeng');
insert into testjoin1 values(5,'gaopeng');
insert into testjoin2 values('1','gaopeng');




ORACLE中的隐士转换，
SQL> select /*+ use_nl(a b) ordered */ * from testjoin1 a join testjoin2 b on a.id=b.id  ;

        ID NAME                 ID                   NAME
---------- -------------------- -------------------- --------------------
         1 gaopeng              1                    gaopeng

Execution Plan
----------------------------------------------------------
Plan hash value: 2498279186


--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    49 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |           |     1 |    49 |     5   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TESTJOIN1 |     5 |   125 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TESTJOIN2 |     1 |    24 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("A"."ID"=TO_NUMBER("B"."ID")) --虽然TESTJOIN2是被驱动表由于隐士转换索引用不到

mysql> explain select * from testjoin1 a Straight_JOIN testjoin2 b on a.id=b.id  ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL                                               |
|  1 | SIMPLE      | b     | NULL       | ALL  | id            | NULL | NULL    | NULL |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 3 warnings (0.00 sec)

Warning (Code 1739): Cannot use ref access on index 'id' due to type or collation conversion on field 'id' --虽然TESTJOIN2是被驱动表由于隐士转换索引用不到 possible_keys可以看出
Warning (Code 1739): Cannot use range access on index 'id' due to type or collation conversion on field 'id'
Note (Code 1003): /* select#1 */ select `test`.`a`.`id` AS `id`,`test`.`a`.`name` AS `name`,`test`.`b`.`id` AS `id`,`test`.`b`.`name` AS `name` from `test`.`testjoin1` `a` straight_join `test`.`testjoin2` `b` where (`test`.`a`.`id` = `test`.`b`.`id`)

MYSQL手册原文：
To retrieve rows from other tables when performing joins. MySQL can use indexes on columns more
efficiently if they are declared as the same type and size. In this context, VARCHARand CHARare
considered the same if they are declared as the same size. For example, VARCHAR(10)and CHAR(10)
are the same size, but VARCHAR(10)and CHAR(15)are not.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-2131608/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7728585/viewspace-2131608/