18道MySQL经典⾯试题（含简单答案解析）
⼀、MySQL的复制原理以及流程
(1)复制基本原理流程
1. 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
2. 从：io线程——在使⽤start slave 之后，负责从master上拉取 binlog 内容，放进 ⾃⼰的relay log中；
3. 从：sql执⾏线程——执⾏relay log中的语句；
(2)MySQL复制的线程有⼏个及之间的关联
flowplayer是什么意思MySQL 的复制是基于如下 3 个线程的交互（ 多线程复制⾥⾯应该是 4 类线程）：
1. Master 上⾯的 binlog dump 线程，该线程负责将 master 的 binlog event 传到slave；
2. Slave 上⾯的 IO 线程，该线程负责接收 Master 传过来的 binlog，并写⼊ relay log；
3. Slave 上⾯的 SQL 线程，该线程负责读取 relay log 并执⾏；
4. 如果是多线程复制，⽆论是
5.6 库级别的假多线程还是 MariaDB 或者 5.7 的真正的多线程复制， SQL 线程只做 coordinator，只负责把
relay log 中的 binlog读出来然后交给 worker 线程， woker 线程负责具体 binlog event 的执⾏；
(3)MySQL如何保证复制过程中数据⼀致性及减少数据同步延时
⼀致性主要有以下⼏个⽅⾯：
1.在 MySQL5.5 以及之前， slave 的 SQL 线程执⾏的 relay log 的位置只能保存在⽂件（ relay-log.info）⾥⾯，并且该⽂件默认每执⾏10000 次事务做⼀次同步到磁盘， 这意味着 slave 意外 crash 重启时， SQL 线程执⾏到的位置和数据库的数据是不⼀致的，将导致复制报错，如果不重搭复制，则有可能会导致数据不⼀致。MySQL 5.6 引⼊参数 relay_log_info_repository，将该参数设置为 TABLE 时， MySQL 将 SQL 线程执⾏到的位置存到mysql.slave_relay_log_info 表，这样更新该表的位置和 SQL 线程执⾏的⽤户事务绑定成⼀个事务，这样 slave 意外宕机后， slave 通过 innodb 的崩溃恢复可以把 SQL 线程执⾏到的位置和⽤户事务恢复到⼀致性的状态。
1. MySQL 5.6 引⼊ GTID 复制，每个 GTID 对应的事务在每个实例上⾯最多执⾏⼀次， 这极⼤地提⾼
了复制的数据⼀致性；
2. MySQL 5.5 引⼊半同步复制， ⽤户安装半同步复制插件并且开启参数后，设置超时时间，可保证在超时时间内如果 binlog 不传到 slave 上
⾯，那么⽤户提交事务时不会返回，直到超时后切成异步复制，但是如果切成异步之前⽤户线程提交时在 master 上⾯等待的时候，事务已经提交，该事务对 master上⾯的其他 session 是可见的，如果这时 master 宕机，那么到 slave 上⾯该事务⼜不可见了，该问题直到 5.7 才解决；
3. MySQL 5.7 引⼊⽆损半同步复制，引⼊参 rpl_semi_sync_master_wait_point，该参数默认为 after_sync，指的是在切成半同步之前，事
务不提交，⽽是接收到 slave 的 ACK 确认之后才提交该事务，从此，复制真正可以做到⽆损的了。
5.可以再说⼀下 5.7 的⽆损复制情况下， master 意外宕机，重启后发现有 binlog没传到 slave 上⾯，这部分 binlog 怎么办分 2 种
情况讨论， 1 宕机时已经切成异步了， 2 是宕机时还没切成异步这个怎么判断宕机时有没有切成异步呢分别怎么处理
延时性：
5.5 是单线程复制， 5.6 是多库复制（对于单库或者单表的并发操作是没⽤的）， 5.7 是真正意义的多线程复制，它的原理是基于 group commit， 只要master 上⾯的事务是 group commit 的，那 slave 上⾯也可以通过多个 worker线程去并发执⾏。和 MairaDB10.0.0.5 引⼊多线程复制的原理基本⼀样。
(4)⼯作遇到的复制 bug 的解决⽅法
5.6 的多库复制有时候⾃⼰会停⽌，我们写了⼀个脚本重新 start slave；待补充…
⼆、MySQL中myisam与innodb的区别，⾄少5点
(1)问5点不同
1.InnoDB⽀持事物，⽽MyISAM不⽀持事物
2.InnoDB⽀持⾏级锁，⽽MyISAM⽀持表级锁
3.InnoDB⽀持MVCC, ⽽MyISAM不⽀持
4.InnoDB⽀持外键，⽽MyISAM不⽀持
5.InnoDB不⽀持全⽂索引，⽽MyISAM⽀持。
6.InnoDB不能通过直接拷贝表⽂件的⽅法拷贝表到另外⼀台机器， myisam ⽀持
7.InnoDB表⽀持多种⾏格式， myisam 不⽀持
8.InnoDB是索引组织表， myisam 是堆表
(2)innodb引擎的4⼤特性
1.插⼊缓冲（insert buffer)
2.⼆次写(double write)
3.⾃适应哈希索引(ahi)
4.预读(read ahead)
(3)各种不同 mysql 版本的Innodb的改进
MySQL5.6 下 Innodb 引擎的主要改进：
1. memcached NoSQL 接⼝
4.MySQL 正常关闭时，可以 dump 出 buffer pool 的（ space， page_no），重启时 reload，加快预热速度
5.索引和表的统计信息持久化到 mysql.innodb_table_stats 和mysql.innodb_index_stats，可提供稳定的执⾏计划
6.Compressed row format ⽀持压缩表
MySQL 5.7 innodb 引擎主要改进
1.修改 varchar 字段长度有时可以使⽤ online DDL
1. Buffer pool ⽀持在线改变⼤⼩
3.Buffer pool ⽀持导出部分⽐例
4.⽀持新建 innodb tablespace，并可以在其中创建多张表
5.磁盘临时表采⽤ innodb 存储，并且存储在 innodb temp tablespace ⾥⾯，以前是 myisam 存储
6.透明表空间压缩功能
(4)2者select count()哪个更快，为什么*
myisam更快，因为myisam内部维护了⼀个计数器，可以直接调取。
(5)2 者的索引的实现⽅式
都是 B+树索引， Innodb 是索引组织表， myisam 是堆表， 索引组织表和堆表的区别要熟悉
三、MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义
(1)varchar与char的区别
在单字节字符集下， char（N） 在内部存储的时候总是定长， ⽽且没有变长字段长度列表中。在多字节字符集下⾯， char(N)如果存储的字节数超过 N，那么 char（N）将和 varchar（N）没有区别。在多字节字符集下⾯，如果存储的字节数少于 N，那么存储 N 个字节，后⾯补空格，补到 N 字节长度。都存储变长的数据和变长字段长度列表。varchar(N)⽆论是什么字节字符集，都是变长的，即都存储变长数据和变长字段长度列表
(2)varchar(50)中50的涵义
最多存放50个字符，varchar(50)和(200)存储hello所占空间⼀样，但后者在排序时会消耗更多内存，因为order by col采⽤fixed_length计算col长度(memory引擎也⼀样)。在早期 MySQL 版本中， 50 代表字节数，现在代表字符数。
(3)int（20）中20的涵义
是指显⽰字符的长度
不影响内部存储，只是影响带 zerofill 定义的 int 时，前⾯补多少个 0，易于报表展⽰
源码使用教程(4)mysql为什么这么设计
sort命令消除重复行对⼤多数应⽤没有意义，只是规定⼀些⼯具⽤来显⽰字符的个数；int(1)和int(20)存储和计算均⼀样；
四、innodb的事务与⽇志的实现⽅式
(1)有多少种⽇志
redo和undo
(2)⽇志的存放形式
redo：在页修改的时候，先写到 redo log buffer ⾥⾯， 然后写到 redo log 的⽂件系统缓存⾥⾯(fwrite)，然后再同步到磁盘⽂件（fsync）。
Undo：在 MySQL5.5 之前， undo 只能存放在 ibdata⽂件⾥⾯， 5.6 之后，可以通过设置 innodb_undo_tablespaces 参数把 undo log
html的表单提交有两种方式
存放在 ibdata之外。
(3)事务是如何通过⽇志来实现的，说得越深⼊越好
基本流程如下：
因为事务在修改页时，要先记 undo，在记 undo 之前要记 undo 的 redo， 然后修改数据页，再记数据页修改的 redo。Redo（⾥⾯包括undo 的修改） ⼀定要⽐数据页先持久化到磁盘。当事务需要回滚时，因为有 undo，可以把数据页回滚到前镜像的
状态，崩溃恢复时，如果 redo log 中事务没有对应的 commit 记录，那么需要⽤ undo把该事务的修改回滚到事务开始之前。如果有 commit 记录，就⽤ redo 前滚到该事务完成时并提交掉。
欢迎⼤家关注我的公种浩【程序员追风】，⽂章都会在⾥⾯更新，整理的资料也会放在⾥⾯。
五、MySQL binlog的⼏种⽇志录⼊格式以及区别
(1) 各种⽇志格式的涵义
1.Statement：每⼀条会修改数据的sql都会记录在binlog中。
优点：不需要记录每⼀⾏的变化，减少了binlog⽇志量，节约了IO，提⾼性能。(相⽐row能节约多少性能 与⽇志量，这个取决于应⽤的SQL情况，正常同⼀条记录修改或者插⼊row格式所产⽣的⽇志量还⼩于Statement产⽣的⽇志量，
但是考虑到如果带条 件的update操作，以及整表删除，alter表等操作，ROW格式会产⽣⼤量⽇志，因此在考虑是否使⽤ROW格式⽇志时应该跟据应⽤的实际情况，其所 产⽣的⽇志量会增加多少，以及带来的IO性能问题。)
缺点：由于记录的只是执⾏语句，为了这些语句能在slave上正确运⾏，因此还必须记录每条语句在执⾏的时候的 ⼀些相关信息，以保证所有语句能在slave得到和在master端执⾏时候相同 的结果。另外mysql 的复制,
像⼀些特定函数功能，slave可与master上要保持⼀致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined
mysql面试题基础知识functions(udf)会出现问题).
使⽤以下函数的语句也⽆法被复制：
LOAD_FILE()
UUID()
USER()
FOUND_ROWS()
SYSDATE() (除⾮启动时启⽤了 --sysdate-is-now 选项)
同时在INSERT ...SELECT 会产⽣⽐ RBR 更多的⾏级锁
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等修改数据的语句，还是会记录所有⾏的变更。
(2)适⽤场景
在⼀条 SQL 操作了多⾏数据时， statement 更节省空间， row 更占⽤空间。但是 row模式更可靠。
(3)结合第⼀个问题，每⼀种⽇志格式在复制中的优劣
Statement 可能占⽤空间会相对⼩⼀些，传送到 slave 的时间可能也短，但是没有 row模式的可靠。Row 模式在操作多⾏数据时更占⽤空间，但是可靠。
六、下MySQL数据库cpu飙升到500%的话他怎么处理？
当 cpu 飙升到 500%时，先⽤操作系统命令 top 命令观察是不是 mysqld 占⽤导致的，如果不是，出占⽤⾼的进程，并进⾏相关处理。如果是 mysqld 造成的， show processlist，看看⾥⾯跑的 session 情况，是不是有消耗资源的 sql 在运⾏。出消耗⾼的 sql，看看执⾏计划是否准确， index 是否缺失，或者实在是数据量太⼤造成。⼀般来说，肯定要 kill 掉这些线程(同时观察 cpu 使⽤率是否下降)，等进⾏相应的调整(⽐如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。也有可能是每个 sql 消耗资源并不多，但是突然之间，有⼤量的 session 连进来导致 cpu 飙升，这种情况就需要跟应⽤⼀起来分析为何连接数会激增，再做出相应的调整，⽐如说限制连接数等
七、sql优化
(1)、explain出来的各种item的意义
id：每个被独⽴执⾏的操作的标志，表⽰对象被操作的顺序。⼀般来说， id 值⼤，先被执⾏；如果 id 值相同，则顺序从上到下。
select_type：查询中每个 select ⼦句的类型。
table：名字，被操作的对象名称，通常的表名(或者别名)，但是也有其他格式。
radius of gyration
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 的需求时，也可以考虑建⽴索引来达到直接排序和汇总的需求。
⼋、备份计划，mysqldump以及xtranbackup的实现原理
(1)备份计划
视库的⼤⼩来定，⼀般来说 100G 内的库，可以考虑使⽤ mysqldump 来做，因为 mysqldump更加轻巧灵活，备份时间选在业务低峰期，可以每天进⾏都进⾏全量备份(mysqldump 备份
出来的⽂件⽐较⼩，压缩之后更⼩)。100G 以上的库，可以考虑⽤ xtranbackup 来做，备份速度明显要⽐ mysqldump 要快。⼀般是选择⼀周⼀个全备，其余每天进⾏增量备份，备份时间为业务低峰期。
(2)备份恢复时间
物理备份恢复快，逻辑备份恢复慢

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。