MySQL DBA面试笔试系列漫步coding
一般来讲在面试当中， 关于数据库相关的面试题频率出现比较高的几个关键词是SQL优化、索引、存储引擎、事务、死锁、乐观锁、悲观锁、关系型数据库和非关系数据库对比等等。 把这几个点问完基本也差不多10~20分钟了(一般一轮面试1小时左右), 基本这些可以让面试官对你的数据库知识有一定的了解了。
如果你线上运维经验, 一般也会问一些比如数据库扩容, 如何给大表加索引, 如何在业务高峰是给一个大表添加字段等。
MySQL数据库基础知识
1、平时MySQL主要用哪个版本
出现概率: ★★★★
可以说说自己用的MySQL版本, 比如我自己用的版本是5.7版本，然后可以也简单聊聊这个版本的一些特点:
1)、安全性
在MySQL 5.7中，有不少安全性相关的改进。包括：
MySQL数据库初始化完成以后，会产生一个 root@localhost 用户，从MySQL 5.7开始，root用户的密码不再是空，而是随机产生一个密码，这也导致了用户安装5.7时发现的与5.6版本比较大的一个不同点。
MySQL官方已经删除了test数据库，默认安装完后是没有test数据库的，就算用户创建了test库，也可以对test库进行权限控制了 MySQL 5.7版本提供了更为简单SSL安全访问配置，并且默认连接就采用SSL的加密方式。
可以为用户设置密码过期策略，一定时间以后，强制用户修改密码。
2)、灵活性
MySQL 5.7的两个全新的功能，即JSON和generate column
1 2 | CREATE TABLE t1 (jdoc JSON); INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}'); |
3)、可用性
在线设置复制的过滤规则，不再需要重启MySQL，只需要停止SQL thread，修改完成以后，启动SQL thread
这个主要大致谈谈你自己的理解， 也可以根据自己的理解多延展一些， 提高面试的加分。
2、数据库三大范式是什么
出现概率: ★★★
第一范式：每个列都不可以再拆分。
第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。
在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。
3、MySQL有关权限的表都有哪几个
出现概率: ★★★
MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：
user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。db权限表：记录各个帐号在各个数据库上的操作权限。table_priv权限表：记录数据表级的操作权限。columns_priv权限表：记录数据列级的操作权限。host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。
4、mysql的binlog有有几种录入格式-分别有什么区别
出现概率: ★★★
MySQL的binlog有三种格式: statement，row和mixed。
1)、statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
2)、row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。
3)、mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。
此外，新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。
平时用到哪些关系型数据库和非关系数据库-可以谈谈你对它们的理解吗
出现概率: ★★★★★
主要讲讲你用过的关系型数据库比如MySQL, 非关系数据库(NoSql数据库)比如Redis, MongoDB等等。
1)、事务方面
关系型数据库的最大特点就是事务的一致性, 所以对于订单模型 对一致性要求比较高的还是建议用MySQL。
2)、关系数据库的另一个特点就是其具有固定的表结构
其实在业务模型中, 表结构固定反而是一件好事, 没有约束的模型 更容易出问题。
3)、复杂SQL，特别是多表关联查询
NoSql是不支持JOIN 这种查询的。
4)、索引方式
关系型数据库：B树、哈希等
NoSql：键值索引
5)、并发支持
关系型数据库：通过事务和锁来支持并发，高并发情况下，执行效率较低。
NoSql：打破了传统关系型数据库范式的约束和事务一致性，因此并发性能高。
当然自己也可以多延伸看一下， 毕竟这个面试出现的概率还是蛮高的。
5、可以简单说说你对mysql的逻辑架构了解吗
出现概率: ★★★
第一层是服务器层，主要提供连接处理、授权认证、安全等功能。
第二层实现了 MySQL 核心服务功能，包括查询解析、分析、优化、缓存以及日期和时间等所有内置函数，所有跨存储引擎的功能都在这一层实现，例如存储过程、触发器、视图等。
第三层是存储引擎层，存储引擎负责 MySQL 中数据的存储和提取。服务器通过 API 与存储引擎通信，这些接口屏蔽了不同存储引擎的差异，使得差异对上层查询过程透明。除了会解析外键定义的 InnoDB 外，存储引擎不会解析 SQL，不同存储引擎之间也不会相互通信，只是简单响应上层服务器请求。
6、了解mysql中的mvcc是什么
出现概率: ★★★
MVCC 是多版本并发控制，在很多情况下避免加锁，大都实现了非阻塞的读操作，写操作也只锁定必要的行。
InnoDB 的MVCC 通过在每行记录后面保存两个隐藏的列来实现，这两个列一个保存了行的创建时间，一个保存行的过期时间间。不过存储的不是实际的时间值而是系统版本号，每开始一个新的事务系统版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。
MVCC 只能在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作，因为 READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行，而 SERIALIZABLE 则会对所有读取的行都加锁。
7、postgresql相对于mysql的优势
出现概率: ★★★★
- 在SQL的标准实现上要比MySQL完善，而且功能实现比较严谨；
- 存储过程的功能支持要比MySQL好，具备本地缓存执行计划的能力；
- 对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强；
- PG主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。
- PG的主备复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。
8、postgresql和mysql的一些区别
出现概率: ★★★★
这个其实出现的概率还比较高， 自己可以说几点就行了
MySQL不支持地理数据类型。
从9.2开始，PG支持json数据类型。相对于MySQL来说，PG对json的支持比较先进。他有一些json指定的操作符和函数，是的搜索json文本非常高效。9.4开始，可以以二进制的格式存储json数据，支持在该列上进行全文索引（GIN索引），从而在json文档中进行快速搜索。
从5.7开始，MySQL支持json数据类型，比PG晚。也可以在json列上建立索引。然而对json相关的函数的支持比较有限。不支持在json列上全文索引。由于MySQL对SQL支持的限制，在存储和处理json数据方面，MySQL不是一个很好的选择。
MySQL索引
1、索引有哪些使用场景
出现概率: ★★★★★
1)、应该创建索引的场景
- 主键应该创建主键索引。
- 频繁作为查询条件的字段应该创建索引。
- 查询中需要与其他表进行关联的字段应该创建索引。
- 需要排序的字段应该创建索引。
- 需要统计或分组的字段应该创建索引。
- 优先考虑创建复合索引。
2)、不应创建索引的场景
- 数据记录较少的表。
- 经常需要增删改操作的字段。
- 数据记录重复较多且分布平均的字段（如性别、状态等）。
索引的选择性是指索引列中不同值的数目与表中记录总数的比。
索引的选择性越接近于1，创建索引的价值就越高。反之就越低。
2、索引的数据结构-b-树-hash
出现概率: ★★★★★
从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。这里所描述的是索引存储时保存的形式，MySQL默认采用的B+Tree， 这里主要讲讲B+树的特点:
1.非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
2.叶子节点包含所有索引字段
3.叶子节点用指针连接，提高区间访问的性能 （快速定位范围查询，例如查询大于20，第一次io从根节点查询三次定位到20，然后通过后面的指针查询大于20的数据，就不用再从根节点的重新再查询，提高性能，叶子节点开始结束节点也是用指针连接串起来的）
3、创建索引的原则是什么
出现概率: ★★★★
1)、选择唯一性索引
2)、为经常需要排序、分组和联合操作的字段建立索引
3)、为常作为查询条件的字段建立索引
4)、限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
5)、尽量使用数据量少的索引
如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。
6)、尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
7)、最左前缀匹配原则
8)、查询时使用计算，会导致索引失效
4、使用索引查询一定能提高查询的性能吗-为什么
出现概率: ★★★★
不是所有的查询使用查询都能提高性能， 比如下面几个案例
- 像 like % xxx% 、不满足最左匹配原则的情况下并不能使用到建好的索引
- MySQL 在可以使用多个索引的情况下，查询优化器会根据查询范围的数据量估算索引代价，最坏的是估算完毕后，发现这些索引的字段区分度不高，还不如扫全表，于是 Mysql 扫全表了
- 如果索引的列比需要查询的列少，Mysql 会通过聚簇索引回表查询其他字段
- 如果索引的字段很大，每个页能存的条目就很少，读取时 IO 会消耗更多，页 Buffer 轮替的更快
5、索引有哪些优缺点
出现概率: ★★★
1)、索引的优点
可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
2)、索引的缺点
时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
空间方面：索引需要占物理空间。
6、讲一讲聚簇索引与非聚簇索引
出现概率: ★★★★
在 InnoDB 里，索引B+Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引，即将数据存储与索引放到了一块，找到索引也就找到了数据。
而索引B+Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引、二级索引。
聚簇索引与非聚簇索引的区别：
非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键（行号）
对于InnoDB来说，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。第一次索引一般是顺序IO，回表的操作属于随机IO。需要回表的次数越多，即随机IO次数越多，我们就越倾向于使用全表扫描 。
通常情况下， 主键索引（聚簇索引）查询只会查一次，而非主键索引（非聚簇索引）需要回表查询多次。当然，如果是覆盖索引的话，查一次即可
注意：MyISAM无论主键索引还是二级索引都是非聚簇索引，而InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引。
7、百万级别或以上的数据如何删除
出现概率: ★★★
方法一:
索引是单独的文件，增删改时，当存在索引，会消耗额外io。删除速度和索引数量成正比
- 删除索引
- 删除需要删除的数据
- 重新建立索引
这个方法有一个很明显的缺点, 就是在正式环境这个表如果访问频率比较高的话， 删除索引后有大量的SQL查询会导致数据库IO和CPU特别高
方法二:
之前我在正式环境的做法是 将删除任务拆分为一次删除1w条， 然后把删除任务重新压入的异步任务队列里面。
8、什么是最左前缀原则-什么是最左匹配原则
出现概率: ★★★★
在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，示例： 对列col1、列col2和列col3建一个联合索引
1 | KEY test_col1_col2_col3 on test(col1,col2,col3); |
联合索引 test_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。
1 | SELECT * FROM test WHERE col1=“1” AND clo2=“2” AND clo4=“4” |
上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。
9、数据库为什么使用b-树而不是b树
出现概率: ★★★
B树和B+树的区别主要有两点：
- 在B树中，你可以将键和值存放在内部节点和叶子节点，但在B+树中，内部节点都是键，没有值。叶子节点同时存放键和值
- B+树的叶子节点有一条链相连，而B+树的叶子节点各自独立。
使用B+树的好处
由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。 B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间。
数据库的数据读取都是需要进行代价巨大的磁盘IO操作，因此，更快地缩小范围和更少的读取次数是数据库需要关注的重点。而B+树在这些点上比B树做的更好。这就是为什么数据库要选用B+树作为底层实现。
10、非聚簇索引一定会回表查询吗
出现概率: ★★★
不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。一个索引包含（覆盖）所有需要查询字段的值，被称之为"覆盖索引"。
举个简单的例子，假设我们在学生表的成绩上建立了索引，那么当进行select score from student where score > 90的查询时，在索引的叶子节点上，已经包含了score 信息，不会再次进行回表查询。
MySQL存储引擎
1、可以简单谈谈MySQL存储引擎MyISAM与InnoDB区别
出现概率: ★★★★
1)、关于 InnoDB
InnoDB 是 MySQL 的默认事务型引擎，用来处理大量短期事务。InnoDB 的性能和自动崩溃恢复特性使得它在非事务型存储需求中也很流行，除非有特别原因否则应该优先考虑 InnoDB。
InnoDB 的数据存储在表空间中，表空间由一系列数据文件组成。MySQL4.1 后 InnoDB 可以将每个表的数据和索引放在单独的文件中。
InnoDB 采用 MVCC 来支持高并发，并且实现了四个标准的隔离级别。其默认级别是 REPEATABLE READ，并通过间隙锁策略防止幻读，间隙锁使 InnoDB 不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定防止幻行的插入。
InnoDB 表是基于聚簇索引建立的，InnoDB 的索引结构和其他存储引擎有很大不同，聚簇索引对主键查询有很高的性能，不过它的二级索引中必须包含主键列，所以如果主键很大的话其他所有索引都会很大，因此如果表上索引较多的话主键应当尽可能小。
InnoDB 的存储格式是平立的，可以将数据和索引文件从一个平台复制到另一个平台。
InnoDB 内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建加速读操作的自适应哈希索引，以及能够加速插入操作的插入缓冲区等。
2)、关于MyISAM
MySQL5.1及之前，MyISAM 是默认存储引擎，MyISAM 提供了大量的特性，包括全文索引、压缩、空间函数等，但不支持事务和行锁，最大的缺陷就是崩溃后无法安全恢复。对于只读的数据或者表比较小、可以忍受修复操作的情况仍然可以使用 MyISAM。
MyISAM 将表存储在数据文件和索引文件中，分别以 .MYD 和 .MYI 作为扩展名。MyISAM 表可以包含动态或者静态行，MySQL 会根据表的定义决定行格式。MyISAM 表可以存储的行记录数一般受限于可用磁盘空间或者操作系统中单个文件的最大尺寸。
MyISAM 对整张表进行加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但是在表有读取查询的同时，也支持并发往表中插入新的记录。
对于MyISAM 表，MySQL 可以手动或自动执行检查和修复操作，这里的修复和事务恢复以及崩溃恢复的概念不同。执行表的修复可能导致一些数据丢失，而且修复操作很慢。
对于 MyISAM 表，即使是 BLOB 和 TEXT 等长字段，也可以基于其前 500 个字符创建索引。MyISAM 也支持全文索引，这是一种基于分词创建的索引，可以支持复杂的查询。
MyISAM 设计简单，数据以紧密格式存储，所以在某些场景下性能很好。MyISAM 最典型的性能问题还是表锁问题，如果所有的查询长期处于 Locked 状态，那么原因毫无疑问就是表锁。
2、myisam索引与innodb索引的区别
出现概率: ★★★★
MyISM和InnoDB索引都是由B+树实现的，但在索引管理数据方式上却有所不同。
1)、InnoDB是聚集索引，数据文件是和（主键）索引绑在一起的，即索引 + 数据 = 整个表数据文件，通过主键索引到整个记录，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，因为辅助索引是以建索引的字段为关键字索引到主键，所以需要两次，先查询到主键，然后再通过主键查询到数据。
主键索引：以主键索引到整条记录
辅助索引：以另一字段索引到主键
2)、MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。
主键索引：以关键字索引到记录的地址
辅助索引：以某字段索引到记录地址
3、InnoDB引擎的4大特性
出现概率: ★★★
1)、插入缓冲 （Insert Buffer/Change Buffer）
插入缓存之前版本叫insert buffer，现版本 change buffer，主要提升插入性能，change buffer是insert buffer的加强，insert buffer只针对insert有效，change buffering对insert、delete、update(delete+insert)、purge都有效。
2)、双写机制（Double Write）
在InnoDB将BP中的Dirty Page刷（flush）到磁盘上时，首先会将（memcpy函数）Page刷到InnoDB tablespace的一个区域中，我们称该区域为Double write Buffer（大小为2MB，每次写入1MB，128个页，每个页16k,其中120个页为后台线程的批量刷Dirty Page，还有8个也是为了前台起的sigle Page Flash线程，用户可以主动请求，并且能迅速的提供空余的空间）。在向Double write Buffer写入成功后，第二步、再将数据分别刷到一个共享空间和真正应该存在的位置。
3)、自适应哈希索引（Adaptive Hash Index，AHI）
哈希算法是一种非常快的查找方法，在一般情况（没有发生hash冲突）下这种查找的时间复杂度为O(1)。InnoDB存储引擎会监控对表上辅助索引页的查询。如果观察到建立hash索引可以提升性能，就会在缓冲池建立hash索引，称之为自适应哈希索引（Adaptive Hash Index，AHI）。
4)、预读 （Read Ahead）
预读（read-ahead)操作是一种IO操作，用于异步将磁盘的页读取到buffer pool中，预料这些页会马上被读取到。预读请求的所有页集中在一个范围内。
MySQL事务
1、 什么是数据库事务？
出现概率: ★★★★★
简单来说：数据库事务可以保证多个对数据库的操作（也就是 SQL 语句）构成一个逻辑上的整体。构成这个逻辑上的整体的这些数据库操作遵循：要么全部执行成功,要么全部不执行 。
1 2 3 4 5 6 | # 开启一个事务 START TRANSACTION; # 多条 SQL 语句 SQL1,SQL2... ## 提交事务 COMMIT; |
事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账 1000 元，这个转账会涉及到两个关键操作就是：
将小明的余额减少 1000 元 将小红的余额增加 1000 元。 事务会把这两个操作就可以看成逻辑上的一个整体，这个整体包含的操作要么都成功，要么都要失败。
这样就不会出现小明余额减少而小红的余额却并没有增加的情况。
2、事物的四大特性-acid-介绍一下
出现概率: ★★★★
ACID，指数据库事务正确执行的四个基本要素的缩写。包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。一个支持事务（Transaction）的数据库，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易方的要求。
3、什么是脏读-幻读-不可重复读
出现概率: ★★★★
1、脏读
脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。例如：用户A向用户B转账100元，对应SQL命令如下
1 2 | update account set money=money+100 where name=’B’; (此时A通知B) update account set money=money - 100 where name=’A’; |
当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读），而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。
2、不可重复读 不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。 例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发生了不可重复读。
不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。 在某些情况下，不可重复读并不是问题，比如我们多次查询某个数据当然以最后查询得到的结果为主。但在另一些情况下就有可能发生问题，例如对于同一个数据A和B依次查询就可能不同，A和B就可能打起来了……
3、虚读(幻读)
幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。
