MySQL

基础

当前读：读取的数据是最新版本的

快照读：读取的是历史版本的记录

索引

索引是帮助MySQL高效获取数据的排好序的数据结构

索引和实际的数据都是存储在磁盘的，只不过在进行数据读取的时候会优先把索引加载进内存

索引结构：二叉树 -> AVL树 -> 红黑树 -> B-树 -> B+树

---

普通索引：允许被索引的数据列包含重复的值

唯一索引：可以保证数据记录的唯一性

主键索引：一张表中只能定义一个主键索引，用于唯一标识一条记录

单值索引：索引只覆盖一个数据列

联合索引：索引覆盖多个数据列

全文索引：通过建立倒排索引，可以极大的提升检索效率，解决判断字段是否包含的问题，是目前搜索引擎使用的一种关键技术

---

聚簇索引：数据和索引是在一起的，树的叶子节点上的data就是该行数据

非聚簇索引：数据和索引是不在一起的，树的叶子节点上的data不是数据本身，而是数据存放的地址

聚簇索引的数据物理存放顺序和索引顺序是一致的，所以一个表中只能有一个聚簇索引，而非聚簇索引可以有多个

InnoDB中，如果定义了主键，那主键就是聚簇索引，如果过没有主键，就会找一个非空的unique列作为聚簇索引，否则，InnoDB会创建一个隐藏的row-id作为聚簇索引

索引设计原则：

1，适合索引的列是出现在where子句中的列，或者连接子句中的列

2，基数较小的表，索引效果较差，没有必要在此建立索引

3，使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间，如果搜索词超过前缀长度，使用索引排除不匹配的行，然后检查其余行是否可能匹配

4，不要过度索引，索引需要额外的磁盘空间，并降低写操作的性能，，在修改表内容时，索引会进行更新甚至重构，索引列越多，这个时间就会越长

5，定义有外键的数据列一定要建立索引

6，更新频繁的字段不适合建索引

7，若是不能有效区分数据的列不适合做索引列（如：性别）

8，尽量的扩展索引，不要新建索引

9，对于那些查询中很少涉及的列，重复值比较多的列不要建立索引

10，对于定义为text、image和bit的数据类型的列不要建立索引

常见存储引擎：

MyISAM：（非聚集）

1，索引文件和数据文件是分离的

      .frm：存储表结构

      .MYD：存储数据文件

      .MYI：存储索引文件

2，不支持事务，但是每次操作都是原子操作

3，支持表级锁，每次操作又是对整个表加锁

4，存储表的总行数

InnoDB：（聚集）

1，表数据本身就是按B+Tree组织的一个索引结构文件

      .frm：存储表结构

      .ibd：存储索引文件和数据文件

2，支持ACID事务，支持事务的四种隔离级别

3，支持行级锁，支持外键约束

4，不存储总行数

---

主键索引：叶子结点存放的是完整的数据记录

非主键索引：叶子节点存放的是索引所在行的主键，非主键索引查找到主键后，如果要找的数据在索引里，会进行回表，根据主键索引来查找出该行的全部数据

---

整形自增主键的作用：

主键：组建整张表的所有数据，提高性能；如果不建主键，mysql会找到能建唯一索引的列，用这列来组建整张表的所有数据，若未找到，mysql会维护一个隐藏列组织整张表的所有数据

整形：比较大小的时候速度快

自增：在增加元素的时候直接在最后面添加元素，在中间加入元素的话可能会导致B+树叶子节点分裂，树的结构变化，影响平衡。

---

buffer pool

是一块内存区域，当数据库操作数据的时候，把硬盘上的数据加载到buffer pool，不直接和硬盘打交道，操作的是buffer pool里面的数据

数据库的增删改查都是在buffer pool上进行，和undo log/redo log/redo log buffer/binlog一起使用，后续会把数据刷到硬盘上

默认大小 128M

free链表

是一个双向链表，链表节点是空闲的缓存页对应的描述信息块（空的缓存页）

作用：帮助我们找到空闲的缓存页

flush链表

是一个双向链表，链表结点是被修改过的缓存页的描述信息块（更新过的缓存页）

作用：帮我们找到脏缓存页，也就是需要刷盘的缓存页

LRU链表

是一个双向链表，链表结点是非空的缓存页对应的描述信息块（有数据的缓存页，包含更新过和未更新过的缓存页，范围比flush链表大，flush链表是它的子集）

作用：用来淘汰不常被访问的缓存页

LRU链表分为热数据区和冷数据区，冷数据区占了总链表的37.5%；冷数据区是不常访问的缓存页；热数据区是经常访问的缓存页

加载数据的时候，缓存页会放在冷数据区的头部

数据页加载到缓存页后，在1s之后，访问该缓存页，该缓存页会被移动到热数据区头部数据页刚加载到缓存页后，在1s之内，访问该缓存页，该缓存页是不会被移动到热数据区头部的

什么时候会lru中的缓存页刷盘并清空？

当缓存页用完的时候，把冷数据区尾部的缓存页刷盘清空，缓存页对应的信息描述块从lru链表中移除，加入到free链表当中

有一个后台线程，他会运行一个定时任务，这个定时任务每隔一段时间就会把LRU链表的冷数据区域的尾部的一些缓存页，刷入磁盘里去，清空这几个缓存页，把他们加入回free链表去；如果该缓存页也在flush链表中（该缓存页更新过），也需要把该缓存页从flush链表中移除

日志

redo log：重做日志（InnoDB特有）

作用：确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

在一条更新语句进行执行的时候，InnoDB引擎会把更新记录写到redo log日志中，然后更新内存，此时算是语句执行完了，然后在空闲的时候或者是按照设定的更新策略将redo log中的内容更新到磁盘中，在数据库进行异常重启的时候，可以根据redo log日志来数据，redo log日志的大小是固定的，即记录满了以后就从头循环写。

用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。

如果事务提交成功，buffer pool里的数据还没来得及写入磁盘，此时系统宕机了，可以根据redo log日志里的数据恢复buffer pool里的缓存数据

binlog：归档日志（MySQL Server，所有的存储引擎都有的）

作用：保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。

记录的是这个更新语句的原始逻辑（SQL语句原文）

binlog可以作为数据库磁盘恢复数据使用，主从复制搭建

MySQL通过将主节点的binlog同步给从节点完成主从之间的数据同步

用于数据库的基于时间点的还原。

undo log：回滚日志（InnoDB特有）

如果事务提交失败要回滚数据，可以用undo log日志里的数据恢复buffer pool里的缓存数据

 事务

 ACID属性：

A原子性：事务是一个原子操作单元，其对数据的修改，要么全执行，要么全不执行。有undo log日志保证，它记录了需要回滚的日志信息。

C一致性：事务无论是成功还是失败，都必须保持事务内操作的一致性。当失败时，都要对前面的操作进行回滚，不管中途是否成功。由其他三大特征保证。

I 隔离性：当多个事务操作一个数据的时候，为防止数据损坏，需要将每个事务进行隔离，互不干扰。由MVCC来保证。

D持久性：事务完成之后，他对于数据的修改是永久性的。由内存+redo log日志来保证，mysql修改数据同时在内存和redo log记录这次操作，宕机的时候可以从redo log恢复

并发事务处理带来的问题：

更新丢失：两个事务选择同一行进行更新，最后的更新覆盖了其他事务所做的更新

脏读：一个事务读取到其他事务已修改但尚未提交的数据，并在其基础上做了修改，若其他事务回滚，则读取到的数据无效，不符合一致性要求

不可重复读：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现读出的数据已经发生了改变、或某些记录已经被删除了

幻读：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据

事务隔离级别：

读未提交（READ UNCOMMINTTED）：能够读取到没有被提交的数据，所以很明显这个级别的隔离机制无法解决脏读、不可重复读、幻读中的任何一种

读已提交（READ COMMINTTED）：能够读到那些已经提交的数据，自然能够防止脏读，但是无法限制不可重复读和幻读

可重复读（REPEATABLE READ）：在数据读出来之后加锁，明确数据读取出来就是为了更新用的，所以要加一把锁，防止别人修改它。读取了一条数据，这个事务不结束，别的事务就不可以改这条记录，这样就解决了脏读、不可重复读的问题，但是幻读的问题还是无法解决

串行化（SERIALIZABLE）：最高的事务隔离级别，不管多少事务，挨个运行完一个事务的所有子事务之后才可以执行另外一个事务里面的所有子事务，这样就解决了脏读、不可重复读和幻读的问题了

mvcc（多版本并发控制）

多版本并发控制：读取数据时通过一种类似快照的方式将数据保存下来，这样读锁和写锁不冲突了

MVCC只在读已提交（READ COMMINTTED）和可重复度（REPETABLR READ）两个隔离级别下工作

开始事务时创建readview，readview维护当前活动的事务id，即未提交的事务id，排序生成一个数组，访问数据，获取数据中的事务id（获取的是事务id最大的记录），对比readview：如果在readview的左边（比readview都小），可以访问（在左边意味着事务已提交），如果在readview的右边（比readview都大）或者就在readview中，不可以访问，获取rool_pointer，取上一版本重新对比（在右边意味着，该事务在readview生成之后出现，是未开始的事务，在readview中意味着该事务还没提交）

已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView，而可重复读隔离级别则在第一次读的时候生成一个ReadView，之后的读都复用之前的ReadView

MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取 同一条数据在版本链上的不同版本数据。

锁

行锁：

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高

共享锁：读锁。多个事务可以对同一个数据共享同一把锁，是由锁的事务可以访问数据，但只能读不能修改

排他锁：写锁。只有一个事务能够获得排他锁，其他事务都不能获取该行的锁

自增锁：通常是针对MySQL当中的自增字段，如果与事务回滚这种情况，数据会回滚，但是自增序列不会回滚

表锁：

偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发最低

表共享读锁：类似共享锁

表排他写锁：类似排他锁

意向锁：是InnoDB自动添加的一种锁，不需要用户干预

页锁：

开销和加锁时间界于表锁和行锁之间：会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

全局锁：

加锁之后整个数据库实例都处于只读状态，所有的数据变更操作都会被挂起，一般用于全库备份的时候

记录锁：属于行锁的一种，记录锁的范围只是表中的某一条数据，避免脏读、不可重复读

间隙锁：属于行锁的一种，间隙锁的范围是表记录的某一个区间，而不锁具体的记录，避免幻读

临建锁（Next-key）：属于行锁的一种，间隙锁+右记录锁（前开后闭），避免脏读、不可重复读、幻读

意向锁：

一个事务枷锁成功后设置的一个状态，其他事务需要对这个表加锁的时候只需要获取这个状态就知道是否可以加锁，避免了对整个索引树的每个节点扫描是否加锁

意向共享锁：当一个事务试图对整个表加共享锁之前，首先需要获得这个表的意向共享锁

意向排他锁：当一个事务试图对整个表加排他锁之前，首先需要获得这个表的意向排他锁

主从同步原理：

主从同步的过程：

mysql的主从复制中主要由三个线程：Master一条线程和Slave中的两条线程

1，主节点binlog，主从复制的基础是主库记录数据库的所有变更记录到binlog，binlog是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件

2，主节点log dump线程，当binlog由变动时，log dump线程读取内容并发送给从节点

3，从节点I/O线程接收binlog内容，并将其写入道relay log文件中

4，从节点的SQL线程读取relay log文件内容对数据更新进行重放，最终保证主从数据库的一致性

注：主节点使用binlog文件+position偏移量来定位主从同步的位置，从节点会保存其已接受到的偏移量，如果从节点发生宕机重启，则自动会从position的位置发起同步

---

由于mysql，默认的复制方式是异步，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是主库挂二，从库处理失败，这时候从库升为主库后，日志就丢失了

全同步复制：主库写入binlog后强制同步日志到从库，所有的从库都执行完成后菜返回给客户端这个方式性能会受到严重影响

半同步复制：从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成了