Mysql知识百解

Mysql

mysql四大特性

• 原子性：一个事务必须视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。通过mysql undo log(回滚日志实现)，也有用到mvcc.
• 一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。是核心由原子性、隔离性、持久性综合实现
• 隔离性：一个事务所做的修改在最终提交以前，对其他事务是不可见的。 通过锁和mvcc(多版本并发控制)实现。
• 持久性：一旦事务提交，则其所做的修改就会永久保存到数据库中。主要通过mysql redo log(重做日志，用于崩溃恢复)日志实现

mysql如何把一条数据保存到mysql

主要经历三个阶段
1.客户端与服务端建立连接发送sql请求
2.服务端使用连接器与客户端建立连接做一些验证等操作，让后在分析器中对sql请求做词法分析与语法分析生成一个AST（抽象语法树节点为Token），然后经过查询优化器有基于成本的优化(CBO cast)和基于规则的优化(RBO rule)一般基于成本的优化会多一点来生成执行性能最高的执行方式，然后交给执行器来进行详细的执行
3.执行器通过存储引擎查询出想要的结果并且把结果返回给服务端再返回给客户端
另一种说法：
首先将执行语句加载到内存中，如果是select情况只需要优先将磁盘的数据加载到内存中即可；insert(要检查的数据)、update、delete先把数据读取到内存中，然后在内存中进行相应的增删改操作，期间会产生几个日志undo log(回滚 mvcc)、redo log和binlog(数据一致性，两阶段提交)；然后把数据溢写道磁盘中(先到内存中->fync指令->写道磁盘)，如果写成共直接到盘，如果失败了有几种情况：
1.sql执行失败undo log 回滚
2.断电之类的，用两阶段提交机制保证最终一致性
两阶段提交：
mysql数据库在增删改的时候用到**磁盘预写日志(write ahead log)**的方式来增加性能，先把数据写到redo log,binlog日志中，然后到一定大小或时间了就再写道磁盘中(这样也减少了磁盘IO次数增加性能)，如果往磁盘中写入数据的时候发生了意外如断电，那么数据库会根据redo log与binlog来恢复数据(两个日志中的执行内容必须一直不然直接丢弃)。

mvcc多版本并发控制

先解释两个概念
1.当前读：简单来说就是是读取的是该条数据的最新数据。在MySQL中当前读查询的时候 select 结尾加 lock in share mode(共享锁)，结尾加 for update(排它锁)都属于当前读。insert、update、delete的时候都要弄到当前读的数据
2.快照读：简单来说就是不加锁的select操作，所以可能读到的不是最新数据，目的使用来提升数据库的并发查询能力。数据行每次新增或更新后形成的一个一个版本，快照读就是直接去读最新版本，所以快照读也是基于mvcc来实现的。
当前读、快照读、mvcc之间的关系
1.首先mvcc是通过维护每条数据库数据的多个版本来使得读写操作没有冲突从而提升性能。
2.快照读就是对数据一致性影响不大的一些数据的快速读取
3.当前读就是对数据修改是通过加锁+读取最新数据来保证数据一致性
mvcc实现原理：
1.数据库行末尾维护的三个隐式字段:

• 事务Id(DB_TRX_ID)：新增或更新数据时候该记录的事务id(全局递增),6字节。
• 回滚指针(DB_ROLL_PTR)：记录指向本条数据在undo log的旧版本，用于事务失败找到旧版本恢复，7字节。
• 隐藏主键(DBZ_ROW_ID)：如果系统没有设置主键那么引擎会自动生成一个row_id代替主键，6字节。
  2.undo log:用于事务出现问题进行回滚恢复的操作，insert的时候事务提交之后就可以删除了，update和delete快照读的时候还要用，只有在快照读或者回滚用不到的时候才会被删除，另外为了节省磁盘空间数据被更新和删除的时候老数据只是先打上delete标记，后续会被purge统一删除。
  3.read view: readview是事务去读取另一个事务的快照读的时候产生的读视图(可以理解为一个快照)，它的最大最用是对一个事务做可见性判断，具体的算法如下：
  首先readview有3个全局属性
  trx_lsit: read view产生的时候的所有活跃事物的id集合
  up_limit_id: trx_lsit中最小的事务id
  low_limit_id: trx_lsit read view产生的时候,下个将生成的事务id
  比较规则：
  1.如果当前事务id < up_limit_id,则可见当前事务id renturn;否则继续
  2.当前事务id > low_limit_id,则不可见当前事务id return,然后去undo log中找最近的旧纪录;否则继续
  3.如果当前事务id在trx_lsit集合中则当前事务活跃不可见return,然后去undo log中找最近的旧纪录，反之可见，over
  可看图：
  在RC与RR两个隔离级别的时候，read view的生成时间不同，RC是每次快照读都会生成一个read view,RR同一个事务中第一次快照读生成一个快照读，再次快照读用的还是一个

mysql事务隔离级别

• READ-UNCOMMITTED(读取未提交)： 事务的修改，即使没有提交，对其他事务也都是可见的。事务能够读取未提交的数据，这种情况称为脏读。
• READ-COMMITTED(读取已提交)： 事务读取已提交的数据，大多数数据库的默认隔离级别。当一个事务在执行过程中，数据被另外一个事务修改，造成本次事务前后读取的信息不一样，这种情况称为不可重复读。
• REPEATABLE-READ(可重复读)： 这个级别是MySQL的默认隔离级别，它解决了脏读的问题，同时也保证了同一个事务多次读取同样的记录是一致的，但这个级别还是会出现幻读的情况。幻读是指当一个事务A读取某一个范围的数据时，另一个事务B在这个范围插入行，A事务再次读取这个范围的数据时，会产生幻读
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
  事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。
  因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容)但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读）并不会有任何性能损失。

幻读问题

原因：在一个事务中，当前读与快照读混用才可能导致幻读，普通的select是快照读，而 for update,lock in share mode,update,delete都属于当前读。
现象：两个事务，事务A里先到一个范围，然后事务B在事务A查询到的范围之内进行了update或delete操作并提交，然后事务A中有了当前读操作，再次查询发现查询到的范围变了
解决：通过记录锁+间隙锁，防止上例中的事务B对A操作的范围进行修改

join原理

MySQL是只支持一种Join算法Nested-Loop Join(嵌套循环连接)，并不支持哈希连接和合并连接，不过在mysql中包含了多种变种，能够帮助MySQL提高join执行的效率。

• Simple Nested-Loop Join，简单嵌套循环就是A表m条数据，B表n条数据，jion处理之后对表进行mn次访问，简单但是开销大例：select * from t1t2;笛卡尔积。
• Index Nested-Loop Join，索引嵌套连接，就是关联字段建立索引来减少比较次数提高效率
  这种算法在链接查询的时候，驱动表会根据关联字段的索引进行查找，当在索引上找到了符合的值，再回表进行查询，也就是只有当匹配到索引以后才会进行回表。至于驱动表的选择，MySQL优化器一般情况下是会选择记录数少的作为驱动表，但是当SQL特别复杂的时候不排除会出现错误选择
• Block Nested-Loop Join，块嵌套连接，就是相对于简单嵌套循环多了一个中间处理的过程，也就是join buffer，使用join buffer将驱动表的查询JOIN相关列都给缓冲到了JOIN BUFFER当中，然后批量与非驱动表进行比较，这也来实现的话，可以将多次比较合并到一次，降低了非驱动表的访问频率。也就是只需要访问一次S表。这样来说的话，就不会出现多次访问非驱动表的情况了，也只有这种情况下才会访问join buffer。
  在MySQL当中，我们可以通过参数join_buffer_size来设置join buffer的值，然后再进行操作。默认情况下join_buffer_size=256K，在查找的时候MySQL会将所有的需要的列缓存到join buffer当中，包括select的列，而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。
  ​ 在有索引的情况下，MySQL会尝试去使用Index Nested-Loop Join算法，在有些情况下，可能Join的列就是没有索引，那么就会走块嵌套连接

mysql索引

索引原理：
1.IO层面，数据库的索引数据及业务数据都是保存在磁盘上的所以频繁的IO操作就成了数据库的性能瓶颈之一，所以读取次数少量少就可以达到提升性能的目的，mysql的索引就利用了分块读取的思想，利用磁盘读取的局部性原理进行磁盘预读，数据以页(16kb)的形式保存在磁盘中，mysql读取的都是页的整数倍。
2.数据结构层面：采用的是B+树，二叉树、AVL树、红黑树都有一个问题数的分支有且仅有2个，当想往书里边插入更多的数据的时候，会造成树的深度变深，从而倒是查询次数变多查询效率变低，然后如果要求数据有序并且可以多叉，并且每个节点上都保存着数据，B树就满足这个条件，但是B树这样存会导致大量存储空间被占用，导致树的分支范围变小，又会导致树的深度增加，所以要把非叶子节点中的数据放到叶子节点中，非叶子节点中只存储key的值，这样就是一个B+树，适合MySQL使用。
B树和B+树的区别：

• B树的每个节点里边都保存了数据，而B+树只有叶子节点保存了数据
• B树的叶子节点之间没什么关系，B+树的叶子节点之间有指针存在形成了一个双向链表，方便范围查询及排序
  索引失效的情况：
• 隐式转换，例如varchar类型的数据保存的是Int，然后查询的时候不加引号
• 组合索引的左侧没匹配上后续的也失效
• 索引列存在于计算、函数、类型转换中
• is null 、is not null
• or操作符后边的索引会失效
• 两个关联表的关联字段长度或者编码不一样会导致索引失效
• like的时候前缀用%，因为索引对字符串的匹配是从左到右逐个字符比较的，%在前就没法比较
• 数据量较小查询优化器认为不使用索引比使用索引更快
  聚簇索引与非聚簇索引的区别：
  innodb存储引擎在进行数据插入的时候，必须绑定到一个索引列上，默认是主键，没有主键选唯一键，再没有会自己生成一个6个字节的rowId,跟苏剧绑定在一起的叫聚簇索引，没绑定在一起的叫非聚簇索引。innodb中数据跟索引都保存在idb文件中，myisam中数据是保存在myi文件中，索引保存在myb文件中。所以innodb既支持聚簇索引与非聚簇索引，myisam只支持非聚簇索引

主从复制

主从复制原理：
首先DDL和DML操作所产生的操作日志会保存在binlog日志中，为了由顺序IO来提升日志同步性能通过IO线程将binlog日志中的数据同步到中继日志relay log中，再由SQL线程通过随机IO来读取中继日志中的信息写入到从库中。
主从同步延迟：
主从复制是单线程操作，主库写DDL和DML操作日志到binlog,salve读取binlog到中继日志relay log都是顺序IO速度很快，但是salve的SQL thread将主库的DDL和DML操作事件在salve中重放时DDL和DML操作是随机的，不是顺序，所以成本要高很多，另一方面sql thread也是单线程，当主库并发高时产生的DML数量超过slave的sql thread所能处理的速度，或者当salve中有大型的query语句产生了锁等待，那么延时就产生了。
同步延迟的解决方案：
1.业务的持久化层采用分库架构，mysql服务可平行扩展，分散压力
2.单个库读写分离，主写从读，分散压力，这样从库压力大保护主库
3.服务端使用缓存技术降低mysql的读压力
4.不同业务的mysql物理上放在不同的机器，分散压力
5.使用比从库更加强劲的硬件做从库，mysql压力小，延迟自然变小
mysql5.7之后使用MTS并行复制技术，永久解决了复制延迟问题，
主从复制有几种模式