MySQL 面试题

MySQL 基础

数据库的约束与范式？

七大约束：

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

三大范式：

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

MySQL 默认端口？

Mysql 的默认端口是3306。

MySQL 和 PostgreSQL 的区别？

PostgreSQL相对于MySQL的优势：

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

MySQL相对于PG的优势：

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

获取当前 Mysql 版本？

SELECT VERSION(); 用于获取当前 Mysql 的版本。

主键和候选键区别？

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

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

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

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

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

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

Mysql 中的存储引擎？

MySQL 的默认存储引擎是 InnoDB。

InnoDB 和 MyISAM 区别？

• 表主键：MyISAM 允许没有任何索引和主键的表存在，索引都是保存行的地址。InnoDB 如果没有设定主键或者非空唯一索引，就会自动生成一个 6 字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。
• 可移植性、备份及恢复：MyISAM 数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。InnoDB 免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十 G 的时候就相对痛苦了

MySQL 驱动程序？

以下是 Mysql 中可用的驱动程序：

• PHP 驱动程序
• JDBC 驱动程序
• ODBC 驱动程序
• CWRAPPER
• PYTHON 驱动程序
• PERL 驱动程序
• RUBY 驱动程序
• CAP11PHP 驱动程序
• Ado.net5.mxj

MySQL 数据类型？

数值类型：

FLOAT 和 DOUBLE 的区别：

• FLOAT 以8位精度存储在 FLOAT 中，并且有四个字节。
• DOUBLE 存储在 DOUBLE 中，精度为18位，有八个字节。

日期和时间类型：

NOW() 和 CURRENT_DATE() 区别：

NOW() 命令用于显示当前年份，月份，日期，小时，分钟和秒。

CURRENT_DATE() 仅显示当前年份，月份和日期。

字符串类型：

CHAR 和 VARCHAR 区别：

• CHAR 是一种固定长度的类型，长度值范围是1到255，VARCHAR 则是一种可变长度的类型。
• CHAR 和 VARCHAR 类型类似，在存储和检索方面有所不同。
• 当 CHAR 值被存储时，它们被用空格填充到特定长度，检索 CHAR 值时需删除尾随空格。

CHAR_LENGTH 和 LENGTH 区别：

• CHAR_LENGTH 是字符数，而 LENGTH 是字节数。
• Latin 字符的这两个数据是相同的，但是对于 Unicode 和其他编码，它们是不同的。

BLOB 和 TEXT 区别：

BLOB 是一个二进制对象，可以容纳可变数量的数据。有四种类型的 BLOB：

• TINYBLOB
• BLOB
• MEDIUMBLOB 和
• LONGBLOB

它们只能在所能容纳价值的最大长度上有所不同。

TEXT 是一个不区分大小写的 BLOB。四种 TEXT 类型

• TINYTEXT
• TEXT
• MEDIUMTEXT 和
• LONGTEXT

它们对应于四种 BLOB 类型，并具有相同的最大长度和存储要求。

BLOB 和 TEXT 类型之间的唯一区别在于对 BLOB 值进行排序和比较时区分大小写，对 TEXT

值不区分大小写。

什么是非标准字符串类型？

以下是非标准字符串类型：

• TINYTEXT

• TEXT

• MEDIUMTEXT

• LONGTEXT

若一张表中只有一个字段 VARCHAR(N)类型，utf8 编码，则 N 最大值为多少?

由于 utf8 的每个字符最多占用 3 个字节。而 MySQL 定义行的长度不能超过65535，因此 N 的最大值计算方法为：(65535-1-2)/3。减去 1 的原因是实际存储从第二个字节开始，减去 2 的原因是因为要在列表长度存储实际的字符长度，除以 3 是因为 utf8 限制：每个字符最多占用 3 个字节。

Mysql 里记录货币用什么类型？

NUMERIC和DECIMAL类型被Mysql实现为同样的类型，这在SQL92标准允许。他们被用于保存值，该值的准确精度是极其重要的值，例如与金钱有关的数据。当声明一个类是这些类型之一时，精度和规模的能被(并且通常是)指定。

例如：salaryDECIMAL(9,2)

在这个例子中，9(precision) 代表将被用于存储值的总的小数位数，而 2(scale) 代表将被用于存储小数点后的位数。因此，在这种情况下，能被存储在salary列中的值的范围是从 -9999999.99 到 9999999.99。在 ANSI/ISOSQL92 中，句法 DECIMAL(p) 等价于 DECIMAL(p,0)。同样，句法 DECIMAL 等价于 DECIMAL(p,0)，这里实现被允许决定值p。Mysql当前不支持 DECIMAL/NUMERIC 数据类型的这些变种形式的任一种。

这一般说来不是一个严重的问题，因为这些类型的主要益处得自于明显地控制精度和规模的能力。DECIMAL 和 NUMERIC 值作为字符串存储，而不是作为二进制浮点数，以便保存那些值的小数精度。一个字符用于值的每一位、小数点(如果 scale>0)和 “-” 符号(对于负值)。如果 scale 是0，DECIMAL 和 NUMERIC 值不包含小数点或小数部分。

DECIMAL 和 NUMERIC 值得最大的范围与 DOUBLE 一样，但是对于一个给定的 DECIMAL 或 NUMERIC 列，实际的范围可由制由给定列的 precision 或 scale 限制。

当这样的列赋给了小数点后面的位超过指定 scale 所允许的位的值，该值根据 scale 四舍五入。

当一个 DECIMAL 或 NUMERIC 列被赋给了其大小超过指定(或缺省的）precision 和 scale 隐含的范围的值，Mysql 存储表示那个范围的相应的端点值。

MySQL 中 ENUM 的用法？

ENUM 是一个字符串对象，用于指定一组预定义的值，并可在创建表时使用。

Create table size(nameENUM(‘Smail,’Medium’,’Large’);

MySQL 索引

什么是索引？

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

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

索引的优缺点？

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

索引的基本原理？

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

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

索引的类型？

唯一索引比普通索引快吗？

对于写多读少的情况， 普通索引利用 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

COMMIT WORK

（4）分布式事务

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

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

事务隔离级别？

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

1. Read uncommitted：读到未提交数据
2. Read committed：脏读，不可重复读
3. Repeatable read：可重读
4. 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 锁

锁的两个概念” />

Lock

Latch

对象

事务

线程

保护

数据库内容

内存数据结构

持续时间

整个事务过程

临界资源

模式

行锁、表锁锁、意向锁

读写锁、互斥锁

死锁

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

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

存在于

Lock Manager 的哈希表中

每个数据结构的对象中

MySQL 锁分类？

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

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

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

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

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

1. SELECT ... LOCK IN SHARE MODE：对读取的行添加S锁，其他事物可以对这些行添加S锁，若添加X锁，则会被阻塞。
2. 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种行锁的算法，其分别是：

1. Record Lock
   1. 记录锁（行锁），单条索引记录上加锁，锁住的永远是索引，而非记录本身。
   2. 行锁的实现依赖于索引，一旦某个加锁操作没有使用到索引，那么该锁就会退化为表锁。记录锁存在于包括主键索引在内的唯一索引中，锁定单条索引记录。
2. Gap Lock
   1. 间隙锁，在索引记录之间的间隙中加锁（锁定一个范围），并不包括该索引记录本身。
   2. 间隙锁存在于非唯一索引中，锁定开区间范围内的一段间隔，它是基于临键锁实现的。
3. 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：

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

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

锁的优化策略？

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

MySQL 优化

数据库设计方面

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

SQL 方面

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

Java 方面

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

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 中控制内存分配的全局参数” />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种：

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

binlog 文件包含两种类型：

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

binlog 提供了3种记录模式：

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

Binlog文件结构：

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

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

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丢失。