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,代码如下:
- CREATETABLE`test007`(
- `id`int(10)unsignedNOTNULLAUTO_INCREMENT,
- `key1`int(10)NOTNULLDEFAULT'0',
- `key2`int(10)NOTNULLDEFAULT'0',
- `key3`int(10)NOTNULLDEFAULT'0',
- PRIMARYKEY(`id`)
- )ENGINE=MyISAMAUTO_INCREMENT=0DEFAULTCHARSET=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("insertinto$table(key1,key2,key3)values('".$k1."','".$k2."','".$k3."')",$db);
- }
- ?>
说明:创建1000000(100W)条记录,数据大小为16.2 MB.
3,测试参数类型为数字型的情况,代码如下:
- mysql>updatetest007setkey1=key1+'1'whereid=10000001;
- QueryOK,0rowsaffected(0.00sec)
- Rowsmatched:0Changed:0Warnings:0
查询语句的性能情况:
- +----------------------+----------+
- |Status|Duration|
- +----------------------+----------+
- |starting|0.000104|
- |checkingpermissions|0.000005|
- |Openingtables|0.000010|
- |Systemlock|0.013440|
- |Tablelock|0.000004|
- |init|0.000035|
- |Updating|0.000020|
- |end|0.000034|
- |queryend|0.000002|
- |freeingitems|0.000028|
- |loggingslowquery|0.000001|
- |cleaningup|0.000005|
- +----------------------+----------+
- 12rowsinset(0.00sec)
说明:主键id的字段类型为数字型.
4,测试参数类型为字符型的情况,代码如下:
- mysql>updatetest007setkey1=key1+'1'whereid='100000001';
- QueryOK,0rowsaffected(0.03sec)
- Rowsmatched:0Changed:0Warnings:0
查询语句的性能情况:
- +----------------------+----------+
- |Status|Duration|
- +----------------------+----------+
- |starting|0.000108|
- |checkingpermissions|0.000005|
- |Openingtables|0.029382|
- |Systemlock|0.000003|
- |Tablelock|0.000003|
- |init|0.000039|
- |Updating|0.000074|
- |end|0.000022|
- |queryend|0.000002|
- |freeingitems|0.000033|
- |loggingslowquery|0.000001|
- |cleaningup|0.000001|
- +----------------------+----------+
- 12rowsinset(0.00sec)
在使用UPDATE更新记录时,如果被更新的字段的类型和所赋的值不匹配时,MySQL将这个值转换为相应类型的值。如果这个字段是数值类型,而且所赋值超 过了这个数据类型的最大范围,那么MySQL就将这个值转换为这个范围最大或最小值。如果字符串太长,MySQL就将多余的字符串截去。如果设置非空字段 为空,那么将这个字段设置为它们的默认值,数字的默认值是0,字符串的默认值是空串(不是null,是"")。
由于测试环境数据量比较小,所以测试的结果不明显,但关键是在开发过程中一定要注意字段类型与参数类型的一致性,避免在特定情况下造成数据在更新和删除过程中的额外开销。
5,测试大数据量的情况,过程如下:
第一步:创建数据表
- CREATETABLE`test008`(
- `id`int(10)unsignedNOTNULLAUTO_INCREMENT,
- `key1`int(10)NOTNULLDEFAULT'0',
- `key2`text,
- `key3`int(10)NOTNULLDEFAULT'0',
- PRIMARYKEY(`id`)
- )ENGINE=MyISAMAUTO_INCREMENT=0DEFAULTCHARSET=gbk
第二步:创建测试数据
创建1000000(100W)条记录,数据大小为2.07 GB (2,224,000,000 字节).
第三步:两条查询性能比较
- mysql>updatetest008setkey1=key1+'1'whereid='100000001';
- QueryOK,0rowsaffected(0.03sec)
- Rowsmatched:0Changed:0Warnings:0
- mysql>updatetest008setkey1=key1+'1'whereid=100000001;
- QueryOK,0rowsaffected(0.00sec)
- Rowsmatched:0Changed:0Warnings:0
第四步:创建索引
- mysql>altertabletest008addindexkey3(key3);
- QueryOK,1000000rowsaffected(5min54.33sec)
- Records:1000000Duplicates:0Warnings:0
第五步:测试不同的条件
- mysql>updatetest008setkey1=key1+'1'whereid='';
- QueryOK,0rowsaffected(0.01sec)
- Rowsmatched:0Changed:0Warnings:0
- mysql>updatetest008setkey1=key1+'1'whereid='12321232123';
- QueryOK,0rowsaffected(44.58sec)
- Rowsmatched:0Changed:0Warnings:0
- mysql>updatetest008setkey1=key1+'1'whereid=12321232123;
- QueryOK,0rowsaffected(0.00sec)
- Rowsmatched:0Changed:0Warnings:0
- --phpfensi.com
- mysql>updatetest008setkey1=key1+'1'whereid='test';
- QueryOK,0rowsaffected(0.00sec)
- Rowsmatched:0Changed:0Warnings:0
注意:上面测试中部分条件已经超出id字段的范围.