mysql

耐心是一切聪明才智的基础。

3范式理解

 字段不可拆分，不冗余，都和id相关。

InnoDB

• 支持事务，行级锁，外键（外键约束），崩溃恢复。
• 5.6之后都支持FULLTEXT。
  • 可以创建在char varchar text字段上创建。
  • 类似于like，但是比like快n倍，貌似对中文支持不好。
• 数据是存于聚集索引，而myisam数据和索引是分开文件存储的，可以更利于压缩，占用更小内存，适用于读插入频繁的操作。

其他数据库区别

• redis
  内存，提供数据结构类型。（字符，hash，队列，集合， 有序集合）
• hbase，clickhouse
  列式存储，不用一次把一行的数据加载到内存，更高的压缩率。行式修改比较费时，随机写。所以一般应用于离线大数据分析统计。
• 芒果db
  文档性数据库，不像关系数据库，列是固定的，修改结构快。
• es
  对倒排索引lucence的封装，类型mysql中的like，不过这个是专门做全文索引的，高可用，高性能。

表结构设计

• 最好 非null 给默认值
  https://mp.weixin.qq.com/s/nvBvrHjPojoVez8W2VqjGg
  null也是占空间 。
  null程序逻辑判断 需要判断 要不容易出现 空指针异常。
  不利于索引的优化。
• 时间类型 datetime 还是 int(百度是i) timeStamp
• 字段空间 合适 TINYINT 不用 int。
  mybatis用map去接受tinyint数据 长度为1时 会自动转为布尔。。
• 创建索引。

分库表

• 统计：主从
• 千万：
  分表 主要解决 查询问题 一个大树分成多个小树 查询快
  share jdbc（应用） mycat（proxy）
  分库 主要解决并发问题 提高吞度量
• 硬件方面 普通硬盘 固态硬盘 raid磁盘阵列 提高并发和建立副本

问题排查

• show processlist 命令查看当前所有连接信息
  show status like '%lock%';# 查询锁状态
  kill SESSION_ID; # 杀掉有问题的session
• 开启慢查询日志，查看慢查询的 SQL。

buffer pool

• free链（池外）+ LRU链（池内） = 全量数据

• flush链（LRU链的子级）记录需要刷新到硬盘的数据，刷新之后，添加到free链里。

• 判断数据是否在池内，有个数据页缓存哈希表key为表空间id+数据页id，value为缓存地址。

• 转换过程：

  • 启动时，硬盘数据会存在free链中。
  • 从mysql读数据是 free链->LRU链的过程。
  • 写入mysql 是LRU链节添加到flush链->写入到硬盘->free链的过程。
  • 定时把LRU链上的冷数据回收->free链。

• 预读机制

  • 一个区内的页数据被多次访问，会触发预读。
    在读表的数据会把数据加载到内存，所有可以按照这个机制，定时把热点数据常驻内存。
    https://www.cnblogs.com/wasitututu/p/13612605.html

分层

• 服务层
  连接层->词法，语法分析(有缓存走缓存)->
  优化层-> 执行层->
  缓存层（key是SQL语句，value是查询结果，类似于mybatis的二级缓存，all session共享）
• 存储引擎
  （Innodb，pool buff也算是缓存）

3层最大数据

• 下一层存储数据条数=上一层最大数据条数*一页数据条数
• 第一层，一条数据大小按照（8bitint+8索引）16byte来算，一页16kb，所以大约最多存1024条数据。
• 第二层，存储数据条数=1024\*1024 = 100w
• 第三层，一条数据大小按照1kb算，一页数据条数就是16.
  存储数据条数=100w\*16 = 1600w

https://juejin.cn/post/6953442154249191454

基数

• 列的不同值数量，例如性别列的基数为2。
  更新时机是默认大于条数10%时更新。
  值是估算的默认20个数据页抽查。
  可以主动去更新表的基数值。
  默认基数不会持久化，可以开启，提高重启速度。

记录数据插入的过程：

• 1.先锁定写入的数据页，表最大记录的数据页还有位置就用这个数据页，否则就在硬盘上找一个新的数据页。
  2.记录undolog，为提供事务隔离性。
  3.写入页缓存，存上flush链（异步刷新到磁盘，每秒会以一定比例落盘，当redolog不可用，mysql关闭，flush链数据达到一定比例也会触发落盘）。
  4.写入到redolog和binlog，写入redolog会有2次提交，第一次会写入状态标记为准备，第二次改为已提交。（两种日志格式个功能不一样，必须保证同步）

change buff

占有pool buff的一部分默认是25%，用于提升二级索引的写入性能。

慢查分析优化

• 默认long_query_time 是10s，所以一般都是需要调整这个参数。
  5.1之后，可以修改为table模式，会添加到表里记录。

show variables like '%slow%'；//查看相关的设置。
show processlist；
mysqldumpslow -s a1 -n 10  mysql.slow_log

优化器，索引，分表
优化器会考虑字段基数。
扫描行数，期望用二级索引，但是考虑到回表可能会直接走主键。
排序倾向于本来有序的字段。

MVCC

• readView+undolog
  readView在事务开始时生成，当前事务id，活跃事务ids，活跃最小事务ids，下一个事务id。
  undolog记录修改数据记录和事务id绑定。
  事务61事务62同时开启：
  [61, [61,62], 61 , 63]
  [62, [61,62], 61 , 63]
  事务1读id为1的记录，在undolog中有事务60的记录，因为60不在活跃事务里，所以是已经提交的可以直接读。
  事务2修改id为1的记录，undolog中会有事务60，事务62的记录。
  这是事务2没有提交，事务1再次读id为1记录拿到的是事务62的记录，对比在活跃事务里但是比自己大，不能查看，继续找到事务60的记录查看。
  这时候事务62提交了，事务61readview会更新为[61, [61], 61 , 63]，再去读undo中事务62的记录，发现不在活跃事务列表里了说明是已经提交的版本，可以查看。

undolog

• 事务隔离性，以及事务回滚。
  逻辑日志，记录了事务中操作中插入的id，修改和删除字段原值，已备回滚。
  日志格式会记录事务id，下一条undolog位置（形成一个链表），表空间字段值等一些信息。
  默认是共享表空间，可以通过设置单独文件存储，在MySQL5.7中允许用户在线truncate undo log。
  回滚是逻辑回滚。

redolog

• 崩溃恢复能力。
  innodb引擎为保证持久化，物理层面的，数据页偏移量，关注宕机后如何恢复已提交但是还没有落盘的数据。
  以redoblock（512kb）形式记录，head，tail，body，其中按照事务id进行分组；
  会先写入redologbuff（16M），写入硬盘可以配置参数，innodb_flush_log_at_trx_commit,
  0:每秒落盘一次;
  1:提交就落盘；（默认）
  2：依托os，每秒落盘一次。
  checkpoint记录redolog写入磁盘的位置，如果发生重启，按照checkpoint恢复之后的redolog即可。

binlog

• 数据备份，恢复，主从。
  逻辑概念，对表做了什么修改，是server层的什么引擎都会记录。
  默认不开启，线上一般都是主从会开启。
  statement，row，maxed3种模式。
  row会比第一种多记录一些信息。
  一般是binlog_row_image=FULL + row格式进行记录，尽量详细记录。

//查看binlog
./mysqlbinlog -vv  --start-position=234 ../var/mysql-bin.000001
//恢复binlog
./mysqlbinlog start-positon=956，stop-position=1230 ../var/mysql-bin.000003 | ./mysql-uroot -p
//开启gtid需要添加
--skip-gtids=true

可以查看对应位置的语句。
gtid记录事务的唯一id，可以根据这个方便主从之间的同步，哪些事务需要同步。
sync_binlog
0 不主动落盘，依赖os的调度。（默认）
1 提交就落盘。（推荐）
n 分组到多少组，一并落盘。