面试-MySQL总结

三范式

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

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

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

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

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

4NF ：没有多值依赖。

MySQL的复制原理以及流程

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

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

数据类型

MySQL数据类型-菜鸟教程 

MYSQL中数据类型介绍

整数： 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 二进制方式存储

mysql中myisam与innodb的区别

存储引擎

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

数据库存储引擎     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）

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

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

5点不同

• InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

• InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

• InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

• InnoDB支持MVCC, 而MyISAM不支持

• InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快； 但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。

• Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

• 对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。

• DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除

数据库ACID

数据库的ACID

数据库事务介绍

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

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

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

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

4 种隔离级别

MVVC的简单介绍

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

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

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

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

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

innodb引擎的4大特性

• 插入缓冲（insert buffer)
  插入主键聚集索引，是顺序的，不需要磁盘的随机读取；但是这也导致同一个表中的非聚集索引不是顺序的，因为B+树的特性决定了非聚集索引插入的离散型。
  插入缓存就是为提高非聚集索引的插入和更新操作的性能而做的优化设计，其原理将插入数据先放到内存就直接返回上层，上层看来已经插入成功，其实插入数据还在内存中，内部会触发内存的索引数据与物理的索引数据进行合并操作，合并时将多个插入合并到一个操作（正好一个索引页），这样大大提高了对非聚集索引插入的性能。

• 二次写(double write)
  为了提升数据页的可靠性。
  写数据页的时候宕机怎么办？
  重做日志，但是如果物理页已经损坏了怎么版？
  用doubleWrite：发现物理页损坏了，则找到其前面的一个副本，用副本来还原当前页，再重做日志。

• 自适应哈希索引(ahi)
  原先的索引是B+树结构，当查询频繁，建立哈希可以提高效率，则自动构建哈希索引，提高速度。

• 异步IO(Async IO)
  同时发起多个IO请求（索引页的扫描），可以将多个IO请求合并为一个IO操作，同时将每个IO请求的结果进行Merge。

• 刷新邻接页
  刷新一个脏页的同事检查所在区的其他页是否需要一起刷新。

Mysql死锁问题

Mysql悲观锁总结和实践

Mysql乐观锁总结和实践

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算法都会被触发。

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

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

索引

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

什么是B-Tree

MySQL索引背后的数据结构及算法原理

MySQL性能优化-慢查询分析、优化索引和配置

MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义

(1)、varchar与char的区别
char是一种固定长度的类型，varchar则是一种可变长度的类型

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

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

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

索引类型：

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

 哈希索引

 无法用于排序。

 只支持全部匹配。

 只支持等值比较。

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

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

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

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

(1)、有多少种日志；

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

(2)、事物的4种隔离级别

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

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

 具体类型介绍：

单列索引：不允许为空

 普通索引 不允许有空值

 唯一索引

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

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

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

全文索引：

空间索引：

参考：细说mysql索引、我的MYSQL学习心得（九） 索引

MySQL binlog的几种日志录入格式以及区别

(1)、binlog的日志格式的种类和分别
(2)、适用场景；
(3)、结合第一个问题，每一种日志格式在复制中的优劣。

• Statement：
  每一条会修改数据的sql都会记录在binlog中，过程导向（没有关注结果）。
  优点：记录sql语句上下文相关信息
  缺点：存储过程，或function，以及trigger的调用和触发无法被正确复制
• 美高梅官方网站，Row:
  不记录sql语句上下文相关信息，仅保存哪条记录被修改成什么样子，结果导向（不关注过程）。
  优点：仅需要记录那一条记录被修改成什么了。所以会非常清楚的记录下每一行数据修改的细节。
  缺点：产生大量的日志内容。
• Mixedlevel:
  是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则 采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式

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 叶节点包含了完整的数据记录，聚集索引。根据主键聚集。

MySQL数据库cpu飙升到500%的话他怎么处理？

(1)、没有经验的，可以不问；
(2)、有经验的，问他们的处理思路。

• 找出占用的线程杀掉，分析日志，找问题，解决
• mysql> show processlist; 找出占用cpu的线程
• 常见问题 ：
  1. 睡眠连接过多，严重消耗mysql服务器资源(主要是cpu, 内存)，并可能导致mysql崩溃。
     解决办法 ：
     mysql的配置my.ini文件中wait_timeout, 即可设置睡眠连接超时秒数，如果某个连接超时，会被mysql自然终止。
     mysql> set global wait_timeout=20;
  2. 增加 tmp_table_size 值
  3. SQL语句没有建立索引
  4. 函数计算的，放到应用层进行

本文由美高梅官方网站发布于数据统计,转载请注明出处：面试-MySQL总结