09-29 10:45 皖南医学院运维工程师发布于浙江

关注

Mysql 面试题

1、MySQL 的复制原理以及流程

基本原理流程，3 个线程以及之间的关联；

主：bin log 线程——记录下所有改变了数据库数据的语句，放进 master 上的 bin log 中；

从：io 线程——在使用start slave 之后，负责从master 上拉取 bin log 内容，放进自己的 relay log 中；

从：sql 执行线程——执行 relay log 中的语句；

2、MySQL 中 myisam 与 innodb 的区别,至少5 点

(1)、问 5 点不同 ;

• 1>.InnoDB 支持事物，而 MyISAM 不支持事物

• 2>.InnoDB 支持行级锁，而 MyISAM 支持表级锁

• 3>.InnoDB 支持 MVCC, 而 MyISAM 不支持

• 4>.InnoDB 支持外键，而 MyISAM 不支持

• 5>.InnoDB 不支持全文索引，而 MyISAM 支持。

(2)、innodb 引擎的 4 大特性

插入缓冲（insert buffer), 二次写(double write), 自适应哈希索引(ahi), 预读(read ahead)

(3)、2 者 selectcount(*)哪个更快,为什么

myisam 更快，因为 myisam 内部维护了一个计数器，可以直接调取。

3、MySQL 中 varchar 与char 的区别以及varchar(50)中的 50 代表的涵义

(1)、varchar 与 char 的区别

char 是一种固定长度的类型，varchar 则是一种可变长度的类型

(2)、varchar(50)中 50 的涵义

最多存放 50 个字符，varchar(50)和(200)存储 hello 所占空间一样，但后者在排序时会消耗更多内存，因为 order by col 采用 fixed_length 计算 col 长度(memory 引擎也一样)

(3)、int (20 ) 中 20 的涵义

是指显示字符的长度

但要加参数的，最大为 255，比如它是记录行数的 id, 插入 10 笔资料，它就显示

00000000001 ~~~00000000010 ，当字符的位数超过 11, 它也只显示 11 位，如果你没有加那个让它未满 11 位就前面加 0 的参数，它不会在前面加0

20 表示最大显示宽度为 20，但仍占 4 字节存储，存储范围不变；

(4)、mysql 为什么这么设计

对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和 int(20)存储和计算均一样；

4、问了 innodb 的事务与日志的实现方式

(1)、有多少种日志

错误日志：记录出错信息，也记录一些警告信息或者正确的信息。

查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。

慢查询日志：设置一个阈值，将运行时间超过该值的所有 SQL 语句都记录到慢查询的日志文件中。

二进制日志：记录对数据库执行更改的所有操作。

中继日志：中继日志也是二进制日志，用来给 slave 库恢复

事务日志：重做日志 redo 和回滚日志 undo

(2)、事物的 4 种隔离级别隔离级别

• 读未提交(RU)

• 读已提交(RC)

• 可重复读(RR) • 串行

(3)、事务是如何通过日志来实现的 ,说得越深入越好。

事务日志是通过 redo 和 innodb 的存储引擎日志缓冲（ Innodb log buffer ）来实现的，当开始一个事务的时候，会记录该事务的 lsn(log sequence number)号 ; 当事务执行时，会往

InnoDB 存储引擎的日志的日志缓存里面插入事务日志；当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过 innodb_flush_log_at_trx_commit 来控制），也就是写数据前，需要先写日志。这种方式称为“预写日志方式 ”

5、MySQL binlog 的几种日志录入格式以及区别

statement：每一条会修改数据的sql 都会记录在 bin log 中。

优点：不需要记录每一行的变化，减少了 bin log 日志量，节约了IO，提高性能。(相比 row能节约多少性能与日志量，这个取决于应用的 SQL 情况，正常同一条记录修改或者插入 row格式所产生的日志量还小于 Statement 产生的日志量，但是考虑到如果带条件的 update 操作，以及整表删除，alter表等操作，ROW 格式会产生大量日志，因此在考虑是否使用ROW 格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的 IO 性能问题。)

缺点：由于记录的只是执行语句，为了这些语句能在 slave 上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在 slave 得到和在 master端执行时候相同的结果。另外 mysql 的复制 , 像一些特定函数功能，slave 可与 master 上要保持一致会有很多相关问题(如 sleep()函数， 1454098 ，以及 user-defined functions(udf)会出现问题).

使用以下函数的语句也无法被复制：

• LOAD_FILE()

• UUID()

• USER()

• FOUND_ROWS()

• SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)

同时在INSERT …SELECT 会产生比 RBR 更多的行级锁

ROW: 不记录 sql 语句上下文相关信息，仅保存哪条记录被修改。

优点： bin log 中可以不记录执行的sql 语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以 rowlevel 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或 function ，以及 trigger 的调用和触发无法被正确复制的问题

缺点 : 所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容 , 比如一条 update 语句，修改多条记录，则 bin log 中每一条修改都会有记录，这样造成 bin log 日志量会很大，特别是当执行 alter table 之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

Mixed level: 是以上两种 level 的混合使用，一般的语句修改使用 statment 格式保存 bin log，

如一些函数，statement 无法完成主从复制的操作，则采用 row格式保存 bin log,MySQL 会根据执行的每一条具体的sql 语句来区分对待记录的日志形式，也就是在 Statement 和 Row 之间选择一种 . 新版本的 MySQL 中队 row level 模式也被做了优化，并不是所有的修改都会以

row level 来记录，像遇到表结构变更的时候就会以 statement 模式来记录。至于 update 或者 delete 等修改数据的语句，还是会记录所有行的变更。

6、MySQL 数据库cpu 飙升到 500% 的话他怎么处理?

• 1 、列出所有进程 show processlist, 观察所有进程 , 多秒没有状态变化的(干掉)

• 2 、查看超时日志或者错误日志 (做了几年开发 , 一般会是查询以及大批量的插入会导致 cpu 与 i/o 上涨 , 当然不排除网络状态突然断了 , 导致一个请求服务器只接受到一半，比如 where 子句或分页子句没有发送 ,, 当然的一次被坑经历)

7、sql 优化各种方法

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

SQL

1 select_type

表示查询中每个 select 子句的类型

SQL

1 type

表示 MySQL 在表中找到所需行的方式，又称“访问类型 ”

SQL

1 possible_keys

指出 MySQL 能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

SQL

1 key

显示 MySQL 在查询中实际使用的索引，若没有使用索引，显示为 NULL

SQL

1 key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

SQL

1 ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

SQL

1 Extra

包含不适合在其他列中显示但十分重要的额外信息

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

查询到 SQL 会执行多少时间 , 并看出 CPU/Memory 使用量 , 执行过程中 Systemlock, Table lock 花多少时间等等

8、备份计划, mysqldump 以及xtranbackup 的实现原理

(1)、备份计划 ;

这里每个公司都不一样，您别说那种 1 小时 1 全备什么的就行

(2)、备份恢复时间 ;

这里跟机器，尤其是硬盘的速率有关系，以下列举几个仅供参考

20G 的 2 分钟（ mysqldump）

80G 的 30 分钟(mysqldump)

111G 的 30 分钟（ mysqldump)

288G 的 3 小时（ xtra) 3T 的 4 小时（ xtra)

逻辑导入时间一般是备份时间的 5 倍以上

(3)、xtrabackup 实现原理

在 InnoDB 内部会维护一个 redo 日志文件，我们也可以叫做事务日志文件。事务日志会存储每一个 InnoDB 表数据的记录修改。当 InnoDB 启动时，InnoDB 会检查数据文件和事务日志，并

执行两个步骤：它应用（前滚）已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。

9、mysqldump 中备份出来的 sql ,如果我想sql 文件中 ,一行只有一个

insert….value()的话,怎么办?如果备份需要带上 master 的复制点信息怎么办?

SQL

1 --skip-extended-insert

3 [root@helei-zhuanshu ~]# mysqldump -uroot -p helei --skip-extended-ins

ert

5 Enter password: 6

7 KEY `idx_c1` (`c1`), 8

9 KEY `idx_c2` (`c2`) 10

11 ) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1;

13 /*!40101 SET character_set_client = @saved_cs_client */;

15 --

17 -- Dumping data for table `helei`

19 --

21 LOCK TABLES `helei` WRITE;

23 /*!40000 ALTER TABLE `helei` DISABLE KEYS */;

25 INSERT INTO `helei` VALUES (1,32,37,38, '2016-10-18 06:19:24', 'sususus

ususususususususu');

27 INSERT INTO `helei` VALUES (2,37,46,21, '2016-10-18 06:19:24', 'sususus

ususu');

29 INSERT INTO `helei` VALUES (3,21,5,14, '2016-10-18 06:19:24', 'susu');

10、500 台 db ,在最快时间之内重启

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

11、innodb 的读写参数优化

(1)、读取参数

SQL

1 global buffer pool以及 local buffer ；

(2)、写入参数 ;

SQL

1 innodb_flush_log_at_trx_commit 2

3 innodb_buffer_pool_size

(3)、与 IO 相关的参数 ;

SQL

1 innodb_write_io_threads = 8 2

3 innodb_read_io_threads = 8 4

5 innodb_thread_concurrency = 0

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

SQL

1 query cache/query_cache_type

并不是所有表都适合使用query cache 。造成 query cache 失效的原因主要是相应的 table 发生了变更

第一个：读操作多的话看看比例，简单来说，如果是用户清单表，或者说是数据比例比较固定，比如说商品列表，是可以打开的，前提是这些库比较集中，数据库中的实务比较小。

第二个：我们“行骗 ”的时候，比如说我们竞标的时候压测，把 query cache 打开，还是能收到 qps 激增的效果，当然前提示前端的连接池什么的都配置一样。大部分情况下如果写入的居多，访问量并不多，那么就不要打开，例如社交网站的， 10% 的人产生内容，其余的 90% 都在消费，打开还是效果很好的，但是你如果是 qq 消息，或者聊天，那就很要命。

第三个：小网站或者没有高并发的无所谓，高并发下，会看到很多 qcache 锁等待，所以一般高并发下，不建议打开 query cache

12、你是如何监控你们的数据库的?你们的慢日志都是怎么查询的?

监控的工具有很多，例如 zabbix ，lepus ，我这里用的是 lepus

13、你是否做过主从一致性校验 ,如果有 ,怎么做的 ,如果没有 ,你打算怎

么做?

主从一致性校验有多种工具例如 checksum 、mysqldiff 、pt-table-checksum 等

14、你们数据库是否支持emoji 表情,如果不支持,如何操作?

如果是 utf8 字符集的话，需要升级至 utf8_mb4 方可支持

15、你是如何维护数据库的数据字典的?

这个大家维护的方法都不同，我一般是直接在生产库进行注释，利用工具导出成 excel 方便流通。

16、表中有大字段X(例如 : text 类型) ,且字段X 不会经常更新,以读为为

主 ,请问

拆带来的问题：连接消耗 + 存储拆分空间；不拆可能带来的问题：查询性能；

• 1 、如果能容忍拆分带来的空间问题 , 拆的话最好和经常要查询的表的主键在物理结构上放置在一起(分区) 顺序 IO, 减少连接消耗 , 最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗

• 2 、如果能容忍不拆分带来的查询性能损失的话 : 上面的方案在某个极致条件下肯定会出现问题 , 那么不拆就是最好的选择

17、MySQL 中 InnoDB 引擎的行锁是通过加在什么上完成(或称实现)的?

为什么是这样子的?

InnoDB 是基于索引来完成行锁

例 : select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定 , 并且 id 是有索引键的列 ,

如果 id 不是索引键那么 InnoDB 将完成表锁 ,, 并发将无从谈起

18、开放性问题 :据说是腾讯的

一个 6 亿的表 a，一个 3 亿的表 b，通过外间 tid 关联，你如何最快的查询出满足条件的第 50000 到第 50200 中的这 200 条数据记录。

• 1 、如果 A 表 TID 是自增长 , 并且是连续的 ,B 表的 ID 为索引

SQL

1 select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

• 2 、如果 A 表的 TID 不是连续的 , 那么就需要使用覆盖索引 .TID 要么是主键 , 要么是辅助索引 ,B 表 ID 也需要有索引。

SQL

1 select * from b , (select tid from a limit 50000,200) a where b.id =

a .tid;

19、什么是存储过程?有哪些优缺点?

存储过程是一些预编译的 SQL 语句。

• 1 、更加直白的理解：存储过程可以说是一个记录集，它是由一些 T-SQL 语句组成的代码块，这些 T-SQL 语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

• 2 、存储过程是一个预编译的代码块，执行效率比较高 , 一个存储过程替代大量 T_SQL 语句 , 可以降低网络通信量，提高通信速率 , 可以一定程度上确保数据安全

20、索引是什么?有什么作用以及优缺点?

• 1 、索引是对数据库表中一或多个列的值进行排序的结构，是帮助 MySQL 高效获取数据的数据结构

• 2 、索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

MySQL 数据库几个基本的索引类型：普通索引、唯一索引、主键索引、全文索引

• 1 、索引加快数据库的检索速度

• 2 、索引降低了插入、删除、修改等维护任务的速度

• 3 、唯一索引可以确保每一行数据的唯一性

• 4 、通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能

• 5 、索引需要占物理和数据空间

21、什么是事务?

事务（ Transaction ）是并发控制的基本单位。所谓的事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。事务是数据库维护数据一致性的单

位，在每个事务结束时，都能保持数据一致性。

22、使用索引查询一定能提高查询的性能吗?为什么

通常 , 通过索引查询数据比全表扫描要快 . 但是我们也必须注意到它的代价 .

• 1 、索引需要空间来存储 , 也需要定期维护 , 每当有记录在表中增减或索引列被修改时 , 索引本身也会被修改 . 这意味着每条记录的 INSERT,DELETE,UPDATE 将为此多付出 4,5 次的磁盘 I/O. 因为索引需要额外的存储空间和处理 , 那些不必要的索引反而会使查询反应时间变慢 . 使用索引查询不一定能提高查询性能 , 索引范围查询(INDEX RANGE SCAN)适用于两种情况 :

• 2 、基于一个范围的检索 , 一般查询返回结果集小于表中记录数的30%

• 3 、基于非唯一性索引的检索

23、简单说一说drop、delete 与truncate 的区

SQL 中的 drop 、delete 、truncate 都表示删除，但是三者有一些差别

• 1 、delete 和 truncate 只删除表的数据不删除表的结构

• 2 、速度 , 一般来说 : drop> truncate >delete

• 3 、delete 语句是 dml, 这个操作会放到 rollback segement 中 , 事务提交之后才生效 ;

• 4 、如果有相应的 trigger, 执行的时候将被触发 . truncate,drop 是 ddl, 操作立即生效 , 原数据不放到 rollback segment 中 , 不能回滚 . 操作不触发 trigger.

24、drop、delete 与truncate 分别在什么场景之下使用?

• 1 、不再需要一张表的时候，用 drop

• 2 、想删除部分数据行时候，用 delete，并且带上 where 子句

• 3 、保留表而删除所有数据的时候用truncate