MySQL 面试汇总

一、MyISAM 与 InnoDB(5.5) 的区别

MyISAM 性能极佳，提供了大量的特性，包括全文索引、压缩、空间函数等，但不支持事务和行级锁，最大的缺陷就是崩溃后无法安全恢复。不过，5.5之后 InnoDB(事务性数据库引擎)成为 MySQL默认的存储引擎。MyISAM 适合 select 密集型的表，而 InnoDB 适合 insert 和 update 密集型的表。两者对比：

1️⃣InnoDB 支持事务，MyISAM 不支持事务。InnoDB 的 AUTOCOMMIT 默认打开，即每条SQL语句会默认被封装成一个事务自动提交，这样会影响速度，最好把多条语句显示放在 begin 和 commit 之间，组成一个事务去提交
2️⃣InnoDB 支持 MVCC(数据库的多版本并发控制)，MyISAM 不支持。应对高并发事务，MVCC 比单纯的加锁更高效；MVCC 只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。MVCC 可以使用乐观锁和悲观锁实现。各数据库 MVCC 的实现并不统一。
3️⃣InnoDB 支持行级锁(row-level locking)(默认)和表级锁。MyISAM 只支持表级锁(table-level locking)，读取时对需要读到的所有表加锁，写入时则对表加排它锁。MyISAM 同一个表上的读写互斥，并发时等待队列读写并存的情况下，默认写请求的优先级高，即使读请求先到，所以不适合大量查询和修改并存的情况，那样查询进程会长时间阻塞。因为 MyISAM 是锁表，所以某项比较耗时的读操作会使其他写进程饿死。
4️⃣InnoDB 支持外键，而 MyISAM 不支持。
5️⃣InnoDB 的主键范围更大，最大是 MyISAM 的 2 倍。
6️⃣InnoDB 不支持全文索引，而 MyISAM 支持。全文索引是指对 char、varchar 和 text 中的每个词(停用词除外)建立倒排序索引。MyISAM 的全文索引其实没啥用，因为它不支持中文分词，必须由使用者分词后加入空格再写到数据表里，而且少于 4 个汉字的词会和停用词一样被忽略掉。MyISAM 支持 BLOB 和 TEXT 的前 500 个字符索引。
7️⃣MyISAM 支持 GIS 数据，InnoDB 不支持。即 MyISAM 支持以下空间数据对象：Point、Line、Polygon 和 Surface 等。
8️⃣没有 where 的 count(*)，MyISAM 比 InnoDB 快得多。因为 MyISAM 内置了一个计数器，count(*) 时它直接从计数器中读，而 InnoDB 必须扫描全表。所以在 InnoDB 上执行 count(*) 时一般要伴随 where，且 where 中要包含主键以外的索引列。为什么“主键以外”？因为 InnoDB 中 primary index 是和 raw data 存放在一起的，而 secondary index 则是单独存放，然后有个指针指向 primary key。所以只是 count(*) 的话使用 secondary index 扫描更快，而 primary key 则主要在扫描索引同时要返回 raw data 时的作用较大。
9️⃣InnoDB支持崩溃后的安全恢复。MyISAM 不支持。

补充：

1. InnoDB 引擎的4大特性：
   插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(ahi)、预读(read ahead)
2. MyISAM 支持延迟更新索引，极大提升写入性能。对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用。

二、数据库三范式

1. 第一范式(1NF)：数据库表中的字段都是单一属性，不可再分的。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
2. 第二范式(2NF)：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖(即不存在组合关键字中的某些字段决定非关键字段的情况)，也即所有非关键字段都完全依赖于任意一组候选关键字。
3. 第三范式(3NF)：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在“A→B→C”的决定关系，则 C 传递函数依赖于 A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段 x → 非关键字段y

三、MySQL 中的 varchar(变长) 与 char(定长)

1️⃣varchar(可变长度) 与 char(固定长度) 的区别
尽可能的使用 varchar 代替 char，因为首先变长字段存储空间小，节省存储空间。其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
2️⃣varchar(50) 中 50 的含义
最多存放 50 个字符。varchar(50) 和 varchar(200) 存储 hello 所占空间一样，但后者在排序时会消耗更多内存，因为 order by col 采用 fixed_length 计算 col 长度(memory引擎也一样)。varchar(50) 这里的 50 限制的是储存字符的个数，字符不分贵贱(不分中文、英文、数字…)。
3️⃣int(20)中 20 的含义
显示字符的长度。但要加参数的，最大为 255，比如它是记录行数的 id，插入 10 笔资料，它就显示 00000000001 ~~ 00000000010。当字符的位数超过 20，它也只显示 20 位，如果没有加那个让它未满 20 位就前面加 0 的参数，它不会在前面加 0。20 表示最大显示宽度为 20，但仍占 4 字节存储，存储范围不变。MySQL 这么设计对大多数应用没有意义，只是规定一些工具用来显示字符的个数。int(1) 和 int(20) 存储和计算均一样。

四、500 台 DB，在最快时间之内重启

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

五、 MySQL 时间类型 datetime、bigint 及 timestamp 的查询效率

1️⃣sql查询速率：InnoDB，通过时间范围查找，性能bigint > datetime > timestamp
2️⃣sql分组速率：InnoDB，通过时间分组，性能timestamp > datetime，但是相差不大
3️⃣sql排序速率：InnoDB，通过时间排序，性能bigint > timestamp > datetime

如果需要对时间字段进行操作(如通过时间范围查找或者排序等)，推荐使用bigint；如果时间字段不需要进行任何操作，推荐使用timestamp，使用4个字节保存比较节省空间，但是只能记录到2038年记录的时间有限。

六、超键、候选键、主键、外键分别是什么？

1. 超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
2. 候选键：是最小超键，即没有冗余元素的超键。
3. 主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值(Null)。
4. 外键：在一个表中存在的另一个表的主键称此表的外键。

七、Innodb 的事务与日志的实现方式

1️⃣事务的四种隔离级别

读未提交(RU)
读已提交(RC)
可重复读(RR)
串行

2️⃣日志分类：

1. 错误日志：记录出错信息，也记录一些警告信息或者正确的信息。
2. 查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。
3. 慢查询日志：设置一个阈值，将运行时间超过该值的所有 SQL 语句都记录到慢查询的日志文件中。
4. 二进制日志：记录对数据库执行更改的所有操作。
5. 中继日志：中继日志也是二进制日志，用来给 slave 库恢复
6. 事务日志：重做日志 redo 和回滚日志 undo

3️⃣事务是如何通过日志来实现的，说得越深入越好。

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

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

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

1️⃣列出所有进程 show processlist，观察所有进程，多秒没有状态变化的(干掉)
2️⃣查看超时日志或者错误日志(一般是查询以及大批量的插入会导致cpu与i/o上涨，当然不排除网络突然中断，导致一个请求服务器只接受到一半，比如 where 子句或分页子句没有发送)

十、Innodb 的读写参数优化

1️⃣读取参数：
global buffer pool以及 local buffer；
2️⃣写入参数：
innodb_flush_log_at_trx_commit
innodb_buffer_pool_size
3️⃣与IO相关的参数：
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 0
4️⃣缓存参数以及缓存的适用场景。
query cache/query_cache_type
并不是所有表都适合使用query cache。造成query cache失效的原因主要是相应的table发生了变更

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

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

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

十一、如何监控数据库？慢日志是怎么查询的？

监控的工具有很多，例如zabbix，lepus

十二、怎么做主从一致性校验

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

十三、数据库如何支持emoji表情？

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

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

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

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

十五、索引查询不一定能提高查询性能

通常，通过索引查询数据比全表扫描要快。但是也必须注意到它的代价。索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改，这意味着每条记录的 INSERT、DELETE、UPDATE 将为此多付出 4、5 次的磁盘 I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况：
①基于一个范围的检索，一般查询返回结果集小于表中记录数的30%。
②基于非唯一性索引的检索。

十六、什么是视图？以及视图的使用场景有哪些？

①视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。
②只暴露部分字段给访问者，所以就建一个虚表，就是视图。
③查询的数据来源于不同的表，而查询者希望以统一的方式查询，这样也可以建立一个视图，把多个表查询结果联合起来，查询者只需要直接从视图中获取数据，不必考虑数据来源于不同表所带来的差异

十七、MySQL 的复制原理以及流程

1. 在 Slave 服务器上执行 start slave 命令开启主从复制开关，开始进行主从复制。

2. 此时，Slave 服务器的 IO 线程会通过在 master 上已经授权的复制用户权限请求连接 master 服务器，并请求从执行 binlog 日志文件的指定位置(日志文件名和位置就是在配置主从复制服务时执行change master命令指定的)之后开始发送 binlog 日志内容。

3. 根据 Slave 服务器的 IO 线程请求的信息分批读取指定 binlog 日志文件指定位置之后的 binlog 日志信息，然后返回给 Slave 端的 IO 线程。返回的信息中除了 binlog 日志内容外，还有在 master 服务器端记录的新的 binlog 文件名称，以及在新的 binlog 中的下一个指定更新位置。

4. 当 Slave 服务器的 IO 线程获取到 Master 服务器上 IO 线程发送的日志内容、日志文件及位置点后，会将 binlog 日志内容依次写到 Slave 端自身的 Relay Log(即中继日志)文件的最末端，并将新的 binlog 文件名和位置记录到 master-info 文件中，以便下一次读取 master 端新 binlog 日志时能告诉Master服务器从新 binlog 日志的指定文件及位置开始读取新的 binlog 日志内容。

5. Slave 服务器端的 SQL 线程会实时检测本地 Relay Log 中 IO 线程新增的日志内容，然后及时把 Relay LOG 文件中的内容解析成 sql 语句，并在自身 Slave 服务器上按解析 SQL 语句的位置顺序执行应用这样 sql 语句，并在 relay-log.info 中记录当前应用中继日志的文件名和位置点。

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

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

十八、常考 sql 书写

1️⃣查出销量前十的记录：
Oracle

过度设计：
select id,col from(
select rownum rn,uo.* from (select * from tab order by sales desc) uo
where rownum<=10 ) ua;
最优：
select * from table order by sales desc where rownum<=10;  

MySQL

select * from table order by sales desc limit 10;  

2️⃣查找表中多余的重复记录【重复记录根据字段(UserId)判断】

select * from table
where UserId in
(select UserId from tab group by UserId having count(UserId) > 1)

3️⃣删除表中多余的重复记录【重复记录根据字段(UserId)判断】只留有rowid最小的记录

delete from tab
where UserId in 
(select UserId from table group  by  UserId having  count(UserId) > 1)
and rowid not in 
(select min(rowid) from  table group by UserId having count(UserId)>1)

4️⃣查找表中多余的重复记录(多个字段)

select * from tab a
where (a.UserId,a.seq) in  
(select UserId,seq from tab group by UserId,seq  having count(*) > 1)

5️⃣删除表中多余的重复记录(多个字段)，只留有rowid最小的记录

delete from tab a
where (a.UserId,a.seq) in  
(select UserId,seq from tab group by UserId,seq having count(*) > 1)
and rowid not in 
(select min(rowid) from tab group by UserId,seq having count(*)>1)

6️⃣查找表中多余的重复记录(多个字段)，不包含rowid最小的记录

select * from tab a
where (a.UserId,a.seq) in
(select UserId,seq from tab group by UserId,seq having count(*) > 1)
and rowid not in
(select min(rowid) from tab group by UserId,seq having count(*)>1)