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. CREATETABLE`test007`(
2. `id`int(10)unsignedNOTNULLAUTO_INCREMENT,
3. `key1`int(10)NOTNULLDEFAULT'0',
4. `key2`int(10)NOTNULLDEFAULT'0',
5. `key3`int(10)NOTNULLDEFAULT'0',
6. PRIMARYKEY(`id`)
7. )ENGINE=MyISAMAUTO_INCREMENT=0DEFAULTCHARSET=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("insertinto$table(key1,key2,key3)values('".$k1."','".$k2."','".$k3."')",$db);
11. }
12. ?>

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

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

1. mysql>updatetest007setkey1=key1+'1'whereid=10000001;
2. QueryOK,0rowsaffected(0.00sec)
3. Rowsmatched:0Changed:0Warnings:0

查询语句的性能情况:

1. +----------------------+----------+
2. |Status|Duration|
3. +----------------------+----------+
4. |starting|0.000104|
5. |checkingpermissions|0.000005|
6. |Openingtables|0.000010|
7. |Systemlock|0.013440|
8. |Tablelock|0.000004|
9. |init|0.000035|
10. |Updating|0.000020|
11. |end|0.000034|
12. |queryend|0.000002|
13. |freeingitems|0.000028|
14. |loggingslowquery|0.000001|
15. |cleaningup|0.000005|
16. +----------------------+----------+
17. 12rowsinset(0.00sec)

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

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

1. mysql>updatetest007setkey1=key1+'1'whereid='100000001';
2. QueryOK,0rowsaffected(0.03sec)
3. Rowsmatched:0Changed:0Warnings:0

查询语句的性能情况:

1. +----------------------+----------+
2. |Status|Duration|
3. +----------------------+----------+
4. |starting|0.000108|
5. |checkingpermissions|0.000005|
6. |Openingtables|0.029382|
7. |Systemlock|0.000003|
8. |Tablelock|0.000003|
9. |init|0.000039|
10. |Updating|0.000074|
11. |end|0.000022|
12. |queryend|0.000002|
13. |freeingitems|0.000033|
14. |loggingslowquery|0.000001|
15. |cleaningup|0.000001|
16. +----------------------+----------+
17. 12rowsinset(0.00sec)

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

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

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

第一步:创建数据表

1. CREATETABLE`test008`(
2. `id`int(10)unsignedNOTNULLAUTO_INCREMENT,
3. `key1`int(10)NOTNULLDEFAULT'0',
4. `key2`text,
5. `key3`int(10)NOTNULLDEFAULT'0',
6. PRIMARYKEY(`id`)
7. )ENGINE=MyISAMAUTO_INCREMENT=0DEFAULTCHARSET=gbk

第二步:创建测试数据

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

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

1. mysql>updatetest008setkey1=key1+'1'whereid='100000001';
2. QueryOK,0rowsaffected(0.03sec)
3. Rowsmatched:0Changed:0Warnings:0
5. mysql>updatetest008setkey1=key1+'1'whereid=100000001;
6. QueryOK,0rowsaffected(0.00sec)
7. Rowsmatched:0Changed:0Warnings:0

第四步:创建索引

1. mysql>altertabletest008addindexkey3(key3);
2. QueryOK,1000000rowsaffected(5min54.33sec)
3. Records:1000000Duplicates:0Warnings:0

第五步:测试不同的条件

1. mysql>updatetest008setkey1=key1+'1'whereid='';
2. QueryOK,0rowsaffected(0.01sec)
3. Rowsmatched:0Changed:0Warnings:0
5. mysql>updatetest008setkey1=key1+'1'whereid='12321232123';
6. QueryOK,0rowsaffected(44.58sec)
7. Rowsmatched:0Changed:0Warnings:0
9. mysql>updatetest008setkey1=key1+'1'whereid=12321232123;
10. QueryOK,0rowsaffected(0.00sec)
11. Rowsmatched:0Changed:0Warnings:0
12. --phpfensi.com
13. mysql>updatetest008setkey1=key1+'1'whereid='test';
14. QueryOK,0rowsaffected(0.00sec)
15. Rowsmatched:0Changed:0Warnings:0

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