MySQL SQL优化之字符串索引隐式转换
之前有用户很不解:SQL语句非常简单,就是select * from test_1 where user_id=1 这种类型,而且user_id上已经建立索引了,怎么还是查询很慢?
test_1的表结构:
CREATE TABLE `test_1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` varchar(30) NOT NULL,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
查看执行计划,可以看出进行了全表扫描,并没有用上user_id的索引。
mysql> explain select * from test_1 where user_id=1;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test_1 | ALL | idx_user_id | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)
仔细看下表结构,user_id的字段类型: `user_id` varchar(30) NOT NULL,
而用户传入的是int,这里会有一个隐式转换的问题。隐式转换会导致全表扫描。
把输入改成字符串类型,执行计划如下,这样就会很快了。
mysql> explain select * from test_1 where user_id='1';
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------------+
| 1 | SIMPLE | test_1 | ref | idx_user_id | idx_user_id | 92 | const | 1 | Using where |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
此外,还需要注意的是:
数字类型的0001等价于1
字符串的0001和1不等价
mysql> select * from test_1;
+----+---------+------+
| id | user_id | name |
+----+---------+------+
| 1 | 0001 | kate |
| 2 | 1101 | Jim |
| 3 | 1 | Jim |
+----+---------+------+
3 rows in set (0.01 sec)
mysql> select * from test_1 where user_id=1;
+----+---------+------+
| id | user_id | name |
+----+---------+------+
| 1 | 0001 | kate |
| 3 | 1 | Jim |
+----+---------+------+
2 rows in set (0.00 sec)
mysql> select * from test_1 where user_id='1';
+----+---------+------+
| id | user_id | name |
+----+---------+------+
| 3 | 1 | Jim |
+----+---------+------+
1 row in set (0.00 sec)
如果表定义的是int字段,传入的是字符串,则不会发生隐式转换。
看下面的测试:
CREATE TABLE `test_2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
mysql> explain select * from test_2 where user_id=1;
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
| 1 | SIMPLE | test_2 | ref | idx_user_id | idx_user_id | 4 | const | 2 | |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> explain select * from test_2 where user_id='1';
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
| 1 | SIMPLE | test_2 | ref | idx_user_id | idx_user_id | 4 | const | 2 | |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
1 row in set (0.00 sec)