Mysql总结

事务

事务四大特性

ACID表示原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）
原⼦性： 事务是最⼩的执⾏单位，不允许分割。事务的原⼦性确保动作要么全部完成，要么全不执行
一致性： 执⾏事务前后，数据保持⼀致，多个事务对同⼀个数据读取的结果是相同的；
隔离性： 并发访问数据库时，⼀个⽤户的事务不被其他事务所⼲扰，各并发事务之间数据库是独⽴的；
持久性： ⼀个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发⽣故障也不应该对其有任何影响。

实现保证：
MySQL的存储引擎InnoDB使用重做日志保证一致性与持久性，回滚日志保证原子性，使用各种锁来保证隔离性。

事务隔离级别

读未提交 READ UNCOMMITTED：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
读已提交 READ COMMITTED：允许读取并发事务已经提交的数据，可以阻⽌脏读，但是幻读或不可重复读仍有可能发⽣。
可重复读 REPEATABLE READ：同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改，可以阻⽌脏读和不可重复读，会有幻读。默认隔离级别
串行化 SERIALIZABLE：最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏，这样事务之间就完全不可能产⽣⼲扰。

脏读(Drity Read) ：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

行锁，表锁，意向锁

共享/排它锁(Shared and Exclusive Locks)：行级锁，
意向锁(Intention Locks)，表级锁
间隙锁(Gap Locks)，锁定一个区间
记录锁(Record Locks)，锁定一个行记录

表级锁：（串行化）

Mysql中锁定 粒度最大的一种锁，对当前操作的整张表加锁，实现简单 ，资源消耗也比较少，加锁快，不会出现死锁 。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。

行级锁

Mysql中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。 InnoDB支持的行级锁，包括如下几种：

共享锁也叫读锁，是共享的，或者说是相互不阻塞的。多个客户在同一时刻可以同时读取同一个资源，而互不干扰。

排他锁也叫写锁则，也就是说一个写锁会阻塞其他的写锁和读锁，这是出于安全策略的考虑，只有这样，才能确保在给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源。

间隙锁（next-key locking）防止幻读的出现

间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入

MVCC多版本并发控制

InnoDB的MVCC,是通过在每行记录后面保存系统版本号(可以理解为事务的ID)，每开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的ID。这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的，防止幻读的产生。
1.MVCC手段只适用于Msyql隔离级别中的读已提交（Read committed）和可重复读（Repeatable Read）.
2.Read uncimmitted由于存在脏读，即能读到未提交事务的数据行，所以不适用MVCC.
3.简单的select快照度不会加锁，删改及select for update等需要当前读的场景会加锁
原因是MVCC的创建版本和删除版本只要在事务提交后才会产生。客观上，mysql使用的是乐观锁的一整实现方式，就是每行都有版本号，保存时根据版本号决定是否成功。Innodb的MVCC使用到的快照存储在Undo日志中，该日志通过回滚指针把一个数据行所有快照连接起来。
版本链
在InnoDB引擎表中，它的聚簇索引记录中有两个必要的隐藏列：
trx_id
这个id用来存储的每次对某条聚簇索引记录进行修改的时候的事务id。
roll_pointer
每次对哪条聚簇索引记录有修改的时候，都会把老版本写入undo日志中。这个roll_pointer就是存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获得上一个版本的记录信息。(注意插入操作的undo日志没有这个属性，因为它没有老版本)
每次修改都会在版本链中记录。SELECT可以去版本链中拿记录，这就实现了读-写，写-读的并发执行，提升了系统的性能。

索引

Innodb和Myisam引擎

Myisam：支持表锁，适合读密集的场景，不支持外键，不支持事务，索引与数据在不同的文件
Innodb：支持行、表锁，默认为行锁，适合并发场景，支持外键，支持事务，索引与数据同一文件

哈希索引

哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。

B+树索引

优点：
B+树的磁盘读写代价低，更少的查询次数，查询效率更加稳定，有利于对数据库的扫描
B+树是B树的升级版，B+树只有叶节点存放数据，其余节点用来索引。索引节点可以全部加入内存，增加查询效率，叶子节点可以做双向链表，从而提高范围查找的效率，增加的索引的范围。
在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度，B树与B+树可以有多个子女，从几十到上千，可以降低树的高度。
磁盘预读原理：将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

聚簇索引和非聚簇索引

聚簇索引：

将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。一个表只能有一个聚簇索引。

InnoDB默认将主键作为聚簇索引，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距甚远。

• 页分裂

当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间

非聚簇索引

将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

二级索引（secondary index，非主键索引）中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。因此，若表上的索引较多的话，主键应当尽可能的小 。

二级索引需要两次索引查找 : 二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。
这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行.

范式和反范式

①第一范式（1NF）列不可分割
②第二范式（2NF）属性完全依赖于主键
③第三范式（3NF）属性不依赖于其它非主属性

SQL查询

SQL语句的执行过程

①通过客户端/服务器通信协议与 MySQL 建立连接。并查询是否有权限
②Mysql8.0之前开看是否开启缓存，开启了 Query Cache 且命中完全相同的 SQL 语句，则将查询结果直接返回给客户端；
③由解析器进行语法语义解析，并生成解析树。如查询是select、表名tb_student、条件是id=‘1’
④查询优化器生成执行计划。根据索引看看是否可以优化
⑤查询执行引擎执行 SQL 语句，根据存储引擎类型，得到查询结果。若开启了 Query Cache，则缓存，否则直接返回。

回表查询和覆盖索引

非聚簇索引需要扫描两遍索引树
（1）先通过普通索引定位到主键值id=5；
（2）在通过聚集索引定位到行记录；
这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。
覆盖索引：主键索引聚簇索引覆盖索引
如果where条件的列和返回的数据在一个索引中，那么不需要回查表，那么就叫覆盖索引。
实现覆盖索引：常见的方法是，将被查询的字段，建立到联合索引里去。

Explain及优化

参考：https://www.jianshu.com/p/8fab76bbf448

索引优化：

①最左前缀索引：like只用于’string%'，语句中的=和in会动态调整顺序
②唯一索引：唯一键区分度在0.1以上
③无法使用索引：!= 、is null 、 or、>< 、（5.7以后根据数量自动判定）in 、not in
④联合索引：避免select * ，查询列使用覆盖索引

语句优化：

①char固定长度查询效率高，varchar第一个字节记录数据长度
②应该针对Explain中Rows增加索引
③group/order by字段均会涉及索引
④Limit中分页查询会随着start值增大而变缓慢，通过子查询+表连接解决
⑤count会进行全表扫描，如果估算可以使用explain
⑥delete删除表时会增加大量undo和redo日志， 确定删除可使用trancate

表结构优化：

①单库不超过200张表
②单表不超过500w数据
③单表不超过40列
④单表索引不超过5个

集群

MySQl主从复制过程

• 原理：将主服务器的binlog日志复制到从服务器上执行一遍，达到主从数据的一致状态。
• 过程：从库开启一个I/O线程，向主库请求Binlog日志。主节点开启一个binlog dump线程，检查自己的二进制日志，并发送给从节点；从库将接收到的数据保存到中继日志（Relay log）中，另外开启一个SQL线程，把Relay中的操作在自身机器上执行一遍
• 优点：
  • 作为备用数据库，并且不影响业务
  • 可做读写分离，一个写库，一个或多个读库，在不同的服务器上，充分发挥服务器和数据库的性能，但要保证数据的一致性

binlog记录格式：statement、row、mixed
基于语句statement的复制、基于行row的复制、基于语句和行（mix）的复制。其中基于row的复制方式更能保证主从库数据的一致性，但日志量较大，在设置时考虑磁盘的空间问题。

数据一致性问题

“主从复制有延时”，这个延时期间读取从库，可能读到不一致的数据。

缓存记录写key法：

在cache里记录哪些记录发生过的写请求，来路由读主库还是读从库

异步复制：

在异步复制中，主库执行完操作后，写入binlog日志后，就返回客户端，这一动作就结束了，并不会验证从库有没有收到，完不完整，所以这样可能会造成数据的不一致。

半同步复制：

当主库每提交一个事务后，不会立即返回，而是等待其中一个从库接收到Binlog并成功写入Relay-log中才返回客户端，通过一份在主库的Binlog，另一份在其中一个从库的Relay-log，可以保证了数据的安全性和一致性。

全同步复制：

指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

线上故障及优化

是这个么场景。有个同学是这样写代码逻辑的。先插入一条数据，再把它查出来，然后更新这条数据。在生产环境高峰期，写并发达到了 2000/s，这个时候，主从复制延时大概是在小几十毫秒。线上会发现，每天总有那么一些数据，我们期望更新一些重要的数据状态，但在高峰期时候却没更新。用户跟客服反馈，而客服就会反馈给我们。
解决方案：

• 分库，拆分为多个主库，每个主库的写并发就减少了几倍，主从延迟可以忽略不计。
• 重写代码，写代码的同学，要慎重，插入数据时立马查询可能查不到。
• 如果确实是存在必须先插入，立马要求就查询到，然后立马就要反过来执行一些操作，对这个查询设置直连主库或者延迟查询。主从复制延迟一般不会超过50ms