面试题-MySQL高级

1. 什么是索引？

答案

索引是一种数据结构，可以帮助我们快速的进行数据的查找

更好的解释：一种排好序的快速查找的数据结构。

2. 索引是个什么样的数据结构呢？

答案

索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，而我们经常使用的InnoDB存储引擎的默认索引实现为：B+索引

3. Hash索引和B+树索引有什么区别或者说优劣呢？

答案

首先要知道Hash索引和B+树索引的底层实现原理：

hash索引底层就是hash表,进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

那么可以看出他们有以下的不同：
- hash索引进行等值查询更快（一般情况下），但是却无法进行范围查询。
  
  因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循（左节点小于父节点,右节点大于父节点，多叉树也类似），天然支持范围。
- hash索引不支持使用索引进行排序，原理同上。
- hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。
- hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件（聚簇索引，覆盖索引）的时候可以只通过索引完成查询。
- hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

4. 在建立索引的时候，都有哪些需要考虑的因素呢？

答案

建立索引的时候一般要考虑字段的使用频率，经常作为条件进行查询的字段比较适合，如果需要建立联合索引的话，还需要考虑联合索引中的顺序。此外也要考虑其他方面，比如防止过多的所以对表造成太大的压力。这些都和实际的表结构以及查询方式有关。

5. 了解过哪些存储引擎？各有什么优缺点？

答案

最常用的是MyISAM和InnoDB。
- InnoDB：支持事务、支持外键、支持行级锁，不支持全文索引。
- MyISAM：不支持事务、不支持外键、不支持行级锁，支持全文索引。

6. 说一下什么是事务的ACID属性

答案
1. 原子性（atomicity)
  
  一个事务要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作，这就是事务的原子性。
2. 一致性（consistency)
  
  事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态。如果数据库系统在运行过程中发生故障，有些事务尚未完成就被迫中断，这些未完成的事务对数据库所作的修改有一部分已写入物理数据库，这是数据库就处于一种不正确的状态，也就是不一致的状态。
3. 隔离性（isolation）
  
  事务的隔离性是指在并发环境中，并发的事务时相互隔离的，一个事务的执行不能不被其他事务干扰。不同的事务并发操作相同的数据时，每个事务都有各自完成的数据空间，即一个事务内部的操作及使用的数据对其他并发事务时隔离的，并发执行的各个事务之间不能相互干扰。在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同，分别是：未授权读取，授权读取，可重复读取和串行化。
4. 持久性（durability）
  
  一旦事务提交，那么它对数据库中的对应数据的状态的变更就会永久保存到数据库中。即使发生系统崩溃或机器宕机等故障，只要数据库能够重新启动，那么一定能够将其恢复到事务成功结束的状态。

7. 事务的隔离级别了解过吗？

答案
1. 读未提交（Read Uncommited），该隔离级别允许脏读取，其隔离级别最低；比如事务A和事务B同时进行，事务A在整个执行阶段，会将某数据的值从1开始一直加到10，然后进行事务提交，此时，事务B能够看到这个数据项在事务A操作过程中的所有中间值（如1变成2，2变成3等），而对这一系列的中间值的读取就是未授权读取。
2. 授权读取也称为已提交读（Read Commited），授权读取只允许获取已经提交的数据。比如事务A和事务B同时进行，事务A进行+1操作，此时，事务B无法看到这个数据项在事务A操作过程中的所有中间值，只能看到最终的10。另外，如果说有一个事务C，和事务A进行非常类似的操作，只是事务C是将数据项从10加到20，此时事务B也同样可以读取到20，即授权读取允许不可重复读取。
3. 可重复读（Repeatable Read)
  
  就是保证在事务处理过程中，多次读取同一个数据时，其值都和事务开始时刻是一致的，因此该事务级别禁止不可重复读取和脏读取，但是有可能出现幻影数据。所谓幻影数据，就是指同样的事务操作，在前后两个时间段内执行对同一个数据项的读取，可能出现不一致的结果。在上面的例子中，可重复读取隔离级别能够保证事务B在第一次事务操作过程中，始终对数据项读取到1，但是在下一次事务操作中，即使事务B（注意，事务名字虽然相同，但是指的是另一个事务操作）采用同样的查询方式，就可能读取到10或20。
4. 串行化
  
  是最严格的事务隔离级别，它要求所有事务被串行执行，即事务只能一个接一个的进行处理，不能并发执行。

8. 说说InnoDB的索引原理

答案

9. 有了解过“回表”的概念吗？什么情况下会出现“回表”？

答案

回表就是先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。

当查询的字段在二级索引上没有的时候，就需要“回表”在主键索引上再查一次。

10. MySQL索引的类型

答案

聚簇索引、二级（辅助）索引、B树索引、hash索引

11. 有做过MySQL的索引优化吗？

答案

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号写最右，SQL高级牢掌握。

12. 什么是聚簇索引？

答案

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据的对应行

13. InnoDB有聚簇索引吗？MyIsam呢？

答案

InnoDB有聚簇索引，主键索引就是聚簇索引。MyIsam没有聚簇索引，因为他的索引和记录是分开存储的。

14. MyIsam的数据是怎么存储的？

答案

MyIsam索引的节点中存储的是数据的物理地址（磁道和扇区），在查找数据时，查找到索引后，根据索引节点中的物理地址，查找到具体的数据内容。

15. InnoDB的数据是怎么存储的？

答案

InnoDB的主键索引文件上直接存放该行数据，称为聚簇索引，非主键索引指向对主键的引用。

16. InnoDB主键索引跟非主键索引在数据存储上的差异

答案
- 主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也称为聚簇索引（clustered index）
- 非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）

17. InnoDB删除某条记录后，内部会怎么处理？

答案

记录头信息里的delete_mask标记位设置为1（表示该记录已删除），同时将记录从记录行链表中断开，并加入到垃圾链表中，垃圾链表的空间后续可以复用。

18. InnoDB如果没有设置主键的话，它内部会怎么处理？

答案

优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。所以我们从上表中可以看出：InnoDB存储引擎会为每条记录都添加transaction_id和roll_pointer这两个列，但是row_id是可选的（在没有自定义主键以及Unique键的情况下才会添加该列）。这些隐藏列的值不用我们操心，InnoDB存储引擎会自己帮我们生成的。

19. 为什么InnoDB一定会生成主键？

答案

因为InnoDB的数据结构是通过聚簇索引组织起来的，如果没有主键的话，通过其他索引回表的时候没法查到相应的数据行。

20. MySQL分库分表了解过吗？

答案

21. MySQL的redo日志和undo日志分别有什么用？

答案
1. redo
  
  作用：保证事务的持久性
  
  MySQL作为一个存储系统，为了保证数据的可靠性，最终得落盘。但是，又为了数据写入的速度，需要引入基于内存的"缓冲池"。其实不止MySQL，这种引入缓冲来解决速度问题的思想无处不在。既然数据是先缓存在缓冲池中，然后再以某种方式刷新到磁盘，那么就存在因宕机导致的缓冲池中的数据丢失，为了解决这种情况下的数据丢失问题，引入了redo log。在其他存储系统，比如Elasticsearch中，也有类似的机制，叫translog。但是一般讨论数据写入时，在MySQL中，一般叫事务操作，根据事务的ACID特性，如何保证一个事务提交后Durability的保证？而这就是 redo log 的作用。当向MySQL写用户数据时，先写redo log，然后redo log根据"某种方式"持久化到磁盘，变成redo log file，用户数据则在"buffer"中(比如数据页、索引页)。如果发生宕机，则读取磁盘上的 redo log file 进行数据的恢复。从这个角度来说，MySQL 事务的持久性是通过 redo log 来实现的。
2. undo
  
  作用：实现事务回滚（原子性）
  
  Undo log是InnoDB MVCC事务特性的重要组成部分。当我们对记录做了变更操作时就会产生undo记录，Undo记录默认被记录到系统表空间(ibdata)中，但从5.6开始，也可以使用独立的Undo 表空间。Undo记录中存储的是老版本数据，当一个旧的事务需要读取数据时，为了能读取到老版本的数据，需要顺着undo链找到满足其可见性的记录。当版本链很长时，通常可以认为这是个比较耗时的操作。大多数对数据的变更操作包括INSERT/DELETE/UPDATE，其中INSERT操作在事务提交前只对当前事务可见，因此产生的Undo日志可以在事务提交后直接删除（谁会对刚插入的数据有可见性需求呢！！），而对于UPDATE/DELETE则需要维护多版本信息，在InnoDB里，UPDATE和DELETE操作产生的Undo日志被归成一类，即update_undo。

22. MySQL的redo日志刷盘时机

答案
- log buffer空间不足时
- 事务提交时
- 后台线程不停的刷刷刷
- 正常关闭服务器时
- 做所谓的checkpoint时

23. MySQL有哪些锁？以及各种锁的作用？

答案

24. MySQL有哪些日志？分别是什么用处？

答案

mysql日志一般分为5种
- 错误日志：-log-err (记录启动，运行，停止mysql时出现的信息)
- 二进制日志：-log-bin （记录所有更改数据的语句，还用于复制，恢复数据库用）
- 查询日志：-log （记录建立的客户端连接和执行的语句）
- 慢查询日志: -log-slow-queries （记录所有执行超过long_query_time秒的所有查询）
- 更新日志: -log-update （二进制日志已经代替了老的更新日志，更新日志在MySQL5.1中不再使用）

25. 在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢？

答案
- 使用了不等于查询
- 列参与了数学运算或者函数
- 在字符串like时左边是通配符，类似于' %aaa'
- 当mysql分析全表扫描比使用索引快的时候不使用索引
- 当使用联合索引，前面一个条件为范围查询，后面的即使符合最左前缀原则，也无法使用索引
以上情况，MySQL无法使用索引

26. 为什么要尽量设定一个主键？

答案

主键是数据库确保数据行在整张表唯一性的保障，即使业务上本张表没有主键，也建议添加一个自增长的ID列作为主键，设定了主键之后，在后续的删改查的时候可能更加快速以及确保操作数据范围安全。

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

答案

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

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

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

图片来源于《高性能MySQL》: 其中默认后缀为使用自增ID,_uuid为使用UUID为主键的测试,测试了插入100w行和300w行的性能

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

28. 字段为什么要求定义为not null？

答案

MySQL官网这样介绍：

null 值会占用更多的字节，且会在程序中造成很多与预期不符的情况。

29. varchar(10)和int(10)代表什么含义？

答案

varchar的10代表了申请的空间长度，也是可以存储的数据的最大长度，而int的10只是代表了展示的长度，不足10位以0填充。也就是说，int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的，只是在展示时按照长度展示。

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

答案

有三种格式，statement，row和mixed。
- statement模式下，记录单元为语句。即每一个sql造成的影响会记录。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
- row级别下，记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动（比如alter table），因此这种模式的文件保存的信息太多，日志量太大。
- mixed。一种折中的方，通操作使用statement记录，当无法使用statement的时候使用row。
此外，新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

31. 什么是存储过程？有哪些优缺点？

答案

存储过程是一些预编译的SQL语句。
1. 更加直白的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。
2. 存储过程是一个预编译的代码块，执行效率比较高,一个存储过程替代大量T_SQL语句，可以降低网络通信量，提高通信速率,可以一定程度上确保数据安全。
但是,在互联网项目中，其实是不太推荐存储过程的，比较出名的就是阿里的《Java开发手册》中禁止使用存储过程，我个人的理解是，在互联网项目中，迭代太快，项目的生命周期也比较短，人员流动相比于传统的项目也更加频繁，在这样的情况下，存储过程的管理确实是没有那么方便，同时，复用性也没有写在服务层那么好。

32. 说一说三个范式

答案

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

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

第三范式：非主键列只依赖于主键，不依赖于其他非主键。

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能，事实上我们经常会为了性能而妥协数据库的设计。

33. 什么情况下应不建或少建索引

答案
1. 表记录太少
2. 经常插入、删除、修改的表
3. 数据重复且分布平均的表字段，假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个字段分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库得查询速度。
4. 经常和主字段一块查询但主字段索引值比较多的表字段

34. 什么是表分区？

答案

表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

35. 表分区与分表的区别

答案

分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。

分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表

36. 表分区有什么好处？

答案
1. 存储更多数据。分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。和单个磁盘或者文件系统相比，可以存储更多数据。
2. 优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。
3. 分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。
4. 避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问。

37. MVCC了解过吗？

答案

MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议 —— MVCC（Multi-Version Concurrency Control）

注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control

MVCC最大的好处：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，现价段几乎所有的RDBMS，都支持MVCC。
1. LBCC：Lock-Based Concurrency Control，基于锁的并发控制
2. MVCC：Multi-Version Concurrency Control
  
  基于多版本的并发控制协议。纯粹基于锁的并发机制并发量低，MVCC是在基于锁的并发控制上的改进，主要是在读操作上提高了并发量。

38. 在MVCC并发控制中，读操作可以分成哪几类？

答案
1. 快照读（snapshot read）：读取的是记录的可见版本（有可能是历史版本），不用加锁（共享读锁s锁也不加，所以不会阻塞其他事务的写）。
2. 当前读（current read）：读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

39. 行级锁定的优点

答案
1. 当在许多线程中访问不同的行时只存在少量锁定冲突。
2. 回滚时只有少量的更改。
3. 可以长时间锁定单一的行。

40. 行级锁定的缺点

答案
1. 比页级或表级锁占用更多的内存。
2. 当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。
3. 如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其他锁定明显慢很多。
4. 用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定。

41. MySQL优化（重要）

答案
1. 开启查询缓存，优化查询
2. explain你的select查询，这可以帮你分析你的查询语句或是表结构的性能瓶颈。
  
  EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索
  
  和排序的。
3. 当只要一行数据时使用limit 1，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。
4. 为搜索字段建索引。
5. 使用 ENUM 而不是 VARCHAR。如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM而不是VARCHAR。
6. Prepared StatementsPrepared Statements很像存储过程，是一种运行在后台的SQL语句集合，我们可以从使用 prepared statements 获得很多好处，无论是性能问题还是安全问题。Prepared Statements 可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“SQL注入式”攻击
7. 垂直分表
8. 选择正确的存储引擎

42. key和index的区别

答案
1. key是数据库的物理结构，它包含两层意义和作用，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary key，unique key，foreign key等。
2. index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的InnoDB表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等。

43. delete、truncate、drop区别

答案
- truncate和delete只删除数据，不删除表结构，drop删除表结构，并且释放所占的空间。
- 删除数据的速度，drop > truncate > delete
- delete属于DML语言，需要事务管理，commit之后才能生效。drop和truncate属于DDL语言，操作立刻生效，不可回滚。
使用场合：当你不再需要该表时，用drop；当你仍要保留该表，但要删除所有记录时，用truncate；当你要删除部分记录时（always with a where clause），用delete。

44. MySQL主从复制原理流程

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

45. 自增主键最大ID记录，MyISAM和InnoDB分别是如何存储的？

答案
- MyISAM表把自增主键的最大ID记录到数据文件里
- InnoDB表把自增主键的最大ID记录到内存中

46. MySQL如何优化DISTINCT？

答案

DISTINCT在所有列上转换为GROUP BY，并与ORDER BY子句结合使用。

47. 解释MySQL外连接、内连接与自连接的区别

答案

先说什么是交叉连接: 交叉连接又叫笛卡尔积，它是指不使用任何条件，直接将一个表的所有记录和另一个表中的所有记录一一匹配。

内连接则是只有条件的交叉连接，根据某个条件筛选出符合条件的记录，不符合条件的记录不会出现在结果集中，即内连接只连接匹配的行。

外连接其结果集中不仅包含符合连接条件的行，而且还会包括左表、右表或两个表中
的所有数据行，这三种情况依次称之为左外连接，右外连接，和全外连接。

左外连接，也称左连接，左表为主表，左表中的所有记录都会出现在结果集中，对于那些在右表中并没有匹配的记录，仍然要显示，右边对应的那些字段值以NULL来填充。右外连接，也称右连接，右表为主表，右表中的所有记录都会出现在结果集中。左连接和右连接可以互换，MySQL目前还不支持全外连接。

Java面试题 mysql

面试题-MySQL高级

1. 什么是索引？

2. 索引是个什么样的数据结构呢？

3. Hash索引和B+树索引有什么区别或者说优劣呢？

4. 在建立索引的时候，都有哪些需要考虑的因素呢？

5. 了解过哪些存储引擎？各有什么优缺点？

6. 说一下什么是事务的ACID属性

7. 事务的隔离级别了解过吗？

8. 说说InnoDB的索引原理

9. 有了解过“回表”的概念吗？什么情况下会出现“回表”？

10. MySQL索引的类型

11. 有做过MySQL的索引优化吗？

12. 什么是聚簇索引？

13. InnoDB有聚簇索引吗？MyIsam呢？

14. MyIsam的数据是怎么存储的？

15. InnoDB的数据是怎么存储的？

16. InnoDB主键索引跟非主键索引在数据存储上的差异

17. InnoDB删除某条记录后，内部会怎么处理？

18. InnoDB如果没有设置主键的话，它内部会怎么处理？

19. 为什么InnoDB一定会生成主键？

20. MySQL分库分表了解过吗？

21. MySQL的redo日志和undo日志分别有什么用？

22. MySQL的redo日志刷盘时机

23. MySQL有哪些锁？以及各种锁的作用？

24. MySQL有哪些日志？分别是什么用处？

25. 在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢？

26. 为什么要尽量设定一个主键？

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

28. 字段为什么要求定义为not null？

29. varchar(10)和int(10)代表什么含义？

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

31. 什么是存储过程？有哪些优缺点？

32. 说一说三个范式

33. 什么情况下应不建或少建索引

34. 什么是表分区？

35. 表分区与分表的区别

36. 表分区有什么好处？

37. MVCC了解过吗？

38. 在MVCC并发控制中，读操作可以分成哪几类？

39. 行级锁定的优点

40. 行级锁定的缺点

41. MySQL优化（重要）

42. key和index的区别

43. delete、truncate、drop区别

44. MySQL主从复制原理流程

45. 自增主键最大ID记录，MyISAM和InnoDB分别是如何存储的？

46. MySQL如何优化DISTINCT？

47. 解释MySQL外连接、内连接与自连接的区别

相关