coder

数据库的事务

隔离级别

何为隔离级别❓指的是一个事务的写，对另外一个事务的影响，特别是读，一共有下面几种隔离级别：

幻读，间隙锁

❓为什么说可重复读是部分解决幻读❓

如果当前事务是快照读，显然不会有问题，因为MVCC保证了其他事务的更新不会对本事务读产生影响。如果当前事务产生了当前读（update、select for update等），则会“读”到其他事务更新的结果（因为要保证数据一致，比如update，肯定不会按照快照去update）。

❓那么如何彻底解决幻读❓ 通过间隙锁和next key lock（RR级别生效），也就是锁区间，既然区间被锁住了，区间内的插入也会被阻塞，就避免了幻读。

InnoDB行锁有三种算法：

举例

 
事务1：select * from tbl where id > 16 for update
事务2：insert into tbl (name) values (‘test’)

快照读 MVCC

❓那么快照读是❓MVCC

MVCC即多版本并发控制，每行数据保留多份。不需要锁就可以实现高级别的事务隔离。通过多版本解决幻读。

每个事务分配一个ID，事务在执行第一条select时生成一个read view。read view包含：

事务ID 状态
1 已提交
2 已提交
3 已提交
4 未提交
5 已提交
6 已提交
7 未提交

每条数据维护一个版本链（undo log），每个节点包括：事务ID，以及这个事务对此数据的改动情况。

  1. 每次更新数据，版本链里新增一条记录，记录当前事务ID，值。

  2. 每次删除数据，版本链里复制一条最新记录，记录当前事务ID，标记为删除。

  3. 查找数据时，在版本链中从最新的记录开始找，如果事务在read view中已提交的ID中，则返回这条数据，否则继续查找。

索引

聚簇索引和非聚簇索引

如下图所示，InnoDB只有一个聚簇索引(主键)，其余均为非聚簇索引(辅助键)。MyISAM均为非聚簇索引。

在这里插入图片描述

回表查询和索引覆盖

为了避免回表查询，可以用索引覆盖的方式，将需要查询的字段包含到索引上，

create table test_user (
	id bigint(20) unsigned not null auto_increment,
    uid bigint(20) unsigned not null,
    nickname varchar(20) not null,
    primary key(id),
    key(uid, nickname)
);

explain select nickname, uid from test_user where uid = 2;

联合索引遵守最左原则，mysql会根据最左边的字段建立B+数索引，然后依次按照剩余字段进行排序。索引如下：

1,2,3 1,2,4 1,3,3 2,3,4 2,3,5

Mysql查询优化

至少是range级别：https://www.modb.pro/db/25959

SQL绑定变量：

在使用编程语言如JAVA对数据库进行查询时，SQL有两种写法，第一种写法是：

select * from user_tbl where name = 'a';
select * from user_tbl where name = 'b';
select * from user_tbl where name = 'c';

第二种写法是：

select * from user_tbl where name = ?

通过PreparedStatement进行预编译SQL，这种写法也称绑定变量，这种写法的好处是SQL解析只用了一次，即编译一次（编译好的结果存在数据库中），提升效率。

SQL

WITH AS

WITH TMP1 AS (SELECT * FROM USER1),
     TMP2 AS (SELECT * FROM USER2)
SELECT * FROM USER1, USER2;

分页

limit offset, N：扫描offset+N条数据，并把从offset开始的N条数据返回。连续分页查询的同时如果修改数据库，可能会产生查询数据丢失的情况，如下。可以通过增加修改时间戳来解决。

 
limit 0, 2(展示前2条)
删除前2条数据的某一个
limit 2, 2(数据丢失)

超大分页优化

log_tbl表特别大，service是非主键索引：

锁

按照锁的粒度

行锁是通过索引来实现的。如果要想使用行锁，查询条件必须带上索引，否则使用的是表锁，并发度降低。

按照锁级别

乐观锁

乐观锁通过非阻塞的方式实现对共享资源的访问。 只支付一次： update order_tbl set pay_status=1 where id=1 and pay_status=0; 可以用乐观锁实现对业务的加锁。

res = update lock_tbl set is_lock=true where is_lock=false and lock_id=xxx;
if (res == 0)
  return
执行业务 
update lock_tbl set is_lock=false where is_lock=true and lock_id=xxx;

Mysql死锁和解决方案

InnoDB可以自行解决死锁

分布式事务

分布式事务的常用方法：

Other