MYSQL 类型转换影起的灾难
众所周知, 如果发生了隐形转换,是无法利用上索引的, 这个是我们在优化SQL时必要的检查项。 下面做个小测试, 反应下在线上发生的灾难。 1. 建测试表: create table ta as select * from employees; Query OK, 300024 rows affected (4.86 sec) Records: 300024 Duplicates: 0 Warnings: 0 2.修改字段类型: alter table ta modify emp_no varchar(20); Query OK, 300024 rows affected (4.76 sec) Records: 300024 Duplicates: 0 Warnings: 0 3.建立索引: alter table ta add index(emp_no); Query OK, 0 rows affected (4.40 sec) Records: 0 Duplicates: 0 Warnings: 0 4. 测试查询: explain select count(*) from employees a, ta b where a.emp_no=b.emp_no and a.emp_no<=10000; +----+-------------+-------+-------+---------------+---------+---------+------+--------+---------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+--------+---------------------------------------------+ | 1 | SIMPLE | a | range | PRIMARY | PRIMARY | 4 | NULL | 1 | Using where; Using index | | 1 | SIMPLE | b | index | emp_no | emp_no | 63 | NULL | 300968 | Using where; Using index; Using join buffer | +----+-------------+-------+-------+---------------+---------+---------+------+--------+---------------------------------------------+ 2 rows in set (0.00 sec) 在这里, 问题就爆出来了, 如果并发很高, 这个SQL肯定会引起负载超高。 改善: 修改字段类型: alter table ta modify emp_no int(11); Query OK, 300024 rows affected (4.48 sec) Records: 300024 Duplicates: 0 Warnings: 0 再次查看执行计划, 这时就发生了很大的变化。 dba@localhost : employees 17:12:10> explain select count(*) from employees a, ta b where a.emp_no=b.emp_no and a.emp_no<=10000; +----+-------------+-------+-------+---------------+---------+---------+--------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+--------------------+------+--------------------------+ | 1 | SIMPLE | a | range | PRIMARY | PRIMARY | 4 | NULL | 1 | Using where; Using index | | 1 | SIMPLE | b | ref | emp_no | emp_no | 5 | employees.a.emp_no | 1 | Using where; Using index | +----+-------------+-------+-------+---------------+---------+---------+--------------------+------+--------------------------+ 2 rows in set (0.01 sec) 从以上的小案例, 我们可以看出, 在解决语句性能问题时, 这些还是首要关注的点。