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,代码如下:

1. CREATE TABLE `test007` (                                    
2. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,            
3. `key1` int(10) NOT NULL DEFAULT '0',                   
4. `key2` int(10) NOT NULL DEFAULT '0',                   
5. `key3` int(10) NOT NULL DEFAULT '0',                      
6. PRIMARY KEY (`id`)                                                          
7. ) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=gbk 

2，创建测试数据,代码如下:

1. <?php  
2. $db = mysql_connect("localhost","root","");  
3. mysql_select_db("test");  
4. set_time_limit(0);  
5. $table = 'test007';  
6. for($i=0;$i<1000000;$i++){  
7.     $k1 = rand(10000,300000);  
8.     $k2 = rand(0,3);  
9.     $k3 = rand(1,100000);  
10.     mysql_query("insert into $table (key1,key2,key3) values ('".$k1."','".$k2."','".$k3."')",$db);  
11. }  
12. ?> 

说明:创建1000000(100W）条记录,数据大小为16.2 MB.

3，测试参数类型为数字型的情况,代码如下:

1. mysql> update test007 set key1=key1+'1' where id=10000001; 
2. Query OK, 0 rows affected (0.00 sec) 
3. Rows matched: 0  Changed: 0  Warnings: 0 

查询语句的性能情况:

1. +----------------------+----------+ 
2. | Status               | Duration | 
3. +----------------------+----------+ 
4. | starting             | 0.000104 | 
5. | checking permissions | 0.000005 | 
6. | Opening tables       | 0.000010 | 
7. | System lock          | 0.013440 | 
8. | Table lock           | 0.000004 | 
9. | init                 | 0.000035 | 
10. | Updating             | 0.000020 | 
11. | end                  | 0.000034 | 
12. | query end            | 0.000002 | 
13. | freeing items        | 0.000028 | 
14. | logging slow query   | 0.000001 | 
15. | cleaning up          | 0.000005 | 
16. +----------------------+----------+ 
17. 12 rows in set (0.00 sec) 

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

4，测试参数类型为字符型的情况,代码如下:

1. mysql> update test007 set key1=key1+'1' where id='100000001'; 
2. Query OK, 0 rows affected (0.03 sec) 
3. Rows matched: 0  Changed: 0  Warnings:0 

查询语句的性能情况:

1. +----------------------+----------+ 
2. | Status               | Duration | 
3. +----------------------+----------+ 
4. | starting             | 0.000108 | 
5. | checking permissions | 0.000005 | 
6. | Opening tables       | 0.029382 | 
7. | System lock          | 0.000003 | 
8. | Table lock           | 0.000003 | 
9. | init                 | 0.000039 | 
10. | Updating             | 0.000074 | 
11. | end                  | 0.000022 | 
12. | query end            | 0.000002 | 
13. | freeing items        | 0.000033 | 
14. | logging slow query   | 0.000001 | 
15. | cleaning up          | 0.000001 | 
16. +----------------------+----------+ 
17. 12 rows in set (0.00 sec) 

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

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

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

第一步:创建数据表

1. CREATE TABLE `test008` (                                    
2. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,            
3. `key1` int(10) NOT NULL DEFAULT '0',                   
4. `key2` text,                   
5. `key3` int(10) NOT NULL DEFAULT '0',                      
6. PRIMARY KEY (`id`)                                                          
7. ) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=gbk 

第二步:创建测试数据

创建1000000(100W）条记录,数据大小为2.07 GB (2,224,000,000 字节).

第三步:两条查询性能比较

1. mysql> update test008 set key1=key1+'1' where id='100000001'; 
2. Query OK, 0 rows affected (0.03 sec) 
3. Rows matched: 0  Changed: 0  Warnings: 0 
4.  
5. mysql> update test008 set key1=key1+'1' where id=100000001; 
6. Query OK, 0 rows affected (0.00 sec) 
7. Rows matched: 0  Changed: 0  Warnings: 0 

第四步:创建索引

1. mysql> alter table test008 add index key3 (key3); 
2. Query OK, 1000000 rows affected (5 min 54.33 sec) 
3. Records: 1000000  Duplicates:0  Warnings: 0 

第五步:测试不同的条件

1. mysql> update test008 set key1 = key1 + '1' where id=''; 
2. Query OK, 0 rows affected (0.01 sec) 
3. Rows matched: 0  Changed: 0  Warnings: 0 
4.  
5. mysql> update test008 set key1 = key1 + '1' where id='12321232123'; 
6. Query OK, 0 rows affected (44.58 sec) 
7. Rows matched: 0  Changed: 0  Warnings: 0 
8.  
9. mysql> update test008 set key1 = key1 + '1' where id=12321232123; 
10. Query OK, 0 rows affected (0.00 sec) 
11. Rows matched: 0  Changed: 0  Warnings: 0 
12. --phpfensi.com 
13. mysql> update test008 set key1= key1+ '1' where id='test'; 
14. Query OK, 0 rows affected (0.00 sec) 
15. Rows matched: 0  Changed: 0  Warnings:0 

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