jdk8.github.io

1. 隔离级别

数据库隔离级别，隔离级别从高到低：

2. SQL

WITH AS

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

3. 分页

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

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

3.1 超大分页优化

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

4. 索引类型

4.1 聚簇索引和非聚簇索引

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

在这里插入图片描述

4.2 回表查询和索引覆盖

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

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 1,3,4

4.3 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解析只用了一次，即编译一次（编译好的结果存在数据库中），提升效率。

5. 锁

5.1 按照锁的粒度

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

5.2 按照锁级别

5.3 间隙锁和next key lock（RR级别生效）

InnoDB行锁有三种算法：

举例

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

5.4 乐观锁

乐观锁通过非阻塞的方式实现对共享资源的访问。 只支付一次： 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;

错误使用：只插入一次： insert into xxx_tbl ... where not exist ... limit 1 注意这个limit 1是必须要带的，否则会重复插入。 FIX :如果真的并发插入了，也没法保证不重复。

5.5 Mysql死锁和解决方案

InnoDB可以自行解决死锁

5.5 MVCC

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

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

根据read review维护了如下事务水位，每个事务根据水位确定从undo log里读什么数据：

已提交事务(如t1, t2, t3) 未提交事务(t4,t7)+已提交事务(t5,t6) 未开始事务(t8)

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

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

  3. 查找数据时，从最新的记录开始找：

    • 如果事务的ID小于最小未提交的事务ID，表示该事务已经提交，返回这个数据。

    • 如果事务ID大于最大的事务ID，表示事务未开始，不可见。
    • 如果在最小未提交事务ID和最大事务ID之间(均包含)，如果这个事务ID在未提交事务ID里，则不可见，如果不在，则返回这个数据。

6. 分布式事务

分布式事务的常用方法：

7. Other