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. --Vevb.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字段的范圍.