MySQL知识点

一、数据库三大范式

1.1 第一范式1NF

第一范式就是属性不可分割，每个字段都应该是不可再拆分的。

比如一个字段是姓名（NAME），在国内的话通常理解都是姓名是一个不可再拆分的单位，这时候就符合第一范式。但是在国外的话还要分为FIRST NAME和LAST NAME，这时候姓名这个字段就是还可以拆分为更小的单位的字段，就不符合第一范式了。

1.2 第二范式2NF

第二范式就是要求表中要有主键，表中其他其他字段都依赖于主键，因此第二范式只要记住主键约束就好了。

比如说有一个表是学生表，学生表中有一个值唯一的字段学号，那么学生表中的其他所有字段都可以根据这个学号字段去获取，依赖主键的意思也就是相关的意思，因为学号的值是唯一的，因此就不会造成存储的信息对不上的问题，即学生001的姓名不会存到学生002那里去。

1.3 第三范式3NF

第三范式就是要求表中不能有其他表中存在的、存储相同信息的字段，通常实现是在通过外键去建立关联，因此第三范式只要记住外键约束就好了。

比如说有一个表是学生表，学生表中有学号，姓名等字段，那如果要把他的系编号，系主任，系主任也存到这个学生表中，那就会造成数据大量的冗余，一是这些信息在系信息表中已存在，二是系中有1000个学生的话这些信息就要存1000遍。因此第三范式的做法是在学生表中增加一个系编号的字段（外键），与系信息表做关联。

二、MySQL有关权限的表都有哪几个

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。
这些权限表分别是：user、db、table_priv、columns_priv、host。

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

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

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

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

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

三、事务的四大特性(ACID)

• 原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

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

• 隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

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

四、索引设计的原则是什么

1.适合索引的列是出现在where子句中的列，或者连接子句中指定的列。

2.基数较小的类，索引效果较差，没有必要在此列建立索引。

3.使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间。

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主要为以上操作。即对数据库安全性完整性等有操作的，可以简单地理解为权限控制等。

六、MySQL分库分表的目的

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

七、什么是死锁？怎么解决

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

常见的解决死锁的方法：
1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

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

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；如果业务处理不好可以用分布式事务锁或者使用乐观锁。

八、什么是脏读？幻读？不可重复读？

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

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

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

九、视图有哪些特点

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

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

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

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

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

十、SQL的生命周期

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

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

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

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

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

6.关掉连接，释放资源

十一、主键使用自增ID还是UUID

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

总之，在数据量大一些的情况下，用自增主键性能会好一些。关于主键是聚簇索引，如果没有主键，InnoDB会选择一个唯一键来作为聚簇索引，如果没有唯一键，会生成一个隐式的主键。

十二、MySQL数据库cpu飙升到100%的话怎么处理

当 cpu 飙升到 100%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。

如果是 mysqld 造成的， show processlist，看看里面跑的session情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确，index 是否缺失，或者实在是数据量太大造成。一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。

十三、MySQL主从复制解决了哪些问题

主从复制的作用是：

• 主数据库出现问题，可以切换到从数据库。
• 可以进行数据库层面的读写分离。
• 可以在从数据库上进行日常备份。
• 数据分布：随意开始或停止复制，并在不同地理位置分布数据备份
• 负载均衡：降低单个服务器的压力
• 高可用和故障切换：帮助应用程序避免单点失败
• 升级测试：可以用更高版本的MySQL作为从库

十四、什么是MySQL的GTID

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

GTID是从MySQL 5.6版本开始在主从复制方面推出的重量级特性。GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。

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

GTID有如下几点作用：

• 根据GTID可以知道事务最初是在哪个实例上提交的。
• GTID的存在方便了Replication的Failover。因为不用像传统模式复制那样去找master_log_file和master_log_pos。
• 基于GTID搭建主从复制更加简单，确保每个事务只会被执行一次。

十五、MySQL常用的备份工具有哪些

常用备份工具mysql复制
逻辑备份(mysqldump，mydumper)
物理备份(copy,xtrabackup)

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

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

3.物理备份：拷贝即可用，速度快。copy:直接拷贝文件到数据目录下，可能引起表损坏或者数据不一致。xtrabackup对于innodb表是不需要锁表的，对于myisam表仍然需要锁表。

十六、MySQL备份计划如何制定

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

100G 以上的库，可以考虑用 xtranbackup 来做，备份速度明显要比 mysqldump 要快。一般是选择一周一个全备，其余每天进行增量备份，备份时间为业务低峰期。

转载自：MySQL夺命16问，你能坚持到第几问？