mysql int索引无效_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 '编码',

`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) |

+----+-------------+-------+------+---------------+------+---------+-------+------+------------------------------------------------+

2 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 |

+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

2 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 |

+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

2 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 |

+----+-------------+-------+-------+---------------+------+---------+-------+------+---------------------------------------------+

2 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函数将类型严格的一方转换为类型松散的一方的类型，这样也能避免精度丢失。比如，可以将数值型向字符串类型转，反过来则会有问题。

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

阅读(780) | 评论(0) | 转发(0) |