乐趣区

关于mysql:MySQL复习

1. 一条查问 SQL 的执行过程

  1. 客户端 => 连接器 权限验证(连贯断开开释内存,对长连贯须要 reset_connection)
  2. 查问缓存(尽量不要应用,缓存命中率比拟低,8.0 后齐全删除)
  3. 分析器 语法分析 词法剖析 MySQL 晓得你要做什么
  4. 优化器 优化查问打算:抉择哪个索引?表连贯的时候决定表的连贯程序
  5. 执行器:调用存储引擎接口取得每行数据,将合乎的后果存储在后果集中

2. 一条更新 SQL 的执行过程

  1. redo log:先写日志,再写磁盘 => 保障 crash safe
  2. Update Set WHERE id = 2

       1.  执行器找存储引擎取 ID = 2 的这一行,ID 是主键索引,引擎间接通过树搜寻取得该行。如果原本就在内存中,间接返回;否则,从磁盘中加载这一行
       1.  执行器将该行数据更改后调用引擎接口写入这行新数据
       1.  引擎将数据更新到内存,同时写 redo log,redo log 处于 prepare 状态,同时引擎告知执行器随时能够提交
       1.  执行器写 binlog 并落盘
       1.  执行器调用引擎的提交事务接口,引擎将 redo log 的状态改为提交,更新实现 
  3. redo log 和 bin log 都能够示意事务的提交状态,而两阶段提交保障了二者逻辑一致性
  4. 全量备份 + bin log:复原、备库

3. 事务隔离

  • 读未提交是指,一个事务还没提交时,它做的变更就能被别的事务看到。
  • 读提交是指,一个事务提交之后,它做的变更才会被其余事务看到。
  • 可反复读是指,一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是统一的。当然在可反复读隔离级别下,未提交变更对其余事务也是不可见的。
  • 串行化,顾名思义是对于同一行记录,“写”会加“写锁”,“读”会加“读锁”。当呈现读写锁抵触的时候,后拜访的事务必须等前一个事务执行实现,能力继续执行。
  • 可反复读的实现:回滚段 => 尽量避免长事务

4. 索引

  1. 为什么要用自增主键作为主键索引?(话中有话主键是间断的):益处:每次插入都是追加操作,不须要移动其余记录,也不波及叶子节点的决裂。而采纳有业务逻辑的字段作为主键,则不能保障是有序插入,波及节点的决裂、合并。
  2. 自增主键字段空间更小(4 或 8),则一般索引的叶子节点小,单页存储更多。
  3. 个别状况下都应用自增主键,只有 KV 场景(只有一个惟一索引)例外。
  4. 笼罩索引:不用回表
  5. 最左前缀:建设联结索引时思考索引的程序

5. 行锁

  1. 两阶段锁:当须要时加锁,事务提交时才开释锁 => 尽可能将影响并发度的锁后推
  2. 高并发时,死锁检测很有可能占用大量 CPU 资源

6. 事务到底是隔离的还是不隔离的

视图

  1. view => 查问语句定义的虚构表
  2. 实现 MVCC 用到的一致性读视图 =>

事务 ID 每行数据不同版本 row_trx_id

为每个事务结构了事务启动时以后沉闷的事务 ID (启动了然而尚未提交)

低水位:数组外面最小 trx_id

高水位:以后零碎创立的最大 trx_id + 1

  1. 如果落在绿色局部,示意这个版本是已提交的事务或者是以后事务本人生成的,这个数据是可见的;
  2. 如果落在红色局部,示意这个版本是由未来启动的事务生成的,是必定不可见的;
  3. 如果落在黄色局部,那就包含两种状况
    a. 若 row trx_id 在数组中,示意这个版本是由还没提交的事务生成的,不可见;
    b. 若 row trx_id 不在数组中,示意这个版本是曾经提交了的事务生成的,可见。

以后读:update 或 select for update / lock in share mode

RC 和 RR 的区别就是 RC 每个语句创立新的视图,RR 事务开始创立新的视图,前面查问沿用

7. 一般索引

change buffer => 非惟一一般索引

8. 空洞

删除只是打标记示意可复用,插入也可能造成页决裂导致呈现空洞

1. 解体复原

Prepare(工夫 A)=> binlog(工夫 B)=> commit

  • 如果 A 处 crash,则事务回滚
  • 如果 B 处 crash

    • 如果 redo log 有了 commit 标记,则提交
    • 如果 redo log 只有 prepare

      • 如果 bin log 是残缺的,则提交
      • 否则回滚事务
  1. 为什么须要两阶段提交?先写 redo log,再写 bin log,解体复原的时候,两个日志都残缺即可,行不行?

不行,这是一个 2PC 的问题,对于 InnoDB 来说,redo log 提交后就无奈回滚(如果回滚,则可能笼罩其余事务的更新);而如果 redo log 胜利,bin log 失败,此时就会呈现不统一。

  1. 只用 bin log 能不能实现解体复原?

    • 历史起因 不提供 crash safe
    • bin log 不记录数据页的更改,解体复原时无奈复原尚未写入到磁盘的提交
  2. 只用 redo log 行不行?

    • bin log 在主从复制方面还有不可代替的作用
    • bin log 能够归档,而 redo log 是循环写

2. explain 应该关注哪些字段?

Column 含意

  • id 查问序号
  • select_type 查问类型(simple、primary、union)
  • table 表名
  • partitions 匹配的分区
  • type join 类型

    • index:索引全表扫描
    • index_merge:两个或以上的索引,最初取交加或并集
    • range:范畴查问
    • ref:匹配多行记录(非惟一索引)=> 多表 join 时,后面表的一行能匹配到前面表的多行
    • eq_ref:匹配一行记录(主键或惟一索引)=> 多表 join 时,后面表的一行能匹配到前面表的一行
  • prossible_keys 可能会抉择的索引
  • key 理论抉择的索引
  • key_len 索引的长度
  • ref 与索引作比拟的列(如果是应用的常数等值查问,这里会显示 const,如果是连贯查问,被驱动表的执行打算这里会显示驱动表的关联字段)
  • rows 要检索的行数 (估算值)
  • filtered 查问条件过滤的行数的百分比(存储引擎返回的数据在通过 server 过滤后,有多少满足比例)
  • Extra 额定信息

    • distinct
    • Using filesort:不能通过索引进行排序,须要额定的索引操作
    • Using index:应用笼罩索引
    • Using temporary:应用长期表

3. order by

  1. 全字段排序
  2. rowid 排序:全字段长度太大,一个文件中放入的行数少,文件多,须要很屡次归并排序,那么缩小不必要的字段,只保留排序字段和 id,最初从主键索引按序返回
  3. 联结索引:防止排序
  4. 笼罩索引:防止二次查问

4. 幻读

  1. 语义问题:并没有保障所有的行都被选中
  2. 数据不统一:先插入后更新,bin log 记录的和理论产生的不统一
  3. 间隙锁:开区间
  4. next-key lock:前开后闭区间
  5. 加锁准则:

    1. 准则 1:加锁的根本单位是 next-key lock。
    2. 准则 2:查找过程中拜访到的对象才会加锁。
    3. 优化 1:索引上的等值查问,给惟一索引加锁的时候,next-key lock 进化为行锁。
    4. 优化 2:索引上的等值查问,向右遍历时且最初一个值不满足等值条件的时候,next-key lock 进化为间隙锁。
    5. 一个 bug:惟一索引上的范畴查问会拜访到不满足条件的第一个值为止。

5. 主备统一

bin log -> dump_thread -> io_thread -> sql_thread

bin log 格局

  • statement:SQL 语句(当应用 Limit 时,如果索引抉择不同,可能造成主备更改不统一)
  • row:逻辑更改
  • mixed

双 M 构造,互为主备

可用性:

  1. 备库机器性能差
  2. 备库跑一些经营剖析内容
  3. 大事务执行很久

主备切换的策略:

​ 可靠性优先:(一旦检测到主备差距达到阈值),则主库改为 read only,期待备库追赶

​ 可用性优先:备库间接可读写,可能产生数据不统一(同时接管客户端申请和 relay log)

可用性取决于主备提早

问题:

  • 如何正确的主备切换?次要是如何持续同步 relay log:GTID(global transaction ID)
  • 读写拆散哪些坑?过期读:semi-sync、判断是否齐全同步、强制走主库

6. 如何抉择驱动表?

应该尽量抉择行数少的表(小表)作为驱动表。

当可能应用被驱动表的索引时,就能够走树搜寻。Index Nested Loop

不能应用索引时,就将每行取出,逐个比拟。Block Nested Loop

MRR:multi-range-read

BKA:Index Nested Loop 为了用上 MRR

目标:应用上被驱动表的索引

长期表上建设索引

哈希 join,而不是无序数组

7. 长期表

长期表只在对其余线程不可见,session 完结时主动删除,可用于排序

退出移动版