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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 关于JOIN使用不同类型的字段类型,数据库可能进行隐士转换,MYSQL ORACLE都是如此, 下面使用一个列子来看看,脚本如下: mysql: drop table testjoin1; drop table testjoin2; create tab...

关于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.
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
28天前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
|
2月前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
2月前
|
SQL 关系型数据库 MySQL
案例剖析:MySQL唯一索引并发插入导致死锁!
案例剖析:MySQL唯一索引并发插入导致死锁!
171 0
案例剖析:MySQL唯一索引并发插入导致死锁!
|
5天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
42 18
|
4天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
16 7
|
3天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化与慢查询优化:原理与实践
通过本文的介绍,希望您能够深入理解MySQL索引优化与慢查询优化的原理和实践方法,并在实际项目中灵活运用这些技术,提升数据库的整体性能。
22 5
|
22小时前
|
SQL 关系型数据库 MySQL
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
21 9
|
7天前
|
存储 关系型数据库 MySQL
Mysql索引:深入理解InnoDb聚集索引与MyisAm非聚集索引
通过本文的介绍,希望您能深入理解InnoDB聚集索引与MyISAM非聚集索引的概念、结构和应用场景,从而在实际工作中灵活运用这些知识,优化数据库性能。
46 7
|
1月前
|
SQL Oracle 关系型数据库
[Oracle]索引
本文介绍了数据库索引的基本概念、优化查询的原理及分类。索引是一种数据结构(如B树或B+树),通过排序后的`rowid`来优化查询性能。文章详细解释了索引的构建过程、B+树的特点及其优势,并介绍了五种常见的索引类型:唯一索引、组合索引、反向键索引、位图索引和基于函数的索引。每种索引都有其适用场景和限制,帮助读者更好地理解和应用索引技术。
58 1
[Oracle]索引
|
22天前
|
关系型数据库 MySQL Java
MySQL索引优化与Java应用实践
【11月更文挑战第25天】在大数据量和高并发的业务场景下,MySQL数据库的索引优化是提升查询性能的关键。本文将深入探讨MySQL索引的多种类型、优化策略及其在Java应用中的实践,通过历史背景、业务场景、底层原理的介绍,并结合Java示例代码,帮助Java架构师更好地理解并应用这些技术。
23 2

推荐镜像

更多