面面试官员系列--刨MySQL祖坟

面面试官员系列–刨MySQL祖坟

这篇就和普通面试题就不一样了，和面试官对着唠、

查询顺序

select distinct  < select_ list>   6.  
from <left_table>  <join_table>   1.
join <right_table>        2.
on   <join_condition>    
where  <where_condition>   3.  
group by   <group_by_list>   4. 
having    <having_condition>   5. 
order by <order_by_condition>   7.
limit < limit_condition>    8. 

以序号为查询顺序

如何查看索引的执行计划，应该关注那些列

explain + SQL 就可以查看索引的执行计划，我们最主要的关注应该是 type 列 。
type 类型： （从上到下，执行速度）

• System ：只有一个行数据 或者主表数据，是const 的特殊列，这种情况很少
• const ： 主键的索引
• eq_ref ： 唯一索引
• ref ： 普通索引，不保证重复
• range ： 索引命中某一范围中
• index ： 扫描整个索引树
• All ： 扫描整个表

但是别的列我们也应该关注

1. id 列 表示了 当有一条sql 中不同表的的执行先后顺序，id 相同执行顺序从上到下，id 不同大的先执行
2. select_type ：表示查询的类型

SIMPLE 简单的select查询，查询中不包含子查询或者UNION

PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY

SUBQUERY 在SELECT或WHERE列表中包含了子查询

DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中

UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED

UNION RESULT 从UNION表获取结果的SELECT

3. table ： 表示这条sql 执行的表
4. possible_keys 和 key

possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
key：实际使用的索引，如果为NULL，则没有使用索引。

5. key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。
6. ref： 显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。
7. rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好
8. Extra：包含不适合在其他列中显式但十分重要的额外信息

1 Using filesort（九死一生）：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
2.Using temporary（十死无生）：使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
3. Using index（发财了）：表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作
4 Using where：表明使用了where过滤
5 Using join buffer：表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
6 impossible where： where子句的值总是false，不能用来获取任何元组
7 select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
8 distinct : 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

索引到底是什么？

索引是关系型数据库为了加速对表中行数据检索的数据结构（磁盘存储）。
一般索引存储的空间也很大。

索引的优缺点有哪些？

优点：

• 大大加快数据检索的速度。
• 将随机I/O变成顺序I/O(因为B+树的叶子节点是连接在一起的)
• 加速表与表之间的连接

缺点：

• 从空间角度考虑，建立索引需要占用物理空间
• 从时间角度 考虑，创建和维护索引都需要花费时间，例如对数据进行增删改的时候都需要维护索引。

索引数据结构有哪些？

• 哈希索引
  哈希索引主要的构成方式就是底层为Hsh表，当hash冲突的时候然后进行拉链法进行存储，很类似于java集合中的HashMap类的实现。
• 树形索引
  树形结构主要是B+树为主的数据结构，B+树的数据结构中，每个节非叶子节点只存储索引，不存储数据，数据在叶子节点对应的的索引所对应的数据列中。因为B+树的比较方法时左开区间比较，所以在查找数据的时候会一直查找到最底层，才能命中。

哈希索引和B+树有什么区别？

因为两者数据结构上的差异导致它们的使用场景也不同，哈希索引一般多用于精确的等值查找，B+索引则多用于除了精确的等值查找外的其他查找。在大多数情况下，会选择使用B+树索引。

• 哈希索引不支持排序，因为哈希表是无序的。
• 哈希索引不、支持范围查找。
• 哈希索引不支持模糊查询及多列索引的最左前缀匹配。
• 因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点

为什么数据库选用B+树作为索引的存储方式？

1. 二叉搜索树很容易形成单链表形式
2. 平衡二叉树是可以避免的
3. B树和平衡二叉树之间存在两个问题

• IO次数
• IO饱和度 , IO利用率

因为在操作系统在和磁盘交互的时候，交换的基本单位页，一页大小为4KB，在MySQL的数据库中每个数据库页的大小为16KB，因为在操作系统进行缺页中断交互的时候满足空间局部性原则。会有一个预读的过程。所以每个节点的索引数据可以存放更多，大大提升了IO的饱和度和IO的利用率，同时也降低了IO次数。

4. B树和B+树的区别

• B树适用于随机检索，而B+树适用于随机检索和顺序检索
• B+树的空间利用率更高，因为B树每个节点要存储键和值，而B+树的内部节点只存储键，这样B+树的一个节点就可以存储更多的索引，从而使树的高度变低，减少了I/O次数，使得数据检索速度更快。
• B+树的叶子节点都是连接在一起的，所以范围查找，顺序查找更加方便
• B+树的性能更加稳定，因为在B+树中，每次查询都是从根节点到叶子节点，而在B树中，要查询的值可能不在叶子节点，在内部节点就已经找到。

那在什么情况适合使用B树呢，因为B树的内部节点也可以存储值，所以可以把一些频繁访问的值放在距离根节点比较近的地方，这样就可以提高查询效率。综上所述，B+树的性能更加适合作为数据库的索引。

索引的种类有哪些？

• 主键索引：数据列不允许重复，不能为NULL，一个表只能有一个主键索引
• 组合索引：由多个列值组成的索引。
• 唯一索引：数据列不允许重复，可以为NULL，索引列的值必须唯一的，如果是组合索引，则列值的组合必须唯一。
• 全文索引：对文本的内容进行搜索。
• 普通索引：基本的索引类型，可以为NULL

哪些情况需要建索引

• 主键自动建立主键索引（唯一 + 非空）。
• 频繁作为查询条件的字段应该创建索引。
• 查询中与其他表关联的字段，外键关系建立索引。
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
• 查询中统计或者分组字段（group by也和索引有关）。

哪些情况不适合建索引

• 记录太少的表。
• 经常增删改的表。
• 频繁更新的字段不适合创建索引。
• Where条件里用不到的字段不创建索引。
• 假如一个表有10万行记录，有一个字段A只有true和false两种值，并且每个值的分布概率大约为50%，那么对A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。

多表联查的索引分析

• 两张表

当两张表联查的时候进行索引分析，将索引建立在另端。（就是比如是 左连接， 会优先去查左表，通过左表的数据，去查右表，因为左表肯定要全查出来，所以索引级别是index ，对索引树进行全查，但是再通过连接条件去查询右表的时候就会很慢，所以要建立在右表 ，对性能上有所提高，所以是在连接的另一端进行建立索引）

• 三张表

同上理 ，索引应该建立在非主表的其余两个子表的连接字段上

JOIN 语句的优化

• 尽可能减少JOIN语句中的NestedLoop（嵌套循环）的总次数：永远都是小的结果集驱动大的结果集。
• 优先优化NestedLoop的内层循环。
• 保证JOIN语句中被驱动表上JOIN条件字段已经被索引。
• 当无法保证被驱动表的JOIN条件字段被索引且内存资源充足的前提下，不要太吝惜Join Buffer 的设置。

讲一下聚簇索引和非聚簇索引？

聚簇索引和非聚簇索引最主要的区别是数据和索引是否分开存储。

聚簇索引：将数据和索引放到一起存储，索引结构的叶子节点保留了数据行。
非聚簇索引：将数据进和索引分开存储，索引叶子节点存储的是指向数据行的地址。

在InnoDB存储引擎中，默认的索引为B+树索引，利用主键创建的索引为主索引，也是聚簇索引，在主索引之上创建的索引为辅助索引，也是非聚簇索引。为什么说辅助索引是在主索引之上创建的呢，因为辅助索引中的叶子节点存储的是主键。查询的时候先查询到主键的值进行回表查询主键索引数据。
在MyISAM存储引擎中，默认的索引也是B+树索引，但主索引和辅助索引都是非聚簇索引，也就是说索引结构的叶子节点存储的都是一个指向数据行的地址。并且使用辅助索引检索无需访问主键的索引。

• 聚簇索引，适合查询和增删。
• 非聚簇索引，不利于增删，查询更快。

非聚簇索引一定会进行回表查询吗？

上面是说了非聚簇索引的叶子节点存储的是主键，也就是说要先通过非聚簇索引找到主键，再通过聚簇索引找到主键所对应的数据，后面这个再通过聚簇索引找到主键对应的数据的过程就是回表查询，那么非聚簇索引就一定会进行回表查询吗？

答案是不一定的，这里涉及到一个索引覆盖的问题，如果查询的数据再辅助索引上完全能获取到便不需要回表查询。例如有一张表存储着个人信息包括id、name、age等字段。假设聚簇索引是以ID为键值构建的索引，非聚簇索引是以name为键值构建的索引，select id,name from user where name = ‘zhangsan’;这个查询便不需要进行回表查询因为，通过非聚簇索引已经能全部检索出数据，这就是索引覆盖的情况。如果查询语句是这样，select id,name,age from user where name = ‘zhangsan’;则需要进行回表查询，因为通过非聚簇索引不能检索出age的值。那应该如何解决那呢？只需要将索引覆盖即可，建立age和name的联合索引再使用select id,name,age from user where name = ‘zhangsan’;进行查询即可。
所以通过索引覆盖能解决非聚簇索引回表查询的问题。

索引的使用场景有哪些？

• 对于中大型表建立索引非常有效，对于非常小的表，一般全部表扫描速度更快些。
• 对于超大型的表，建立和维护索引的代价也会变高，这时可以考虑分区技术。
• 如何表的增删改非常多，而查询需求非常少的话，那就没有必要建立索引了，因为维护索引也是需要代价的。
• 一般不会出现再where条件中的字段就没有必要建立索引了。
• 多个字段经常被查询的话可以考虑联合索引。
• 字段多且字段值没有重复的时候考虑唯一索引。
• 字段多且有重复的时候考虑普通索引。

索引的设计原则？

• 最适合索引的列是在where后面出现的列或者连接句子中指定的列，而不是出现在SELECT关键字后面- 的选择列表中的列。
• 索引列的基数越大，索引的效果越好，换句话说就是索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差，因为列的基数最多也就是三种，大多不是男性就是女性。
• 尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，并且索引高速缓存中的块可以容纳更多的键值，会使得查询速度更快。
• 尽量利用最左前缀。
• 不要过度索引，每个索引都需要额外的物理空间，维护也需要花费时间，所以索引不是越多越好。

索引失效的情况

• 在联合索引的查询条件下，最左前缀原则，当遇到范围的符号会停止搜索
• 条件中有or，例如 select * from table_name where a = 1 or b = 3
• 在索引上进行计算会导致索引失效，例如select * from table_name where a + 1 = 2
• 在索引的类型上进行数据类型的隐形转换，会导致索引失效，例如字符串一定要加引号，假设 select * from table_name where a = '1'会使用到索引，如果写成select * from table_name where a = 1则会导致索引失效。
• 在索引中使用函数会导致索引失效，例如select * from table_name where abs(a) = 1
• 在使用like查询时以%开头会导致索引失效
• 索引上使用！、=、<>进行判断时会导致索引失效，例如select * from table_name where a != 1
• 索引字段上使用 is null/is not null判断时会导致索引失效，例如select * from table_name where a is null

索引优化的建议

索引优化的一般性建议：

• 对于单值索引，尽量选择针对当前query过滤性更好的索引。
• 在选择复合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
• 在选择复合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引。
• 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

口诀：

• 带头大哥不能死。
• 中间兄弟不能断。
• 索引列上不计算。
• 范围之后全失效。
• 覆盖索引尽量用。
• 不等有时会失效。
• like百分加右边。
• 字符要加单引号。
• 一般SQL少用or。

查询优化

1. 小表驱动大表 （相当于外循环为小范围，内循环为大范围，类似于避免连接次数过多）

优化原则：对于MySQL数据库而言，永远都是小表驱动大表。

in和exists一般用于子查询。

• 使用exists时会先进行外表查询，将查询到的每行数据带入到内表查询中看是否满足条件；使用in一般会先进行内表查询获取结果集，然后对外表查询匹配结果集，返回数据。
• in在内表查询或者外表查询过程中都会用到索引。
• exists仅在内表查询时会用到索引
• 一般来说，当子查询的结果集比较大，外表较小使用exist效率更高；当子查询寻得结果集较小，外表较大时，使用in效率更高。
• 对于not in和not exists，not exists效率比not in的效率高，与子查询的结果集无关，因为not in对于内外表都进行了全表扫描，没有使用到索引。not exists的子查询中可以用到表上的索引。
• in 是把外表和内表作为hash 连接，而 exists 是对外表作为 loop 循环，每次loop 循环再对内表查询，当主表比子查询表大使用in， 反之 使用exists 。
  2. oreder by 优化

ORDER BY子句，尽量使用索引排序，避免使用Using filesort排序。

MySQL支持两种方式的排序，FileSort和Index，Index的效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

ORDER BY满足两情况，会使用Index方式排序：

• ORDER BY语句使用索引最左前列。
• 使用WHERE子句与ORDER BY子句条件列组合满足索引最左前列。

结论：尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀原则。

如果不在索引列上，File Sort有两种算法：MySQL就要启动双路排序算法和单路排序算法

1. 双路排序算法：MySQL4.1之前使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和ORDER BY列，対他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。一句话，从磁盘取排序字段，在buffer中进行排序，再从磁盘取其他字段。
2. 单路排序算法：从磁盘读取查询需要的所有列，按照ORDER BY列在buffer対它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。

但是单路排序算法有问题：如果SortBuffer缓冲区太小，导致从磁盘中读取所有的列不能完全保存在SortBuffer缓冲区中，这时候单路复用算法就会出现问题，反而性能不如双路复用算法。

单路复用算法的优化策略：

• 增大sort_buffer_size参数的设置。
• 增大max_length_for_sort_data参数的设置。

提高ORDER BY排序的速度：

• ORDER BY时使用SELECT *是大忌，查什么字段就写什么字段，这点非常重要。在这里的影响是：

• 当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会使用单路排序算法，否则使用多路排序算法。

• 两种排序算法的数据都有可能超出sort_buffer缓冲区的容量，超出之后，会创建tmp临时文件进行合并排序，导致多次IO，但是单路排序算法的风险会更大一些，所以要增大sort_buffer_size参数的设置。

• 尝试提高sort_buffer_size：不管使用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。

• 尝试提高max_length_for_sort_data：提高这个参数，会增加用单路排序算法的概率。但是如果设置的太高，数据总容量sort_buffer_size的概率就增大，明显症状是高的磁盘IO活动和低的处理器使用率。

3. group by 优化

• GROUP BY实质是先排序后进行分组，遵照索引建的最佳左前缀。

• 当无法使用索引列时，会使用Using filesort进行排序，增大max_length_for_sort_data参数的设置和增大sort_buffer_size参数的设置，会提高性能。

• WHERE执行顺序高于HAVING，能写在WHERE限定条件里的就不要写在HAVING中了。

4. 总结

• MySQL两种排序方式：Using filesort和Index扫描有序索引排序。
• MySQL能为排序与查询使用相同的索引，创建的索引既可以用于排序也可以用于查询。

慢查询日志是什么

• MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
• long_query_time的默认值为10，意思是运行10秒以上的语句。
• 由慢查询日志来查看哪些SQL超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒钟的SQL，结合之前explain进行全面分析。

当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

SQL优化索引

• SQL的执行一定要满足索引的搜素（不以数据量小听之任之）/如何判断SQL是否使用索引

查看执行计划，看是否有索引，生成最优的执行计划 desc / explain +SQL

• 选择主键索引时为什么最好选择自增

当主键自增时候新增数据，只会变动最右子树。

• 为什么经常变动的列不建议创建索引

对数据库性能消耗大，维护不方便。

• 重复度高的列不建议建立索引，可能引发适得其反的效果【离散型】

因为重复度高的查询到的也是一个范围，可能会进行多次回表。

• 执行SQL时，注意列的隐式转换带来的索引失效的问题

由于数据类型不匹配，导致索引失效，变成全局扫描的模式。

• 合理利用联合索引解决常出现的多字段条件查询/能正确判断联合索引列的使用情况
• 创建合适的联合索引，注重列的排列顺序（最左前置原则/关键字比较规则）

最左前置原则：最常用列 > 离散度高的列 > 最少空间
最左前缀匹配：联合索引，会从第一个索引开始等值匹配，知道遇到范围性的字段就开始停止搜索。

• Innodb引擎的执行，巧妙的利用好了覆盖索引/ICP机制（创建更合适的联合索引）减少回表操作

索引覆盖：通过索引项的信息可直接返回所需的查询列，则该索引称之为覆盖索引，减少回表操作

数据库的存储引擎你了解多少？

• CSV存储引擎

特点：

1. 不能定义索引，列定义必须为NOT NULL ，不能设置自增列
2. CSV表的数据的存储格式用“，” ，可直接编辑文件进行进行数据的编排
3. 数据安全性低

应用场景：

1. . 不适合大表或者数据的再线处理
2. 数据的快速导出导入
3. 表格直接转成CSV

• Archive存储引擎

特点：

1. 压缩协议进行数据的存储，磁盘占用少 *
2. 只支持insert 和select两种操作
3. 值允许自增ID列建立索引

应用场景：

1. 数据备份系统（日志系统/ 文档归档）
2. 大量设备高频的数据采集

• Memory 存储引擎

特点：

1. 数据都是存储在内存中，处理效率高，表的大小限定默认为16M
2. 不支持大数据存储类型的字段，如blog，text类型
3. 不支持可变长类型字段 varchar（32） = char（32）
4. 支持hash索引，等值查询效率高
5. 数据的可靠性低，重启数据库服务或者数据库崩溃数据丢失

应用场景:

1. 热点数据加速加载，（功能类似于缓存中间件）
2. Mysql临时表存储（查询结果在内存找那个计算数据）
3. 如果中间表数据过大，就会使用Myisam

• Myisam存储引擎

5.1之前默认的数据库存储引擎

特点：

1. 较快的数据插入和读取性能
2. 支持索引缓存，不支持数据缓存
3. 数据存储具有较小的磁盘占用
4. 支持表级别的锁，不支持事务
5. 数据文件与索引文件分开存储，主键索引与辅助索引同级
6. 针对数据统计有额外的常数存储，故而count（*）的查询效率很高

应用场景：

1. 只读应用或者以读为主的业务

• Innodb存储引擎

特点：

1. 支持事务
2. 行级锁
3. 支持索引和数据缓存
4. 聚集索引
5. 外键支持
6. MVCC

应用场景
无脑选择

SQL的执行过程

上图就是整在存储引擎在访问数据库之前干的事情，因为查询缓存存在很多不好的限制，判断限制 和失效规则 都十分苛刻 所以关闭了缓存在8.0之后。

一个DQL语句的执行

一条SQL语句查找的时候，首先经过Mysql的服务层也就是解析和优化之后。在Innodb的存储引擎下，进行查找。其中先去Buffer Pool中寻找，未命中才会去数据库中查找。

Buffer Pool是什么？

• Buffer Pool用于缓存数据表数据与索引数据，把磁盘上的数据加载到bufferPool ，避免每次都进行磁盘IO，起到加速访问的作用。Buffer Pool 在内存中。

• 基于空间局部性原理（预读），磁盘访问按照数据库页大小读取到能够提高性能，缓冲池按数据库页（Innodb_page_size) 缓冲数据，所以在BufferPool中，不仅要缓存时间使用到的数据，而且还需要缓存非目标数据和预读的数据

• Buffer Pool 的存储结构设计总体要遵循LRU 的特性

• Buffer Pool 需要解决两大难题 预读失效 和 缓存池污染

预存失效： 由IO操作的特性将未实际使用的数据加载到了Buffer Pool中缓存了，实际上后续却并未使用

缓存池污染： 可能SQL的执行需要扫描大量的页数据，为了缓存这些页数据，导致缓冲池中大量热点数据替换出去。

Buffer Pool的设计

新生带+ 老生带停滞时间阈值

一个数据来了后先放在老年带的头部，当被多次访问，而且停留时间达到相应的时间阈值就会到新生带 。

DML事务的操作

innodb的一次事务的提交过程中，事务的操作并不会马上将变更的数据刷新到磁盘中。
在Innodb中事务的完结只需要将数据的变更记录在Buffer Pool 中形成脏页 ，完成Redo Log记录即可
脏页是指在Buffer Pool 中记录的数据与磁盘中不一致的情况。
刷脏是指刷脏线程将Buffer Pool 的脏页刷新落到磁盘的过程。
所以当用户在执行一项DML事务操作的时候,通常是分为四个步骤

1. 先在Mysql 服务器通过后，在Innodb存储引擎中存储时，在Buffer Pool 中形成脏页。
2. 然后在Redo Log Buffer 上中进行日志缓存
3. Mysql服务器进行binlog日志。
4. 然后把Redo Log Buffer 中的内容刷新到磁盘中。事务就完成了

十分像分布式事务中2PC的操作

刷脏的时机有哪些？

• 当Redo Log写满了（有两个文件 每个48M）
• Buffer Pool 空间不够用
• MySQL 服务属于空闲状态
• Mysql正常关闭时。

介绍一下Redo Log日志

Redo Log 日志是重做日志，记录的是事务提交过程中数据的变更情况，是物理日志。其主要用于保证数据库服务意外中断，导致脏页未及时刷脏，数据的恢复手段。
而Redo Log Buffer为Redo Log 在内存中日志缓存区，一旦Redo Log Buffer 中的日志内容刷新到磁盘中的Redo Log日志文件中，标志着事务的成功。Redo Log Buffer 中的大小为16M
Redo Log 是一组固定大小的文件，记录日志信息循环使用。

Redo Log 的刷盘策略有哪些

因为在Log Buffer 向磁盘中刷的时候会经过两个过程，一个是用户态，一个是内核态，先刷到用户磁盘上，然后操作系统在刷到盘上文件中。这个操作两个过程不是原子性的。有可能失败
刷盘策略的参数 innodb_flush_log_at_trx_commit 有三个 0,1 ，2
0 : 表示在向用户盘中刷新的时候，以每秒几个事务进行刷新，然后在向文件中写时每一笔事务为单位进行刷盘提交。
2.：表示向用户盘中刷新的时候，以每个事务为单位进行刷新，再向系统磁盘中刷新的时候以每秒几个事务为单位进行刷新
1 ：在两个过程中都是每一笔作为单位提交。

所以1是最安全的

Binlog日志是什么？

Binlog是Mysql Serrver层逻辑过程日志文件。
Binlog以事件的形式记录的所有的DDL,DML语句操作。
Binlog以文件追加的方式进行记录，没有大小的限制。
Binlog是主从复制（搭建集群），和数据恢复的神器。（生产环境开启）
参数主要有三种记录方式

• row （生产中建议开启）： 记录每一条数据的变化
• Statement ： 只记录sql语句
• Mixed 自动判断切换

Change Buffer是什么？

Change Buffer 是一块特殊的数据缓存区。
Change Buffer 是保存唯一索引页的数据变更并且这些数据不存在与Buffer Pool 中的空间。
主要是为了缓存变更记录，当查询操作到达的时候将数据合并加载到Buffer Pool，减少其IO次数。

Innodb中是如何保证事务的特性

• 原子性（Atomicity）
  最小的工作单位，要么一起成功，要么一起失败，进行回滚

提交：Innodb 事务的提交过程
回滚： Undo Log

• 一致性（Consistency）
  事务中操作的数据改变是符合预期的，数据的中间态无需对外暴露，结果符合预期即可。

WAL（write ahead logging）日志预写机制
脏页未刷脏 通过Redo Log 来保证数据不会丢失
刷脏出现页断裂情况下， 通过双写机制保证数据的不丢失

• 隔离性（Isolation）
  并发访问场景下事务间共享数据可见性设定带来的问题

锁的机制
MVCC机制

• 持久性（Durability）
  事务所做的修改就会永久保存，不会因为系统意外导致数据的丢失。

Redo Log Redo Log Buffer 刷盘到file的策略

Undo Log是什么讲一下。

Undo Log 是物理日志，记录的是每个事务的过程中每条数据的变化情况。
Undo Log默认保存在系统的空间表中
Undo Log的设计初衷主要的作用是在事务的异常中断，或主动（rollback）过程中，我们可以基于UndoLog中记录的数据进行数据的回滚，保证原子性的数据回滚
Undo Log 记录了事务过程中各个版本历史，所以事务在版本控制层面，Undo Log数据可以作为数据旧版本快照，可以提供其他并发事务进行快照读。

刷脏为啥会出现页断裂，双写机制是什么？

因为数据库页的大小和操作系统IO每次的操作的页的大小不同，会分多次写入。
多次写入的过程中就会出行断裂，
双写机制就类似与之前Redo Log 写入一样，是两次过程写入，当在双写磁盘中写好之后才会刷脏

MVCC 是什么

MVCC(multiple version concurrent control)是一种控制并发的方法，主要用来提高数据库的并发性能

MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改

隐藏字段：
在内部，InnoDB 存储引擎为每行数据添加了三个 隐藏字段：

• DB_TRX_ID（6字节）：表示最后一次插入或更新该行的事务 id。
• DB_ROLL_PTR（7字节） 回滚指针，指向该行的 undo log 。如果该行未被更新，则为空
  -DB_ROW_ID（6字节）：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引

ReadView： 主要是用来做可见性判断，里面保存了 “当前对本事务不可见的其他活跃事务”

undo-log：

• 当事务回滚时用于将数据恢复到修改前的样子
• 另一个作用是 MVCC ，当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过 undo log 读取之前的版本数据，以此实现非锁定读

不同事务或者相同事务的对同一记录行的修改，会使该记录行的 undo log 成为一条链表，链首就是最新的记录，链尾就是最早的旧记录

事务的隔离级别引发的问题有哪些？

• 脏读： 事务一 读取了事务二未提交的数据

• 不可重复读 ： 在同一个事务中两次读取同一个数据时，因为有别的事务进行了修改导致，两次读取的事务不一致问题

• 幻读：在同一次事务中两次读取的数据个数不一致 ； 有别的线程进行对其添加
  （第一次查询到了一个数据），（另外一个线程进行了新增的操作，第二次查询的时候出现了两个数据，出现了幻读）

事务的隔离级别有哪些？

MySQL默认的隔离级别是可重复读（repeatable-read），MySQL事务隔离级别有以下四种：

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	innodb中不存在
串行化（serializable）	否	否	否

• 读未提交（read-uncommitted）：对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
  ​ 读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。
• 读已提交（read-committed）：对方事务提交之后的数据我方可以读取到。
  ​ 种隔离级别解决了: 脏读现象没有了。
  ​ 读已提交存在的问题是：不可重复读。
• 可重复读（repeatable-read）：这种隔离级别解决了：不可重复读问题。
  ​ 这种隔离级别存在的问题是：读取到的数据是幻象。
• 串行化（serializable）：解决了所有问题。效率低。需要事务排队。

Innodb 隔离级别方案

• Lock Based Concurrency Control （LBCC)

事务并发访问数据的时，即对数据加锁，阻止其他事务对数据进行操作，利用锁的排他性独占数据的操作限权。

当前读

• Multi Version Concurrency Contol (MVCC)

事务并发访问数据的时候，对正在事务内处理的数据做多版本管理，避免写操作堵塞，从而引发读操作的并发阻塞问题，将数据在当下时间点进行一份数据快照的备份。并用这个快照来提供给其他事务进行一致性读取

快照读 ----》 在undo log 中

在Mysql中锁的分类

• 共享锁（行锁）：Share Locks
• 排他锁（表锁）：Exclusive Locks

1. 共享锁 ： 读锁（S锁） 多个事务对数据可以共享访问，但不能操作修改

使用方式

select * from t_table where id= 1 lock in share mode ---- 加锁

commit / rollback ----------释放

2. 排它锁： 写锁（X），事务获取了X锁，其他事务就不能再获取锁。只有获取了X锁的事务才能对数据进行修改和读取

使用方式：
DML 都是加锁
select * from t_table where id=1 for update
提交回滚进行释放

Innodb 行锁的实现

innodb的行锁是通过给索引的索引项加锁实现

SQL的执行基于索引的检查，Innodb 才使用行锁，未使用索引检索的SQL执行Innodb使用表锁

基于辅助索引检索的SQL，辅助索引对应的主键索引都将锁定指定的索引项
注意

DML语句默认加X锁，如果没有索引就会将整个表进行锁住。

意向锁

• 意向共享锁（IS）：SQL执行加共享锁之前必须先获取该表的IS 锁，意向共享锁之间是可以兼容的。
• 意向排它锁（IX）：SQL执行加排它锁之前必须先获得该表的X锁，意向排它锁之间是可以相互兼容的。

意向锁（IX,IS）是Innodb引擎操作数据之前自动加的，不需要用户干预。

意义：当事务想要进行锁表的时候，可以先判断意向锁是否存在，存在的时候可以快速返回，该表不能启动表锁。

在Innodb 中行锁的三种实现

• SQL 的执行按索引等值匹配过程且能命中数据 ------ 记录锁：实际将某个数据列锁住

• SQL的执行按照索引检索但是无法命中数据 --------间隙锁 ：锁住数据不存在的区间（左开右开）

• SQL的执行使用索引的范围查找方式 -----------临键锁 ：锁住命中几率区间和下一个区间（左开右闭）

Record lock：对索引项加锁
Grap lock：对索引之间的“间隙”、第一条记录前的“间隙”或最后一条后的间隙加锁。
Next-key lock：当sql按照索引范围查找的时候，锁住命中几率区间和下一个区间（左开右闭）

默认的临界锁实现导致不能幻读

大表数据查询如何进行优化？

• 索引优化
• SQL语句优化
• 水平拆分
• 垂直拆分
• 建立中间表
• 使用缓存技术
• 固定长度的表访问起来更快
• 越小的列访问越快

什么是垂直分表、垂直分库、水平分表、水平分库？　＊＊＊

• 垂直分表：将一个表按照字段分成多个表，每个表存储其中一部分字段。一般会将常用的字段放到一个表中，将不常用的字段放到另一个表中。

垂直分表的优势：

避免IO竞争减少锁表的概率。因为大的字段效率更低，第一数据量大，需要的读取时间长。第二，大字段占用的空间更大，单页内存储的行数变少，会使得IO操作增多。

可以更好地提升热门数据的查询效率。

• 垂直分库：按照业务对表进行分类，部署到不同的数据库上面，不同的数据库可以放到不同的服务器上面。

垂直分库的优势：

降低业务中的耦合，方便对不同的业务进行分级管理。
可以提升IO、数据库连接数、解决单机硬件资源的瓶颈问题。
垂直拆分（分库、分表）的缺点：

主键出现冗余，需要管理冗余列
事务的处理变得复杂
仍然存在单表数据量过大的问题

• 水平分表：在同一个数据库内，把同一个表的数据按照一定规则拆分到多个表中。

水平分表的优势：

解决了单表数据量过大的问题
避免IO竞争并减少锁表的概率

• 水平分库：把同一个表的数据按照一定规则拆分到不同的数据库中，不同的数据库可以放到不同的服务器上。

水平分库的优势：

解决了单库大数据量的瓶颈问题
IO冲突减少，锁的竞争减少，某个数据库出现问题不影响其他数据库（可用性），提高了系统的稳定性和可用性

• 水平拆分（分表、分库）的缺点：

分片事务一致性难以解决
跨节点JOIN性能差，逻辑会变得复杂
数据扩展难度大，不易维护
在系统设计时应根据业务耦合来确定垂直分库和垂直分表的方案，在数据访问压力不是特别大时应考虑缓存、读写分离等方法，若数据量很大，或持续增长可考虑水平分库分表，水平拆分所涉及的逻辑比较复杂，常见的方案有客户端架构和恶代理架构。

了解读写分离吗？

读写分离主要依赖于主从复制，主从复制为读写分离服务。

读写分离的优势：

• 主服务器负责写，从服务器负责读，缓解了锁的竞争
• 从服务器可以使用MyISAM，提升查询性能及节约系统开销
• 增加冗余，提高可用性

主从复制的原理

MySQL复制：为保证主服务器和从服务器的数据一致性，在向主服务器插入数据后，从服务器会自动将主服务器中修改的数据同步过来。

主从复制的原理：

主从复制主要有三个线程：binlog线程，I/O线程，SQL线程。

• binlog线程：负责将主服务器上的数据更改写入到二进制日志（Binary log）中。
• I/O线程：负责从主服务器上读取二进制日志（Binary log），并写入从服务器的中继日志（Relay log）中。
• SQL线程：负责读取中继日志，解析出主服务器中已经执行的数据更改并在从服务器中重放

复制过程：

• Master在每个事务更新数据完成之前，将操作记录写入到binlog中。
• Slave从库连接Master主库，并且Master有多少个Slave就会创建多少个binlog dump线程。当Master节点的binlog发生变化时，binlog dump会通知所有的Slave，并将相应的binlog发送给Slave。
• I/O线程接收到binlog内容后，将其写入到中继日志（Relay log）中。
• SQL线程读取中继日志，并在从服务器中重放。

主从复制的作用：

• 高可用和故障转移
• 负载均衡
• 数据备份
• 升级测试