MySQL面试真题

自我介绍三要素

1. 自我认知的能力。意思就是要对自己有个全方位的认识，不要觉得简历上写了就不用再说一遍了。一定要让面试官感觉你很重视，很真诚。
2. 对岗位的认知能力。从之前的经历中，挑选出匹配你要面试的岗位的职责的本领。阐述之前经历的优化点，抓住关键、可以让面试官快速的记住你。
3. 分清主次，捡重要的说。学会去听清除对方所提出的问题，不要在不了解问题的基础上畅所欲言。回答问题时，要学会摸清对方心理，捡重点阐述。

技术问答

问题1：MySQL主要存储引擎MyISAM和Innodb的不同之处？

解答思路：可以从五个方向去介绍：

1. 事务支持的不同（MyISAM不支持事务，Innodb支持事务）
2. 锁粒度（Innodb 支持行锁， MyISAM表锁）
3. 存储空间（InnoDB 即缓存索引文件，又缓存数据文件，MyISAM只缓存索引文件）
4. 存储结构（MyISAM的数据扩展文件名为.MYD，索引文件扩展名为.MYI；InnoDB 表数据都保存在一个数据文件里面，即.ibd）;
5. 统计总行数（MyISAM 有保存表的总行数，select count(1) 的时候直接取出总行数；InnoDB没有保存总行数，select count(1) 会统计全表，非常耗时）

问题2：介绍一下InnoDB的体系结构

解答思路：谈及InnoDB的体系结构，首先要考虑MySQL的体系结构：分为server层和存储引擎层两部分

先要聊清楚MySQL的整体方向，然后再去设计InnoDB的体系结构，建议从三方面介绍：内存、线程、磁盘
内存中包含： insert_buffer、data_buffer、 index_buffer、 redo_log_buffer、 double_write
内存刷新到磁盘的机制：redo log buffer、 脏页、 binlog cache 的刷新条件。
各种线程的作用： master_thread、 purge_thread、 redo log thread、 read thread、 write thread、 page cleaner thread
磁盘中存放的数据文件： redo log、 undo log、 binlog

问题3：MySQL有哪些索引类型

解题思路： 可以从三个角度去谈。首先从数据结构上可以分为B+tree索引、Hash索引、fulltext索引（InnoDB和MyISAM都支持）；其次是从存储角度上去谈可以分为
聚集索引和非聚集索引；最后从逻辑角度上可以分为primary key、 normal key、 单列、 复合和覆盖索引

问题4：MySQL binlog有哪几种格式？生产中你用哪种？各自有什么特点？

解题思路：可以对各种格式的优缺点分别介绍

• statment 格式

优点： 不需要记录每一行的变化，减少了binlog的日志量，节省了I/O,提升了性能

缺点：当使用一些特殊函数，在跨库操作时容易丢失数据

不建议在生产环境使用

• row格式

优点：清晰记录了每一行数据的信息， 不会出现跨库丢失数据的情况，安全性能非常高。

缺点：当内容记录到日志中，都将以每行的修改来记录，会产生大量的binlog信息，网络开销也比较大

生产环境中推荐使用

• mixed 格式

MySQL 5.1的一个过渡版本，DDL语句会生成statment，DML 会生成row

生产环境不建议使用

问题5：MySQL 主从复制的具体原理是什么？

解题思路： 直接阐述其原理，思路要清晰。

主服务器把记录更新到二进制的日志文件binlog中，从服务器通过I/O thread 向主库发起binlog请求， 主服务器 通过I/O dump thread 把日志传递给从库，
从库通过I/O thread 记录到自己的中继日志中，然后通过SQL thread 应用中继日志中SQL内容。

问题6：MySQL 主从延迟的原理是什么？ 如何监控主从延迟，如何解决主从延迟？

解答思路： 这个是上一个问题的延伸，可以从最核心的延迟问题来讲解。我们知道主库可以并发写入，但是从库只能通过SQL thread 完成任务（MySQL 5.7之前），
这是延迟的最核心原因。

再从其它方面总结主从延迟原因

（1）MySQL主从同步从来都不是实时同步的，是异步的同步，也就是说，主库提交事务后，从库才再执行一遍

（2）在主库上对没有索引的大表的列进行delete 或者update的操作

（3）从库的硬件配置没有主库的好，经常忽略从库的重要性

（4）网络抖动导致I/O线程复制延迟

传统方法，通过比较 主从服务器之间的position号的差异值。或者通过第三方工具（业界中的瑞士军刀percona-toolkit）中的pt-heartbeat 命令进行主从延迟监控。
还可以通过查看second_behind_master 估算一下主从延迟时间

介绍完主从延迟问题之后，可以展开讨论如何解决主从延迟

（1） 使用MySQL 5.7的并行复制功能。在MySQL的5.7版本中，真正实现了基于组提交的并行复制功能，简单的说就是主库并行执行SQL语句，从库也可以通过多个
worker线程并发执行relay log 中主库提交的事务。想要开启MySQL 5.7的并行复制功能，可以在从库中设置参数 slave_parallel_workers > 0; 并把
5.7版本中新添加的slave_parallel_type 参数设置为 LOGICAL_CLOCK.该参数有DATABASE 和 LOGICAL_CLOCK 两个值。

（2） 可以采用 Percona公司的percona-xtradb-cluster（PXC架构），这种架构可以实现多节点写入，达到实时同步

（3） 业务规划初期，就要选择合适的分库分表策略，避免单表或者单库过大，带来额外的复制压力，从而带来主从延迟的问题

（4） 避免无用的IO消耗，可以增加高转速的磁盘、SSD

（5）适当调整buffer pool的大小

（6）避免让数据库进行各种大量的运算，要记住数据库知识用来存储数据的，让应用端多分担些压力，或者通过缓存来完成。

问题7：数据库的双一是什么？

解题思路： 可以从两个参数着手分析： 一个是sync_binlog = 1; 另一个是 innodb_flush_log_at_trx_commit = 1; 这两个参数是控制MySQL磁盘写入
策略以及数据安全性的关键参数。

innodb_flush_log_at_trx_commit = 1，每次提交事务时，MySQL都会把log buffer 的数据写入log file，并且刷盘；

sync_binlog = N (N > 0);代表 MySQL在写N次二进制日志（binlog）时，会使用fdatasync()函数将它的二进制日志binlog 同步到磁盘

问题8：如何实施大表DDL才能把性能影响降低到最低？

解题思路： 我们可以先通过传统的方法导入/导出数据, 新建一张一模一样的表结构，把需要执行的DDL语句在无数据的新表中执行，然后再把老表中的数据导入到新表，最后
把新表改为老表的名字。

问题9：为什么要为InnoDB表设置自增主键？

解题思路： 使用自增列做主键，写入顺序是自增的，和B+树叶子节点分裂的顺序是一致的。InnoDB 表的数据写入顺序和B+树索引的叶子节点顺序一致时，存取效率是最高的。

问题10：如何优化一条慢SQL？

解题思路：针对SQL的优化，不要一上来就回答添加索引，这样显得太不专业，可以从以下几个角度去讲

（1） 回归到表的设计层面上，数据类型选择是否合理

（2） 大表碎片的整理是否完整

（3）表的统计信息是否准确

（4）审查表的执行计划，判断字段上面有没有合适的索引

（5）针对索引的选择性，建立合适的索引（这又涉及到大表的DDL问题）

问题11：服务器负载过高或者网页打开缓慢，简单说下你的优化思路？

解题思路：我们可以从优化思路中的四维模型去阐述

首选要发现问题的过程，通过操作系统、数据库、程序设计、硬件角度四个维度找到问题所在。 先找到瓶颈点的问题，定制好优化方案，形成处理问题的体系模型。
体系模型定制好后，在测试环境进行优化方案的测试，达到理想的效果后再应用到线上环境。

问题12：接触到哪些MySQL的主流架构？ 架构应用中有哪些问题需要考虑？

（1）M-S

（2）MHA

（3）MM+Keepalived

（4）PXC

（5） 利用中间件ProxySQL 配合PXC架构

问题13：什么是死锁？ 锁等待？通过哪些表可以监控？

解题思路：死锁是指两个或者多个事务在同一资源上互相占用，并请求加锁时，导致的恶性循环现象。当多个事务以不同的顺序对同一资源加锁时，就会产生死锁。

锁等待：MySQL数据库中，不同session 在更新同行数据时，会出现锁等待的现象。

重要的三张锁的监控表：innodb_trx、 innodb_locks 和 innodb_lock_waits

问题14：之前处理过 MySQL的案例？

（1） MySQL版本升级

（2） 处理集群架构中的各种坑

（3）根据公司的业务类型，合理的设计库表结构和后期结构

（4）定期进行灾备演练

（5）恢复误删除的数据信息