mysql相关

发表于 更新于 分类于 本文字数： 1.9k 阅读时长 ≈ 2 分钟
mysql相关

1. MVVC

MVCC用于读已提交和可重复读级别的控制，主要通过undo log日志版本链和read view来实现

每个字段隐藏两个字段： roll_pointer和trx_id

roll_pointer指向更新事务之前生成的undo log，undo log用于事务的回滚，保证事务的原子性。

trx_id就是最近一次更新数据的事务ID

ReadView 重要概念：

  1. m_ids，就是还未提交的事务id集合
  2. low_limit_id，ReadView 生成时刻系统尚未分配的下一个事务 ID
  3. up_limit_id，是 trx_list 列表中事务 ID 最小的 ID
  4. creator_trx_id，创建read view的事务ID，也就是自己的事务

ReadView原理：

  1. 如果trx_id<up_limit_id，那么说明就是之前事务的数据，直接返回，也就对应了小明第一次开启事务查询的场景
  2. 如果trx_id>low_limit_id，trx_id还在[up_limit_id，low_limit_id]范围之内，并且trx_id在m_ids中，就会根据roll_pointer去查找undo log日志链，找到之前版本的数据。
  3. 如果trx_id=creator_trx_id，那么说明就是自己修改的，直接返回就好了，对应的就是自己去修改数据的场景

不同隔离级别创建ReadView

  1. 读提交

​ 每次查询都会生成一次read view

  1. 可重复读

​ 整个事务生成一次快照读，并且在第一次读取时

2. mysql 事务

  • 原子性 ： 要么都成功，要么都失败
  • 一致性 ： 从一个状态转换另一个稳定状态
  • 隔离性 : 事务之间互补影响
  • 持久性 : 事务一旦提交就永久有效

2.1 事务实现

mysql事务

redo_log是物理日志，记录页的物理修改操作，而undo_log是逻辑日志，根据每行记录进行记录。

2.1.1 redo log 重做日志

redo log 有两部分组成 redo log buffer ，redo log file

redo log 写入流程：

  • 执行update操作。

  • 先将原始数据从磁盘读取到内存，修改内存中的数据。

  • 生成一条重做日志写入redo log buffer，记录数据被修改后的值。

  • 当事务提交时，需要将redo log buffer中的内容刷新到redo log file。

  • 事务提交后，也会将内存中修改数据的值写入磁盘。

redo log为什么可以实现事务的原子性和持久性？

  • 原子性，是redo log记录了事务期间操作的物理日志，事务提交之前，并没有写入磁盘，保存在内存里，如果事务失败，数据库磁盘不会有影响，回滚掉事务内存部分即可。

  • 持久性，redo log 会在事务提交时将日志存储到磁盘redo log file，保证日志的持久性。

2.1.2 事务隔离级别

READ-UNCOMMITTED: 读未提交

READ-COMMITTED：读以提交

REPEATABLE-READ：可重复读

SERIALIZABLE: 序列化

3. 执行流程

  1. 客户端发送更新命令到MySQL服务器，经过处理连接、解析优化等步骤；
  2. Server层向InnoDB存储引擎要id=1的这条记录；
  3. 存储引擎先从缓存中查找这条记录，有的话直接返回，没有则从磁盘加载到缓存中然后返回；
  4. Server层执行器修改这条记录的name字段值；
  5. 存储引擎更新修改到内存中；
  6. 存储引擎记录redo日志，并将状态设置为prepare状态；
  7. 存储引擎通知执行器，修改完毕，可以进行事务提交；
  8. Server先写了个binlog；
  9. Server提交事务；
  10. 存储引擎将redo日志中和当前事务相关的记录状态设置为commit状态。

4. 调优

4.1 工具 Explain使用

select： all->index->rang->ref->const 查询速度递增

4.2 mysql 优化原则

  • 最左匹配
  • 避免回表，（覆盖索引）
  • 索引下推 5.6
  • 不为离散度低数据设置索引 如sex
  • 随机无序的值不作为索引，如身份证号，UUID
  • 为用于搜索，排序，分组字段建立索引
  • 频繁更新字段不适合作为索引

4.3 大表如何删除数据

  • 删除索引
  • 删除数据
  • 创建索引

5.使用

create user ‘ua‘@’%’ identified by ‘pa’; 创建用户（ua）和密码（pa）

grant all privileges on . to ‘ua‘@’%’ with grant option; 全局权限

注意： 对于一个已经存在的连接，它的全局权限不受 grant 命令的影响

revoke all privileges on . from ‘ua‘@’%’; 收回权限

5.1 Db权限

grant all privileges on db1.* to ‘ua‘@’%’ with grant option;

grant 修改 db 权限的时候，是同时对磁盘和内存生效的