mysql查看表执行效率_MySQL中Update的执行效率测试及验证

最新推荐文章于 2024-08-30 08:42:11 发布
最新推荐文章于 2024-08-30 08:42:11 发布
阅读量394 收藏
点赞数
文章标签： mysql查看表执行效率
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_35336727/article/details/113134303
版权
本文通过模拟不同数据量，对比测试了带有条件和无条件的MySQL Update语句执行效率。结果显示，增加条件的Update执行效率更高。测试包括1000至5000000条数据的场景，并提供了详细的测试代码和结果分析。
摘要由CSDN通过智能技术生成

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

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

0

顶

0

踩

分享到：

18e900b8666ce6f233d25ec02f95ee59.png

72dd548719f0ace4d5f9bca64e1d7715.png

2016-12-06 16:22

浏览 5825

分类:数据库

评论

Guosheng Hu
关注
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
MySQL中Update的执行效率测试及验证
常想一二
12-06 1063
[size=x-large][b]1、引言[/b][/size] 某日，在讨论解决生产环境的问题时，一同事问说增加条件的Update语句效率是否更高？虽然我当时就有了自己的判断，但本着严谨的态度，我还是编写了测试代码，对MySQL中Update的执行效率进行测试及验证。 [size=x-large][b]2、解决思路[/b][/size] 1、根据生产环境的情况，模拟不同数据量的测试数据...
【性能测试】MySQL数据库性能测试
寻小莫的博客
09-17 5215
文章目录一、数据库性能测试概述1.1 数据库性能测试的目的1.2 数据库的性能测试范围二、数据库架构2.1 数据库常用架构1. 一主多从2. 双机热备3. 主从同步工作原理2.2 数据库分库分表的设计方法拆分原因分库分表方案三、mysql专题性能测试3.1 概述3.2 mysql主流分支MariaDb3.3 Mysql数据库重点监控指标1. QPS2. TPS3. 线程连接数4. Query Cache5. 锁定状态6. 主从延时3.4 mysql慢查询1. 慢查询定义2. 慢查询开启3. 慢查询日志分析3
【MySQL】如何优化 SQL UPDATE 语句以提升性能
最新发布
linjiuxiansheng的博客
08-30 909
通过以上几个步骤，我们可以显著提升UPDATE语句的执行效率。具体来说，使用JOIN替代逗号分隔、为关键字段创建索引、分批更新数据，以及利用EXPLAIN分析执行计划，都是非常有效的优化手段。优化 SQL 是一个需要结合实际数据量和应用场景的工作，希望本文的思路能够帮助你在项目中更好地优化 SQL 语句，提高数据库的运行效率。
提升mysql大数据插入或者更新数据条目的执行效率
思考的蜗牛
03-15 8529
我在做项目的时候有这么个需求，我需要对每天产生的16000条数据进行批量的更新修改， 首先我们先在数据表里面查询新的数据条目是否已经在现有的表里存在，如果存在就执行update方法，如果不存在就直接执行insert方法 我的做法就是 for(循环16000条数据){ String sql = "select * from user where userid=? and uname=? an
查看mysql语句运行时间的2种方法
wangyueting415的博客
03-22 5366
网站运行很慢的时候，我就特别起知道为什么这么慢，所以我查啊查,数据库绝对是很重要的一部分，里面运行的sql是绝对不能放过的。平时做项目的时候，我也会注意sql语句的书写，写出一些高效的sql来，所以我会经常测试自己写的sql语句。我把我知道的二个方法，总结一下发出来。 一，show profiles 之类的语句来查看 1，查一下profile是不是打开了，默认是不打开的。 m
MySQL 从一张表update字段到另外一张表中
09-10
5. **测试和验证**: 在实际应用前，先在测试环境中验证更新操作，确认没有问题后再在生产环境中运行。 总结，MySQL的多表更新功能使得在不同表之间同步数据变得简单，但同时也需要谨慎处理，确保数据的准确性和一致...
Jsp SQL.rar_JSP+Mysql_JSP_mysql_java mysql 访问_jsp mysql_jsp sql
09-21
3. **SQL操作**：了解如何在JSP中编写和执行SQL语句，如SELECT、INSERT、UPDATE和DELETE。 4. **预编译语句与参数化查询**：使用PreparedStatement防止SQL注入，提高代码的可读性和安全性。 5. **结果集处理**：...
MySQL.zip_MYSQL_MySQL操作类_WADY_unhappyik5
09-24
在实际项目中，了解如何有效地使用MySQL操作类可以大大提高开发效率。例如，你可能需要创建一个用户管理功能，这涉及到添加新用户、验证登录和更新用户信息等操作。使用操作类，你可以快速地构建这些功能，而不必...
mysql_cn.rar_MYSQL_mysql 帮助文档
09-24
6. **触发器**：触发器在特定的数据库操作（如INSERT、UPDATE或DELETE）发生时自动执行，可用于实现复杂的业务逻辑和数据验证。 7. **存储过程和函数**：存储过程是一组预编译的SQL语句，可以封装复杂的操作，提高...
mysql 批量更新及效率对比
09-16
比较集中mysql批量更新的方式，推荐一种效率最高的更新语句写法
【MySQL】数据库性能测试工具（sysbench）
weixin_33895695的博客
04-26 591
[root@wallet01~]#yuminstall-ymakeautomakelibtoollibaio-develpkgconfigmysql-devel [root@wallet01~]#gitclonehttps://github.com/akopytov/sysbench.git InitializedemptyGitrepos...
如何提高mysql的update速度_如何提高数据库update更新的速度
weixin_33391709的博客
01-21 4298
欢迎进入Linux社区论坛，与200万技术人员互动交流 >>进入 如何提高数据库update更新的速度 不用不知道，一用吓一跳。。看下面这条SQL语句 [java] String sql="update cats set name_alias='"+rs.getString(1)+"'where name='"+rs.getString(1)+"'"; 如果数据欢迎进入Linux社区论...
mysql的upsert与查询插入更新性能、速度对比
linsonggao的博客
04-23 714
当实际工作中遇到这么一种需求：数据库没有这条数据，我需要新增，有这条数据我需要修改，或者某个字段的数量进行调整。那么我们就可以考虑使用mysql的upsert语句了。使用php语言对比mysql的upsert效率。
mysql循环更新数据_大批量更新数据mysql批量更新的四种方法
weixin_31981135的博客
01-19 4827
mysql 批量更新如果一条条去更新效率是相当的慢, 循环一条一条的更新记录,一条记录update一次，这样性能很差，也很容易造成阻塞。mysql 批量更新共有以下四种办法1、.replace into 批量更新replace into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y');2、insert into ...on duplicate ...
如何精确测量程序运行时间
Julia_li_2007的专栏
01-15 2263
 前言Contents1 前言 2 进程调度和模式切换 3 方法一：间隔计数 4 方法二：周期计数 5 方法三：gettimeofday函数计时 6 总结 对于一个嵌入式程序员来说，“我的程序到底运行多快”，是我们最为关心的问题，因为速度，实时性，永远是嵌入式设备性能优化的基本立足点之一。 可惜的是，我们平时常用的测试运行时间的方法，
sql怎么撤回update_第11问：不小心对一个大表进行了 update，怎么看进度？
weixin_39903176的博客
11-19 3355
问题有时候我们会不小心对一个大表进行了 update，比如说写错了 where 条件......此时，如果 kill 掉 update 线程，那回滚 undo log 需要不少时间。如果放置不管，也不知道 update 会持续多久。那我们能知道 update 的进度么？实验我们先创建一个测试数据库：快速创建一些数据：连续执行同样的 SQL 数次，就可以快速构造千万级别的数据：查看一下总的行数：我们...
提升update执行效率
y_18800130246的博客
08-20 2507
提升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表分析与优化技巧总结
这些统计信息随后被MySQL查询优化器用来确定执行查询时的最佳表联合顺序，从而提高查询效率。例如，当执行涉及多个表的联接操作时，优化器会根据这些统计信息选择最佳的执行计划。对于MyISAM表，`ANALYZE TABLE`相当...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值