MySQL 面试题_mysql的所有数据类型都可以为null吗

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 与 Oracle 的区别？

MySQL 和 Oracle 都是关系型数据库。

• 开源性：MySQL 是开源免费的数据库，可以根据企业需要实现定制化，Oracle 是不开源并且需要收费的。
• 数据类型：对于字符类型，MySQL 支持 CHAR 和 VARCHAR，Oracle 支持 CHAR，NCHAR，VARCHAR2 和 NVARCHAR2。
• 主键使用：MySQL 支持主键，一般使用自动增长类型，Oracle 没有自动增长，主键一般使用序列，插入记录时将序列号的下一值付给该字段即可，ORM 框架只需要 native 主键生成策略即可。
• 模糊查询：MySQL 使用 like 进行模糊查询，Oracle 也可以用 like ，但这种方法不能使用索引，效率低，一般用字符串比较函数 instr(字段名，‘字符串’)>0 模糊查询。
• 临时表：MySQL 中的临时表是仅对当前用户会话可见的数据库对象，一旦会话结束这些表将自动删除。Oracle 中临时表一旦创建就会存在，直到它们被显式删除，并且对具有适当权限的所有会话都可见。但临时表中的数据仅对将数据插入表中的用户会话可见，并且数据可能在事务或用户会话期间持续存在。
• 安全验证：MySQL 使用用户名，密码和位置来验证用户，Oracle 使用更多安全校验，如用户名，密码，配置文件，本地身份验证，外部身份验证，高级安全增强功能等。
• 数据存储：与 Oracle 相比，MySQL 没有表空间，角色管理，快照，同义词和包以及自动存储管理。
• 数据备份：Oracle 提供不同类型的备份工具，如冷备份，热备份，导出，导入，数据泵。Oracle 提供了最流行的称为 Recovery Manager（RMAN）的备份实用程序。通过 RMAN 可以使用极少的命令或存储脚本自动化我们的备份调度和恢复数据库。MySQL 使用 mysqldump 和 mysqlhotcopy 备份工具。

获取当前 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。

索引的优缺点：

• 优点 ：
  • 可以通过唯一索引保证数据唯一性
  • 加快数据的查询速度
  • 可以加速表和表的连接
  • 在查询过程中使用索引，还会触发 MySQL 隐藏的优化器，提高查询性能
• 缺点 ：
  • 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
  • 索引需要使用物理文件存储，也会耗费一定空间。
  • 当对表数据进行更新和删除时，需要实时维护索引记录，降低了数据的维护速度，有可能会出现数据更新了，但是索引没更新上（这种情况概率比较小）。

索引的基本原理？

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

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

索引创建原则？

哪些情况下需要创建索引：

• 选择唯一性索引：唯一性索引的值是唯一的，可以更快速的通过索引来确定某条记录
• 为经常需要作为查询条件、排序、分组和联合操作的字段建立索引
• 尽量使用数据量少的索引，如果索引的值很长，那么查询的速度会受到影响
• 尽量使用前缀来作为索引
• 尽量选择区分度高的列作为索引，区分度高是指字段不重复的列，比如不要给性别或状态等列建立索引
• 尽量的扩展索引，而不是新建索引
• 在 where 子句中使用到的字段建立联合索引

哪些情况下不建议创建索引：

• 重复度比较高的列不要设置索引
• 对于定义为 text、image、bit 类型的列不要建索引
• 如果数据列经常被修改的字段不要建立索引
• 数据量小的表不要建索引，也许全表扫性能会更高
• 不建议对无序的字段建立索引，如 UUID
• 不要对一个字段建立多个联合索引

索引的类型？

MySQL 索引的数据结构？

（1）二叉树

• 对半搜索，每个节点最多两个子节点
• 左侧子节点小于根节点，右侧子节点大于等于根节点
• 二叉排序树的查找性能在 O(Log2n) 到 O(n) 之间

正常二叉树：

极端二叉树：查找时间复杂度为 O(n)

（2）平衡二叉树

• 满足二叉树的特性
• 任何节点的两个子树的高度最大差为1
• 如果对平衡二叉树进行删除和新增，那么会破坏平衡，就会出发旋转，最终达到平衡，也成自平衡二叉树

虽然能做到平衡了，避免了O(n)，但是每次都进行频繁的左旋或右旋。

（3）红黑树

• 也是自平衡（但没有高度差为1的限制，它有另外一套规则）
• 每个结点是红的或者黑的
• 根结点是黑的
• 每个叶子结点是黑的（NULL）
• 树中不存在两个相邻的红色结点（即红色结点的父结点和子结点不能均是红色）
• 从任意一个结点（包括根结点）到其任何后代 NULL 结点（默认是黑色的）的每条路径都具有相同数量的黑色结点。​​​​​​​

（4）Hash

• 对索引的 key 进行一次 hash 计算就可以定位出数据存储的位置
• 很多时候 Hash 索引要比 B+ 树索引更高效
• 仅能满足 “=”，“IN”，不支持范围查询
• 存在 hash 冲突问题

（5）B-Tree

• 叶节点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列
• 数据节点存在每个节点上

（6）B+Tree

• 非叶子节点不存储 data，只存储索引（冗余），可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用双向指针连接，提高区间访问的性能

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

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

 MySQL 中有那些锁？

（1）MySQL 锁

锁（Lock）是数据库在并发访问时保证数据一致性和完整性的主要机制。

MySQL 数据库的并发操作：

• 读-读操作不需要加锁
• 写-写操作需要加锁
• 读-写操作使用 MVCC 实现（InnoDB）

Lock 与 Latch

• Lock 的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般 Lock 的对象仅在事务 commit 或 rollback 后进行释放（不同事务隔离级别释放的时间可能不同）。
• Latch 称为闩锁（轻量级的锁），因为其要求锁定的时间必须非常短，若持续的时间长，则应用的性能会非常差。在InnoDB引擎中，Latch 又可以分为 mutex（互斥量）和 rwlock（读写锁）。其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。

Lock 与 Latch 比较：

MySQL 中按照锁的粒度可以分为表级锁、页级锁和行级锁：

表级锁：

• 锁定整个表。
• 特点：开销小、加锁解锁快，锁定的粒度大，锁冲突的概率高、并发性能低。
• 使用表级锁的主要是 InnoDB、MyISAM，MEMORY，CSV 等一些非事务性存储引擎。

页级锁：

• 页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间。
• 特点：开销和加锁时间界于表锁和行锁之间，锁定粒度界于表锁和行锁之间，并发度一般。会出现死锁.
• 使用页级锁定的主要是 BerkeleyDB 存储引擎。

行级锁：

• 锁定表中的一行或多行，没有被锁定的数据行可以正常访问。
• 特点：开销大，加锁慢，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。会出现死锁。
• 使用行级锁：InnoDB（默认行级锁）

（2）行级锁

lnnoDB 实现了以下两种类型的行锁：

• 共享锁（S）：
  • 共享锁（Shared Lock）又称读锁，简称 S 锁。
  • 当一个事务对数据加上读锁之后，允许获得该锁的事务读取数据行，同时其他事务只能对该数据加读锁，而不能加写锁，直到所有的读锁释放之后其他事务才能对该数据加写锁。
  • 共享锁主要是为了支持并发读取数据，读取数据的时候不支持修改，避免出现重复读的问题。
• 排他锁（X）：
  • 排他锁（Exclusive Lock）又称写锁，简称 X 锁。
  • 当一个事务对数据加上写锁之后，允许获得该锁的事务更新或删除数据行，同时其他事务将不能对该数据加任何锁，直到该锁释放。
  • 排他锁的目的是在对数据修改时，不允许其他事务同时修改和读取，避免出现脏读和幻读的问题。

共享锁和排他锁的兼容性：

其他行锁：

• 记录锁：记录锁属于行锁的一种，只不过记录锁的锁定范围只有表中的某一行。
• 间隙锁：间隙锁属于行锁的一种，间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。间隙锁 是 InnoDB 在 RR(可重复读) 隔离级别 下为了解决幻读问题时引入的锁机制。
• 临键锁：临键锁属于行锁的一种，并且是 InnoDB 行锁的默认算法。临键锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。通过临建锁可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

（3）表级锁

lnnoDB 实现了以下两种类型的表锁：一种是 LOCK TABLES 语句手动指定的锁，另一种是由InnoDB自动添加的意向锁。

意向锁：

• 意向共享锁（IS）：事务在给数据行加行级共享锁之前，必须先取得该表的 IS 锁。
• 意向排他锁（IX）：事务在给数据行加行级排他锁之前，必须先取得该表的 IX 锁。

表级锁的兼容性：

（4）悲观锁和乐观锁

MySQL 基于加锁的机制，可以分为悲观锁和乐观锁：

悲观锁：

• 悲观锁就是对于数据的处理持悲观态度，总认为会发生并发冲突，获取和修改数据时，其他事务会修改数据，所以在整个数据处理过程中，需要将数据锁定。
• 悲观锁的实现，通常依靠数据库提供的锁机制实现，比如 MySQL 的排他锁，select .... for update 来实现悲观锁。
• 悲观锁在并发控制上采取的是先上锁然后再处理数据的保守策略，虽然保证了数据处理的安全性，但也降低了效率。

乐观锁：

• 乐观锁就是对数据的处理持乐观态度，乐观的认为数据一般情况下不会发生冲突，只有提交数据更新时，才会对数据是否冲突进行检测。如果发现冲突了，则返回错误信息给用户，让用户自已决定如何操作。
• 乐观锁的实现不依靠数据库提供的锁机制，需要我们自已实现，实现方式一般是记录数据版本，一种是通过版本号，一种是通过时间戳。给表加一个版本号或时间戳的字段，读取数据时，将版本号或时间戳一同读出，数据更新时，将版本号加1或者时间戳更新。
• 乐观锁是基于程序实现的，所以不存在死锁的情况，适用于读多的应用场景。如果经常发生冲突，上层应用不断的让用户进行重新操作，这反而降低了性能，这种情况下悲观锁就比较适用。

MySQL 死锁的原因及解决？

MySQL 解决死锁的方法：

1. 设置锁等待时间 innodb_lock_wait_timeout，默认是50s。
2. 死锁检测 innodb_deadlock_detect，默认是开启的。如果检测到是死锁，就会取消权重不高的那个事务来解决死锁。死锁等待会消耗数据库 CPU 资源。

# 关闭死锁检测
set global innodb_deadlock_detect=0;
 
# 开启死锁检测
set global innodb_deadlock_detect=1;

死锁预防：

• 减少长事务
• 大事务拆成小事务，需要结合业务进行分析
• 保持加锁顺序一致，尽量一次性锁住所需要的行。
• 业务允许的情况下，降低隔离级别，RR 级别下会有间隙锁，会提高死锁发生的概率。
• 交互式界面中，记得要提交事务

死锁的排查：

1. 通过日志系统及时通知死锁事件，结合业务代码与死锁日志进行分析或者通过 pt-deadlock-logger 监控死锁 elasticsearch+kibana+logstash 搭建日志监测系统。
2. 通过 show engine innodb status 查看最近一次的死锁日志。

MySQL 的 MVCC 机制？

（1）MVCC 机制

MVCC（Multi Version Concurrency Control）被称为多版本并发控制，是指在数据库中为了实现高并发的数据访问，对数据进行多版本处理，并通过事务的可见性来保证事务能看到自己应该看到的数据版本。

MVCC 最大的好处是读不加锁，读写不冲突。在读多写少的系统应用中，读写不冲突是非常重要的，极大的提升系统的并发性能，这也是为什么现阶段几乎所有的关系型数据库都支持 MVCC 的原因，不过目前 MVCC 只在 Read Commited 和 Repeatable Read 两种隔离级别下工作。
 

（2）行记录的三个隐藏字段

• DB_ROW_ID：如果没有为表显式的定义主键，并且表中也没有定义唯一索引，那么 InnoDB 会自动为表添加一个 row_id 的隐藏列作为主键。
• DB_TRX_ID：事务中对某条记录做增删改时，就会将这个事务的事务 ID 写入到 trx_id。
• DB_ROLL_PTR：回滚指针，指向 undo_log 的指针。
   

（3）Undo log 多版本链：

每一条数据都有多个版本，版本之间通过 undo_log 链条进行连接。
 

（4）Read View：

Read View 是 InnoDB 在实现 MVCC 时用到的一致性读视图（consistent read view） ，用于支持  RC(ReadCommitted，读提交）和 RR(Repeatable Read，可重复读）隔离级别的实现。

Read View 简单理解就是对数据在每个时刻的状态拍成照片记录下来，那么之后获取某时刻的数据时就还是原来的照片上的数据，是不会变的。

Read View 中比较重要的字段有4个：

• m_ids：用来表示 MySQL 中哪些事务正在执行，但是没有提交
• min_trx_id：就是 m_ids 里最小的值
• max_trx_id：下一个要生成的事务 id 值，也就是最大事务 id
• creator_trx_id：就是你这个事务的 id

通过 Read View 判断记录的某个版本是否可见：

• trx_id = creator_trx_id：如果被访问版本的 trx_id，与 readview 中的 creator_trx_id 值相同，表明当前事务在访问自己修改过的记录，该版本可以被当前事务访问。
• trx_id < min_trx_id：如果被访问版本的 trx_id，小于 readview 中的 min_trx_id 值，表明生成该版本的事务在当前事务生成 readview 前已经提交，该版本可以被当前事务访问。
• trx_id >= max_trx_id：如果被访问版本的 trx_id，大于或等于 readview 中的 max_trx_id 值，表明生成该版本的事务在当前事务生成 readview 后才开启，该版本不可以被当前事务访问。
• trx_id > min_trx_id && trx_id < max_trx_id：如果被访问版本的 trx_id 值在 readview 的 min_trx_id 和 max_trx_id 之间，就需要判断 trx_id 属性值是不是在 m_ids 列表中：
  • 在：说明创建 readview 时生成该版本的事务还是活跃的，该版本不可以被访问。
  • 不在：说明创建 readview 时生成该版本的事务已经被提交，该版本可以被访问。
     

何时生成 ReadView 快照

• 在读已提交（Read Committed，简称 RC）隔离级别下，每一次读取数据前都生成一个 ReadVlew。
• 在可重复读（Repeatable Read，简称 RR）隔离级别下，在一个事务中，只在第一次读取数据前生成一个 ReadVlew。
   

（5）快照读与当前读

在 MVCC 并发控制中，读操作可以分为两类：快照读（Snapshot Read）与当前读（current Read）。

• 快照读：快照读是指读取数据时不是读取最新版本的数据，而是基于历史版本读取的一个快照信息（mysql 读取 undo_log 历史版本)，快照读可以使普通的 SELECT 读取数据时不用对表数据进行加锁，从而解决了因为对数据库表的加锁而导致的两个如下问题：
  • 解决了因加锁导致的修改数据时无法对数据读取问题。
  • 解决了因加锁导致读取数据时无法对数据进行修改的问题。
• 当前读：当前读是读取的数据库最新的数据，当前读和快照读不同，因为要读取最新的数据而且要保证事务的隔离性，所以当前读是需要对数据进行加锁的（update、delete、insert、select ....lock in share mode、select for update 为当前读）。

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

（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（幻读问题）。在 MySQ L默认的隔离级别（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 中控制内存分配的全局参数?

• 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种：

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