MySQL常见面试问题整理（一）：MySQL基础

大概的MySQL面试常见问题见：
MySQL索引连环18问
MySQL基础、锁、事务、分库分表、优化
文章在此基础上查找了一些其他资料帮助理解

1. 说一说三大范式、五大约束。

首先三大范式

1. 第一大范式：原子性。强调的是列的原子性，即数据库中每一列的字段都是单一属性，不可再分的。并且这个单一属性必须是由基本的数据类型所构成的，如整数、字符串等。
2. 第二大范式：依赖性。在满足1NF的基础上再满足依赖性的两个约束：一张表必须有一个主键；非主键类必须完全依赖于主键，而不能只依赖主键的一部分。（完整依赖）
3. 第三大范式：在满足2NF的基础上，另外再满足一个条件，即非主键列必须直接依赖于主键，不能存在传递依赖。（直接依赖）

但是在我们的日常开发当中，并不是所有的表一定要满足三大范式，有时候冗余几个字段可以少关联几张表，带来的查询效率的提升有可能是质变的

其次五大约束

1. PRIMARY KEY(primary key):设置主键约束。主键约束指定表的一列或几列的组合的值在表中具有惟一性，即能惟一地指定一行记录。主键默认非空，默认唯一性约束，只有主键可以设置自动增长（主键不一定自增，自增一定是主键）。
   如果是单列主键，IMAGE 和TEXT 类型的列不能被指定为主关键字，也不允许指定主关键字列有NULL 属性。
   多列组成的主键叫联合主键，而且联合主键约束只能设定为表级约束；单列组成的主键，既可设定为列级约束，也可以设定为表级约束。（列级约束是定义在列属性中的，而表级约束是定义在列之后的，两者本质上没什么区别，而如果你的约束需要同时对多列进行约束那么就只能采用表级约束）

2. UNIQUE(unique)：设置唯一性约束，不能有重复值，但可以为空。

3. DEFAULT(default)：默认值约束，如果某属性不填 则设置为默认值

4. NOT NULL(not null)：设置非空约束，该字段不能为空；

5. FOREIGN KEY (foreign key):设置外键约束。当需要建立两表间的关系时使用。使用外键时需要注意只有INNODB的数据库引擎支持外键；外键与参照列的数据类型必须相同。（数值型要求长度和无符号都相同，字符串要求类型相同，长度可以不同）；设置外键的字段必须要有索引，如果没有索引，设置外键时会自动生成一个索引；外键可以预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
   当对参照表的参照字段进行删除或更新时，外键表中的外键需要进行相对的操作，如下：

RESTRICT: 拒绝对参照字段的删除或修改（默认）； 　　
NO ACTION:与RESTRICT相同，但这个指令只在MySql生效；　
CASCADE: 删除或更新参照表的参照字段时，外键表的记录同步删除或更新； 　　
SET NULL:删除删除或更新参照表的参照字段时，外键表的外键设为NULL (此时外键不能设置为NOT NULL)。

2. 超键、候选键、主键、外键分别是什么？

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
候选键：是最小超键，即没有冗余元素的超键。
主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
外键：在一个表中存在的另一个表的主键称此表的外键。

即超键>候选键>主键

创建简单的两个表，说明一下各个键！

学生信息（学号 身份证号 性别 年龄 身高 体重 宿舍号）和 宿舍信息（宿舍号 楼号）

超键：只要含有“学号”或者“身份证号”两个属性的集合就叫超键，例如R1（学号 性别）、R2（身份证号 身高）、R3（学号
身份证号）等等都可以称为超键！

候选键：不含有多余的属性的超键，比如（学号）、（身份证号）都是候选键，又比如R1中学号这一个属性就可以唯一标识元组了，而有没有性别这一属性对是否唯一标识元组没有任何的影响！

主键：就是用户从很多候选键选出来的一个键就是主键，比如你要求学号是主键，那么身份证号就不可以是主键了！

外键：宿舍号就是学生信息表的外键

3. MySQL 中的 varchar 和 char 有什么区别？

char 是一个定长字段,假如申请了char(10)的空间,那么无论实际存储多少内容.该字段都占用 10 个字符,如果少于10个，则用空格填满。

而 varchar 是变长的,也就是说申请的只是最大长度,占用的空间为实际字符长度+1,最后一个字符存储使用了多长的空间。当读取字段值时，MySQL会先读取这个存储字符长度的字节，然后再开始获取字段中的数据。但是由于一个字节最多能表示的存入字符是是255个（1个字节能表示的最大正数），当需要存入更多数据时，MySQL会自动扩容，使用2个字节记录“实际存入的字符数量”，能够表示的最大正数就是65535，并且，不会再扩容，所以，使用varchar最多存储65535个字符！

使用char类型存储的字符串的长度一定是固定的（即使不固定，也会添加空格，使得该字段的所有字符串的长度都与字段的设计值保持一致），可以直接读取，所以在检索效率上来讲,char > varchar,因此在使用中,如果不确定某个字段的值的长度,可以使用 varchar,否则应该尽量使用 char.例如存储用户 MD5 加密后的密码,则应该使用 char。

4. MySQL中 in 和 exists 区别

用一个例子来理解in 和 exists ，假设有A\B两表，A表2759174行数据（大表）、B表7262行数据（小表），假设B.id 、 A.id均存在索引。
当执行下面两个语句时，

SELECT * FROM A WHERE A.id IN (SELECT id FROM B); //q1
SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id); //q2

1. in查询相当于多个or条件的叠加。比如对于q1，假设B表的所有id为(1,2,3)，查询1可以转换为：

SELECT * FROM A WHERE A.id = 1 OR A.id = 2 OR A.id = 3;

这里主要是用到了A的索引，B表如何对查询影响不大。

2. exists先将内表（A表）数据逐条取出，然后对外表（B表）数据用loop逐条查询，每次查询都会查看exists的条件语句，当exists里的条件语句能够返回记录行时（无论记录行是的多少，只要能返回），条件就为真，返回当前loop到的这条记录；反之，如果exists里的条件语句不能返回记录行，则当前loop到的这条记录被丢弃。

for (i = 0; i < count(A); i++) {
    a = get_record(A, i); #从A表逐条获取记录
    if (B.id = a[id]) { #如果子条件成立
        result[] = a;
    }
}
return result;

这里主要用到了B表的索引，A表如何对查询的效率影响不大。

所以在上面这个例子当中，q2的查询性能明显由于q1，因为它用到B表的索引，而B表小于A表。

当不考虑索引时，规律就是大表连小表，用in。小连大用exist。（看上面in和exists的具体逻辑）

总结而言：

MySQL中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

如果查询的两个表大小相当，那么用in和exists差别不大。 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。 not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

5.SQL语句主要分为哪几类?

数据定义语言DDL（Data Ddefinition Language）CREATE，DROP，ALTER主要为以上操作 即对逻辑结构等有操作的，其中包括表结构，视图和索引。

数据查询语言DQL（Data Query Language）SELECT 这个较为好理解 即查询操作，以select关键字。各种简单查询，连接查询等 都属于DQL。

数据操纵语言DML（Data Manipulation Language）INSERT，UPDATE，DELETE主要为以上操作 即对数据进行操作的，对应上面所说的查询操作 DQL与DML共同构建了多数初级程序员常用的增删改查操作。而查询是较为特殊的一种 被划分到DQL中。

数据控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK 主要为以上操作 即对数据库安全性完整性等有操作的，可以简单的理解为权限控制等。

6.drop、delete与truncate的区别

7.为什么表数据删掉一半，表文件大小不变？

针对MySQL中应用最广泛的InnoDB引擎展开讨论。一个InnoDB表包含两部分，即：表结构定义和数据。在MySQL 8.0版本以前，表结构是存在以.frm为后缀的文件里。而MySQL 8.0版本，则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小，所以主要讨论表数据。

主要讨论为何简单地删除表数据达不到表空间回收的效果，以及正确回收空间的方法。

参数innodb_file_per_table设置为OFF，表空间无法回收

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数
innodb_file_per_table控制的：

1. 这个参数设置为OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一
   起；
2. 这个参数设置为ON表示的是，每个InnoDB表数据存储在一个以 .ibd为后缀的文件中。
   从MySQL 5.6.6版本开始，它的默认值就是ON了。

建议不论使用MySQL的哪个版本，都将这个值设置为ON。因为，一个表单独存储为一个文
件更容易管理，而且在你不需要这个表的时候，通过drop table命令，系统就会直接删除这个文
件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

增删数据导致表的空洞，表空间无法正确回收

InnoDB里的数据都是用B+树的结构组织的。

假设，我们要删掉R4这个记录，InnoDB引擎只会把R4这个记录标记为删除。如果之后要再插入一个ID在300和600之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。不过如果插入的是一个ID是800的行，就不能复用这个位置了。

如果我们删掉了一个数据页上的所有记录，整个数据页就可以被复用了。而且数据页的复用跟记录的复用是不同的。记录的复用，只限于符合范围条件的数据，但是当整个页从B+树里面摘掉以后，可以复用到任何位置。以图1为例，如果将数据页page A上的所有记录删除以后，page A会被标记为可复用。这时候如果要插入一条ID=50的记录需要使用新页的时候，page A是可以被复用的。

数据页被标记为可复用的可能情况：

1. 如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一
   个数据页就被标记为可复用。
2. 如果用delete命令把整个表的数据删除,所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。

delete命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过delete命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

不止是删除数据会造成空洞，插入数据也会。

如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造
成索引的数据页分裂。按照 B+ 树的算法，如果插入行需要插入的数据页还有空间，需要逻辑上挪动后面的数据，空出位置。而如果当前数据页已经没有位置，就需要进行页分裂，即申请一个新的数据页，然后挪动部分数据到新数据页过去。举例如下：

页分裂完成后，page A的末尾就留下了空洞（注意：实际上，可能不止1个记录的位置是空洞）

除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，插入一条记录竟然使得整体空间利用率降低大约 50%。

另外，更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。不难理解，这也是会造
成空洞的。也就是说，经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。

通过重建表收缩表空间

重建表可以去除空洞。新建一个与表A结构相同的表B，然后按照主键ID递增的顺序，把数据一行一行地从表A里读出来再插入到表B中。由于表B是新建的表，所以表A主键索引上的空洞，在表B中就都不存在了。显然地，表B的主键索引更紧凑，数据页的利用率也更高。如果我们把表B作为临时表，数据从表A导入表B的操作完成后，用表B替换A，从效果上看，就起到了收缩表A空间的作用。

重建表以MySQL 5.5版本为分水岭，可分为两个阶段

1. 在MySQL 5.5版本之前，使用alter table A engine=InnoDB命令来重建表，MySQL会自动完成转存数据、交换表名、删除旧表的操作，不需要自己创建临时表B，如下图：
   但是在往临时表插入数据的过程存在问题，如果此时有新的数据要写入到表A的话，就会造成数据丢失。因此，在整个DDL过程中，表A中不能有更新。也就是说，这个DDL不是Online的。

2. 在MySQL 5.6版本开始引入的Online DDL，对这个操作流程做了优化。此时重建表流程如下：
   
   1） 建立一个临时文件，扫描表A主键的所有数据页；
   2）用数据页中表A的记录生成B+树，存储到临时文件中；
   3）生成临时文件的过程中，将所有对A的操作记录在一个日志文件（rowlog）中，对应的是图
   中state2的状态；
   4）临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的
   数据文件，对应的就是图中state3的状态；
   5） 用临时文件替换表A的数据文件

由于日志文件记录和重放操作这个功能的存在，这个方案在重建表的过程中，允许对表A做增删改操作。这也就是Online DDL名字的来源。

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

存储过程是一些预编译的 SQL 语句。（可以理解成等待调用的、预编译好的函数）

1、更加直白的理解：存储过程可以说是一个记录集，它是由一些 T-SQL 语句组成的代码块，这些 T-SQL 语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

2、存储过程是一个预编译的代码块，执行效率比较高,一个存储过程替代大量 T_SQL 语句 ，可以降低网络通信量，提高通信速率,可以一定程度上确保数据安全（通过存储过程限制对基础数据的访问）

但是,在互联网项目中,其实是不太推荐存储过程的,比较出名的就是阿里的《Java 开发手册》中禁止使用存储过程,我个人的理解是,在互联网项目中,迭代太快,项目的生命周期也比较短,人员流动相比于传统的项目也更加频繁,在这样的情况下,存储过程的管理确实是没有那么方便,同时,复用性也没有写在服务层那么好。

9.不同的count性能区别（count(*)、count(1)、count(主键)、count(非主键字段)）

先说结论：

基于常用的 innodb 存储引擎来说明。count() 是一个聚合函数，函数的参数不仅可以是字段名，也可以是其他任意表达式，该函数作用是统计符合查询条件的记录中，函数指定的参数不为 NULL 的记录有多少个。

在通过 count 函数统计有多少个记录时，MySQL 的 server 层会维护一个名叫 count 的变量。server 层会循环向 InnoDB 读取一条记录，如果 count 函数指定的参数不为 NULL，那么就会将变量 count 加 1，直到符合查询的全部记录被读完，就退出循环。最后将 count 变量的值发送给客户端。

1. count(1) 执行过程,用select count(1) from t_order;这条语句作为例子：
   如果表里只有主键索引，没有二级索引,那么，InnoDB 循环遍历聚簇索引（主键索引），将读取到的记录返回给 server 层，但是不会读取记录中的任何字段的值，因为 count 函数的参数是 1，不是字段，所以不需要读取记录中的字段值。参数 1 很明显并不是 NULL，因此 server 层每从 InnoDB 读取到一条记录，就将 count 变量加 1。但是，如果表里有二级索引时，InnoDB 循环遍历的对象就二级索引了。这是因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间，所以二级索引树比聚簇索引树小，这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小，因此「优化器」优先选择的是二级索引。
2. count(*) 执行过程。count(*) 其实等于 count(0)，也就是说，当你使用 count(*) 时，MySQL 会将 * 参数转化为参数 0 来处理。(虽然对于selete * 这条语句来说，*代表读取记录中的所有字段值，但在这里不是这个意思)。所以，count(*) 执行过程跟 count(1) 执行过程基本一样的，性能没有什么差异。
3. count(主键字段) 执行过程,用select count(id) from t_order;//id 为主键值 为例；
   如果表里只有主键索引，没有二级索引时，那么，InnoDB 循环遍历聚簇索引，将读取到的记录返回给 server 层，然后读取记录中的 id 值，就会 id 值判断是否为 NULL，如果不为 NULL，就将 count 变量加 1。count(1) 相比 count(主键字段) 少一个步骤，就是不需要读取记录中的字段值，所以通常会说 count(1) 执行效率会比 count(主键字段) 高一点。但是，如果表里有二级索引时，InnoDB 循环遍历的对象就不是聚簇索引，而是二级索引。
4. count(字段) 执行过程。对于没有索引的普通字段查询来说，会采用全表扫描的方式来计数，所以它的执行效率是比较差的。

MySQL 会对 count(*) 和 count(1) 有个优化，如果有多个二级索引的时候，优化器会使用key_len 最小的二级索引进行扫描。只有当没有二级索引的时候，才会采用主键索引来进行统计。

10.关于buffer pool

buffer pool 是一块内存区域，为了「提高数据库的性能」，当数据库操作数据的时候，把硬盘上的数据加载到 buffer pool，不直接和硬盘打交道，操作的是 buffer pool 里面的数据，数据库的增删改查都是在 buffer pool 上进行

buffer pool 里面缓存的数据内容也是一个个数据页

其中「有三大双向链表」:

1. 「free 链表」
   用于帮助我们找到空闲的缓存页
2. 「flush 链表」
   用于找到脏缓存页，也就是需要刷盘的缓存页
3. 「lru 链表」
   用来淘汰不常被访问的缓存页，分为热数据区和冷数据区，冷数据区主要存放那些不常被用到的数据
   预读机制:

Buffer Pool 有一项特技叫预读，存储引擎的接口在被 Server 层调用时，会在响应的同时进行预判，将下次可能用到的数据和索引加载到 Buffer Pool

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

主键用来唯一标识出在一张表中的某一条数据，在数据库中设置了主键约束的列数据库引擎会保证其唯一性。为了应对业务场景的变化，通常将主键的值设置为与业务无关的量，也就是自增id和UUID。

自增ID简单的说就是在向表中插入一条数据时不用自己设置id的值，数据库引擎会自动根据表中的数据的id+1进行填充。由于是数据库引擎负责生成的主键id，因此不需要我们自己处理主键重复的问题，同时生成比较简单，速度快，并且因为是增量生成的，在插入和查找数据的时候可以比使用随机的值具有更好的性能。

如果是UUID使用随机的值作为主键，以MySQL为例，数据存储在主索引（B+树）的叶子节点上，因此同一叶子节点的各条数据继续需要按主键顺序进行存储，当由一条新的数据插入的时候，如果向新数据的主键处于已有数据主键的中间值，可能需要为了将新数据插入到对应的位置而移动数据，会增加额外的开销。

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

总之，在数据量大一些的情况下，用自增主键性能会好一些。关于主键是聚簇索引，如果没有主键，InnoDB会选择一个唯一键来作为聚簇索引，如果没有唯一键，会生成一个隐式的主键。

自增ID与UUID的比较

1. 自增ID是有序的，而UUID是随机的。前面已经说了，如果主键是有序的，数据库可以具有更好的性能（至少对MySQL而已是如此）
2. 自增ID所需的存储空间比UUID要小
3. 由于自增ID比UUID更加简单，因此生成自增ID的生成速度也比UUID更快
4. 自增ID与数据相关，主键会暴露出去的话，自增ID会显示当前表中的数据规模；而UUID则无此风险
5. 自增ID在不同的数据库中可能重复，在分布式的环境下无法保证唯一。而UUID在分布式环境下也可以保证唯一

何时使用自增ID，何时使用UUID

如果数据量非常大需要分库，或者需要更好的安全性，那么使用UUID
对于非敏感数据或者数据量没有大需要分库，使用自增id能节省存储空间并获得更好的性能

12.什么是MySQL的GTID？

TID（Global Transaction ID，全局事务ID）是全局事务标识符，是一个已提交事务的编号，并且是一个全局唯一的编号。

GTID是从MySQL 5.6版本开始在主从复制方面推出的重量级特性。

GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。

GTID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。

GTID有如下几点作用：

1. 根据GTID可以知道事务最初是在哪个实例上提交的。

2. GTID的存在方便了Replication的Failover。

3. 因为不用像传统模式复制那样去找master_log_file和master_log_pos。

4. 基于GTID搭建主从复制更加简单，确保每个事务只会被执行一次。

参考：

【1】公众号【moon聊技术 】：MySQL 常见面试题总结！
【2】Mysql45讲—极客时间 林晓斌
【3】事务的特性——原子性（实现原理）
【4】数据库事务原子性、一致性是怎样实现的？
【5】MySQL 中的WAL机制
【6】数据库——彻底明白超键、候选键、主键、外键
【7】MySQL中In与Exists的区别
【8】MySQL 16 问！IT邦德
【9】小林coding
【10】uuid与自增id的区别与使用
【11】超详细的mysql数据库GTID介绍—概念、优缺点、原理、生命周期等（好文章！！）