sql update更新不同字段类型性能分析
发布:smiling 来源: PHP粉丝网 添加日期:2014-10-15 10:15:37 浏览: 评论:0
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
- --phpfensi.com
- 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字段的范围.
Tags: update性能分析 update更新
相关文章
- ·MySql多表关联Update更新sql语句(2014-10-08)
- ·mysql的update更新语句详解(2014-10-14)
- ·sql update是否成功更新了数据方法(2014-10-14)
- ·sql插入数据已经存在,则执行update更新(2015-04-18)
推荐文章
热门文章
最新评论文章
- 写给考虑创业的年轻程序员(10)
- PHP新手上路(一)(7)
- 惹恼程序员的十件事(5)
- PHP邮件发送例子,已测试成功(5)
- 致初学者:PHP比ASP优秀的七个理由(4)
- PHP会被淘汰吗?(4)
- PHP新手上路(四)(4)
- 如何去学习PHP?(2)
- 简单入门级php分页代码(2)
- php中邮箱email 电话等格式的验证(2)