数据库三范式

第一范式(1st NF －列都是不可再分)
第二范式(2nd NF－每个表只描述一件事情)
第三范式(3rd NF－ 不存在对非主键列的传递依赖)

执行流程

https://www.cnblogs.com/xingxia/p/mysql_operation.html
连接器（用户身份验证）
->查询缓存 (Mysql会先校验这个sql是否执行过，以Key-Value的形式缓存在内存中,8.0删除缓存，经常更新缓存没有意义)
->分析器（词法分析：提取关键字 表、字段名、查询条件 -> 语法分析：判断sql是否正确）
->优化器（比如多个索引的时候该如何选择索引）
->执行器(调用存储引擎的API来执行查询,将结果返回给客户端)
image.png

binlog的写入机制

每个线程有自己binlog cache，但是共用同一份binlog文件。
write，指的就是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快。
fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为fsync才占磁盘的IOPS。
write 和fsync的时机，是由参数sync_binlog控制的：
1. sync_binlog=0的时候，表示每次提交事务都只write，不fsync；
2. sync_binlog=1的时候，表示每次提交事务都会执行fsync；
3. sync_binlog=N(N>1)的时候，表示每次提交事务都write，但累积N个事务后才fsync。
因此，在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成0，比较常见的是将其设置为100~1000中的某个数值。
但是，将sync_binlog设置为N，对应的风险是：如果主机发生异常重启，会丢失最近N个事务的binlog日志。
binlog的组提交
1. binlog_group_commit_sync_delay参数，表示延迟多少微秒后才调用fsync;
2. binlog_group_commit_sync_no_delay_count参数，表示累积多少次以后才调用fsync。
用于故意拉长binlog从write到fsync的时间，以此减少binlog的写盘次数。在MySQL5.7的并行复制策略里，它们可以用来制造更多的“同时处于prepare阶段的事务”。这样就增加了备库复制的并行度。既可以“故意”让主库提交得慢些，又可以让备库执行得快些。在MySQL5.7处理备库延迟的时候，可以考虑调整这两个参数值，来达到提升备库复制并发度的目的。

redolog的写入机制

为了控制redo log的写入策略，InnoDB提供了innodb_flush_log_at_trx_commit参数，它有三种
可能取值：
1. 设置为0的时候，表示每次事务提交时都只是把redo log留在redo log buffer中;
2. 设置为1的时候，表示每次事务提交时都将redo log直接持久化到磁盘；
3. 设置为2的时候，表示每次事务提交时都只是把redo log写到page cache。
InnoDB有一个后台线程，每隔1秒，就会把redo log buffer中的日志，调用write写到文件系统的
page cache，然后调用fsync持久化到磁盘。
注意，事务执行中间过程的redo log也是直接写在redo log buffer中的，这些redo log也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的redo log，也是可能已经持久化到磁盘的。 实际上，除了后台线程每秒一次的轮询操作外，还有两种场景会让一个没有提交的事务的redo
log写入到磁盘中。
1. 一种是，redo log buffer占用的空间即将达到innodb_log_buffer_size一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是write，而没有调用fsync，也就是只留在了文件系统的page cache。
2. 另一种是，并行的事务提交的时候，顺带将这个事务的redo log buffer持久化到磁盘。假设一个事务A执行到一半，已经写了一些redo log到buffer中，这时候有另外一个线程的事务B提交，如果innodb_flush_log_at_trx_commit设置的是1，那么按照这个参数的逻辑，事务B要把redo log buffer里的日志全部持久化到磁盘。这时候，就会带上事务A在redo log buffer里的日志一起持久化到磁盘。
MySQL的“双1”配置：sync_binlog和innodb_flush_log_at_trx_commit都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是redo log（prepare 阶段），一次是binlog。
非“双1”配置
1. 业务高峰期。一般如果有预知的高峰期，DBA会有预案，把主库设置成“非双1”。
2. 备库延迟，为了让备库尽快赶上主库。
3. 用备份恢复主库的副本，应用binlog的过程
4. 批量导入数据的时候。
一般情况下，把生产库改成“非双1”配置，是设置innodb_flush_logs_at_trx_commit=2、
sync_binlog=1000。

如果mysql出现性能瓶颈，瓶颈在IO上，优化方案
  1. 设置 binlog_group_commit_sync_delay和 binlog_group_commit_sync_no_delay_count参数，减少binlog的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
    2. 将sync_binlog 设置为大于1的值（比较常见是100~1000）。这样做的风险是，主机掉电时会丢binlog日志。
    3. 将innodb_flush_log_at_trx_commit设置为2。这样做的风险是，主机掉电的时候会丢数据。

加锁规则（两个原则，两个优化，一个bug）
  1. 原则1：加锁的基本单位是next-key lock。希望你还记得，next-key lock是前开后闭区间。
    2. 原则2：查找过程中访问到的对象才会加锁。
    3. 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
    4. 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
    5. 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

change buffer

唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。
change buffer用的是buffer pool里的内存，因此不能无限增大。change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

能不能使用join语句?
  • 如果可以使用IndexNested-Loop Join算法，也就是说可以用上被驱动表上的索引，其实是没问题的。
  • 如果使用Block Nested-Loop Join算法，扫描行数就会过多。尤其是在大表上的join操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种join尽量不要用。
    • 执行过程
  1. 扫描表t1，顺序读取数据行放入join_buffer中，放完第88行join_buffer满了，继续第2步；
    2. 扫描表t2，把t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结 果集的一部分返回；
    3. 清空join_buffer；
    4. 继续扫描表t1，顺序读取最后的12行数据放入join_buffer中，继续执行第2步。
    内存判断次数是不受选择哪个表作为驱动表影响的。而考虑到扫描行数，在M和N大小确定的情况下，N小一些，整个算式的结果会更小。 所以结论是，应该让小表当驱动表,需要join_buffer_size改大
  • 在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”,应该作为驱动表。
    MySQL 执行一条查询的流程
    • 客户端先通过连接器连接到 MySQL 服务器；• 连接器权限验证通过之后，先查询是否有查询缓存，如果有缓存（之前执行过此语句）则直接返回缓存数据，如果没有缓存则进入分析器；
    • 分析器会对查询语句进行语法分析和词法分析，判断 SQL 语法是否正确，如果查询语法错误会直接返回给客户端错误信息，如果语法正确则进入优化器；
    • 优化器是对查询语句进行优化处理，例如一个表里面有多个索引，优化器会判别哪个索引性能更好；
    • 优化器执行完就进入执行器，执行器则开始执行语句进行查询比对了，直到查询到满足条件的所有数据，然后进行返回。

InnoDB 为什么要使用 B+ 树，而不是 B 树、Hash、红黑树或二叉树？

• B 树：不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低。
• Hash：虽然可以快速定位，但是没有顺序，IO 复杂度高。
• 二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且 IO 代价高。
• 红黑树：树的高度随着数据量增加而增加，IO 代价高。

主备延迟存在可能原因
  1. 备库性能差（配置参数不一致）
    1. 对称部署
  2. 单点备库查询量大、从库备份数据
    1. 一主多从，分担压力
    2. 通过binlog输出到外部系统，比如Hadoop这类系统，让外部系统提供统计类查询的能力。

一主多从的方式大都会被采用。因为作为数据库系统，还必须保证有定期全量备份的能力。而从库，就很适合用来做备份。
从库和备库在概念差不多。会在HA过程中被选成新主库的，称为备库，其他的称为从库。

  1. 大事务或者大量DML
    1. 大量删除历史数据
    2. 大表DDL
  2. 备库的并行辅助能力
    读写分离过期读
    原因
    主从可能存在延迟，客户端执行完一个更新事务后马上发起查询，如果查询选择的是从库的话，就有可能读到刚刚的事务更新之前的状态。
    解决方案
  • 强制走主库方案
    1. 对于必须要拿到最新结果的请求，强制将其发到主库上。比如，在一个交易平台上，卖家发 布商品以后，马上要返回主页面，看商品是否发布成功。那么，这个请求需要拿到最新的结果，就必须走主库
    2. 对于可以读到旧数据的请求，才将其发到从库上。在这个交易平台上，买家来逛商铺页面，就算晚几秒看到最新发布的商品，也是可以接受的。那么，这类请求就可以走从库。
  • sleep方案

sleep1秒存在问题

  1. 如果这个查询请求本来0.5秒就可以在从库上拿到正确结果，也会等1秒；
  2. 如果延迟超过1秒，还是会出现过期读。
    • 判断主备无延迟方案
    • 配合semi-sync方案

仍存在问题

  1. 一主多从的时候，在某些从库执行查询请求会存在过期读的现象；
  2. 在持续延迟的情况下，可能出现过度等待的问题。
    • 等主库位点方案
    • 等GTID方案

不允许过期读的要求，就只有两种选择，一种是超时放弃，一种是转到主库查询

InnoDB和MyISAM存储引擎区别

【事务、主键、索引、锁、count】
1.事务
InnoDB 支持事务，MyISAM 不支持事务
2.外键
InnoDB 支持外键，而 MyISAM 不支持
3.聚集索引
InnoDB 是聚集索引，MyISAM 是非聚集索引。
聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4.表行数
InnoDB：不保存表的具体行数，执行 select count() from table 时需要全表扫描。
MyISAM：用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
5.最小的锁粒度锁
InnoDB：最小的锁粒度是行锁，
MyISAM：最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。
6.表主键
MyISAM：允许没有主键的表存在。
InnoDB：如果没有设定主键，就会自动生成一个6字节的主键(用户不可见)。
7.数据文件
InnoDB数据文件
.frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息
.ibd：使用独享表空间存储表数据和索引信息，一张表对应一个ibd文件。
ibdata文件：使用共享表空间存储表数据和索引信息，所有表共同使用一个或者多个ibdata文 件。
*MyISAM数据文件
.frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息
.myd文件：主要用来存储表数据信息。
.myi文件：主要用来存储表数据文件中任何索引的数据树

如何选择
是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；
如果表中绝大多数都只是读查询，可以考虑 MyISAM，
如果既有读写也挺频繁，请使用InnoDB。(事务)
MyISAM可被压缩，存储空间较小，全文索引
MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。不能建立太大的表。

Join

Join查询原理

查询原理：MySQL内部采用了一种叫做 nested loop join（嵌套循环连接）的算法。Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与 Join，则再通过前两个表的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复，基本上MySQL采用的是最容易理解的算法来实现join。所以驱动表的选择非常重要，驱动表的数据小可以显著降低扫描的行数。
一般情况下参与联合查询的两张表都会一大一小，如果是join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表。简单来说，驱动表就是主表，left join 中的左表就是驱动表，right join 中的右表是驱动表。

Nested-Loop Join

Index Nested-Loop Join：INLJ，索引嵌套循环连接
减少内层表数据的匹配次数
Block Nested-Loop Join：BNLJ，缓存块嵌套循环连接
减少内层表数据的循环次数
Simple Nested-Loop Join：SNLJ，简单嵌套循环连接