主备

  1. 主备切换流程
    45_ms
    状态1，客户端读写都只访问节点A，B是从库，同步A的更新，保证AB数据相同
    需要切换的时候，就变成状态2，客户端只访问节点B
  • 状态1不会操作B，但是建议将B设置为read only
    • 防止他人误操作B
    • 防止切换逻辑出现双写的问题
    • 判断访问节点的角色
    • read only对超级管理员无效，所以可以同步更新的线程有超级权限

  1. binlog statement

    • statement就是记录SQL语言，甚至会把注释也都记录进去
    • statement的格式下，delete带有limit，就是unsafe的，会导致主从不一致
    • 删除条件的两个都有索引，如果主从版本不一，可能选择的索引不一致，所以顺序不同，导致delete的东西不同，本质上就是主从库可能delete的时候用到的索引不同，因为有limit存在，比如只删一条数据，就会导致数据不一致。

  2. binlog row

    • row里面多了Table_map和Delete_rows
    • Table_map用于说明接下来操作哪个库哪个表
    • Delete_rows用于定义删除行为
    • 实际上，row虽然会占用空间，但是有助于恢复数据，比如误删，可以根据binlog看到，直接插入即可；误增误改都可以这么处理

  3. binlog mixed

    • 如果有大量的删，那么row就会记录大量的内容，导致磁盘IO变多，占用空间变多
    • statement就不会，只会有一条语句，但是在某些情况下，会导致不一致
    • 所以mixed判断，如果不会导致不一致就用statement，如果可能会不一致，就切换到row
    • 这个格式用的不多

双M

45_2m

  • 建议设置log_slave_updates=ON，从库执行完relay log以后也生成binlog
  • A更新事务，binlog记录A的server id
  • B接收到binlog，更新，并记录server id也就是A的server id
  • 再传回A，A判断server id等于自身，所以就不会处理日志

并发连接和并发查询

  • 线程等待锁的时候，并发线程计数减一
  • 因为等待锁的时候不吃cpu资源
  • innodb_thread_concurrency设置最大并发线程数

检测MySQL

  • 再mysql库中创建一个health_check表，只放一行数据，定期执行
    mysql> select * from mysql.health_check;

  • 可以检测因为并发线程过多导致的数据库不可用的问题

    • select 1不行，因为不涉及InnoDB引擎，就算超出限制也会返回
    • 但是这样的话，如果binlog磁盘占满了，也会阻塞commit事务，但是不会阻塞返回，所以这种方法无法判断是否存在磁盘满的情况

  • 更新判断：放一个timestamp字段，表示最后一次执行检测的时间：mysql> update mysql.health_check set t_modified=now();

    • 对主从库都需要执行更新检测
    • 为了主备更新不冲突，再mysql.health_check上存入多行数据，用两个库的serverid作为主键
    • 但是可能IO已经100%了，此时正好执行到update语句，那么就无法判断这种情况，不能及时切换
    • 可以通过检测performance_schema里面redolog和binlog的写入时间判断是否存在问题，但是性能损耗10%

误删数据

  • delete误删某一行，那么就用binlog恢复即可，需要binlog=row，binlog_row_image=FULL
  • 可以将sql_safe_updates=ON预防，如果delete或者update没有写where条件，或者where条件没有索引，就会报错
  • 如果需要删全表，则使用truncate table 或者 drop table
  • 但是如果drop或者truncate，无法使用binlog恢复，只能全量备份+增量恢复
  • 如果对于核心库，可以使用延迟备份，减慢binlog到从库执行的时间，避免被删

kill

  • 两个kill命令，kill query + id 和 kill connection + id
  • 发送kill命令以后，session运行状态改为THD::KILL_QUERY，并给session发送一个信号
  • kill无效
    • 线程没有执行到判断线程状态的逻辑
    • 终止逻辑耗时较长
      1. 大事务被kill，需要回滚
      2. 大事务回滚包含较大的临时文件，压力过大，耗时长
      3. DDL最后阶段
    • ctrl+c命令是服务器开启一个新的线程发送kill query命令

全表扫描

  1. 如果扫描一张大表，就获取一行数据，存储到net_buffer中，内存大小由net_buffer_length控制，默认16K
  2. 重复获取行，直到net_buffer写满，发送
  3. 发送成功清空net_buffer，重复
  4. 发送返回EAGAIN或者WSAEWOULDBLOCK，表示本地网络栈写满，进入等待，直到网络栈重新可写，再继续等待
  • 所以如果客户端接收的慢，事务执行时间就会变长
    • 可以使用mysql_store_result，将收到的内容存入客户端内存，加快接收速度
    • 但是如果是大查询，就会占用大量内存，就需要使用mysql_use_result

innodb_buffer_pool_size一般设置为可用物理内存的60%-80%，线上服务的内存命中率需要达到99%，可以通过show engine innodb status查看Buffer pool hit rate

  • InnoDB淘汰数据页用的LRU的改进
    • 因为如果对一个不常用的表使用全表扫描，这时候就会将别的业务正在使用的内存给顶掉，导致别的业务缓存失效
    • 所以InnoDB按照5:3的比例将LRU分为了young和old区，
      45_youndold
    • 如图，状态1，访问P3，P3在young区，按照LRU，移到链表头部，变成状态2
    • 此时，要插入新的数据页，淘汰Pm，但是新的数据页Px插入LRU_old处
    • 处于old区的数据页，每次被访问都要做下面的判断：
      • 如果数据页存在超过1s，就移动到链表头
      • 少于1s，位置不变，innodb_old_blocks_time=1000，表示1s
      • 这样扫描大表也用到了buffer pool，但是对于young区没有影响

join

  • join应该将小表作为驱动表，大表作为被驱动表，因为InnoDB只会用被驱动表上的索引，对于驱动表，也就是join前面的表，用的是全表扫描。如果驱动表是行数N，被驱动表M被驱动表上索引查询，所以如果回表，就需要在普通索引和主键索引上都查询一次，那么时间复杂度为2log2(M)2log_2(M),驱动表上查询NN行，所以总共是N+2log2(M)N+2log_2(M)，这就是NLJ
  • 这是仅限于可以使用被驱动表的索引，如果被驱动表没有索引，就会使用BNL
    • 将驱动表的数据取出放到join_buffer中，
    • 将被驱动表的每一行跟join_buffer中的数据对比，满足条件放到结果集中
    • 总时间复杂度就是NMNM
    • 这样的话，时间复杂度就是两个表的全表扫描N+M+NMN + M + NM
    • 如果驱动表较大，无法放到join_buffer中，就会分段放，join_buffer_size默认256k
      • 就会将驱动表放一部分行，直到join_buffer放满为止，然后将被驱动表取出比对，再清空join_buffer，放驱动表的下一部分数据，直到比对结束
      • 假设N行驱动表分K段，N越大，K越大，所以K表示为λ×N\lambda \times N，λ∈(0,1)\lambda \in (0, 1)
      • 扫描行数就是N+λ×N×MN + \lambda \times N \times M
      • 判断N∗MN*M次
      • 所以应该让小表当驱动表
  • 如果join很慢，就把join_buffer_size改大一些
  • 尽量让join走NLJ，并且警惕explain检查字段是否出现Block Nested Loop
  • 在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。
  • join比强行拆分单表查询性能更好

Multi-Range Read(MRR)

  • 如果一张表有主键id索引，普通索引a，通过a索引扫描，有时候需要回表查询主键
  • 此时a虽然是在索引上顺序查询的，但是因为需要回表，所以在主键id索引上就是乱序查的。但是大都数数据都是通过主键自增顺序插入得到的，所以可以认为按照主键递增顺序查询的话对磁盘接近顺序读，性能会更好
  • 所以MRR就是先根据索引a找到数据，放到read_rnd_buffer中，(随机读缓冲区)，将缓冲区中的数据按照id递增排序，然后依次在主键中查找。大小由read_rnd_buffer_size控制，如果buffer放满了就会先执行排序，然后查找id返回结果，清空buffer再进行下一步
  • 如果想要使用MRR，set optimizer_switch="mrr_cost_based=off"，因为优化器不会判断时使用MRR，所以设置为off，固定使用MRR

Batched Key Access(BKA)

  • 当使用join的时候，驱动表一行一行到被驱动表比对，用不上MRR

  • 现在将驱动表的内容拿出来，放到join_buffer中，原本join_buffer在BNL里才有暂存驱动表的数据，在NLJ没有用，现在NLJ也会用到

  • 然后通过join_buffer跟被驱动表比对

  • set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';通过这条语句开启MRR和BKA，因为BKA基于MRR

  • 如果join使用BNL，多次扫描一个冷表，语句执行超过1s，再次扫描冷表就会将数据页放到LRU头部

  • 对应的情况就是冷表的数据量小于buffer pool的3/8，能够完全放到old区中。

  • 如果冷表很大，业务正常访问的数据页，就没有机会进入young区，这是因为正常访问的数据页要进入young区的话，需要隔1s再被访问到，但是冷表很大，old区在1s内就淘汰了正常数据页，就会导致buffer pool没有这个数据

  • 所以大表JOIN不仅会影响IO，IO在join结束也就恢复了，还会影响buffer，需要后面慢慢恢复

BNL对系统的影响

  1. 多次扫描被驱动表，影响IO
  2. join条件需要执行M×NM \times N次对比，大表占用非常多资源
  3. 导致buffer pool热数据被淘汰，影响内存利用率

  • 可以尝试给被驱动表添加索引

  • 如果不适合加索引，比如次数比较少，添加索引不划算。可以建临时表，给临时表加索引，再join即可，优化很好

  • 或者自己在业务端实现hash join，会比临时表优化的还好，select * from t1驱动表t1所有数据存入hash结构，比如set之类，select * from t2 where b>=1 and b<=2000获取被驱动表的2000条数据，然后一条一条在业务端跟hash表内容比对即可。临时表需要比较1k×2k1k \times 2k次，但是hash join只需要比较2k次

  • 临时表可以用各种引擎，但是内存表只用memory引擎，一般由mysql自动创建销毁

  • 临时表建表语法时create temporary table ...

  • 一个临时表只能被创建它的session访问，其他session不可见

  • 临时表可以与普通表重名

  • 如果有同名的，那么show create和增删改查访问临时表

  • show tables不显示临时表

  • 所以临时表不需要担心重名问题，可以用来给join使用，而且不需要担心数据删除问题

  • 分库分表跨库查询就会使用临时表

  • 创建临时表的时候，InnoDB创建一个frm文件保存表结构定义，前缀是#sql{进程 id}_{线程 id}_ 序列号
    5.6版本之前，会在临时文件目录下创建一个相同前缀.ibd存放数据文件，5.7版本后有专门的临时表空间存放临时数据，因为前缀不同，所以可以创建同名临时表

  • 但是普通表是库名+表名得到的，同一个库创建两个同名表就会报错

  • 当session结束的时候，就会执行drop temporary table + 表名

  • 如果binlog不是row，就会记录临时表的操作，不然会报错，提示临时表不存在。但是row格式只会记录数据，所以不会有对临时表的操作，不会发现临时表不存在这种问题，就不会记录临时表

内存临时表

  1. union的explain会显示using temporary，表示使用临时表，将union前的结果存在临时表中，然后利用主键唯一性剔除第二个查询中的相同的结果
  • 如果是union all，就没有去重了，所以也不需要临时表了
  1. group by

  • 创建内存临时表，表上两个字段m和c，主键是m

  • 扫描t1上的索引a，取id，并%10记x

    • 临时表没有x的行，那么插入x,1
    • 如果有x的行，那么对应的c+1
    • group by处理完以后默认会对字段m按照自然顺序排序，如果不想排序提高效率，就可以直接加上order by null
      tmp_table_size是内存临时表的大小设置参数，默认16M，超过大小会转为磁盘临时表

  • 不管是内存临时表还是磁盘临时表，group by都需要构建一个带唯一索引的表，执行代价高

  • 5.7版本支持generated column机制，用来实现数据关联更新alter table t1 add column z int generated always as(id % 100), add index(z);

  • 这样索引z上的数据就是group by有序的数据了group by id %100就可以改写为select z, count(*) as c from t1 group by z;此时不再需要临时表，也不需要排序，直接返回即可

  • group by语句使用SQL_BIG_RESULT提示，可以告诉sql数据量很大，直接使用磁盘临时表，然后优化器还会将原本的B+树直接改为数组存储 ，两次优化
    select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

  • 初始化sort_buffer，放入整型字段m

  • 扫描t1.a，取id%100放入sort_buffer

  • 对sort_buffer中字段m排序

  • 排完序就是有序数组

  • 这样就不会使用临时表

InnoDB把数据放在主键索引上，其他索引保存主键id就是索引组织表IOT

  • 总是有序

  • 有空洞时，插入新数据只能放在固定位置，从而保证有序

  • 数据位置改变，只需要修改主键索引

  • 主键索引查一次，普通索引查两次

  • 变长数据
    Memory数据单独存放，索引上保存数据位置，就是堆组织表HOT

  • 按照写入顺序

  • 有空洞时找到空位就可以插入新数据

  • 数据位置改变，需要修改所有索引

  • 所有索引地位相同

  • 不支持变长，定义varchar，也是 按照char(N)

  • 内存表锁表，并且丢失会有风险，一般不会用

    • 但是内存临时表不一样，因为不会被其他session访问，所以没有并发性问题
    • 本来重启就是要删除的，所以丢失没关系
    • 备库临时表也不会影响主库
    • 支持hash索引

  • MyISAM主键自增值在数据文件中

  • InnoDB主键自增值在内存中，8.0版本实现持久化

    • 5.7版本前没有持久化，保存在内存，重启后就找表的最大值，最大值+1作为自增值
    • 如果id=10，则AUTO_INCREMENT=11，删除id=10，还是11，如果删除以后重启，AUTO=10
    • 8.0版本变更记录在redo log中，重启依靠redo log恢复之前的值

  • 插入一行数据，指定为自增，判断id=0，null，或者没有指定，就把表的自增值赋值给他

  • 如果id指定了值，就使用指定值；如果指定值小于自增值，则自增值不变；

    • 如果指定值大于等于自增值，就修改自增值，就是auto_increment_offset开始，auto_increment_increment为步长叠加，找到第一个大于指定值作为新的自增值。两个参数默认都是1

  • 如果插入数据，自增主键，但是插入数据失败，数据重复了，那么数据无法插入成功，并且主键自增值也没有改回去（唯一键冲突会导致主键自增不连续）

  • 事务回滚也会导致自增主键不连续，为了性能，因为申请id很快，但是回滚id可能会导致主键冲突，没有必要

  • 批量insert ... select， replace ...select, load data的场景下设置innodb_autoinc_lock_mode=2可以提升并发性，并且不会出现数据一致性问题