Mysql中字段类型不一致导致索引无效的处理办法

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

前两天有个同事算数据,写出来的sql执行很慢。那个sql也很简单,就是一个左联带条件的查询。explain之后发现,其中有一张表没有用到索引。初始以为是没有建索引,于是建上索引再试,发现问题依旧。后来查看表结构才发现,原来用来做关联的字段是一个varchar类型的字段,而联接的另一张表中的字段类型却是bigint,结果造成了类型的不匹配,以致于索引失效。

如果要想索引起效,最直接的办法就是将两张表的对应字段类型改成一样的。但如果表中数据量很大,或者改类型有风险时可以采用另一种办法,即通过类型转换函数来处理。

下面详细说明一下:


首先建立两张测试表,分别是table_a,table_b.


1
2
3
4
5
6
CREATE  TABLE  `table_a` (
  `id`  int (10) unsigned  NOT  NULL  AUTO_INCREMENT COMMENT  '自增ID' ,
  `code`  varchar (20)  NOT  NULL  COMMENT  '编码' ,
  PRIMARY  KEY  (`id`),
  KEY  `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=7  DEFAULT  CHARSET=utf8


1
2
3
4
5
CREATE  TABLE  `table_b` (
  `code`  int (10) unsigned  NOT  NULL  COMMENT  '编码' ,
  ` name varchar (20)  NOT  NULL  COMMENT  '名称' ,
  KEY  `code` (`code`)
) ENGINE=InnoDB  DEFAULT  CHARSET=utf8


然后再往两张表中分别插入几条记录

1
2
3
4
5
6
7
INSERT  INTO  `table_a` (`id`, `code`)  VALUES
(1,  '1001' ),
(5,  '1001' ),
(3,  '1002' ),
(6,  '1002' ),
(2,  'A001' ),
(4,  'B001' );


1
2
3
INSERT  INTO  `table_b` (`code`, ` name `)  VALUES
(1001,  '测试1' ),
(1002,  '测试2' );


好了,数据准备完成,下面可以做测试了


首先,我们做一个简单的左联接查询:

1
2
3
4
5
6
7
mysql>  SELECT  count (1)  FROM  `table_a` a  LEFT  JOIN  table_b b  ON  a.code = b.code  WHERE  b.code =1001;
+ ----------+
count (1) |
+ ----------+
|        2 |
+ ----------+
1 row  in  set , 2 warnings (0.00 sec)



因为数据量很少,所以查询几乎不耗时,我们来看一下explain的结果:


1
2
3
4
5
6
7
8
9
mysql> explain  SELECT  count (1)  FROM  `table_a` a  LEFT  JOIN  table_b b  ON  a.code = b.code
WHERE  b.code =1001;
+ ----+-------------+-------+------+---------------+------+---------+-------+------+------------------------------------------------+
| id | select_type |  table  | type | possible_keys |  key   | key_len | ref   |  rows  | Extra                                          |
+ ----+-------------+-------+------+---------------+------+---------+-------+------+------------------------------------------------+
|  1 | SIMPLE      | b     | ref  | code          | code | 4       | const |    1 | Using  where ; Using  index                        |
|  1 | SIMPLE      | a     |  ALL   | code          |  NULL  NULL     NULL   |    6 | Range checked  for  each record ( index  map: 0x2) |
+ ----+-------------+-------+------+---------------+------+---------+-------+------+------------------------------------------------+
rows  in  set  (0.00 sec)


注意表格的第二行,其中type是ALL,key是NULL,ref也是NULL,也就是说没有用到索引。

当数据量达到百万级时,这样的查询是非常慢的。


接下来,我们用convert函数来对字段进行处理,再看一下explain的结果:


1
2
3
4
5
6
7
8
9
mysql> explain  SELECT  count (1)  FROM  `table_a` a  LEFT  JOIN  table_b b  ON  a.code =  convert (b.code,  char )
WHERE  b.code =1001;
+ ----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type |  table  | type | possible_keys |  key   | key_len | ref   |  rows  | Extra                    |
+ ----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | b     | ref  | code          | code | 4       | const |    1 | Using  where ; Using  index  |
|  1 | SIMPLE      | a     | ref  | code          | code | 62      | const |    2 | Using  where ; Using  index  |
+ ----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
rows  in  set  (0.00 sec)


可以看到key,ref都不再是NULL了,rows的数量也比之前的要少得多,索引已经生效。


当然,用cast也是一样的,如下:


1
2
3
4
5
6
7
8
9
mysql> explain  SELECT  count (1)  FROM  `table_a` a  LEFT  JOIN  table_b b  ON  a.code =  cast (b.code  as  char )
WHERE  b.code =1001;
+ ----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type |  table  | type | possible_keys |  key   | key_len | ref   |  rows  | Extra                    |
+ ----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | b     | ref  | code          | code | 4       | const |    1 | Using  where ; Using  index  |
|  1 | SIMPLE      | a     | ref  | code          | code | 62      | const |    2 | Using  where ; Using  index  |
+ ----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
rows  in  set  (0.00 sec)


上面的转换是将b表中的字段类型转成跟a表的一样,如果反过来,将a表的转成跟b表的一样,会有效果么?


于是,测试了如下的语句:

1
2
3
4
5
6
7
8
9
mysql> explain  SELECT  count (1)  FROM  `table_a` a  LEFT  JOIN  table_b b  ON  convert (a.code, signed) = b.code
WHERE  b.code =1001;
+ ----+-------------+-------+-------+---------------+------+---------+-------+------+---------------------------------------------+
| id | select_type |  table  | type  | possible_keys |  key   | key_len | ref   |  rows  | Extra                                       |
+ ----+-------------+-------+-------+---------------+------+---------+-------+------+---------------------------------------------+
|  1 | SIMPLE      | b     | ref   | code          | code | 4       | const |    1 | Using  where ; Using  index                     |
|  1 | SIMPLE      | a     |  index  NULL           | code | 62      |  NULL   |    6 | Using  where ; Using  index ; Using  join  buffer |
+ ----+-------------+-------+-------+---------------+------+---------+-------+------+---------------------------------------------+
rows  in  set  (0.00 sec)


可以看到key这一列不再是NULL了,但ref这一列仍然是NULL,这说明用到了索引,但索引没有找到适合适的引用。从rows的数量上看也没有变化。这种转换是不成功的。


思考一下原因,a表中的code包含多种类型的数据,而b表中的code只有一种类型,当由a转换为b类型时,a表中的数据有可能会丢失。实际上Mysql对于由字符串转整型还会给出警告。


如下:

1
2
3
4
5
6
7
mysql>  select  convert ( 'A001' , signed);
+ -------------------------+
convert ( 'A001' , signed) |
+ -------------------------+
|                       0 |
+ -------------------------+
1 row  in  set , 1 warning (0.00 sec)


结论:当表联接的字段类型不匹配时索引会失效,要想索引生效,可以用cast或convert函数将类型严格的一方转换为类型松散的一方的类型,这样也能避免精度丢失。比如,可以将数值型向字符串类型转,反过来则会有问题。


吐槽一下,在firefox中为什么过长的行会被隐藏,敢不敢overflow不hidden,从mysql里拷个explain的表格现在都会被截断,太纠结了。To 51的程序员们











本文转自 ustb80 51CTO博客,原文链接:http://blog.51cto.com/ustb80/1287847,如需转载请自行联系原作者
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
234
分享
相关文章
MySQL索引学习笔记
本文深入探讨了MySQL数据库中慢查询分析的关键概念和技术手段。
311 80
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
MySQL底层概述—8.JOIN排序索引优化
MySQL原理简介—9.MySQL索引原理
本文详细介绍了MySQL索引的设计与使用原则,涵盖磁盘数据页的存储结构、页分裂机制、主键索引设计及查询过程、聚簇索引和二级索引的原理、B+树索引的维护、联合索引的使用规则、SQL排序和分组时如何利用索引、回表查询对性能的影响以及索引覆盖的概念。此外还讨论了索引设计的案例,包括如何处理where筛选和order by排序之间的冲突、低基数字段的处理方式、范围查询字段的位置安排,以及通过辅助索引来优化特定查询场景。总结了设计索引的原则,如尽量包含where、order by、group by中的字段,选择离散度高的字段作为索引,限制索引数量,并针对频繁查询的低基数字段进行特殊处理等。
MySQL原理简介—9.MySQL索引原理
MySQL底层概述—6.索引原理
本文详细回顾了:索引原理、二叉查找树、平衡二叉树(AVL树)、红黑树、B-Tree、B+Tree、Hash索引、聚簇索引与非聚簇索引。
MySQL底层概述—6.索引原理
图解MySQL【日志】——磁盘 I/O 次数过高时优化的办法
当 MySQL 磁盘 I/O 次数过高时,可通过调整参数优化。控制刷盘时机以降低频率:组提交参数 `binlog_group_commit_sync_delay` 和 `binlog_group_commit_sync_no_delay_count` 调整等待时间和事务数量;`sync_binlog=N` 设置 write 和 fsync 频率,`innodb_flush_log_at_trx_commit=2` 使提交时只写入 Redo Log 文件,由 OS 择机持久化,但两者在 OS 崩溃时有丢失数据风险。
39 3
MySQL秘籍之索引与查询优化实战指南
最左前缀原则。不冗余原则。最大选择性原则。所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快!
130 22
 MySQL秘籍之索引与查询优化实战指南
浅入浅出——MySQL索引
本文介绍了数据库索引的概念和各种索引结构,如哈希表、B+树、InnoDB引擎的索引运作原理等。还分享了覆盖索引、联合索引、最左前缀原则等优化技巧,以及如何避免索引误用,提高数据库性能。
MySQL中为什么要使用索引合并(Index Merge)?
通过这些内容的详细介绍和实际案例分析,希望能帮助您深入理解索引合并及其在MySQL中的
192 10
缓存与数据库的一致性方案,Redis与Mysql一致性方案,大厂P8的终极方案(图解+秒懂+史上最全)
缓存与数据库的一致性方案,Redis与Mysql一致性方案,大厂P8的终极方案(图解+秒懂+史上最全)
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等