sql 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字段的范围

 

时间： 2024-08-01 14:48:04

sql update更新不同字段类型性能分析的相关文章

在SQL Server中修改字段类型和字段名称

  在SQL Server中修改字段类型和字段名称 --以下是完整的SQL执行语句 if exists(select * from syscolumns where id=object_id('数据表名称') and name='字段名') --判断该字段是否存在 begin ALTER TABLE 表明 ALTER COLUMN 字段名 VARCHAR(64);--更改类型 end GO if exists(select * from syscolumns where id=object_id

SQL中利用DMV进行数据库性能分析

相信朋友对SQL Server性能调优相关的知识或多或少都有一些了解.虽然说现在NOSQL相关的技术非常的火热,但是RMDB(关系型数据库)与NOSQL是并存的,并且适用在各种的项目中.在一般的企业级开发中,主要还是RMDB占据主导地位.并且在互联网项目中,也不是摒弃了RMDB,例如MySQL就在很多的互联网应用中发挥着作用.所以,对数据库的调优是个值得深入学习的课题.本系列文章,主要讲述与SQL Server相关的调优知识,希望能够为朋友们带来一些帮助. 本篇提纲如下: 传统SQL Serve

如何使用Sql Server 2008 Hierarchyid字段类型构造树型分类表

利用数据库表构造无限级分类,通常我们会采用下面这种方式 这种主子关系结构清晰,但查询时会有一些小问题:查询当前类别的所有子类及子类下的子孙类,会出现递归查询,而实际应用中这种查询是很常见的,比如我们要查询所有技术类文章信息: 解决这类问题在SqlServer2005出现之前通常是采用游标来操作,但熟悉数据库内部机制的人都知道使用游标带来的性能问题和其他问题是比较严重的 到了SqlServer2005下,可以选择用CTE来做递归查询,例如要查询所有技术类文章,就可使用这种方式,向上递归和向下递归基

SQL Server-聚焦EXISTS AND IN性能分析（十六）

前言 前面我们学习了NOT EXISTS和NOT IN的比较,当然少不了EXISTS和IN的比较,所以本节我们来学习EXISTS和IN的比较,简短的内容,深入的理解,Always to review the basics. 初步探讨EXISTS和IN 我们创建表Table1并且取出前面创建BigTable表中的六条数据并插入其中,同时有一条数据重复,如下: CREATE TABLE Table1 (IntCol UNIQUEIDENTIFIER) Insert into Table1 (IntC

“INSERT ON DUPLICATE KEY UPDATE”更新部分字段

之前的wormhole(我们开发的大规模批量数据传导工具,类似apache的sqoop,淘宝的datax)的mysql writer支持insert into 和 replace into两种方式入mysql表. 现在有需求要支持update部分字段操作,比如计算完的一堆商户ID(ShopID)和对应PageView总数(ViewTotal),需要更新到一张多字段的Shop表对应字段中(ShopID是主键),如果还是用insert into会抛duplicated key exception ,

sqlserver字段类型详解（转）

bit    整型 bit数据类型是整型,其值只能是0.1或空值.这种数据类型用于存储只有两种可能值的数据,如Yes 或No.True 或False .On 或Off.    注意:很省空间的一种数据类型,如果能够满足需求应该尽量多用.   tinyint   整型 tinyint 数据类型能存储从0到255 之间的整数.它在你只打算存储有限数目的数值时很有用.这种数据类型在数据库中占用1 个字节.  注意:如果bit类型太单调不能满足您的需求,您可以考虑用tinyint类型,因为这个类型相对也

Windows 8系统Windows Update更新硬件驱动图解

故障现象: 在Windows 8下使用Windows Update更新硬件驱动. 原因分析: 在一些特殊情况下,我们可以使用Windows自带的Update功能来对硬件更新驱动,当然,首先电脑需要正常连接互联网,我们可以来更新一些已知硬件的驱动更新,或者来更新未知硬件的驱动. 注意:如果条件允许,请优先使用硬件厂商提供的硬件驱动程序. 此情况也适用于Windows Server(2008.2012)等服务器系统来更新或安装驱动. 解决方案: 必备条件:计算机需要连接到互联网. 在传统桌面的情况下

Hibernate之update(1)——更新部分字段

  Hibernate 中如果直接使用Session.update(Object o),会把这个表中的所有字段更新一遍. 如果你没有对你需要更新的字段以外的字段赋值,那么这些字段会被置空. public class TeacherTest { @Test public void update() { Session session = HibernateUitl.getSessionFactory().getCurrentSession(); session.beginTransaction()

Hibernate更新某些字段的几种update方法

Hibernate 中如果直接使用 Session.update(Object o); 会把这个表中的所有字段更新一遍. 比如: view plaincopy to clipboardprint? public class TeacherTest { @Test public void update(){ Session session = HibernateUitl.getSessionFactory().getCurrentSession(); session.beginTransactio