MySQL理论知识面试题

1. varchar（10）和int（10）代表什么含义？

varchar是变长字段，比如varchar(10)表示申请了最大长度为10的空间，但占用的空间为实际字符长度+1，而int（10）只是代表了展示的长度，不足10位用0填充，也就是说int（1）和int（10）所能存储的数字大小以及占用的空间都是相同的，只是在展示时按照长度展示，int（1）展示1位，int（10）展示10位。

2. MySQL中varchar与char的区别以及varchar（50）中50代表的含义。

• varchar与char的区别是：char是固定长度的字段，varchar是可变长度的字段。
• varchar（50）中50的含义最多存放50个字符，但实际占用空间要看存储的数据。比如varchar（50）和varchar（200）存储hello占用的空间都是一样的。
• int（10）中10的含义是指显示字符的长度。比如数字1，那么便显示0000000001。int（1）和int（10）存储和计算一样

3. InnoDB的日志种类

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

4. 事务如何通过日志来实现的

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

5. 三个范式是什么？

1. 第一范式（1NF）
   数据库中的字段都是单一属性，不可再分。这个单一属性由基本数据类型构成，包括整型、实数、字符型、逻辑型、日期型等。
   比如：
   学生信息表：有年龄、性别、学号等字段，不可再分，满足基础数据类型，满足第一范式
2. 第二范式（2NF）
   满足第一范式，表中的字段必须完全依赖与全部主键（可能是单一字段，也可能使多个字段组成）而非部分主键。
   比如：
   学号为1024的同学，姓名为Java，年龄是23。姓名和年龄都依赖着学号主键（因为通过主键可以确定姓名和年龄）
3. 第三范式（3NF）
   满足第二范式，非主键外的所有字段必须互不依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
   比如：
   我们大学分了很多系（中文系、英语系、计算机系），这个系表由一下字段组成（系编号、系主任、系简介、系架构）那能不能在学生表中添加系编号、系主任、系简介、系架构字段呢？
   答案肯定是不行的，非主键：系主任、系简介、系架构这些依赖于—>学生表中的非主键系编号,而它依赖于—>学生表的主键学号

6. 事务的隔离级别

• Serializable（串行化：避免脏读、不可重复读，虚读）
• Repeatable（可重复读：避免脏读、不可重复读）
• Read committed（读提交：避免脏读）
• Read uncommitted（读未提交：什么都避免不了）

脏读：一个事务中查询到的数据前后不一致
重复读：一个事务中查询到的数据始终和开始一样

7. SQL中约束有哪些

• not null：字段不为空
• unique：字段内容不能重复
• primary key：主键
• foreign key：外键
• check：控制字段的值范围

8. drop、delete、truncate

drop：用于删除表，属于DML
delete：用于删除记录，属于DDL
truncate：保留表而删除所有数据，属于DDL

9. 索引的优缺点

1）优点
加快了数据库的检索速度
2）缺点

• 降低插入、删除、修改等维护任务的速度，因为数据更新同时需要更新索引
• 索引需要占用物理和数据空间

10. 什么时候要创建索引

1）表经常进行select操作
2）表记录超多，记录内容分布访问广
3）列名经常在where子句或连接条件中出现

11. 什么时候不要创建索引

1）表经常进行insert、update、delete操作
2）表很小
3）列名不常出现在where子句或连接条件中

12. 索引的基本原理

13. 怎么处理慢查询

13. 关系型与非关系型数据库的优势

1）非关系型数据库的优势
性能：NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能很高。
可扩张性：同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展

2）关系型数据库的优势
复杂查询：可以在一个表或者多个表之间做非常复杂的数据查询
事务支持：可以实现数据的安全访问以及操作

14. 索引的数据类型

15. 彻底搞懂B-树、B+树、B树

彻底搞懂系列B-树、B+树、B-树、B*树

16. 为什么设计红黑树

红黑树通过它规则的设定，确保了插入和删除的最坏的时间复杂度是 O(log N) 。

红黑树解决了 AVL 平衡二叉树的维护起来比较麻烦的问题，红黑树，读取略逊于 AVL，维护强于 AVL，每次插入和删除的平均旋转次数应该是远小于平衡树。
因此：
相对于要求严格的 AVL 树来说，红黑树的旋转次数少，所以对于插入、删除操作较多的情
况下，我们就用红黑树。但是，只是对查找要求较高,那么 AVL 还是较优于红黑树

17. B树和B+树的区别。为什么MySQL使用B+树？

B索引树每个节点存储的都是索引值和data域，而B+索引树的非叶子结点存储的都是索引值，叶子节点存储的都是data域。

• B树的存储结构图
  
• B+树的存储结构图
  
  

18. AVL 树（平衡二叉树）和红黑树的区别

红黑树的算法时间复杂度和 AVL 相同，但统计性能比 AVL 树更高。

1、红黑树和 AVL 树都能够以 O(log2 n)的时间复杂度进行搜索、插入、删除操作。
2、由于设计，红黑树的任何不平衡都会在三次旋转之内解决。AVL 树增加和删除可能需要 通过一次或多次树旋转来重新平衡这个树。

在查找方面：

红黑树的性质(最长路径长度不超过最短路径长度的 2 倍)，其查找代价基本维持在 O(logN)左右，但在最差情况下(最长路径是最短路径的 2 倍少 1)，比 AVL 要略逊色一点。   
AVL 是严格平衡的二叉查找树（平衡因子不超过 1）。查找过程中不会出现最差情况的单支树。因此查找效率最好，最坏情况都是 O(logN)数量级的

所以，综上：
  AVL 比 RBtree 更加平衡，但是 AVL 的插入和删除会带来大量的旋转。 所以如果插入和删除比较多的情况，应该使用 RBtree, 如果查询操作比较多，应该使用 AVL。AVL 是一种高度平衡的二叉树，维护这种高度平衡所付出的代价比从中获得的效率收益还大，故而实际的应用不多，更多的地方是用追求局部而不是非常严格整体平衡的红黑树。当然，如果场景中对插入删除不频繁，只是对查找特别有要求，AVL 还是优于红黑的。

19. mysql 的 Innodb 引擎为什么采用的是 B+树的索引方式

B+树只有叶子节点存放数据，而其他节点只存放索引，而 B 树每个节点都有 Data 域。所以相同大小的节点 B+树包含的索引比 B 树的索引更多（因为 B 树每个节点还有 Data 域）还有就是 B+树的叶子节点是通过链表连接的，所以找到下限后能很快进行区间查询，比 B树中序遍历快。

---

1. 数据库三大范式

• 1NF：每个属性都不可再分；
• 2NF：在1NF基础上，消除了非主键对于主键的部分函数依赖;
• 3NF：在2NF基础上，消除了非主键对于主键的传递函数依赖;

2. SQL约束的种类

• 非空约束(NOT NULL): 用于控制字段的内容一定不能为空
• 唯一约束(UNIQUE): 控件字段内容不能重复，一个表允许有多个唯一约束。
• 主键约束(PRIMARY KEY): 也是用于控件字段内容不能重复且不能为空。
• 外键约束(FOREIGN KEY): 外键约束是指用于在两个表之间建立关系，需要指定引用主表的哪一列

3. MySQL存储引擎MyISAM与InnoDB区别

• 事务：MyISAM不支持事务，InnoDB支持事务
• 锁级别：MyISAM只支持表锁，InnoDB支持行锁和表锁，默认使用行锁。
• 外键：MyISAM不支持外键。InnoDB支持外键完整性约束
• MyISAM数据和索引是分别存储的，数据.MYD，索引.MYI；InnoDB数据和索引是集中存储的，.ibd
• InnoDB的主键是聚集索引，Data域保存该行的所有数据内容；非聚集索引的Data域保存的则是主键数据。因此非主键查询需要查询两次回表。
• MyISAM的主键索引和非聚集索引的Data域都是保存行数据记录的地址

4. 索引以及索引类型和优缺点

• 索引：索引是帮助数据库高效获取数据的数据结构 本质索引就是数据结构
• 索引分类
  1）主键索引(primary key) 　 唯一标识，主键不可重复，只能有一个列作为主键
  2）唯一索引(unique key) 　 当前列的值必须唯一，允许有空值
  3）常规索引(key/index) 　 普通索引是最基本的索引，没什么限制。
  4）全文索引(fullText) 　 快速定位数据
• 索引的优点
  1）提高数据的检索效率，降低数据库的IO成本
  2）通过索引列对数据进行排序，降低数据的排序成本，降低CPU的消耗
• 索引的缺点
  1）索引也是一张表，因此索引列也是占用空间的
  2）索引也要动态的维护，会降低表的更新速度

6.MySQL为什么必须要有主键

Innodb中的每张表都会有一个聚集索引，Innodb选择聚集索引的规则如下：

• 如果一个主键被定义了，那么这个主键就是作为聚集索引
• 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引
• 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，该列的值会随着数据的插入自增。

7. 主键一般用自增ID还是UUID？

MySQL推荐使用自增ID
   因为在MySQL的 InnoDB
存储引擎中，主键索引是聚簇索引，主键索引的B+树的叶子节点按照顺序存储了主键值及数据，如果主键索引是自增ID，只需要按顺序往后排列即可，如果是UUID，ID是随机生成的，在数据插入时会造成大量的数据移动，产生大量的内存碎片，造成插入性能的下降

8. 为什么使用B+Tree作为索引

• 局部性原理逻辑分析
  局部性原理：使用到一个数据，大概率会使用其附近的数据 磁盘预读：内存读写块，磁盘读写慢，磁盘读写并不是按需读取，而是按页预读，一次会读一页的数据，每次加载更多的数据，如果未来要读取的数据就在这一页中，可以避免未来的磁盘IO，提高效率；
• B+Tree索引的性能分析
  1）B+树的叶子节点之间通过双向链表连接，因此B+树只需要遍历叶子节点就可以解决 对全部关键字信息的扫描，所以范围查询、排序等操作，B+树有着更高的性能。
  2）B+树的非叶子结点没有存储数据，因此相对于B树来说，每个节点容纳的关键字等过，那么页所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多，相对来说I/O读写次数也就降低了。
  3）B+树的非叶子结点没有存储数据，所以任何关键字的查找必须走一条从根结点到叶子结点的路，即所有关键字查询的路径长度相同，因此B+树的查询效率更加稳定

9. Hash索引以及优缺点

• 哈希索引采用一定的哈希算法，检索时不需要类似B+树那样从根节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置
• Hash索引仅能满足等值的查询，不能满足范围查询、排序
• 当发生Hash碰撞时，Hash索引不能避免表数据的扫描
• 当创建组合索引时，不能只使用组合索引的部分列进行查询。因为hash索引是把多个列数据合并后再计算Hash值，所以对单独列数据计算Hash值是没有意义的。

10. 索引优化

1. 全值匹配 针对联合索引需要遵循最佳左前缀原则以及中间不可断
2. 不要在索引列上做任何操作（计算，函数，类型转换等），否则索引失效而进行全表扫描
3. 存储引擎不能使用索引中范围条件右边的列（范围in,between,like等之后索引全失效）
4. 尽量使用索引覆盖，减少select *
5. 在使用不等于（!= 或者<>）会无法使用索引导致全表扫描
6. 在使用is null,is not null也无法使用索引
7. like 的前模糊匹配索引也会失效，即通配符应加在like右边（like’abc%’），如果左边有通配符就用覆盖索引解决
8. 字符串不加单引号索引失效
9. 少用or,用or连接时会索引失效，使用 union all 或者 union 来替代

11. 适合创建索引的情况

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该建立索引
3. 查询中与其他表关联的字段，外键建立索引
4. 频繁更新的字段不适合建立索引
5. 尽量建立组合索引
6. 排序字段应该建立索引
7. 分组和统计字段应该建立索引

12. 不适合创建索引的情况

1. 表的记录太少
2. 经常更新的表
3. 数据值重复且平均分布的字段

13.explain的执行计划

参考于：https://www.cnblogs.com/myseries/p/10736268.html

14. 事务的ACID原则

1）原子性 　事务中的所有操作要么都成功，要么都失败
2）一致性 　事务执行前后数据要保持一致性，例如转账前后总金额保持不变
3）隔离性 　多个用户并发访问数据库时，数据库为每个用户创建一个事务，事务之间互相隔离，互不干扰。
4）持久性
　事务完成以后，对数据的修改是永久性的。就算系统发生故障，该修改也永久保持

15. 事务的并发存在的问题

1）脏读： 一个事务读取了另一事务未提交的数据
2）不可重复读： 一个事务多次读取某个数据，得到不一致的结果（多次读取的结果不一致）
3）幻读： 一个事务读取到了另一个事务插入或者删除的数据，导致前后不一致。

9. MySQL锁的类型有哪些？

8. 数据库的乐观锁和悲观锁是什么？

两种锁都是为了确保多个事务同时存取数据库中同一个数据时不破坏事务的隔离性和统一性以及数据库的统一性，乐观锁和悲观锁是并发控制采用的主要手段。

• 悲观锁：每次查询数据以及操作数据都加锁
• 乐观锁：每次查询以及操纵数据都不加锁，只需比对数据的版本号（或者时间戳），正确方可操作数据

18. InnoDB的行锁有两种类型：

• 共享锁（S锁、读锁）：多个事务可以对同一数据行共享一把S锁，但只能进行读不能修改；
• 排它锁（X锁、写锁）：一个事务获取排它锁之后，可以对锁定范围内的数据行执行写操作，在锁定期间，其他事务不能再获取这部分数据行的锁（共享锁、排它锁），只允许获取到排它锁的事务进行更新数据。
    注意：对于update，delete，insert 操作，InnoDB会自动给涉及的数据行加排他锁；对于普通select语句，InnoDB不会加任何锁，可以在select语句末位加for update锁定一行 。

19. InnoDB行锁的实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这就意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。

20. MySQL的binlog有几种录入格式？分别有什么区别？

• 基于语句的复制statement（默认）
  每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO,在主从同步中不建议使用该模式，因为有一些使用了函数之类的语句不支持。
• 基于行的复制row
  日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。优点：能清楚的记录每一行数据修改的细节;缺点：数据量太大日志量太大。
• mixed级别
  一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。

21. 主从复制的原理

22. 主从复制的优点

• 读写分离；
• 提高数据安全性，数据进行了备份；
• 主数据库出现故障，可以进行数据库的切换。

23. 读写分离和分库分表

参考于：https://www.cnblogs.com/lfri/p/12545831.html

章尾
参考链接：https://blog.csdn.net/qq_39033181/article/details/116117025

---

4. 最左前缀原则是什么？

5. 什么是MVCC

MySQL数据库有哪些锁？