面试-MySQL总结

三范式

三范式定义（范式和反范式）

1NF：每个数据项都是最小单元，不可分割，确定行列之后只能对应一个数据。

2NF：每一个非主属性完全依赖于候选码（属性组的值能唯一的标识一个元组，但是其子集不可以）。

3NF：每一个非主属性既不传递依赖于码，也不部分依赖于码（主码=候选码为多个市，从中选出一个作为主码）。

BCNF：主属性（候选码中的某一个属性）内部也不能部分或传递依赖于码。

4NF ：没有多值依赖。

数据类型

 

整数： int(m)里的m是表示数据显示宽度，浮点数，定点数。

字符串：char（n）4.0 n 代表字节，5.0 n 代表字符 （UTF-8=3zj，GBK=2zj）

 char 固定的字符数，空格补上；检索速度快。

 varchar 字符数+1个字节（n<=255）或2个字节(n>255)

 text 字符数+2个字节；不能有默认值；索引要指定前多少个字符；文本方式存储

 blob 二进制方式存储

存储引擎

各种存储引擎的区别与联系     （存储数据技术和策略，存储机制、索引技巧、锁定水平等）

     show table status 显示表的相关信息

InnoDB与MyISAM的比较（从5.7开始innodb存储引擎成为默认的存储引擎。）

 锁机制：行级锁，表级锁

 事务操作：事务安全，不支持

InnoDB (1)可靠性要求比较高，要求事务；(2)表更新和查询都相当的频繁，并且行锁定的机会比较大的情况。

 MySQL4.1之后每个表的数据和索引存储在一个文件里。

 InnoDB 采用了MVCC来支持高并发，并且实现了四个标准的隔离级别。其默认级别是REPEATABLE READ(可重复读) ，行级锁。

 自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。

 外键约束。MySQL支持外键的存储引擎只有InnoDB。

 支持自动增加列AUTO_INCREMENT属性。

MyIsam  (1)做很多count 的计算；(2)插入不频繁，查询非常频繁；(3)没有事务。

 表存储在两个文件中，数据文件（MYD）和索引文件（MYI）

 表级锁，读=共享锁，写=排它锁。

 适合选择密集型的表，插入密集型的表。

数据库ACID

原子性（Atomicity）一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作。

一致性（Consistency）数据库总是从一个一致性的状态转换到另一个一致性的状态。

隔离性（Isolation）一个事务所做的修改在最终提交以前，对其他事务是不可见的。

持久性（Durability）一旦事务提交，则其所做的修改不会永久保存到数据库。

4 种隔离级别

READ UNCOMMITTED（未提交读）脏读：事务中的修改，即使没有提交，对其他事务也都是可见的。

READ COMMITTED（提交读）不可重复读：事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。

REPEATABLE READ(可重复读)：幻读：一个事务按相同的查询条件读取以前检索过的数据，其他事务插入了满足其查询条件的新数据。产生幻行。

SERIALIZABLE（可串行化） 强制事务串行执行

MVVC是个行级锁的变种，它在普通读情况下避免了加锁操作，自特定情况下加锁。

Mysql死锁问题

M

M

SELECT ... LOCK IN SHARE MODE SELECT ... FOR UPDATE：（LOCK IN SHARE MODE 在有一方事务要Update 同一个表单时很容易造成死锁）

乐观锁：取锁失败，产生回溯时影响效率。

 取数据时认为其他线程不会对数据进行修改。

 更新时判断是否对数据进行修改，版本号机制或CAS操作。

悲观锁：每次取数据都会加锁。

innodb_lock_wait_timeout 等待锁超时回滚事务：  【超时法】

直观方法是在两个事务相互等待时，当一个等待时间超过设置的某一阀值时，对其中一个事务进行回滚，另一个事务就能继续执行。在innodb中，参数innodb_lock_wait_timeout用来设置超时时间。

wait-for graph算法来主动进行死锁检测：  【等待图法】

innodb还提供了wait-for graph算法来主动进行死锁检测，每当加锁请求无法立即满足需要并进入等待时，wait-for graph算法都会被触发。

索引

索引（存储引擎 快速找到记录的一种数据结构，索引的基本功能）

索引类型：

 B-Tree索引 索引列的顺序影响者是否使用索引。

 哈希索引

 无法用于排序。

 只支持全部匹配。

 只支持等值比较。

 有很多哈希冲突时，效率不太高。

 空间数据索引（R-Tree）无需前缀查询，从所有维度查询数据。

 全文检索 查找文本中的关键词，类似于搜索引擎做的事情。

 具体类型介绍：

单列索引：不允许为空

 普通索引 不允许有空值

 唯一索引

 主键索引 在 InnoDB 引擎中很重要

组合引擎：多个字段上创建的索引，复合索引时遵循最左前缀原则。

 查询中某个列有范围查询，则其右边的所有列都无法使用查询

全文索引：

空间索引：

参考：、

MySQL索引详解 （一般使用磁盘I/O次数评价索引结构的优劣。）

 磁盘存取原理

 局部性原理与磁盘预读

M 阶 B-Tree

 

 根节点至少有2个子树。

 每个非叶子节点由n-1个key和n个指针组成。

 分支节点至少拥有m/2颗子树，最多拥有m个子树。（除根节点和叶子结点外）

 所有叶节点具有相同的深度，等于树高 h。

 每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针。

B+ Tree

 内节点不存储data，只存储key。

 叶子节点不存储指针。

MySQL 索引实现

 MyISAM 索引文件和数据文件是分离,非聚集索引。

 InnoDB 叶节点包含了完整的数据记录，聚集索引。根据主键聚集。

EXPLAIN 字段介绍

 possible_keys：显示可能应用在这张表中的索引。

 key：实际使用的索引。

 key_len：使用的索引的长度，越短越好。

 ref：显示索引的哪一列被使用了。

 rows：MySQL认为必须检索的用来返回请求数据的行数。

 type：使用了何种类型。从最好到最差的连接类型为system、const（常量）、eq_ref、ref、range、index（索引全表扫描）和ALL（全表扫描）。

视图 

视图最简单的实现方法是把select语句的结果存放到临时表中。具有性能问题，优化器很难优化临时表上的查询。

 合并算法 ：select语句与外部查询视图的select语句进行合并，然后执行。

 临时表算法 ：先执行视图的select语句，后执行外部查询的语句。

视图在某些情况下可以提升性能，并和其他提升性能的方式叠加使用。

 视图不可以跨表进行修改数据，

 创建有条件限制的视图时，加上“WITH CHECK OPTION”命令。

触发器

 触发器的触发事件 ， 可以是 INSERT 、UPDATE 或者 DELETE 。

 触发时间 ， 可以是 BEFORE 或者 AFTER。

 同一个表相同触发时间的相同触发事件 ， 只能定义一个触发器，只支持基于行触发。

 触发器的原子性，InnoDB支持事务，MyISAM不支持。

事件

     类似于Linux的定时任务，某个时间或者每隔一段时间执行一段SQL代码。

备份

数据备份（深入浅出Mysql 27章 备份与恢复）

 全备份与增量备份的比较。

 确保 MySQL 打开 log-bin 选项，有了 BINLOG，MySQL 才可以在必要的时候做完 整恢复，或基于时间点的恢复，或基于位置的恢复。

逻辑备份（将数据库中的数据备份为一个文本文件，备份的文件可以被查 看和编辑。）

物理备份

 冷备份：cp移动数据文件的方法。

 恢复：移动数据文件，使用 mysqlbinlog 工具恢复自备份以来的所有 BINLOG。

 热备份：（将要备份的表加读锁，然后再 cp 数据文件到备份目录。）

 MyISAM：mysqlhotcopy工具。

 ibbackup 是 Innobase 公司（www.innodb.com）的一个热备份工具。

恢复

     完全恢复

 将备份作为输入执行。

 将备份后执行的日志进行重做。

     不完全恢复（跳过误操作语句，再恢复后 面执行的语句，完成我们的恢复。）

 基于时间点的操作。跳过故障发生时间。

 基于位置的恢复。找到出错语句的位置号，并跳过位置区间。

 

日志

错误日志：记录了当 mysqld 启动和停止时，以及服务器在 运行过程中发生任何严重错误时的相关信息。

二进制文件：记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言） 语句，不包括数据查询语句。语句以“事件”的形式保存，它描述了数据的更改过程。（定期删除日志，默认关闭）。

查询日志：记录了客户端的所有语句，格式为纯文本格式，可以直接进行读取。（log 日志中记录了所有数据库的操作，对于访问频繁的系统，此日志对系统性能的影响较大，建议关闭，默认关闭）。

慢查询日志：慢查询日志记录了包含所有执行时间超过参数long_query_time（单位：秒）所设置值的 SQL 语句的日志。（纯文本格式）。

日志文件小结：

 系统故障时，建议首先查看错误日志，以帮助用户迅速定位故障原因。

 记录数据的变更、数据的备份、数据的复制等操作时，打开二进制日志。默认不记录此日志，建议通过--log-bin 选项将此日志打开。

 如果希望记录数据库发生的任何操作，包括 SELECT，则需要用--log 将查询日志打开， 此日志默认关闭，一般情况下建议不要打开此日志，以免影响系统整体性能。

 查看系统的性能问题， 希望找到有性能问题的SQL语 句，需要 用 --log-slow-queries 打开慢查询日志。对于大量的慢查询日志，建议使用 mysqldumpslow 工具 来进行汇总查看。