JZTXT

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

发布时间 2023-06-24 12:49:55作者: 程序杰杰

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

    （个人理解：DDL、DML按照申请顺序排队执行）[DML加MDL读锁，DDL加MDL写锁，读写之间互斥]

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

  2. change buffer存储inser 和 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-kye-lock，即前开后闭
      • 访问到的才会加锁
    • 2个优化（都是针对索引的等值查询）
      • [等值查询时]唯一索引存在，则next-kye-lock退化为行锁
      • [等值查询时]向右遍历最后一个值不满足，则next-kye-lock退还为间隙锁，即前开后开
    • 1个bug
      • 唯一索引的范围查询也会访问到不满足第一个值为止（即不论你是普通索引还是唯一索引，找到当前值，也会继续找下一个，即使唯一索引下一个值一定不满足，也会找，访问到的就会加锁）