性能优化之数据库篇2-事务与锁

MySQL事务

事务可靠性模型ACID：

  • Atomicity: 原子性，一次事务中的操作要么成功，要么失败
  • Consistency: 一致性，跨表，跨行，跨事务，数据库始终保持一致状态
  • Isolation: 隔离性，保护事务不会互相干扰，包含4种隔离级别
  • Durability: 持久性，事务提交后，不会丢数据。如系统崩溃，电源故障。

事务隔离级别

  • 读未提交 READ UNCOMMITTED
  • 读已提交 READ COMMITTED
  • 可重复读 REPEATABLE READ
  • 可串行化 SERIALIZABLE

可以设置全局的默认隔离级别，也可以单独设置会话的隔离级别

  1. 读未提交

很少使用，不能保证一致性。

  • 问题：脏读、幻读、不可重复读
  • 锁：以非锁的方式执行
  1. 读已提交

每次查询都会设置和读取自己的新快照

  • 问题：幻读、不可重复读
  • 锁：锁定索引记录，而不锁定记录之间的间隙
  1. 可重复读（默认隔离级别）

使用事务第一次读取时创建的快照。

  • 问题：幻读
  • 锁：使用唯一索引的唯一查询条件时，只锁定查找到的索引记录。其他查询条件，会锁定扫描到的索引范围，通过间隙锁或临键锁来阻止其他会话在这个范围中插入值。
  1. 串行化

最严格的级别，事务串行执行，资源消耗最大

锁

锁是数据库系统区别于文件系统的一个关键特性，用于管理对共享资源的并发访问。Innodb引擎是在行级别对数据库上锁。

MySQL拥有4种锁

  • 共享锁 （S Lock），允许事务读一行数据
  • 排他锁（X Lock)，允许事务删除或更新一行数据

为了支持多粒度的锁定，这种锁定允许行级上的锁和表级上的锁同时存在。InnoDB还支持意向锁。意向锁就是把锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上加锁。如下图所示，如果需要对记录r上X锁，那么就需要对数据库A、表、页上意向锁IX，最后对r上X锁。如果其中任何一个部分导致等待，那么该操作需要等待粗粒度锁的完成。

意向锁的设计目的是为了在一个事务中揭示下一行将被请求的锁类型

  • 意向共享锁 （IS Lock），事务想要获得一个表中某几行的共享锁
  • 意向排他锁 （IX Lock），事务想要获得一个表中某几行的排他锁

兼容关系图：

锁类型 IS IX S X
IS 兼容 兼容 兼容 不兼容
IX 兼容 兼容 不兼容 不兼容
S 兼容 不兼容 兼容 不兼容
X 不兼容 不兼容 不兼容 不兼容

用户可以通过命令查看锁请求的信息

间隙锁

会锁定整个查询范围内的所有键值，即使这个键值不存在。

如：

select * from emp where empid > 1 and empid<10 for update;

即使emp只有2,4,7三条记录，也会对1-10之间的“间隙”记录加锁。

SHOW ENGINE INNODB STATUS

悲观锁与乐观锁

悲观锁和乐观锁是人们定义出来的概念，你可以理解为一种思想。

悲观锁

悲观锁：就是对数据处理保持悲观态度，总认为会冲突。所以在整个数据处理过程中，需要将数据锁定。

悲观锁的实现通常通过数据库提供的锁机制实现，比如mysql中的排他锁，select … for update.

举个例子。扣减库存的情况下，我们需要先判断商品库存是否充足。

  1. 建表
CREATE TABLE `tb_goods_stock` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `goods_id` bigint(20) unsigned DEFAULT '0' COMMENT '商品ID',
  `nums` int(11) unsigned DEFAULT '0' COMMENT '商品库存数量',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `modify_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `goods_id` (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品库存表';
  1. 开启事务，查询商品，并对记录加锁

先关闭自动提交事务 set autocommit = 0;

BEGIN;
SELECT nums FROM tb_goods_stock WHERE id=1 FOR UPDATE;
  1. 判断商品数量是否大于购买数量。如果不满足，回滚事务
  2. 如果满足条件，扣库存，提交事务
UPDATE tb_goods_stock SET nums=nums-5 WHERE id=1 AND nums>5;
COMMIT;
  1. 事务提交时会释放锁。

乐观锁

乐观锁是对数据的处理保持乐观态度，认为数据一般不会冲突，只有提交数据更新时，才会对数据释放冲突进行检测。

乐观锁的实现不依靠数据库提供的锁机制，而是需要我们自己实现，实现方式一般是记录数据版本，一种是通过版本号，一种是公故时间戳。很多持久化框架已经封装好了乐观锁的实现，如hibernate。

  1. 建表
CREATE TABLE `tb_goods_stock` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `goods_id` BIGINT(20) UNSIGNED DEFAULT '0' COMMENT '商品ID',
  `nums` INT(11) UNSIGNED DEFAULT '0' COMMENT '商品库存数量',
  `create_time` DATETIME DEFAULT NULL COMMENT '创建时间',
  `modify_time` DATETIME DEFAULT NULL COMMENT '更新时间',
  `version` BIGINT(20) UNSIGNED DEFAULT '0' COMMENT '版本号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `goods_id` (`goods_id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='商品库存表';
  1. 查询要卖的商品和版本号
BEGIN;
SELECT nums,VERSION FROM tb_goods_stock WHERE id=1;
  1. 判断商品数量是否大于购买数量。如果不满足，就回滚事务。
  2. 如果满足，扣减库存
UPDATE tb_goods_stock SET nums=nums-5,VERSION=VERSION+1 WHERE id=1 AND nums>5 AND VERSION=0;
  1. 判断更新是否成功，如果成功，则提交，否则就回滚。

日志

undo log :撤销日志

保证事务的原子性

用处：事务回滚、一致性读、崩溃恢复
记录事务回滚时所需的撤销操作。一条insert语句，对应一条delete的undo log,一条update语句，对应一条反向update的undo log

redo log:重做日志

确保事务的持久性，防止事务提交后未刷新到磁盘就崩溃了

事务执行过程中写入redo log，记录事务对数据页做了哪些修改

可以提升性能，先写日志，再刷磁盘。

日志文件：ib_logfile0,ib_logfile1

日志缓冲：innodb_log_buffer_size

强刷：fsync()

MVCC 多版本并发控制

可以解决的问题：

  • 使InnoDB支持一致性读:READ COMMITTED和REPEATABLE READ。当我们在某个时间点查询数据时，只能看到这个时间点之前的事务提交更新的结果。
  • 让查询不被阻塞、无需等待被其他事务持有的锁，可以增加并发性能。

快照读：读取的是快照数据，不加锁的简单Select 都属于快照读

当前读：读取的是最新数据，加锁的select，或者增删改都是进行当前读。

1. 行记录的隐藏列

  • row_id :隐藏的行id，用来生成默认的聚集索引，如果创建表时没有指定聚集索引，这时InnoDB就会用这个隐藏id来创建聚集索引。
  • trx_id：操作这个数据的事务id，最后一个对数据操作的事务id
  • roll_pointer：回滚指针，指向这个记录的undo log数据

数据的快照记录通过链表的结构串联起来了，要找到历史快照，可以通过遍历roll_ptr的方式查找。

2. Read View

  • trx_ids 系统当前正在活跃的事务id集合
  • low_limit_id Read View生成时刻系统尚未分配的下一个事务ID
  • up_limit_id 活跃事务中最小的事务id

Read View是事务进行快照读操作的时候生产的读试图，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护当前活跃事务的ID(每个事务开启都有一个ID，这个ID是递增的)

如果一个事务要查询行记录，那么应该读哪个快照版本呢？Read View就是解决这个问题的。Read View可以帮助我们解决可见性问题，Read View保存了当前事务开启时所有活跃的事务列表。换个角度可以理解为Read View保存了不应该让这个事务看到的其他事务Id的列表。

如果事务隔离级别是

  • 读已提交，那么一个事务每次的select都会去查一次Read View,每次查到的Read View不同，就可能导致不可重复读或幻读的情况

  • 可重复读，那么事务只在第一次select的时候获取一次Read View，然后后面的select会复用这个Read View。

举个例子：
当事务2对用户表某行数据执行了快照读，数据库为该行数据生成一个Read View读视图，假设当前事务ID为2，此时还有事务1和事务3在活跃中，事务4在事务2快照读前一刻提交更新了

update user set name=’小明1′ where id=1

此时undo日志中存在如下版本链。

事务2会拿着trx_id字段记录的事务id:4去跟Read View中的up_limit_id(1)和low_limit_id(5)比较

  1. 判断db_trx_id < up_limit_id，如果为true就 结束，否则继续判断。
  2. db_trx_id >= low_limit_id ，如果为true就结束，否则继续判断
  3. 判断db_trx_id是否在活跃列表中，若不存在，符合条件，结束。

优化实战

  1. 建表的时候字段尽量小，如果是固定的，可以用char.

小贴士：int、bigint等后面写的数字没有实际意义。如int(8)

示例建表的字段类型选择：

  1. 存储引擎的选择，一般用Innodb,如果是冷数据，也没人查了，但是又不能删，可以用TokuDB,支持高压缩比（1：12）

  2. 注意sql中的隐式转换

  3. 适当增加索引，注意索引的区分度，如身份证号、手机号比较好。性别、年龄就比较差。

  4. 主键id推荐单调递增，这样插入的时候速度更快

  5. 数据写入优化

  • 通过PreparedStatement减少SQL解析
  • Multiple Values减少交互。
insert into persons 
(id_p, lastname , firstName, city )
values
(200,'haha' , 'deng' , 'shenzhen'),
(201,'haha2' , 'deng' , 'GD'),
(202,'haha3' , 'deng' , 'Beijing');
  1. 数据更新，范围更新需要谨慎，避免锁的范围过大
  2. 模糊查询

like 如果前面不加%，还是能走到索引的。如果查询条件非常多，并且任意组合的那种场景，不建议再用sql来做，推荐solr、ES。
9. 连接查询 避免笛卡尔积

连接查询时，当连接on条件是非唯一字段时，会出现笛卡尔积(局部笛卡尔积)；当连接on条件是唯一字段时，则不会出现笛卡尔积。
10. 注意索引失效

  • null、not、not in、函数等
  • 减少使用or，可以用union或union all(不去重，比union性能好)代替
  • 必要时可以用force index强制指定索引
  1. 查询sql的设计
  • 考虑查询量和查询次数的平衡
  • 避免大量数据的传输
  • 避免使用临时文件排序或临时表
  • 分析类需求，可以用汇总表
Tags:

VirMach 便宜 VPS

QNews

QNews