MySQL 面试题

MySQL 基础

数据库的约束与范式？

七大约束：

检查约束：以数据类型以及数据的长度进行约束，在一个表中，所插入的数据，必须和数据类型匹配，并且范围不能超过指定的长度。
非空约束 not null：非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。如果有非空约束，那么在插入插入数据时，必须插入一条数据，非空约束一般用于收集并存储的字段数据。所有的类型的值都可以是null，包括int、float 等数据类型。
默认值约束 default：创建列时可以指定默认值，当插入数据时如果未主动设置数据，则自动添加该默认值。
主键约束 primary key：确保每一行唯一，主键约束相当于唯一约束 + 非空约束的组合，是用于唯一识别一个实体的字段，不允许有空值，当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。
唯一约束 unique：保证每一行的数据是唯一的，没有重复的数据，保证数据的唯一性。唯一约束不允许出现重复的值，但是可以为多个null。同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。
外键约束 foreign key：用于表与表之间的关联，一张表中的外键一般都是另一张表中的主键，并且加上外键约束之后，插入的数据就必须是该主键存在的值。
自增长约束：如果为某列设置自增列，插入数据时无需设置此列，默认将自增（一个表只能有一个自增列）。
自定义约束：自定义约束是使用存储过程或者触发器来进行约束。

三大范式：

确保每列保持原子性（不可拆分性）：确保一列只存放一个数据，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。如果实体中的某个属性有多个值时，必须拆分为不同的属性。
确保每一行的唯一性：满足第一范式的基础上，确保每一行数据的唯一，能够唯一区分一个实体，实现的方式可以是主键。
确保每列都和主键列直接相关，而不是间接相关：第三范式需要确保数据表除外键外，其他非主键属性不得重复出现在第二张表上。

MySQL 默认端口？

Mysql 的默认端口是3306。

MySQL 和 PostgreSQL 的区别？

PostgreSQL相对于MySQL的优势：

在SQL的标准实现上要比MySQL完善，而且功能实现比较严谨；
存储过程的功能支持要比MySQL好，具备本地缓存执行计划的能力；
对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强；
PG主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。
PG的主备复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。
MySQL的存储引擎插件化机制，存在锁机制复杂影响并发的问题，而PG不存在。

MySQL相对于PG的优势：

innodb的基于回滚段实现的MVCC机制，相对PG新老数据一起存放的基于XID的MVCC机制，是占优的。新老数据一起存放，需要定时触发VACUUM，会带来多余的IO和数据库对象加锁开销，引起数据库整体的并发能力下降。而且VACUUM清理不及时，还可能会引发数据膨胀；
MySQL采用索引组织表，这种存储方式非常适合基于主键匹配的查询、删改操作，但是对表结构设计存在约束；
MySQL的优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作；
MySQL分区表的实现要优于PG的基于继承表的分区实现，主要体现在分区个数达到上千上万后的处理性能差异较大。
MySQL的存储引擎插件化机制，使得它的应用场景更加广泛，比如除了innodb适合事务处理场景外，myisam适合静态数据的查询场景。

获取当前 Mysql 版本？

SELECT VERSION();

用于获取当前 Mysql 的版本。

主键和候选键区别？

表格的每一行都由主键唯一标识，一个表只有一个主键。

主键也是候选键，按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用。

设置为 AUTOINCREMENT 的列达到最大值会发生什么？

它会停止递增，任何进一步的插入都将产生错误，因为密钥已被使用。

怎样找出最后一次插入时分配了哪个自动增量？

LAST_INSERT_ID 将返回由 Auto_increment 分配的最后一个值，并且不需要指定表名称。

Mysql 中的存储引擎？

存储引擎

描述

MySQL 索引

什么是索引？

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。

索引的作用就相当于目录的作用。其本身是一种特殊的文件，它们包含着对数据表里所有记录的引用指针，会占据一定的物理空间。

索引的优缺点？

优点：使用索引可以大大加快数据的检索速度（大大减少检索的数据量）
缺点：创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。另外，索引需要使用物理文件存储，也会耗费一定空间。

索引的基本原理？

把无序的数据变成有序的查询

把创建了索引的列的内容进行排序
对排序结果生成倒排表
在倒排表内容上拼上数据地址链
在查询的时候，先拿到倒排表的内容，再取出数据地址链，从而拿到具体数据

索引的类型？
索引类型索引描述
普通索引

(Index)
用来快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
唯一索引

(Unique Key)
可以保证数据记录的唯一性，允许数据为 NULL，一张表允许创建多个唯一索引。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
主键索引

(Primary Key)
数据表的主键列使用的就是主键索引。是一种特殊的唯一索引，一张数据表有只能有一个主键，并且主键不能为 null，不能重复。
前缀索引

(Prefix)
前缀索引也叫局部索引，前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。（前缀的标识度高。比如密码就适合建立前缀索引，因为密码几乎各不相同）
全文索引

(Full Text)
全文索引主要是为了检索大文本数据中的关键字的信息，通过建立倒排索引，可以极大提升索引效率，解决判断字段是否包含问题，是目前搜索引擎使用的关键技术。组合索引多列值组成一个索引，专门用于组合搜索。
唯一索引比普通索引快吗？

对于写多读少的情况，普通索引利用 change buffer 有效减少了对磁盘的访问次数，而唯一索引需要校验唯一性，此时普通索引性能要高于唯一索引。

聚集索引与非聚集索引？

两者都是B+树的数据结构，依赖于有序的数据。

聚集索引：聚集索引即索引结构和数据一起存放，并按一定的顺序进行排序的索引，找到了索引就找到了数据。主键索引属于聚集索引。

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

非聚集索引：非聚集索引即索引结构和数据分开存放的索引。非聚集索引的叶子节点并不存放数据，存储的数据行地址，根据数据行地址再回表查数据。

优点： - 更新代价比聚集索引要小。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的
缺点： - 跟聚集索引一样，非聚集索引也依赖于有序的数据- 可能会二次查询(回表) ：这应该是非聚集索引最大的缺点了。当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

MyISAM：采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

InnoDB：主键索引采用聚集索引（索引的数据域存储数据文件本身），辅助索引的数据域存储主键的值；因此从辅助索引查找数据，需要先通过辅助索引找到主键值，再访问辅助索引；最好使用自增主键，防止插入数据时，为维持 B+树结构，文件的大调整。

最左前缀匹配原则？

最左前缀匹配原则：最左优先，在创建多列索引时，要根据业务需求，where 子句中使用最频繁的一列放在最左边。

mysql 会一直从左向右匹配直到遇到范围查询(

、

between

、

like

)就停止匹配，比如

a = 1 and b = 2 and c > 3 and d = 4

如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

和

in

可以乱序，比如

a = 1 and b = 2 and c = 3

建立(a,b,c)索引可以任意顺序，mysql 的查询优化器会帮你优化成索引可以识别的形式。

索引失效的情况？

通过 explain 显示出 MySQL 执行的字段内容：

id：SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符
select_type：SELECT 查询的类型
table：查询的是哪个表
partitions：匹配的分区
type：join 类型
possible_keys：此次查询中可能选用的索引
key：此次查询中确切使用到的索引
ref：哪个字段或常数与 key 一起被使用
rows：显示此查询一共扫描了多少行，这个是一个估计值
filtered：表示此查询条件所过滤的数据的百分比
extra：额外的信息

（1）like 以 % 开头，索引无效；当 like 前缀没有 %，后缀有 % 时，索引有效。

（2）or 语句前后没有同时使用索引。

当 or 左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效。

（3）组合索引，不是使用第一列索引，索引失效。

（4）数据类型出现隐式转化。

如 varchar 不加单引号的话可能会自动转换为 int 型，使索引无效，产生全表扫描。

（5）在索引字段上使用 not，<>，!=。

不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。优化方法： key <> 0 改为 key > 0 or key < 0。

（6）对索引字段进行计算操作、字段上使用函数。

（7）其他

当全表扫描速度比索引速度快时，MySQL 会使用全表扫描，此时索引失效。
左连接查询或者右连接查询查询关联的字段编码格式不一样。
使用 NOT IN、NOT EXISTS 导致索引失效
在索引列上使用 IS NULL 或 IS NOT NULL 操作有可能会导致索引失效，IS NULL 不走索引，IS NOT NULL 走索引

可以使用多少列创建索引？

任何标准表最多可以创建16个索引列。

MySQL 事务

事务的特性？

ACID：原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。

原子性：整个事务中的所有操作，要么全部成功，要么全部失败，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
隔离性：隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

事务的分类？

事务可以分为很多中类型，一般分为：扁平事务、带有保存点的扁平事务、链事务、嵌套事务、分布式事务。

（1）扁平事务：

扁平事务是最简单的一种，在实际开发中也是使用的最多的一种事务。在这种事务中，所有操作都处于同一层次，最常见的方式如下：

BEGIN WORK

Operation 1

Operation 2

Operation 3

...

Operation N

COMMIT WORK

// 示例

begin work;

select * from user;

update user set name = 'sihai' where id = 1;

commit work;

扁平事务的主要缺点是不能提交或回滚事务的某一部分，或者分几个独立的步骤去提交。

（2）带有保存点的扁平事务

这种事务除了支持扁平事务支持的操作外，这种事务跟扁平事务最大的区别就是允许在事务执行过程中回滚到同一事务中较早的一个状态，这是因为可能某些事务在执行过程中出现的错误并不会对所有的操作都无效，放弃整个事务不合乎要求，开销也太大。保存点用来通知系统应该记住事务当前的状态，以便以后发生错误时，事务能回到该状态。

示例：

begin work;

select * from user;

savepoint t1;

update user set name = 'sihai' where id = 1;

savepoint t2;

commit work;

通过上面的方式我们就建立了两个保存点 t1、t2，通过

ROLLBACK TO SAVEPOINT t1

，就可以返回到保存点 t1。

（2）链事务

链事务：在提交一个事务时，释放不需要的数据对象，将必要的处理上下文隐式的传给下一个要开始的事务。需要注意，提交事务操作和下一个事务操作将合并为一个原子操作，就是下一个事务可以看到上一个事务的结果。

链事务，就是指回滚时，只能恢复到最近一个保存点；而带有保存点的扁平事务则可以回滚到任意正确的保存点。

示例：

begin work;

select * from user;

savepoint t1;

update user set name = 'sihai' where id = 1;

savepoint t2;

commit work;

还是这个例子，但是对于链事务来说，是不能直接rollback到保存点t1的，最能恢复到最近的一个保存点t2；另外我们需要注意，链事务在执行 commit 后就会释放当前事务所持有的所有锁，而带有保存点的扁平事务不会影响所持有的锁。

（3）嵌套事务

在事务中再嵌套事务，这种结构有点像一颗横着的树的结构，位于根节点的事务称为顶层事务。事务的前驱称为父事务，其它事务称为子事务。事务的前驱称为父事务，事务的下一层称为子事务。

子事务既可以提交也可以回滚，但是它的提交操作并不马上生效，除非由其父事务提交。因此就可以确定，任何子事务都在顶层事务提交后才真正的被提交了。同理，任意一个事务的回滚都会引起它的所有子事务一同回滚。

BEGIN WORK

SubTransaction1:

BEGIN WORK

SubOperationX

COMMIT WORK

SubTransaction2:

BEGIN WORK

SubOperationY

COMMIT WORK

...

SubTransactionN:

BEGIN WORK

SubOperationN

COMMIT WORK

（4）分布式事务

分布式事务通常是指在一个分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点。

在不同的物理地址，通过网络访问，执行不同的事务，这就是分布式事务。

事务隔离级别？

数据库事务的隔离级别有4个，由低到高依次为

Read uncommitted：读到未提交数据
Read committed：脏读，不可重复读
Repeatable read：可重读
Serializable：串行事物

这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

脏读

不可重复读

幻读

Read uncommitted

√

Read committed

√

Repeatable read

√

Serializable

脏读、不可重复读、幻读？

（1）脏读

脏读发生在一个事务A读取了被另一个事务B修改，但是还未提交的数据。假如B回退，则事务A读取的是无效的数据。这跟不可重复读类似，但是第二个事务不需要执行提交。

（2）不可重复读

在基于锁的并行控制方法中，如果在执行 select 时不添加读锁，就会发生不可重复读问题。

在多版本并行控制机制中，当一个遇到提交冲突的事务需要回退但却被释放时，会发生不可重复读问题。

在上面这个例子中，事务2提交成功，它所做的修改已经可见。然而，事务1已经读取了一个其它的值。在序列化和可重复读的隔离级别中，数据库管理系统会返回旧值，即在被事务2修改之前的值。在提交读和未提交读隔离级别下，可能会返回被更新的值，这就是“不可重复读”。

有两个策略可以防止这个问题的发生：

推迟事务2的执行，直至事务1提交或者回退。这种策略在使用锁时应用。
而在多版本并行控制中，事务2可以被先提交。而事务1继续执行在旧版本的数据上。当事务1尝试提交时，数据库会检验它的结果是否和事务1、事务2顺序执行时一样。如果是，则事务1提交成功。如果不是，事务1会被回退。

（3）幻读

幻读发生在当两个完全相同的查询执行时，第二次查询所返回的结果集跟第一个查询不相同。

发生的情况：没有范围锁。

原子性、一致性和持久性实现原理？

原子性、稳定性和持久性是通过 redo 和 undo 日志文件实现的，不管是 redo 还是 undo 文件都会有一个缓存我们称之为 redo_buf 和 undo_buf。同样，数据库文件也会有缓存称之为 data_buf。

undo 记录了数据在事务开始之前的值，当事务执行失败或者 ROLLBACK 时可以通过 undo 记录的值来恢复数据。

redo 日志记录数据修改后的值，可以避免数据在事务提交之前必须写入到磁盘的需求，减少 I/O。

MySQL 锁

锁的两个概念?

（1）Lock

Lock 的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般 Lock 的对象仅在事务 commit 或 rollback 后进行释放（不同事务隔离级别释放的时间可能不同）。

查看方式：

show engine innodb status

（2）Latch

Latch 称为闩锁（轻量级的锁），因为其要求锁定的时间必须非常短，若持续的时间长，则应用的性能会非常差。在InnoDB引擎中，Latch 又可以分为 mutex（互斥量）和 rwlock（读写锁）。其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。

show engine innodb mutex

Lock 与 Latch 比较

Lock

Latch

对象

事务

线程

保护

数据库内容

内存数据结构

持续时间

整个事务过程

临界资源

模式

行锁、表锁锁、意向锁

读写锁、互斥锁

死锁

通过 waits-for graph、time out 等机制进行死锁检测与处理

无死锁检测机制。仅通过应用程序加锁的顺序保证无死锁发生

存在于

Lock Manager 的哈希表中

每个数据结构的对象中

MySQL 锁分类？

基于锁的属性

共享锁

共享锁（Shared Lock）又称读锁，简称S锁。

当一个事务对数据加上读锁之后，其他事务只能对该数据加读锁，而不能加写锁，直到所有的读锁释放之后其他事务才能对该数据加写锁。

共享锁主要是为了支持并发读取数据，读取数据的时候不支持修改，避免出现重复读的问题。

排他锁

排他锁（Exclusive Lock）又称写锁，简称X锁。

当一个事务对数据加上写锁之后，其他事务将不能对该数据加任何锁，直到该锁释放。

排他锁的目的是在对数据修改时，不允许其他事务同时修改和读取，避免出现脏读和幻读的问题。

基于锁的状态

意向共享锁

意向排他锁

基于锁的粒度

行级锁

锁定表中的一行或多行，没有被锁定的数据行可以正常访问。

特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

使用行级锁：InnoDB

页级锁

页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间。

特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

使用页级锁定的主要是 BerkeleyDB 存储引擎。

表级锁

锁定整个表。

特点：粒度大、加锁解锁简单、不会出现死锁，容易发生锁冲突、并发性能低。

使用表级锁的主要是 MyISAM，MEMORY，CSV 等一些非事务性存储引擎。

记录锁

记录锁属于行锁的一种，只不过记录锁的锁定范围只有表中的某一行。

间隙锁

间隙锁属于行锁的一种，间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。间隙锁是 InnoDB 在 RR(可重复读) 隔离级别下为了解决幻读问题时引入的锁机制。

临键锁

临键锁属于行锁的一种，并且是 InnoDB 行锁的默认算法。

临键锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。

通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。

临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

基于加锁机制

乐观锁

悲观锁

S or X (共享锁、排他锁)的兼容性：

S锁

X锁

S锁

√

X锁

IS or IX (共享、排他)意向锁的兼容性

IS锁

IX锁

S锁

X锁

IS锁

√

IX锁

√

S锁

√

X锁

一致性锁定读/非锁定读？

（1）一致性锁定读（Locking Reads）

在一个事务中查询数据时，普通的 SELECT 语句不会对查询的数据进行加锁，其他事务仍可以对查询的数据执行更新和删除操作。因此，InnoDB 提供了两种类型的锁定读来保证额外的安全性：

SELECT ... LOCK IN SHARE MODE：对读取的行添加S锁，其他事物可以对这些行添加S锁，若添加X锁，则会被阻塞。
SELECT ... FOR UPDATE：会对查询的行及相关联的索引记录加X锁，其他事务请求的S锁或X锁都会被阻塞。当事务提交或回滚后，通过这两个语句添加的锁都会被释放。注意：只有在自动提交被禁用时，SELECT FOR UPDATE才可以锁定行，若开启自动提交，则匹配的行不会被锁定。

一致性锁定读必须在一个事物中，当事务提交了，锁就释放了，因此使用这两种方式加锁的语句，必须加上

begin

、

start transaction

或者

set autocommit = 0

。

（2）一致性非锁定读（consistent nonlocking read）

一致性非锁定读是指 InnoDB 存储引擎通过多版本控制(MVVC)读取当前数据库中行数据的方式。如果读取的行正在执行 DELETE 或 UPDATE 操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB 会去读取行的一个快照。所以，非锁定读机制大大提高了数据库的并发性。

一致性非锁定读是 InnoDB 默认的读取方式，即读取不会占用和等待行上的锁。在事务隔离级别

READ COMMITTED

和

REPEATABLE READ

下，InnoDB 使用一致性非锁定读。

然而，对于快照数据的定义却不同。在

READ COMMITTED

事务隔离级别下，一致性非锁定读总是读取被锁定行的最新一份快照数据。而在

REPEATABLE READ

事务隔离级别下，则读取事务开始时的行数据版本。

行锁的算法？

InnoDB 存储引擎有3种行锁的算法，其分别是：

Record Lock 1. 记录锁（行锁），单条索引记录上加锁，锁住的永远是索引，而非记录本身。2. 行锁的实现依赖于索引，一旦某个加锁操作没有使用到索引，那么该锁就会退化为表锁。记录锁存在于包括主键索引在内的唯一索引中，锁定单条索引记录。
Gap Lock 1. 间隙锁，在索引记录之间的间隙中加锁（锁定一个范围），并不包括该索引记录本身。2. 间隙锁存在于非唯一索引中，锁定开区间范围内的一段间隔，它是基于临键锁实现的。
Next-Key Lock 1. 临键锁，Record lock 和 Gap lock 的结合，既除了锁住记录本身，也锁住索引之间的间隙（一个范围）。2. 临键锁存在于非唯一索引中，该类型的每条记录的索引上都存在这种锁，它是一种特殊的间隙锁，锁定一段左开右闭的索引区间。

举个例子10，20，30，那么该索引可能被 Next-Key Locking 的区间为：

除了 Next-Key Locking，还有 Previous-Key Locking 技术，这种技术跟 Next-Key Lock正好相反，锁定的区间是区间范围和前一个值（左闭右开）。同样上述的值，使用 Previous-Key Locking 技术，那么可锁定的区间为：

不是所有索引都会加上 Next-key Lock 的，这里有一种特殊的情况，在查询的列是唯一索引（包含主键索引）的情况下，Next-key Lock 会降级为 Record Lock。

用户可以通过以下两种方式来显示的关闭 Gap Lock：

将事务的隔离级别设为READ COMMITED。
将参数innodb_locks_unsafe_for_binlog设置为1。

Gap Lock 的作用：是为了阻止多个事务将记录插入到同一个范围内，设计它的目的是用来解决 Phontom Problem（幻读问题）。在MySQL默认的隔离级别（Repeatable Read）下，InnoDB 就是使用它来解决幻读问题。

锁的优化策略？

读写分离
分段加锁
减少锁持有的时间

MySQL 优化

数据库设计方面

范式优化：比如消除冗余
反范式优化：比如适当加冗余等（减少 join）
拆分表：分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。拆分又分垂直拆分和水平拆分
首先应考虑在 where 及 order by 涉及的列上建立索引。
尽量不在包含大量重复值的字段上建立索引，如性别 sex。
索引不是越多越好，一般考虑在6个左右。
应在查询较多的字段上建立索引，而不是在频繁更新的字段上建立索引。
字段的值只有数值型时，应设计为数字型字段。因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
避免频繁创建和删除临时表，以减少系统表资源的消耗。
临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先 create table，然后insert。
如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

SQL 方面

不要使用 select * from t，用具体的字段列表代替*，不要返回用不到的任何字段。
尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。
尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，可以用 union 代替（Innodb）。
尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
慎用 in 和 not in，否则会可能导致全表扫描，可以用 exists 代替 in。
用 where 子句替换 having 子句因为 having 只会在检索出所有记录之后才对结果集进行过滤。
避免使用隐式转换：如 varchar 类型字段在用where name = 1，而要用where name = '1'，否则MySQL 会自动进行数据类型转换再执行，函数作用于表字段时，索引就会失效。
使用 like 时，应使用右模糊 name like 'zhang%'，而不是name like '%zhang%'或者name like '%zhang'。
排序请尽量使用升序。
复合索引高选择性的字段排在前面。
根据联合索引的第二个及以后的字段单独查询用不到索引。
字符串比较长的可以考虑索引一部份减少索引文件大小，提高写入效率。
如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描： select id from t where num=@num 可以改为强制查询使用索引： select id from t with(index(索引名)) where num=@num。
尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
尽量避免大事务操作，提高系统并发能力。

Java 方面

尽可能的少造对象。
合理摆正系统设计的位置。大量数据操作，和少量数据操作一定是分开的。大量的数据操作，肯定不是ORM框架搞定的。使用jDBC链接数据库操作数据
控制好内存，让数据流起来，而不是全部读到内存再处理，而是边读取边处理；
合理利用内存，有的数据要缓存

MySQL 底层

B 树和 B+ 树的区别？

B 树也称 B-树，全称为多路平衡查找树，在B树中，所有节点既存放键(key) 也存放数据(data)，叶子节点各自独立。
B+树是B树的一种变体，内部节点都是键(key)，没有值，叶子节点同时存放键(key)和值(value)。而且所有的叶子结点中增加了指向下一个叶子节点的指针，因此 InnoDB 建议为大部分表使用默认自增的主键作为主索引。

B 树的优点？

B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

B+ 树的优点？

由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。 B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可（检索效率很稳定）。而B树则需要对树的每一层进行遍历(当于对范围内的每个节点的关键字做二分查找)，这会需要更多的内存置换次数，因此也就需要花费更多的时间

数据库为什么使用 B+ 树而不是 B 树？

B树只适合随机检索，而B+树同时支持随机检索和顺序检索；
B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。
B+树的查询效率更加稳定，B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。
B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。
增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是，两者的实现方式不太一样。

MyISAM 引擎中，B+Tree 叶节点的 data 域存放的是数据记录的地址。在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引"。
InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）"，而其余的索引都作为辅助索引，辅助索引的 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM 不同的地方。在根据主索引搜索时，直接找到 key 所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

Hash 索引与 B+ 树？

在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度，而不需要使用hash索引。

hash 索引底层就是 hash 表，进行查找时，调用一次 hash 函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。
hash 索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。因为在 hash 索引中经过 hash 函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，支持范围查询。
hash 索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
hash 索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生 hash 碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

MySQL 综合

MySQL 中控制内存分配的全局参数?

Keybuffersize：keybuffersize 指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Keyreadrequests 和 Keyreads，可以知道 keybuffersize 设置是否合理。比例 keyreads /keyreadrequests 应该尽可能的低，至少是1:100，1:1000 更好。keybuffersize 只对 MyISAM 表起作用。即使你不使用 MyISAM 表，但是内部的临时磁盘表是 MyISAM 表，也要使用该值。keybuffersize 设置注意事项： - 单个 keybuffer 的大小不能超过 4G- 建议 keybuffer 设置为物理内存的 1/4(针对 MyISAM 引擎)，甚至是物理内存的 30%~40%，如果 keybuffersize 设置太大，系统就会频繁的换页，降低系统性能。因为 MySQL 使用操作系统的缓存来缓存数据，所以我们得为系统留够足够的内存；在很多情况下数据要比索引大得多。- 如果机器性能优越，可以设置多个keybuffer,分别让不同的 keybuffer 来缓存专门的索引
innodbbufferpool_size：表示缓冲池字节大小，InnoDB 缓存表和索引数据的内存区域。mysql 默认的值是 128M。最大值与你的CPU 体系结构有关，在 32 位操作系统，最大值是 4294967295(2^32-1) ，在 64 位操作系统，最大值为18446744073709551615 (2^64-1)。在 32 位操作系统中，CPU 和操作系统实用的最大大小低于设置的最大值。如果设定的缓冲池的大小大于 1G，设置 innodbbufferpoolinstances 的值大于 1。数据读写在内存中非常快, innodbbufferpoolsize 减少了对磁盘的读写。当数据提交或满足检查点条件后才一次性将内存数据刷新到磁盘中。然而内存还有操作系统或数据库其他进程使用, 一般设置 bufferpool 大小为总内存的 3/4 至 4/5。若设置不当, 内存使用可能浪费或者使用过多。对于繁忙的服务器, buffer pool 将划分为多个实例以提高系统并发性, 减少线程间读写缓存的争用。buffer pool 的大小首先受 innodbbufferpool_instances 影响, 当然影响较小。
querycachesize：当 mysql 接收到一条 select 类型的 query时，mysql 会对这条 query 进行 hash 计算而得到一个 hash 值，然后通过该 hash 值到 query cache 中去匹配，如果没有匹配中，则将这个hash 值存放在一个 hash 链表中，同时将 query 的结果集存放进cache 中，存放 hash 值的链表的每一个 hash 节点存放了相应 query结果集在 cache 中的地址，以及该 query 所涉及到的一些 table 的相关信息；如果通过 hash 值匹配到了一样的 query，则直接将 cache 中相应的 query 结果集返回给客户端。如果 mysql 任何一个表中的任何一条数据发生了变化，便会通知 query cache 需要与该 table 相关的query 的 cache 全部失效，并释放占用的内存地址。query cache优缺点 - query 语句的 hash 计算和 hash 查找带来的资源消耗。mysql 会对每条接收到的 select 类型的 query 进行 hash 计算然后查找该 query 的 cache 是否存在，虽然 hash 计算和查找的效率已经足够高了，一条 query 所带来的消耗可以忽略，但一旦涉及到高并发，有成千上万条 query 时，hash 计算和查找所带来的开销就的重视了；- query cache 的失效问题。如果表变更比较频繁，则会造成 query cache 的失效率非常高。表变更不仅仅指表中的数据发生变化，还包括结构或者索引的任何变化；- 对于不同 sql 但同一结果集的 query 都会被缓存，这样便会造成内存资源的过渡消耗。sql 的字符大小写、空格或者注释的不同，缓存都是认为是不同的 sql（因为他们的 hash 值会不同）；- 相关参数设置不合理会造成大量内存碎片，相关的参数设置会稍后介绍。
readbuffersize：是 MySQL 读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL 会为它分配一段内存缓冲区。readbuffersize 变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。

Mysql 表中允许的触发器？

在 Mysql 表中允许有六个触发器（TRIGGERS），如下：

BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE
AFTER DELETE

在 Mysql 中运行批处理模式？

以下命令用于在批处理模式下运行：

mysql;

mysqlmysql.out

Mysql 有关权限的表？

Mysql 服务器通过权限表来控制用户对数据库的访问，权限表存放在 mysql 数据库里，由 mysql_install_db 脚本初始化。这些权限表分别为 user，db，table_priv，columns_priv 和 host。

MyISAM 表格将存储在哪里？

每个 MyISAM 表格以三种格式存储在磁盘上：

•

.frm

文件存储表定义

• 数据文件具有

.MYD

（MYData）扩展名

• 索引文件具有

.MYI

（MYIndex）扩展名

MyISAM Static 和 Dynamic 区别？

MyISAM Static 上的所有字段有固定宽度。

动态 MyISAM 表将具有像 TEXT，BLOB 等字段，以适应不同长度的数据类型。

MyISAM Static 在受损情况下更容易恢复。

myisamchk 是用来做什么的？

它用来压缩 MyISAM 表，这减少了磁盘或内存使用。

什么是访问控制列表？

ACL（访问控制列表）是与对象关联的权限列表，这个列表是Mysql服务器安全模型的基础，它有助于排除用户无法连接的问题。

Mysql将ACL（也称为授权表）缓存在内存中，当用户尝试认证或运行命令时，Mysql会按照预定的顺序检查ACL的认证信息和权限。

使用 Unix shell 登录 Mysql？

通过以下命令登录：

#[mysqldir]/bin/mysql-hhostname-u<UserName>-p<password>

Unix 和 Mysql 之间时间戳的转换？

UNIX_TIMESTAMP 是从 Mysql 时间戳转换为 Unix 时间戳的命令

FROM UNIXTIME 是从 Unix 时间戳转换为 Mysql 时间戳的命令

federated 表是什么？

federated 表，允许访问位于其他服务器数据库上的表。

Mysql中有哪些不同的表格？

共有5种类型的表格：

MyISAM
Heap
Merge
INNODB
ISAM

MyISAM 是 Mysql 的默认存储引擎。

Heap 表是什么？

HEAP 表存在于内存中，用于临时高速存储。

BLOB或TEXT字段是不允许的
只能使用比较运算符=，<，>，=>，=<
HEAP表不支持AUTO_INCREMENT
索引不可为NULL

如何控制 HEAP 表的最大尺寸？

Heal 表的大小可通过称为 max_heap_table_size 的 Mysql 配置变量来控制。

MySQL binlog？

binlog 是 mysql 用来记录数据库表结构变更以及表数据修改的的二进制日志，它只会记录表的变更操作，但不会记录 select 和 show 这种查询操作。

binlog 比较常用的场景有以下3种：

数据恢复：误删数据之后可以通过 mysqlbinlog 工具恢复数据。
主从复制：主库将 binlog 传给从库，从库接收到之后读取内容写入从库，实现主库和从库数据一致性。
审计：可以通过二进制日志中的信息进行审计，判断是否对数据库进行注入攻击。

binlog 文件包含两种类型：

索引文件（文件名后缀为.index）用于记录哪些日志文件正在被使用
日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

binlog 提供了3种记录模式：

ROW：记录的是每一行被修改的数据
STATEMENT：记录的是执行的SQL语句
MIXED：statement 和 row 模式的混合

记录模式

优点

缺点

ROW

能清楚记录每一个行数据的修改细节

批量操作，会产生大量的日志，尤其是 alter table 会让日志文件大小暴涨

STATEMENT

日志量小，减少磁盘IO，提升存储和恢复速度

在某些情况下会导致主从数据不一致，比如 Sql 语句中有 last_insert_id()、now() 等函数。

MIXED

准确性强，文件大小适中

当 binlog format 设置为 mixed 时，普通复制不会有问题，但是级联复制在特殊情况下会 binlog 丢失。

Binlog文件结构：

MySQL的binlog文件中记录的是对数据库的各种修改操作，用来记录修改操作的数据结构是Log event。不同的修改操作对应的不同的log event。比较常用的log event有：Query event、Row event、Xid event等。binlog文件的内容就是各种Log event的集合。

Binlog文件中Log event结构如下图所示：

event字段名

描述

timestamp

4字节事件开始执行时间

event_type

1字节指定该事件的类型

event size

4字节该事件的长度

server_id

1字节服务器的serverId

next log pos

4字节下一个事件开始的位置

flag

2字节 event flags

binlog 的写入机制：

根据设置的记录模式和操作生成相应的log event

事务执行过程中产生log event会先写入缓冲区，每个事务线程都有一个缓冲区，Log Event保存在一个binlog_cache_mngr数据结构中，在该结构中有两个缓冲区，一个是stmt_cache，用于存放不支持事务的信息；另一个是trx_cache用于存放支持事务的信息。

事务在提交阶段会将产生的log event写入到外部binlog文件中。不同事务以串行方式将log event写入binlog文件中，所以一个事务包含的log event信息在binlog文件中是连续的，中间不会插入其他事务的log event。

一张表有ID自增主键，当 insert 17条记录后删除第15,16,17条记录，重启Mysql，再 insert 一条记录，这条记录的ID是18还是15？

如果表的类型是 MyISAM，那么是18。因为 MyISAM 表会把自增主键的最大ID记录到数据文件里，重启MySQL自增主键的最大ID也不会丢失
如果表的类型是 InnoDB，那么是15。InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或者是对表进行OPTIMIZE操作，都会导致最大ID丢失。

标签： mysql 数据库面试

本文转载自: https://blog.csdn.net/baidu_27414099/article/details/136421007
版权归原作者 Jsundoku 所有，如有侵权，请联系我们删除。

MySQL 基础

MySQL 索引

MySQL 事务

MySQL 锁

MySQL 优化

MySQL 底层

MySQL 综合

发表评论

“MySQL 面试题”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航