数据库高级面试问题：数据库优化面试大全

数据库高级面试问题：数据库优化面试大全mysql主要的存储引擎myisam和innodb的不同之处？Drizzle与mysql的差别就比较大了，并且不能兼容，如果想运行此环境，就需要重写一些代码了！MariaDB不仅是mysql的替代品，主要还是创新和提高mysql自有技术。新功能介绍XtraDB是innodb存储引擎的增强版，可用来更好地发挥最新的计算机硬件系统性能，还包含在高性能模式下的新特性。它可以向下兼容，因为它是在innodb基础上构建，所以他有更多的指标和扩展功能。而且它在cpu多核的条件下，可以更好地使用内存，时数据库性能提到更高！

Question 1：

你目前接触的mysql版本是什么？除了官方版本，还接触过其他的mysql分支版本嘛？

产生分支的原因

许多开发人员认为有必要将其拆分成其他项目，并且每个分支项目都有自己的专长。该需求以及Oracle对核心产品增长缓慢的担忧，导致出现了许多开发人员感兴趣的子项目和分支

三个流行MySQL分支：Drizzle、MariaDB和Percona Server（包括XtraDB引擎）

MariaDB不仅是mysql的替代品，主要还是创新和提高mysql自有技术。

新功能介绍

1. multi-source replication 多源复制
2. 表的并行复制
3. galera cluster集群
4. spider水平分片
5. tokuDB存储引擎

XtraDB是innodb存储引擎的增强版，可用来更好地发挥最新的计算机硬件系统性能，还包含在高性能模式下的新特性。它可以向下兼容，因为它是在innodb基础上构建，所以他有更多的指标和扩展功能。而且它在cpu多核的条件下，可以更好地使用内存，时数据库性能提到更高！

Drizzle与mysql的差别就比较大了，并且不能兼容，如果想运行此环境，就需要重写一些代码了！

Question 2:

mysql主要的存储引擎myisam和innodb的不同之处？

1. 事务的支持不同（innodb支持事务，myisam不支持事务）
2. 锁粒度（innodb行锁应用，myisam表锁）
3. 存储空间（innodb既缓存索引文件又缓存数据文件，myisam只能缓存索引文件）
4. 存储结构
5. （myisam：数据文件的扩展名为.MYD myData ，索引文件的扩展名是.MYI myIndex）

（innodb：所有的表都保存在同一个数据文件里面 即为.Ibd）

5. 统计记录行数

（myisam：保存有表的总行数，select count(*) from table;会直接取出出该值）

（innodb：没有保存表的总行数，select count(*) from table；就会遍历整个表，消耗相当大）

Question 3：

Innodb的体系结构简单介绍一下？

谈及到innodb的体系结构，首先要考虑mysql的体系结构，分为两部分mysql的server层和存储引擎层

先要跟面试官聊清楚mysql的整体方向，然后再去涉及innodb体系结构

建议从三方面介绍innodb体系结构：内存----线程-----磁盘

内存中包含insert_buffer data_buffer index_buffer redo_log_buffer double_write

内存刷新到磁盘的机制，redo，脏页，binlog的刷新条件

各种线程的作用，master_thread purge_thread redo log thread read thread write thread page cleaner thread

磁盘中存放着数据文件，redo log，undo log，binlog

Question 4：

mysql有哪些索引类型：

1. 数据结构角度上可以分：B tree索引，hash索引，fulltext索引（innodb，myisam都支持）
2. 存储角度上可以分：聚集索引，非聚集索引
3. 逻辑角度上可以分：primary key，normal key，单列，复合，覆盖索引

Question 5：

mysql binlog有几种格式：

1. statement

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能

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

注：在生产中不建议使用

2. row

优点：清晰记录每行的数据信息，不会出现跨库丢数据的情况

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

注：生产中推荐使用

3. mixed

是mysql5.1的时候，一个过渡版本，DDL语句会记录成statement，DML会记录row。

注：生产中不建议使用

Qusetion 6：

mysql主从复制的具体原理是什么？

主服务器把数据更新记录到二进制日志中，从服务器通过io thread向主库发起binlog请求，主服务器通过IO dump thread把二进制日志传递给从库，从库通过io thread记录到自己的中继日志中。然后再通过sql thread应用中继日志中sql的内容。

Qusetion 7：

数据库中双一是什么？

sync_binlog=1

innodb_flush_log_at_trx_commit=1

innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数

innodb_flush_log_at_trx_commit设置为1，每次事务提交时MySQL都会把log buffer的数据写入log file，并且刷到磁盘中去。

sync_binlog =N (N>0) ，MySQL 在每写 N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去

Qusetion 8：

如何监控mysql replication复制延迟？

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

Qusetion 9：

大表DDL语句，如何实施，才能把性能影响降到最低？

1. 可以通过传统方法导入导出数据，新建一张与原表一样的表结构，把需要执行的ddl语句在无数据的新表执行，然后把老表中的数据导入到新表中，把新表改成老表的名字
2. 通过第三方工具 业界中的瑞士×××percona-toolkit中的命令，pt-online-schema-change进行在线操作
3. 对于新版本的mysql（5.7）可以直接在线online ddl

Qusetion 10：

为什么要为innodb表设置自增列做主键？

1.使用自增列做主键，写入顺序是自增的，和B 数叶子节点分裂顺序一致

2.表不指定自增列做主键，同时也没有可以被选为主键的唯一索引，InnoDB就会选择内置的rowid作为主键，写入顺序和rowid增长顺序一致

所以InnoDB表的数据写入顺序能和B 树索引的叶子节点顺序一致的话，这时候存取效率是最高

Qusetion 11：

如何优化一条有问题的sql语句？

针对sql语句的优化，我们不要上来就回答添加索引，这样显得太不专业。我们可以从如下几个角度去分析

1. 回归到表的设计层面，数据类型选择是否合理
2. 大表碎片的整理是否完善
3. 表的统计信息，是不是准确的
4. 审查表的执行计划，判断字段上面有没有合适的索引
5. 针对索引的选择性，建立合适的索引（就又涉及到大表DDL的操作问题）

Qusetion 12：

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

1. 首先我们要发现问题的过程，通过操作系统，数据库，程序设计，硬件角度四个维度找到问题所在
2. 找到瓶颈点的位置
3. 制定好优化方案，形成处理问题的体系
4. 体系制定好之后，在测试环境进行优化方案的测试
5. 测试环境如果优化效果很好，再实施到生产环境
6. 做好处理问题的记录

Qusetion 13：

接触过哪些mysql的主流架构？架构应用中有哪些问题需要考虑？

1. M-S
2. MHA
3. MM keepalived
4. PXC

共同存在的问题：主从延迟问题的存在，在主库宕机，切换过程中要考虑数据一致性的问题，避免出现主从复制不一致

Qusetion14：

什么是死锁？锁等待？如何优化这类问题？通过数据库哪些表可以监控？

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

锁等待：mysql数据库中，不同session在更新同行数据中，会出现锁等待

重要的三张锁的监控表innodb_trx，innodb_locks，innodb_lock_waits

Qusetion 15：

处理过mysql哪些案例

我们可以简单从mysql四个知识模块跟他聊聊mysql体系结构，数据备份恢复，优化，高可用集群架构

1. mysql版本的升级
2. 处理mysql集群的各种坑和问题
3. 根据公司业务类型，设计合理mysql库，表，架构。
4. 定期进行灾备恢复演练
5. 误删除数据之后，恢复数据

简单先从这几个方向说说，每个问题再展开分析。

当然还会有一些人事上面的问题，例如为啥选我们的公司，你觉得你自己的优势是什么？你期望的薪资大概是多少？这些问题，就很简单了。我们只要过了技术面试，这些都不是啥问题了！