`
cgs1999
  • 浏览: 536241 次
  • 性别: 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定时器实战
评论
发表评论

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

相关推荐

    Java访问MySQL数据库的测试程序

    在这个场景中，我们有一个名为"Java访问MySQL数据库的测试程序"的项目，它是一个小型的应用程序，主要用于验证和实践如何使用Java语言来连接并操作MySQL数据库，执行基本的CRUD（创建Create、读取Read、更新Update、...

    Jmeter的mysql-connector-java-5.1.6-bin.jar包，纯正靠谱，接口测试性能测试相关JAR包

    预编译的SQL语句，提高执行效率，常用于批量操作或动态参数设置。 5. `com.mysql.jdbc.ResultSet`: 存储SQL查询的结果，提供了遍历和提取数据的方法。 在JMeter中使用JDBC Request时，需要注意以下几点： - 设置...

    MYSql 基本配置及JDBC测试

    在本文中，我们将深入探讨MySQL的基本配置以及如何使用Java的JDBC（Java Database Connectivity）进行连接和测试。首先，让我们了解MySQL的基础知识。 1. **MySQL安装与配置**： - 下载：访问MySQL官网...

    mysql5.1中文手册.zip

    MySQL 5.1是MySQL数据库管理系统的一个重要版本，它在早期的5.x系列中提供了许多增强功能和优化，为开发者提供了更稳定、高效的数据存储和管理解决方案。本手册旨在详细介绍MySQL 5.1的各项特性和操作方法，帮助用户...

    mysql测试机1234

    在测试环境中，MySQL测试机1234可能用于验证数据库的性能、稳定性、兼容性和新功能。通过监控、压力测试和故障模拟，可以确保在实际部署前发现问题并解决。同时，测试环境也是开发人员调试应用代码和数据库交互的...

    MySQL 替换某字段内部分内容的UPDATE语句

    在执行大型更新之前，最好先备份数据，或者在测试环境中验证更新语句的效果。 此外，避免在`UPDATE`语句中使用可能导致全表扫描的操作，例如`SELECT * FROM`或没有索引的`WHERE`条件，这可能导致性能问题。如果`...

    elenmetManagement-mysql.rar

    开发者需要在config文件中配置数据库连接字符串，以确保程序能够正确连接到MySQL服务器并执行相应的CRUD（Create, Read, Update, Delete）操作。 5. 数据库连接字符串配置 数据库连接字符串是连接到数据库所必需的...

    mysql--5.1.30jar包

    9. **重复测试**：优化后，重新执行压力测试以验证改进的效果。持续迭代，直到达到满意的性能水平。 总之，使用 JMeter 对 MySQL 数据库进行压力测试是评估数据库在高并发场景下性能的有效方法。通过这种方式，你...

    MySQL 从一张表update字段到另外一张表中

    5. **测试和验证**: 在实际应用前，先在测试环境中验证更新操作，确认没有问题后再在生产环境中运行。 总结，MySQL的多表更新功能使得在不同表之间同步数据变得简单，但同时也需要谨慎处理，确保数据的准确性和一致...

    MySQL MySQLtest

    - 测试程序可能包含打开连接、创建`Statement`或`PreparedStatement`、执行SQL（如`SELECT`, `INSERT`, `UPDATE`, `DELETE`）、处理`ResultSet`以及关闭资源的代码。 - 遵循"连接池"最佳实践，可以提高应用性能，...

    mysql8.0官方文档离线版

    MySQL 8.0的并行查询优化了多线程执行，提高了大数据量查询的效率。 这份官方文档离线版是MySQL 8.0用户和开发者的宝贵资源，无论你是初学者还是经验丰富的DBA，都能从中获取所需的知识，解决实际问题。通过深入...

    MYSQL无法远程连接

    4. **测试连接**：在完成以上步骤后，建议使用MySQL客户端工具（如MySQL Workbench）进行远程连接测试，确保一切正常。 综上所述，通过上述方法可以有效解决MySQL无法远程连接的问题。不过，在实施这些方案时，还...

    mysql5.6安装包 mysql5.6官网下载的

    - **查询执行优化**：MySQL 5.6引入了新的优化器策略，如延迟关联、子查询缓存等，提高了复杂查询的执行效率。 - **InnoDB存储引擎改进**：InnoDB作为默认存储引擎，在5.6版本中得到了显著增强，支持更多的并发事务...

    mysql中文教程(pdf)C

    触发器则是在特定事件（如INSERT、UPDATE或DELETE）发生时自动执行的代码，常用于实现数据验证和维护。 视图是虚拟的表，基于一个或多个实际表，根据用户的需求提供定制化的数据视图。视图可以简化复杂的查询，保护...

    MySqlDll+MySqlHelper.cs+Test.cs（C#.Net2.0-4.7）

    MySQLDll、MySqlHelper.cs 和 Test.cs 这些文件名暗示了一个使用 C# 开发的...使用这样的工具可以帮助开发人员快速地构建与 MySQL 数据库交互的应用程序，无需深入了解底层的数据库连接和命令执行细节，提高开发效率。

    Mysql8.0.25自动化安装部署指南

    总之，MySQL 8.0.25 的自动化安装部署是一项涉及多步骤的任务，通过编写和执行适当的脚本，可以极大地简化这一过程，提高效率，并确保部署的一致性和可靠性。记得在每个步骤中都要考虑安全性和可维护性，确保你的...

    mysql代码-mysql 测试脚本

    在编写MySQL测试脚本前，你需要了解SQL语言的基本概念，包括数据类型（如INT、VARCHAR、DATE等）、DML语句（INSERT、UPDATE、DELETE）以及DDL语句（CREATE TABLE、ALTER TABLE、DROP TABLE）等。此外，JOIN操作、子...

    MYSQL中文参考手册(CHM)

    7. **触发器**：触发器在特定数据库事件（如INSERT, UPDATE, DELETE）发生时自动执行，常用于实现数据验证、审计跟踪等功能。 8. **事务处理**：事务是数据库操作的原子单元，包括提交（COMMIT）、回滚（ROLLBACK）...

    hibernate和MySQL的jar

    在描述中提到的“核心jar，亲测可用，跑demo是够用了”，这表明这些jar文件包含了运行Hibernate与MySQL交互所需的基本组件，并且已经有人验证过它们在演示或测试环境中可以正常工作。通常，这包括Hibernate的核心库...

    c++测试数据库

    下面是一个简单的示例，演示如何在C++中使用MySQL Connector/C++库连接到数据库并执行查询： ```cpp #include &lt;mysql_driver.h&gt; #include &lt;mysql_connection.h&gt; #include using namespace std; using namespace ...

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