【巨人的肩膀】MySQL面试总结（一）

💪

1、什么是ER图

ER 图 全称是 Entity Relationship Diagram（实体联系图），提供了表示实体类型、属性和联系的方法。

• 实体 ：通常是现实世界的业务对象，当然使用一些逻辑对象也可以。比如对于一个校园管理系统，会涉及学生、教师、课程、班级等等实体。在 ER 图中，实体使用矩形框表示。

• 属性 ：即某个实体拥有的属性，属性用来描述组成实体的要素，对于产品设计来说可以理解为字段。在 ER 图中，属性使用椭圆形表示。

• 联系 ：即实体与实体之间的关系，这个关系不仅有业务关联关系，还能通过数字表示实体之间的数量对照关系。例如，一个班级会有多个学生就是一种实体间的联系。

2、数据库范式了解吗

首先回答范式的作用：

• 消除重复数据减少冗余数据，从而让数据库内的数据能划分的更合理，让磁盘空间得到更有效利用的一种标准化标准
• 消除潜在的异常（插入异常，更新异常，删除异常）

数据库范式有 3 种：

• 1NF(第一范式)：属性不可再分。要求数据库表的每一列都是不可分割的原子数据项。
• 2NF(第二范式)：1NF 的基础之上，消除了非主属性对于码的部分函数依赖。在满足第一范式的前提下每张表只描述一件事情
• 3NF(第三范式)：3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖 。在满足第一和第二范式的前提下，第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

第一范式1NF：

​ 属性（对应于表中的字段）不能再被分割，也就是这个字段只能是一个值，不能再分为多个其他的字段了。1NF 是所有关系型数据库的最基本要求 ，也就是说关系型数据库中创建的表一定满足第一范式。

第二范式2NF：

​ 第二范式在第一范式的基础上增加了一个列，这个列称为主键，非主属性都依赖于主键。

第三范式3NF：

​ 3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖 。符合 3NF 要求的数据库设计，基本上解决了数据冗余过大，插入异常，修改异常，删除异常的问题。

最后回答性能与规范：

• 阿里规范：关联查询的表不得超过三张表
• 越符合三大范式，则表会越来越多，表多了不一定是好事，查询时需要连接多个表，增加查询复杂度，降低数据库的查询性能。因此，并不是应用的范式越高越好，要看实际情况而定。我个人观点认为，大多数情况应用到第三范式已经足够，在一定情况下第二范式或第一范式也是可以的。甚至有时为了提高运行效率，可以让数据冗余(比如数据表里存放了语文数学英语成绩，但是如果在某个时间经常要得到它的总分，每次都要进行计算会降低性能，不如加上总分这个冗余字段)

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

• 超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键

• 候选键：是最小超键，即没有冗余元素的超键

• 主键(主码) ：主键用于唯一标识一个元组(行)，不能有重复，不允许为空。一个表只能有一个主键。

• 外键(外码) ：外键用来和其他表建立联系用，外键是另一表的主键，外键是可以有重复的，可以是空值。一个表可以有多个外键

4、为什么不推荐使用外键与级联

对于外键和级联，阿里巴巴开发手册这样说到：

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

说明: 以学生和成绩的关系为例，学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群; 级联更新是强阻塞，存在数据库更新风暴的风险; 外键影响数据库的插入速度

为什么不要用外键呢？

1. 增加了复杂性： a. 每次做 DELETE 或者 UPDATE 都必须考虑外键约束，会导致开发的时候很痛苦。外键的主从关系是定的，假如哪天需求有变化，数据库中的这个字段根本不需要和其他表有关联的话就会增加很多麻烦。
2. 对分库分表不友好 ：因为分库分表下外键是无法生效的。

但是外键存在即合理，外键也是有好处的，比如：级联操作方便，减轻了程序代码量，所以如果系统不涉及分库分表，并发量不是很高的情况还是可以考虑使用外键的。

5、什么是存储过程

存储过程是一些预编译的 SQL 语句。

1. 更加直白的理解：存储过程可以说是一个记录集，它是由一些 T-SQL 语句组成的代码块，这些 T-SQL 语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了
2. 存储过程是一个预编译的代码块，执行效率比较高,一个存储过程替代大量 T_SQL 语句 ，可以降低网络通信量，提高通信速率,可以一定程度上确保数据安全

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

6、drop、delete与truncate区别

• drop(丢弃数据): drop table 表名 ，直接将表都删除掉，在删除表的时候使用
• truncate (清空数据) : truncate table 表名 ，只删除表中的数据，再插入数据的时候自增长 id 又从 1 开始，在清空表中数据的时候使用
• delete（删除数据） : delete from 表名 where 列名=值，删除某一行的数据，如果不加 where 子句和truncate table 表名作用类似

truncate 和不带 where 子句的 delete、drop 都会删除表内的数据，但是 truncate 和 delete 只删除数据不删除表的结构、执行 drop 语句，此表的结构也会删除，也就是执行 drop 之后对应的表不复存在。

此外，truncate 和 drop 属于 DDL(数据定义语言)语句，操作立即生效，而 delete 语句是 DML (数据库操作语言)语句，事务提交之后才生效。

	Delete	Truncate	Drop
类型	属于DML	属于DDL	属于DDL
回滚	可回滚	不可回滚	不可回滚
删除内容	表结构还在，删除表的全部或者一部分数据行	表结构还在，删除表中的所有数据	从数据库中删除表，所有的数据行、索引和权限也会被删除
删除速度	删除速度慢，需要逐行删除	删除速度快	删除速度最快

DML 语句和 DDL 语句区别：

• DML 是数据库操作语言（Data Manipulation Language）的缩写，是指对数据库中表记录的操作，主要包括表记录的插入、更新、删除和查询，是开发人员日常使用最频繁的操作。
• DDL （Data Definition Language）是数据定义语言的缩写，简单来说，就是对数据库内部的对象进行创建、删除、修改的操作语言。DDL 语句更多的被数据库管理员（DBA）所使用，一般的开发人员很少使用

另外，由于select不会对表进行破坏，所以有的地方也会把select单独区分开叫做数据库查询语言 DQL（Data Query Language）

一般来说，执行速度上：drop > truncate > delete

• drop命令会把表占用的空间全部释放掉，truncate命令执行的时候不会产生数据库日志，因此比delete要快。除此之外，还会把表的自增值重置和索引恢复到初始大小等。delete命令执行的时候会产生数据库的binlog日志，而日志记录是需要消耗时间的，但是也有个好处方便数据回滚恢复

7、数据库设计通常分为那几步

1. 需求分析 : 分析用户的需求，包括数据、功能和性能需求
2. 概念结构设计 : 主要采用 E-R 模型进行设计，包括画 E-R 图
3. 逻辑结构设计 : 通过将 E-R 图转换成表，实现从 E-R 模型到关系模型的转换
4. 物理结构设计 : 主要是为所设计的数据库选择合适的存储结构和存取路径
5. 数据库实施 : 包括编程、测试和试运行
6. 数据库的运行和维护 : 系统的运行与数据库的日常维护

8、什么是关系型数据库

关系型数据库 （RDBMS，Relational Database Management System）就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。关系型数据库中，我们的数据都被存放在了各种表中（比如用户表），表中的每一行就存放着一条数据（比如一个用户的信息）。

常见的关系型数据库：MySQL、Oracle、SQL Server

9、什么是SQL

SQL 是一种结构化查询语言(Structured Query Language)，专门用来与数据库打交道，目的是提供一种从数据库中读写数据的简单有效的方法。几乎所有的主流关系数据库都支持 SQL ，适用性非常强。并且，一些非关系型数据库也兼容 SQL 或者使用的是类似于 SQL 的查询语言。

10、MySQL有什么优点

1. 成熟稳定，功能完善
2. 开源免费
3. 文档丰富，既有详细的官方文档，又有非常多优质文章可供参考学习
4. 开箱即用，操作简单，维护成本低
5. 兼容性好，支持常见的操作系统，支持多种开发语言
6. 社区活跃，生态完善
7. 事务支持优秀， InnoDB 存储引擎默认使用 repeatable-read 并不会有任何性能受损失，并且，InnoDB 实现的 repeatable-read 隔离级别其实是可以解决幻读问题发生的。
8. 支持分库分表、读写分离、高可用

11、一个SQL语句在MySQL中的执行流程

大体来说，MySQL 可以分为 Server 层和存储引擎两部分。

连接器： 身份认证和权限相关(登录 MySQL 的时候)。

查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。

分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。

优化器： 按照 MySQL 认为最优的方案去执行。

执行器： 执行语句，然后从存储引擎返回数据。 执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。

插件式存储引擎 ： 主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎

12、MySQL支持哪些存储引擎，默认使用哪个

MySQL 支持多种存储引擎，你可以通过 show engines 命令来查看 MySQL 支持的所有存储引擎。MySQL 当前默认的存储引擎是 InnoDB。并且，所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 支持事务。

13、MyISAM和InnoDB有什么区别

MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎，可谓是风光一时。MySQL 5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

• InnoDB 支持事务，MyISAM 不支持

• InnoDB 支持外键，而 MyISAM 不支持

• InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高；MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。

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

• InnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度。也就说，MyISAM 一锁就是锁住了整张表，这在并发写的情况下是多么滴憨憨啊！这也是为什么 InnoDB 在并发写的时候，性能更牛皮了！

• MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交(commit)和回滚(rollback)事务的能力。并且，InnoDB 默认使用的 REPEATABLE-READ（可重读）隔离级别是可以解决幻读问题发生的。

• InnoDB 的性能比 MyISAM 更强大。

14、MyISAM和InnoDB如何选择

大多数时候我们使用的都是 InnoDB 存储引擎，在某些读密集的情况下，使用 MyISAM 也是合适的。不过，前提是你的项目不介意 MyISAM 不支持事务、崩溃恢复等缺点（可是~我们一般都会介意啊！）。

一般情况下我们选择 InnoDB 都是没有问题的，但是某些情况下你并不在乎可扩展能力和并发能力，也不需要事务支持，也不在乎崩溃后的安全恢复问题的话，选择 MyISAM 也是一个不错的选择。但是一般情况下，我们都是需要考虑到这些问题的。

因此，对于咱们日常开发的业务系统来说，你几乎找不到什么理由再使用 MyISAM 作为自己的 MySQL 数据库的存储引擎

15、MySQL查询缓存

执行查询语句的时候，会先查询缓存。不过，MySQL 8.0 版本后移除，因为这个功能不太实用。缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。 因此，开启查询缓存要谨慎，尤其对于写密集的应用来说更是如此。

16、MySQL事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。数据库事务可以保证多个对数据库的操作（也就是 SQL 语句）构成一个逻辑上的整体。构成这个逻辑上的整体的这些数据库操作遵循：要么全部执行成功,要么全部不执行 。

事务具有ACID特性：

1. 原子性（Atomicity） ： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
2. 一致性（Consistency）： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
3. 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
4. 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的！

MySQL怎么保证原子性的？

我们知道如果想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，在 MySQL 中，恢复机制是通过 回滚日志（undo log） 实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。如果执行过程中遇到异常的话，我们直接利用 回滚日志 中的信息将数据回滚到修改之前的样子即可！并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚之前未完成的事务

17、SQL标准定义了哪些事务隔离级别

SQL 标准定义了四个隔离级别：

1. READ-UNCOMMITTED(读取未提交) ： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读
2. READ-COMMITTED(读取已提交) ： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生
3. REPEATABLE-READ(可重复读) ： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生
4. SERIALIZABLE(可串行化) ： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读

MySQL 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别

18、并发事务带来了哪些问题（什么是脏读、幻读、不可重复读）

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题：

• 脏读：Dirty read

事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据

例如：事务 1 读取某表中的数据 A=20，事务 1 修改 A=A-1，事务 2 读取到 A = 19,事务 1 回滚导致对 A 的修改并为提交到数据库， A 的值还是 20

• 丢失修改

在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。

例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 先修改 A=A-1，事务 2 后来也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。

• 不可重复读

事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果 不一致。

例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 再次读取 A =19，此时读取的结果和第一次读取的结果不同。

• 幻读

系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

例如：事务 2 读取某个范围的数据，事务 1 在这个范围插入了新的数据，事务 2 再次读取这个范围的数据发现相比于第一次读取的结果多了新的数据。

不可重复读侧重于修改，幻读侧重于新增或删除（多了或少量行），脏读是一个事务回滚影响另外一个事务。

19、事务的实现原理

事务是基于重做日志文件(redo log)和回滚日志(undo log)实现的。

每提交一个事务必须先将该事务的所有日志写入到重做日志文件进行持久化，数据库就可以通过重做日志来保证事务的原子性和持久性。

每当有修改事务时，还会产生 undo log，如果需要回滚，则根据 undo log 的反向语句进行逻辑操作，比如 insert 一条记录就 delete 一条记录。undo log 主要实现数据库的一致性。

20、MySQL的 redo log，undo log，bin log 都是干什么的

• redo log是InnoDB引擎特有的，只记录该引擎中表的修改记录。bin log是MySQL的Server层实现的，会记录所有引擎对数据库的修改。
• redo log是物理日志，记录的是在具体某个数据页上做了什么修改；binlog是逻辑日志，记录的是这个语句的原始逻辑。
• redo log是循环写的，空间固定会用完；binlog是可以追加写入的

redo log记录修改内容（哪一页发生了什么变化），写于事务开始前，用于数据未落磁盘，但数据库挂了后的数据恢复。bin log记录修改SQL，写于事务提交时，可用于读写分离。undo log记录修改前记录，用于回滚和多版本并发控制

21、并发事务的控制方式有哪些

MySQL 中并发事务的控制方式无非就两种：锁 和 MVCC。锁可以看作是悲观控制的模式，多版本并发控制（MVCC，Multiversion concurrency control）可以看作是乐观控制的模式。MySQL 中主要是通过 读写锁 来实现并发控制。

• 共享锁（S 锁） ：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
• 排他锁（X 锁） ：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）

读写锁可以做到读读并行，但是无法做到写读、写写并行。另外，根据根据锁粒度的不同，又被分为 表级锁(table-level locking) 和 行级锁(row-level locking)

• InnoDB 不光支持表级锁，还支持行级锁，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。
• 不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类。

MVCC 是多版本并发控制方法，即对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号是唯一的。

22、什么是MVCC

MVCC， 即多版本并发控制。MVCC 的实现，是通过保存数据在某个时间点的快照来实现的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

23、MVCC的实现原理

InnoDB 每一行数据都有一个隐藏的回滚指针，用于指向该行修改前的最后一个历史版本，这个历史版本存放在 undo log 中。如果要执行更新操作，会将原记录放入 undo log 中，并通过隐藏的回滚指针指向 undo log 中的原记录。其它事务此时需要查询时，就是查询 undo log 中这行数据的最后一个历史版本。

MVCC 最大的好处是读不加锁，读写不冲突，极大地增加了 MySQL 的并发性。通过 MVCC，保证了事务 ACID 中的 I（隔离性）特性。

24、按照锁的粒度分数据库锁有哪些？

在关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。

行级锁：

• 行级锁是MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。
• 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

表级锁：

• 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）

• 开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低

页级锁：

• 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。
• 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

MyISAM和InnoDB存储引擎使用的锁：

• MyISAM采用表级锁
• InnoDB支持行级锁和表级锁，默认为行级锁

扩展：mysql行锁和表锁，什么时候会用到？

• 一般情况是对应的存储引擎没有行级锁（例如：MyIASM），或者是对应的 SQL 语句没有匹配到索引，这个时候需要进行全表扫描，因此使用表级别锁
• 当增删改查匹配到索引时，Innodb 会使用行级锁。

25、从锁的类别上分MySQL都有哪些锁呢？

从锁的类别上来讲，有共享锁和排他锁。

• 共享锁: 又叫做读锁。 当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个
• 排他锁: 又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

多个人同时读是可以接受的，但是写只能一个人写。锁的粒度取决于具体的存储引擎，InnoDB实现了行级锁，页级锁，表级锁。他们的加锁开销从大到小，并发能力也是从大到小。

26、数据库的乐观锁和悲观锁是什么？怎么实现的

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

• 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制
• 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：乐观锁一般会使用版本号机制或CAS算法实现
• 悲观锁适用于写比较多的情况，乐观锁适用于写比较少的情况。

27、行级锁的使用有什么注意事项

InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。

当我们执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。这个在我们日常工作开发中经常会遇到，一定要多多注意！！！

28、InnoDB有哪几类行锁

InnoDB 行锁是通过对索引数据页上的记录加锁实现的，MySQL InnoDB 支持三种行锁定方式：

• 记录锁 Record Lock：属于单个行记录上的锁
• 间隙锁 Gap Lock：锁定一个范围，不包括记录本身
• 临键锁 Next-Key Lock：Record Lock + Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁

在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围

29、意向锁有什么作用

如果需要用到表锁的话，如何判断表中的记录没有行锁呢，一行一行遍历肯定是不行，性能太差。我们需要用到一个叫做意向锁的东东来快速判断是否可以对某个表使用表锁。

意向锁是表级锁，共有两种：

• 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。

• 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

30、索引介绍

**索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。**索引的作用就相当于书的目录。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

索引底层数据结构存在很多种类型，常见的索引结构有: B 树， B+树 和 Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构

索引优点：

• 使用索引可以大大加快 数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性

索引缺点：

• 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率
• 索引需要使用物理文件存储，也会耗费一定空间

但是，**使用索引一定能提高查询性能吗?**大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

31、索引的底层数据结构

Hash表：哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据。通过哈希算法，我们可以快速找到 key 对应的 index，找到了 index 也就找到了对应的 value。但是！哈希算法有个 Hash 冲突 问题，也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。

既然哈希表这么快，为什么 MySQL 没有使用其作为索引的数据结构呢？ 主要是因为 Hash 索引不支持顺序和范围查询。假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。并且，每次 IO 只能取一个。

SELECT * FROM tb1 WHERE id < 500;

在这种范围查询中，优势非常大，直接遍历比 500 小的叶子节点就够了。而 Hash 索引是根据 hash 算法来定位的，难不成还要把 1 - 499 的数据，每个都进行一次 hash 计算来定位吗?这就是 Hash 最大的缺点了

B树和B+树：B 树全称为 多路平衡查找树，B+ 树是 B 树的一种变体。目前大部分数据库系统及文件系统都采用 BTree 或其变种 B+Tree 作为索引结构

B树和B+树二者有何异同呢？（为什么InnoDB存储引擎选用B+树而不是B树呢）

• B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
• B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

31、MySQL有哪几种索引类型？

从数据结构角度：

1. 树索引
2. Hash索引

从物理存储角度：

1. 聚集索引：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引和数据行
2. 非聚集索引：不是聚簇索引，就是非聚簇索引

从逻辑角度：

1. 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引
2. 唯一索引：索引列的值必须唯一，但允许有空值
3. 复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
4. 联合索引
5. 全文索引

32、主键索引和二级索引(辅助索引)

数据表的主键列使用的就是主键索引。一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

**二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。**唯一索引，普通索引，前缀索引等索引属于二级索引。

• 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。

• 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL

• 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符

• 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引

33、聚簇索引和非聚簇索引

聚簇索引（聚集索引）：聚簇索引即索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引

聚簇索引的优点：

• 查询速度非常快 ：聚簇索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。相比于非聚簇索引， 聚簇索引少了一次读取数据的 IO 操作。
• 对排序查找和范围查找优化 ：聚簇索引对于主键的排序查找和范围查找速度非常快

缺点：

• 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢
• 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的

非聚簇索引：非聚簇索引即索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据

优点：更新代价比聚簇索引要小 。非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子节点是不存放数据的

缺点：

• 依赖于有序的数据 ：跟聚簇索引一样，非聚簇索引也依赖于有序的数据
• 可能会二次查询(回表) ：这应该是非聚簇索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询

注意：MyISAM无论主键索引还是二级索引都是非聚簇索引，而InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引

34、非聚簇索引一定会回表查询吗

**非聚簇索引不一定回表查询。**这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回
表查询.

举个简单的例子,假设我们在员工表的年龄上建立了索引,那么当进行select age from employee where age < 20的查询时,在索引的叶子节点上,已经包含了age信息,不会再次进行回表查询.

35、覆盖索引和联合索引

覆盖索引：覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。

再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。

联合索引：MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

具体原因为：MySQL使用索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。

36、讲一讲MySQL的最左前缀匹配原则

最左前缀匹配原则：在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询，如 >、<、between 和 以%开头的like查询 等条件，才会停止匹配。

---

所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据

37、怎么知道创建的索引有没有被使用到？或者说怎么才可以知道这条语句运行很慢的原因

使用 Explain 命令来查看语句的执行计划，MySQL 在执行某个语句之前，会将该语句过一遍查询优化器，之后会拿到对语句的分析，也就是执行计划，其中包含了许多信息。可以通过其中和索引有关的信息来分析是否命中了索引，例如：possilbe_key、key、key_len 等字段，分别说明了此语句可能会使用的索引、实际使用的索引以及使用的索引长度。

38、如何正确使用索引

首先选择合适的字段创建索引：

• 不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
• 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段
• 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引
• 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
• 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率

被频繁更新的字段应该慎重建立索引：虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了

尽可能的考虑建立联合索引而不是单列索引：因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升

注意避免冗余索引：冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 ，在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引

考虑在字符串类型的字段上使用前缀索引代替普通索引：前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引

39、什么情况下索引会失效？即查询不走索引？

避免索引失效：索引失效也是慢查询的主要原因之一，常见的导致索引失效的情况有下面这些

• 使用 SELECT * 进行查询
• 创建了组合索引，但查询条件未准守最左匹配原则
• 在索引列上进行计算、函数、类型转换等操作
• 以 % 开头的 LIKE 查询比如 like '%abc';
• 使用不等于查询

40、能用MySQL直接存储文件(比如图片)吗

可以是可以，直接存储文件对应的二进制数据即可。不过，还是建议不要在数据库中存储文件，会严重影响数据库性能，消耗过多存储空间。可以选择使用云服务厂商提供的开箱即用的文件存储服务，成熟稳定，价格也比较低。数据库只存储文件地址信息，文件由文件存储服务负责存储。

41、MySQL如何存储IP地址

可以将 IP 地址转换成整形数据存储，性能更好，占用空间也更小。

MySQL 提供了两个方法来处理 ip 地址：

• INET_ATON() ： 把 ip 转为无符号整型 (4-8 位)
• INET_NTOA() :把整型的 ip 转为地址

插入数据前，先用 INET_ATON() 把 ip 地址转为整型，显示数据时，使用 INET_NTOA() 把整型的 ip 地址转为地址显示即可。

42、如何分析SQL的性能

我们可以使用 EXPLAIN 命令来分析 SQL 的 执行计划 。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。EXPLAIN 并不会真的去执行相关的语句，而是通过 查询优化器 对语句进行分析，找出最优的查询方案，并显示对应的信息。

43、查询性能的优化方法

减少请求的数据量

1. 只返回必要的列：最好不要使用 SELECT * 语句
2. 只返回必要的行：使用 LIMIT 语句来限制返回的数据
3. 缓存重复查询的数据

减少服务器端扫描的行数

1. 最有效的方式是使用索引来覆盖查询

44、MySQL中的varchar和char有什么区别

• char(n)：固定长度类型，比如：订阅 char(10)，当你输入”abc”三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。

  • 优点：效率高
  • 缺点：占用空间
  • 适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。

• varchar(n)：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。

在检索效率上来讲,char > varchar,因此在使用中,如果确定某个字段的值的长度,可以使用char,否则应该尽量使用varchar.例如存储用户MD5加密后的密码,则应该使用char

45、varchar(10)和int(10)代表什么含义

• varchar的10代表了申请的空间长度，也是可以存储的数据的最大长度

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

46、关心过业务系统里面的sql耗时吗？统计过慢查询吗？对慢查询都怎么优化过

在业务系统中,除了使用主键进行的查询,其他的我都会在测试库上测试其耗时,慢查询的统计主要由运维在做,会定期将业务中的慢查询反馈给我们。

慢查询的优化首先要搞明白慢的原因是什么? 是查询条件没有命中索引?是load了不需要的数据列?还是数据量太大?

所以优化也是针对这三个方向来的：

• 首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对语句进行分析以及重写.
• 分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能的命中索引
• 如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表.

47、select语句的优化

1. 避免出现 select *

   首先，select * 操作在任何类型数据库中都不是 一个好的SQL编写习惯，使用select * 取出全部列，会让优化器无法完成 索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额 外的I/O,内存和CPU消耗

2. 避免出现不确定结果的函数

   特定针对主从复制这类业务场景。由于原理上从 库复制的是主库执行的语句，使用如now()、rand()、sysdate()、current_user()等不确定结果 的函数很容易导致主库与从库相应的数据不一 致。另外不确定值的函数,产生的SQL语句无法利用query cache

3. 多表关联查询时，小表在前，大表在后

4. 使用表的别名：当在SQL语句中连接多个表时，请使用表的别名 并把别名前缀于每个列名上。这样就可以减少解 析的
   时间并减少哪些友列名歧义引起的语法错误

5. 用where字句替换HAVING字句

   避免使用HAVING字句，因为HAVING只会在检 索出所有记录之后才对结果集进行过滤，而 where则是
   在聚合前筛选记录，如果能通过 where子句限制记录的数目，那就能减少这方面的开销。HAVING中的
   条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句 中。where和having的区别：
   where后面不能使用组函数

48、什么是索引下推

索引下推（Index Condition Pushdown） 是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。在InnoDB中只针对二级索引有效