超级开发工程师

# MySQL

# 1 一条SQL的一生

# 1.1 数据库查询过程

image-20220909111349004

共有三个层次 客户端、Server、引擎层

  1. 客户端 (DataGrip、Navicat或者Java程序等)
  2. Service
    1. 缓存: 当一条select语句执行后,会把语句做key,值做value存在缓存中(MySQL已删除,因为太容易失效了)
    2. 分析器: 分析SQL的合法性,并将select后面的语句与字段映射,from后面的语句与表做映射
    3. 优化器: 让我(MySQL)看看哪个索引更合适
    4. 执行器: 调用指定的存储引擎
  3. 引擎层(存储引擎,在建表时确立,MySQL5.6后默认使用InnoDB,常见的还有MySAM以及Memory等)

# 1.2 SELECT语句

  1. 与MySQL建立连接,并根据当前用户查询出相应的权限(权限在查出来后只要连接没中断,那就不会改变,哪怕你另起一个线程修改了,权限依然还是原来的)
  2. 有缓存查缓存,然后返回
  3. 做SQL校验
  4. 让MySQL自己选择更有效的索引
  5. 查看是否有权限,有则调用执行器查询

# 1.3 DML语句

  1. 与SELECT类似,先建立连接 -> 查索引 -> 执行

  2. 更新数据时

    1. redo log文件中写日志,并将状态设置为prepare,redo log位于引擎层,只有InnoDB有,用于容灾,当redo log满了后会刷到磁盘,成功后悔清除掉,所以哪怕服务器宕机,也不用担心更新数据丢失
    2. 写完redo log后,再在bin log中记录日志,bin log位于Server层,所有引擎都可以使用,bin log的主要作用是用于备份,比如,我在主库创建数据后,可以將bin log传给从库,从库根据bin log来更新数据
    3. bin log 写完之后,將redo log的状态改成commit,只有状态为commit的数据,才可以刷到磁盘中,二者联动保证哪怕服务宕机或者超时,bin log和redo log仍然同步

# 2 事务

# 2.1 特性(ACID)

  1. 原子性: 事务要么都成功,要么都失败
  2. 一致性: 事务前后,数据总量不变,以余额为例,转账后不管成功与否双方总额不变
  3. 隔离性: 事务之间不可互相访问,详见下一节
  4. 持久性: 事务提交后要永久保存到磁盘

# 2.1 隔离级别

  1. 读未提交: 可以读取到其他事务已更新但是未提交的数据(脏读)
  2. 读已提交: 可以读取到其他事务已提交的数据,但是本身事务中多次查询同一个SQL可能获取不同的结果(不可重复读)
  3. 可重复读: 一个事务中,查询同一条SQL两次,结果一致,但如果有更新操作可能会有新的行没被更新到或者少了几行(幻读)
  4. 串行化: 直接上锁,只有当前事务可以访问

# 2.2 快照在MVCC中如何工作

​ 在MySQL中,解决脏读和不可重复读的主要方式就是多版本并发控制-MVCC(Multi Version Concurrency Control)

​ 实现的方式是:在事务开始时创建一个快照视图,事务只能访问数据视图内的数据,进而保证数据的可重复读,一般使用场景为数据库备份

​ 实现原理如下:

​ 首先数据库中的每行数据除了业务数据以外,还包含两个隐藏字段: row_trx_id(哪个事务更新本行数据),roll_pointer(上一个版本的undo log)

​ 这里出现了一个undo log的概念,前文1.3中有介绍两个东西bin log(复制、数据备份时使用)以及redo log(灾后数据恢复),而新出现的undo log则是指,每当有新的事务提交后,就会有一个新版本数据,并将这个版本的roll_pointer指向上一个版本数据,从而形成一条undo log链,当需要回滚的时候则通过roll_pointer找到上一个版本

​ 除了undo log还需要展开介绍一下视图,视图是在事务开始时(手动开),或者事务第一次执行CRUD语句时建立,每个视图都包含四个字段

1. creator_trx_id: 本次事务的id(事务id是从小到大递增的)
1. m_ids: 目前已经创建但是还没commit的事务id数组
1. min_trx_id: 第一条还没commit的事务中
1. max_trx_id: 所有事务中(不管是否commit)最后一次创建的事务+1,即:下一条将要创建的事务id

通过undo log以及视图,就可以实现事务读取到他该读的数据

举个例子:

当一条update语句更新一行的时候,他会遇到这几种情况:

  1. row_trx_id是否小于min_trx_id,是则说明修改这条数据的事务已经提交了,这行数据可以读取
  2. row_trx_id是否大于max_trx_id,是则说明修改这条数据在创建视图之后还有其他事务也更新了,他已经不是最新的了,不可读取
  3. 如果row_trx_id在min_trx_id和max_trx_id之间,则需要分情况讨论
    1. row_trx_id = creator_trx_id,自己修改的当然可以读取
    2. row_trx_id != creator_trx_id
      1. row_trx_id在m_ids里,当前事务和row_trx_id的事务并发修改了这一行,不可读取
      2. row_trx_id不在m_ids里,说明row_trx_id已经提交过了,则这行就是最新的了,可以读取

如果发现不可取,那他就会通过roll_pointer找到上一个版本,再通过上面的方式判断,直至找到可以读取为止

# 3 索引

# 3.1 数据结构

三类:

  1. hash

    实现方式: 参考HashMap

    优点: 新增快,单个查询快

    缺点:范围查找慢

    适用范围: 单个查找的缓存

  2. 有序数组

    实现方式: 递增数组

    优点:单个查找和范围查找都快(二分搜索)

    缺点: 新增慢,需要移动后面的数组

    使用返回: 静态存储不会修改的数据,例如,某一年的数据

  3. tree

实现方式: 二叉树: 大小关系为 左 < 父 < 右 (效率高但用得少,树高太高的话请求数据块次数太多)

​ 多叉树: 子树从左往右递增

优点: 查询与插入都是O(LogN)

缺点: 查单个的时候比hash慢(总得有点缺点)

适用范围: 上面做不到的树都能做到,就是做不到第一

# 3.2 类型

  1. 主键索引 : 主键自动成索引,不可重复
  2. 普通索引: 可以重复的索引
  3. 唯一索引: 不可重复的索引
  4. 复合索引: 可以理解为把多个普通索引合并成一个索引

聚簇(cù)索引或者一级索引(主键索引): 叶子节点存值

非聚簇索引或者二级索引(其他三个): 叶子存主键

查非聚簇索引时,因为只能查到主键,索引需要回表二次查

# 3.3 索引维护

非主键索引或者主键索引但是主键非自增,在插入元素时

​ 如果正好新元素需要插在一个叶子数据块的中间,则意味着插入位置后面的元素都需要往后移动一个,因为树结构采用页式存储,每个叶子节点都是一个页,如果大于数据库存储数量,则会触发页分裂,影响性能,影响原因如下 (ps: 如果两个页的利用率都太低,则会页聚合)

  1. 会将一部分数据移动到下一个页
  2. 分页后也会导致空间利用率变低,降低50%

主键索引并且主键自增,在插入元素时(尽可能使用自增索引)

​ 每条数据都是顺序插入,不会引起页分裂

# 3.4 覆盖索引

​ 3.2的时候交代:使用非主键索引来进行查找的时候,通常都会回表,从而变成查了两次,那么看以下的情况:

select id from user where name = "Woohoo"
1

​ 这种情况下,第一次会查出name为Woohoo的行的主键,因为第一次走的是非聚簇索引,所以只能查出主键,但是主键就是我们需要的东西,所以不必回表查了,这就被称为覆盖索引,是一种非常常用的优化手段

​ 那么有必要创建身份证号_姓名这样的复合索引吗?身份证加上姓名是唯一的,但是身份证号本就是唯一的,所以这个复合索引就没意义吗?

​ 如果说此时有一个需求,会大量通过身份证查姓名

select name from user idNumber = "350101199710062666"
1

那这个复合索引就是有意义的,出于索引覆盖的原因只需要查一次复合索引即可,无需要再回表查,会省出大量时间

# 3.5 最左前缀匹配

​ 如果有一张表user里面有四个字段 id, 身份证号码,姓名,手机号,有一个接口需要查询姓名,另一个接口需要查手机号,如何构建索引效率最高?

​ 可以构建一个复合索引(姓名,手机号),那么当查询条件只有姓名或者查询条件是姓名+索引时,便会走这个索引,其中必须满足以下几点:

  1. 最左边的如果是字符串,可以模糊查询但必须右模糊,并且后续的都不生效,例如: (name = "陈%" and phone = 110,则只有name = "陈%",可以生效
  2. 最左边如果是数字,若是范围查询,则只有最左边的索引生效,后续的都不生效
  3. 如果我们有一个接口查姓名,另一个接口查手机号,那么在只查手机号的时候,(姓名,手机号)这个索引就生效了,所以必须再维护一个普通索引或者唯一索引(手机号)

# 3.6 索引下推

​ 还是这么一个表user,字段如下: id,身份证号码,姓名,手机号

​ 构建一个复合索引(姓名,手机号),那么当执行以下语句时,

select * from user where name like "陈%" and phone = "110"
1

​ 因为使用了模糊查询,所以索引只能走到name,但是后面的phone就直接废除了吗?

​ 曾今是的,但在mysql5.6之后多了个索引下推的功能,他会去排除phone!="110"的数据,从而减少回表次数

# 4 锁

# 4.1 全局锁

当表开启全局锁的时候,其他线程的所有DDL或者DML语句都将被暂停

使用场景:全库逻辑备份(但是支持 mvcc 的可以不需要)

# 4.2 表级锁

  1. 表锁 直接锁定整张表(手动上锁)

    1. 上读锁,则其他线程和自身都不可以写数据,但可以读数据;
    2. 上写锁,则只允许当前线程自身读写数据

  2. 元数据锁-MDL(metaData Lock) 防止 DDL 与 DML 之间以及 DDL 与 DDL 之间的并发冲突(系统自动上锁)

    1. 上读锁,保证表结构不能变,其他线程和自身都可以CRUD
    2. 上写锁,只有自身线程可以对表结构进行改变,并且其他线程不可进行CRUD与增删字段
    3. 在事务中,事务一开启就会获取MDL,并且写锁优先级会高于读锁优先级,如果最开始先获取了读锁,后续一个线程请求写锁,一个线程请求读锁,则写锁会先被阻塞,因为写锁优先级高,所以读锁也会被堵塞,如果客户端还有重试机制,那整个库的线程就爆满了,因此一定要避免长事务

    DDL操作可能会引起线上CRUD阻塞,因此需要谨慎

# 4.3 行锁

首先,行锁只有InnoDB存储引擎支持

# 4.3.1 二阶段锁

事务当中

第一阶段是上锁: 第一次执行update语句时,会对更新的所有行加上行锁

第二阶段是解锁: 只有在事务提交之后,才会将锁释放

所以应该尽可能把并发度高的更新语句放在事物的最末端,减小锁被占用的时间,进而减小冲突的可能

# 4.3.2 死锁

现在有以下情况:

  1. 事务A执行一个update语句锁定了行A(没来得及commit)

  2. 事务B执行一个update语句,锁定了行B

  3. 此时若事务A需要操作行B并且事务B需要操作行A

因为事务中只要执行了update语句就会上锁,所以这个时候事务A和事务B都在等待对方释放锁,这也就是死锁

解决死锁有两个有两种策略:

  1. 超时回滚,当线程等待超时(MySQL默认等待时间是50s),则会回滚当前事务

    如果死锁则需要等待50s,这队系统来说是致命的,但是

  2. 死锁检测,每当获取一个锁的时候会被加到所队列,然后判断自己的加入是否会引起死锁,会则回滚自身业务,但是因为检测需要消耗资源,若并发量很大,则很容易导致CPU满负载,因此会看到CPU利用率很高,但是没处理几个事务

如何解决高访问率的行数据?

  1. 关闭死锁检测,确保事务不会发生死锁,可以关闭死锁检测,缺点就是,万一发生死锁,挺50s对系统十分致命

  2. 并发控制,对数据库并发量进行控制

    1. 客户端层面做并发控制,但是客户端多了请求数据库压力依然很大
    2. 中间件做并发控制
    3. MySQL源码层面做并发控制
    4. 拆行,例如有一个账户是热点行,可以把一个账户拆成N个行,增减余额时候随机取一个处理出来