MySQL夺命15问

原文来源于：程序员恰恰

如有侵权，联系删除

前言

MySQL在面试中经常被问到，本文总结了面试中的经典问题

1. 数据库三大范式是什么？

第一范式：每个列都不可以再拆分。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。

比如性能。事实上我们经常会为了性能而妥协数据库的设计。

2. mysql有关权限的表都有哪几个?

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。

这些权限表分别user，db，table_priv，columns_priv和host。

user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。

db权限表：记录各个帐号在各个数据库上的操作权限。

table_priv权限表：记录数据表级的操作权限。

columns_priv权限表：记录数据列级的操作权限。

host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

3. 事务的四大特性(ACID)介绍一下?

原子性：事务是最小的执行单位，不允许分割。

事务的原子性确保动作要么全部完成，要么完全不起作用；

一致性：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；

隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，

各并发事务之间数据库是独立的；

持久性：一个事务被提交之后。它对数据库中数据的改变是持久的， 即使数据库发生故障也不应该对其有任何影响。

4.  SQL语句主要分为哪几类?

数据定义语言DDL（Data Ddefinition Language）CREATE，DROP，ALTER

主要为以上操作 即对逻辑结构等有操作的，其中包括表结构，视图和索引。

数据查询语言DQL（Data Query Language）SELECT

这个较为好理解 即查询操作，以select关键字。

各种简单查询，连接查询等 都属于DQL。

数据操纵语言DML（Data Manipulation Language）INSERT，UPDATE，DELETE

主要为以上操作 即对数据进行操作的，

对应上面所说的查询操作 DQL与DML共同构建了多数初级程序员常用的增删改查操作。

而查询是较为特殊的一种 被划分到DQL中。

数据控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK

主要为以上操作 即对数据库安全性完整性等有操作的，可以简单的理解为权限控制等。

5. MySQL分库分表的目的是？

分库分表就是为了 解决由于数据量过大而导致数据库性能降低的问题， 将原来独立的数据库拆分成若干数据库组成，将数据大表拆分成若干数据表组成， 使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。

分库分表常用的中间件如下：

6. 什么是死锁？怎么解决？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

常见的解决死锁的方法

1. 如果不同程序会并发存取多个表， 尽量约定以相同的顺序访问表，可以大大降低死锁机会。

2. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3. 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

如果业务处理不好可以用分布式事务锁或者使用乐观锁

7.  什么是脏读？幻读？不可重复读？

脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据， 由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致， 这可能是两次查询过程中间插入了一个事务更新的原有的数据。

幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致， 例如有一个事务查询了几列(Row)数据， 而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中， 就会发现有几列数据是它先前所没有的。

8.  视图有哪些特点？

视图的特点如下: 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。

视图是由基本表(实表)产生的表(虚表)。视图的建立和删除不影响基本表。

对视图内容的更新(添加，删除和修改)直接影响基本表。

当视图来自多个基本表时，不允许添加和删除数据。

视图的操作包括创建视图，查看视图，删除视图和修改视图。

9. SQL的生命周期？

1. 应用服务器与数据库服务器建立一个连接

2. 数据库进程拿到请求sql

3. 解析并生成执行计划，执行

4. 读取数据到内存并进行逻辑处理

5. 通过步骤一的连接，发送结果到客户端

6. 关掉连接，释放资源

10. 主键使用自增ID还是UUID？

推荐使用自增ID，不要使用UUID。

因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说， 主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序)， 如果主键索引是自增ID，那么只需要不断向后排列即可，如果是UUID， 由于到来的ID与原来的大小不确定，会造成非常多的数据插入，数据移动，然后导致产生很多的内存碎片，进而造成插入性能的下降。

总之，在数据量大一些的情况下，用自增主键性能会好一些。

关于主键是聚簇索引，如果没有主键，InnoDB会选择一个唯一键来作为聚簇索引，如果没有唯一键，会生成一个隐式的主键。

11. MySQL数据库cpu飙升到100%的话怎么处理？

当 cpu 飙升到 100%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的。

如果不是，找出占用高的进程，并进行相关处理。

如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确，index 是否缺失，或者实在是数据量太大造成。

一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)， 等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。

也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升， 这种情况就需要跟应用一起来分析为何连接数会激增， 再做出相应的调整，比如说限制连接数等。

12. MySQL主从复制解决了哪些问题？

主从复制的作用是：

主数据库出现问题，可以切换到从数据库。可以进行数据库层面的读写分离。可以在从数据库上进行日常备份。

数据分布：随意开始或停止复制，并在不同地理位置分布数据备份

负载均衡：降低单个服务器的压力

高可用和故障切换：帮助应用程序避免单点失败

升级测试：可以用更高版本的MySQL作为从库

13. 什么是MySQL的GTID？

TID（Global Transaction ID，全局事务ID）是全局事务标识符， 是一个已提交事务的编号，并且是一个全局唯一的编号。

GTID是从MySQL 5.6版本开始在主从复制方面推出的重量级特性。

GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。

GTID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。

GTID有如下几点作用：

1. 根据GTID可以知道事务最初是在哪个实例上提交的。

2. GTID的存在方便了Replication的Failover。因为不用像传统模式复制那样去找master_log_file和master_log_pos。

3. 基于GTID搭建主从复制更加简单， 确保每个事务只会被执行一次。

14. MySQL常用的备份工具有哪些？

常用备份工具mysql复制

逻辑备份(mysqldump，mydumper)

物理备份(copy,xtrabackup)

备份工具差异对比：

1. mysql复制相对于其他的备份来说，得到的备份数据比较实时。

2. 逻辑备份：分表比较容易。mysqldump备份数据时是将所有sql语句整合在同一个文件中；mydumper备份数据时是将SQL语句按照表拆分成单个的sql文件， 每个sql文件对应一个完整的表。

3. 物理备份：拷贝即可用，速度快。

copy:直接拷贝文件到数据目录下，可能引起表损坏或者数据不一致。

xtrabackup对于innodb表是不需要锁表的，对于myisam表仍然需要锁表。

15. MySQL备份计划如何制定

视库的大小来定，一般来说 100G 内的库，可以考虑使用 mysqldump 来做， 因为 mysqldump更加轻巧灵活，备份时间选在业务低峰期， 可以每天进行都进行全量备份(mysqldump 备份出来的文件比较小，压缩之后更小)。

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

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