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，創建測試數據

 代碼如下 復制代碼

<?php
$db = mysql_connect("localhost","root","");
mysql_select_db("test");
set_time_limit(0);
$table = 'test007';
for($i=0;$i<1000000;$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字段的范圍