MySQL架构、日志事务以及索引介绍

1. MySQL分为server层和存储引擎层

2. server层

   1. 连接器：管理连接权限验证
   2. 查询缓存：命中缓存，直接换回结果
   3. 分析器：分析语法
   4. 优化器：生成执行计划，选择索引
   5. 执行器：操作索引，返回结果

3. 存储引擎：

   存储引擎负责数据的存储和提取，器架构是插件式的，innodb在MySQL5.5.5版本开始成为MySQL的默认存储引擎

   1. InnoDB：支持事务，支持外键，InnoDB是聚集索引，数据文件和索引绑在一起的，必须要有主键，通过主键查询的效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据，不支持全文索引。
   2. MyISAM：不支持事务，不支持外键，MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的，查询效率上MyISAM要高于InnoDB，因此做读写分离的时候一般选择用InnoDB做主机，MyISAM做从机。
   3. Memory：有比较大的缺陷使用场景较少，文件数据都存储在内存中，如果mysqld进程发生异常时，重启或关闭都会机器这些数据都会消失。

4. SQL的执行过程

   1. 客户端连接上MySQL的连接器，连接器获取权限，维持管理连接；连接完成后，如果没有后续的指令，这个连接就处于空闲状态。如果太长时间不适用这个连接，这个连接就会断开，空闲时长默认是8小时，由 wait_timeout 参数控制。

   2. 往MySQL数据库发送一条SQL，这个时候查询缓存开始工作，看看之前有没有执行过这个SQL，如果有则直接返回缓存数据到客户端，只要对表执行过更新操作缓存都会失效，因此一些很少使用更新的数据表可考虑使用数据库缓存，对频繁更新的表使用缓存反而弊大于利，使用缓存的方法如以下SQL，通过SQL_CACHE指定。

      select SQL_CACHE * from table where xxx = xxx
      

   3. 当未命中缓存的时候，分析器开始工作，分析器判断是select还是update还是insert，分析语法是否正确。

   4. 优化器根据表的索引和SQL语句决定采用哪个索引，决定join的顺序。

   5. 执行器执行SQL，调用存储引擎的接口，扫描遍历表，或者插入更新数据。

5. MySQL日志：MySQL有两个重要的日志，redolog和binlog，redolog是属于InnoDB的日志，binlog则属于Server层的日志。

   1. redolog又称作重做日志，用于记录事务操作的变化，记录的是数据修改后的值，不管事务是否提交都会记录下来。它在数据库重启恢复的时候被使用，InnoDB利用这个日志恢复到数据库宕机前的状态，以此来保证数据库的完整性。redolog是物理日志，记录的是某个表的数据做了哪些修改，redolog是固定大小的，也就是说后面的日志会覆盖前面的日志。
   2. binlog又称作归档日志，它记录了对MySQL执行更改的所有操作，到不包括select和show这类的操作，binlog是逻辑日志，记录的是某个表执行了哪些操作，binlog是以追加的形式写入日志，后面的日志不会被前面的覆盖。

6. 数据的更新过程，执行一个更新操作的过程如下：

   读取对应的数据到内存 -> 更新数据 -> 写redolog日志 -> redolog日志的状态为prepare -> 写binlog日志 -> 提交事务 -> redolog日志状态状态为commit，数据正式写入日志文件。我们发现redolog日志的提交是"两段式提交"，这样做的目的是为了数据恢复的时候确保数据恢复的准确性，因为数据恢复是通过备份的binlog来完成的，所以要确保redolog和binlog一致。

7. 索引：索引按照数据结构可以分为哈希表、有序数组、搜索树、跳表：

   1. 哈希表适用于只有等值查询的场景；
   2. 有序数组适用于等值查询和范围查询的场景，但有序数组的更新代价很大，所以最好用于静态数据表；
   3. 搜索树的搜索效率稳定，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高；
   4. 跳表可以理解为优化后的哈希索引

8. InnoDB使用了B+tree索引模型，而且是多叉树。虽然二叉树的索引效率是最高的，但是索引需要写入磁盘，如果使用二叉树磁盘io会变得很频繁。在InnoDB中分为主键索引(聚簇索引)和非主键索引(二级索引)。主键索引包含了该行数据的全部信息，二级索引保存了该行数据的主键，所以使用二级索引的时候会先查出主键的值，然后回表查询出数据，而使用主键索引则不需要回表。

9. 对二级索引而言可以使用覆盖索引来优化SQL，如下

   select * from table where key = 1;
   select id from table where key = 1;
   

   key是一个二级索引，第一条sql是先查出id，然后根据id回表查询出真正的数据，而第二条查询索引后直接返回数据不需要回表。第二条sql索引key覆盖了我们查询的需求，称作覆盖索引。

10. 普通索引和唯一索引：

    InnoDB是按照数据页来读数据的，当要读取一条数据的时候，先将本页数据全部读入内存，然后找到对饮的数据，而不是直接读取，每页数据的默认大小为16KB。

    当一个数据页需要更新的时候，如果内存中有盖数据页就直接更新，如果没有数据页则在没有影响数据一致性的前提下，将更新操作先更新到change buffer中，在下次查询需要访问这个数据页的数据的时候再写入更新操作，除了查询会将change buffer写入磁盘中。对于唯一索引来说，所有的操作都要先判断这个操作是否会违反唯一性约束，因此唯一索引的更新无法使用change buffer，而普通索引可以，唯一索引更新比普通索引更新多一个唯一性校验的过程。

11. 联合索引：

    两个或更多个列上的索引称作联合索引(符合索引)。联合索引可减少索引的开销，以联合索引(a, b, c)为例，建立这样的索引相当于建立了a, ab, abc三个索引—MySQL从左到右的使用索引中的字段，一个索引可以只是用索引中的一部分，但只能使用最左侧的部分，而且当最左侧的索引字段是常量引用时，就十分有用，这就是最左前缀原则。由最左前缀原则可知，组合索引是有顺序的，那么哪个索引放在前面就比较有讲究了。对于组合索引还有一个点，就是索引下推。

12. 索引下推：

    假设有索引(a, b, c)，有如下的SQL：

    select * from table where a = XXX and b = XXX;
    

    这个SQL会进行两次筛选，第一次查出a = XXX的数据，再从a = XXX中查出b = XXX的数据。使用索引下推和不适用索引下推的区别在于：不使用索引下推会先查出a = XXX的数据的主键，然后根据查询出的主键回表查询出全行数据，再在全行数据上查询区b = XXX的数据；而索引下推的执行过程是先查出a = XXX数据的主键，然后再这些主键上二次查询出b = XXX的主键，然后回表。

13. 索引下推的特点：

    1. InnoDB引擎的表，索引下推只能用于二级索引
    2. 索引下推一般可用于所查询字段不全是联合索引的字段，查询的条件为多条件查询且查询条件子句字段全是联合索引。

14. 前缀索引：

    有时候需要索引很长的字符列，这回让索引变的很大很慢还占内存。通常可以以开始的字符部分作为索引，这就是前缀索引。这样就可以大大节约索引空间，从而提高索引效率，但这样也会降低索引的选择性。

15. 脏页对数据库的影响：

    当内存数据页和磁盘的数据不一致的时候我们成这个内存页为脏页，内存数据写入磁盘后数据一致，成为干净页。当要读入数据而数据库没有内存的时候，就需要淘汰内存中的数据页，干净页可以直接淘汰，而脏页需要先刷入磁盘再淘汰。如果一个查询要淘汰的脏页太多会导致查询的时间边长。为了减少脏页对数据库性能影响，InnoDB会控制脏页的比例和脏页的刷新时机。