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 t2G *************************** 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>
很明显执行计划变化了，在数据量少或者执行少的情况下还好，如果并发大，或者数据量大，那么这将是一个灾难，这也是有时候我们在优化SQL的时候，发现索引都有，执行计划就是不对，那么你就应该看看是否出现了类型转换。