Mysql中字段类型不一致导致索引无效的处理办法-阿里云开发者社区

开发者社区> 余二五> 正文

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

简介:
+关注继续查看

前两天有个同事算数据,写出来的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 '编码',
 `namevarchar(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,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
关于重建LOB字段的表后是否需要重建LOB索引(试验)
SQL> create table test123  2    (t clob)  3   lob(t) store as (disable storage in row); Table created SQL> select SEGMENT_NAME,SEG...
640 0
Greenplum 优化CASE - 对齐JOIN字段类型,使用数组代替字符串,降低字符串处理开销,列存降低扫描开销
标签 PostgreSQL , 数组 , 字符串 , 字符串处理 , JOIN , where , 类型一致性 背景 Greenplum通常被用作OLAP,在一些用户使用过程中,可能因为数据结构设计,SQL问题等原因导致性能不佳,虽然通过增加节点可以解决问题,但是如果能优化的话,可以节约不少硬件资源。
1136 0
[Struts]处理表单中值为空的日期类型字段
在示范中心项目中,我们把ActionForm中日期类型的字段指定为String类型,而在对应的JavaBean中指定为java.sql.Date类型。当用户提交表单的时候,在Action里使用BeanUtils.copyProperties()方法从ActionForm构造JavaBean对象(详见利用BeanUtils在对象间复制属性)。
1031 0
PostgreSQL 快速给指定表每个字段创建索引 - 1
标签 PostgreSQL , 索引 , 所有字段 , 并行创建索引 , max_parallel_maintenance_workers 背景 如何快速给表的所有字段,每个字段都加上索引。 《PostgreSQL 设计优化case - 大宽表任意字段组合查询索引如何选择(btree, gin, rum) - (含单个索引列数超过32列的方法)》 满足任意字段组合查询的需求。
1418 0
SAP LSMW 导入OPEN PO 单据时候’税码’字段的处理
SAP LSMW Standard Batch (Direct) Input 方式制作的LSMW工具导入OPEN PO 单据时候’税码’字段的处理 如下的Open PO 批量导入LSMW工具,   ...
1087 0
PostgreSQL 快速给指定表每个字段创建索引 - 2
标签 PostgreSQL , 索引 , 所有字段 , 并行创建单个索引 , max_parallel_maintenance_workers , 异步调用 , dblink , 并行创建多个索引 , adhoc查询 背景 PostgreSQL 支持丰富的类型、索引,统计信息。
1032 0
+关注
20382
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载