`
cgs1999
  • 浏览: 530172 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

MySQL中Update的执行效率测试及验证

mysqlupdate效率java 
阅读更多
1、引言
某日，在讨论解决生产环境的问题时，一同事问说增加条件的Update语句效率是否更高？虽然我当时就有了自己的判断，但本着严谨的态度，我还是编写了测试代码，对MySQL中Update的执行效率进行测试及验证。

2、解决思路
1、根据生产环境的情况，模拟不同数据量的测试数据，分别模拟以下数据量的数据进行比较
100020005000
100002000050000
100000200000500000
100000020000005000000

2、测试对比增加条件和不加条件两种的Update语句的执行时间

3、解决过程
3.1 数据表名
不同数据量存放在不同的数据表中，表名规则体现数据量，代码如下 
// 根据数据量生成表名
private String getTableName(int count) {
	return "call_charge_" + count;
}


3.2 删除数据表 
// 删除数据表
private void dropTable(String tableName) {
	String dropTableSql = "DROP TABLE IF EXISTS `" + tableName + "` ";
	jdbcTemplate.execute(dropTableSql);
}


3.3 创建数据表 
// 创建数据表
private void createTable(String tableName) {
	String createTableSql = "CREATE TABLE `" + tableName + "` (";
	createTableSql += "`caller_flag` int(11) NOT NULL,";
	createTableSql += "`call_type` int(11) NOT NULL,";
	createTableSql += "`e164no` char(64) NOT NULL,";
	createTableSql += "`conf_begin_time` int(11) NOT NULL,";
	createTableSql += "`begin_time` int(11) NOT NULL,";
	createTableSql += "`end_time` int(11) NOT NULL,";
	createTableSql += "`correct_begin_time` int(11) NOT NULL,";
	createTableSql += "`correct_end_time` int(11) NOT NULL,";
	createTableSql += "`correct_begin_time_string` varchar(32) NOT NULL,";
	createTableSql += "`correct_end_time_string` varchar(32) NOT NULL,";
	createTableSql += "`opposite_call_type` int(11) NOT NULL,";
	createTableSql += "`opposite_e164no` char(64) NOT NULL,";
	createTableSql += "`opposite_conf_begin_time` int(11) NOT NULL,";
	createTableSql += "`company_guid` varchar(32) NOT NULL,";
	createTableSql += "`band_width` int(11) NOT NULL,";
	createTableSql += "`roam_flag` int(11) NOT NULL,";
	createTableSql += "`out_flag` int(11) NOT NULL,";
	createTableSql += "`incoming_flag` int(11) NOT NULL,";
	createTableSql += "`csu_guid` varchar(32) NOT NULL,";
	createTableSql += "KEY `correct_begin_time` (`correct_begin_time`),";
	createTableSql += "KEY `company_guid` (`company_guid`)";
	createTableSql += ") ENGINE=InnoDB DEFAULT CHARSET=utf8";
	jdbcTemplate.execute(createTableSql);
}


3.4 初始化数据 
// 初始化数据
private void initData(String tableName, int count) {
	String insertSql = "insert into `" + tableName + "` ";
	insertSql += "(`caller_flag`, `call_type`, `e164no`, `conf_begin_time`, `begin_time`, `end_time`, ";
	insertSql += "`correct_begin_time`, `correct_end_time`, `correct_begin_time_string`, `correct_end_time_string`, ";
	insertSql += "`opposite_call_type`, `opposite_e164no`, `opposite_conf_begin_time`, `company_guid`, ";
	insertSql += "`band_width`, `roam_flag`, `out_flag`, `incoming_flag`, `csu_guid`) ";
	insertSql += "values('1','2','051211#999548','1420075106','1420075107','1420075188','1420066067','1420066148',";
	insertSql += "'2015-01-01 06:47:47','2015-01-01 06:49:08','1','0512114880046','2147483647','','960','0',";
	insertSql += "'0','0','%s')";

	for (int i = 0; i < count; i++) {
		if (i % 3 == 0) {
			jdbcTemplate.execute(String.format(insertSql, CSU_GUID1));
		} else {
			jdbcTemplate.execute(String.format(insertSql, CSU_GUID2));
		}
	}
}


3.5 复制数据 
// 复制数据
private void copyData(String templateTableName, int count, int step) {
	String createSql = "CREATE TABLE `" + getTableName(count) + "` SELECT * FROM " + templateTableName;
	jdbcTemplate.execute(createSql);

	String copySql = "INSERT INTO `" + getTableName(count) + "` SELECT * FROM " + templateTableName;
	for (int i = step; i < count; i += step) {
		jdbcTemplate.update(copySql);
	}
}


3.6 公用测试代码 
private String CSU_GUID1 = "10103000000006000000000000000001";
private String CSU_GUID2 = "10103000000006000000000000000002";
private String CSU_GUID3 = "10103000000006000000000000000003";

// 公用单元测试方法
private void doTest(String templateTableName, int count, int step) {
	dropTable(getTableName(count));
	copyData(templateTableName, count, step);
	doTest(count);
}

// 测试加条件和不加条件的update
private void doTest(int count) {
	long startTime = System.currentTimeMillis();
	String updateSql = "update " + getTableName(count) + " set csu_guid = '" + CSU_GUID3 + "' where csu_guid != '"
			+ CSU_GUID1 + "'";
	jdbcTemplate.update(updateSql);
	long endTime = System.currentTimeMillis();
	System.out.println(String.format("测试数据[%d]过滤Update=%d", count, endTime - startTime));

	startTime = System.currentTimeMillis();
	updateSql = "update " + getTableName(count) + " set csu_guid = '" + CSU_GUID2 + "'";
	jdbcTemplate.update(updateSql);
	endTime = System.currentTimeMillis();
	System.out.println(String.format("测试数据[%d]直接Update=%d", count, endTime - startTime));
}


3.7 Junit测试 
@Test
public void prepareData() {
	dropTable("call_charge_data");
	createTable("call_charge_data");
	initData("call_charge_data", 100);
}

@Test
public void create1000() {
	doTest("call_charge_data", 1000, 100);
}

@Test
public void create2000() {
	doTest(getTableName(1000), 2000, 1000);
}

@Test
public void create5000() {
	doTest(getTableName(1000), 5000, 1000);
}

@Test
public void create10000() {
	doTest(getTableName(1000), 10000, 1000);
}

@Test
public void create20000() {
	doTest(getTableName(10000), 20000, 10000);
}

@Test
public void create50000() {
	doTest(getTableName(10000), 50000, 10000);
}

@Test
public void create100000() {
	doTest(getTableName(10000), 100000, 10000);
}

@Test
public void create200000() {
	doTest(getTableName(100000), 200000, 100000);
}

@Test
public void create500000() {
	doTest(getTableName(100000), 500000, 100000);
}

@Test
public void create1000000() {
	doTest(getTableName(100000), 1000000, 100000);
}

@Test
public void create2000000() {
	doTest(getTableName(1000000), 2000000, 1000000);
}

@Test
public void create5000000() {
	doTest(getTableName(1000000), 5000000, 1000000);
}


3.8 测试结果 
--------------------------
Test[com.hero.test.update.TestMysqlUpdate.create1000] start...
测试数据[1000]过滤Update=30
测试数据[1000]直接Update=135
Test[com.hero.test.update.TestMysqlUpdate.create1000] end...
--------------------------
Test[com.hero.test.update.TestMysqlUpdate.create10000] start...
测试数据[10000]过滤Update=225
测试数据[10000]直接Update=852
Test[com.hero.test.update.TestMysqlUpdate.create10000] end...
--------------------------
Test[com.hero.test.update.TestMysqlUpdate.create100000] start...
测试数据[100000]过滤Update=7883
测试数据[100000]直接Update=43920
Test[com.hero.test.update.TestMysqlUpdate.create100000] end...
--------------------------
Test[com.hero.test.update.TestMysqlUpdate.create1000000] start...
测试数据[1000000]过滤Update=26170
测试数据[1000000]直接Update=460927
Test[com.hero.test.update.TestMysqlUpdate.create1000000] end...
--------------------------
Test[com.hero.test.update.TestMysqlUpdate.create2000] start...
测试数据[2000]过滤Update=53
测试数据[2000]直接Update=657
Test[com.hero.test.update.TestMysqlUpdate.create2000] end...
--------------------------
Test[com.hero.test.update.TestMysqlUpdate.create20000] start...
测试数据[20000]过滤Update=329
测试数据[20000]直接Update=2025
Test[com.hero.test.update.TestMysqlUpdate.create20000] end...
--------------------------
Test[com.hero.test.update.TestMysqlUpdate.create200000] start...
测试数据[200000]过滤Update=10867
测试数据[200000]直接Update=95978
Test[com.hero.test.update.TestMysqlUpdate.create200000] end...
--------------------------
Test[com.hero.test.update.TestMysqlUpdate.create2000000] start...
测试数据[2000000]过滤Update=42915
测试数据[2000000]直接Update=944865
Test[com.hero.test.update.TestMysqlUpdate.create2000000] end...
--------------------------
Test[com.hero.test.update.TestMysqlUpdate.create5000] start...
测试数据[5000]过滤Update=122
测试数据[5000]直接Update=1449
Test[com.hero.test.update.TestMysqlUpdate.create5000] end...
--------------------------
Test[com.hero.test.update.TestMysqlUpdate.create50000] start...
测试数据[50000]过滤Update=11848
测试数据[50000]直接Update=14608
Test[com.hero.test.update.TestMysqlUpdate.create50000] end...
--------------------------
Test[com.hero.test.update.TestMysqlUpdate.create500000] start...
测试数据[500000]过滤Update=14463
测试数据[500000]直接Update=222890
Test[com.hero.test.update.TestMysqlUpdate.create500000] end...
--------------------------
Test[com.hero.test.update.TestMysqlUpdate.create5000000] start...
测试数据[5000000]过滤Update=90726
测试数据[5000000]直接Update=2322410
Test[com.hero.test.update.TestMysqlUpdate.create5000000] end...
--------------------------
Test[com.hero.test.update.TestMysqlUpdate.prepareData] start...
Test[com.hero.test.update.TestMysqlUpdate.prepareData] end...

整理后的测试结果如下所示
数据量加条件的执行时间(ms)不加条件的执行时间(ms)单元测试执行时间(s)
1000301355.103
2000536577.096
5000122144913.244
100002258529.859
20000329202526.353
50000118481460868.743
100000788343920102.544
2000001086795978232.576
50000014463222890560.074
1000000261704609271018.023
2000000429159448652052.403
50000009072623224105145.207

特别说明：
测试结果，因机器及机器的工作状态而不同

4、总结
从测试结果的对比来看，增加条件的Update的执行效率明显比不加条件的Update的执行更短，效率也就更好

5、附录
1、测试基类BaseTest 
import org.junit.After;
import org.junit.Before;
import org.junit.Rule;
import org.junit.rules.TestName;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

/**
 * Junit测试基类
 * @author chengesheng@gmail.com
 * @date 2014-7-28 下午5:27:34
 * @version 1.0.0
 */
// 使用@RunWith(SpringJUnit4ClassRunner.class),才能使测试运行于Spring测试环境
@RunWith(SpringJUnit4ClassRunner.class)
// @ContextConfiguration 注解有以下两个常用的属性：
// locations：可以通过该属性手工指定 Spring 配置文件所在的位置,可以指定一个或多个 Spring 配置文件
// inheritLocations：是否要继承父测试类的 Spring 配置文件，默认为 true
// 如果只有一个配置文件就直接写locations=“配置文件路径+名”
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class BaseTest {

	@Rule
	public TestName name = new TestName();

	@Before
	public void before() {
		System.out.println("--------------------------");
		System.out.println("Test[" + getClassName() + "." + getMethodName() + "] start...");
	}

	@After
	public void after() {
		System.out.println("Test[" + getClassName() + "." + getMethodName() + "] end...");
	}

	private String getClassName() {
		return getClass().getName();
	}

	private String getMethodName() {
		return name.getMethodName();
	}
}


2、Spring配置文件applicationContext.xml 
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xsi:schemaLocation="http://www.springframework.org/schema/beans 
                     http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

	<!-- 配置数据源 -->
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8" />
		<property name="username" value="root" />
		<property name="password" value="" />
	</bean>

	<!-- 配置Spring  JdbcTemplate -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource">
			<ref bean="dataSource" />
		</property>
	</bean>
</beans>
0
顶
0
踩
分享到：
| MySQL定时器实战
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    Mysql 进阶修行学习

    索引：结构、Btree、索引分类、索引语法、性能分析、执行频次、慢查询日志、show profiles、explain、使用规则（验证索引效率、最左前缀法则、索引失效情况、SQL提示、覆盖索引&回表查询、前缀索引、单列&联合索引、...

    PHP开发实战1200例(第1卷).(清华出版.潘凯华.刘中华).part1

    实例005 测试XAMPP是否安装成功 11 实例006 XAMPP应用技巧 12 实例007 第2个PHP程序 13 1.3 IIS+PHP+MySQL——独立搭建PHP 开发环境 14 实例008 安装PHP 14 实例009 安装MySQL 15 实例010 安装IIS 21 实例011 第3个...

    PHP开发实战1200例(第1卷).(清华出版.潘凯华.刘中华).part2

    实例005 测试XAMPP是否安装成功 11 实例006 XAMPP应用技巧 12 实例007 第2个PHP程序 13 1.3 IIS+PHP+MySQL——独立搭建PHP 开发环境 14 实例008 安装PHP 14 实例009 安装MySQL 15 实例010 安装IIS 21 实例011 第3个...

    P2P视频技术源码(VC)

    客户端发送消息时, 使用了用于验证的7个字节中的前3字节, 用这3字节来标识Session 的下标, 这样就避免了查询开销. 2) 使用maxid来减少搜索次数. 在TCP中没有使用Hash, 使用了maxid这一项, 用来记录Session中最大的id...

    P2P视频播放器 详细制作实例

    客户端发送消息时, 使用了用于验证的7个字节中的前3字节, 用这3字节来标识Session 的下标, 这样就避免了查询开销. 2) 使用maxid来减少搜索次数. 在TCP中没有使用Hash, 使用了maxid这一项, 用来记录Session中最大的...

    SQL培训第一期

    用于操作关系型数据库对象内部的数据，insert、update、delete。 1.2.3 数据定义语言（DDL） 用来建立及定义数据表、字段以及索引等数据库结构，create、alter、drop 。 1.2.4 数据控制语言（DCL） 用于控制对数据库...

    asp.net知识库

    如何在DotNet 2的登录组件中检索用户的锁定状态及解锁？ ASP.NET 2.0, 想说爱你不容易 SqlDataSource WEB控件：当DeleteCommandType= 遭遇 ASP.NET 2.0 只读 TextBox 回发后信息丢失的 bug asp.net2.0：扩展...

    基于J2EE框架的个人博客系统项目毕业设计论文(源码和论文）

    因此，在具体设计实现该博客网站时，主要考虑了主流博客网站的几个主要功能:(1)博客的注册、登录验证功能(2) 网络用户通过关键字搜索博文功能(3) 最热门博客页面推荐浏览(4) 文章详细内容及相关评论显示(5) 博客...

    Java开发实战1200例(第1卷).(清华出版.李钟尉.陈丹丹).part3

    实例005 下载并安装JRE执行环境 8 实例006 编程输出星号组成的等腰三角形 9 1.2 开发工具 11 实例007 下载最新的Eclipse 11 实例008 为最新的Eclipse安装中文语言包 12 实例009 活用Eclipse的工作空间 14 实例010 在...

Magicbox
Global site tag (gtag.js) - Google Analytics