Mysql-二刷一些重要知识点记录

  1. 执行DDL的时候,即使此DDL被其他DML阻塞了,但是后续DML都会被此DDL阻塞

    (个人理解:DDL、DML按照申请顺序排队执行)[DML加MDL读锁,DDL加MDL写锁,读写之间互斥]

    使用online ddl也就不害怕线上DDL了

  2. change buffer存储insert 和 update的数据。如果不马上查询,起到加速DML的作用

  3. [尽量使用普通索引代替唯一索引]唯一索引因为要校验唯一性,因此不会使用change buffer

  4. [索引长度尽量控制短一些]索引一页的大小16B,因此长度过长单页上存储的数据就少,查询效率就低一些,因此对于一些字符串索引使用 select count(distinct left(room_id, 4)) from live_playback 来找到包含95%的长度即可

  5. [Mysql策略:内存中有一定是正确的,内存中没有磁盘一定是正确的]

    因此从内存淘汰脏页会flush到磁盘的

  6. [监控redo log和脏页使用率]

    因为redo log满了则mysql的update等只能阻塞等flush到磁盘后操作了,业务无法接受的性能。

    因为脏页过多,如果一次查询需要淘汰的脏页多的话,需要等flush到磁盘后再执行,业务无法接受的性能。

  7. [删除记录不会让空间缩小]

    数据页单个记录删除,删除的记录被标记为可复用(复用的位置,新增的记录必须满足原记录的范围关系,比如删除的记录左右两边范围是(600,800),那只有这个区间的插入才能复用到)

    数据页整个页删除,删除的页被标记为可复用(这个页可以被直接应用)

    随机插入,如果某个数据页满了,会也分裂,导致原页尾部空间的浪费

  8. 重建表alter table t engine=InnoDB每个数据页会预留1/16的空间,因此其也不是最紧凑的。这个命令在mysql5.6后是online ddl了

  9. [count(*)效率最高,因为Mysql针对其进行了优化]

    首先,其会在逻辑正确下,找最小的索引数进行统计
    然后,它也不会取出字段值,直接让server层累加

    count(字段)<count(主键id)<count(1)≈count(*)

  10. [order by无法借助索引,则会使用设定的内存排序,大小不足借助磁盘使用归并排序]

    select a,b,c from table where b='xx' order a limit 1000

    • 全字段排序
      • 所有需要返回字段长度<设定的max_length_for_sort_data,排序使用(a,b,c)全字段
    • rowid排序
      • 排序使用(a,id)字段排序,排序后根据limit 1000 按照id回表聚簇索引,将a,b,c返回
    • 联合索引(b,a),即可通过索引避免排序
  11. [using temporary使用memory内存引擎,因此再其基础上的排序,优先使用rowid]

    因为内存排序,字段越少,sort buffer空间有限时,能排序的越多

    当然,小于tmp_table_size使用memroy内存临时表(默认16M),大于的话只能使用磁盘临时表了

  12. [索引失效:加函数、类型隐式转换、字符集不一致]

    说到底都是因为查询的字段加了函数,导致索引失效

    • mysql默认将字符串转为数值,因此 varchar(32) b = 100,实际是cast(b int) = 100,索引失效
    • 表的字符编码不一致,mysql默认转为父类的编码进行处理
  13. [字符串查询大坑]

    https://www.cnblogs.com/ningxinjie/p/17336895.html

    [最下面上期问题时间]内详细说明

    根据字符串字段设定长度阶段 + 查询 + server判断

    比如字段b是varchar(4),数据库中b的值为1234有10万行,我们要查询b='12345',mysql会先截断前4位1234,然后查询,因为select *,因此一个个回表,然后server判断1234!=12345,最后返回为空。

    因此这条查询语句执行是非常慢的

    那业务该如何避免呢?

    1. b区分区太低了,比如1234有10万行,这种如果是随机产生的,直接存到es中,使用倒排索引形式供用户或者业务查询
    2. 如果是业务含义,长度固定为4的话,则查询前校验合法性,比如长度不是4那不合法,直接报错返回,就不要查db了
  14. [RR引入间隙锁的目的是为了解决binlog记录内容回放与实际执行不一致]

    在读已提交下,不引入间隙锁,使用binlog的row格式即可解决(不过测试感觉并没有锁住那一行,执行更新的时候其他事务提交了,也会顺带把后续提交事务的那行数据改了,所以我感觉这种配置的作用是为了防止其他场景的数据不一致)

  15. [加锁分析精髓都在这了:https://www.cnblogs.com/ningxinjie/p/17336904.html](针对读已提交的隔离级别)

    • 2个原则
      • 加锁单位是next-key-lock,即前开后闭
      • 访问到的才会加锁
    • 2个优化(都是针对索引的等值查询
      • [等值查询时]唯一索引存在,则next-key-lock退化为行锁
      • [等值查询时]向右遍历最后一个值不满足,则next-key-lock退还为间隙锁,即前开后开
    • 1个bug
      • 唯一索引的范围查询也会访问到不满足第一个值为止(即不论你是普通索引还是唯一索引,找到当前值,也会继续找下一个,即使唯一索引下一个值一定不满足,也会找,访问到的就会加锁)
  16. [next-key-lock实质是间隙锁+行锁,加锁过程也是分两段加,先加间隙锁,再加行锁]

    间隙锁之间不冲突,行锁之间冲突

    sessionA sessionB
    begin
    select id from t where c=10 for update
    select id from t where c=10 for update
    [block]
    insert into t value(8,8,8)
    error 1213
    检测到死锁

    sessionA执行的语句,加(5,10]next-key-lock和(10,15)的间隙锁

    虽然sessionB被block了,但是其加锁过程是:想要加(5,10]的next-key-lock,具体过程是先加(5,10)的间隙锁,加锁成功,再加10的行锁,被block了,因此sessionA需要插入数据时被sessionB的间隙锁阻塞了,造成了死锁!

  17. [mysql是如何保证redo log与binlog完整性的]

    原文地址:https://www.cnblogs.com/ningxinjie/p/17336913.html

    write:是写到操作系统的page cache

    fsync:是刷到磁盘(占用磁盘 IOPS)

    引言:我们都知道redo log + binlog可以做到崩溃恢复(crash - safe),那redo log与binlog的完整性mysql是如何保证的呢?

    • binlog:

      • 要点:事务提交后才能写入binlog,且要保证事务的完整性。(因为记录到binlog备库就要使用了,因此事务未提交绝对不能记录到binlog)

      • 先写binlog cache(每个线程/会话 1份,大小不足使用磁盘临时文件来记录),再写binlog文件

      • sync_binlog 含义
        0 每次提交只write,不fsync
        1 每次提交都会fsync
        N 累计N次提交后fsync
    • redo log

      • 要点:和binlog不同,其事务是否提交记录到redo log,备库也不用,因此无所谓

      • 先写redo log buffer(共用1个),再写redo log文件

      • innodb_flush_log_at_trx_commit 含义
        0 每次提交只把redo 留到redo log buffer中
        1 每次提交都会把redo log buffer fync到磁盘
        2 每次提交都会把redo log buffer write到page cache
    • 双1配置:

      • sync_binlog=1&innodb_flush_log_at_trx_commit=1
    • mysql的优化

      • redo log的组提交:redo log buffer空间是共享的,因此在一个事务提交时,会连同其他事务的redo log一起fsync,这些被连带提交的事务,在真正提交时发现自己需要提交的redo log已经被fsync了,就不必再操作了。(使用LSN的概念来记录长度的,这个说后文会讲,先记录下)

      • binlog的组提交:因为binlog是每个先成一份cache,因此在binlog cache没法使用组提交,因此mysql将binlog的write后,需要fsync时,将write的部分一起fsync。

      • binlog_group_commit_sync_delay 表示redo log repare阶段fsync后延迟多少微秒后再执行binlog的fsync
        binlog_group_commit_sync_no_delay_count 表示累计多少次以后再执行fsync

        以上表格binlog希望尽量应用到组提交的属性,是或的关系(即满足1个就fsync了)

    • 说明一点:fsync是针对redo log的prepare阶段和binlog的写入阶段的。redo log的commit阶段是直接write的,因为有了前两个阶段就已经能实现crash-safe了,因此redo log的commit阶段不直接刷盘了!

    • 个人画个图串一下理解

    • 补充:redo log的组提交的个人新理解:第一个事务提交的时候,会等待一小段时间,如果这段时间其他事务也提交了,就一起刷到redo log中(标记这些为prepare阶段)。针对其他正在进行中的但是未提交事务,我理解就是不会标记prepare阶段

    • [当主从延迟变大时,从库可以修改redo log和binlog的刷盘时机,来加速主从同步]

  18. [主从延迟second_behind_master的计算值 = 从库执行完SQL时间 - 主库提交binlog的时间]

    1. 主库执行完语句,从binlog cache写入binlog,时间为T1

      细说就是:redo log的prepare阶段写入完成,binlog写入,此时就会同步了,而不用等redo log的commit阶段

    2. 主库发送给从库,从库接收并记录到relay log,时间为T2

    3. 从库执行relay log中的语句,时间为T3

    second_behind_master = T3 - T1

    【通常,seconds_behind_master 的值是根据从库正在执行的 relay log 语句的时间戳与最新接收的 relay log 语句的时间戳之间的差异来计算的。这个差异表示了从库在执行当前语句之前所滞后的时间。】

  19. [只有在事务提交的时候,才会写入binlog,从库才会收到]

    这部分内容在上方有说明,但是因为写的太长了,就把要点单独抽出来写一下

    事务未提交的时候

    • 对于binlog:语句一定在binlog cache(线程隔离、事务隔离)

    • (我个人的理解,因为按照这个理解,下面才能串通)对于redo log:语句不一定都在redo log buffer,因为组提交可能随着一个事务的提交,已经写入了redo log。第一个事务需要提交时,稍等一小段时间再提交

      • 这段时间提交了的事务,则同时记录prepare阶段
      • 这段时间未提交的事务,则只记录到redo log不标记阶段
  20. [为了减少主从延迟,从库尽量针对不同事务并行复制]

    原文地址:https://www.cnblogs.com/ningxinjie/p/17336925.html

    以下2个为原则:

    1. 同一个事务不拆解(为了保证从库查询事务的一致性)【因此1个大事务不会被拆解并行同步
    2. 操作同一行的不同事务,不能并行(为了保证主从数据的一致性)

    总结下从库并行复制的思路:

    1. 按表复制
    2. 按行复制(采用这个)

    只要不同事务不涉及同一个行,就可以并发,执行逻辑如下:

    1. 不同事务不涉及处理同一行,直接分配给空闲worker处理即可
    2. 涉及同一行,且与多个worker冲突,阻塞等待
    3. 涉及同一行,且只与一个worker冲突,分配给那个冲突的worker排队执行,不再阻塞,继续分发并行后置relay log

    期间纠正到的知识点:

    1. redo log的组提交是第一个事务在提交的时候等待一小段时间,如果期间有其他事务也提交了,就一起写入redo log的prepare阶段
    • 处于prepare一起可提交的,和redo log commit期间的,这两部门的事务都是可以直接并行执行的(因为如果涉及到行锁,他们就不可能处于同时提交状态)

    总结:

    记住按行复制的思路即可(除了id,也得考虑唯一键等)。

    事务A:拆解涉及到哪些行修改

    事务B:拆解涉及到哪些行修改

    无冲突则并行,有冲突则看是否只有一个冲突,是则排队,否则阻塞等待

  21. [GTID——切到新主库 和 新接从库 的优雅稳定的方式]

    GTID模式默认选取的主库包含全部的数据,如果新选择的主库比从库少数据,那GTID模式的主从同步会直接报错!

    对于当前已经存在得GTID,数据库会直接忽略此条SQL。

    切新主库的时候:从库将自己的GTID发送给新主库,新主库验证全部GTID全都存在

    1. 不存在则直接报错,停止同步
    2. 存在则找到主库还未同步给从库最小的GTID语句,从此处开始同步给从库,从库对于GTID相同的直接忽略

    可使用以下两条命令查看是否开启了gtid(小红书这边是开启的)

    SELECT @@global.gtid_mode;

    SHOW GLOBAL VARIABLES LIKE 'gtid_mode';

  22. [间隙锁看最右边,如果左边边界被删除,锁的范围会被放大]

    锁的答疑文章:https://www.cnblogs.com/ningxinjie/p/17336941.html

    再说一句:间隙锁和next-key-lock要求隔离级别是可重复度!如果你是读已提交,就不会有这些锁!

    隔离级别的查询:SELECT @@tx_isolation;

    1. 比如sessionA加锁范围是(5,10],sessionB先删除5这行,再插入5这行就会被阻塞。因为当sessionB删除5这行时,sessionA的范围就变成(0,10](这里假设0是db存储5前面那个值

      sessionA sessionB
      begin
      select * from t where c > 5 and c <10 for update
      delete from t where c = 5
      此时 (5,10],因为5的缺失,锁范围变成(0,10]
      Insert t values(5,5)

      会被阻塞
      • sessonA的加锁过程
        1. (等值查询)先找到c>5的第一个值c=10,对其加next-key-lock (5,10]
        2. 因为c=10已经不满足了,因此停止了遍历

      多说一下:如果语句是select * from t where c > 5 and c <10 for update

      则加锁过程是(先等值找,再后面都是范围查询,范围查询就不会进行优化条件)

      1. (等值查询)先找到c>5的第一个值c=10,对其加next-key-lock (5,10]
      2. (范围查询,因此不会退还为间隙锁)继续向右遍历下一个c=15,对其加next-key-lock (10,15]
  23. [buffer pool 是引擎层的]
    [大表全表查询发挥,服务端是分批返回给客户端的,因此即使是超大表,也不会因为数据过多造成OOM]

    server层:

    net_buffer(每个线程独立各一个)

    本地网络栈(server层共享一个)

    1. 查询结果数据写到net_buffer中。这块内存大小默认16k,每个线程一个
    2. 当本线程的net_buffer内存空间写满,则调用网络接口发给客户端
    3. 如果发送成功则清空net_buffer,继续读取数据
    4. 如果发送函数返回EAGAIN或WSAEWOULDBLOCK,就表示本地网格栈写满了,只能阻塞等到本地网格栈有空间后才能继续。

    innodb层:

    buffer pool内存页的优化

    内存页采用LRU的变形(划分为young区和old区域(5:3的占比))。

    这个old和young跟之前理解相反,新读进来的内存页先放到old区头部,如果下次读取old区的内存页发现其已经在内存停留了1s则移到young区的头部(这样就避免了一部分历史大表的扫描,导致内存命中率降低)

  24. [领略个核心要点:分析为什么需要加锁,可以看最终的binlog会不会造成数据不一致,如果从库回放数据不一致,那就需要锁来保证]

posted @ 2023-06-24 12:41  程序杰杰  阅读(47)  评论(0编辑  收藏  举报