[面试题]25个MySQL经典面试题「建议收藏」

大家好，又见面了，我是你们的朋友全栈君。

1、MySQL的复制原理及流程

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

1. 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
2. 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中；
3. 从：sql执行线程——执行relay log中的语句；

2、myisam与innodb区别，至少5点

2.1)、5点不同；

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

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

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

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

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

2.2)、innodb引擎4大特性

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

2.3)、2者selectcount(*)哪个更快，为什么

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

3、varchar与char区别及varchar(50)的50的涵义

3.1)、varchar与char的区别

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

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

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

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

是指显示字符的长度 但要加参数的，最大为255，比如它是记录行数的id,插入10笔资料，它就显示00000000001 ~~~00000000010，当字符的位数超过11,它也只显示11位，如果你没有加那个让它未满11位就前面加0的参数，它不会在前面加0 20表示最大显示宽度为20，但仍占4字节存储，存储范围不变；

3.4)、mysql为什么这么设计

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

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

4.1)、有多少种日志；

错误日志：记录出错信息，也记录一些警告信息或者正确的信息。 查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。 慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。 二进制日志：记录对数据库执行更改的所有操作。 中继日志： 事务日志：

4.2)、事务的4种隔离级别

隔离级别 读未提交(RU) 读已提交(RC) 可重复读(RR) 串行

4.3)、事务是如何通过日志实现的。

事务日志通过redo和innodb的存储引擎日志缓冲（Innodb log buffer）实现，当开始一个事务时，会记录该事务的lsn(log sequence number)号; 当事务执行时，往InnoDB引擎的日志缓存里插入事务日志；当事务提交时再将日志缓冲写盘（通过innodb_flush_log_at_trx_commit控制），也就是写数据前，需先写日志。这种方式称为“预写日志方式”

5、binlog的几种日志格式及区别

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 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等修改数据的语句，还是会记录所有行的 变更。

6、MySQL数据库cpu飙升到500%怎么处理？

没经验的，可不问；有经验的，问处理思路。 列出所有进程 show processlist 观察所有进程 多秒没有状态变化的(干掉) 查看超时日志或者错误日志 (做了几年开发,一般是查询及大批量插入会导致cpu与i/o上涨,当然不排除网络状态断了,导致一个请求服务器只接受到一半，如where子句或分页子句没发送)

7、sql优化

7.1)、explain各种item的意义；

select_type ：表示查询中每个select子句的类型 type ：表示MySQL在表中找到所需行的方式，又称“访问类型” possible_keys ：指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用 key ：显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度 ref ：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值 Extra ：包含不适合在其他列中显示但十分重要的额外信息

7.2)、profile的意义及使用场景；

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

8、备份计划，mysqldump及xtranbackup实现原理

8.1)、备份计划；

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

8.2)、备份恢复时间；

这里跟机器，尤其是硬盘的速率有关系，以下列举几个仅供参考 20G的2分钟（mysqldump） 80G的30分钟(mysqldump) 111G的30分钟（mysqldump) 288G的3小时（xtra) 3T的4小时（xtra) 逻辑导入时间一般是备份时间的5倍以上

8.3)、xtrabackup实现原理

在InnoDB内部会维护个redo日志文件，也叫事务日志文件。事务日志会存储每个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据文件和事务日志，并执行两个步骤：它应用（前滚）已提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。

9、mysqldump备份出的sql文件中一行只有一个insert….value()的话，怎么办？如果备份需带master的复制点信息怎么办？

--skip-extended-insert
[root@helei-zhuanshu ~]# mysqldump -uroot -p helei --skip-extended-insert
Enter password:
  KEY `idx_c1` (`c1`),
  KEY `idx_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `helei`
--

LOCK TABLES `helei` WRITE;
/*!40000 ALTER TABLE `helei` DISABLE KEYS */;
INSERT INTO `helei` VALUES (1,32,37,38,'2016-10-18 06:19:24','susususususususususususu');
INSERT INTO `helei` VALUES (2,37,46,21,'2016-10-18 06:19:24','susususususu');
INSERT INTO `helei` VALUES (3,21,5,14,'2016-10-18 06:19:24','susu');

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

puppet，dsh

11、innodb读写参数优化

11.1)、读取参数

global buffer pool以及 local buffer；

11.2)、写入参数；

innodb_flush_log_at_trx_commit
innodb_buffer_pool_size

11.3)、与IO相关的参数；

innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 0

11.4)、缓存参数及缓存的适用场景。

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、是否有开发规范，如果有，如何执行

有，开发规范网上有很多了，可自己看看总结下

17、表中有大字段X(例如：text类型)，且字段X不常更新，以读为主，请问

(1)、您是选择拆成子表，还是继续放一起； (2)、写出您这样选择的理由。 答：拆带来的问题：连接消耗 + 存储拆分空间；不拆可能带来的问题：查询性能； 如果能容忍拆分带来的空间问题,拆的话最好和经常要查询的表的主键在物理结构上放置在一起(分区) 顺序IO,减少连接消耗,最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗 如果能容忍不拆分带来的查询性能损失的话:上面的方案在某个极致条件下肯定会出现问题,那么不拆就是最好的选择

18、MySQL中InnoDB引擎的行锁是通过加在什么上完成(或称实现)的？为什么这样？

答：InnoDB是基于索引来完成行锁 例: select * from tab_with_index where id = 1 for update; for update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列, 如果 id 不是索引键那么InnoDB将完成表锁,并发将无从谈起

19、如何从mysqldump产生的全库备份中只恢复某个库、某张表？

答案见：http://suifu.blog.51cto.com/9167728/1830651

20、开放性问题：据说是腾讯的

一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。 1、如果A表TID是自增长,并且是连续的,B表的ID为索引

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

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

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

21、MySQL 在线变更表结构方案

典型问题：目前可选的在线变更表结构方案有哪些？ 在线变更表结构方案如下：

• 直接 ALTER TABLE
• oak-online-alter-table，参考：http://shlomi-noach.github.io/openarkkit/oak-online-alter-table.html
• 5.7 新增 online rename index，参考：https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html
• pt-online-schema-change，参考：https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html
• gh-ost，参考：https://github.com/github/gh-ost
• OnlineSchemaChange，参考：https://github.com/facebookincubator/OnlineSchemaChange
• LHM，参考：https://github.com/soundcloud/lhm
• 腾讯 GSC 引擎
• 自定义脚本

22、MySQL 如何对大数据量的表自动分库分表存储

典型问题：当一个表预期数据量足够大时，如何分库分表存储，读写分离，来实现高效、稳定的数据存储和读。

分库分表分区是解决大数据量时的一个分而治之的思路，建议依次考虑的顺序如下

1.分区：表分区之后只是引擎存储的工作去保证，对用户相对透明，因为对应用侵入度较低； 2.分表：在同一个 schema 中的多个表，应用可能需要根据业务逻辑去判断业务对应的表，这种情况下单库内路由也相对比较好办； 3.分库：这个方法最大的问题就是分布式事务，目前市场有很多开源中间件可以选择，如当当或者 360 的，但未必能够满足需求，需要进行选择。

可考虑为什么出现大数据量？如从生命周期角度考虑，对于大数据，是否可分为热、温和冷三种类型呢？如存在，那么：

1）冷数据（历史数据）：是否就可以从现行数据表中进行定期剥离呢？比如交易记录，后续只是进行查询，完全可以将完全交易的数据进行定期转存到历史库 2）温数据：对于访问频度相对低一点的数据，如果考虑存储成本，是否可以采用分区的形式将这些数据放在相对廉价的存储上面 3）热数据：对于频繁访问的数据，一般是整个系统的性能瓶颈点，是否可以考虑 SSD 的硬盘，这样能保证既有业务的快速响应

对于数据生命周期管理还需考虑业务实际场景：

当数据量比较大时落地实现的所有功能都交给数据库吗？作为架构设计中的业务架构、应用架构、技术架构、数据架构和部署运行架构中的架构之一，应是与其他架构设计逻辑整合的一起的，需应用人员和业务人员参与，有部分功能为了保障数据库整体性能需要提升到应用逻辑中去完成，可更好的提升数据库性能，在实战中的一些经验，如不用存储过程、不用外键、不用复杂表操作，尽量单表操作，这些不是不做，而是数据库不做，约束交给应用去做，这样应用在从数据库得到快速响应后在应用层面进行逻辑处理，而这种处理的服务器一般可较好的扩展，提高响应能力。

23、MySQL 单表量级达到 5 千万以上，如何添加修改字段而不产生锁表？

典型问题： 1、MySQL 如果单实例，没有主从 单库 ，单表量级达到 5 千万以上，该表的插入和查询都慢很多, 如何添加修改字段而不产生锁表？

2、如有主从，表的量级达到千万以上，如何修改添加表字段？是先从库添加完再由从库变更为主库去用，让其在同步么？

1、单实例情况，建议用 pt 或 gh-ost 工具，二者均不会产生锁表，前者通过触发器实现，或通过解析 binlog 实现。

2、多实例情况，如果用 pt 工具，是在主库做变更；如果用 gh-ost 工具，主从均可操作。

24、生产环境下，变更 MySQL 表结构步骤是什么？

1. 确认表的元数据信息，包括：字段类型、数据量、存储引擎
2. 对需求里的改表语句进行审核，如果存在不规范的地方，联系开发进行修正
3. 确认当前节点是什么角色，也就是主节点和从节点
4. 确认主从状态是否正常
5. 根据数据量、业务场景、业务容忍度，选择变更的方案以及预估需要的时间
6. 确定操作时间点，如果数据量大，建议在低峰进行
7. 通知研发开始进行变更操作，告知研发观察对应业务
8. 变更过程中，留意 MySQL 监控和机器监控，观察主从状态、主从连接数、主从机器负载
9. 变更过程出现问题，及时 KILL 相关操作
10. 变更顺利完成，进行数据校验
11. 告知研发

25、MySQL 建表的最佳实践是什么？

简单列下：

• 表名跟业务绑定，表名使用小写字母和下划线命名
• 除存放日志的表和中间临时表外，其他表原则上必须有主键
• 创建表必须包含行记录的创建时间字段和修改时间字段
• 优先选择存储引擎类型为 InnoDB
• 表和字段必须有 Comment 注释
• 字符集优先选择 UTF-8
• 根据数据尺寸决定数据长度，尽量减少冗余
• 组合索引不能超过5列，最好保持在 3 列以内
• 组合索引最常使用的字段或区分度高的字段考虑放在索引第一列
• 索引不宜太多，维护索引也需要成本，单表索引数量建议不超过 5 个
• 尽量避免使用触发器、存储过程、自定义函数(UDF)、视图
• 预估容量，是否需要使用分区表，是否需要分表分库
• 所有字段建议设置默认值，INT 为 0，VARCHAR 为 ‘’

26、MySQL 表空间设置个和优化策略主要有哪些？

1. innodb_file_per_table 参数设置为 ON
2. 定期执行 OPTIMIZE TABLE tableName;
3. 定期执行 ALTER TABLE tableName ENGINE = InnoDB;

注意：第 2、3 条是高危操作，会影响业务，建议在低峰期操作

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/175607.html原文链接：https://javaforall.cn