sql update更新不同字段类型性能分析

MySQL在使用update更新数据时，如果条件字段的类型为数字型，但参数是字符型的而且该条件又匹配不到记录，就会引起严重的性能问题

。如下：

代码如下	复制代码
1，update test007 set key1 = key1 + '1' where id = 200000; 2，update test007 set key1 = key1 + '1' where id = '200000';

注意上面查询语句区别在于参数的类型不同，前者为数字型，后者为字符型，同时id为200000这条记录是不存在的。

如果使用第二条查询，而且满足记录不存在，这条查询将出现严重的效率问题，测试情况如下：

二，测试实践
1，创建一张测试数据表test007

代码如下

复制代码

CREATE TABLE `test007` (                                   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,           `key1` int(10) NOT NULL DEFAULT '0',                  `key2` int(10) NOT NULL DEFAULT '0',                  `key3` int(10) NOT NULL DEFAULT '0',                     PRIMARY KEY (`id`)                                                         ) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=gbk

2，创建测试数据

代码如下	复制代码
$db = _connect("localhost","root",""); mysql__db("test"); set_time_limit(0); $table = 'test007'; for($i=0;$i     $k1 = rand(10000,300000);     $k2 = rand(0,3);     $k3 = rand(1,100000);     mysql_query("insert into $table (key1,key2,key3) values ('".$k1."','".$k2."','".$k3."')",$db); } ?>

说明：创建1000000(100W）条记录，数据大小为16.2 MB

3，测试参数类型为数字型的情况

代码如下

复制代码

mysql> update test007 set key1=key1+'1' where id=10000001; Query OK, 0 rows affected (0.00 sec) Rows matched: 0  Changed: 0  Warnings: 0 查询语句的性能情况 +----------------------+----------+ | Status               | Duration | +----------------------+----------+ | starting             | 0.000104 | | checking permissions | 0.000005 | | Opening tables       | 0.000010 | | System lock          | 0.013440 | | Table lock           | 0.000004 | | init                 | 0.000035 | | Updating             | 0.000020 | | end                  | 0.000034 | | query end            | 0.000002 | | freeing items        | 0.000028 | | logging slow query   | 0.000001 | | cleaning up          | 0.000005 | +----------------------+----------+ 12 rows in set (0.00 sec)

说明：主键id的字段类型为数字型

4，测试参数类型为字符型的情况

代码如下

复制代码

mysql> update test007 set key1=key1+'1' where id='100000001'; Query OK, 0 rows affected (0.03 sec) Rows matched: 0  Changed: 0  Warnings: 0 查询语句的性能情况 +----------------------+----------+ | Status               | Duration | +----------------------+----------+ | starting             | 0.000108 | | checking permissions | 0.000005 | | Opening tables       | 0.029382 | | System lock          | 0.000003 | | Table lock           | 0.000003 | | init                 | 0.000039 | | Updating             | 0.000074 | | end                  | 0.000022 | | query end            | 0.000002 | | freeing items        | 0.000033 | | logging slow query   | 0.000001 | | cleaning up          | 0.000001 | +----------------------+----------+ 12 rows in set (0.00 sec)

在使用UPDATE更新记录时，如果被更新的字段的类型和所赋的值不匹配时，MySQL将这个值转换为相应类型的值。如果这个字段是数值类型，而且所赋值超 过了这个数据类型的最大范围，那么MySQL就将这个值转换为这个范围最大或最小值。如果字符串太长，MySQL就将多余的字符串截去。如果设置非空字段 为空，那么将这个字段设置为它们的默认值，数字的默认值是0，字符串的默认值是空串（不是null，是""）。

由于测试环境数据量比较小，所以测试的结果不明显，但关键是在开发过程中一定要注意字段类型与参数类型的一致性，避免在特定情况下造成数据在更新和删除过程中的额外开销。

5，测试大数据量的情况，过程如下

代码如下

复制代码

第一步：创建数据表 CREATE TABLE `test008` (                                   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,           `key1` int(10) NOT NULL DEFAULT '0',                  `key2` text,                  `key3` int(10) NOT NULL DEFAULT '0',                     PRIMARY KEY (`id`)                                                         ) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=gbk 第二步：创建测试数据 创建1000000(100W）条记录，数据大小为2.07 GB (2,224,000,000 字节) 第三步：两条查询性能比较 mysql> update test008 set key1=key1+'1' where id='100000001'; Query OK, 0 rows affected (0.03 sec) Rows matched: 0  Changed: 0  Warnings: 0 mysql> update test008 set key1=key1+'1' where id=100000001; Query OK, 0 rows affected (0.00 sec) Rows matched: 0  Changed: 0  Warnings: 0 第四步：创建索引 mysql> alter table test008 add index key3 (key3); Query OK, 1000000 rows affected (5 min 54.33 sec) Records: 1000000  Duplicates: 0  Warnings: 0 第五步：测试不同的条件 mysql> update test008 set key1 = key1 + '1' where id=''; Query OK, 0 rows affected (0.01 sec) Rows matched: 0  Changed: 0  Warnings: 0 mysql> update test008 set key1 = key1 + '1' where id='12321232123'; Query OK, 0 rows affected (44.58 sec) Rows matched: 0  Changed: 0  Warnings: 0 mysql> update test008 set key1 = key1 + '1' where id=12321232123; Query OK, 0 rows affected (0.00 sec) Rows matched: 0  Changed: 0  Warnings: 0 mysql> update test008 set key1= key1+ '1' where id='test'; Query OK, 0 rows affected (0.00 sec) Rows matched: 0  Changed: 0  Warnings: 0

注意：上面测试中部分条件已经超出id字段的范围