这是我参与8月更文挑战的第7天，活动详情查看：8月更文挑战
概述
承接上一篇基础篇的面试题，本篇面试题将会列一些相对原理或者或者配置层次的面试题，基础篇传送门： mysql基础-常见面试题-基础篇
面试题
1、一条SQL语句在MySQL中如何执行的？
1、客户端请求
2、连接器（验证用户身份，给予权限）
3、查询缓存（存在缓存则直接返回，不存在则执行后续操作）
4、分析器（对SQL进行词法分析和语法分析操作）
5、优化器（主要对执行的sql优化选择最优的执行方案方法）
6、执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）
7、去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）
2、MySQL的复制原理以及流程
- 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
- 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中；
- 从：sql执行线程——执行relay log中的语句；
3、事务是如何通过日志来实现的？
- 事务日志是通过redo和innodb的存储引擎日志缓冲（Innodb log buffer）来实现的，当开始一个事务的时候，会记录该事务的lsn(log sequence number)号;
- 当事务执行时，会往InnoDB存储引擎的日志的日志缓存里面插入事务日志；
- 当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过innodb_flush_log_at_trx_commit来控制），也就是写数据前，需要先写日志。这种方式称为“预写日志方式”
4、MySQL binlog的几种日志录入格式以及区别
1、Statement：每一条会修改数据的sql都会记录在binlog中。
优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能 与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条 件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所 产生的日志量会增加多少，以及带来的IO性能问题。)
缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的 一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， 53323537，以及user-defined functions(udf)会出现问题).
2、Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。
优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下 每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题
缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比 如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。
3、Mixedlevel: 是以上两种level的混合使用
一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则 采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择 一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更
5、exlain各个item的意思以及作用
- select_type：表示查询中每个select子句的类型
- type：表示MySQL在表中找到所需行的方式，又称“访问类型
- possible_keys：指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
- key：显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL
- key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
- ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
- Extra：包含不适合在其他列中显示但十分重要的额外信息
6、innodb的读写参数优化
- 读取参数：global buffer pool以及 local buffer；
- 写入参数：innodb_flush_log_at_trx_commit 和innodb_buffer_pool_size
- 与IO相关的参数：
1) innodb_write_io_threads = 8
2) innodb_read_io_threads = 8
3) innodb_thread_concurrency = 0
7、缓存参数以及缓存的适用场景
缓存参数
query cache/query_cache_type
适用场景
并不是所有表都适合使用query cache。造成query cache失效的原因主要是相应的table发生了变更
1）读操作多的话看看比例，简单来说，如果是用户清单表，或者说是数据比例比较固定，比如说商品列表，是可以打开的，前提是这些库比较集中，数据库中的实务比较小。
2）当我们对数据库进行压测，把query cache打开，还是能收到qps激增的效果，当然前提示前端的连接池什么的都配置一样。大部分情况下如果写入的居多，访问量并不多，那么就不要打开，例如社交网站的，10%的人产生内容，其余的90%都在消费，打开还是效果很好的，但是你如果是qq消息，或者聊天，则不建议打开
8、MVCC定义以及实现原理
定义
MVCC是行级锁的一个变种，但它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只是锁定必要的行。MVCC的实现是通过保存数据在某个时间的快照来实现的。不管需要执行多长时间，每个事务看到的数据都是一致的。
实现原理
InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现。一列保存了行的创建时间，一列保存行的过期时间（或删除时间）。存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来查询到每行的版本号进行比较。
9、Repeatable Read 隔离级别下，MVCC工作方式
select操作
InnoDB会根据以下两个条件检查每行记录，只有符合这两个条件才会被查询出来。
1) InnoDB只查找创建版本号小于或等于当前事务版本号的数据行，这样可以确保事务读取的行，要么是在开始事务之前已经存在，要么是事务自身插入或修改过的。
2) 过期版本号要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除。
insert操作：InnoDB为新插入的每一行保存当前系统版本号作为行创建时间。
delete操作：InnoDB为删除的每一行保存当前系统版本号作为行过期时间。
update操作：InnoDB为插入的一行新纪录保存当前系统版本号作为行创建时间，同时保存当前系统版本号到原来的行作为过期时间；
10、undo log如何实现一致性
- undo log主要为事务的回滚服务。
- undo log记录了数据在某个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。
- undo log记录的是已部分完成并写入磁盘的未完成（未提交）的事务。
- 单个事务的回滚，不会影响到其他事务做的操作。
11、redo log 与 undo log 的区别
- 两种日志都是为了恢复操作。
- redo log是恢复提交事务修改的页操作。而undo log是回滚行记录到特定版本。
- 两者记录的内容也不同，redo log是物理日志，记录页的物理修改操作。而undo log是逻辑日志，根据每行记录继续记录。
12、如何在MySQL分析死锁产生的原因？
如果出现死锁，可以用 show engine innodb status; 命令来确定最后一个死锁产生的原因。返回结果包括：死锁相关事务详细信息， 如：引发死锁的**SQL**语句，事务已经获得的锁，正在等待什么锁，已及被回滚的事务等。
13、InnoDB如何避免死锁
1、使用select ... for update 语句获取必要的锁；
为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时，通过为预期要修改的每行使用 select ... for update 获取必要的锁。
2、直接申请足够级别的锁；
在事务中，如果要更新记录，应该直接申请足够级别的锁，即排它锁。而不应该先申请共享锁、更新时再申请排它锁。因为当再申请排它锁时，其他事务可能已经获得了相同记录的共享锁，从而造成锁冲突或死锁。
3、约定多个表的访问顺序；
如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。在应用中，如果不同的程序胡并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
14、使用索引查询一定能提高查询的性能吗？为什么
通常,通过索引查询数据比全表扫描要快.但是我们也必须注意到它的代价.
1）索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:
2）基于一个范围的检索,一般查询返回结果集小于表中记录数的30%
3）基于非唯一性索引的检索
