1、聚集索引和非聚集索引

• 聚集索引：索引中键值的逻辑顺序决定了表中对应行的物理顺序，且索引到的值保存了全部数据；
• 非聚集索引：索引中的键值的逻辑顺序与表中对应行的物理顺序不一致，同时，叶子节点不再存储全数据，而是仅保存索引字段和主键信息，如果要获取其余数据，则需要使用主键进行回表操作。（因此，这里引出了覆盖索引的必要性）

2、为什么推荐整型的自增主键(不使用uuid做主键)

• uuid 不是整型，也不是自增
• 自增是为了减少建树过程中的分裂和平衡的次数，提高写入性能
• 如果主键为自增 id 的话，mysql 在写满一个数据页的时候，直接申请另一个新数据页接着写就可以了。
• 如果主键是非自增 id，为了确保索引有序，mysql 就需要将每次插入的数据都放到合适的位置上。
• 当往一个快满或已满的数据页中插入数据时，新插入的数据会将数据页写满，mysql 就需要申请新的数据页，并且把上个数据页中的部分数据挪到新的数据页上， 就造成了页分裂，这个大量移动数据的过程是会严重影响插入效率的。

3、Mysql中的索引结构

• Hash
  • 对索引的key进行一次hash计算就可以定位出数据的存储位置；
  • 仅能够满足"="，"in"，不支持范围查找（关键）
  • 存在hash冲突
• B+树
  • 叶子节点之间用指针连接，提高了区间的访问性能
  • 为什么不用B树？B+树设计增加每一次的索引数目，相同的数据量下，树高度小，减少磁盘I/O次数。

4、最左匹配原则

• 假设复合索引为(A,B,C)，底层B+树是会按照(A,B,C)的依次排序，如果打破最左匹配原则，跳过了前面的字段后，剩余字段会是无序的。

5、数据库的ACID属性

• A：原子性-事务所包含的操作要么全部成功执行，要么失败回滚。
• C：一致性-是指事务发生前后数据库要从一个一致的状态，变换到另外一个一致的状态。
• I：隔离性-多个线程并发的事务之间应该有一定的隔离措施。
• D：持久性-事务的操作一旦提交，将会对数据库造成永久的改变。

6、脏读、不可重复读、幻读

• 脏读：是指一个事务读到了另外一个事务没有提交的脏数据
• 不可重复读：A线程在事务中首先读取一次数据n=100，之后B提交了对n的修改n=150，A线程在同一次事务中，再读一次，就读到n=150。在同一事务中，两次读到的数据不一样；
• 幻读：是指A线程的业务是首先查询某一记录，如果该记录不存在，则插入记录。因此，需要执行两条指令。但是，当第一条指令执行时，同时又有另外一个线程在事务中，插入了新的记录，且与A线程的插入记录冲突，对于A线程来说，就发生了幻读。

7、事务的隔离级别

• 读未提交：在这个级别上，所有的事务都能看见其他线程未提交的记录；
• 读已提交：从一个事务开始到提交的过程中，所有的对记录的修改都是不可见的，除非该修改提交。可以解决脏读的问题，但是不可重复读；
• 可重复读：MySQL的默认隔离级别。解决了不可重复读的问题，但无法解决脏读。
• 串行化：解决所有的事务问题，但是效率太低。

8、锁

• 行锁：对某一行的数据加锁
• 表锁：对访问的整个表进行加锁
• 读锁：共享锁
• 写锁：排他锁
• 锁升级：当查询时，如果索引失效，则需要进行全表扫描，因此会对整个表进行加锁
• 间隙锁：当使用范围查找，并使用共享锁或者排他锁时，InnoDB会给复合条件的已有记录进行加锁，同时还会对记录之间的间隙进行加锁。

9、SQL语句的执行顺序

10、日志

10.1 错误日志

• 默认开启，记录数据库服务器在运行过程中发生的严重错误相关的信息。

10.2 二进制日志

• BinLog：记录了所有的DDL(数据库定义语言)和DML(数据操纵语言)语句，但是不包括数据查询语句。
• 该日志对于灾难时的数据恢复起着及其重要的作用
• 主从复制也是利用的该日志
• 默认不开启
• BinLog日志格式
  • STATEMENT：日志文件中记录的都是SQL语句。主从复制时候，就是拿到这些SQL语句，重新执行一次
  • ROW：记录的是每一行的数据变化
  • MIXED：继承了两种日志格式

10.3 查询日志

• 记录了操作数据库相关的所有操作

10.4 慢查询日志

• 记录了查询效率较低的sql语句，可以设置时间阈值，默认是10s。默认不开启。

11、MVCC

• 谈到MVCC，首先需要说起MySQL的几个组件。UndoLog、版本链、以及ReadView。
• UndoLog即撤销日志，目的就是在事务开始的时候生成，如果事务需要回滚，则作为回滚依据；

• 在MySQL的每一列数据中，还维护着两个重要的字段，一个是修改数据的当前事务Id（该事务并不一定提交），另外一个是RollBack指针，指向了之前的UndoLog日志，这样，最终就会形成如下一条链式结构，称为版本链。

  
  
• ReadView：这里不讨论ReadView具体的可见性算法，只从应用上来说。ReadView在进行快照读的时候产生，其目的是通过自身数据结构，维护了当前数据库事务状态（涉及到相关的已经提交事务以及未提交事务）对当前快照读的可见性。例如，对于当前读操作，可以屏蔽掉部分未提交事务。
• MVCC机制，实现了对于数据的当前读操作。
• 在RC隔离级别下，通过快照读就实现了对于未提交数据的屏蔽，但是与RR的区别是，每一次select都会产生新的read view；
• 在RR隔离级别下，之后在第一次快照读时产生新的read view，而后面的继续使用该read view。

12、InnoDB和MyISAM

• InnoDB：支持事务，具有外键，回滚和崩溃恢复能力，线程安全；
• MyISAM：不支持外键和事务，访问速度快
• Memony：利用内存创建表，访问速度非常快，因为数据存在于内存，而且默认使用Hash索引，但是一旦关闭，数据就会丢失；

12.1 区别

• 事务和外键：InnoDB支持事务和外键，强调安全和完整性，适合大量的DML语句。MyISAM不支持事务和外键，它适合提供高速存储和检索。
• 锁机制：
  • InnoDB支持行级锁，锁定的记录可以锁定表中的具体记录。基于索引来加锁实现。
  • MyISAM支持表级锁，锁定的是整张表。
• 索引结构
  • InnoDB使用聚集索引，索引和纪律在一起记录，既缓存索引，又缓存记录；
  • MyISAM使用非聚集索引，索引和记录分开
• 并发处理能力
  • MyISAM使用表锁，会导致并发能力下降，读之间不阻塞，但是读写阻塞；
  • InnoDB读写阻塞可以与隔离级别有关，可以采用多版本并发控制来支持高并发
• 存储文件
  • InnoDB表对应两个文件：一个.frm表结构文件，一个.ibd数据文件
  • MyISAM对应三个文件：一个.frm表结构文件，一个MYD表数据文件，一个.MYI索引文件

13、InnoDB存储结构

• 左半部分内存结构
  • BufferPool：缓存。以Page为单位，默认大小16k，底层采用了链表数据结构管理
    • Page管理机制：
      1. free page：空闲页，未被使用
      2. clean page：被使用的page，数据从未被修改过。
      3. dirty page：脏页，被使用的page，数据被修改过，与磁盘中的数据不一致
    • 针对上述三种类型，InnoDB通过三种链表结构来进行管理和维护。
    1. free list：表示空闲缓冲区，管理free page
    2. flush list：表示需要刷新到磁盘的缓冲区域，管理dirty page，内部按照page修改时间排序。脏页既存在于flush list中也存在于lru list中
    3. lru list：表示正在使用的缓冲区，管理clean page和dirty page，缓冲区以midpoint为基点，划分出了是否热点数据
    • 改进型LRU算法维护
      • 普通LRU：末尾淘汰法
      • 改进型LRU：链表划分为new和old两个部分，加入元素时并不是从表头插入，而是从中间的midpoint插入，如果数据很快再次访问，那么page就会像new列表移动，如果没有被访问，则逐步被old部分淘汰
      • 当有新的缓存页加入时，如果有足够的free page，则从free list拿下使用，如果没有从lru队列中淘汰获得新页使用
    • Buffer Pool配置参数调优
  • ChangeBuffer：缓存DML的数据，如果DML操作修改的数据没有在内存（即在BufferPool中没有命中）中，那么InnoDB不着急将修改页调入内存，而是先记录在缓冲池中，等到①该页面被调入内存；②服务器空闲；③服务器shutdown时，写回。
    • 当下次查询记录时，首先从磁盘中读取，然后将读取到的信息与ChangeBuffer中的信息合并，最终载入到BufferPool中
    • 写缓冲区仅适用于非唯一的普通索引页。如果在索引中设置了唯一性，在进行修改时，INnoDB必须要进行唯一性校验，因此该情况下写缓存失效。
  • Log Buffer：redo、undo
  • 自适应哈希索引：对Buffer Pool进行优化
• 右半部分磁盘结构
  • 系统表空间
  • 独立表空间
  • 通用表空间
  • Undo表空间
  • Redo Log

14、InnoDB数据文件

• Innodb数据文件存储结构，分为一个ibd数据文件->Segment(段)->Extent(区)->Page(页)->Row(行)
  • Tablespace：表空间，用于存储多个ibd数据文件，用于存储表的记录和索引。一个文件包含多个段
  • Segment：段，用于管理多个Extent，分为数据段(Leaf node segment)、索引段(Non-leaf node segment)、回滚段（rollback segment）。一个表至少会有两个segment，一个管理数据，一个管理索引。每创建一个索引，会多两个段
  • Extent：区，一个区固定包含64个连续页，大小为1M。当表空间不足时，需要重新分配页资源，会直接创建一个区。
  • Page：页，用于存储多行Row数据，大小为16K。包含很多种页类型，undo页，系统页，事务数据页，大的BLOB对象页。
  • Row：行，包含了记录的字段值，事务ID，滚动指针，字段指针等信息

• page是文件最基本的单位，无论何种类型的page，都是由page header，page tailer和page body组成。

  
  

15、Undo Log

15.1 Undo Log介绍

• 以撤销和取消为目的的日志，返回某个指定的状态。在事务开始之前，会将过程中修改的记录保存到Undo日志中，当事务回滚或者数据库崩溃时，可以利用Undo日志，撤销未提交的事务对数据库产生的影响；
• Undo日志在数据库事务开始的时候被创建；事务提交后，Undo日志并不会立即删除，而是会放到undo Log列表中，等待后台线程的删除。

15.2 Undo Log的作用

• 实现事务的原子性
• MVCC

16、Redo Log

• RedoLog顾名思义就是重做日志，以恢复数据为目的。
• 随着事务操作的执行，会生成RedoLog，在事务提交时，将产生的RedoLog写入到LogBuffer中，并不是随着事务的提交立即刷入磁盘。
• 当相关的事务被持久化到磁盘之后，RedoLog的使命即完成，其空间可以被重用，相应的可以被覆盖。
• Redo可以被看做是一种低成本的临时持久化方案。虽然相同的数据也被持久化到了最终的数据文件中，但是，相对于这个过程，Redo时低成本的，不涉及索引、调整等操作，只是做临时的数据备份。
• 其有三种工作模式：0-每秒提交一次RedoBuffer到OS Cache，并将OS Cache中数据刷入磁盘；1-随着事务的提交，立即提交到OS Cache，并立即刷入磁盘。这种方式，安全但是性能差；2-立即提交到OS Cache，但是后台每秒刷一次盘。

17、Undo Log和Redo Log对比总结

• UndoLog-以撤销为目的的日志，返回到某个执行的状态；RedoLog-重做日志，以数据恢复为目的；
• UndoLog-是对于数据库状态逻辑上的回滚，其会记录一个相反的操作，而是数据库从逻辑上恢复到之前状态。RedoLog：物理格式日志，记录的是物理数据页面的修改信息。
• UndoLog-当事务提交后，并不立即清除，会有一个purge线程判断是否会在其他事务的管理版本中使用；RedoLog- 当脏页数据写到磁盘后，开始清除。

18、DoubleWrite

• DoubleWrite保证了InnoDB存储页的可靠性。在对缓冲池中的脏页进行刷新时，并不是写入磁盘。而是首先将其写入到DoubleWrite中，DW分为内存和磁盘两部分，存储引擎首先将脏页拷贝到内存中的buffer中，之后再写入并刷盘至磁盘。

19、DML语句

• Modify

update 表名
set 列名=新值
where 条件

• detele

delete 
from 表名
where 条件

• insert

insert into
表名(列名) 
新值()