foolish1024

数据库锁（二）

2019/04/05 数据库

数据库锁（二）

1.Innodb索引原理

  • Innodb一定存在聚簇索引，默认以主键作为聚簇索引；

    • 没有主键时，会用一个唯一且不为空的索引列做为主键，成为此表的聚簇索引；

    • 如果没有这样的索引，Innodb会隐式定义一个主键来作为聚簇索引。

  • 创建的索引，如组合索引，前缀索引，唯一索引，都属于非聚簇索引；

  • 有几个索引，就有几颗B+树；

  • 聚簇索引的叶子节点为磁盘上的真实数据，非聚簇索引的叶子节点还是索引节点，存放的是该索引字段的值和对应的主键索引（聚簇索引）。

    • MyISAM中的索引文件和数据文件是分离的，叶子节点存储的是索引和数据文件的指针两类。

2.Innodb行锁

Innodb行锁是通过索引上的索引项加锁实现的，也就意味着，只有通过索引条件检索数据，Innodb才使用行锁，否则使用表锁。

两个注意点：

  • Innodb中所谓的表锁，是通过Next-key Locks，也就是行锁+间隙锁来实现锁表的操作的；
  • 如果当前事务的隔离级别是RU或RC，都不存在间隙锁，也就是说，无论条件列上是否存在索引，都不会锁表，只锁行；锁表只在RR或Serializable中成立。

2.1 加锁规则

for update–>加X锁；lock in share mode加S锁。

  • RU/RC+条件列是无索引

select * from table where num = 1 for update

num列没有索引，如果对应的id=1，则在id=1的聚簇索引上加行级X锁，当前读。不会加表锁，因为在RU/RC隔离级别下，没有间隙锁。

  • RU/RC+条件列是聚簇索引

select * from table where id = 1 for update

id列有主键，在id=1的聚簇索引上加X锁，当前读。

  • RU/RC+条件列是非聚簇索引

select * from table where num = 1 for update

num列上建非唯一索引，此时有一颗聚簇索引形成的B+索引树和一颗非聚簇索引形成的B+索引树。如果num=1对应的id=1，则先在num=1的索引上加行级X锁，接着去聚簇索引树上查询到对应的id=1，并在该聚簇索引上加行级X锁，当前读。

  • RR/Serializable+条件列无索引

select * from table where num = 1 for update

全表的所有记录的聚簇索引上加X锁，并在聚簇索引的所有间隙加Gap Lock，也就是全表加锁，当前读。

  • RR/Serializable+条件列是聚簇索引

select * from table where id = 1 for update

在id=1的聚簇索引上加X锁，当前读。

select * from table where id = 1 lock in share mode

在id=1的聚簇索引上加S锁，并加间隙锁，当前读。

  • RR/Serializable+条件列是非聚簇索引

select * from table where num = 1 for update

num=1（匹配到两条，id=1，id=2）的非聚簇索引上加X锁，并加间隙锁，接着在id=1和id=2的聚簇索引上加S锁。

select * from table where num = 1 lock in share mode

num=1的非聚簇索引上加S锁，并加间隙锁，接着在id=1的聚簇索引上加S锁。

  • 总结：

    • RU/RC隔离级别下，无论有没有索引，都只是加行级锁；

    • RR/Serilizable隔离级别下，有索引加行级锁，无索引加行级锁+间隙锁；
    • 聚簇索引和非聚簇索引只是加锁的多少不同，聚簇索引只加一个锁，非聚簇索引需要给非聚簇索引列加锁，也要给聚簇索引列加锁；
    • 聚簇或非聚簇索引匹配到的结果有多条，或者在聚簇索引加S锁的时候，会同时加间隙锁（RR/Serializable隔离级别下）；
    • Serializable隔离级别下，所有select语句都会自动加上lock in share mode，因此都会使用行锁+间隙锁。
    • 不存在的列只会加间隙锁（RR/Serializable隔离级别下）。

2.2 使用RC隔离级别

MySQL默认使用RR隔离级别，但是线上一般使用RC隔离级别，主要原因：

  • RR存在间隙锁，出现死锁的几率大；
  • 在RR隔离级别下，条件列未命中索引会锁表，而RC只锁行；
  • RC级别下，半一致性读特性增加了update操作的并发性。（半一致性读：一个update语句，如果读到一行已经加锁的记录，此时Innodb返回记录最近提交的版本，由MySQL上层判断此版本是否满足update的where条件，若满足，则重新发起一次读操作，此时会读取行的最新版本并加锁）

RC隔离级别下，不可重复读的问题可以接受，因为数据已经提交，即使读出来的数据前后不一致也是可以的。

3.binlog

binlog是记录所有数据库表结构变更（create，alter table，，，）以及表数据修改（insert，update，delete，，，）的二进制日志。update操作没有造成数据变化，也会记入binlog。binlog不会记录select和show这类操作，可以通过查询通用日志查看。

3.1结构

binlog称为二进制日志，包括两类文件：

  • 索引文件（文件名后缀为.index）用于记录哪些日志文件正在被使用；
  • 日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML（除了查询语句）语句事件。

例子：

my.conf配置文件有如下配置

log_bin：on //打开binlog日志
log_bin_basename：/var/log/mysql/mysql-bin  //bin文件路径及名前缀
log_bin_index：/var/log/mysql/mysql-bin.index  //bin文件index

那么在文件目录/var/log/mysql下会有两个文件mysql-bin.000001和mysql-bin.index。

mysql-bin.index就是索引文件，里面记录哪些文件是日志文件

./mysql-bin.000001

在Innodb中日志文件可以分为两部分，一部分在缓存中，一部分在磁盘上。通过刷盘造作将缓存中的日志刷到磁盘上。跟刷盘有关的参数有两个：

binlog_cache_size: 二进制日志缓存部分的大小，默认值32k

sync_binlog=[N]: 表示写缓冲多少次，刷一次盘,默认值为0
  • binlog_cache_size大小要合适，太大会造成内存浪费，太小会频繁将缓存日志写入临时文件；
  • sync_binlog等于0时表示刷新binlog的时间点由操作系统决定，性能最好，等于1表示每次事务提交时就会刷新binlog到磁盘。

当数据库在操作系统宕机时，可能数据并没有同步到磁盘，可以通过binlog恢复数据。

3.2 作用

  • 恢复：数据库宕机，通过binlog恢复数据；

  • 复制：MySQL的主从复制。

    • 主库将变更写入binlog日志，有一个log dump线程，将binlog传给从库；

    • 从库有两个线程，一个IO线程，一个SQL线程，IO线程读取主库传过来的binlog内容并写入到relay log，SQL线程从relay log里面读取内容，写入到从库的数据库。

  • 审计：用户通过二进制日志中的信息进行审计，判断是否有对数据库进行注入攻击。

主从复制的延时问题：从库同步主库数据的过程是串行化的，高并发场景下，会有延时问题。

并行复制解决延时问题，从库开启多个线程，并行读取relay log中不同库的日志，然后并行存放不同库的日志。

主库数据丢失问题：如果主库突然宕机，恰好数据还没有同步到从库，那么有些数据可能就丢失了。

半同步复制解决数据丢失问题，semi-sync复制，主库写入binlog日志之后，强制此时立即将数据同步到从库，从库将日志写入自己本地的relay log之后，返回一个ack给主库，主库接收到至少一个从库的ack之后才会认为写操作完成了。

3.3 格式

format 定义 优点 缺点
statement 记录修改SQL的语句 日志文件小，节约IO，提高性能 准确性差，对一些系统函数不能复制，如now()，uuid()等
row 记录每行实际数据的变更 准确性强，能准确复制数据的变更 日志文件大，需要较大的网络IO和磁盘IO
mixed 上述两种模式的混合 准确性强，文件大小适中 有可能发生主从不一致问题

一般线上使用row格式。

4.索引的使用

索引不被用到的情况：

  • **select * **可能导致不走索引；
  • COUNT(*)不能走索引；
  • 索引列上有函数运算；
  • 隐式转换；
  • !=，<>不走索引；
  • not in，not between不走索引。

索引被用到的情况：

  • >，<，between，in都可以用到索引；

  • 最左前缀原则

    • a

    • a，b
    • a，b，c
    • a，c，只有a会走索引，c不会

参考：

史上最全的select加锁分析

研发应该懂的binlog知识

Search

    Table of Contents