【MySql知识点】

Mysql结构

更新步骤

隔离级别

我们来看看在不同的隔离级别下，事务 A 会有哪些不同的返回结果，也就是图里面 V1、V2、
V3 的返回值分别是什么。

• 若隔离级别是“读未提交”， 则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果
  已经被 A 看到了。因此，V2、V3 也都是 2。
• 若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看
  到。所以， V3 的值也是 2。
• 若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这
  个要求：事务在执行期间看到的数据前后必须是一致的。
• 若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A
  提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

索引

• 根据叶子节点的内容，索引类型分为主键索引和非主键索引。
  主键索引的叶子节点存的是整行数据，非主键索引的叶子节点内容是主键的值。
• 基于主键索引和普通索引的查询的区别
  基于非主键索引的查询需要多扫描一棵索引树，先通过普通查询获取主键ID，再查询主键索引；回到主键索引树搜索的过程，这个过程称为回表。

  覆盖索引（避免回表的情况）

  • 只查询主键ID。非主键索引树查询即可实现。

  索引下推

  • 在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。
    而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程
    中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

  错选索引

  • 由于索引统计信息不准确导致的问题，你可以用 analyze table xx。
  • 而对于其他优化器误判的情况，你可以在应用端用 force index 来强行指定索引，也可以通过修
    改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。

Mysql锁

• 全局锁

  • 全局锁的典型使用场景是，做全库逻辑备份。MySQL 提供了一个加全局读锁的方法，命令是
    Flush tables with read lock (FTWRL)。
  • 官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时
    候，导数据之前就会启动一个事务，来确保拿到一致性视图。（这个实现只有在支持可重复读这个隔离级别的引擎）

• 表级锁（MDL）

  • MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。

  • 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

  • 读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线
    程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
    

  • eg：默认添加的MDL锁可能导致的问题：给一个小表加个字段，导致整个库挂了。
    查询表时sessionA和B加的MDL读锁，sessionC添加字段事务阻塞，导致后面的查询查询都堵塞，变得完全不可读写了，如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session再请求的话，这个库的线程很快就会爆满。

  • 在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释
    放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

死锁

• 这时候，事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。
  事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。
  当出现死锁以后，有两种策略：
  一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout 来设置。（默认值50，时间太长，设置太短容易误伤非死锁，即锁等待）
  另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事
  务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

事务

事务隔离

• 实现MVCC中的快照，这个快照是基于整个库的，这个快照并非所有数据，而是基于transaction id实现；InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。
• 即使在同一个事务中，一开始的查询拿的是当前版本的数据，更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读（current read）”。所以在更新前会拿到最新版本的数据。

普通索引和唯一索引

• 涉及大量的更新操作字段（写多读少，反之则起副作用），尽量使用普通索引。如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。
• redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

Mysql IO

• InnoDB 刷脏页的控制策略，通过设置 innodb_io_capacity；它会告诉 InnoDB 你的磁盘能力。这个值我建议设置成磁盘的 IOPS。（磁盘的 IOPS 可以通过 fio测试）

行锁&间隙锁

• 使用for update会对指定行添加对应的行锁

log

binlog

• binlog 有两种格式，一种是 statement（记录sql原文），一种是 row（记录主键ID）。可能你在其他资料上还会看到有第三种格式，叫作 mixed，其实它就是前两种格式的混合（row很占空间，但准确）。mixed 格式可以利用 statment 格式的优点，同时又避免了数据不一致的风险。
• 目前主流还是row，目的在于更好的恢复数据
• 双M结构，binlog还记录了serverId，通过幂等判断，防止循环复制

主备结构

• 主备通过binlog来实现一致性
• 主备延迟：
  1.备库所在机器的性能要比主库所在的机器性能差。解决：主备库选用相同规格的机器，并且做对称部署
  2.备库的压力大。解决：一主多从
  3.大事务。解决：不要一次性地用 delete 语句删除太多数据
  4.大表 DDL。解决：这就要通过并行策略解决备库的并行复制能力
• 并行复制策略（最小粒度是事务）
  1.MySQL5.6 基于schema的并行复制（按库并行）：同库事务放到同一个work执行；效果不大，不太适用
  2.MySQL5.7 基于group commit的并行复制：同一组提交的事务可以并行执行；缺点大事务需要等待。mysql版本进行优化，在redo log prepare阶段就可并行
  3.MySQL8.0 基于write-set的并行复制
• 主备切换策略
  1.可靠性优先策略：判断备库延迟小于某个值，把主库设为readonly，备库再取消readonly。切换过程中存在不可用时间
  2.可用性优先策略：直接把连接切到备库 ，并且让备库 B 可以读写，那么系统几乎就没有不可用时间。存在问题数据不一致。可后期使用binlog恢复。
• 主备切换binlog通过GTID保持数据一致
  1、GTID就是一个由主库命名的唯一ID，并且随binlog发送到从库。
  2、从库在拿到binlog时，先判断GTID是否存在，若存在则忽略此条log，若不存在则执行并加入自己的GTID集合
• 读写分离
  1.无法接受过期读，使用等主库位点方案和等GTID，缺点从库无法查询，压力都会集中在主库
  等位点：根据主库binlog日志的位置去从库binlog判断，存在即在从库读取，反之回主库查询
  等GTID：根据事务执行返回的GTID去从库查询，缺点和等位点一致
  2.可接受过期读方案：判断主备无延迟方案；配合 semi-sync半同步（同步binlog返回ack确认）
• 误删库表
  1.使用全量备份数据恢复出一个临时库，或者change replication filter replicate_do_table = (tbl_name) 命令，就可以让临时库只同步误操作的表；然后取指定时间的binlog日志应用到临时库
  2.延迟复制备库，搭建延迟复制备库，设置N秒延迟，在指定时间内发现误操作使用stop slave，就可恢复出需要的数据。
  误删库表预防
  1.读写账号分离，开发DML只给读权限
  2.制定规范，先对表名做修改，等无影响后再delete这张表
• 生产库操作规范
  生产库操作写好四个脚本：备份脚本、执行脚本、验证脚本和回滚脚本

KILL失效

分为kill connection 和kill query

• kill 无效的第一类情况，即：线程没有执行到判断线程状态的逻辑（线程执行过程都有埋点）。跟这种情况相同的，还有由于 IO 压力过大，读写 IO 的函数一直无法返回，导致不能及时判断线程的状态。
• 另一类情况是，终止逻辑耗时较长（1.大事务kill，2.大查询回滚，3.DDL最后阶段被kill）

表数量大导致MYSQL连接慢

• 客户端连接成功后需要做三个操作：1.执行 show databases；2.切到 db1 库，执行 show tables；3.把这两个命令的结果用于构建一个本地的哈希表。第三步的效果就是tab键自动补全表名。
• 操作：
  1.mysql命令加上-A

JOIN

1.如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
2.如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用

• 选择（前提是可以使用被驱动表的索引）
  1.使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；
  2.如果使用 join 语句的话，需要让小表做驱动表。
• （BNL算法）A(100行) join B(1000行)当未使用上索引时，会将A表数据放到内存，再依次取B表数据进行对比，返回满足join条件的数据，扫描行数为1100，但是判断次数100*1000，由于此操作在内存中完成，会比SNL算法快很多
• 如果你的 join 语句很慢，就把 join_buffer_size 改大。

自增主键ID不连续

• 唯一键冲突是导致自增主键 id 不连续
• 回滚也会产生这种现象

快速复制表

• 将数据导成insert语句（.sql文件）
  mysqldump -h $ host -P $ port -u $ user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where=“a>900” --result-file=/client_tmp/t.sql
• 导出CSV文件
  select * from db1.t where a>900 into outfile ‘/server_tmp/t.csv’;
• 物理拷贝