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

最新推荐文章于 2022-06-30 00:19:36 发布
最新推荐文章于 2022-06-30 00:19:36 发布
阅读量1k 收藏
点赞数
分类专栏： 案例文档 其它 文章标签： mysql update效率 java
[size=x-large][b]1、引言[/b][/size]
某日，在讨论解决生产环境的问题时，一同事问说增加条件的Update语句效率是否更高？虽然我当时就有了自己的判断，但本着严谨的态度，我还是编写了测试代码，对MySQL中Update的执行效率进行测试及验证。

[size=x-large][b]2、解决思路[/b][/size]
1、根据生产环境的情况，模拟不同数据量的测试数据，分别模拟以下数据量的数据进行比较
[table]
|1000|2000|5000|
|10000|20000|50000|
|100000|200000|500000|
|1000000|2000000|5000000|
[/table]
2、测试对比增加条件和不加条件两种的Update语句的执行时间

[size=x-large][b]3、解决过程[/b][/size]
[size=large][b]3.1 数据表名[/b][/size]
不同数据量存放在不同的数据表中，表名规则体现数据量，代码如下 

// 根据数据量生成表名
private String getTableName(int count) {
	return "call_charge_" + count;
}


[size=large][b]3.2 删除数据表[/b][/size] 

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


[size=large][b]3.3 创建数据表[/b][/size] 

// 创建数据表
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);
}


[size=large][b]3.4 初始化数据[/b][/size] 

// 初始化数据
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));
		}
	}
}


[size=large][b]3.5 复制数据[/b][/size] 

// 复制数据
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);
	}
}


[size=large][b]3.6 公用测试代码[/b][/size] 

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));
}


[size=large][b]3.7 Junit测试[/b][/size] 

@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);
}


[size=large][b]3.8 测试结果[/b][/size] 

--------------------------
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...

整理后的测试结果如下所示
[table]
|数据量|加条件的执行时间(ms)|不加条件的执行时间(ms)|单元测试执行时间(s)|
|1000|30|135|5.103|
|2000|53|657|7.096|
|5000|122|1449|13.244|
|10000|225|852|9.859|
|20000|329|2025|26.353|
|50000|11848|14608|68.743|
|100000|7883|43920|102.544|
|200000|10867|95978|232.576|
|500000|14463|222890|560.074|
|1000000|26170|460927|1018.023|
|2000000|42915|944865|2052.403|
|5000000|90726|2322410|5145.207|
[/table]
[color=red][b]特别说明：[/b][/color]
测试结果，因机器及机器的工作状态而不同

[size=x-large][b]4、总结[/b][/size]
从测试结果的对比来看，增加条件的Update的执行效率明显比不加条件的Update的执行更短，效率也就更好

[size=x-large][b]5、附录[/b][/size]
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>
iteye_11946
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
sql怎么撤回update_第11问：不小心对一个大表进行了 update，怎么看进度？
weixin_39903176的博客
11-19 3317
问题有时候我们会不小心对一个大表进行了 update，比如说写错了 where 条件......此时，如果 kill 掉 update 线程，那回滚 undo log 需要不少时间。如果放置不管，也不知道 update 会持续多久。那我们能知道 update 的进度么？实验我们先创建一个测试数据库：快速创建一些数据：连续执行同样的 SQL 数次，就可以快速构造千万级别的数据：查看一下总的行数：我们...
mysql 批量更新及效率对比
09-16
比较集中mysql批量更新的方式，推荐一种效率最高的更新语句写法
Mysql语句执行效率检查和通过索引优化_实例
hit、run
11-23 522
#通过对表的主要字段,添加索引的方式.可以显著的提高查询效率 ALTER TABLE t_jt_contract_integral_detail ADD INDEX index_userId (userId); ALTER TABLE t_jt_contract_integral_detail ADD INDEX index_comp (userId, contractId, staffId);
mysql关于update的一道测试题
weixin_33836874的博客
01-29 139
2019独角兽企业重金招聘Python工程师标准>>> ...
mysql的insert与update效率提高上万倍的经历
热门推荐
cleanfield的专栏
05-12 4万+
1.任务的队列化，如果任务的执行会涉及到大范围的随机跳转操作，而这种跳转还会引起资源竞争，那么最好的办法就是将任务队列化，按照跳转最少，资源竞争最少的原则进行排序。 2.在任务队列化的基础上，map/reduce
MySQL 从一张表update字段到另外一张表中
09-10
5. **测试和验证**: 在实际应用前，先在测试环境中验证更新操作，确认没有问题后再在生产环境中运行。 总结，MySQL的多表更新功能使得在不同表之间同步数据变得简单，但同时也需要谨慎处理，确保数据的准确性和一致...
MySQL 替换某字段内部分内容的UPDATE语句
12-15
在执行大型更新之前，最好先备份数据，或者在测试环境中验证更新语句的效果。 此外，避免在`UPDATE`语句中使用可能导致全表扫描的操作，例如`SELECT * FROM`或没有索引的`WHERE`条件，这可能导致性能问题。如果`...
MySQL 一次执行多条语句的实现及常见问题
09-11
总的来说，MySQL的多语句执行功能提供了更高的效率，但也需要谨慎使用，确保正确处理结果集，并且时刻关注安全性。在实际应用中，结合适当的错误处理和安全措施，可以有效利用这一特性进行更高效的数据操作。
mysql测试机1234
07-18
在测试环境中，MySQL测试机1234可能用于验证数据库的性能、稳定性、兼容性和新功能。通过监控、压力测试和故障模拟，可以确保在实际部署前发现问题并解决。同时，测试环境也是开发人员调试应用代码和数据库交互的...
软件测试教程系列之MySQL数据库教学视频（中）
06-12
MySQL数据库是世界上最受欢迎的开源关系型数据库管理系统之一，尤其在软件开发和测试...通过学习，你将能够熟练运用SQL语句进行数据操作，理解数据库设计原则，并能运用到实际的软件测试工作中，提升工作效率和质量。
mysql最大执行时间_导入大型MySQL数据库时,最大执行时间超过300秒
weixin_42355399的博客
01-19 559
我正在尝试使用命令导入641 MBMySQL数据库：mysql -u root -p ddamiane_fakty < domenyin_damian_fakty.sql但是我收到了一个错误：ERROR 1064 (42000) at line 2351406: You have an error in your SQL syntax; check the manual that corre...
MySQL 5.7数据库参数优化
11-20 1049
连接相关参数 max_connections：允许客户端并发连接的最大数量，默认值是151，一般将该参数设置为300-1000 max_connect_errors：如果客户端尝试连接的错误数量超过这个参数设置的...
MySQL查询优化
Oh~Why not!
09-25 305
一张900w的数据表，怎么把原先要花费17s执行的SQL优化到300ms？ 有一张财务流水表，未分库分表，目前的数据量为9555695，分页查询使用到了limit，优化之前的查询耗时16 s 938 ms (execution: 16 s 831 ms, fetching: 107 ms)，按照下文的方式调整SQL后，耗时347 ms (execution: 163 ms, fetching: 184 ms)； 操作：查询条件放到子查询中，子查询只查主键ID，然后使用子查询中确定的主键关联查询其他的属性字段
MySQL优化：从十几秒优化到三百毫秒
qq_46540738的博客
06-30 1万+
mysql性能优化
提升update执行效率
y_18800130246的博客
08-20 2448
提升update执行效率 最近需要更新小3千万的数据表，更新值的表有7百多万，使用update更新语句效率很慢，执行计划的cost值高达256730753431。 经查找资料，尝试使用merge into，执行计划的cost值仅为134685，2分种执行完成，效率大大提升。 merge into语法： merge into table1 A using (select column1,column2,… from table2) B on (a.column1=b.column1 and …) when
MySQL和PGSQL对比
Hehuyi_In的博客
10-14 3698
【干货总结】:可能是史上最全的MySQL和PGSQL的对比材料 运维了MySQL和PGSQL已经有一段时间了，最近接到一个数据库选型需求，于是便开始收集资料整理了一下，然后就有了下面的对比表 关键词：PostgreSQL 11、MySQL5.7 比较版本：PostgreSQL 11VSMySQL5.7（innodb引擎） Oracle官方社区版版权情况：PostgreSQ...
MySQL面试题及执行步骤，关系型和非关系型数据库的区别及索引的使用
MySQL执行SQL语句的步骤依次为客户端请求、连接器验证用户身份和权限、查询缓存、分析器进行词法和语法分析、优化器选择最优执行方案、执行器执行SQL并获取数据返回。 3、MySQL使用索引的原因是什么？ MySQL使用...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值