Mysql夺命50连环问
一、Explain表达式1.expalin有哪些主要字段2.type字段有哪些值3.Extra有哪些主要指标4.Explain关联子查询信息补充
二、数据库事务5.事务的特性ACID和隔离级别6.说下MVCC的内部细节7.Mysql的可重复读怎么实现的8.可重复读解决幻读了吗
三、索引和sql优化9.hash索引的优缺点10.索引失效的例子和场景11.什么是索引覆盖12.使用索引一定可以提高效率吗13.聚簇索引和非聚簇索引14.索引下推15.最左前缀原则16.什么是自适应hash索引17.自增和UUID如何选择18.B树与B+树的区别19.一个B+树索引大概能存多少记录20.如何做分页查询优化21.慢查询优化如何做22.如何做join的优化23.什么是行溢出，InnoDB如何解决行溢出24.什么是写失效，Doublewrite Buffer如何解决写失效25.select(1) 、select(列)、select(*) 有什么区别26.说说mysql执行一条查询语句的过程27.mysql内部支持缓存查询吗28.为什么like以%开头会导致索引失效29.说说Mysql关键字的执行顺序
四、存储引擎30.InnoDB和MySAIM的区别是什么31.什么是Buffer Pool32.InnoDB如何管理page页33.LRU算法，Mysql为什么要改进LRU33.InnoDB IO线程相关参数优化了解过吗34.InnoDB日志相关的参数优化了解过吗(redo log)35.InnoDB内存相关参数优化36.为什么Change Buffer（写缓冲区）只适用于非唯一索引
五、各种锁37.数据库锁的种类38.说说共享锁（S锁）和排它锁（X锁）39.InnoDB的行锁的实现40.并发事务会产生哪些问题41.Mysql死锁的原因和处理方法
六、数据库日志42.三种日志的区别：undo log、redo log 、bin log43.redo log 和 undo log 的持久化策略44.bin log与 undo log的区别45.Mysql的bin log有哪几种日志格式46.Mysql线上修改大表结构有哪些风险
七、数据库优化和架构47.Mysql的体系架构48.说说Mysql的主从复制49.如何解决主从不同步的问题50.什么是分库分表，什么时候进行51.四大范式
这里分多个模块进行mysql面试题的总结Mysql的常见面试题，不做详细知识的普及，设计到的点会简单先介绍基础知识再说面试题，以方便理解。
一、Explain表达式
这里先简单普及下Explain的基础知识，Explain在各个数据库中基本都是存在的，用于帮住我们查看sql的执行计划，他可以模拟数据库的优化，将数据库的执行操作呈现给使用者，以此来帮助我们对sql进行更好地优化。
1.SELECT TYPE：查询的类型，如SIMPLE（简单查询）、PRIMARY（外层查询）、UNION（联合查询）、DEPENDENT UNION（依赖的联合查询）等。
2.TABLE：查询涉及的表名。
3.TYPE：连接类型，表示MySQL在表中找到所需行的方式，例如system（系统表）、const（常量查询）、eq_ref（唯一索引查找）、ref（非唯一索引查找）、range（范围扫描）、index（索引扫描）、ALL（全表扫描）等。这个字段的值可以帮助你判断查询的性能，一般来说，system和const是最好的，而ALL是最差的。
4.POSSIBLE_KEYS：查询可能使用的索引。
5.KEY：实际使用的索引。
6.KEY_LEN：使用的索引的长度。可根据该字段判断索引建立的列是否合适。
7.REF：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
8.ROWS：MySQL估计为了找到所需的行而要读取的行数。
9.FILTERED：表示符合查询条件的数据百分比。越高表示数据库只需要很少的过滤就可以定位目标行，越高越好
10.EXTRA：额外的信息，如Using index（使用覆盖索引）、Using temporary（使用临时表）、Using filesort（使用文件排序）等
1.expalin有哪些主要字段
这个是基础考察，通常说出select_type、table、type（重要）、possible_keys、key 、extra基本没问题。注意最容易考察到的是type和extra两个字段，因为这两个里面的信息是最丰富的。
2.type字段有哪些值
该字段主要作用是标识sql的查询等级，他的值和解释如下，从上到下效率依次递减 在sql优化时通常需要将sql的优化到range级别，range以上都是精确查找这个不适用于普遍大多数情况，所以一般能够优化到range即可。system是查询的系统表所以我们的业务表是无法达到的，const是主键的精确查找，eq_ref是唯一索引的精确查找，ref则是非唯一索引的精确查找，range则是根据索引的范围查找，一般要求sql优化是需要达到这个级别的，index则是扫描索引的全部节点，all则是扫描原表的全部记录。（注意：Mysql的的Innodb主键是聚簇索引，主键索引中存储的是主键和数据行，当数据需要通过普通索引检索数据时是先检索到主键索引的位置再根据主键索引找数据）
3.Extra有哪些主要指标
Extra是除了type以外最为重要的一个指标了（这里不是说possible_key和key等不重要，而是这些很好很容易掌握），他常见有以下几种值 以上信息解释的已经比较常见了，而且最常用的也就是这些指标
4.Explain关联子查询信息补充
这里补充一个关联子查询的优化，如果出现了关联子查询我们可以在Explain下面位置看到： Mysql5.5中使用关联子查询（where 中使用in 且in中是一个查询字句），无论是select还是update都是非常慢的（即使有索引），Mysql5.6 、Mysql5.7 中select的关联子查询被优化了（被优化为了内连查询）但是update的关联查询不会被优化性能是非常低的，即使有索引也不行（如过你用的的是Mysql5.6或者5.7不要使用update的关联子查询，而应该使用内连的方式进行更新）。这些情况再Mysql8中被改善了，Msyql8中关联子查询无论是select还是update都被优化了，不过即使Mysql8中被优化为了内连，这里不加索引也不会太慢，因为Mysql8中有一个hash join的优化（这是优化器的选择，可能选择也可能不选择看是否满足大小表的条件等）他可以根据hash的特点快速定位到链接的行，不过这种也只是适用于等值连接。总结一句话就是所有的更新都不要使用关联子查询（即使你是mysql8会被自动优化，也要有个好习惯）。select则从Mysql5.6开始只要有索引则影响不大。
连接查询补充说明： Mysql8.0.18 之前，未使用索引的连接（内连接、自然连接）查询，默认使用Block Nested Loop（Mysql5.6, Mysql5.7）算法、Nested Loop算法（Mysql5.5），在Mysql8.0.18开始支持Hash join，Mysql8.0.20 删除Block Nested Loop算法，默认采用Hash join进行优化连接查询。Hash join需要使用参数join_buffer_size 来控制连接缓存的使用，这也影响着连接查询的效率。再考虑优化连接查询时，这是一个必须考虑的事情。
-- 默认是256kb
show variables like '%join_buffer_size%'
Hash join的原理大致是，使用较小的表（通常是行数较小的表）作为驱动表，将他的连接条件构建成一个散列表，然后对较大表计算连接条件的hash值，与散列表进行比对，来进行等值查询。
二、数据库事务
关系型数据库基本都是有很好的事务的支持的，Mysql自然也不例外，Mysql中常用的引擎有两种一个是InnoDB一个是MyISAM。其中InnoDB是支持事务的，二MyISAM则不支持事务，因此我们对于有事务要求的表都是使用InnoDB引擎。下面是常见的事务的面试题解析。
5.事务的特性ACID和隔离级别
说事务那事物的特性ACID则是基础，如下：
原子性：事务的操作不可分割，共同失败或成功
隔离性：不同事物间相互不影响，称为隔离性，但是各个数据库实现时并不是直接完全隔离，而是又设置了隔离级别，方便灵活使用
一致性：数据成功或者失败后和预设结果一致
持久性：事务成功后数据会持久化到磁盘
上面就是ACID的特性了，其中需要说的是隔离级别，上面也说了事务具有隔离性，就是说不同事务间应该是相互隔离的，如果想要将事务进行完全隔离，基本做法就是加锁，不过这样会导致数据库的读写效率大大下降，所以数据库的各大厂商又都提供了隔离级别，来控制事务的隔离性，其中Mysql默认的隔离级别是重复读（RR），而Oracle、Sql Server则是读已提交（RC），全部的隔离级别如下：
读未提交：可以读取其他事务未提交的数据，这个隔离性是最差的，一旦数据库的一条数据同时被多人操作就会出现问题
读已提交：可以读取其他事务已经提交的数据，这个是Oracle的默认隔离级别
重复读：不会读取到其他事务已经提交的数据（指当前事务已经读取到的数据），读到的数据都是本事务开始时的快照信息，被称为快照读。
串行化：所有事物完全隔离，互相不干扰，这也是最严格的隔离级别，注意串行化不同事物的读写也会阻塞。
说隔离级别就必须要说隔离级别带来和解决的问题了，下面是常见的问题：
读未提交：有脏读（读到了其他事务未提交的修改数据）、不可重复读（读到了其他事务已提交的修改数据）、幻读（读到了其他事务新增的数据）等全部问题，这个隔离级别啥问题都解决不了，所以没人用
读已提交：解决脏读问题，但仍有不可重复读和幻读的问题
重复读：解决脏读、不可重复读的问题，仍有幻读的问题
串行化：解决全部问题，就是效率太低
6.说下MVCC的内部细节
Mysql使用MVCC机制提高了Mysql服务的并发性，同时事务的隔离级别实现也是基于此来实现的。先来介绍下MVCC实现的，MVCC有两部分组成undo链和Read View： (undolog: 记录事务的操作，一般用于事务的回滚使用和MVCC场景使用) 这里先说下undo的版本链，如下图当有多个事务对同一个数据进行操作时，undo log中会维护相关数据的一个版本链（这个数据存储在Buffer Pool中的数据页中和普通数据没有太大区别），这里不同版本的数据是不同事物操作的原始数据。且每一个版本中都会维护一个roll_pointer用以和其他版本的关联形成版本链，同时每个版本还有事务提交的状态等信息，同时还有事务id等，事务id根据事务的出现先后顺序进行增长。不同事物操作时数据时（修改和删除）都是基于自己的版本内部进行操作，这样就可以实现不同事物的数据隔离了。 那Read View是做什么用的呢？Read View则是统计了undo链的信息，方便查询时使用的，Read View包含了以下信息： m_ids: 版本链中所有未提交事务的事务id min_trx_id: 未提交事务的事务的最小id（事务id随着事务的增加递增） max_trx_id: 版本链中最大事务id+1，用于分配给下一次的事务 creator_trx_id: 创建Read View事务的事务id，当有查询需求时会创建Read View 以上就是MVCC的信息了，假如有一个查询请求进来，首先会创建出一个Read View，然后根据一定的规则寻找到这个查询语句应该找到哪个事务版本的信息进行返回。这块具体在下一个面试题细说。 注意：不同事物的undo log 构建成一个undo 链，但是每一个事物都会有且只有一个Read View。
7.Mysql的可重复读怎么实现的
Mysql的可重复度解决了脏读和不可重复读的问题，那是如何解决不可重复度呢，我们知道解决的表象就是其他事务已经提交的修改数据对当前事务没有影响，当前事务也读取不到别的事务已经提交的信息，更不会读取到未提交的数据了。可重复读其实是通过MVCC来实现可重复读的。当一个查询请求发起时首先会建立一个Read View(Read View的信息在上面已经介绍了，Read View中存储的其实就是undo版本链的概况信息)，在RR模式下，同一个事务会且只会建立一个Read View,同事务的读取都是依据同一个Read View来操作的，当获取数据时依据Read View 和 版本链来获取数据，那么是怎么获取的呢？ 这个步骤有四步简述就是：先判断是否有当期查询事务所属的未提交的事务有的话直接返回当前事务内的数据，如果没有则寻找当前版本链中最后提交事务的数据作为结果返回。
8.可重复读解决幻读了吗
回答这个问题之前需要先了解MVCC中的快照读和当前读，这里先简单说下这个快照读和当前读的概念
快照读：
在MVCC中事务创建undo的快照版本，事务的后续操作都是基于这个数据快照来进行的，所以MVCC场景下是没有幻读的问题的（但是重复读的隔离级别是存在幻读的），因为数据读取都是基于快照来读取的，自然也是无法解决幻读的。
当前读：
当前读是指不从快照中读取数据，而是从原表中直接读取，比如使用select for update,会增加行级锁，此时读取数据就是直接从原表读取，而不是从MVCC中，这就是当前读，当一个事物未执行完毕后有新增或删除的数据时就会发生幻读问题（前后读取的数据条数不一致），因为此时使用原表读取，相当于是读取到了已经提交的事务信息了。
MVCC可以帮助我们实现重复读解决不可重复读的问题，但是并无法解决幻读，因为幻读是发生在当前读的场景下的，当前读时数据直接从原表读取，不走MVCC。所以可重复度的隔离级别无法解决幻读问题。
三、索引和sql优化
这个部分用于总结索引和sql优化有关的面试题，这里先做索引知识的普及：
聚簇索引： Mysql中InnoDB中表的主键是聚簇索引，索引使用的数据结构是B+Tree，所谓聚簇索引就是索引和数据存放到了一起，主键和数据他们一起存放到了B+Tree的叶子节点中。如果主键是bitint（8字节）单条数据1kb，一个page页16kb，那么B+Tree三层就可以存储2000W左右的数据，索引Mysql的查询效率会很高。 主键索引：在InnoDB中，主键索引就是聚簇索引，值不可为null且唯一。二级索引（辅助索引）： 在InnoDB中除了聚簇索引剩下的都是普通索引，普通索引又有以下几种区分 唯一索引：唯一性约束主键，值可为null，不过在B+Tree中null值的索引都是靠前存储都是存放到一起的，因为是排好序的 普通索引：没有任何约束条件，只是根据值建立索引 前缀索引：前缀索引一般使用在字符串和text类型的字段上，用以简化索引的长度，但同时会降低索引检索的效率 复合索引：多个列共同组成的索引称为复合索引，复合索引的使用有助于提高多条件查询时的效率和降低表磁盘空间的占用 全文索引：全文索引主要是为了解决like左模糊等like查询的劣势 空间索引：空间索引只能针对空间类型的字段进行建立，mysql中的空间索引和全文索引实现都不是B+Tree
9.hash索引的优缺点
Mysql中创建索引时支持声明索引的类型，索引类型通常有Hash和BTree两种，一般都是选用B+Tree索引。Hash索引通过计算索引列的hash值来决定索引存放在Hash表中的位置，检索时通过Hash值的比对来快速定位数据。 优点： 因为是Hash定位，所以对于没有hash冲突时单条数据的定位会特别快，存在hash冲突时还需要遍历链表来一一比对 缺点：他的缺点就比较明显了，
范围查询慢：对于范围查询效率低下，因为hash索引并没有对数据进行值排序排序效率低：排序时无法根据所以直接排序，需要从新计算效率低下占用空间相对较高：hash码的存放会相当于原始值增加磁盘占用
10.索引失效的例子和场景
下面列举索引失效的例子和场景
or 条件：or条件两边条件都需要索引，不然索引失效不等条件：使用不等条件对于普通索引，查询的结果集较大时索引失效条件索引列参与运算：索引列进行计算比如索引+1=3等，导致索引失效条件索引列参与比较：比较两个索引列的大小会导致索引失效条件索引列使用函数：使用函数直接失效like查询左模糊：左模糊直接失效索引列字符集不一致：字符集不一致索引失效，无法比较相等，只能全表扫描联合索引不满足最左前缀：最左前缀要求一定要使用最左侧的列（可以不是放在最左，不在最左优化器会进行优化）类型隐式转换：字符串索引列传入数字进行查询，比如定义id列为varchar，传入了一个数字类型条件is not null：is null 走索引，is not null 不走索引not in： not in非主键时索引失效order by limit ：等主键都走索引，其他键有一定概率索引失效
11.什么是索引覆盖
所谓索引覆盖就是查询表信息时，无需回表查询直接在索引列就可以完成条件检索和返回信息的获取，索引覆盖会在explain的extra列提示using index。这是一种高效的表现，索引覆盖在explain中type列最低表现为index（索引列的全扫），当然索引覆盖时最好是将数据优化到range也及时索引的范围查询。
12.使用索引一定可以提高效率吗
万事过犹不及，索引虽然可以提升效率但是是对于查询来说的，随着索引的增加，会增大数据插入的负荷，导致数据插入变慢，其次索引的建立还会导致磁盘空间的增长，一般一个表的索引不建议太多，太多可能会导致索引占用空间更大。此外Buffer Pool需要加载数据过大的索引信息，会导致Buffer Pool中存储的表信息减少，对于查询就会增加其他表的IO，从而降低整体效率，这样也是会有影响，所以索引太多肯定不号。
13.聚簇索引和非聚簇索引
聚簇索引和非聚簇索引的区别是B+Tree的叶子节点是否存放一整行的数据。 在InnoDB中主键索引就是聚簇索引，聚簇索引是在叶子节点存放索引和行记录，所以聚簇索引既是索引也是行记录，InnoBD要求表必须有聚簇索引，如果没有则使用表中非null的唯一索引建立聚簇索引，如果没有唯一索引则InnoDB会自动创建一列Rowid用以作为主键。对于MyISAM则所有索引都是非聚簇索引，他们的索引和值都是分开的，所有索引都是指向表记录，而不像InnoDB中索引都是指向聚簇索引。
14.索引下推
覆盖索引是数据查询时无需回表，而索引下推的核心是为了减少回表而对查询的优化。一般对于复合索引的非等值查询（多个条件存其中存在非等值，比如a like ‘王%’ and b = 1）时，如果没有索引下推则需要先根据第一次的检索结果进行回表查询在筛选第二个条件，但如果直接在索引列就可以比较的话则可以减少回表的查询次数，从而提升性能。
15.最左前缀原则
最左前缀原则说的是复合索引的使用规范，当满足最左原则时可以更高效的利用复合索引，假设有一个复合索引包含散列A、B、C 若条件是B、C的任意组合不走索引。 若条件是A、B走复合索引 若条件是A、C走索引，C的检索可能会触发Mysql的索引下推以优化C列的回表查询 若条件是A、B、C（任意先后）走复合索引，Mysql会将复合索引的列进行优化为正常排序（A、B、C） 上面就是复合索引的生效场景了，那复合索引为什么有个最左前缀原则呢？ 这个根本原因还是因为索引的数据结构影响的，Mysql在构建复合索引时是根据声明列的先后顺序建立的复合索引，先声明的在最左面，所以只有使用了最左面的列，后面的列的检索才能继续索引。 由此我们也可以得出一个结论：构建复合索引时，区分度较高使用频率较高的列应该尽量靠左
16.什么是自适应hash索引
自适应hash索引（AHI）与Buffer Pool（缓冲池）、Doublewrite Buffer (双写缓存)并成为InnoDB的三大特性。AHI的自适应就是不需要人为干预，所以自适应hash索引其实就是InnoDB根据场景自己选择的一种索引优化方式。当InnoDB发现查询符合自适应hash的建立条件时就会为其自动建立自适应hash索引。那在什么场景下如何建立自适应hash索引呢？ 什么场景下建立自适应hash索引：
等值查询：hash索引对于等值查询效率较高，所以只会对等值查询来进行优化查询频率高：这个频率并没有说是多大的频率，反正是InnoDB自己控制的，我们无法调整，更高的查询频率InnoDB会自动优化优化B+Tree: 自适应hash索引是对B+Tree的一个优化，也就是对B+Tree的查询路的一个优化，可以通过自适应hash索引快速定位而不需要走索引树
如何建立自适应hash索引：
建立在Buffer Pool：自适应hash索引会建立在Buffer Pool中，所以会占用BP的空间。
与自适应Hash相关的配置：
# 默认开启自适应hash索引，关闭off
innodb_adaptive_hash_index=on
# 自适应hash索引使用分片进行实现，可设置分片数
innodb_adaptive_hash_index_parts=8
17.自增和UUID如何选择
先说结果，必须选择自增的，这也是推荐的id的生成方式（单库）。Mysql中索引的机构是B+Tree，而B+Tree的结构是所有叶子节点存储数据，且数据都是排好序的。
自增插入快（排序效率高），插入效率高，UUID计算位置较慢，排序较慢int（4字节）和bigint（8字节，与java中long对应）占用空间较UUID小
综合来看自增主键更为优秀，只是在插入时略慢和等值查询略慢，但大部分场景都会优秀很多。
18.B树与B+树的区别
B树： 一种平衡的多路查找树，常见的还有平衡二叉查找树-红黑树，BTree允许一个节点存放多个数据，这样可以尽可能的减少数的深度，存放更多的数据，而且平衡树的特点是叶子节点会尽可能多以减少树的深度。BTree中所有子结点中子树最大的值称为BTree得的阶，1颗m阶（树中节点最多可以包含的子节点数为阶）的BTree需要满足以下条件（不为空的话）： 每个节点最多含有m个子树（这是必然的基本是废话） 根节点至少有两个子树 所有叶子节点都在同一层，且按升序排序 B+Tree： B+Tree是在BTree基础上的优化，所以BTree拥有的优点他都有，而B+Tree的优点，BTree却没有，B+Tree更适合存储索引，InnoDB就是使用的B+Tree，而且大部分关系型数据库都支持B+Tree类型的索引，那B+Tree优化了哪些呢？
非叶子节点不存储数据，所有数据均存储在叶子节点上所有叶子节点通过指针相连，形成链表，方便排序和范围查找 总结B+Tree的优点：
降低BTree的高度，BTree因为非叶子节点存储数据会导致同层级的树没有B+Tree存储的数据多，所以可以降低树的高度B+Tree查询效率更高，无需扫描整个树，只需要遍历非叶子节点即可，而且非叶子节点还是一个链表结构B+Tree排序能力好，范围查询能力好，也是因为所有叶子节点都有指针相连B+Tree查询效率稳定，大部分场景优于BTree（极端情况BTree可能只需要一次两次就可以查询到数据比如根节点）
19.一个B+树索引大概能存多少记录
在B+Tree中，一个节点是一个page，一个page16kb也就是16384b 如果索引是int类型则是4b，指针占6b，所以一个节点最多可以存储16384/(4+6)个索引指针，也就是1638个指针，所以有1638个叶子节点，也就是1638个page，那每个节点可以存储多少记录呢？ 假设1行记录占用1kb，那么一个page可以存储16行数据 则： 二阶的B+Tree可以存储163816≈2.6w数据 三级的B+Tree可以存储163816*1638≈4300W数据 如果主键不是int而是bigint（对应java的long，也是最常用的主键类型）的话，这个值除以2也就是2000w左右，这也是建议分表的一个表的数据量超过这个数据量，表的索引树的层级增加，查询效率会下降很多，所以一般建议2000W左右可以进行分表了，官方建议是1000w和100G进行水平分表或垂直分表。
20.如何做分页查询优化
1.利用主键来查 只查主键，不用回表，这样应该很快，然后再拿查到的id进行范围查询这样会很快 select * from sunacwy_mdm.tb_mdm_room order by id limit 500000,10 select * from sunacwy_mdm.tb_mdm_room where id >= (select id from sunacwy_mdm.tb_mdm_room order by id limit 500000,1) limit 10 使用该方法以后，第一种大概是1s足有，第二中只需要0.4s左右，优化还是很明显的 2.先查询id范围再和原表join select room.* from sunacwy_mdm.tb_mdm_room room natural join ( select id from sunacwy_mdm.tb_mdm_room order by id limit 500000,10 )b 3.使用between and 代替 limit 使用limit越往后，扫描的行数就会越多，所以若是知道了起始行和结束行对应的id，可以使用between …and 来代替分页（不过一般是不知道的，所以使用方法1和方法二更多一些）
21.慢查询优化如何做
慢查询首先需要区分已结束的慢查询和正在进行的慢查询，如果分析已经结束的慢查询是需要通过慢查询日志来进行记录和分析的，而线上的正在运行的慢查询则需要通过实时查看mysql的线程和事务信息来确定。
慢查询日志分析已完成慢查询 这是慢查询相关的配置信息： [mysqld]
# 打开慢查询，影响mysql整体性能
slow_query_log = ON
# 设置慢查询日志文件
slow_query_log_file = /var/log/mysql/mysql-slow.log
# 设置慢查询的阈值，这里是2秒
long_query_time = 2
# 记录没有使用索引的查询
log_queries_not_using_indexes = ON
当打开慢查询以后，一旦sql查询时间超过了设置的long_query_time的值就会被记入到慢查询日志，同时会记录查询的原始sql，返回的记录数，查询的总的记录数，查询的时间、查询的执行总时间、锁定的时间等关键信息。通过这些信息我们是可以初步获取到慢sql的基础信息。 实时分析运行中的sql信息 当sql还在执行时是无法根据慢查询日志文件看到慢sql的，所以我们还需要分析实时的sql，实时的sql可以通过 # 查看正在运行的线程信息(只显示100条)
show processlist;
# 查看全部运行的线程信息
show full processlist
这是使用命令时展示的后半段信息（前面还有id、host、db等一些信息） 这里的command列用以标识sql的种类，处于sleep的说明是连接池的静态线程，等待客户端使用此时是空闲的状态，处于Query的是查询阶段（更新、插入等也会有该阶段），Binlog_dump则是主从的线程。然后在最后一列info中是可以看到具体的sql的。Time列是表示当前状态经过的时间，最主要的还是State列，该列表示sql的执行的各个阶段：常见的有：
Sending data：正在处理 SELECT 查询的记录，同时正在把结果发送给客户端。Sleeping：正在等待客户端发送新请求.Sorting for group：分组阶段Sorting for order：排序阶段Locked：被其他线程锁住了资源 通过这些信息我们可以知道当前线程的信息，和所处的阶段。
那知道了查询的sql，如何进行优化呢？这里总结了常用的优化的三个方向：
IO优化： 使用索引，使用索引时优先使用过滤最充分的索引（主键>唯一>普通），减少回表，只检索需要的列，关于索引的优化则需要使用explain进行进一步分析索引的情况CPU优化： 小表驱动大表超过三表的连接考虑拆分，分组、排序都是用索引网络优化： 如果客户端与服务端跨机房会有网络带宽的影响，需要考虑网络的影响，可以先观察show processlist的state如果大量处于sending data很可能是网络问题，再在服务器上看下top如果cpu和内存也是正常，可以在用ping验证下，就可以判断出来是不是网络问题，网络问题只能是提升带宽来解决了。
22.如何做join的优化
这个需要区分Mysql的版本来聊，在Mysql8.0.18之前，连接条件没有索引的话，数据检索都会很慢，通过explain可以在Extra中看到关键信息 Mysql5.5 之前会有笛卡尔积形成是一种可怕的结果
Mysql5.7使用BNL算法优化没有使用索引的join连接（Explain的Extra中可以看到）
Mysql8.0以后如果连接条件没有索引，Mysql会使用Hash连接进行优化，hash连接是根据连接字段自动建立hash表来优化连接的手段，hash的特点对于等值连接可以明显提升效率
上面是通过索引方面来聊的，其次还需要我们注意
小表驱动大表：小表驱动大表可以有效减少内表的扫描次数连接条件必须索引：上面说了不使用索引的场景，最好是使用索引的不使用索引增加join buffer的大小：在没有使用索引时我们可以看到上面三个场景都是使用的join buffer（使用索引时不使用），join buffer会先将驱动表的连接列加载到join buffer中，如果join buffer 够大则可以明显提升连接效率，其次每个连接都会有一个join buffer，默认是256kb，可以通过参数join_buffer_size来进行调整减少不必要字段的检索，减少IO
23.什么是行溢出，InnoDB如何解决行溢出
这里先说下什么是行格式，在InnoDB引擎中行格式通常我们在定义表结构时已经声明，比如下面的sql中ROW_FORMAT=DYNAMIC的就是用来指定行格式的样式的，这里是DYNAMIC，也是Mysql5.7以后得默认值了，mysql8也是DYNAMIC。
CREATE TABLE example_table (
id INT NOT NULL AUTO_INCREMENT,
cus_name VARCHAR(100) NOT NULL,
address TEXT,
PRIMARY KEY (id),
INDEX idx_cus_name (cus_name)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
InnoDB还支持的行格式还有：Redundant（冗余）、Compact（紧凑）、Compressed（压缩）等 Compact是紧凑的行模式，这种模式就是为了可以一个页可以存储更多的数据，一个数据页最大为16kb，一行数据就是一个数据页，而无论哪种行模式都不是只存储数据还有一些额外的其他信息需要存储比如null值指针（行中的null值都是通过指针维护，并不会占用一个真正空间），可变长度的列的统计、其他头信息等。当存储的数据超过了最大16kb时，一个页就无法存储数据了，此时需要将数据存储到溢出页中，然后在原数据页中维护一个偏移量指针来维护溢出页的地址，用此来解决行溢出的问题。 此外不仅在Compact在Dynamic中也是存在行溢出的问题的。Compressed通过压缩数据来存储，所以很少有溢出，Redundant只存储前缀信息在行中，所以一般也不会有。
24.什么是写失效，Doublewrite Buffer如何解决写失效
在Mysql数据持久化是以页为单位进行持久化的，而Mysql的页大小是16kb，OS的则是4kb，所以Mysql的一个页持久化完成需要和OS交互四次（如下图），如果在写OS过程中Mysql异常宕机就会导致数据写入磁盘失效，这时称为写失效。这种场景如果不加以处理肯定会导致数据丢失。
那InnoDB是如何处理写失效的问题的呢？事实上Mysql通过Doublewrite Buffer（InnoDB三大特性Buffer pool、Doublewrete Buffer、自适应hashindex）来解决的写失效问题，Doublewrite Buffer就是为了解决写失效提升数据写入的可靠性来存在的，他是内存+磁盘的结构，包括内存结构和磁盘结构两个部分。
内存中，Doublewrite Buffer由128个页（Page）构成，大小是2MB。这些页在内存中以Doublewrite Buffer的形式存在。 磁盘中，Doublewrite Buffer在系统表空间上是128个页（2个区，extend1和extend2），大小也是2MB。这些页在磁盘上以Doublewrite File的形式存在。
InnoDB中数据更新时会先将数据更新到Buffer Pool中，Buffer Pool中存储数据也是通过页来存储的，未将数据变更刷新到磁盘的页称为脏页，脏页刷新到磁盘可以配置事务结束是否刷新到磁盘也可以是InnoDB的后台进程控制刷新到磁盘，在刷新到磁盘时会先将数据刷新到内存中的Doublewrite Buffer，而数据进入到内存中的Doublewrite Buffer以后并不会立即刷新到磁盘中的数据文件中，而是先刷新到磁盘中的共享表空间，也就是Doublewrite File，这个过程是顺序写很快，写完以后才会离散写入数据文件idb中。这就是数据写入到磁盘的过程： 而Doublewrite Buffer真正起作用的是在数据刷新到磁盘过程中Mysql宕机的场景下： 因为InnoDB的数据页是16kb系统页是4kb，一旦刷新过程中宕机了，就需要依赖Doublewrite Buffer来恢复完整的数据了，如果宕机时并没有这种终端整页的刷新操作，那么只是使用redlog也是可以完整恢复数据的（DWB中只存储了2M数据），而在Doublewrite Buffer和redolog共同保证了数据的持久化和完整性。
25.select(1) 、select(列)、select(*) 有什么区别
count(1): 统计时不忽略null值的行 count(): 包含所有列，不排序null值的行 count(列名): 会忽略列值为null的行，所以他的结果与count(1)和count(*)可能更小，而count(1)和count()的结果肯定是相同的。 那他们三个效率上是怎么样的呢？这个要区分InnoDB和MyISAM来聊
InnoDB：count()=count(1)>count(列名) InnoDB中处理count()和count(1)都是通过扫描二级索引来统计行数，如果二级索引不存在则取扫描聚簇索引，不排除null，所以在InnoDB中他们其实还算高效，而count(列）需要看是不是索引列，是索引列同样走索引不是的话则会全表扫描然后排去null的值。MyISAM：count()>=count(1)>count(列名) MyISAM中存储了数据的准确行数，用以支持快速返回数据的行数，此值就是通过count(*)来返回的，所以在MyISAM中使用coun(*)效率最高，而count(1) 只有在第一列时not null 时他的处理才和count()一样，对于count(列)则和InnoDB中差不多。
总结三者选择优先选择count(*）来进行行数据的统计，如果有排除null的要求，则需要使用count(列名)
26.说说mysql执行一条查询语句的过程
如下图，就是mysql在执行一条查询语句的处理过程了。首先是客户端与服务端建立半双工的连接（TCP是全双工的连接），然后mysql会先看查询是否在缓存中（mysql8以后开始禁用查询缓存，因为没啥用，并没有提供多大帮助）。然后就会交给解析器进行sql解析，然后交给预处理器进行对sql进行预处理，最后经过查询优化器来对sql进行一系列优化，这个优化主要是将写的不太好的sql进行强制调优，当然这个调优不是万能的，很多sql还需要人为进行调优。查询优化器会生成一个执行计划来交给查询执行引擎，查询执行引擎则是调取我们使用的真正的存储引擎来执行sql，获取结果后再将结果返回给客户端。
27.mysql内部支持缓存查询吗
支持，可以通过以下命令查看到query_cache相关的配置信息：
-- 查看mysql版本
select version();
-- 查询缓存相关参数
show variables like '%query_cache%'
在mysql5.7.31中执行，弹出如下： 注意查询缓存在mysql8中已经关闭了，缓存肯定是为了提升效率的，那为什么会关闭呢，就是因为官方也认为这个没有太大的用处，因为缓存使用的频率并没有多高，所以建议我们还是在应用端自己做应用缓存不要依赖数据库的缓存。下面列举了查询缓存失效的场景：
数据更新相关缓存立马失效，使用时关于单条的缓存意义不大，范围查询又很容易失效sql必须完全一致才会走缓存sql完全一致查询得的结果集还必须小于query_cache_limit才可以走缓存有自定义的触发器、函数、存储等都无法走缓存表结构变更，该表的全部缓存失效
28.为什么like以%开头会导致索引失效
这个原理其实和最左原则时类似的，B+Tree的查询效率高时因为所有的叶子节点的数据都是排好序的，他的排序规则就是依据的字段的信息，从左往后，这样当我们查询数据时就可以通过最左侧到右侧的字符快速定位数据了，但是如果最左侧是模糊的值，则左侧不可知就无法通过索引找到真正的想要数据，此时就只能走全表扫描了。
29.说说Mysql关键字的执行顺序
mysql中的关键字也是有执行顺序的，就像一条命令总会分先后进行解析命令的含义。Mysql中的关键字执行顺序是：
from
on/using
join
where
group by
having
select
distinct
union
order by
limit
Mysql中先执行from来加载驱动表，驱动表加载完以后会根据执行on条件，因为需要先将连接字段进行放入join buffer（不使用索引时）或者加载连接条件的索引，然后才是根据条件去筛选被驱动的表数据此时执行的是join，此时驱动表和被驱动表的数据已经建立了关系，然后执行where开始对数据进行初步的筛选，数据筛选完成，则根据分组条件进行分组，分完组则对分组后的数据使用having进行进一步的筛选。当数据最终被筛选完成就可以执行select挑选我们需要的字段了此时如果需要去重则会执行distinct，再然后如果需要多个结果集的关联则会执行union（使用union相连的字句页不允许使用limit 和 order by），最终结果集形成以后则根据筛选确定的列进行排序order by，排完序就根据limit进行分页了。 不过需要注意的是我们根据关键字的执行顺序得到的启示：
where 后面不可以进行聚合函数，因为where在分组之前执行，所以如果需要聚合函数可以写在select、having、order by条件筛选先后经过了on、where、having三步，筛选条件越靠前效率越高（实际执行时内连的on条件会被优化到where中），所以我们应该将条件尽量写在on、where中而不是having中
四、存储引擎
这部分用以总结存储引擎相关的面试题，Mysql完成各项操作都需要以存储引擎为基础，存储引擎是Mysql的核心组件。常见的存储引擎有InnoDB（事务）、MyISAM（报表）、Memory（纯内存）、ARCHIVE（数据归档），当然也还有别的存储引擎，不过使用频率就不高了，了解以上即可，真真使用时一般会用InnoDB、MyISAM为首选。
30.InnoDB和MySAIM的区别是什么
InnoDB和MyISAM是最常用的两种存储引擎，下面通过各项的比较来区分他们的区别
事务和外键 InnoDB支持事务和外键， 适合update和insert，有事务要求的只能选择InnoDB MyISAM不支持事务和外键，但拥有更高的存储和检索速度，所以更适合做普通的报表查询，数据展示的表锁机制 InnoDB支持行级锁，锁定指定记录（基于索引实现，若无索引则会锁表），基于索引来加锁实现 MyISAM支持表级锁，锁定整张表索引 InnoDB使用聚簇索引，索引和记录存放一起（这里说的是主键索引） MyISAM使用非聚簇索引，主键索引和普通索引存放的都是表记录的位置并发处理能力 MyISAM写入时是表级锁，所以写入效率不高，所以适合写特别少的场景，而读取效率高，所以适合报表 InnoDB读写阻塞与隔离级别有关，同时InnoDB也引入了MVCC来支持高并发数据存储 InnoDB数据和索引存储在一起，只有两个文件：frm表结构文件，ibd数据索引文件，单表最大支持64TB MyISAM数据和索引分开存储，有三个文件：frm表结构文件，MYD数据文件，MYI索引文件，单表最大256TB
以上就是InnoDB和MyISAM的区别了，总结是InnoDB更适合对事务有要求的场景，而MyISAM支持快速的数据读取，但没有事务更适合报表类的表的索引，不过实时的就不太好了，适合做T+1的报表使用，此时没有写入场景都是查询。
31.什么是Buffer Pool
Buffer Pool是InnoDB的三大特性之一（还有Doublewrite Buffer，自适应hash索引上面也都说了），简称BP，其作用是用来缓存表数据和索引数据，从而减少磁盘IO操作，提升效率。 BP由数据页Page和控制块、Change Buffer组成，数据页顾名思义就是存储的数据信息，控制块则相当于是数据页的索引，他存储着数据页的地址和编号，数据页中的表信息，数据页中存储的记录的最大和最小记录等元数据信息。BP默认大小是128M，他的基础单位是Page，单个Page默认是16kb。此外的控制块约占总Buffer Pool的5%。
当发生数据查询时： 当发生查询时，会根据查询的索引（如果使用了索引）在Buffer Pool中进行索引页Page的检索，如果所需的页在Buffer Pool中则称为“页命中”此时就无需访问磁盘就可以返回数据给客户端了。如果未全部找到所需的数据，则会根据条件从磁盘中加载所需的数据到Buffer Pool中（如下图）加载数据时也是以Page为单位进行加载，一个Page可能会含有多条数据，加载后再对客户端进行数据返回。那这里会有一个情况Buffer Pool如果满了呢？这个就需要考虑Buffer Pool的大小了，一般Buffer Pool大小设置为服务器内存的60%到80%之间（32G一般Buffer Pool设置为24G），当然了即使我们设置的再大，Buffer Pool这样一直加载数据那肯定会有数据存储溢出的时候，所以还需要了解Buffer Pool的数据数据页的替换策略LRU（lru最近最少使用，后面面试题细说）。 查询关于Buffer Pool相关的参数信息： show variables like '%buffer_pool%'
如下图是笔者生产库的配置信息： 当发生数据变更时： 当数据发生更新时，也是先根据索引在Buffer Pool中检索数据，如果数据检索到了，会将对应的数据页中的对应数据进行更新，数据更新后的Page则称为脏页，他会在一定时间后进行刷新到磁盘中（如果未刷新到磁盘时就宕机了，会使用redo log+Doublewrite Buffer进行恢复），刷新时是利用Doublewrite Buffer进行刷新的（这个上面说了这里不细说了）。
无论是查询和修改都会涉及一个问题如何判断数据是否在Buffer Pool中，InnoDB如何判断呢？在InnoDB中有一个Hash表维护了表空间和数据页的信息和对应的控制块（key和value的形式），当需要查询一个数据时先是根据主键索引找到需要查询的数据页，然后在根据这个hash表就可以定位到该页是否在Buffer Pool中了，在的话就直接从BufferPool中加载，不在的话则需要Buffer Pool找分配空闲页去加载所需的数据了。
32.InnoDB如何管理page页
InnoDB中数据（普通数据、索引数据等）存储的地方在Buffer Pool中，Buffer Pool中则是以页为单位进行对数据管理，单个page的默认大小是16kb，page页分为三个类型：
free page：空闲page，未被使用的状态clean page：被使用的page，但是page中的数据未被修改过dirty page：被使用的page，且数据已经被修改，此时page的数据和磁盘不一致
Buffer Pool中通过链表来管理page页，三种类型的page被三种不同的双向链表来管理，可以使用以下命令来查看page页的大小，这个值一般不会动。
show variables like '%innodb_page_size%'
free list: 用来管理free page ,需要注意的是free list存储的并不是page页的信息，而是page页对应的控制块的信息（上面的面试题说的Buffer Pool主要是page和对应的控制块组成，在Buffer Pool中控制块就在对应的链表中），而page页的统计信息在对应的控制块中。free list中有一个基节点，这里面并没有存储任何page和控制块的信息，而是存储的是free链表的头结点地址、尾结点地址，还有free list中有多少个节点等信息。当一个控制块对应的page页被使用，那么这个控制块就会被移除，而后在clean对应链表中进行添加，而当一个控制块对应的page空闲出来，就会在free list中新增一个控制块 flush list: 用以管理dirty page（脏页）, 这个链表中的数据都是待刷新到磁盘中的数据。 在链表中他们按照修改时间先后的顺序进行排序。在InnoDB引擎中数据修改完以后并不会立刻刷新到磁盘中，而是会在未来某个时间节点（这里是支持配置的，可以间隔一定时间，也可以在更新时刷入磁盘）才会将脏页刷新到磁盘（Doublewrite Buffer），而page一旦被加载了修改后的数据，那么他就会从free list中删除对应的控制块，而在flush list中添加一个对应的控制块。 LRU list（核心）: LRU list（最近最少使用链表） 主要是为了管理热数据的，InnoDB 的核心Buffer Pool就是为了将热数据留在内存，以减少数据查询和修改时和磁盘的IO次数，而无论是clean page还是dirty page都是含有数据的，都有可能被DML和select命中，所以需要进行统一管理带有数据的clean page和dirty page，LRU list管理的数据页页正式他们俩。到这里会发现dirty page 同时被 LRU list 和 flush list管理，这里需要说下，这两个链表管理dirty page的目的是不同的，flush list只管理dirty page，根据修改先后管理着dirty page，后台会有一个线程Page Cleaner定时去将flush list对应的dirty page进行刷新到磁盘（刷新后从flush list剔除，但并不会从LRU list剔除），从而实现磁盘和内存数据的一致性，这也是dirty page同步数据到磁盘的主要手段。而LRU list管理clean page 和dirty page主要是为了管理热数据，因为他们都含有数据，就有可能会被DML和查询命中，所以InnoDB需要集中管理以提高使用频率较高的数据在内存中呆的时间更久。那LRU list是如何管理数据页（这里将含有数据的page称为数据页）的呢。 LRU list 分为两段：yong 区域 和 old 区域，yong list 用以存储访问频次较高的数据，而old 区域则用以存储访问频次较低的数据，访问频次较高的数据因为命中率较高，所以应该存储在内存中，而访问频次比较低的数据应该需要淘汰掉，在LRU链表中数据过多时，会将old区域末尾的数据从内存中剔除给热数据让位，所以old中末尾的数据会在空间紧张时进行刷新到磁盘（如果是dirty page先刷新再剔除，是clean page 则直接剔除）然后剔除。 默认情况下yong区域占5/8(63%)，而old区域占3/8(37%)。中间节点称为midpoint，数据插入时从midpoint进行插入（插入到Old区域的头节点），然后根据使用频率向两端移动，使用频率高则向着yong的头部移动，使用频率低的则从midpoint向着old区域的尾部移动（old区域尾部的数据优先淘汰）。 old和yong的占比可以通过以下命令调整： show variables like '%innodb_old_blocks_pct%'
old区域的数据并不是说一定不可以再进入到yong区域，只要访问频率上去一样还可以进入到yong中的，不过这里有一个时间控制，只有再默认间隔1000ms以上的时间内多次访问才会被认为是有效的访问，才会计入提高old区域数据的热度的计算中。这个默认1000ms可以通过以下参数控制： show variables like '%innodb_old_blocks_time%'
33.LRU算法，Mysql为什么要改进LRU
未优化之前的LRU算法是通过一个链表来维护数据页，淘汰方法简单粗暴：数据从头部插入尾部淘汰，假如有一个查询特别大的数据量很容易将热数据淘汰掉，所以这个LRU算法有着明显的短板，结构如下： 传统LRU算法的缺点：
1.全表扫描的查询会导致真正的热数据被提出LRU list，导致热数据的查询需要从新从磁盘加载2.Mysql有预读机制，预读机制会在数据未被访问时就将数据加载到Buffer Pool中，传统的LRU算法会将预读的数据放到头部，如果预读的数据未被使用则会导致真正的有效数据被从LRU list释放。Mysql预读机制分为线性预读、随机预读、索引预读等，根据查询的数据的特点InnoDB来选择预读的模式，比如查询是线性查询InnoDB会使用线性预读读取接下来需要访问的数据，随机查询则可能触发随机预读，索引预读则在索引的范围扫描和索引的全局扫描时可能会触发，说白了就是猜测你接下来可能需要查询的数据，提前将它加载到内存中。
改进型LRU算法： 如下图，改进型LRU算法不再是从头部插入，而是从新生代和老年代的分界线进行插入，实际插入的位置是老年代的头部，只有数据重复被使用时才会移动到新生代（这里还有一个参数innodb_old_blocks_time控制，老年代中数据的存放时间，这个默认1s，也就是1s内不会移动只有1s后触发使用才会移动），且根据数据的使用频率来决定移动的距离，这样就避免了上面说的问题，即使发生了全表扫描那么数据也是先从老年代进行回收，而不是首先影响新生代的数据。
33.InnoDB IO线程相关参数优化了解过吗
数据库都是一个IO密集型的系统，所以对于IO的流程优化尤为重要，核心宗旨就是减少磁盘IO尽量让数据查询走内存。常见的有数据查询缓存（mysql8已经默认禁用了），脏页的刷盘（脏页一达到Buffer Pool一定的比例会自动触发刷盘，此时有磁盘IO），在Buffer Pool中的LRU 链表中也会有数据刷新到磁盘的动作，老年代的冷数据会定时进行清理，这些数据访问频率并不高，会定时刷到磁盘以流出更多的空间给热数据。下面就来分三块说说这些的IO的参数优化：
查询缓存 查询缓存在Mysql8之后开始禁用，原因是效率提升不高，不过若是能命中缓存则效率还是很高的，这里有两个参数需要注意是否开启查询缓存： -- 是否开启查询缓存
show variables like '%have_query_cache%'
还有一个是查询缓存的类型，0不使用缓存，1缓存所有的结果，2只缓存在select 语句中通过SQL_CACHE指定需要缓存的查询。 show variables like '%query_cache_type%'
如果想要指定使用缓存，可以像下面这么写，不过必须得设置上面参数为2，不过mysql8我尝试已经不让通过命令修改了。 SELECT SQL_CACHE * FROM your_table WHERE condition;
脏页的刷盘 Buffer Pool中有三个配置项如下，控制脏页刷新的信息， -- 脏页刷新阈值，当到达该值时InnoDB强制刷新脏页信息到磁盘，默认值是75%，最大值是99.99%，一般设置不建议超过90%
show variables like '%innodb_max_dirty_pages_pct%'
-- 脏页刷新的最小比例阈值，当达到时InnoDB开始着步刷新脏页，以减少脏页数量
show variables like '%innodb_max_dirty_pages_pct_lwm%'
-- 自适应脏页刷新，InnoDB会根据系统的写入活动动态调整刷新脏页的速度
show variables like '%innodb_adaptive_flushing%'
innodb_max_dirty_pages_pct太小会导致脏页的频繁刷新，一般不做变更也是可以的，如果数据更新比较频繁，可以适当进行调高到90% 也是可以的 这里表示脏页到达10%以后开始着步开始刷新，使用自适应的刷新方式 LRU 链表的刷盘 LRU 链表是用来管理clean page 和dirty page的一个链表，他使用改进型的LRU算法来管理数据页，当数据的热度降低以后，数据会被移动到LRU的老年代的尾部，老年代的尾部的数据会定期和内存紧张时进行删除或者刷盘后删除，这个删除发生在老年代也就是Old区域，所以我们关注的重点应该是老年代。InnoDB提供了以下的策略： -- 查询老年代占用的比例，默认3/8 也就是37%
show variables like '%innodb_old_blocks_pct%'
这个参数是控制老年代默认的比例的，若是这个值较小则会频繁触发刷盘和删除动作导致真正的数据无法从老年代头部进入到年轻代，所以若是数据大数据比较少（大数据比较多的话调整这个效果不明显），可以适当调高这个值，增加老年代数据的大小，这样就可以让老年代的数据更有机会进入到新生代了。此外关于老年代的参数还有一个值： -- 默认1000ms，放入来年代头部的数据在这个时间内不会因为重复使用而进入新生代
show variables like '%innodb_old_blocks_time%'
很明显这个值越小，数据进入新生代的概率就越大，所以如果并发量比较大，想要数据更多快的进入新生代可以适当调小该值。
34.InnoDB日志相关的参数优化了解过吗(redo log)
这里一般说的是redo log的优化，redo log是InnoDB的事务日志，他分为两部分内存部分redo log buffer（注意他不属于Buffer Pool）、磁盘部分redo log file。当一个数据被修改时先被写入到Buffer Pool中，写入完成会将数据同步写入到redo log中，redolog不像undolog那样存储原始的行记录，也不像binlog那样存储sql语句，他存储的是一些物理信息，比如被修改的数据页的信息，数据的操作类型，事务id等信息。 言归正传，这里说下Redo log 的优化。
日志缓冲区相关参数配置 redo log buffer 大小配置，参数如下默认大小是16MB，一般不会调整这个值，一般用默认就行，不过若是使用的字段有大字段text/blog等，可能导致redo log 直接撑满，此时可以考虑调整redo log的大小，以节省服务器性能，如不调整msyql还需要对事务进行分段延时提交等处理，会额外消耗性能。总之更大的redo log buffer 会让事务的性能更好，不过也会导致异常时数据恢复时间的增长。show variables like '%innodb_log_buffer_size%'
redo log file相关配置 redo log 分为两部分，一部分在内存一部分在磁盘，磁盘中的redo log file 有两个关键配置信息：show variables like '%innodb_log_files_in_group%'
该参数用以描述redo log file的个数，默认是2，一般也不会进行调整。show variables like '%innodb_log_file_size%'
这是redo log file的大小，默认情况下innodb_log_file_size*innodb_log_files_in_group不能大于512G,所以单个redo log file 的大小应该不大于256G，这个redo log 大小一般是会根据需要进行调整的，但是也不可以设置太大，太大了影响mysql恢复的时间，一旦mysql出现问题，长时间恢复不了就会出现很大的线上问题，通常情况下1G的redo log恢复起来大概是5分钟，笔者生产使用1.5G的大小。真正的值应该设置多大较为合理需要结合实际业务与机器性能进行恢复测试进行评估。
innodb_log_file_size 设置过小： 如果该值设置的过小会导致mysql频繁的切换redo log，因为小很容易就写满所以需要很频繁的切换，频繁的触发数据库的检查点checkpoint（checkpoint负责定时将redo log的数据刷新到对应的表记录，以减少redo log大小，进而减少数据库异常情况下恢复数据库的时间），进而导致IO的次数过多，影响数据库的性能，所以设置过小肯定不合适。innodb_log_file_size 设置过大： 如果该值设置的过大，数据库正常情况下性能会随着值的增加而有所提高（也需要考虑机器本身的性能）根本原因是减少磁盘IO，不过一旦异常时数据库需要恢复的数据就会增大，所以过大了也不好，一般情况下恢复1G的redo log需要5分钟。如何设置： 计算一定时间内的redo log的大小，然后根据测试的结果进行设置，官方推荐redo log最少应满足1h的业务需求，怎么统计1h的（或者更多）的redo log的大小呢，可以通过观察Log SequenceNumber (日志顺序号) 的变化来统计一定时间内的日志的大小。或者就是模拟宕机然后观测恢复时间集合redolog的大小来进行确定合理大小。
35.InnoDB内存相关参数优化
回答这个问题首先应该知道InnoDB内存包含了那几部分，Buffer Pool内存包含了数据页、Adaptive Hash Index 、Change Buffer、Log Buffer等。Change Buffer用于存储二级索引的更新缓存当普通索引发生更新时会通过他减少磁盘IO，Adaptive Hash Index 这是自适应hash索引，用以优化等值查询时的B+Tree索引，Log Buffer则是内存中的redo log日志。言归正传，如何优化InnoDB的内存配置呢？ 首先应该关注的参数就是Buffer Pool的大小配置，如下：他的默认大小是128M，但是这个值肯定不行的，一般数据库服务器都是单独服务器，应该将BufferPool的内存设置为总内存的60%-80%（这个范围被认为是一个更好的范围）。该值较大时有利于提升数据库的并发访问能力，因为数据可以更多的在内存命中，而不用做磁盘加载。
-- 查询Buffer Pool大小默认是128M
show variables like '%innodb_buffer_pool_size%'
注意该参数是支持线上调整，而不用重启mysql的，可以使用如下的sql语句进行全局调整大小，然后在配置文件my.cnf中同步调整，这样下次重启服务就会保持和重启之前一致了。
-- 将Buffer Pool的大小设置为2G
set global innodb_buffer_pool_size = 2*1024*1024*1024;
这个设置会实时生效，如下： 如果没有实时查看到调整的结果，可以通过下面的参数查看调整的进度：
show status where variable_name like '%innodb_buffer_pool_resize_status%'
我这里是已经调整完成了： 那调整完Buffer Pool的大小，调整的合适不合适我们也是需要考虑的一个很重要的问题，不能调整了就完事了，不关注调整后的效果肯定不行，那如何关注呢？我们可以通过如下的的工时来计算Buffer Pool对数据DML的命中率：
-- Innodb_buffer_pool_read_requests：命中缓存的请求数，也就是走了Buffer Pool不用走磁盘的
-- Innodb_buffer_pool_reads：需要从磁盘加载数据的请求数
Innodb_buffer_pool_read_requests/(Innodb_buffer_pool_read_requests+Innodb_buffer_pool_reads)*100
通过上面的例子我们可以计算出一个100以内的值，一个合理的命中率应在90以上，如果你的值低于这个值应该增加Buffer Pool的大小，来提高缓存的命中率（我随机去查了两个生产库都是97以上）。当高并发情况下会增加LRU热数据的更新速度，此时可能会降低这个值，所以更需要我们调高Buffer Pool的大小来增加数据的命中率。
此外我们还可以配合下面的参数来一起看下Buffer Pool中数据页的一个情况：
show status where variable_name like '%innodb_buffer_pool_pages%'
解释下对应的意思：
Innodb_buffer_pool_pages_data：包含数据页数，也就是clean page+dirty pageInnodb_buffer_pool_pages_dirty：脏页的数量，脏页会定期+不定期刷到磁盘，所以很容易是0Innodb_buffer_pool_pages_flushed：这个是InnoDB缓冲池中刷新脏页的请求数Innodb_buffer_pool_pages_free：空闲页，也就是free pageInnodb_buffer_pool_pages_misc: 这是一个除去clean page、dirty page 、free page其他占用的页数，一般很小Innodb_buffer_pool_pages_total：这是总的页数
通过这个信息我们可以做一些印证，如果数据命中缓冲池的命中率比较小，我们可以再观测下free page是不是太小了，如果太小了说明应该增加Buffer Pool大小了，如果不小则可能是LRU中管理的热数据被快速的淘汰了，此时可以考虑增加来年代大小来增加数据进入新生代的概率，或者调小来年代停留时间来增加数据进入新生代的概率。
36.为什么Change Buffer（写缓冲区）只适用于非唯一索引
Buffer Pool中含有多个模块，Change Buffer则是Buffer Pool中主要的模块之一。 Change Buffer是InnoDB存储引擎中用于优化非唯一索引更新操作的一种数据结构。它的主要目的是减少对磁盘的I/O操作，从而提高数据库的性能。当InnoDB表中的非唯一索引的行被更新、删除或插入时，如果这些操作不需要立即更新索引页，那么这些操作的信息将被暂时存储在Change Buffer中，而不是直接写入磁盘。Change Buffer 属于Buffer Pool中的一部分，一般占用是25%的Buffer Pool，最大为50%的Buffer Pool。
change buffer 暂存更改 当需要更改的信息不在Buffer Pool中时，且数据符合非唯一索引的要求，会将带操作的数据的id存储在Change Buffer中，然后更改操作会正常记录在redo log中，然后是binlog落盘，redolog commit merge 操作 当数据被读取到Buffer Pool时会触发merge操作，此时会先将数据执行在Change Buffer中的动作，然后再将其刷新到Buffer Pool中 其他操作 change buffer中的数据是在Buffer Pool中，因此存在断电即失的风险，所以Mysql会不定时将这里的数据刷新到磁盘，也不一定完全是在将数据加载到Buffer Pool中才会Merge操作，此外change buffer空间有限，所以也需要淘汰机制，对于很有没有操作的数据也会进行淘汰（先入盘）。 为什么不适用于唯一索引呢？因为唯一索引的更改必须保证数据的唯一性，而数据不在Buffer Pool则必须回表或者将相关数据加载到Buffer Pool中进行比较，所以不适用于唯一索引。
五、各种锁
这一块用以总结Mysql中的各种锁机制，先对锁的基础知识进行普及。下面说的锁都是InnoDB的锁，MyISAM只支持表锁，数据更新为表增加表锁。
行锁（RecordLock） 行锁基于索引实现，分为共享锁、排它锁，行锁都是悲观锁，行锁可以提高并发能力，但是行锁的加锁和释放锁对cpu开销会很大。可以分为共享锁（S所）和排他锁（X锁），如果没有索引InnoDB则不会使用行锁，而是使用表锁，来锁住全表的记录。在RC、RR模式下可用间隙锁（GapLock） 上面说了行锁是基于索引实现的，当更新条件是唯一索引或者主键索引时都是行锁，若是更新条件是索引，但不是主键或者唯一索引时，可能会有间隙锁产生，间隙锁就是锁住两条索引的间隙，不让数据进行在间隙进行插入（InnoDB索引是排好序的所以很容易找到该锁住的间隙），比如使用name作为表的索引，表中有三条name为张三的索引记录，当更新数据时会将这三条记录和三条记录中间的两个间隙外加两边的两个间隙都进行锁住，防止数据的变更。间隙锁一般不会单独存在，出现基本就是和行锁一起出现的，RR模式下可用Next-key Lock 他是行锁+间隙锁，会同时锁住记录和记录周围的间隙，在RR模式下可用。在InnoDB中数据进行加锁时，默认加的就是Next-key Lock，也就是行锁+间隙锁，若是发现索引时唯一索引或者主键索引则会将锁优化为行锁。表锁 当更新数据条件不是索引时，走的就是表锁，就是会锁住表中的所有记录。当然即使用了索引如果更新的数据量比较大时，InnoDB也有可能走表锁，此时优化器认为表锁性能更高就会走表锁了。
37.数据库锁的种类
mysql中锁根据不用的划分类型可以有如下几种锁，锁的详细解解释参照上面的简介 根据锁的级别可以分为：行锁、间隙锁、next-key lock、表锁。行锁锁住单行，间隙锁锁住行间间隙。next-key lock是这两个的组合。 根据是否共享可以分为：共享锁（S锁）、排它锁（X锁）。共享锁支持其他共享锁的加入，不支持排它锁加入，排它锁则不支持任何锁的加入。注意一条数据被加入共享锁，则该数据不支持其他事务线程的修改，支持查询。若是一条数据被加入排它锁，则不支持其他线程修改，但支持其他事务线程的查询（这里查询其实走的是MVCC）。 根据锁的实现机制可以分为：乐观锁、悲观锁。Mysql中的乐观锁一般使用版本号来实现，更新前需要比对版本号，版本号一致再更新，不一致则不更新，这是一种CAS机制，也是典型的乐观锁实现原理。悲观锁则有共享锁和排它锁两种，需要借助sql来实现（具体sql看下面一个面试题）。
38.说说共享锁（S锁）和排它锁（X锁）
无论是共享锁还是排他锁都是一个悲观锁，他俩的主要区别是其他事务线程能否也对该数据进行持有，共享锁可以与其他共享锁共享数据，排他锁则拒绝一切的其他加锁行为。不过需要注意无论是共享锁还是排它锁，都是支持其他线程进行读取的。在Mysql中共享锁和排他锁都需要使用特定的sql语句来实现。注意无论是共享所还是排它锁，在RR模式下，存在唯一索引的条件都是行锁，非唯一索引则可能是Next-key lock ,没有索引则是表锁。
-- 开启事务
begin;
-- 添加共享锁
select * from user_info where id =2 lock in share mode;
-- 提交事务，事务提交自动释放锁
commit;
上面是一个事务对数据进行加共享锁的sql，如果还存在两一个事务需要对该条数据进行修改，如下：
-- 开启事务
begin;
-- 更新
update user_info
set usr_last_name = ''
where id = 2;
-- 提价事务
commit;
此时在第一个事务提交之前，事务二无法对其进行修改，也无法增加排它锁，但可以对他增加共享锁。 对于排他锁，则应该这么写：
begin;
select * from user_info where id =2 for update;
commit;
如果还有一个线程有如下代码（和共享锁的一样）：
-- 更新
update user_info
set usr_last_name = ''
where id = 2;
-- 提价事务
commit;
则在排他锁提交前，任务事务不能对该数据进行加任何锁，不过可以读取该条数据，不过读取该条数据的原理是通过MVCC进行读取的。
特别注意： MVCC解决并发读问题，并发读时都是走的MVCC也就是快照读，但是delete、update、insert都是默认直接加排它锁的（RC、RR）。无论是排它锁还是共享锁，只要条件是唯一索引都是行锁，若是非唯一索引则是next-key lock，没有索引则会锁定全表，使用上面的方式就可以进行验证，这里就不重复验证了。
39.InnoDB的行锁的实现
InnoDB的行锁是通过对索引上的记录加锁来实现的，所以说如果没有索引肯定走不到索引，没有索引进行更新就会变成全表扫描了。所以更新时更应该使用主键和唯一索引进行数据更新了，最次也要使用一个普通索引作为条件，使用普通索引时InnoDB加的锁是行锁+间隙锁，此时性能比表锁会高一些，不过值的注意的是虽然行锁的并发性高，但是加锁和释放锁的是很消耗cpu资源的。 这里可能会有个延伸的问题，就是如果条件是唯一索引+其他无索引条件，或者普通索引+其他无索引条件时，InnoDB的锁使用情况是什么样的呢？ 当这种场景时都是根据索引进行加锁，索引扫描的范围就是加锁范围，对于索引外的其他条件其实对加锁不影响，如果都是非索引条件自然就是全表扫描了。
40.并发事务会产生哪些问题
所谓并发事务就是多个事务操作同一条数据，此时被视为并发事务，根据事务的隔离级别的不同，事务并发可能存在以下问题： 隔离级别为读未提交：会有脏读、不可重复读、幻读问题 隔离级别为读已提交：解决了脏读，会有不可重复读、幻读问题，这是Oracle和SqlServer的默认隔离级别 隔离级别为重复读：解决了脏读、不可重复度，会有幻读问题，这是Mysql的默认隔离级别 隔离级别为串行化：解决了所有问题，不过性能也是最差的，对并发的支持很差。 此外还有回滚覆盖的问题（RC读已提交会有这种问题）、提交覆盖（不可重复读），当一个事务提交的数据以后其他事务发生了回滚，可能会把这个数据回滚覆盖了，这个问题在RR下不存在，因为RR中增删改都是加锁的行为，只有事务提交后其他事务才可以对当前数据进行增删改，所以不会有回滚覆盖和提交覆盖的问题。
事务本质上是对MVCC和锁的一个封装，隐藏了底层细节，一般使用事务时是无需我们手动进行增加S锁和X锁的，InnoDB会自动帮我们处理这些，当只有使用的隔离级别无法满足数据的锁定情况时才需要我们手动加锁。
41.Mysql死锁的原因和处理方法
死锁原因：
表级的死锁：两个事务都持有了对方想要获取的表的锁，常出现在没有索引的增删改中。
行级的死锁：两个事务都持有了对方想要获取的行的锁，出现在唯一索引和主键索引中。
间隙锁的死锁：两个事务都持有了对方想要获取的间隙的锁，出现在使用普通的非唯一索引的场景中。 死锁的表现也比较明显，会导致对应的数据的所有增删改进入阻塞，然后导致事务线程不断堆积，系统响应越来越慢导致数据库扛不住崩溃都有可能。 解决方法： -- 查询正在运行的事务，这里可以看到所有的事务，包含产生了锁的事务信息
SELECT * FROM information_schema.INNODB_TRX;
从下图位置可以看到那些线程持在执行，哪些线程在等待锁，Running的是持有锁的sql线程： 然后从下图位置可以看到他的事务id和对应的sql： 也可以通过如下命令可以查看所有的sql线程，里面也会有对应的sql信息，就可以根据这个找到对应的系统代码了 -- 查看所有线程
SHOW PROCESSLIST;
然后就可以从这个列表中根据最开找到的线程id找到真正的线程，再根据这个线程对应的info信息，看看系统中哪块代码的问题。基本就能找到死锁的根本原因了。 如何避免死锁： 死锁之所以产生，基本都是因为相互持有了对方想要获取的锁，如果多个事务获取锁时，应该按照固定的顺序获取锁（增删改应该根据固定的表顺序进行，比如都是先操作A表，然后再操作B表），避免这种乱序的获取，这样可以很大程度上避免死锁的产生。
六、数据库日志
这一节总结日志相关的面试题，Mysql的undo log、redo log、bin log则是他的核心之一，所以这块的掌握是必须的，也是面试的高频考点。
42.三种日志的区别：undo log、redo log 、bin log
undo log（回滚日志）： undo log是InnoDB的行为，主要用途是用于事务回滚和MVCC使用，在事务开始之前数据就会记录数据到undolog buffer（Buffer Pool中也是数据页形式存在）中，他通过记录一条完整的修改前数据来帮助事务进行回滚，发生回滚时会先将Buffer Pool中的数据进行回滚，此外undo log的版本链还被用于MVCC，在事务编发读时，之所以可以实现事务内部的多次读取不会出现不一致的情况（RR模式）就是依赖于undo log的版本链（这里不做细致介绍，上面有专门介绍这块的内容）。此外还需要说明的是undo log在内存中就会有丢失的风险，所以undo log还有一部分在磁盘，用以持久化undo log的信息，当事务提交后会通过fsync线程将undo log buffer中的信息持久化到undo log file中（其实就是通过Buffer Pool的刷脏）。这里必须要说的是undolog buffer 其实就是Buffer Pool中的数据页，他们通过roll pointer进行关联形成一个rundo 链，和普通的数据页没有任何区别，所以对于undo log的操作也会有redo log 的产生，undo log也会有刷脏持久化。 redo log（重做日志）： redo log是InnoDB特有的日志，主要用途是用于Mysql在InnoDB刷脏之前异常崩溃的场景，若是InnoDB刷脏之前crash了，那么InnoDB就可以使用redo log进行回复Buffer Pool中的内容，这种技术被称为日志先写（write ahead logging，很多数据库都有这个设计），那为什么可以使用redo log可以恢复呢，这就需要说下redo log产生的过程了，InnoDB在开始写Buffer Pool的数据之后就会写redo log日志，此时InnoDB会将信息存入到redo log buffer 中，这个阶段也被称为redo log的prepare阶段（二阶段提交的一阶段），经过一定时间并在事务结束之前会将redo log持久化到磁盘中的redo log中，当事务结束后此时bin log持久化成功了，会做redo log的commit（二阶段提交的二阶段），然后会在redo log日志中表示日志已经是二阶段的状态了。此时是可以保证redo log已经持久化成功了的，所以对于提交的事务，即使在Buffer Pool刷脏之前宕机了，InnoDB依然可以使用redo log进行恢复，若是事务未完成失败则会根据undo log进行回滚。那么既然可以利用redo 恢复Buffer Pool应该就可以用它恢复磁盘数据，为什么不用它记录磁盘数据而使用bin log呢，原因是因为redo log被设计为环形数据结构，只能承载一定量的数据，redo log的写入是顺序写很高，以提高性能减少数据丢失的风险，而若是使用的bin log，效率并没有这么快，不适合做事物的备份信息，所以正常也应该是只是通过这部分做不丢失的风险，而不应该做为整体的回滚方案。 说到这里还需要提下Doublewrite Buffer，他是为了解决刷脏期间的写失效问题的，Buffer Pool的脏页在写入过程中如果失败（Buffer Pool的页是16kb，OS 的页是4kb，所以一次Buffer Pool的数据写入需要分为四次），就无法完全通过redo log进行恢复了因为他们的page不一致，此时需要使用Doublewrite file 进行数据恢复了，Doublewrite Buffer 也是一种日志先写的思路，先通过顺序写将日志写入到了磁盘中的Doublewrite log中，然后再写入磁盘（如果在日志先写就失败了应该会出现问题吧）。 bin log（二进制日志）： bin log是Mysql的行为与存储引擎无关，主要用途是记录数据库的数据变更，此外还用与主从模式的搭建，从库需要解析主库的binlog以实现主从同步，bin log会在事务提交之后写入，DML、DDL操作都会产生binlog日志，bin log日志对于数据的记录方式分为三种：
ROW：记录数据库每行的变化，如果是个范围修改，那么他会记录每行的前后变化，所以被称为ROW模式基于行进行记录，他的优点是准确度高，缺点是占用空间大，如果是DDL则会出现大量的binlog日志，磁盘空间很快会被占用，可用于主从同步。STATEMENT: 该模式下的范围修改不会记录每行的变化，而是记录总体变化是一个sql，优点是文件占用小，缺点是对于函数等的记录在主从同步时可能会出现主从同步解析异常的情况。MIXED：混和模式，就是上面两种的混和，他对于主从同步有意义的场景使用ROW记录，对于没有意义的场景使用STATEMENT记录，从而在性能和空间上找到了一个这种的方案，这种模式下也支持主从同步。 此外binlog还经常用以数据的全量数据恢复，一般数据库的备份行为是周期性备份全量，每日备份增量，全量数据通过dump进行备份（注意dump会导致大面积锁表，生产工作时间千万别用），而增量数据则是通过binlog进行备份。在数据库异常崩溃时一般是先通过全量备份恢复一个临时库，然后再通过增量的binlog恢复增量场景的数据，从而实现数据库的数据恢复（误操作，将数据大面积删除或者污染时也可以使用这种方式）。 binlog一般设计的配置参数如下： #开启binlog日志
log_bin=ON
#binlog日志的基本文件名
log_bin_basename=/var/lib/mysql/mysql-bin
#binlog文件的索引文件，管理所有binlog文件
log_bin_index=/var/lib/mysql/mysql-bin.index
#配置serverid，主从模式下主从的该值不可相同
server-id=1
下面是数据库对三种日志的执行时间的图示：
43.redo log 和 undo log 的持久化策略
undo log用于事务回滚和MVCC，redo log则用于系统异常crash时恢复Buffer Pool中的未脏刷的数据，这是他们的不同用途，不过他们都有一个相同点那就是都是分为缓存和磁盘两部分。undo log在内存中存放在Buffer Pool中就是普通的数据page，持久化通过刷脏的方式进行持久化，而且也是必须得在对应的redo log持久化成功后才可以刷脏，他的操作过程也会产生redo log，通常情况下undo log严格说他是一个完整的数据，而不是日志。需要注意的是undo log持久化之后并不会立刻被删除，因为他的数据还会被MVCC使用，而是将代删除的undo log放入到一个队列中，由purge线程来控制删除的时间。
redo log的持久化一份分为两个过程（这里是对过程进行了拆分）写入到OS Buffer，刷新到redo log 磁盘。任何缓冲区的数据一般刷新到磁盘都是需要先经过OS Buffer的，然后由OS Buffer使用fsync()刷新到磁盘中。redo log也是这样，不过redo log提供了对这个过程的控制参数：
show variables like '%innodb_flush_log_at_trx_commit%'
下面是对应的值的解释，总共有三个值可供设置0,1,2:
0: 延时写，实时刷，每秒将数据同步到OS Buffer再实时调用fsync()刷到磁盘，极端场景丢失1s数据，事务回滚1: 实时写，实时刷，实时写入OS Buffer ，然后实时调用fsync()刷到磁盘，这是默认值，很少丢失数据，但性能相对来说略低2: 实时写，延时刷，实时写入OS Buffer，每秒调用fsync()刷到磁盘，建议设置的值，极端丢失1s数据，事务回滚
44.bin log与 undo log的区别
这个问题，可以从多个角度说下他们的区别：
位置：binlog是Mysql的server级别的日志存储在磁盘上，undo log是InnoDB的事务日志存储在Buffer Pool中，通过刷脏的方式进行持久化 格式：binlog有三种数据格式对数据进行记录ROW、STATEMENT、MIXED，undolog则是通过记录数据的原始信息的变化和事务id，roll pointer 等信息来实现功能 用途：binlog用于主从同步，数据的备份和恢复，undo log用于事务的回滚和MVCC
45.Mysql的bin log有哪几种日志格式
binlog有三种日志格式ROW、STATEMETN、MIXED。(详见上面的undo、redo、bin的介绍)
46.Mysql线上修改大表结构有哪些风险
这里首先需要明确的是表结构的修改都是锁表的，比如新增列，删除列，更改列的长度等等都是会锁表的（索引的操作不会锁表），此时其他的查询操作不受影响，但是更新操作都会阻塞等待锁。又因为是大表所以这个时间一般会很长，具体多长得看数量的大小了，所以一般数据量大的表的表结构修改都是在业务量比较低的时候，尽量减少影响。
数据量大，修改时间长，影响业务的增删改操作数据量大，影响主从同步，造成主从不同步数据量大，导致cpu和内存升高，影响数据库服务整体性能
数据库表结构操作流程： 1.对表加锁，此时表是只读状态 2.复制原表数据结构 3.修改表的物理结构 4.将原表数据同步到中间表，同步完成，删除原表锁定中间表 5.将中间件表更名为原表 6.刷新数据字典，释放锁
大表的修改如果必须是工作时间可以考虑使用三方工具，这个影响可能会更小，比如online-scheme-change。
七、数据库优化和架构
这部分用以总结数据库整体的优化与架构的模式等场景
47.Mysql的体系架构
Mysql被设计为一种分层的可拔插的架构，各层级分工明确，且大部分支持进行替换，比如查询优化器、解析器、存储引擎等都是支持替换的，结构不复杂，如下图所示：
48.说说Mysql的主从复制
流程 Mysql的主从复制（Master-Slave Replication）依赖于binlog日志，binlog日志中记录输了数据的变化情况，从库可以根据这个变化进行同步信息，具体同步流程如下图： 1.主库更新数据写入binlog 2.主库使用binlog dump线程通知从库 2…从库读取主库的binlog日志，将其写入到自己的中继日志（relay log） 3.从库将中继日志中的数据写入到从库中，注意这个过程默认单线程，5.7以后支持多线程配置 主从复制用途： 实时灾备，用于故障恢复，从库可以作为一个备机 用于读写分离，提供查询服务 主从部署必要条件： 主库开启binlog配置 主从的server-id不同 主从服务器联通 主库配置 配置参考：主从配置，这里只提供配置，不提供 [mysqld]
server-id = 1 # 节点ID，确保唯一
log config
log-bin = mysql-bin #开启mysql的binlog日志功能，binlog日志位置
sync_binlog = 1 #控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
binlog_format = mixed #binlog日志格式，mysql默认采用statement，建议使用mixed
expire_logs_days = 7 #binlog过期清理时间
max_binlog_size = 100m #binlog每个日志文件大小
binlog_cache_size = 4m #binlog缓存大小
binlog-do-db=test1 #需要同步的数据库
binlog-do-db=test2 #需要同步的数据库
max_binlog_cache_size= 512m #最大binlog缓存大
binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行
auto-increment-offset = 1 # 自增值的偏移量
auto-increment-increment = 1 # 自增值的自增量
slave-skip-errors = all #跳过从库错误
从库配置 [mysqld]
server-id=2
log-bin=mysql-bin #如果从数据库，不需要再往其他数据库同步，可以注释掉
relay-log=slave-relay-bin #必须开启，从主数据库同步的binlog会写入到该目录下
relay-log-index=slave-relay-bin
#如果主从数据库名称相同
replication-do-db=数据库名
#如果主从数据库名称不同
replication-rewrite-db= 主数据库名 -> 从数据库名
49.如何解决主从不同步的问题
在使用主从模式时，主从不同步是属于一种经常碰到的问题，也是我们必须考虑的问题。想要解决主从不同步的问题，首先需要明确主从不同步的原因： 1.默认情况下主库和从库同步日志全部是单线程，而主库的写入是多线程，所以从库的同步肯定没有主库快，有一定的延时。
解决：Mysql5.7 开始支持从库开启多线程从relay log中将数据写入表中，以提升主从同步的速度，需要修改下面两个参数show variables like '%slave_parallel%'
slave_parallel_type: 并发类型，如果需要设置多线程则需要调整为LOGICAL_CLOCK slave_parallel_workers：线程数，设置为想要的线程数即可，也不建议太大 这样就可以实现从库的并发写入了，从而提升服务器的写入速度。以减少主从不同步的影响。
2.部分写请求意外写入从库，导致了数据再从主库同步过去时数据重复出现主从不一致的情况（id不一致，时间不一致）
解决：设置从库为只读模式，禁止任何写入show variables like '%read_only%'
查看只读配置： 如果从库想要只读需要将read_only设置为1或者true将只允许读不允许从库写，不过这里的只读并不会影响主从复制的写入，而且这个设置不会影响super用户的写入，上图中还有一个super_read_only的参数，不建议使用，使用他会导致主从同步的从库写也会被阻止，所以一般都是使用read_only，然后将从库上super用户的ip权限设置为本机，这样就可以防止从库的写入了操作了。
3.造成主从不一致的原因还有一个主要原因就是主库和从库的同步机制，默认是异步的，异步的同步就会造成数据的不同步
解决：解决这个就需要更改主从同步的模式了，但是改成完全同步的场景就会造成响应速度慢等问题，所以一般采用半同步的方式，在效率和一致性上找一个折中方案。不过Mysql本身不支持半异步的同步方式，需要使用三方插件rpl_semi_sync，详细配置可参见：半同步与增强半同步
4.此外解决主从不同步的问题的核心还可以再从以下三方面考虑 IO：这里IO使用了多线程和半异步，是可以降低主从不同步的风险的，同时可以考虑提升从库的内存，增加从库的Buffer Pool的大小，提升数据的处理速度。 CPU：提升CPU性能，不过这里主要还是IO的瓶颈，CPU应该不会成为主要瓶颈，而且也通过多线程写入了一半cpu不是瓶颈 网络：尽量让主从处于一个机房内部，这样网络传输就会非常快，网络基本就不会成为带宽的瓶颈。
50.什么是分库分表，什么时候进行
分库分表就是将数据库拆分为多个，将一个表拆分为多个表，根据拆分方式不同可以划分为水平拆分和垂直拆分。水平拆分是表结构保持一致，根据数据的范围进行拆分。而垂直拆分是直接将表的一部分列拆出去。最后达到降低单表和单库的数据量的目的，从而提升查询的效率。
什么场景下分表 对于Mysql来说单表数据量超过1000w大小超过100G，查询性能就会降低很多（这个数据范围再大很可能会造成索引树的深度增加，导致查询效率降低，1000w和100G是官方给的），此时适合水平拆分，将数据分开存储。若是因为表的列太多，导致查询的效率下降，可以将单个表的列拆分为两个或多个表，不过此时也会增加查询的复杂度。
垂直分表：根据字段拆分，将业务关联性更强的字段放置到同一张表中，拆分出两张或多张表水平分表：水平分表就是将单表的数据根据不同的规则拆分到不同表，可以根据id的基偶等规则，不过水平分表和垂直分表不能解决，整体的IO、CPU的瓶颈，不过会提升单表查询的效率 什么场景下分库 单机存储内存和磁盘达到瓶颈，或者连接数达到上限，此时可以考虑进行分库，将部分表拆分到其他库，以减轻当前库的压力。
垂直分库：根据业务拆分，将不同的业务表分别存储到不同的数据上水平分库：不同库具有相同的表结构，只是数据不同，可以根据id的基数偶数来拆分，也可以根据雪花id的机器信息来拆分等，水平拆分能够缓解单机和单库的性能瓶颈和压力，突破IO瓶颈， 连接数，硬件资源等瓶颈
51.四大范式
第四大范式是对三大范式的进一步补充，但并不是说在设计库表时就必须百分百遵循这些范式，这些范式给我们提供了一个标准的模版，使用时应该以这个为模版结合自己的实际业务。
第一范式(1NF)： 基本要求：表中的所有字段都是原子性的，即不可再分的数据项。 目的：确保每个字段都是单一属性的，避免将多个值存储在一个字段中，从而简化数据的查询和更新 举例：人员表中维护订单id数组，这个就是违反原子性的设计第二范式(2NF)： 基本要求：在第一范式的基础上，表中的所有非主键字段都必须完全依赖于主键，而不是仅依赖于主键的一部分。 目的：减少数据冗余，确保数据的一致性，避免更新异常。 举例：主键部分信息和某个字段的信息一致，这个基本应用中不会有人犯这个错误第三范式(3NF)： 基本要求：在第二范式的基础上，表中的所有字段不仅必须直接依赖于主键，而且不能存在传递依赖，即不能依赖于其他非主键字段。 目的：进一步减少数据冗余，简化数据维护。 举例：某个字段受其他字段的更新影响，会有联动关系巴斯-科德范式(BCNF): 基本要求：它在3NF的基础上消除了主属性对候选键的部分依赖或者传递依赖的关系（可以确定数据唯一性的组合列） 目的：处理第三范式无法处理的一些特殊依赖关系，进一步消除数据冗余。 举例：可以参考这个文章：巴斯范式，简述就是表中的主数据不影响对候选键有依赖和传递依赖，否则就不应该在一个表。
对于范式的满足越深，表中的冗余字段越少，表就会越多，真正设计库表时可以考虑业务等场景，无需全部满足，但应该尽量满足。
相关阅读
您阅读本篇文章共花了：
发表评论