Mysql 面试实战系列（四）——快马扬鞭

数聚天下 2017-05-20 08:34

走过路过不要错过，错过一个点也许就错过一两万，错过一次好的机会。如果您有什么好的建议或者问题，也请留言，大家共同商讨。

1. 讲述下如何做sql优化

Øexplain 出来的各种 item 的意义

Øprofile 的意义以及使用场景。

Øexplain 中的索引问题。

(1) explain 出来的各种 item 的意义

id:每个被独立执行的操作的标志，表示对象被操作的顺序。一般来说， id 值大，先被执行；

如果 id 值相同，则顺序从上到下。

select_type：查询中每个 select 子句的类型。 具体待明天补充。

table:名字，被操作的对象名称，通常的表名(或者别名)，但是也有其他格式。

partitions:匹配的分区信息。

type: join 类型。 具体指待明天补充。

possible_keys：列出可能会用到的索引。

key: 实际用到的索引。

key_len: 用到的索引键的平均长度，单位为字节。

ref:表示本行被操作的对象的参照对象，可能是一个常量用 const 表示，也可能是其他表的 key 指向的对象，比如说驱动表的连接列。

rows:估计每次需要扫描的行数。

filtered: rows * filtered / 100 表示该步骤最后得到的行数(估计值)。

extra:重要的补充信息。 具体待明天补充。

(2) profile 的意义以及使用场景。

Profile 用来分析 sql 性能的消耗分布情况。当用 explain 无法解决慢 SQL 的时候，需要用

profile 来对 sql 进行更细致的分析，找出 sql 所花的时间大部分消耗在哪个部分，确认 sql

的性能瓶颈。 (我用的也不多，期待更好的答案)

(3) explain 中的索引问题。

Explain 结果中，一般来说，要看到尽量用 index(type 为 const、 ref 等， key 列有值)，避

免使用全表扫描(type 显式为 ALL)。比如说有 where 条件且选择性不错的列，需要建立索引。

被驱动表的连接列，也需要建立索引。被驱动表的连接列也可能会跟 where 条件列一起建立

联合索引。当有排序或者 group by 的需求时，也可以考虑建立索引来达到直接排序和汇总

的需求

2. 讲述下做过何种备份及相关计划

Ø备份计划

Ø备份恢复时间

Ø备份恢复失败如何处理

原理：

mysqldump 属于逻辑备份。加入--single-transaction 选项可以进行一致性备份。后台进程

会先设置 session 的事务隔离级别为 RR(SET SESSION TRANSACTION ISOLATION LEVEL

REPEATABLE READ)，之后显式开启一个事务(START TRANSACTION /*!40100 WITH CONSISTENT

SNAPSHOT */)，这样就保证了该事务里读到的数据都是事务事务时候的快照。之后再把表的

数据读取出来。 如果加上--master-data=1 的话，在刚开始的时候还会加一个数据库的读锁

(FLUSH TABLES WITH READ LOCK),等开启事务后，再记录下数据库此时 binlog 的位置(show

master status)，马上解锁，再读取表的数据。等所有的数据都已经导完，就可以结束事务。

Xtrabackup:

xtrabackup 属于物理备份，直接拷贝表空间文件，同时不断扫描产生的 redo 日志并保存下

来。最后完成 innodb 的备份后，会做一个 flush engine logs 的操作(老版本在有 bug，在

5.6 上不做此操作会丢数据)，确保所有的 redo log 都已经落盘(涉及到事务的两阶段提交

概念，因为 xtrabackup 并不拷贝 binlog，所以必须保证所有的 redo log 都落盘，否则可

能会丢最后一组提交事务的数据)。这个时间点就是 innodb 完成备份的时间点，数据文件虽

然不是一致性的，但是有这段时间的 redo 就可以让数据文件达到一致性(恢复的时候做的事

情)。然后还需要 flush tables with read lock，把 myisam 等其他引擎的表给备份出来，

备份完后解锁。 这样就做到了完美的热备。

备份计划：

视库的大小来定，一般来说 100G 内的库，可以考虑使用 mysqldump 来做，因为 mysqldump

更加轻巧灵活，备份时间选在业务低峰期，可以每天进行都进行全量备份(mysqldump 备份

出来的文件比较小，压缩之后更小)。

100G 以上的库，可以考虑用 xtranbackup 来做，备份速度明显要比 mysqldump 要快。一般

是选择一周一个全备，其余每天进行增量备份，备份时间为业务低峰期。

备份恢复时间：

物理备份恢复快，逻辑备份恢复慢

备份恢复失败如何处理：

首先在恢复之前就应该做足准备工作，避免恢复的时候出错。比如说备份之后的有效性检查、

权限检查、空间检查等。如果万一报错，再根据报错的提示来进行相应的调整。、

3.500 台 db，在最快时间之内重启

可以使用批量 ssh 工具 pssh 来对需要重启的机器执行重启命令。 也可以使用 salt（前提是客户端有安装 salt）或者 ansible（ ansible 只需要 ssh 免登通了就行）等多线程工具同时操作多台服务器

4.innodb 的读写参数优化

Ø读取参数， global buffer pool 以及 local buffer

Ø写入参数

Ø与 IO 相关的参数

Ø缓存参数以及缓存的适用场景

(1)读取参数， global buffer pool 以及 local buffer

Global buffer：

Innodb_buffer_pool_size

innodb_log_buffer_size

innodb_additional_mem_pool_size

local buffer(下面的都是 server 层的 session 变量，不是 innodb 的)：

Read_buffer_size

Join_buffer_size

Sort_buffer_size

Key_buffer_size

Binlog_cache_size

(2)写入参数

insert_buffer_size资源由 www.eimhe.com 美河学习在线收集提供

innodb_double_write

innodb_write_io_thread

innodb_flush_method

(3)与 IO 相关的参数

Sync_binlog

Innodb_flush_log_at_trx_commit

Innodb_lru_scan_depth

Innodb_io_capacity

Innodb_io_capacity_max

innodb_log_buffer_size

innodb_max_dirty_pages_pct

(4)缓存参数以及缓存的适用场景

指的是查询缓存吗？？？ 使用于读多写少，如分析报表等等

query_cache_size

query_cache_type

query_cache_limit

maximumquery_cache_size

如果有帮到客官，回个赞吧