MySQL单引号引起的严重性能问题

最近在工作中遇到一个MySQL update的严重性能问题，语句大概是这样的:

 

 

update t_user set fstate=0 where fuid in (11111, 22222, ....,  .....)

 

 

in后的括号中，有1000个fuid的样子

 

表结构如下：

 

 

 

 

存储引擎是Innodb, 版本是MySQL 4.0.18 

 

其中fuid是主键, 由于表数据量在300W左右，所以刚开始更新很慢，我以为是表数据量太大引起的性能问题，

 

起初一直在寻求优化MySQL数据库配置，结果都是浮云，语句还是那么的慢。

 

 

偶然期发现自己用的是varchar字段类型，但却在赋值时没有加单引号：

 

对于varchar或者char字段里，如果只放数字类型的字符串，可以不加单引号执行SQL语句，不报错：

 

例如：

 

正常的写法是：

 

 

select  * from t_user where fuid='11111';

但你可以写这样写：

 

select  * from t_user where fuid=11111;

 

这条语句完全可以正常执行，但是表数据量很大的时候速度很慢。

 

 

但如果这样写：

 

select * from t_user where fuid=abcdef;

 

SQL执行时会报错，于是用explain分析：

 

在未加单引号时：

 

 

加入单引号时：

 

 

执行计划里type说明了问题所在，不加单引号的会造成全表扫描，所以表数据量越大速度越慢，找出了问题所在，

 

给原来的update语句加上了单引号，在300W数量级的表中，速度快了200倍的样子。

 

 

我在最新版本的MySQL 5.1里也实验过，同样有这个问题，百思不得其解，为什么MySQL在检查到where后查询条件

 

字段是varchar，不强制限制赋值必须用单引号，字母的可以检测到，但数字就不会。

 

 

如果有高人知道MySQL为什么这样做的原因可以告诉我，小弟不胜感激 ：）

 

 

评论

4 楼 tuhaitao 2012-02-25  

fengyie007 写道

参数类型与数据库类型不同，虽然能自转转换，但无法走索引，肯定慢了。

不是没发走索引，是木有索引可走，这是由于数据类型不一致，没办法比较，右值数据类型是int, 而左值存储的字段类型是varchar, mysql木有严格的检查数据类型，我估计是采用尝试的策略来转换数据类型，发现字段值可以转换成int，于是转换了改列所有的数据，由于记录数很多，所以造成了很慢，而后我又尝试将varchar字段里赋值为'asd测试' 这样的数据，发现mysql查询报错，这说明确实采用了运行时动态转换的原则，发现转换后的字符编码不再'0'-'9'的字符范畴，这里我觉得mysql以后的版本里最好限制这种隐式转换，不同的数据类型不让比较，便可以防止这种问题的发生：）

3 楼 tuhaitao 2011-11-23  

utensil 写道

原因是mysql不是把你的数字转成了字符串，而是把每个查询到的结果转成了数字去比较：

http://dev.mysql.com/doc/refman/5.0/en/how-to-avoid-table-scan.html

You are comparing indexed columns with constant values and MySQL has calculated (based on the index tree) that the constants cover too large a part of the table and that a table scan would be faster. See Section 7.3.1.2, “WHERE Clause Optimization”.

学习了

2 楼 utensil 2011-11-16  

原因是mysql不是把你的数字转成了字符串，而是把每个查询到的结果转成了数字去比较：

http://dev.mysql.com/doc/refman/5.0/en/how-to-avoid-table-scan.html

You are comparing indexed columns with constant values and MySQL has calculated (based on the index tree) that the constants cover too large a part of the table and that a table scan would be faster. See Section 7.3.1.2, “WHERE Clause Optimization”.

1 楼 fengyie007 2011-11-16  

参数类型与数据库类型不同，虽然能自转转换，但无法走索引，肯定慢了。