当前位置:Gxlcms > mysql > sqlupdate更新不同字段类型性能分析

sqlupdate更新不同字段类型性能分析

时间:2021-07-01 10:21:17 帮助过:80人阅读

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,创建测试数据

代码如下

$db = _connect("localhost","root","");
mysql__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字段的范围

人气教程排行