MySQL 原理与优化之Update 优化

前言:

谈到Update 语句大家可能不会陌生,很多情况下我们都会使用它来更新table中的记录。一般而言我们会使用innodb 的存储引擎,innodb引擎是基于行锁的,具体一点说是针对索引来加锁的(保证锁不能失效),并不是针对记录加锁,如果对于没有建立索引的字段进行过滤操作,从而执行update 的话,会导致表锁 。

今天就看看在使用innodb的时候如何使用update 语句。

已经存在course 表,其内容如下:

目前该表没有加任何的索引,默认情况下id 是表的索引。

接着让我们分别开启两个事务(两个客户端),分别执行下述指令。

开启第一个事务:

begin;
update course set name = 'Java' where id = 1;

开启另外一个事务

begin;
update course set name = 'Spark' where id = 4;

两个事务都可以执行,然后再分别执行两个事务的 commit 操作,就可以看到更新的结果。

两个事务能够并行执行的条件是id 是表course 的索引,可以由于update id 分别对应2 和4 ,只是针对这两行记录进行加锁。

接着让我们看看另外一个例子,依旧是开启两个事务,但是where 条件选择使用name ,而且name 没有作为course 表的索引。

开启一个事务:

begin;
update course set name = 'Vue.js' where name = 'PHP';

然后再执行另外一个事务

begin;
update course set name = 'SQLServer' where name = 'MySQL';

此时在执行第二个事务的update 语句的时候,会被阻塞。就是因为针对name 的过滤条件并不是course 的索引,此时的update 语句进行了锁表的操作,必须等第一个事务commit之后,释放掉表锁,第二个事务才能继续执行。

为了让两个事务能够并行执行,我们将name 加入到course 的索引中去。

create index index_name on course(name);
show index from course;

接着再执行刚才的两个事务,这两个事务分别对不同的记录进行更新,where 中的name条件不一样的情况下,也不会出现锁表的情况,这是因为将name 作为了course 的索引。

为了演示方便,这里我们将更新的条件调整一下:

开启一个事务:

begin;
update course set name = 'PHP' where name = 'Vue.js';

然后再执行另外一个事务

begin;
update course set name = 'MySQL' where name = 'SQLServer';

结果和我们预想的一样,两个事务可以并行执行,同时在commit之后能够看到结果。

总结一下, 如果使用innodb存储引擎,update 的时候存在where 条件的情况下,条件字段是索引的情况可以提升更新的效率,避免锁表的情况发生。

到此这篇关于MySQL 原理与优化之Update 优化的文章就介绍到这了,更多相关MySQL Update 优化内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL中一条update语句是如何执行的

    目录 前言 前期准备 SQL语句的执行过程

  • MySQL update set 和 and的区别

    问题描述 最近接到一个奇怪的咨询,update 语句执行没有报错,但是没有更新数据,具体有问题的语句类似于如下形式: update test.stu set cname = '0' and math = 90 and his = 80 where id = 100; 原因分析 直观上看,这个 update 语句的语法是有问题的,正常更新多列数据的语法应该是用逗号,类似于如下形式: update test.stu set cname = '0',math = 90,his = 80 where i

  • 教你如何让spark sql写mysql的时候支持update操作

    目录 1.首先了解背景 2.如何让sparkSQL支持update 3.改造源码前,需要了解整体的代码设计和执行流程 4.改造源码 如何让sparkSQL在对接mysql的时候,除了支持:Append.Overwrite.ErrorIfExists.Ignore:还要在支持update操作 1.首先了解背景 spark提供了一个枚举类,用来支撑对接数据源的操作模式 通过源码查看,很明显,spark是不支持update操作的 2.如何让sparkSQL支持update 关键的知识点就是: 我们正常

  • MySQL UPDATE 语句一个“经典”的坑

    目录 1.有问题的SQL语句 有人问,比如下图: 问题归纳起来就是:在MySQL里面update一条记录,语法都正确的,但记录并没有被更新... 刚遇到这个问题的时候,我拿到这条语句直接在测试库里面执行了一把,发现确实有问题,但和开发描述的还是 有区别 ,这里我用测试数据来模拟下: 1.有问题的SQL语句 执行之后的记录是: 可以看到,结果并不像这位开发同学说的"好像没有效果",实际上是有效果的: why? 看起来,语法是完全没有问题,翻了翻MySQL官方文档的update语法: 看到

  • MySQL数据库执行Update卡死问题的解决方法

    目录 问题分析 解决办法 过程复现和解决 扩展 总结 执行数据库更新update操作的时候数据库卡死了 问题分析 一般都是数据库事务未提交,导致update或者delete卡死. 解决办法 在执行完更新或删除后,记得将事务提交commit; 找到数据库客户端,执行commit操作. 如果还不行. 那么应该是数据库在执行数据操作失败 or 事务未提交 之后,将需要执行的sql语句锁死了. 过程复现和解决 通过如下命令查看数据库的自动提交状态 show variables like 'autocom

  • 实操MySQL+PostgreSQL批量插入更新insertOrUpdate

    目录 一.百度百科 1.MySQL 2.PostgreSQL 3.PostgreSQL相对于MySQL的优势 二.postgres中insertOrUpdate代码实例 1.创建user表 2.简单的方式实现 3.利用unnest函数实现 4.如果数据已存在,就就什么也不做 三.相关重点函数简介 1.unnest(anyarray) 四.userMapper.xml写法 五.MySQL中insertOrUpdate代码实例 1.建表语句 2.普通方式 3.ON DUPLICATE KEY UPD

  • mysql ON DUPLICATE KEY UPDATE重复插入时更新方式

    目录 mysql当插入重复时更新的方法 第一种方法 第二种方法 第三种方法 Mysql on duplicate key update 解决插入重复数据时更新值的问题以及其存在的问题 一.使用 二.存在问题 mysql当插入重复时更新的方法 第一种方法 示例一:插入多条记录 假设有一个主键为 client_id 的 clients 表,可以使用下面的语句: INSERT INTO clients (client_id,client_name,client_type) SELECT supplie

  • MySQL将select结果执行update的实例教程

    一.单表查询->更新 UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause] 二.多表联合查询->更新 UPDATE a INNER JOIN (SELECT yy FROM b) c ON a.id = c.id SET a.xx = c.yy [WHERE Clause] 上面的 INNER JOIN ,可以换为 LEFT JOIN . RIGHT JOIN 等联合查询. SET 后的字段必须为 a

  • Mysql事务中Update是否会锁表?

    两种情况: 1.带索引 2.不带索引 前提介绍: 方式:采用命令行的方式来模拟 1.mysq由于默认是开启自动提交事务,所以首先得查看自己当前的数据库是否开启了自动提交事务. 命令:select @@autocommit; 结果如下: +--------------+ | @@autocommit | +--------------+ | 0 | +--------------+ 如果是1,那么运行命令:set autocommit = 0;设置为不开启自动提交 2.当前的数据库表格式如下 tb

  • MySQL 原理与优化之Update 优化

    前言: 谈到Update 语句大家可能不会陌生,很多情况下我们都会使用它来更新table中的记录.一般而言我们会使用innodb 的存储引擎,innodb引擎是基于行锁的,具体一点说是针对索引来加锁的(保证锁不能失效),并不是针对记录加锁,如果对于没有建立索引的字段进行过滤操作,从而执行update 的话,会导致表锁 . 今天就看看在使用innodb的时候如何使用update 语句. 已经存在course 表,其内容如下: 目前该表没有加任何的索引,默认情况下id 是表的索引. 接着让我们分别开

  • MySQL 原理与优化之原数据锁的应用

    MySQL 中原数据锁是系统自动控制添加的,对于用户来说无需显示调用,当我们使用一张表的时候就会加上原数据锁. 原数据锁的作用是为了保护表原数据的一致性,如果在表上有活动事务的时候,不可以对元数据进行写入操作.也就是为了避免DML 和DDL 之间的冲突,保证读写的正确性. 说白了就是,在对数据表进行读写操作的时候,不能进行修改表结构的操作. 如上图所示,在执行select 操作的时候,MySQL 会自动加上shared_read 锁,在insert,update, delete 以及 selec

  • MySQL 原理与优化之Limit 查询优化

    假设有表tb_sku,其表结构如下: 表中大约有200w条记录,执行如下的sql 语句大约 4.36s 返回数据 select count(*) from tb_sku; 接着我们使用 对其进行分页查找: select * from tb_sku limit 0,10; limit 语句 其中0 代表起始位置,10 为每页返回的数据数量. 如上图所示,很快就返回了查询结果. 接着我们再使用SQL 语句 select * from tb_sku limit 10,10; 语句从记录位置10的位置开

  • MySQL 原理优化之Group By的优化技巧

    目录 今天来看看MySQL 中如何多Group By 语句进行优化的. 先创建tb_user 表如下: 通过show index from tb_user; 命令查看表,没有存在任何的索引. 执行如下代码,查看SQL 执行情况 explain select profession, count(*) from tb_user group by profession ; 发现返回结果中 type 为“ALL” ,Extra 返回“Using temporary” 说明没有使用索引. 于是,创建基于p

  • MySQL Index Condition Pushdown(ICP)性能优化方法实例

    一 概念介绍 Index Condition Pushdown (ICP)是MySQL 5.6 版本中的新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式. a 当关闭ICP时,index 仅仅是data access 的一种访问方式,存储引擎通过索引回表获取的数据会传递到MySQL Server 层进行where条件过滤. b 当打开ICP时,如果部分where条件能使用索引中的字段,MySQL Server 会把这部分下推到引擎层,可以利用index过滤的where条件在存储引擎层进行

  • Mysql数据库中数据表的优化、外键与三范式用法实例分析

    本文实例讲述了Mysql数据库中数据表的优化.外键与三范式用法.分享给大家供大家参考,具体如下: 数据表优化 将商品信息表进行优化 1.创建商品种类表: create table if not exists goods_cates( id int unsigned primary key auto_increment, name varchar(40) not null ); 2.将商品种类写入商品种类表中: 注意:插入另一个表的查询结果不需要加values insert into goods_

  • MySQL 的 21 个规范、优化最佳实践!

    前言 每一个好习惯都是一笔财富,本文分 SQL 后悔药,SQL 性能优化,SQL 规范优雅三个方向,分享写 SQL 的 21 个好习惯和最佳实践! 写完SQL先explain查看执行计划(SQL性能优化) 日常开发写 SQL 的时候,尽量养成这个好习惯呀:写完 SQL 后,用 explain 分析一下,尤其注意走不走索引. 操作 delete 或者 update 语句,加个 limit(SQL后悔药) 在执行删除或者更新语句,尽量加上 limit,以下面的这条 SQL 为例吧: delete f

  • 浅谈MySQL 亿级数据分页的优化

    背景 下班后愉快的坐在在回家的地铁上,心里想着周末的生活怎么安排. 突然电话响了起来,一看是我们的一个开发同学,顿时紧张了起来,本周的版本已经发布过了,这时候打电话一般来说是线上出问题了. 果然,沟通的情况是线上的一个查询数据的接口被疯狂的失去理智般的调用,这个操作直接导致线上的MySql集群被拖慢了. 好吧,这问题算是严重了,下了地铁匆匆赶到家,开电脑,跟同事把Pinpoint上的慢查询日志捞出来.看到一个很奇怪的查询,如下 POST domain/v1.0/module/method?ord

  • MySQL优化之连接优化

    上文MySQL优化之缓存优化 这篇文章中提到了一个很重要的概念,就是show variables是用来表示系统编译或者配置在my.cnf中的变量值.而show status则称之为状态值,显示的是当前服务实例运行所具有的状态信息,是一个动态改变的值.因此常用来观测当前MySQl的运行是否正常,如果不正常那么依靠调整静态参数来提高MySQL的性能.所以明白这两个概念的不同,是后面调优的基础. MySQL 连接优化 记得有一次在公司内部连接MySQL的时候,总是连接不上.找到DBA后,经过排查原因,

  • 实现MySQL定时批量检查表repair和优化表optimize table的shell脚本

    本文介绍mysql定时批量检查表repair和优化表optimize table的shell脚本,对于MySQL数据库的定期维护相当有用!如下所示: #!/bin/bash host_name=192.168.0.123 user_name=jincon.com user_pwd=jincon.com database=my_db_name need_optmize_table=true tables=$(mysql -h$host_name -u$user_name -p$user_pwd $

随机推荐