SQL优化之【类型转换】

DBA的日常工作中SQL优化占大半的时间，通常都是SQL语句性能问题或者schema设计有问题，最近遇到一个类型转换的问题，所以在这里分享一下，废话不多说了，直接建表进行测试。

mysql> create table t1 ( id int , name char(20), key ( id) );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 select 1,'aa';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t1 select 2,'bb';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> create table t2 select * from t1;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>

进行查询测试：

mysql> explain select a.id from t1 as a inner join t2 as b on a.id=b.id;  
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | a     | index | id            | id   | 5       | NULL |    2 | Using index                    |
|  1 | SIMPLE      | b     | ALL   | NULL          | NULL | NULL    | NULL |    2 | Using where; Using join buffer |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------+

这什么情况？出现了Using join buffer，那是因为我们在创建t2表的时候直接使用了create table xxx select * from xxx，这种创建表且带数据的方法会去掉索引。我们看看表结构：

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

可以看见id上面的索引不在了，加上索引回归主题进行测试。

mysql> alter table t2 add key(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

再次进行join查询：

mysql> explain select a.id from t1 as a inner join t2 as b on a.id=b.id;
+----+-------------+-------+-------+---------------+------+---------+-----------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref       | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+-----------+------+--------------------------+
|  1 | SIMPLE      | a     | index | id            | id   | 5       | NULL      |    2 | Using index              |
|  1 | SIMPLE      | b     | ref   | id            | id   | 5       | test.a.id |    1 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+-----------+------+--------------------------+
2 rows in set (0.00 sec)

mysql>

可以看见上面的执行计划是正确的，没有异常，那么何时出现类型转换呢？很简单，我们把t2表的id字段改成字符串就行。

mysql> alter table t2 modify id char(20);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>

mysql> explain select a.id from t1 as a inner join t2 as b on a.id=b.id;
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                       |
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------+
|  1 | SIMPLE      | a     | index | id            | id   | 5       | NULL |    2 | Using index                                 |
|  1 | SIMPLE      | b     | index | id            | id   | 61      | NULL |    2 | Using where; Using index; Using join buffer |
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------+
2 rows in set (0.00 sec)

mysql>