关于mysql的一切
数据库三大范式
第一范式(确保每列保持原子性)
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
第二范式(确保表中的每列都和主键相关)
第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
第三范式(确保每列都和主键列直接相关,而不是间接相关)
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段
详见例子可见 https://www.cnblogs.com/linjiqin/archive/2012/04/01/2428695.html
mysql的引擎
mysql的引擎由MyISAM和InnoDB，其主要区别如下：
1) 事务支持
MyISAM不支持事务，而InnoDB支持。InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响速度，所以最好是把多条SQL语句显示放在begin和commit之间，组成一个事务去提交。
MyISAM是非事务安全型的，而InnoDB是事务安全型的，默认开启自动提交，宜合并事务，一同提交，减小数据库多次提交导致的开销，大大提高性能。
2) 表锁差异
MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。
MyISAM锁的粒度是表级，而InnoDB支持行级锁定。简单来说就是, InnoDB支持数据行锁定，而MyISAM不支持行锁定，只支持锁定整个表。即MyISAM同一个表上的读锁和写锁是互斥的，MyISAM并发读写时如果等待队列中既有读请求又有写请求，默认写请求的优先级高，即使读请求先到，所以MyISAM不适合于有大量查询和修改并存的情况，那样查询进程会长时间阻塞。因为MyISAM是锁表，所以某项读操作比较耗时会使其他写进程饿死。
3) CURD操作
MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。
InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。
4) 外键
MyISAM：不支持
InnoDB：支持
5）索引的具体实现
InnoDB采用B+树，是聚集索引，即数据文件本身就是一个索引结构，树的叶节点data域保存了完整的数据记录；
MyISDM也采用B+树，但树中存储的是数据的地址；
更多详情可参照MyISAM和InnoDB索引结构原理
MyISAM和InnoDB两者的应用场景：
1) MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
2) InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。
更多区别和参考 https://www.cnblogs.com/kevingrace/p/5685355.html
事务的特性
数据库事务 transanction 正确执行的四个基本要素：ACID
（1）原子性(Atomicity)：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
（2）一致性(Correspondence)：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
（3）隔离性(Isolation)：隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行 相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆， 必须串行化或序列化请 求，使得在同一时间仅有一个请求用于同一数据。
（4）持久性(Durability)：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
事务隔离级别
脏读：一个事务中访问到了另外一个事务未提交的数据；
不可重复读：一个事务读取同一条记录2次，得到的结果不一致；
幻读：在《高性能Mysql》里对于幻读是这样定义的：幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。虽然这本书里是这么说的，而且Myql官方文档里也大概是这个意思，但是我个人来讲的话我觉得这更符合不可重复读的特征，所以更倾向于将上面的理解为不可重复读的一种，至于幻读呢，我更愿意理解为 某一次的select出的结果集不能支撑下一次的操作（这里的操作主要是指插入），更具体些，当前的事务下select某个记录发现不存在，此时进行插入操作，提示记录已经存在，再次select发现记录还是不存在。对于上面的这个现象我认为是发生了幻读。
数据库锁
InnoDB中锁的类型
- 共享锁(S Lock): 允许事务读一行数据
- 排他锁(X Lock):允许事务删除或者更新一行数据
排他锁和共享锁的兼容性：
|
|
X |
S |
|
X |
冲突 |
冲突 |
|
S |
冲突 |
兼容 |
InnoDB支持多粒度的锁定， 允许行级锁和表级锁同时存在；
意向锁是表级别的锁，其设计目的主要是为了在一个事务中揭示下一行将被请求的锁的类型
- 意向共享锁(IS Lock)：事务想要获得一个表中某几行的共享锁
- 意向排他锁(IX Lock)：事务想要获得一个表中某几行的排他锁
由于InnoDB存储引擎支持的是行级别的锁，所以意向锁其实不会阻塞除全表锁以外得到任何请求。
为了更简单的监控当前的事务并分析可能存在的锁问题，可通过以下3张表：
- INNODB_TRX：存储事务的信息，包括事务的状态，等待事务的锁ID，事务的SQL语句等
- INNODB_LOCKS：存储锁的信息，包括锁的模式、类型、模式(S/X)，被锁的表、页的数量、行的数量、行的主键值等
- INNODBLOCKSWAITS：反映当前的等待，包含申请锁资源的事务ID、锁ID，阻塞的事务ID、锁ID
一致性的非锁定读操作
一致性的非锁定读(consistent nonlocking read)是指InnoDB存储引擎通过行多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行Delete/Update操作，这时读取操作不会等待行上锁的释放，而会读取行的一个快照数据。
在Read Commited和Repeatable Read(InnoDB默认)下，InnoDB存储引擎使用非锁定的一致性读。但对于快照数据的定义有所区别：Read Commited下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据；Repeatable和Repeatable Read下，对于快照数据，非一致性读总是读取事务开始时的行数据版本；
这里Session A的最后一次查询会得到不同的结果：Read Commited下得到的数据为空；Repeatable Read下读到的结果和第一次读到的结果相同。
具体可参考<<mysql技术内幕>>6.2.2一致性的非锁定读操作
在默认情况下，InnoDB存储引擎的Select操作使用一致性非锁定读，但在某些情况下，我们需要对读取操作进行加锁。InnoDB存储引擎对select语句支持两种加锁操作：
- select ... for update 对读取的行记录加一个X锁。其他事务想在这些行上加任何锁都会被阻塞
- select ... lock in share mode 对读取的行记录加一个S锁。其他事务可以向被锁定的记录加S锁，但是对于加X锁，则会被阻塞
锁的算法
- Record Lock：单个行记录上的锁
- Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
- Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身
Repeatable Read下，Next-Key Lock 是默认的行记录锁定算法
索引
联合索引遵循最左前缀原则
美团技术团队的博文，对索引和慢查询的介绍很详细：MySQL索引原理及慢查询优化
索引优化案例：
分页查询优化
select * from trade_info where status = 0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59' order by id desc limit 102120, 20;
表tradeinfo上有索引idxstatuscreatetime(status,createtime)，通过上面分析知道，等价于索引**（status,createtime,id)**，对于典型的分页limit m, n来说，越往后翻页越慢，也就是m越大会越慢，因为要定位m位置需要扫描的数据越来越多，导致IO开销比较大，这里可以利用辅助索引的覆盖扫描来进行优化，先获取id，这一步就是索引覆盖扫描，不需要回表，然后通过id跟原表trade_info进行关联，改写后的SQL如下：
select * from trade_info a ,
(select id from trade_info where status = 0 and create_time >= '2020-10-01 00:00:00' and create_time <= '2020-10-07 23:59:59' order by id desc limit 102120, 20) as b //这一步走的是索引覆盖扫描，不需要回表
where a.id = b.id;
日志
drop、truncate和delete的区别与选择 https://blog.csdn.net/shadow_zed/article/details/78252494
-
redo log：恢复提交事务修改的页操作；通常是物理日志，记录的是页的物理修改操作。
-
uodo log：回滚记录到某个特定版本；通常是逻辑日志，根据每行记录进行记录。
- bin log用来进行Point-In-Time(PIT)的恢复及主从复制环境的建立。
binlog和redolog的区别？
（1）重做日志是在InnoDB存储引擎层产生的，而二进制日志是在MySQL数据库上层产生的，二进制日志不仅仅针对InnoDB存储引擎，任何存储引擎都会产生二进制日志。
（2）两种日志的记录内容形式不同。二进制日志是一种逻辑日志，记录的是SQL语句；而InnoDB存储引擎层面的重做日志是物理格式日志，记录的是对于每个页的修改。
（3）写入磁盘的时间不同，二进制日志只在事务提交完成后进行一次写入，而redo log在事务进行中不断的写入。
主从同步
master服务器将数据的改变都记录到二进制binlog日志中，只要master上的数据发生改变，则将其改变写入二进制日志；salve服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/O Thread请求master二进制事件，同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/O Thread和SQL Thread将进入睡眠状态，等待下一次被唤醒
- 「master服务器将数据的改变记录二进制binlog日志」。当master上的数据发生改变时，则将其改变写入二进制日志中；
- 「slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变」，如果发生改变，则开始一个I/O Thread请求master二进制binlog事件；
- 同时「master节点为每个I/O线程启动一个dump线程」，根据偏移量将新的 binary log 发送给 slave 服务器，并「保存至slave节点本地的中继日志relay log中」;
- slave节点将启动「SQL线程」从中继日志relay log中读取二进制日志binlog，在本地重放，使得其数据和主节点的保持一致，最后I/O Thread和SQL Thread将进入睡眠状态，等待下一次被唤醒。
也就是说：
- 「从库」会生成两个线程，一个「I/O线程」，一个「SQL线程」；
- 「I/O线程会去请求主库的binlog」，并将得到的binlog写到本地的「relay-log」(中继日志)文件中；
- 「主库」会生成一个「log dump线程」，用来「给从库I/O线程传binlog」；
- 「SQL线程会读取relay log文件中的日志」，并解析成sql语句逐一执行。
https://www.cnblogs.com/kevingrace/p/6256603.html
配置过程 https://learnku.com/articles/57154
last but not least, 关于mysql必读的两本书《MySql技术内幕：InnoDB存储引擎》《高性能Mysql》
