MySql 知识点之事务、索引、锁原理与用法解析

本文实例讲述了MySql 知识点之事务、索引、锁原理与用法。分享给大家供大家参考，具体如下：

事务

  • 事务概念

事务就是一组原子性的SQL查询，或者说一个独立的工作单元。如果数据库引擎执行一组操作语句，那么久执行所有的操作，如果其中有任何一条崩溃或其他原因无法执行，所有语句将不会执行。也就是说事务内的语句，要么全部执行成功，要么全部执行失败。

  • 事务特性ACID

    • 原子性(atomicity)

    一个事务被视为最小工作单元，不可拆分，整个事务所有的操作要么全部提交成功，要么全部失败回滚，不可只执行部分。

    • 一致性(consistency)

    数据库从一个一致性的状态转换到另外一个一致性的状态。数据库某个状态下符合所有的完整性约束的状态。

    • 隔离性(isolation)

    通常来说，一个事务所做的修改在最终提交前，对其他事务是不可见的。此时应该保证各个事务要进行隔离，事务之间不可相互干扰。

    • 持久性(durability)

    一旦事务提交，所有的修改会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

  • 事务的隔离级别

    • READ UNCOMMITTED(未提交读)

    事务中的修改，即使没有提交，对其他事务也是可见的，事务可以读取未提交的数据，造成脏读，也会造成不可重复。

    • READ COMMITTED(提交读)

    大多数数据库的默认级别是READ COMMITTED（MySQL默认REPEATABLE READ），该级别事务解决了脏读，但是会出现不可重复读，因为两次执行同样的查询，查询结果不一样。

    • REPEATABLE READ(可重复读)

    该级别解决了脏读，保证可重复读，但是理论上，可重复读隔离级别还是无法解决幻读，所谓幻读，指的是党某个事物在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录。InnoDB和XtraDB存储引擎通过多版本并发控制MVVC解决了幻读的问题。

    • SERIALIZABLE(可串行化)

    可串行化是隔离最高级，它强制了事务串行执行，完全避免了幻读，简单来说SERIALIZABLE会在读取的每一行加锁，所以会导致大量的等待超时和锁争用的问题，实际开发中很少使用。

索引

  • 索引概念

索引是存储引擎用户快速找到记录的一种数据结构，举例

SELECT userName FROM user WHERE userId = 1;

如果在userId列上加上索引，则MySQL将使用该索引找到userId的行，也就是说，MySQL先在索引上按值进行查找，然后返回所有包含该值的数据行。

  • 索引方式

    • B-Tree索引

    使用B-Tree数据结构来存储数据，大多MySQL引擎都支持该索引。B-Tree索引可以加快访问数据的速度，因为B-Tree对索引列顺序组织存储，范围查找快。

    • hash索引

    哈希索引基本哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码值较小。哈希索引将所有的哈希码存储在索引中，同时在哈斯表中保存指向每个数据行的指针。MySQL中只有Memory引擎显示支持哈希索引。

  • 索引类型

    • 普通索引

    主要任务加快对数据的访问

    • 唯一索引

    普通索引是允许数据重复的，如果确定了某列数据不会重复，则可创建唯一索引，唯一索引有两个好处，索引更有效：插入新数据，如果重复，MySQL拒绝插入。

    • 主键索引

    主键本身默认创建索引

    • 全文索引

    文本字段上的普通索引只能加快对出现在字段内最前面的字符串进行的检索操作，如果字段里存放的是由几个或者多个单词构成的大段文字，普通索引就不行了，这种场合用全文索引比较合适

    查询效率：唯一索引>自增主键>主键

    插入：主键>自增主键>唯一索引

锁

在这里我们主要讨论下行级锁

  • 表级

引擎MyISAM，可以理解为锁整张表，可以同时读，不可以同时写。在锁定期间，其它进程无法对该表进行写操作，如果是写锁，则其它进程则不允许读。

  • 行级

引擎INNODB，单独一行记录加锁，可以同时读，不可同时写。行级锁开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

  • InnoDB锁行

由于InnoDB预设是Row-Level Lock，所以只有[明确]的指定主键，MySQL才会执行Row lock，否则MySQL将会执行Table Lock

例1：（明确指定主键，并且有此记录，Row Lock）

SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

例2: (明确指定主键，若查无此记录，无lock)

SELECT * FROM products WHERE id='-1' FOR UPDATE;

例3: (无主键，table lock)

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

例4: (主键不明确，table lock)

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

例5: (主键不明确，table lock)

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注1: FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。
注2: 要测试锁定的状况，可以利用MySQL的Command Mode ，开二个视窗来做测试。

更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL索引操作技巧汇总》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

希望本文所述对大家MySQL数据库计有所帮助。

赞 (0)

相关推荐

  • MySQL存储过程例子(包含事务，输出参数，嵌套调用)

    drop procedure if exists pro_rep_shadow_rs; delimiter | ---------------------------------- -- rep_shadow_rs -- 用来处理信息的增加,更新和删除 -- 每次只更新上次以来没有做过的数据 -- 根据不同的标志位 -- 需要一个输出的参数, -- 如果返回为0,则调用失败,事务回滚 -- 如果返回为1,调用成功,事务提交 -- -- 测试方法 -- call pro_rep_shadow_rs

  • 由不同的索引更新解决MySQL死锁套路

    由不同的索引更新解决MySQL死锁套路

    前几篇文章介绍了用源码的方式来调试锁相关的信息,这里同样用这个工具来解决一个线上实际的死锁案例,也是我们介绍的第一个两条 SQL 就造成死锁的情况.因为线上的表结构比较复杂,做了一些简化以后如下 CREATE TABLE `t3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` varchar(5), `b` varchar(5), PRIMARY KEY (`id`), UNIQUE KEY `uk_a` (`a`), KEY `idx_b` (`b`)

  • MySQL死锁套路之唯一索引下批量插入顺序不一致

    MySQL死锁套路之唯一索引下批量插入顺序不一致

    前言 死锁的本质是资源竞争,批量插入如果顺序不一致很容易导致死锁,我们来分析一下这个情况.为了方便演示,把批量插入改写为了多条 insert. 先来做几个小实验,简化的表结构如下 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` varchar(5), `b` varchar(5), PRIMARY KEY (`id`), UNIQUE KEY `uk_name` (`a`,`b`) ); 实验1: 在记录不存在的情况下,

  • 当Mysql行锁遇到复合主键与多列索引详解

    背景 今天在配合其他项目组做系统压测,过程中出现了偶发的死锁问题.分析代码后发现有复合主键的update情况,更新复合主键表时只使用了一个字段更新,同时在事务内又有对该表的insert操作,结果出现了偶发的死锁问题. 比如表t_lock_test中有两个主键都为primary key(a,b) ,但是更新时却通过update t_lock_test .. where a = ? ,然后该事务内又有insert into t_lock_test values(...) InnoDB中的锁算法是Ne

  • Mysql中的事务是什么如何使用

    什么是事务? 事务是逻辑上的一组操作,组成这组操作的各个单元,要不全都成功要不全都失败,这个特性就是事务 注意:mysql数据支持事务,但是要求必须是innoDB存储引擎 解决这个问题: mysql的事务解决这个问题,因为mysql的事务特性,要求这组操作,要不全都成功,要不全都失败,这样就避免了某个操作成功某个操作失败.利于数据的安全 如何使用: (1)在执行sql语句之前,我们要开启事务 start transaction; (2)正常执行我们的sql语句 (3)当sql语句执行完毕,存在两

  • MySQL 死锁套路：唯一索引 S 锁与 X 锁的爱恨情仇

    在初学者从源码理解MySQL死锁问题中介绍了使用调试 MySQL  源码的方式来查看死锁的过程,这篇文章来讲讲一个常见的案例. 毫不夸张的说,有一半以上的死锁问题由唯一索引贡献,后面介绍的很多死锁的问题都跟唯一索引有关.这次我们讲一段唯一索引 S 锁与 X 锁的爱恨情仇 我们来看一个简化过的例子 # 构造数据 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10), `level` int(11),

  • mysql 锁表锁行语句分享(MySQL事务处理)

    复制代码 代码如下: mysql_query("set autocommit=0"); $list_one = $db->fetch_first("select * from prizes where id = ".$id." FOR UPDATE"); $db->query("DELETE from prizes WHERE id =".$list_one['id']); mysql_query("co

  • MySql的事务使用与示例详解

    在MySQL中,事务就是一个逻辑工作单元的一系列步骤.事务是用来保证数据操作的安全性. 事务的特征: 1.Atomicity(原子性) 2.Consistency(稳定性,一致性) 3.Isolation(隔离性) 4.Durability(可靠性) 注:事务只针对对数据数据产生影响的语句有效. show engines //查看mysql锁支持的数据引擎 MyISAM不支持事物,InnoDB支持事物 默认情况下,MySQL将以自动提交模式运行,这意味着没一条小命令都将当做一个只有一条命令的事物

  • mysql实现事务的提交和回滚实例

    mysql创建存储过程的官方语法为: 复制代码 代码如下: START TRANSACTION | BEGIN [WORK]COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]SET AUTOCOMMIT = {0 | 1} 我这里要说明的mysql事务处理多个SQL语句的回滚情况.比如说在一个存储过程中启动一个事务,这个事务同时往三个表中插入数据,每插完一张表需要

  • MySQL数据库优化之索引实现原理与用法分析

    MySQL数据库优化之索引实现原理与用法分析

    本文实例讲述了MySQL数据库优化之索引实现原理与用法.分享给大家供大家参考,具体如下: 索引 什么是索引 索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存.如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录.表里面的记录数量越多,这个操作的代价就越高.如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置.如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍.

  • MySql 知识点之事务、索引、锁原理与用法解析

    本文实例讲述了MySql 知识点之事务.索引.锁原理与用法.分享给大家供大家参考,具体如下: 事务 事务概念 事务就是一组原子性的SQL查询,或者说一个独立的工作单元.如果数据库引擎执行一组操作语句,那么久执行所有的操作,如果其中有任何一条崩溃或其他原因无法执行,所有语句将不会执行.也就是说事务内的语句,要么全部执行成功,要么全部执行失败. 事务特性ACID 原子性(atomicity) 一个事务被视为最小工作单元,不可拆分,整个事务所有的操作要么全部提交成功,要么全部失败回滚,不可只执行部分.

  • mysql滑动聚合/年初至今聚合原理与用法实例分析

    mysql滑动聚合/年初至今聚合原理与用法实例分析

    本文实例讲述了mysql滑动聚合/年初至今聚合原理与用法.分享给大家供大家参考,具体如下: 滑动聚合是按顺序对滑动窗口范围内的数据进行聚合的操作.下累积聚合不同,滑动聚合并不是统计开始计算的位置到当前位置的数据. 这里以统计最近三个月中员工第月订单情况为例来介绍滑动聚合. 滑动聚合和累积聚合解决方案的主要区别在于连接的条件不同.滑动聚合条件不再是b.ordermonth <= a.ordermonth,而应该是b.ordermonth大于前三个月的月份,并且小于当前月份.因此滑动聚合的解决方案的

  • Java图形界面Swing原理及用法解析

    这篇文章主要介绍了Java图形界面Swing原理及用法解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 JButton组件 布局管理器 FlowLayout 流式布局 BorderLayout 方位布局 GridLayout 表格布局 绝对布局 JLable 组件 文本框组件 JPanel轻量级容器 创建事件监听类 (更换监听类实现监听) 窗口监听适配器 都可使用匿名类实现监听 每个监听方法都可以返回一个Event对象来返回监听值 以上就是本

  • Python partial函数原理及用法解析

    这篇文章主要介绍了Python partial函数原理及用法解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 介绍 partial其实是Python模块functools中定义的一个函数,当我们需要经常调用某个函数时,但是其中某些参数是已知的固定值,这样可能会让代码显得冗余,这个时候就可以考虑使用partial函数. 使用 假设我们要做二进制转十进制 int('1000000', base=2) # 64 int('1010101', bas

  • Python openpyxl模块原理及用法解析

    这篇文章主要介绍了Python openpyxl模块原理及用法解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 此模块不是Python内置的模块需要安装,安装方法如下 pip install openpyxl 注意: 此模块只支持offce 2010,即是电子表格后缀是*.xlsx 1.openpyxl模块常用函数 import openpyxl wb = openpyxl.load_workbook('example.xlsx') ####

  • SpringCloud断路器Hystrix原理及用法解析

    这篇文章主要介绍了SpringCloud断路器Hystrix原理及用法解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 在分布式环境中,许多服务依赖项中的一些必然会失败.Hystrix是一个库,通过添加延迟容忍和容错逻辑,帮助你控制这些分布式服务之间的交互.Hystrix通过隔离服务之间的访问点.停止级联失败和提供回退选项来实现这一点,所有这些都可以提高系统的整体弹性 两个比较重要的类 HystrixCommand HystrixObserv

  • Laravel框架源码解析之模型Model原理与用法解析

    本文实例讲述了Laravel框架源码解析之模型Model原理与用法.分享给大家供大家参考,具体如下: 前言 提前预祝猿人们国庆快乐,吃好.喝好.玩好,我会在电视上看着你们. 根据单一责任开发原则来讲,在laravel的开发过程中每个表都应建立一个model对外服务和调用.类似于这样 namespace App\Models; use Illuminate\Database\Eloquent\Model; class User extends Model { protected $table =

  • MySQL InnoDB之事务与锁详解

    引题:为何引入事务? 1>.数据完整性 2>.数据安全性 3>.充分利用系统资源,提高系统并发处理的能力 1. 事务的特征 事务具有四个特性:原子性(Atomiocity).一致性(Consistency).隔离性(Isolation)和持久性(Durability),这四个特性简称ACID特性. 1.1原子性 事务是数据库的逻辑工作单位,事务中包括的所有操作要么都做,要么都不做. 1.2 一致性 事务执行的结果必须是使数据库从一个一致性的状态变到另外一个一致性状态. 1.3 隔离性 一

  • Linux硬链接与软链接原理及用法解析

    在linux系统中有种文件是链接文件,可以为解决文件的共享使用.链接的方式可以分为两种,一种是硬链接(Hard Link),另一种是软链接或者也称为符号链接(Symbolic Link). 硬链接概念 硬链接(hard link, 也称链接)就是一个文件的一个或多个文件名 硬链接是指通过索引节点来进行链接.在Linux的文件系统中,保存在磁盘分区中的文件不管是什么类型都会给它分配一个编号,这个编号被称为索引节点编号号(Inode Index)或者Inode,它是文件或者目录在一个文件系统中的唯一

随机推荐