MySQL 常见面试题

MySQL 字段类型

整数类型的 UNSIGNED 属性有什么用？

MySQL 中的整数类型可以使用可选的 UNSIGNED 属性来表示不允许负值的无符号整数。使用 UNSIGNED 属性可以将正整数的上限提高一倍，因为它不需要存储负数值。

例如， TINYINT UNSIGNED 类型的取值范围是 0 ~ 255，而普通的 TINYINT 类型的值范围是 -128 ~ 127。INT UNSIGNED 类型的取值范围是 0 ~ 4,294,967,295，而普通的 INT 类型的值范围是 -2,147,483,648 ~ 2,147,483,647。

对于从 0 开始递增的 ID 列，使用 UNSIGNED 属性可以非常适合，因为不允许负值并且可以拥有更大的上限范围，提供了更多的 ID 值可用。

char和varchar的区别

CHAR

• CHAR类型用于存储固定长度字符串：MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除字符串中的末尾空格同时，CHAR值会根据需要采用空格进行剩余空间填充，以方便比较和检索。但正因为其长度固定，所以会占据多余的空间，也是一种空间换时间的策略；

• CHAR适合存储很短或长度近似的字符串。例如，CHAR非常适合存储密码的MD5值、定长的身份证等，因为这些是定长的值。

• 对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型占用磁盘的存储空间是连续分配的，不容易产生碎片。

• 对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。

VARCHAR：

• VARCHAR类型用于存储可变长度字符串，是最常见的字符串数据类型。它比固定长度类型更节省空间，因为它仅使用必要的空间(根据实际字符串的长度改变存储空间)。

• VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。假设采用latinl字符集，一个VARCHAR(10)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002 个字节，因为需要2个字节存储长度信息。

• VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引擎的处理方式是不一样的。例如，MylSAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。

• 操作内存的方式：对于varchar数据类型来说，硬盘上的存储空间虽然都是根据字符串的实际长度来存储空间的，但在内存中是根据varchar类型定义的长度来分配占用的内存空间的，而不是根据字符串的实际长度来分配的。显然，这对于排序和临时表会较大的性能影响。

VARCHAR(100)和 VARCHAR(10)的区别是什么？

VARCHAR(100)和 VARCHAR(10)都是变长类型，表示能存储最多 100 个字符和 10 个字符。因此，VARCHAR (100) 可以满足更大范围的字符存储需求，有更好的业务拓展性。而 VARCHAR(10)存储超过 10 个字符时，就需要修改表结构才可以。

虽说 VARCHAR(100)和 VARCHAR(10)能存储的字符范围不同，但二者存储相同的字符串，所占用磁盘的存储空间其实是一样的，这也是很多人容易误解的一点。

不过，VARCHAR(100) 会消耗更多的内存。这是因为 VARCHAR 类型在内存中操作时，通常会分配固定大小的内存块来保存值，即使用字符类型中定义的长度。例如在进行排序的时候，VARCHAR(100)是按照 100 这个长度来进行的，也就会消耗更多内存。

DECIMAL 和 FLOAT/DOUBLE 的区别是什么？

DECIMAL 和 FLOAT 的区别是：DECIMAL 是定点数，FLOAT/DOUBLE 是浮点数。DECIMAL 可以存储精确的小数值，FLOAT/DOUBLE 只能存储近似的小数值。

DECIMAL 用于存储具有精度要求的小数，例如与货币相关的数据，可以避免浮点数带来的精度损失。

在 Java 中，MySQL 的 DECIMAL 类型对应的是 Java 类 java.math.BigDecimal。

在 MySQL 中存储金额数据，应该使用什么数据类型?

在数据库中业界常用两种类型来存储金额：bigint和 decimal。

• bigint 代码中用 long。

  • 范围：可以存储的整数范围为-2^63到 2^63-1(在 MySQL 中为 64 位有符号整数)

  • 存储空间：占用8字节(64位)

  • 精度：精确存储整数，但不支持小数部分，存储的金额单位是分

• decimal 代码中使用 BigDecimal。

  • 范围：可以存储的数字范围和小数位数由定义的精度和标度决定

  • 存储空间：存储空间取决于定义的精度和标度，存储较大数值时会占用更多空间

  • 精度：支持高精度的小数运算，精确存储定点数，一般用 decimal(18,6)，18 是总位数，6 是小数

项目中到底选择 Long 还是 BigDecimal?

long 类型保存到分，使得小数位(厘)的数据不好处理(需要手动处理，比较麻烦)，因此精度不够高。

在高精度金额计算场景下不合适，有些三方支付系统是需要抽成的，例千分之三、万分之一等等，这类的抽成要求精度比较高，例如 会得到1234.5678 这样的金额，乘以 100 后仍然无法存储为精确整教，还是小数,，存储则会丢失精度。0.0078 看似很少，但抽成的笔数一多，比如一天 30 万笔，每笔少个 0.0078，每天的差额也得 2340 元！一年不得 85 万！所以高精度的计算不推荐使用 bigint。

BigDeimnal则很活用于高精度金额场景，且非常灵活，只不过相对于long性能会差一些，但是在大部分业务上我个人认为可以忽略这个性能问题，除非是特殊场景要求极端性能，所以一般情况下我推荐使用 gDecimal.

int(10)和char(10)的区别？

int(10)中的10表示的是显示数据的长度，而char(10)表示的是存储数据的长度。

为什么不推荐使用 TEXT 和 BLOB？

数据库规范通常不推荐使用 BLOB 和 TEXT 类型，这两种类型具有一些缺点和限制，例如：

• 不能有默认值。

• 在使用临时表时无法使用内存临时表，只能在磁盘上创建临时表（《高性能 MySQL》书中有提到）。

• 检索效率较低。

• 不能直接创建索引，需要指定前缀长度。

• 可能会消耗大量的网络和 IO 带宽。

• 可能导致表上的 DML 操作变慢。

• ……

TEXT 类型最大可以存储多长的文本?

TEXT 是非标准字符串类型，除了它还有 TINYTEXT、MEDIUMTEXT 和 LONGTEXT，一般我们存储一些不确定的较长文本才会使用到它们，例如文章之类的。

它们的存储的文本长度汇总如下:

• TINYTEXT：最大长度为 255 字节。

• TEXT：最大长度为 65,535 字节(约 64 KB)。

• MEDIUMTEXT：最大长度为16,777,215字节(约16 MB)

• LONGTEXT：最大长度为 4,294,967,295 字节(约4GB)。

DATETIME 和 TIMESTAMP 的区别是什么？

DATETIME 类型没有时区信息，TIMESTAMP 和时区有关。

TIMESTAMP 只需要使用 4 个字节的存储空间，但是 DATETIME 需要耗费 8 个字节的存储空间。但是，这样同样造成了一个问题，Timestamp 表示的时间范围更小。

• DATETIME：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

• Timestamp：1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

Boolean 类型如何表示？

MySQL 中没有专门的布尔类型，而是用 TINYINT(1) 类型来表示布尔值。TINYINT(1) 类型可以存储 0 或 1，分别对应 false 或 true。

为什么不建议使用null作为默认值

Mysql不建议用Null作为列默认值不是因为不能使用索引，而是因为：

• 索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化。比如进行索引统计时，count(1),max(),min() 会省略值为NULL 的行。

• NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式 (opens new window)中至少会用 1 字节空间存储 NULL 值列表。建议用"“或默认值0来代替NULL

不建议使用null作为默认值，并且建议必须设置默认值，原因如下：

• 既然都不可为空了，那就必须要有默认值，否则不插入这列的话，就会报错；

• 数据库不应该是用来查问题的，不能靠mysql报错来告知业务有问题，该不该插入应该由业务说了算；

• 对于DBA来说，允许使用null是没有规范的，因为不同的人不同的用法。

但像合同生效时间、获奖时间 等这种不可控字段，是可以不设置默认值的，但同样需要not null

为什么禁止使用外键

• 外键会降低数据库的性能。在MySQL中，外键会自动加上索引，这会使得对该表的查询等操作变得缓慢，尤其是在大型数据表中。

• 外键也会限制了表结构的调整和更改。在实际应用中，表结构经常需要进行更改，而如果表之间使用了外键约束，这些更改可能会非常难以实现。因为更改一个表的结构，需要涉及到所有以其为父表的子表，这会导致长时间锁定整个数据库表，甚至可能会导致数据丢失。

• 在MySQL中，外键约束可能还会引发死锁问题。当想要对多个表中的数据进行插入、更新、删除操作时，由于外键约束的存在，可能会导致死锁，需要等待其他事务释放锁。

• MySQL中使用外键还会增加开发难度。开发人员需要处理数据在表之间的关系，而这样的处理需要花费更多的时间和精力，以及对数据库的深入理解。同时，外键也会增加代码的复杂度，使得SQL语句变得难以理解和调试。

在阿里巴巴开发手册中也有提到，传送门

使用自增主键有什么好处？

自增主键可以让主键索引尽量地保持递增顺序插入，避免了页分裂，因此索引更紧凑，在查询的时候，效率也就更高。

自增主键保存在什么地方？

不同的引擎对于自增值的保存策略不同：

• MyISAM引擎的自增值保存在数据文件中。

• 在MySQL8.0以前，InnoDB引擎的自增值是存在内存中。MySQL重启之后内存中的这个值就丢失了，每次重启后第一次打开表的时候，会找自增值的最大值max(id)，然后将最大值加1作为这个表的自增值；MySQL8.0版本会将自增值的变更记录在redo log中，重启时依靠redo log恢复。

自增主键一定是连续的吗？

不一定，有几种情况会导致自增主键不连续。

1、唯一键冲突导致自增主键不连续。当我们向一个自增主键的InnoDB表中插入数据的时候，如果违反表中定义的唯一索引的唯一约束，会导致插入数据失败。此时表的自增主键的键值是会向后加1滚动的。下次再次插入数据的时候，就不能再使用上次因插入数据失败而滚动生成的键值了，必须使用新滚动生成的键值。

2、事务回滚导致自增主键不连续。当我们向一个自增主键的InnoDB表中插入数据的时候，如果显式开启了事务，然后因为某种原因最后回滚了事务，此时表的自增值也会发生滚动，而接下里新插入的数据，也将不能使用滚动过的自增值，而是需要重新申请一个新的自增值。

3、批量插入导致自增值不连续。MySQL有一个批量申请自增id的策略：

• 语句执行过程中，第一次申请自增id，分配1个自增id

• 1个用完以后，第二次申请，会分配2个自增id

• 2个用完以后，第三次申请，会分配4个自增id

• 依次类推，每次申请都是上一次的两倍（最后一次申请不一定全部使用）

如果下一个事务再次插入数据的时候，则会基于上一个事务申请后的自增值基础上再申请。此时就出现自增值不连续的情况出现。

4、自增步长不是1，也会导致自增主键不连续。

InnoDB的自增值为什么不能回收利用？

主要为了提升插入数据的效率和并行度。

假设有两个并行执行的事务，在申请自增值的时候，为了避免两个事务申请到相同的自增 id，肯定要加锁，然后顺序申请。

假设事务 A 申请到了 id=2， 事务 B 申请到 id=3，那么这时候表 t 的自增值是 4，之后继续执行。

事务 B 正确提交了，但事务 A 出现了唯一键冲突。

如果允许事务 A 把自增 id 回退，也就是把表 t 的当前自增值改回 2，那么就会出现这样的情况：表里面已经有 id=3 的行，而当前的自增 id 值是 2。

接下来，继续执行的其他事务就会申请到 id=2，然后再申请到 id=3。这时，就会出现插入语句报错“主键冲突”。

而为了解决这个主键冲突，有两种方法：

• 每次申请 id 之前，先判断表里面是否已经存在这个 id。如果存在，就跳过这个 id。但是，这个方法的成本很高。因为，本来申请 id 是一个很快的操作，现在还要再去主键索引树上判断 id 是否存在。

• 把自增 id 的锁范围扩大，必须等到一个事务执行完成并提交，下一个事务才能再申请自增 id。这个方法的问题，就是锁的粒度太大，系统并发能力大大下降。

可见，这两个方法都会导致性能问题。

因此，InnoDB 放弃了“允许自增 id 回退”这个设计，语句执行失败也不回退自增 id。

自增主键会遇到什么问题？

使用数据库的自增主键，虽然在很多情况下都很方便，但也可能遇到一些问题：

• 插入性能问题：对于非常高并发的插入操作，自增主键可能会成为性能瓶颈。因为每次插入新记录时，都需要获取一个新的自增ID，这个操作是串行的，无法并发执行。

• 主键耗尽：如果表的记录非常多，可能会出现自增主键耗尽的情况。尤其是对于定义为整型的自增主键，如果插入的记录数超过了整型的最大值，就无法再插入新的记录。

• 分布式系统问题：在分布式系统中，如果多个数据库节点都需要生成自增主键，就需要保证生成的主键在全局是唯一的。这通常需要引入额外的机制或工具，比如分布式ID生成器。

如果 InnoDB 表没有配置主键，有最大值上限吗?

如果我们在 InnoDB 表中不配置主键，那么默认 InnoDB 会创建一个不可见的长度为 6 个字节的 row id.

InnoDB 在全局维护了一个 dict_sys.row_id 值，所有需要用到 row id 的表，每次插入一行数据，都会获取这个值，然后将其+1。

这个值的范周是 0~2^48-1。如果这个值达到上限后，又会从0开始，然后继续循环，如果插入的新数据的 row id 在表中已存在，那么老的数据会被这个新数据覆盖，不会产生有任何报错。

如果要验证这个情况，可以通过 gbd 动态地将 mysgl 的 dict sys.row id 修改，执行多次插入就能发现覆盖的问题。

一般生产环境中不会有表没有设置主键的情况，这个仅做了解即可。

utf8 、utf8mb3和 utf8mb4的区别

utf8mb3：只支持最长三个字节的BMP（Basic Multilingual Plane，基本多文种平面）字符（不支持补充字符）。

utf8mb4：mb4即 most bytes 4，即最多使用4个字节来表示完整的UTF-8，具有以下特征：

• 支持BMP和补充字符。

• 每个多字节字符最多需要四个字节。

utf8mb4是utf8的超集并完全兼容它，是MySQL 在 5.5.3 版本之后增加的一个新的字符集，能够用四个字节存储更多的字符，几乎包含了世界上所有能看到见的语言字符。

• 差异比较

差异点utf8mb3utf8mb4最大使用字节数34支持字符类型BMPBMP+其它字符字符类型常见的 Unicode 字符常见的 Unicode 字符 + 部分罕用汉字 + emoji表情 + 新增的 Unicode 字符等Unicode范围U0000 - U+FFFF（即BMP)U0000 - U+10FFFF占用存储空间略小（如CHAR(10) 需要10 * 3 = 30 个字节的空间；VARCHAR 类型需要额外使用1个字节来记录字符串的长度）稍大（如CHAR(10) 需要 10 * 4 = 40 个字节的空间；VARCHAR 类型需要额外使用2个字节来记录字符串的长度）兼容性切换至utf8mb4 一般不会有问题，但要注意存储空间够不够、排序规则是否变化切换至utf8mb3可能会有问题，字符丢失、报错或乱码安全性稍低，更容易被恶意字符串攻击较高，保留恶意字符串，然后报错或乱码提示

如何选择？一句话就是，根据具体的业务需求和实际情况，选择最合适的字符集。

Mysql使用

在设计表(建表)时需要注意什么?

设计表的时候，在满足业务需求的情况下，需要额外考虑表结构的高效性、扩展性以及维护性

• 选择合适的数据类型：为字段选择合适的数据类型可以有效减少存储空间，并提高查询效率。例如：

  • 使用 INT而不是 BIGINT，前提是如果数据不会超出 INT范围。

  • 使用 VARCHAR 而不是 TEXT，如果字段长度比较短且可变。

  • 使用 DATE、DATETIME或 TIMESTAMP 而不是 VARCHAR 来存储日期时间信息。

• 主键与唯一约束：主键是表的唯一标识符，每个表应该有一个主键。如果需要确保某些字段唯一性，可以使用 唯一约束(UNIQUE)。

• 索引的设计：索引是提高查询性能的关键。但是设计时应避免过多索引，以免对写操作造成负担。对于经常查询的字段(如WHERE、JOIN、ORDER BY 中使用的字段)应该创建索引。考虑 复合索引，可以将多个列组成一个索引，优化复合查询性能。避免在低基数列(如布尔值、性别字段)上创建索引，因为它们不会带来太大的查询性能提升。

• 表的范式化 (Normalizaton)：规范化(通常遵循到3NF)有助于消除数据冗余，提高数据一致性，避免数据更新异常。但在某些场景下，可以选择一定的 反规范化 来提高查询性能。例如:

  • 冗余一些常用字段，避免关联表查询，提升性能。

  • 预留一些扩展字段，例如 extendld 等，便于后续扩展。

  • 部分格式不可控字段可以设计为json 格式，防止频繁变更表结构。

• 外键与关联设计：虽然在关系型数据的场景下，使用外键(FOREIGN KEY)可以确保数据一致性。但是外键约束会影响性能，所以一般现在互联网公司不使用外键。

相比于 Oracle，MySQL 的优势有哪些?

• MySQL有免费版本，成本低，Oracle 数据库的许可费用和维护成本较高

• MySQL是开源的，有广泛的社区支持和丰富的文档资源，能快速上手和解决问题。

• MySQL适合小型和中型应用，相比 Oracle 资源占用较少，更加轻量

而 Oracle 在处理大型企业级应用、复杂事务、并发控制和高级功能(如高级分析、数据仓库支持等)方面具有更强的能力,本身 MySQL 最初的思想就是开源、简便易用，也因为这个定位主要场景在互联网企业中使用，互联网的爆发式增长，成就了 MySQL。Oracle 起步早，结构严谨、高可用、高性能且安全，在传统行业(运输、制造、零售、金融等等)中早早的占据了核心地位

总结来说 Oracle 用起来会更省心一些，但是花钱！MySQL 相比而言会更操心一些，但是省钱！

什么是数据库的逻辑删除?数据库的物理删除和逻辑删除有什么区别?

逻辑删除是一种将数据标记为已删除但实际不会从数据库中移除的删除方式。

一般是在表中添加一个表示删除状态的字段，如 is_deleted，默认是0 表示未删除，1 表示已删除, 物理删除则是直接从数据库中删除记录。

一般业务上都是使用逻辑删除，便于后续的数据分析、追溯等。

什么是数据库的逻辑外键?数据库的物理外键和逻辑外键各有什么优缺点?

逻辑外键是一种在应用程序层面上管理和维护数据完整性的方法，而不是通过数据库本身的外键约束。主要是利用应用程序代码来保证引用的完整性

逻辑外键的优缺点

优点：

• 灵活性高：应用程序层面控制，可以更灵活地实现复杂的业务逻辑。

• 性能优化：避免了数据库层面的约束检查，可以在某些情况下提高性能。

• 跨数据库兼容性：逻辑外键在不同类型的数据库之间更容易迁移。

缺点：

• 代码复杂性增加：需要在应用程序代码中手动实现和维护引用完整性，增加了代码的复杂性和错误的可能性

• 一致性风险：如果应用程序代码未正确实现引用完整性检查，可能导致数据不一致。

• 维护成本高：逻辑外键需要开发人员持续关注和维护，增加了维护成本。

物理外键的优缺点

优点：

• 自动维护引用完整性：数据库会自动检查和维护外键约束，确保数据的一致性。

• 减少应用层复杂性：开发人员不需要手动管理引用完整性，减少了代码的复杂性和错误的可能性

• 数据完整性保障：数据库层面的约束能够更有效地防止非法数据的插入或更新。

缺点：

• 性能开销：外键约束会增加插入、更新和删除操作的开销，特别是在处理大量数据时。

• 迁移和复制的复杂性：在进行数据库迁移或复制时，外键约束可能会增加复杂性，需要小心处理。

• 灵活性较低：物理外键在某些复杂业务逻辑下可能不够灵活，需要更多的手动控制。

为什么不推荐使用外键？

阿里巴巴Java开发手册中写到 可以看到，主要是因为数据库的外键会产生级联更新从而导致性能问题。因为现在很多互联网公司都是高并发大流量场景，所以一般互联网公司不推荐使用外键。

数据库外键性能问题盘点：

1. 级联更新：按照手册举例，如果学生表的ID 被修改，数据库同时需要改成绩表中的对应的学生 ID，如果还有其他关联表则都需要被修改。在高并发数据量大的情况下，一次修改会产生意料之外的级联更新使得数据库压力过大，导致系统其他操作数据库的请求阻塞，很可能导致系统全面崩盘。

2. 检查维护：不是拿学生成绩举例，因为设置了外键，插入成绩表对应的学生ID 时，数据库需要去检查这个学生ID 在对应的学生表是否存在，来保证数据外键完整性约束除了插入，删除、更新相关外键，数据库都需要去检查数据的完整性，这就产生了性能开销。

3. 锁问题：如果多个事务并发修改学生表，那么对应锁定的数据涉及的成绩表也需要被锁定，同理并发修改成绩表，那么对应的学生的表的数据也需要被锁定，关联外键越多锁定的数据也就越多。锁的数据多除了性能问题，还可能会带来死锁的问题。

数据库外键业务问题盘点：

1. 无法支持复杂的业务控制：不说其他复杂的情况，就拿删除数据使用逻辑删除的情况，数据库的外键就无法满足级联修改，还是需要开发人员编写业务代码手动控制

2. 分库分表：数据库的外键约束只能控制一个数据库实例，跨实例的情况下无法满足。

设计数据库表时，关联表和在一个表中加冗余字段关联各有什么优势?

使用关联表更利于保持数据强一致性，且减少数据冗余，适合数据高度复用，特别是多对多场景。

而在表中添加冗余字段可以提升查询性能，减少JOIN 简化查询逻辑，适用于对性能要求高的业务。

冗余字段是一种反规范化设计，但是大部分互联网企业都采用冗余字段的设计，性能优先，且一致性的情况通过代码可以解决。

MySQL数据如何同步到Redis缓存？

有两种方案：

1、通过MySQL自动同步刷新Redis，MySQL触发器+UDF函数实现。

过程大致如下：

1. 在MySQL中对要操作的数据设置触发器Trigger，监听操作

2. 客户端向MySQL中写入数据时，触发器会被触发，触发之后调用MySQL的UDF函数

3. UDF函数可以把数据写入到Redis中，从而达到同步的效果

2、解析MySQL的binlog，实现将数据库中的数据同步到Redis。可以通过canal实现。canal是阿里巴巴旗下的一款开源项目，基于数据库增量日志解析，提供增量数据订阅&消费。

canal的原理如下：

1. canal模拟mysql slave的交互协议，伪装自己为mysql slave，向mysql master发送dump协议

2. mysql master收到dump请求，开始推送binary log给canal

3. canal解析binary log对象（原始为byte流），将数据同步写入Redis。

为什么阿里Java手册禁止使用存储过程？

先看看什么是存储过程。

存储过程是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程主要有以下几个缺点：

1. 存储过程难以调试。存储过程的开发一直缺少有效的 IDE 环境。SQL 本身经常很长，调试要把句子拆开分别独立执行，非常麻烦。

2. 移植性差。存储过程的移植困难，一般业务系统总会不可避免地用到数据库独有的特性和语法，更换数据库时这部分代码就需要重写，成本较高。

3. 管理困难。存储过程的目录是扁平的，而不是文件系统那样的树形结构，脚本少的时候还好办，一旦多起来，目录就会陷入混乱。

4. 存储过程是只优化一次，有的时候随着数据量的增加或者数据结构的变化，原来存储过程选择的执行计划也许并不是最优的了，所以这个时候需要手动干预或者重新编译了。

什么是数据库的视图?

数据库的视图是一个虚拟表，它可以包含一个或多个表的数据，并且可以对这些数据进行筛选、计算、排序等操作。

它并不存储实际的数据，而是通过查询其他表的数据来生成的。视图可以简化复杂的查询、增强数据安全性(限制访问某些数据)以及提供数据的不同表示方式。

什么是数据库的游标?

数据库游标是一个数据库对象，使得应用程序可以逐行访问查询结里集，而不仅仅是一次性处理所有结果。

它允许开发者在结果集中的行之间移动，方便进行复杂的数据操作，比如更新或删除特定的行。

Mysql基本架构

详细内容查看：Mysql语句执行流程

MySQL主要分为 Server 层和存储引擎层：

• Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。

• 存储引擎： 主要负责数据的存储和读取。server 层通过api与存储引擎进行通信。

MySQL Server 层主要由下面几部分构成：

• 连接器：建立连接，管理连接、校验用户身份；

• 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；

• 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；

• 执行 SQL：执行 SQL 共有三个阶段：

  • 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。

  • 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划（选择使用哪个索引）；

  • 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

查询语句执行流程？

查询语句的执行流程如下：权限校验、查询缓存、分析器、优化器、权限校验、执行器、引擎。

举个例子，查询语句如下：

1. 首先，通过连接器，客户端与MySQL服务器建立连接，并完成身份认证和权限验证过程。在此过程中，客户端需要提供用户名和密码以证明其合法性，服务器则会对这些信息进行核对。

2. 检查是否开启缓存。MySQL 8.0之前，Query Cache 确实会缓存完全相同的查询结果，以便重复执行相同查询时直接返回缓存数据。然而，MySQL 8.0及以后版本已经完全弃用Query Cache，因此在MySQL 8.0及更高版本中这一步骤不在适用。

3. MySQL的解析器会对查询语句进行解析，检查语法是否正确，并将查询语句转换为内部数据结构。预处理器则会根据MySQL的规则进一步检查解析树是否合法，如检查数据表或数据列是否存在等。

4. 优化器会根据查询语句的结构、表的统计信息等因素，生成多个可能的执行计划，并通过成本估算器选出最优的执行计划。两种执行方案，先查 id > 1 还是 name = ‘’，优化器根据自己的优化算法选择执行效率最好的方案；这一步旨在提高查询效率，降低资源消耗。

5. 执行器按照优化器选择的执行计划，调用存储引擎的API来执行查询。存储引擎负责实际的数据存储和检索，根据执行器的请求，读取或写入数据。

6. 存储引擎负责实际的数据存储和检索工作，根据执行器的请求，读取或写入数据。

7. 如果开启了Query Cache且查询结果能够命中缓存，查询结果会从缓存中直接返回。而如果没有开启Query Cache或缓存没有命中，MySQL会直接返回查询结果。

更新语句执行过程？

更新语句执行流程如下：分析器、权限校验、执行器、引擎、redo log（prepare状态）、binlog、redo log（commit状态）

举个例子，更新语句如下：

具体的执行流程如下图：

1. 找存储引擎取到 id = 1 这一行记录。

2. 根据主键索引树找到这一行，如果 id = 1 这一行所在的数据页本来就在内存池（Buffer Pool）中，就直接返回给执行器；否则，需要先从磁盘读入内存池，然后再返回。

3. 记录Undo Log日志，对数据进行备份，便于回滚。

4. 拿到存储引擎返回的行记录，把 name 字段设置为 “”，得到一行新的记录，然后再调用存储引擎的接口写入这行新记录。

5. 将这行新数据更新到内存中，同时将这个更新操作记录到 Redo Log 里面，为 Redo Log 中的事务打上 prepare 标识。然后告知执行器执行完成了，随时可以提交事务。

6. 生成这个操作的 Binlog，并把 Binlog 写入磁盘。

7. 提交事务。

8. 把刚刚写入的 Redo Log 状态改成提交（commit）状态，更新完成。

为什么记录完redo log，不直接提交，而是先进入prepare状态？

假设先写redo log直接提交，然后写binlog，写完redo log后，机器挂了，binlog日志没有被写入，那么机器重启后，这台机器会通过redo log恢复数据，但是这个时候binlog并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。

MySQL的查询优化器如何选择执行计划?

一般需要经过以下几个步骤:

1. 将 SQL 语句解析为解析树。

2. 预处理，包括语法检查、权限验证、查询重写(例如常量表达式计算、子查询展开等)

3. 生成多个执行计划，并选择成本最低的执行计划。

实际上优化器的内部实现方式比较复杂，我们仅需着重关心它的成本计算，这样能更好地理解 SQL的执行计划是如何选择的。

MySQL会根据成本来选择最终应用的索引，这里成本主要包括 I/O 成本和 CPU 成本。

所谓的 I/O 成本其实指的是把数据从磁盘加载到内存的成本。SQL是以页的形式来读取数据的，即使你只要一条数据，但是实际读取的还是整页的数据，因为根据空间局部性原理，这条数据被读取，那么距离它空间近的数据，也有很大概率会被读取，因此相的的数据也应该被加载到内存中，所以MySQL 默认读取一页。在优化器内，读取一页的成本记为1。

数据从磁盘读取到内存后，需要比较、排序等，这些操作需要占用 CPU 资源，因此优化器以扫描的行记为成本，一行的成本为 0.2。

因此最终的成本计算就是看 扫描行数 * 0.2+数据长度/16kb=成本。对比所有索引的成本，最终选择最低成本的索引

扩展查询优化器的优化

查询重写：优化器会对查询进行重写，来简化查询动作或消除冗余查询。例如

1. 子查询优化：将子查询转换为连接查询，或者将子查询展开(unroll)。

2. 常量表达式计算：在预处理阶段计算常量表达式，以简化查询。

表连接优化：优化器会评估不同的表连接顺序，并选择成本最低的连接顺序。简单来说优化器会选择行数更少的表优先进行连接，以减少中间结果集的大小。例如:

1. 嵌套循环连接(Nested LoopJoin)：对于小表或带索引的连接使用

2. 哈希连接(Hash Join)：对于大表的连接使用。

3. 排序合并连接(Sort-MergeJoin)：对于大表的排序后合并连接使用

为什么说MySQL 一般单表不要超过 2000W 行？

总的来说，是因为超过2000W行后，B+树的层级会变高，导致IO次数增多

• MySQL 的表数据是以页的形式存放的，页在磁盘中不一定是连续的。

• 页的空间是 16K, 并不是所有的空间都是用来存放数据的，会有一些固定的信息，如，页头，页尾，页码，校验码等等。

• 在 B+ 树中，叶子节点和非叶子节点的数据结构是一样的，区别在于，叶子节点存放的是实际的行数据，而非叶子节点存放的是主键和页号。

• 索引结构不会影响单表最大行数，2000W 只是推荐值，超过了这个值可能会导致 B + 树层级更高，影响查询性能。

详情可以看这篇文章：为什么说MySQL 一般单表不要超过 2000W 行？；类似的：MySQL 三层 B+ 树能存多少数据，这种问题也能在文章中找到答案

为啥设计单页大小16k?

MySQL索引采用的是B+树数据结构，每个叶子节点（叶子块）存储一个索引条目的信息。而MySQL使用的是页式存储（Paged storage）技术，将磁盘上的数据划分为一个个固定大小的页面，每个页面包含若干个索引条目。

为了提高索引查询效率和降低磁盘I/O的频率，MySQL设置了16KB的单页大小。这是因为在MySQL中：

• 内存大小限制：MySQL的索引需要放在内存中进行查询，如果页面过大，将导致索引无法完全加载到内存中，从而影响查询效率。

• 磁盘I/O限制：当需要查询一个索引时，MySQL需要把相关的页面加载到内存中进行处理，如果页面过大，将增加磁盘I/O的开销，降低查询效率。

• 索引效率限制：在B+树数据结构中，每个叶子节点存储着一个索引条目，因此如果每个页面能够存放更多索引条目，就可以减少B+树结构的深度，从而提高索引查询效率。

综上所述，MySQL索引单页大小设置为16KB可以兼顾内存大小、磁盘I/O和索引查询效率等多方面因素，是一种比较优化的方案。需要注意的是，对于某些特殊的应用场景，可能需要根据实际情况对单页大小进行调整。

存储引擎

常见的存储引擎有哪些？

MySQL中常用的四种存储引擎分别是： MyISAM、InnoDB、MEMORY、ARCHIVE。MySQL 5.5版本后默认的存储引擎为InnoDB。

InnoDB存储引擎

InnoDB是MySQL默认的事务型存储引擎，使用最广泛，基于聚簇索引建立的。InnoDB内部做了很多优化，如能够自动在内存中创建自适应hash索引，以加速读操作。

优点：支持事务和崩溃修复能力；引入了行级锁和外键约束。

缺点：占用的数据空间相对较大。

适用场景：需要事务支持，并且有较高的并发读写频率。

MyISAM存储引擎

数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，可以使用MyISAM引擎。MyISAM会将表存储在两个文件中，数据文件.MYD和索引文件.MYI。

优点：访问速度快。

缺点：MyISAM不支持事务和行级锁，不支持崩溃后的安全恢复，也不支持外键。

适用场景：对事务完整性没有要求；表的数据都会只读的。

MEMORY存储引擎

MEMORY引擎将数据全部放在内存中，访问速度较快，但是一旦系统奔溃的话，数据都会丢失。

MEMORY引擎默认使用哈希索引，将键的哈希值和指向数据行的指针保存在哈希索引中。

优点：访问速度较快。

缺点：

1. 哈希索引数据不是按照索引值顺序存储，无法用于排序。

2. 不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。

3. 只支持等值比较，不支持范围查询。

4. 当出现哈希冲突时，存储引擎需要遍历链表中所有的行指针，逐行进行比较，直到找到符合条件的行。

ARCHIVE存储引擎

ARCHIVE存储引擎非常适合存储大量独立的、作为历史记录的数据。ARCHIVE提供了压缩功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差。

MyISAM和InnoDB的区别？

1. 存储结构的区别。每个MyISAM在磁盘上存储成三个文件。文件的名字以表的名字开始，扩展名指出文件类型。 .frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

2. 存储空间的区别。MyISAM支持支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后，不会再进行修改操作，可以使用压缩表，极大的减少磁盘的空间占用。InnoDB需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

3. 可移植性、备份及恢复。MyISAM数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。对于InnoDB，可行的方案是拷贝数据文件、备份 binlog，或者用mysqldump，在数据量达到几十G的时候就相对麻烦了。

4. 是否支持行级锁。MyISAM 只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。而InnoDB 支持行级锁和表级锁，默认为行级锁。行锁大幅度提高了多用户并发操作的性能。

5. 是否支持事务和崩溃后的安全恢复。 MyISAM 不提供事务支持。而InnoDB 提供事务支持，具有事务、回滚和崩溃修复能力。

6. 是否支持外键。MyISAM不支持，而InnoDB支持。

7. 是否支持MVCC。MyISAM不支持，InnoDB支持。应对高并发事务，MVCC比单纯的加锁更高效。

8. 是否支持聚集索引。MyISAM不支持聚集索引，InnoDB支持聚集索引。

9. 全文索引。MyISAM支持 FULLTEXT类型的全文索引。InnoDB不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

10. 表主键。MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址。对于InnoDB，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)。

11. 表的行数。MyISAM保存有表的总行数，如果select count(*) from table;会直接取出该值。InnoDB没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了where条件后，MyISAM和InnoDB处理的方式都一样。

mysql语句

SQL中select、from、join、where、group by、having、order by、limit 的执行顺序是什么?

首先SQL语句的基本语法如下：

1. select 查询字段1，查询字段2，聚合函数(如count max)，distinct

2. from 表名

3. join on 表名

4. where 条件

5. group by 分组排列

6. having 条件

7. order by 排序（升序降序）

8. limit 结果限定

按照以上书写顺序，完整的执行顺序应该是这样：

1. from子句：识别查询表的数据；

2. join on/union：用于连接多表数据；

3. where子句：基于指定的条件对记录进行筛选；

4. group by 子句将数据划分成多个组别，如按性别男、女分组；

5. 有聚合函数时，要使用聚集函数进行数据计算；

6. Having子句：筛选满足第二条件的数据；

7. 执行select语句进行字段筛选

8. distinct筛选重复数据；

9. 对数据进行排序；

10. 执行limit进行结果限定。

distinct 和 group by的区别是什么？

1. 功能不同：DISTINCT 用于去除重复行，而 GROUP BY 用于对结果集进行分组，通常与聚合函数一起使用。

2. 返回结果不同：DISTINCT 返回去重后的结果集，查询结果集中只能包含去重的列信息，有其他列信息会报错；GROUP BY 返回按指定列分组后的结果集，可以展示多列信息，并可以包含聚合函数的计算结果。

3. 应用场景不同：DISTINCT 更适合单纯的去重需求，GROUP BY 更适合分组统计需求。

4. 性能略有不同：如果去重的字段有索引，那么 GROUP BY 和 DISTINCT 都可以使用索引，此情况它们的性能是相同的；而当去重的字段没有索引时，DISTINCT 的性能就会高于 GROUP BY，因为在 MySQL 8.0 之前，GROUP BY 有一个隐藏的功能会进行默认的排序，这样就会触发 filesort 从而导致查询性能降低。

exist和in的区别？

exists用于对外表记录做筛选。exists会遍历外表，将外查询表的每一行，代入内查询进行判断。当exists里的条件语句能够返回记录行时，条件就为真，返回外表当前记录。反之如果exists里的条件语句不能返回记录行，条件为假，则外表当前记录被丢弃。

in是先把后边的语句查出来放到临时表中，然后遍历临时表，将临时表的每一行，代入外查询去查找。

• EXISTS 一旦找到匹配行就会立即返回，通常在子查询返回较大数据集时性能更好。

• IN 通常会评估整个子查询并构建一个值列表，然后再进行匹配，在处理较大数据集时可能性能较差。

truncate、delete与drop区别？

• TRUNCATE：快速清空表，但保留结构。适用于需要清空但不删除表的场景。

• DELETE：逐行删除数据，可以选择性删除某些记录。适用于只想删除部分数据的场合，且支持事务。

• DROP：删除整个表定义及其所有数据。适用于不再需要表结构或其数据的情况。

相同点：

1. truncate和不带where子句的delete、以及drop都会删除表内的数据。

2. drop、truncate都是DDL语句（数据定义语言），执行后会自动提交。

不同点：

1. truncate 和 delete 只删除数据不删除表的结构；drop 语句将删除表的结构被依赖的约束、触发器、索引；

2. 一般来说，执行速度: drop > truncate > delete。

having和where区别？

• 二者作用的对象不同，where子句作用于表和视图，having作用于组。

• where在数据分组前进行过滤，having在数据分组后进行过滤。

count(*) 和 count(1)哪个快?

按照性能排序是：count(*) = count(1) > count(主键字段) > count(字段)

1. count(主键字段)的执行过程：

比如说，id是主键字段。

• 如果表里只有主键索引，那么，InnoDB 循环遍历聚簇索引，将读取到的记录返回给 server 层，然后读取记录中的 id 值，并根据 id 值判断是否为 NULL，如果不为 NULL，就将 count 变量加 1。

• 如果表里有二级索引时，InnoDB 循环遍历的对象就不是聚簇索引，而是二级索引。因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间，所以二级索引树比聚簇索引树小，这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小，因此「优化器」优先选择的是二级索引。

1. count(1) 的执行过程：

如果表里只有主键索引，没有二级索引时。那么，InnoDB 循环遍历聚簇索引（主键索引），将读取到的记录返回给 server 层，但是不会读取记录中的任何字段的值，因为 count 函数的参数是 1，不是字段，所以不需要读取记录中的字段值。参数 1 很明显并不是 NULL，因此 server 层每从 InnoDB 读取到一条记录，就将 count 变量加 1。

显然，count(1) 相比 count(主键字段) 少一个步骤，就是不需要读取记录中的字段值，所以通常会说 count(1) 执行效率会比 count(主键字段) 高一点。

但是，如果表里有二级索引时，InnoDB 循环遍历的对象就二级索引了。

1. count(*) 的执行过程(mysql官方文档推荐)：

count() 其实等于 count(0)，也就是说，当你使用 count() 时，MySQL 会将 * 参数转化为参数 0 来处理。

所以，count(*) 执行过程跟 count(1) 执行过程基本一样的，性能没有什么差异。

1. count(字段) 的执行过程：采用全表扫描的方式来统计

MySQL多表查询时有哪些连接方式

当进行多表查询时，在 MySQL 中常用的连接方式有以下几种：

• 内连接（INNER JOIN）：返回同时满足连接条件的行。它通过比较连接列的值，将两个或多个表中匹配的行组合在一起。

• 左外连接（LEFT JOIN）：返回左表中的所有行，以及与左表匹配的右表的行。如果右表中没有匹配的行，对应的列将填充为 NULL。

• 右外连接（RIGHT JOIN）：返回右表中的所有行，以及与右表匹配的左表的行。如果左表中没有匹配的行，对应的列将填充为 NULL。

• 自连接（Self JOIN）：将单个表视为两个独立的表，使用别名来引用同一个表。这种连接适用于在同一个表中根据某些条件关联不同的行。

• 交叉连接（CROSS JOIN）：返回两个表的笛卡尔积，即所有可能的组合。它将第一个表的每一行与第二个表的每一行进行组合。

还有一个是全外连接（FULL JOIN）：返回左右两个表中的所有行。如果某个表中没有匹配的行，对应的列将填充为 NULL。需要注意 MySQL 不支持 FULL JOIN 可以使用UNION ALL 模拟。

为什么大厂不建议使用多表join

最主要的原因就是join的效率比较低：MySQL是使用了嵌套循环（Nested-Loop Join）的方式来实现关联查询的，就是要通过两层循环，用第一张表做外循环，第二张表做内循环，外循环的每一条记录跟内循环中的记录作比较，符合条件的就输出。

1. 性能问题：

• 多表 JOIN 会增加查询的复杂性，可能导致性能下降，特别是在数据量大时。

• 数据库需要在执行查询时处理更多的行和列，这可能导致更高的 I/O 操作和内存使用。

1. 可读性和维护性：

• 复杂的 JOIN 查询会使 SQL 语句变得难以理解，导致维护成本增加。

• 当查询需要频繁修改时，复杂的 JOIN 会让代码更容易出错。

1. 索引利用率：

• 多表 JOIN 可能会导致数据库无法有效利用索引，影响查询的优化。

• 如果 JOIN 的字段没有适当的索引，查询性能会显著下降。

1. 锁竞争：

• 多表 JOIN 可能导致更长时间的行锁或表锁，从而增加锁竞争的可能性，影响并发性能。

1. 数据完整性：

• 复杂的 JOIN 查询可能掩盖数据问题或不一致性，使得调试较为困难。

• 难以确保在 JOIN 查询中返回的数据符合业务逻辑和数据完整性要求。

那应该怎么写？

• 分解查询：在内存中自己做关联，即先从数据库中把数据查出来之后，再次查询，然后再进行数据封装。

• 考虑数据冗余：在某些情况下，可以考虑数据冗余来减少 JOIN 的需要。

• 数据冗余，宽表：就是基于一定的join关系，把数据库中多张表的数据打平做一张大宽表，可以同步到ES或者干脆直接在数据库中直接查都可以

UNION 与UNION ALL 的区别？

UNION和UNION ALL是在SQL中用于合并查询结果集的操作符，它们之间存在以下区别：

• UNION：UNION用于合并两个或多个查询结果集，并去除重复的行。它将多个查询的结果合并为一个结果集，并自动去除重复的行。在执行UNION操作时，数据库会进行额外的去重操作，这可能会带来一定的性能开销。

• UNION ALL：UNION ALL同样用于合并查询结果集，但不去除重复的行。它将多个查询的结果简单地合并在一起，包括重复的行。相比于UNION，UNION ALL不进行去重操作，因此执行效率更高。

总结来说：在使用时，可以根据具体的需求来选择合适的操作符。如果需要去除重复的行，可以使用UNION；如果不需要去重，或者对性能要求较高，可以使用UNION ALL。需要注意的是，使用UNION或UNION ALL时，要求被合并的查询结果的列数和列类型保持一致。

where 1 = 1的作用？会影响性能吗？

WHERE 1=1 表达式的值始终为 TRUE，因此它不会对查询结果产生任何实际的限制或筛选作用。它的主要作用是为条件语句提供一个基础条件，方便在构建动态SQL时，后续的查询条件可以更加灵活地拼接。

对于性能而言，WHERE 1=1 表达式并不会造成明显的性能损失。数据库优化器会意识到 1=1 永远为 TRUE，因此它不会对查询的执行计划产生任何实际影响。WHERE 1=1 在执行时不会进行额外的计算，因为它本质上是一个常量布尔值。

• 查询优化：MySQL数据库会在查询时自动优化查询条件，对于 WHERE 1=1 这样的条件，它不会引入额外的计算负担。优化器通常会跳过 1=1，并且直接处理剩下的查询条件。

• 执行计划：在 MySQL 的查询执行计划中，WHERE 1=1 通常不会出现在最终的执行过程中。优化器会识别到它的无用性，并忽略它对查询结果的影响。

• 影响微乎其微：如果查询中包含大量复杂的条件，WHERE 1=1 的影响几乎可以忽略不计。它只是一个常量，任何现代数据库都能轻松处理这样简单的条件

LIMIT 100000000,10和LIMIT 10 的执行速度是否相同?

速度差很多，limit 10 快很多!

原因如下:

• LIMIT 100000000,10 需要先处理(通常是读取并跳过)前 100000000 条记录，然后再获取到需要的 10条记录，开销成本很大，因为需要扫描 100000000 数据才能得到后面的10条教据，会导致大量的磁盘 I/O

• LIMIT 10 从结果集第一个记录开始扫描直接返回前 10 条记录。

通常面对 LIMIT 100000000,10 这种大分页的情况，可以先使用条件过滤掉，比如使用主键 ID 来进行范围过滤，然后再 limit 10。

MySQL 中的数据排序是怎么实现的?

排序过程中，如果排序字段命中索引，则利用索引排序。反之，使用文件排序。

使用索引排序：当ORDER BY子句中的列恰好有对应的索引时，MySQL可以直接利用索引来完成排序操作，这是最高效的排序方式。索引本身是有序的，所以MySQL只需要按照索引的顺序扫描即可得到排序后的结果。

文件排序(filesort)：当无法使用索引进行排序时，MySQL会使用文件排序。

• 如果数据量少，则在内存中排序。具体是使用单路或双路排序

• 如果数据量较大，超过了系统变量 sort_bufer_ size 的大小，还会使用临时文件来协助排序。一般使用归并排序

具体的排序过程如下：

1. 首先，MySQL会检查ORDER BY子句中的列是否有可用的索引。如果有可用的索引，MySQL会直接使用索引进行排序。

2. 如果没有可用的索引，MySQL会执行以下步骤:

3. 从表中读取满足条件的所有行

4. 对于每一行，只保存需要排序的列和可以唯一标识行的列(如主键)使用快速排序算法在内存中对这些数据进行排序

5. 如果数据量太大，会使用临时文件来辅助排序

6. 根据排序结果回表查询所需的列

需要注意的是，ORDER BY的使用可能会对查询性能产生显著影响。我们可以通过以下方式优化排序操作:

• 在经常需要排序的列上创建适当的索引。

• 尽量避免对大数据集进行排序。

• 如果可能，尽量使用覆盖索引，即索引包含了查询所需的所有列，这样可以避免回表操作。

• 适当调整系统变量，如增加 sort_bufer_ size 的大小。

• 考虑使用限制结果集大小的 LMIT 子句，特别是在只需要前几条记录的情况下。

总的来说，MVSQL的排序实现既考虑了效率(通过利用索引)，又保证了在没有合适索引的情况下也能完成排序(通过文件排序)。理解这些原理对于优化数据库查询性能至关重要，

什么是单路排序？双路排序？

双路排序

有一个叫 max length for_sort cata参数，默认是4096字节，如果 select列的数据长度超过它,则 MySQL采用row id排序，即把 row id(有主键就是主键)+排序字段放置到 sort bufer 中排序

比如现在要执行: select a,b,c from t1 where a=‘‘order by b;

假设select 列的数据长度超过了 max_length_for_sort_data，为了节省排序占用的空间，此时 sort bufer 只会有放置 id 和b 来排序。 排序后，再通过id 回表查询得到 a、b、c，最终将最后的结果集返回给客户端。所以排序需要多个回表的过程，等于需要两次查询，也叫双路排序(Two-Pass Sort)。

单路排序

假设 select 列的数据没有超过 max length for_sortdata，则可以进行单路排序(Singile-Pass Sort)，就是将select 的字段都放置到 sort buffer 中 排序后直接得到结果集，返回给客户端即可，相比双路排序它减少了回表的动作，因此效率更高

开始 MySQL 只有双路排序，后续优化推出了单路排序。

磁盘文件临时排序

如果查询的数据超过 sort_bufer，说明内存放不下了，因此需要利用磁盘文件进行外部排序，一般会使用归并排序，简单理解就是将数据分为很多份文件，单独对文件排序，之后再合并成一个有序的大文件。利用磁盘排序效率会更低，针对一些情况可以调大sort buffer size，避免磁盘临时文件排序,

索引

详细内容请查看：Mysql索引分类

什么是索引？

索引是存储引擎用于提高数据库表的访问速度的一种数据结构。它可以比作一本字典的目录，可以帮你快速找到对应的记录。

索引一般存储在磁盘的文件中，它是占用物理空间的。

索引的优缺点？

优点：

• 加快数据查找的速度

• 为用来排序或者是分组的字段添加索引，可以加快分组和排序的速度

• 加快表与表之间的连接

缺点：

• 建立索引需要占用物理空间

• 会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长

索引的作用？

数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，查询速度大大提升。

什么情况下需要建索引？

1. 经常用于查询的字段；即对经常出现在 WHERE 子句中的列创建索引。

2. 经常用于连接的字段建立索引，可以加快连接的速度

3. 经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度；例如对 ORDER BY、GROUP BY 或者 DISTINCT 操作中出现的列建立索引。

什么情况下不推荐建索引？

1. where条件中用不到的字段不适合建立索引

2. 表记录较少。比如只有几百条数据，没必要加索引。

3. 需要经常增删改。对于频繁进行插入、更新和删除操作的表，索引会导致额外的维护开销，因为每次数据变更时都需要更新索引，这会影响性能。需要评估是否适合加索引，

4. 参与列计算的列不适合建索引

5. 区分度不高的字段不适合建立索引，如性别，只有男/女/未知三个值。加了索引，查询效率也不会提高。

索引的数据结构

索引的数据结构主要有B+树和哈希表，对应的索引分别为B+树索引和哈希索引。InnoDB引擎的索引类型有B+树索引和哈希索引，默认的索引类型为B+树索引。

B+树索引

B+ 树是基于B 树和叶子节点顺序访问指针进行实现，它具有B树的平衡性，并且通过顺序访问指针来提高区间查询的性能。

在 B+ 树中，节点中的 key 从左到右递增排列，如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1，则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1。 进行查找操作时，首先在根节点进行二分查找，找到key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出key所对应的数据项。

MySQL 数据库使用最多的索引类型是BTREE索引，底层基于B+树数据结构来实现。

哈希索引

哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是O(1)，一般多用于精确查找。

索引有什么分类？

从数据结构角度来看，MySQL索引可以分为以下几类:

• B+树索引：通过树形结构存储数据，适用于范围查询(如 BETWEEN)和精确査询(如 =)，支持有序数据的快速查找、排序和聚合操作。是 MySQL 默认的索引类型，常用于 InnoDB 和 MyISAM 引擎

• 哈希索引：基于哈希表的结构，适用于等值查询(如 = )，查询速度非常快，但不支持范围査询(如>、<=)。哈希索引不存储数据的顺序，常用于 Memory 引擎。

• 倒排索引(Full-Text)：用于全文搜索，将全文分词，通过存储词与文档的映射，支持模糊匹都和关键字搜索，特别话合用于大文本字段，如TXT类型的列，用于查找包合特定词语的记录。

• R- 树索引：专为多维空间数据(如地理坐标)设计，适用于空间查询(例，计算地理位置的最近距离、区域查询等)。常用于存储和查询地理信息系统(G1S)中的空间数据。

从常见的基于InnoDB B+树索引角度来看，可以分为：（后文中的聚集索引也就是聚簇索引）

• 聚簇索引：InnoDB 中主键索引就是聚族索引。它基于主键排序存储。之所以叫聚簇索引是因为素引的叶子节点存储完整数据行数据,

• 非聚谈索引：指的是 InnoDB 中非主键索引的索引，之所以称之为非聚族是因为这个索引叶子节点仅保存索引字段和主键的值，如果要查询完整的数据行中的数据，需要再从聚族索引助主键索引中通过主键查询，一个表可以有多个非聚族索引。

从索引性质的角度来看，可以分为:

• 普通索引(二级索引、辅助索引)：普通索引是最基本的索引，它没有任何限制，值可以为空。

• 主键索引：名为primary的唯一非空索引，不允许有空值。

• 联合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时需遵循最左前缀原则。

• 唯一索引：索引列中的值必须是唯一的，但是允许为空值。唯一索引和主键索引的区别是：唯一索引字段可以为null且可以存在多个null值，而主键索引字段不可以为null。唯一索引的用途：唯一标识数据库表中的每条记录，主要是用来防止数据重复插入。

• 全文索引：只能在CHAR、VARCHAR和TEXT类型字段上使用全文索引。

• 前缀索引：前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。

• 空间索引：用于空间数据(如地图上的经纬度坐标等)查询。通常使用 R-树结构，适合多维数据的查询，如区域查询和最近距离查询，主要用于 MySAM 和InnoDB 存储引警中的地理信息数据。

为什么不用hash表

这与hash表的原理有关。

使用hash表的目的是为了尽可能的散列，因此在使用hash表的时候要选择hash算法，避免hash碰撞和hash冲突。

这就导致了hash表存储的数据是无序的，当需要进行范围查询的时候，只能挨个进行遍历对比，效率极低

所以：

1. 不用hash表的原因在于不适合范围查询

2. 容易导致全表扫描，因为可能存在不同的key经过hash运算后值相同

3. 索引列上的值相同的话，易造成hash冲突，效率低下

为什么不用二叉树

如果将一个乱序的数据放入二叉树中，效率会高，但是如果数据是有顺序的，比如1、2、3、4、5，则二叉树将会编程一个链表的样式，失去了二叉树的优势

总结：有可能退化成链表，读取数据需要频繁IO

为什么不用红黑树（或二叉平衡树）

红黑树也是一种二叉平衡树，红黑树可以有效解决掉顺序数据一次放入二叉树而导致的形成链表的结果，但是红黑树一个节点只能存储一个数据，就导致如果是大量的数据，红黑树的高度就不可控，如果一个红黑树是20的高度，要查询的数据在叶子结点，则表示需要需磁盘20次IO，效率还是不高

总结：相比于二叉树能避免退化成链表，但IO次数还是会很高

为什么不用B树而用B+树

B树比红黑树的优势是，B树是一个节点上存储多个数据，比如磁盘的一页数据，这样的横向扩展，相同的数据量就可以比红黑树减少更多的高度，从而减少了磁盘的IO次数

B树：data为数据的磁盘地址，还可能是整条列的数据

1. 数据是分散在每个结点上的，所有结点元素不重复，

2. 结点元素从左到右递增

3. 叶子结点具有相同的深度，叶子结点的指针为空

B+树：data为数据的磁盘地址，还可能是整条列的数据

1. 叶子结点包含了所有的索引字段，以及数据的磁盘地址，而非叶子结点不再存储data数据，作用只是便于查找的冗余索引

2. 非叶子结点是从子结点选取一页的开头来作为自己的值，指针为子结点那页的地址

3. 每一个结点里的值都是排好序的

4. 叶子结点之间是用「双向链表」进行连接，这样方便了范围查找，比如where col > 10，这是mysql对B+的变种，也是对比B树的一个优势

5. 由于data可能会很大，非叶子结点在不存储data后，非叶子可以存储的元素则会变多，还可以降低树的高度，提高了查询的效率，这是与B树对比，B+树的一个优势

总结：B+树相比于B树，非叶子节点不再存储data数据，非叶子可以存储的元素则会变多，一个非叶子节点就可以存储更多的索引数据，更进一步降低树的高度，提高了查询的效率。相比于hash表，B+树利用叶子节点之间的指针可以进行范围查询

为什么用B+树作为索引结构？

• 高效的查找性能：B+树是一种自平衡树，每个叶子节点到根节点的路径长度相同，B+树在插入和删除节点时会进行分裂和合并操作，以保持树的平衡，但它又会有一定的冗余节点，使得删除的时候树结构的变化小，更高效。查找、插入、删除等操作的时间复杂度为 O(log n)，能够保证在大数据量情况下也能有较快的响应时间。

• 树的高度增长不会过快，使得查询磁盘的 I/0 次数减少：B+树不像红黑树，数据越多树的高度增长就越快，它是多叉树，非叶子节点仅保存主键或索引值和页面指针，使得每一页能容纳更多的记录，因此内存中就能存放更多案引，容易命中缓存，使得查询磁盘的 IO 次数减少。

• 范围查询能力强：B+ 树特别适合范围查询。因为叶子节点通过链表链接，从根节点定位到叶子节点查找到范围的起点之后，只需要顺序扫描链表即可遍历后续的数据，非常高效。

什么是最左匹配原则？

如果 SQL 语句中用到了组合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个组合索引去进行匹配。当遇到范围查询(>、<、between、like)就会停止匹配，后面的字段不会用到索引。

对(a,b,c)建立索引，查询条件使用 a/ab/abc 会走索引，使用 bc 不会走索引。

对(a,b,c,d)建立索引，查询条件为a = 1 and b = 2 and c > 3 and d = 4，那么a、b和c三个字段能用到索引，而d无法使用索引。因为遇到了范围查询。

如下图，对(a, b) 建立索引，a 在索引树中是全局有序的，而 b 是全局无序，局部有序（当a相等时，会根据b进行排序）。直接执行b = 2这种查询条件无法使用索引。 最左前缀 当a的值确定的时候，b是有序的。例如a = 1时，b值为1，2是有序的状态。当a = 2时候，b的值为1，4也是有序状态。 当执行a = 1 and b = 2时a和b字段能用到索引。而执行a > 1 and b = 2时，a字段能用到索引，b字段用不到索引。因为a的值此时是一个范围，不是固定的，在这个范围内b值不是有序的，因此b字段无法使用索引。

什么是聚集索引？什么是聚集索引？

InnoDB使用表的主键构造主键索引树，同时叶子节点中存放的即为整张表的记录数据。聚集索引叶子节点的存储是逻辑上连续的，使用双向链表连接，叶子节点按照主键的顺序排序，因此对于主键的排序查找和范围查找速度比较快。

聚集索引的叶子节点就是整张表的行记录。InnoDB 主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多。

对于InnoDB来说，聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引。如果没有主键也没有合适的唯一索引，那么InnoDB内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键长度为6个字节，它的值会随着数据的插入自增。

非聚集索引：索引叶子节点存储的是数据行的主键和对应的索引列，需通过主键才能访问完整的数据行。一个表可以有多个非聚集索引(也称之为非主键索引、辅助索引、二级索引)，适用于快速查找特定列的数据。

什么是回表？

“回表"是指在使用二级索引(非聚簇索引)作为条件进行查询时，由于一级索引中只存储了索引字段的值和对应的主键值，无法得到真正想要的数据。

如果要查询数据行中的其它数据，需要根据主键去聚簇索引查找实际的数据行，这个过程被称为回表

什么是覆盖索引？

select的数据列只用从索引中就能够取得，不需要回表进行二次查询，也就是说查询列要被所使用的索引覆盖。对于innodb表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以MySQL使用b+树索引做覆盖索引。

对于使用了覆盖索引的查询，在查询前面使用explain，输出的extra列会显示为using index。

比如user_like 用户点赞表，组合索引为(user_id, blog_id)，user_id和blog_id都不为null。

explain结果的Extra列为Using index，查询的列被索引覆盖，并且where筛选条件符合最左前缀原则，通过索引查找就能直接找到符合条件的数据，不需要回表查询数据。

explain结果的Extra列为Using where; Using index， 查询的列被索引覆盖，where筛选条件不符合最左前缀原则，无法通过索引查找找到符合条件的数据，但可以通过索引扫描找到符合条件的数据，也不需要回表查询数据。

MySQL 索引的最左前缀匹配原则是什么?

MYSOL索引(的最左前缀匹配原则指的是在使用联合索引时，查询条件必须从索引的最左侧开始匹配。如果一个联合索引包含多个列，查询条件必须包含第一个列的条件，然后是第二个列，以此类推。

底层原理：因为联合索引在B+树中的排列方式道循“从左到右”的顺序，例如联合索引(a，b，c) 会按照(a，b，c)的顺字在B+ 树中进行排序。MYSQL在查找时会优先使用 a 作为匹配依据，然后依次使用 b 和 c。因此，组合索引能够从左到右依次高效匹配，跳过最左侧字段会导致无法利用该索引。

以下查询条件符合最左匹配原则

以下条件不符合最左匹配原则

索引的设计原则？

选择合适的列：

• 经常用于查询的字段；即对经常出现在 WHERE 子句中的列创建索引。

• 经常用于连接的字段建立索引，可以加快连接的速度

• 经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度；例如对 ORDER BY、GROUP BY 或者 DISTINCT 操作中出现的列建立索引。

优化多列索引：

• 复合索引：对多个列组合查询的情况，考虑使用复合索引而非单个列索引，以减少表扫描次数。

• 利用最左前缀原则：确保复合索引的列顺序遵循最左前缀原则，即将使用最频繁的列放在最前面。

控制索引的数量和类型 ：

• 避免过多索引： 索引有利于查询，但每个新增索引会影响插入、更新、删除的性能。应在查询效率与维护成本之间取得平衡。

• 适用索引类型： 选择合适类型的索引，例如 B-tree 索引用于大多数情况，Full-text 索引用于全文搜索，Hash 索引用于等值查询（如内存表）。

考虑数据分布和选择性 ：

• 高选择性的列： 高基数、高选择性（具有许多不同值）的列索引往往能提高查询效率。

• 均匀分布的数据： 索引在数据分布均匀时效果最佳，避免对只有少数不同值的列（低基数）设置索引。

避免冗余和重复索引

• 合并索引：考虑合并类似用途的索引，减少冗余。

• 定期清理：定期审查和清理不再使用或效果不佳的索引。

其他注意点：

• 覆蓋索引： 当索引包含查询所需的所有列时，称为覆盖索引，能有效减少读取数据页的次数。尽量使查询使用覆盖索引。

• 索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差。

• 避免给"大字段"建立索引。尽量使用数据量小的字段作为索引。因为MySQL在维护索引的时候是会将字段值一起维护的，那这样必然会导致索引占用更多的空间，另外在排序的时候需要花费更多的时间去对比。

• 尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，查询速度更快。

• 频繁增删改的字段不要建立索引。假设某个字段频繁修改，那就意味着需要频繁的重建索引，这必然影响MySQL的性能

使用索引一定有效吗?如何排查索引效果?

索引不一定有效。

例如查询条件中不包含索引列、低基数列索引效果不佳，或查询条件复杂且不匹配索引的顺序。

对于一些小表，MySQL可能选择全表扫描而非使用索引，因为全表扫描的开销可能更小。

最终是否用上索引是根据 MySQL 成本计算决定的，评估 CPU 和 IO 成本最终选择用辅助索引还是全表扫猫，有时候确实是全表扫描成本低所以没用上索引，但有时候由于一些统计数据的不准确，导致成本计算误判，而没用上索引。

排查索引效果的方法：使用 EXPLAIN 命令，通过在查询前加上 EXPLAIN，可以查看 MSQL选择的执行计划，了解是否使用了索引、使用了哪个索引、估算的行数等信息。

主要观察 EXPLAIN 结果以下几点

• type(访问类型)：这个属性显示了查询使用的访问方法，例如ALL、index、range等，当查询使用案引时，这个属性通常会显示为 index或range，表示查询使用了案引访问。如果这个值是ALL，则表示查询执行了全表扫描

• key(使用的索引)：这个属性显示了查询使用的索引，如果查询使用了索引，则会显示索引的名称。如果这个值是ALL，则表示查询没有使用索引。

• rows(扫描的行数)：这个属性显示了查询扫描的行数，需要评估下扫描量

区分度不高的字段建索引一定没用吗

区分度不高的字段建索引的问题:

• 低效的过滤效果： 如果字段的选择性很低（例如只有几个不同的值），那么通过索引过滤数据的效果可能不明显，因为每个值对应太多的行。

• 索引维护开销： 在更新或插入操作时，所有的索引需要同步更新，低选择性字段的索引可能带来不必要的开销，而收益有限。

• 增大查询成本： 尽管索引帮助快速定位数据位置，但由于返回的数据集过大，可能会导致后续操作（如排序和过滤）带来额外的系统开销。

那何时低选择性索引可能有用呢 ？

• 固定值查询： 即使选择性低，如果应用程序中有特定固定的值或者特定过滤条件经常被使用，并要求快速响应（如状态标志字段），索引仍可能提高查询速度。

• 结合其他高选择性条件： 当低选择性的字段被结合其他有高选择性的字段一起使用时，可能仍然增益明显。

• 作为覆盖索引的一部分： 如果一个多列索引（复合索引）包含一个低选择性字段，同时能使一个查询成为覆盖索引，这依然可以提升性能。

• 大数据集中的特定场合： 在非常大的数据集中，占用大量数据的字段索引可能仍有助于减少所需的扫描行数。

• 统计分析和报告： 如果索引用于统计或聚合操作中，通过索引可以更高效地进行分组操作。

以上属于特定情况，区分度不高的字段建立索引还是要依据具体应用场景和查询场景进行权衡和测试。

索引什么时候会失效？

导致索引失效的情况：

• 对于组合索引，不是使用组合索引最左边的字段，则不会使用索引

• 以%开头的like查询如%abc，无法使用索引；非%开头的like查询如abc%，相当于范围查询，会使用索引

• 查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效

• 判断索引列是否不等于某个值时

• 对索引列进行运算

• 查询条件使用or连接，也会导致索引失效

什么是前缀索引？

有时需要在很长的字符列上创建索引，这会造成索引特别大且慢。使用前缀索引可以避免这个问题。

前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。

创建前缀索引的关键在于选择足够长的前缀以保证较高的索引选择性。索引选择性越高查询效率就越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的数据行。

什么是索引下推？

索引下推(Index Condition Pushdown,ICP)是一种减少回表查询,提高查询效率的技术。它允许 MVSQL在使用索引查找数据时，将部分査询条件下推到存储引层过滤，从而减少需要从表中读取的数据行，减少了IO(本该由 Server 层做操作，交由存储引擎层因此叫做“下推”)。

注意：索引下推是应用在联合索引上的。

在没有索引下推的情况下，当查询使用复合索引时，MySQL可能需要访问主表来评估不能完全通过索引条件确定的行。例如，如果只有索引的部分条件在索引中能被使用，而其他条件需要读取实际行再进行筛选，传统方式可能会导致更多的全行读取。

在联合索引遍历过程中，对联合索引中包含的字段先做判断，在存储引擎层进行数据过滤，而不是在服务层过滤，直接过滤掉不满足条件的记录，利用索引现有的数据减少回表次数，就叫索引下推

假设当前 people 表有索引 INDEX(zipcode，lastname，firstname)，当执行以下查询时:

如果没有索引下推，当前的联合索引只能用上 zipcode=‘95854’这个条件，引擎层用不上lastname 这个条件过滤，只能得到所有符合 zipcode=‘95054’记录，传递给 server 层过滤。

有了索引下推之后，引擎层在得到符合 zipcode=‘95854’的数据后，可以直接通过 lastname 条件过滤数据，不符合条件的不会返回给 server 层。

索引数量是否越多越好?为什么?

索引并不是越多越好。因为索引不论从时间还是空间上都是有一定成本的

从时间上：每次对表中的数据进行增删改的时候，索引也必须被更新,，这会增加写入操作的开销，例如出除了一个 name为 的记录，不仅主键索引上需要修改，如果name字段有索引，那么 name 索引都需要修改，所以索引越多需要修改的地方也就越多，时间开销就大了，并目 B+ 树可能会有页分裂、合并等操作，时间开销就会更大。还有一点需要注意，mysql 有个查询优化器，它需要分析当前的查询，选择最优的计划、这过程就需要考虑出选择哪个索引的查询或本低，如果索引过多，那么会导致优化器耗费更多的时间在选择上，甚至可能因为索引选的不准确而选择了次优的索引。

从空间上：每建立一个二级索引，都需要新建一个 B+ 树，默认每个数据页都是16KB，如果教据量很大，索引又很多，占用的空间可不小

Innodb加索引的时候会锁表吗

在 InnoDB 中，操作索引时是否锁表取决于具体的操作类型。有些情况可能会导致表锁，而有些情况则不会。

• 在线添加索引（Online DDL）：

  • 从 MySQL 5.6 开始，InnoDB 支持在线添加索引。这意味着在添加索引期间，表可以同时进行读取和写入操作，从而不会锁住整张表。

  • 在线 DDL 的特性允许通过特定配置，让表在加索引的同时保持可用状态（如 LOCK=NONE 选项）。

  • 默认情况下，即使是在线添加索引，也可能会对表的某些部分进行短暂的锁定，但不会长期锁表。需要注意的是，在不支持在线 DDL 的旧版本中，添加索引通常是涉及锁表的操作，尤其是在大量数据情况下。

• 删除索引操作通常不需要长时间锁表，但在删除过程中可能进行短暂的锁定以保证元数据更新的一致性。

• 对于某些索引修改，可能会引发锁表现象，尤其是在不支持在线 DDL 的情况下或在某些特定场景下，需要评估具体MySQL版本和参数配置。

字符串怎么做索引?

在MySQL中，可以通过B+树索引结构对字符串类型的列进行排序。具体来说，当使用B+树索引进行排序时，MySQL会根据字符串的字典序（Lexicographic Order）进行排序。

字典序是指将字符串中的每个字符依次比较，直到找到不同的字符为止。如果两个字符串在相同的位置上具有不同的字符，则以这两个字符的ASCII码值比较大小，并按照升序或降序排列。例如，字符串"abc"和"def"比较大小时，先比较’a’和’d’的ASCII码，因为’d’的ASCII码大于’a’，所以"def"大于"abc”。

需要注意的是，如果对长字符串进行排序，可能会影响索引查询的性能，因此可以考虑使用前缀索引或全文索引来优化。同时，在实际开发中，还需要注意选择适当的字符集和排序规则，以确保排序结果正确和稳定。

索引字段的长度有限制吗?

在MySQL中，索引的长度通常是由三个因素决定的：数据类型、字符集和存储引擎。不同的数据类型、字符集和存储引擎所支持的最大索引长度也有所不同。

一般情况下，索引的长度不应该超过存储引擎所支持的最大索引长度。在InnoDB存储引擎中，单个索引所能包含的最大字节数为767个字节（前缀索引除外）。如果索引的长度超过了最大长度，则会导致创建索引失败。因此，在设计表结构时，需要根据索引列的数据类型和字符集等因素，合理设置索引长度，以充分利用索引的优势。

对于字符串类型的索引，还需要注意以下几点：

• 对于UTF-8字符集，每个字符占用1-4个字节，因此索引长度需要根据实际情况进行计算。例如，一个VARCHAR(255)类型的列在utf8mb4字符集下的最大长度为255*4=1020个字节。

• 可以使用前缀索引来减少索引的大小，提高索引查询效率。在创建前缀索引时需要指定前缀长度。例如，可以在创建索引时使用name(10)来指定name列的前10个字符作为索引。

• 在使用全文索引对字符串进行搜索时，MySQL会将文本内容分割成单个词汇后建立倒排索引。在建立索引时需要考虑到中英文分词的问题，以确保全文索引的准确性和查询效率。

综上所述，索引的长度需要根据数据类型、字符集和存储引擎等多个因素进行综合考虑，并合理设置索引长度，以提高索引查询效率和利用率。

Mysql索引/SQL优化

对索引使用左或者左右模糊匹配

在MySQL中，LIKE 模糊查询可能会导致性能问题，特别是当使用通配符 % 开头时，因为这通常会导致全表扫描，也就是 like %xx 或者 like %xx% 这两种方式 (左或者左右模糊匹配的时候) 都会造成索引失效。

• 前缀匹配：一般使用LIKE ‘prefix%‘的形式，这种情况MySQL能够利用索引

• 对于一定需要匹配后缀的情况（即LIKE ‘%suffix’），可以创建一个辅助列存储反转字符串，并基于此列进行前缀匹配。

计算(使用函数)

因为索引保存的是索引字段的原始值，而不是经过计算后的值，自然就没办法走索引了。

函数计算或者表达式计算都没办法走索引

不过，从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。

但是在索引范围之外使用函数是不影响索引的： 即函数不在索引范围（即WHERE或ON条件）使用时，索引仍然有效。例如，在 SELECT 列表、ORDER BY、GROUP BY等地方使用函数通常不影响索引的使用。

类型转换

1. 如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的话，那么这条语句会走全表扫描。

2. 但是如果索引字段是整型类型，查询条件中的输入参数即使字符串，是不会导致索引失效，还是可以走索引扫描。

原因在于MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。也就是说，如果索引字段是整型类型，查询条件中的输入参数是字符串，会自动转换成整型，所以索引不会失效。而索引字段是字符串，而输入的是整型，由于是字符串转数字，而索引不是整型类型，所以索引失效了。

因此在使用sql语句时：数值类型禁止加引号，字符串类型必须加引号

联合索引非最左匹配

联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。

原因是，在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。

比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

• where a=1；

• where a=1 and b=2 and c=3；

• where a=1 and b=2；

因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。

但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

• where b=2；

• where c=3；

• where b=2 and c=3；

对于where a = 1 and c = 0 这个语句，前面的a = 1是会走索引的，后面的c不走索引。

where条件的顺序影响索引使用吗

在MySQL中，WHERE条件的顺序确实可能影响索引的使用，尤其是在使用复合索引（多列索引）的情况下。但是，这种影响主要取决于MySQL查询优化器的工作方式，而不是直接由WHERE子句中条件的书写顺序决定的。

• MySQL的查询优化器通常会尝试重新排列WHERE条件以最优化索引的使用。

• 对于复合索引，索引的使用遵循"最左前缀"原则。

• 虽然WHERE条件的顺序通常不会影响索引的使用，但将索引列的条件放在前面可能会使查询计划更易读和理解。

不应使用 or

在 WHERE 子句中or，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。

in

尽量使用IN代替OR。但是IN包含的值不应过多，应少于1000个。

因为 IN 通常是走索引的，当IN后面的数据在数据表中超过30%的匹配时是全表的扫描，不会走索引

其实就是 Mysql优化器会根据当前表的情况选择最优解。 Mysql优化器认为走全表扫描 比 走索引+回表快 那就不会走索引

范围查询阻断，后续字段不能走索引

索引

SQL语句

所谓的停止四配指的是：a 可以用上联合索引，但是 b和c 却不行。因为 b 需要先经过范国查询，此时经过饰选得到 c 的数据是无序的。比如a为1 和a为2数据中的 b和c是无序的，因此无法利用索引查询。

如果遇到如>=、<=、BETWEEN、前缀like(xx%)的范围查询，则在等值处不会停止匹配。因为这些查询包会一个等值判断，可以直接定位到某个数据，然后往后扫描可。

相关原理可以看这篇文章 唯一索引范围查询

覆盖索引优化

覆盖索引是指 SQL 中 查询的所有字段，在这个二级索引 B+Tree 的叶子节点上都能找得到那些字段，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，就可以避免回表的操作。

asc和desc混用

desc 和asc混用时会导致索引失效

避免更新索引列值

每当索引列的值发生变化时，数据库必须更新相应的索引结构，更新索引列值可能导致这些树结构的重平衡或重新构建，增加了额外的计算和I/O开销。

不等于、不包含不能用到索引的快速搜索

在索引上，避免使用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等

not in一定不走索引吗？

答案是不一定。Mysql优化器会根据当前表的情况选择最优解。

主要在于如果 MySQL 认为 全表扫描 比 走索引+回表效率高， 那么他会选择全表扫描。

重要SQL必须被索引

update、delete的where条件列、order by、group by、distinct字段、多表join字段（on后面的字段）

例如：select id from table_a where name = ’’ order by address ; 此时建立 name + address的联合索引比较好(此处name条件必须是 = ,如果是范围则无效)；如果是order by主键，则只需要在name字段建立索引即可，因为name索引表中是包含主键的，也就是所谓了避免了回表操作。

避免使用子查询

通常情况下，一般建议使用连接查询代替子查询，原因如下： 连接查询（JOIN）子查询在执行连接查询时，数据库会根据查询优化器的策略将多个表的数据进行合并，然后进行过滤和选择。子查询要先执行内部查询，然后再使用其结果进行外部查询。嵌套子查询需要多次扫描数据，并且每次子查询都可能会触发独立的扫描操作，这增加了开销。数据库优化器在处理连接查询时有更多的优化手段，如排序合并连接、哈希连接和嵌套循环连接等。优化器可以根据统计信息和查询结构进行调整，选择最优的执行计划。子查询有时不能充分利用优化器的优化策略，特别是在嵌套子查询的情况下，优化器可能会生成次优的执行计划。由于连接查询一次性扫描多个表并进行合并，所以可以充分利用数据缓存，减少I/O操作。子查询可能会导致多次扫描相同的数据，特别是在嵌套子查询和相关子查询的情况下，子查询每次执行都可能触发新的数据扫描，增加了I/O开销。可以通过JOIN条件有效地过滤数据，减少中间结果的大小。可能会产生较大的中间结果集，需要多次筛选和处理，增加了内存和计算的开销。

order by的坑

已知存在 custom_id 和 order_date 的联合索引。

在对数据进行，custom_id 排序的情况下，再对 order_date 进行排序。

耗时 0.669 秒

当调换排序顺序，就无法走索引了，此时针对custom_id的索引排序就是失效了。

耗时 1.645 秒

即order by也需满足联合索引的最左匹配原则

同时默认情况下，索引是升序的，如果需要降序排序，那么索引也会失效！！！

MySQL查询 limit 1000,10 和limit 10 速度一样快吗？

两种查询方式。对应 limit offset, size 和 limit size 两种方式。

而其实 limit size，相当于 limit 0, size。也就是从0开始取size条数据。

也就是说，两种方式的区别在于offset是否为0。

先来看下limit sql的内部执行逻辑。

MySQL内部分为server层和存储引擎层。一般情况下存储引擎都用innodb。

server层有很多模块，其中需要关注的是执行器是用于跟存储引擎打交道的组件。

执行器可以通过调用存储引擎提供的接口，将一行行数据取出，当这些数据完全符合要求（比如满足其他where条件），则会放到结果集中，最后返回给调用mysql的客户端。

以主键索引的limit执行过程为例：

执行select * from xxx order by id limit 0, 10;，select后面带的是星号，也就是要求获得行数据的所有字段信息。

server层会调用innodb的接口，在innodb里的主键索引中获取到第0到10条完整行数据，依次返回给server层，并放到server层的结果集中，返回给客户端。

把offset搞大点，比如执行的是：select * from xxx order by id limit 500000, 10;

server层会调用innodb的接口，由于这次的offset=500000，会在innodb里的主键索引中获取到第0到（500000 + 10）条完整行数据，返回给server层之后根据offset的值挨个抛弃，最后只留下最后面的size条，也就是10条数据，放到server层的结果集中，返回给客户端。

可以看出，当offset非0时，server层会从引擎层获取到很多无用的数据，而获取的这些无用数据都是要耗时的。

因此，mysql查询中 limit 1000,10 会比 limit 10 更慢。原因是 limit 1000,10 会取出1000+10条数据，并抛弃前1000条，这部分耗时更大。

深分页怎么优化？

还是以上面的SQL为例：select * from xxx order by id limit 500000, 10;

方法一：延迟关联（子查询）

从上面的分析可以看出，当offset非常大时，server层会从引擎层获取到很多无用的数据，而当select后面是*号时，就需要拷贝完整的行信息，拷贝完整数据相比只拷贝行数据里的其中一两个列字段更耗费时间。

因为前面的offset条数据最后都是不要的，没有必要拷贝完整字段，所以可以将sql语句修改成：

先执行子查询 select id from xxx by id limit 500000, 1, 这个操作，其实也是将在innodb中的主键索引中获取到500000+1条数据，然后server层会抛弃前500000条，只保留最后一条数据的id。

但不同的地方在于，在返回server层的过程中，只会拷贝数据行内的id这一列，而不会拷贝数据行的所有列，当数据量较大时，这部分的耗时还是比较明显的。

在拿到了上面的id之后，假设这个id正好等于500000，那sql就变成了

这样innodb再走一次主键索引，通过B+树快速定位到id=500000的行数据，时间复杂度是lg(n)，然后向后取10条数据。

方法二：根据id主键进行排序

记录上次查询的最大ID（或其他唯一标识符），并以此为起点进行下一次查询。这种方法需要有连续的、唯一的列（如自增ID）以用于分页。

通过主键索引，每次定位到start_id的位置，然后往后遍历10个数据，这样不管数据多大，查询性能都较为稳定。

方法三：elasticsearch

可以考虑用搜索引擎来解决这个问题，不过 es也会有深度分页的问题，所以如果对 es不熟，面试就不要这样答了，防止面试官问 es 的深度分页如何解决

大表查询慢怎么优化？

某个表有近千万数据，查询比较慢，如何优化？

当MySQL单表记录数过大时，数据库的性能会明显下降，一些常见的优化措施如下：

• 合理建立索引。在合适的字段上建立索引，例如在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描

• 索引优化，SQL优化。索引要符合最左匹配原则等

• 建立分区。对关键字段建立水平分区，比如时间字段，若查询条件往往通过时间范围来进行查询，能提升不少性能

• 利用缓存。利用Redis等缓存热点数据，提高查询效率

• 限定数据的范围。比如：用户在查询历史信息的时候，可以控制在一个月的时间范围内

• 读写分离。经典的数据库拆分方案，主库负责写，从库负责读

• 通过分库分表的方式进行优化，主要有垂直拆分和水平拆分

• 数据异构到es

• 冷热数据分离。几个月之前不常用的数据放到冷库中，最新的数据比较新的数据放到热库中

• 升级数据库类型，换一种能兼容MySQL的数据库（OceanBase、TiDB等）

什么时候索引失效反而提升效率

• 小表查询： 对于非常小的表，MySQL可能会选择全表扫描（忽略索引），因为全表扫描的开销可能比通过索引逐行查找还要低。在这种情况下，索引失效不会损害性能，反而简化了查询。

• 读取大部分或所有行： 当一个查询返回表中很大比例的行（如 30% 或更多）时，使用索引查找可能会耗时更多，因为数据库必须跳回主数据页以读取完整记录。全表扫描可能更有效，因为它可以逐行顺序读取数据。

• 低选择性索引： 如果索引列的选择性非常低，例如一个布尔型字段，许多行有相同的值，那么依赖索引可能会产生不必要的开销。全表扫描可以避免索引的搜索和回表开销。

• 频繁更新的表： 对于包含大量更新操作的表，索引的维护成本可能相对较高。尤其是在频繁更新索引列时，通过避免使用或减少复杂的索引可以减轻写操作的负担。

• 复杂查询的优化选择： 对于复杂的多表联接查询，优化器有时可以选择执行计划中不使用某个索引（或部分失效）以提高整体联接和计算效率。

• 数据分布与优化器误判： 在某些特定情况下，如果索引导致MySQL错误地估计数据分布或行数，手动禁用索引或提示优化器使用不同策略可能提升性能。

不考虑分库分表，如果单表的数据量很大，如何优化?

优先做索引优化例如用上覆盖索引、复合索引等尽量减少扫描行数和回表次数。

还有表结构优化，比如冗余一些字段来减少不必要的join 查询。

除此之外，还有业务层面的优化，例如列表全部查询变成分页查询，将大查询拆分成小批次查询等。

针对一些业务性质，也可以将冷热数据分离，例如将一些历史数据归档迁移到历史表，减少当前表的大小。这部分做完后，可以考虑架构优化，比如引入 Redis 缓存，减少 DB访问，也可以考虑读写分离，读请求走从库，分担主库压力，也可以采用分区来实现单表数据的拆分，因为物理上会按分区拆成多个文件，从而查询条件带上分区键的时候，无需扫描全量数据。不过要细究的话，这其实也算一种分表

MySQL缓存相关

查询缓存的作用？

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

开启查询缓存后在同样的查询条件以及数据情况下，会直接在缓存中返回结果。这里的查询条件包括查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息。

查询缓存不命中的情况：

1. 任何两个查询在任何字符上的不同都会导致缓存不命中。

2. 如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL 库中的系统表，其查询结果也不会被缓存。

3. 缓存建立之后，MySQL 的查询缓存系统会跟踪查询中涉及的每张表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。

为什么 8.0 版本后移除了？缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。 因此，开启查询缓存要谨慎，尤其对于写密集的应用来说更是如此。

为什么要有 Buffer Pool？

虽然说 MySQL 的数据是存储在磁盘里的，但是也不能每次都从磁盘里面读取数据，这样性能是极差的。

要想提升查询性能，那就加个缓存。所以，当数据从磁盘中取出后，缓存内存中，下次查询同样的数据的时候，直接从内存中读取。

为此，Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

• 当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。

• 当修改数据时，首先是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，最后由后台线程将脏页写入到磁盘。

Buffer Pool详情请查看Buffer Pool的三个链表

一次查询大量数据对Buffer Pool的影响？

Buffer Pool 命中率（Hit Ratio）

• 当执行一个大查询时，MySQL 需要加载大量的数据页到 Buffer Pool 中。如果这些数据页之前没有被缓存，它们将从磁盘读取并替换掉 Buffer Pool 中已有的页面。

• 这种行为可能导致频繁使用的数据页被挤出（evicted），进而降低 Buffer Pool 的命中率。低命中率意味着更多的磁盘 I/O 操作，这会显著减慢查询速度和整体数据库性能。

InnoDB 缓冲池刷新：InnoDB 存储引擎通过后台线程定期刷新脏页（即已经被修改但尚未写入磁盘的数据页）。当有大量的新页被加载到 Buffer Pool 中时，为了腾出空间，InnoDB 可能需要更积极地进行脏页刷新操作，这同样会消耗额外的 I/O 资源，并且在高负载下可能导致性能瓶颈

Mysql缓存能否替代Redis

1. Redis缓存支持的场景更多。

• 实际工作中缓存的结果不单单是Mysql Select语句返回的结果，有可能是在此基础上又加工的结果；而Mysql缓存的是Select语句的结果

• Redis可以提供更丰富的数据类型的访问，如List、Set、Map、ZSet

1. Redis缓存命中率要远高于Mysql缓存。

• Mysql选择要缓存的语句的方式不是根据访问频率，主要是根据select语句里边是否包含动态变化的值，没有动态变化值的则缓存，比如用了now函数就不会缓存。Redis是由客户端自主根据访问频率高进行缓存。

• Redis丰富的数据结构使得缓存复用率更高，比如缓存的是List，可以随意访问List中的部分元素，比如分页需求

• Mysql缓存的失效粒度很粗，只要表有更新，涉及该表的所有缓存（不管更新是否会影响缓存）都失效，这样使得缓存的利用率会很低，只是适用更新很少的表

• 当存在主从结点，并且会从多个结点读取数据时，各个结点的缓存不会同步3. 性能：Redis的查询性能要远高于Mysql缓存，最主要的原因是Redis是全部放在内存的，但是因为mysql缓存的命中率问题使得Mysql无法全部放到内存中。Redis性能好也还有一些其他原因

• Redis的存储结构有利于读写性能Redis是IO多路复用，可以支持更大的吞吐，Mysql的数据特征使得做成IO多路复用绝大多数情况下也没有意义

• 数据更新时会同时将该表的所有缓存失效，会使得数据更新的速度变慢。

什么是change_buffer

change_buffer 使 buffer_pool 里的一块内容

Change Buffer是 MySQLInnoDB 存储引擎中的一个机制，用于暂存对二级索引的插入和更新操作的变更，而不立即执行这些操作，随后，当InnoDB 进行合适的条件时(如页被读取或 Flush 操作)会将这些变更写入到二级索引中

如果当前表 针对 name 有一个二级索引。假设执行一条 update table set name =‘yes’ where id = 1(这条语句需要修改 name这个二级索引中的数据)，此时bufferpool 并没有对应二级索引的索引页数据。

这个时候需要把索引页加载才内存中立即执行修改吗?不是的，这时候 change buffer 就上场了。

如果当前二级索引页不在 bufferpool 中，那么innodb会把更新保作缓存到 change buffer中，当下次访问到这条教据后，会把索引页加到 bufferpool 中，并且应用上 changebuffer 里面的变更，这样就保证了数据的一致性。上述 SQL 中，change buffer 中会存储 name 字段的旧索引值删除操作和新索引值插入操作。

作用：

• 提高写入性能：通过将对二级索引的变更暂存，可以减少对磁盘的频繁写入，提升插入和更新操作的性能。当二级索引页不在 bufferpool 中时,change buffer可以避免立即从磁盘读取对应索引页导致的昂贵的随机I/O，对应的更改可以在后面当二级索引页读入 bufferpool 时候被批量应用。

• 批量处理：Change Buffer 可以在后续的操作中批量处理这些变更，减少了随机写入的开销。

change buffer 只能用于二级索引的更改，不适用于主键索引，空间索引以及全文索引。还有，唯一索引也不行，因为唯一索引需要读取数据然后检查数据的一致性

更改先缓存在 change buffer 中，假如数据库挂了，更改不是丢了吗? change buffer也是要落盘存储的， change bufer 会落盘到系统表空间里面，然后 redo log 也会记录 change buffer 的修改来保证数据一致性。

Doublewrite Buffer 是什么?它有什么作用?

MhSOL 的 Doublewite Bufer是 InnoDB 存储引擎中的一个机制，用于确保数据的安全性和一致性，其作用是将数据首先写入一个内存缓中区(双写缓中区)，然后再将其写入数据文件。这种方式可以防止在写入过程中因崩溃或故障导致数据损坏，确保数据的一致性和完整性。

工作原理简述：

• 写入流程：当事务提交时 InnoDB 首先将数据写入Doubewrite Bufer，再从该缓冲区将数据写入磁盘的实际数据文件。

• 恢复机制：在崩溃恢复时，InnoDB 会使用 Doublewrite Buffer 中的数据来修复损坏的页，保证数据不丢失

Log Buffer 是什么?它有什么作用?

MySQL 中的 Log Buffer 是一个内存区域，用于暂时存储事务日志(redo log)的数据。在InnoDB 存储引擎中，它的主要作用是提高性能，通过批量写入操作将日志数据从内存中写入磁盘，减少磁盘 I/0 操作的频率.。

MySQL 日志

详细请看：Mysql的三大日志

MySQL 中常见的日志有哪些？

MySQL日志主要包括查询日志、慢查询日志、事务日志、错误日志、二进制日志等。其中比较重要的是 bin log（二进制日志）和 redo log（重做日志）和 undo log（回滚日志）。

bin log

bin log是MySQL数据库级别的文件，记录对MySQL数据库执行修改的所有操作，不会记录select和show语句，主要用于恢复数据库和同步数据库。

redo log

redo log是innodb引擎级别，用来记录innodb存储引擎的事务日志，不管事务是否提交都会记录下来，用于数据恢复。当数据库发生故障，innoDB存储引擎会使用redo log恢复到发生故障前的时刻，以此来保证数据的完整性。将参数innodb_flush_log_at_tx_commit设置为1，那么在执行commit时会将redo log同步写到磁盘。

undo log

除了记录redo log外，当进行数据修改时还会记录undo log，undo log用于数据的撤回操作，它保留了记录修改前的内容。通过undo log可以实现事务回滚，并且可以根据undo log回溯到某个特定的版本的数据，实现MVCC。

bin log和redo log有什么区别？

1. bin log会记录所有日志记录，包括InnoDB、MyISAM等存储引擎的日志；redo log只记录innoDB自身的事务日志。

2. bin log只在事务提交前写入到磁盘，一个事务只写一次；而在事务进行过程，会有redo log不断写入磁盘。

3. bin log是逻辑日志，记录的是SQL语句的原始逻辑；redo log是物理日志，记录的是在某个数据页上做了什么修改。

bin log 有哪些日志类型？

binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED，区别如下：

• STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式， binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致；

• ROW：记录行数据最终被修改成什么样了（这种格式的日志，就不能称为逻辑日志了），不会出现 STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已；

• MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；

注意：不同的日志类型在主从复制下除了有动态函数的问题，同样对对更新时间也有影响。一般来说，数据库中的update_time都会设置成ON UPDATE CURRENT_TIMESTAMP，即自动更新时间戳列。在主从复制下： 如果日志格式类型是STATEMENT，由于记录的是sql语句，在salve端是进行语句重放，那么更新时间也是重放时的时间，此时slave会有时间延迟的问题； 如果日志格式类型是ROW，这是记录行数据最终被修改成什么样了，这种从库的数据是与主服务器完全一致的。

什么是 Write-Ahead Logging (WAL)技术?它的优点是什么?MySQL 中是否用到了 WAL?

WAL(Wite-Ahead Logging)技术是一种数据库事务日志管理技术，它确保在修改真正的数据之前，先将修改记录写入日志，这使得即使系统崩溃，通过日志也能恢复教据，保证了数据的特久性和一致性

WAL它的核心思想就是先写日志，再写数据，大致执行流程如下:

1. 当一个事务开始时，所有对数据库的修改都会先记录到一个日志文件中，而不是直接应用到数据库文件，这些日志记录了数据的变更信息，可以用于恢复数据.。

2. 当日志记录被安全地写入磁盘后，才会将这些修改应用到数据库文件中。

在 MySQL InnoDB 存储引擎中，重做日志 (Redo Log)就是 WAL的实现，用于保证事务的持久性和崩溃恢复能力。InnoDB 重做日志的工作机制如下:

1. 当一个事务开始时，所有对数据库的修改首先记录到重做日志缓冲区中。

2. 重做日志缓冲区的数据会周期性地刷新到磁盘上的重做日志文件(ib_logfile0和ib_logfile1)。

3. 当事务提交时，InnoDB 确保重做日志已写入磁盘，然后将数据页的修改写入数据文件。

4. 如果系统崩溃，InnoDB 会在启动时通过重做日志重新应用所有未完成的事务，以恢复数据库到一致状态。

MySQL插入一条 SQL 语句，redo log 记录的是什么?

因为 redo log 是物理日志，记录“某页(Page)某位置的数据被修改为某值”。它不记录逻辑操作(如“插入一行”，而是直接记录对页的变更。所以在插入操作中，redolog 记录的是事务在数据页上的修改 数据页的插入点、记录的偏移量和插入的实际数据并更新页目录、页头等元数据

插入操作 redo log 具体执行流程

1. 修改缓冲页(Buffer Pool)：数据先写入内存中的缓冲池，而不是直接写入磁盘.

2. 生成 redo log：同时生成一条 redo log，记录插入对数据页的物理修改细节。

3. 日志先行(Write-Ahead Logging, WAL)：redo log 先被写入磁盘上的 redo log 文件。

MySQL的binlog有几种格式？分别有什么区别？

有三种格式，statement，row和mixed。

• statement：每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。

• row：不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。

• mixed：一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。

undo log 是如何保证事务的原子性的？

通过使用 undo log，数据库系统可以记录事务过程中每次数据修改之前的旧值。当事务失败或被中止时，数据库可以利用这些记录将数据恢复到事务开始之前的状态，从而保证事务的原子性。

Undo log 的这种机制确保了即使在事务执行过程中出现错误，数据库仍然能够保持一致性和原子性。

慢查询日志有什么用？

慢查询日志用于捕捉和记录执行时间超过指定阈值的 SQL 查询。慢查询日志在数据库性能优化和问题诊断中发挥着重要作用。

事务

详细内容请查看：Mysql的事务及隔离级别

事务的四大特性？

事务特性ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

• 原子性（Atomicity）：语句要么全执行，要么全不执行，是事务最核心的特性，事务本身就是以原子性来定义的；实现主要基于undo log

• 持久性（Durability）：保证事务提交后不会因为宕机等原因导致数据丢失；实现主要基于redo log

• 隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性保证事务执行尽可能不受其他事务影响；InnoDB默认的隔离级别是RR，RR的实现主要基于锁机制（包含next-key lock）、MVCC（包括数据的隐藏列、基于undo log的版本链、ReadView）

• 一致性（Consistency）：事务追求的最终目标，是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。一致性的实现既需要数据库层面的保障，也需要应用层面的保障

常见的InnoDB是支持事务的，但是MyISAM是不支持事务的

数据库的三大范式

第一范式1NF

确保数据库表字段的原子性。

比如字段 userInfo: 广东省 10086'，依照第一范式必须拆分成 userInfo: 广东省``userTel: 10086两个字段。

第二范式2NF

首先要满足第一范式，另外包含两部分内容，一是表必须有一个主键；二是非主键列必须完全依赖于主键，而不能只依赖于主键的一部分。

举个例子。假定选课关系表为student_course(student_no, student_name, age, course_name, grade, credit)，主键为(student_no, course_name)。其中学分完全依赖于课程名称，姓名年龄完全依赖学号，不符合第二范式，会导致数据冗余（学生选n门课，姓名年龄有n条记录）、插入异常（插入一门新课，因为没有学号，无法保存新课记录）等问题。

应该拆分成三个表：学生：student(stuent_no, student_name, 年龄)；课程：course(course_name, credit)；选课关系：student_course_relation(student_no, course_name, grade)。

第三范式3NF

首先要满足第二范式，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

假定学生关系表为Student(student_no, student_name, age, academy_id, academy_telephone)，主键为"学号”，其中学院id依赖于学号，而学院地点和学院电话依赖于学院id，存在传递依赖，不符合第三范式。

可以把学生关系表分为如下两个表：学生：(student_no, student_name, age, academy_id)；学院：(academy_id, academy_telephone)。

2NF和3NF的区别？

• 2NF依据是非主键列是否完全依赖于主键，还是依赖于主键的一部分。

• 3NF依据是非主键列是直接依赖于主键，还是直接依赖于非主键。

并发事务带来了哪些问题?

先了解下几个概念：脏读、不可重复读、幻读。

• 脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。

• 不可重复读是指在对于数据库中的某行记录，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，另一个事务修改了数据并提交了。

• 幻读是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录。对幻读的正确理解是一个事务内的读取操作的结论不能支撑之后业务的执行。假设事务要新增一条记录，主键为id，在新增之前执行了select，没有发现id为xxx的记录，但插入时出现主键冲突，这就属于幻读，读取不到记录却发现主键冲突是因为记录实际上已经被其他的事务插入了，但当前事务不可见。

不可重复读和幻读有什么区别？

• 不可重复读的重点是内容修改或者记录减少比如多次读取一条记录发现其中某些记录的值被修改；

• 幻读的重点在于记录新增比如多次执行同一条查询语句（DQL）时，发现查到的记录增加了。

幻读其实可以看作是不可重复读的一种特殊情况，单独把幻读区分出来的原因主要是解决幻读和不可重复读的方案不一样。

事务隔离级别有哪些？

事务隔离就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。

MySQL数据库为我们提供的四种隔离级别：

• Serializable (串行化)：通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。

• Repeatable read (可重复读)：MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，解决了不可重复读的问题。

• Read committed (读已提交)：一个事务只能看见已经提交事务所做的改变。可避免脏读的发生。

• Read uncommitted (读未提交)：所有事务都可以看到其他未提交事务的执行结果。

生产环境数据库一般用的什么隔离级别呢？

生产环境大多使用RC。为什么不是RR呢？

可重复读(Repeatable Read)，简称为RR 读已提交(Read Commited)，简称为RC

缘由一：在RR隔离级别下，存在间隙锁，导致出现死锁的几率比RC大的多！ 缘由二：在RR隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行!

也就是说，RC的并发性高于RR。

并且大部分场景下，不可重复读问题是可以接受的。毕竟数据都已经提交了，读出来本身就没有太大问题！

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

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

锁 控制方式下会通过锁来显式控制共享资源而不是通过调度手段，MySQL 中主要是通过 读写锁 来实现并发控制。

• 共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。

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

读写锁可以做到读读并行，但是无法做到写读、写写并行。另外，根据根据锁粒度的不同，又被分为 表级锁(table-level locking) 和 行级锁(row-level locking) 。InnoDB 不光支持表级锁，还支持行级锁，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类。

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

MVCC 在 MySQL 中实现所依赖的手段主要是: 隐藏字段、read view、undo log。

• undo log : undo log 用于记录某行数据的多个版本的数据。

• read view 和 隐藏字段 : 用来判断当前版本数据的可见性。

Mysql是如何实现事务的？

MySQL 主要是通过:锁、RedoLog、Undo Log、MVCC 来实现事务：MySQL利用锁(行锁、间隙锁等等)机制，使用数据并发修改的控制，满足事务的隔离性

它会记录事务对数据库的所有修改，当 MSQL 发生宕机或崩溃时，通过重放redolog 就可以恢复数据，用来满足事务的持久性。

Redo Log(重做日志)，UndoLog(回滚日志)，它会记录事务的反向操作，简单地说就是保存数据的历史版本，用于事务的回滚，使得事务执行失败之后可以恢复之前的样子。实现原子性和隔离性

MVCC(多版本并发控制)，满足了非锁定读的需求，提高了并发度，实现了读已提交和可重复读两种隔离级别，实现了事务的隔离性。

其实事务主要是为了实现一致性，具体是通过 AID，即原子性、隔离性和持久性来达到一致性的目的。

事务的二阶段提交是什么?

MySQL事务的二阶段提交是指在MySQL中，为了确保redo log(重做日志)和binlog(二进制日志)之间的一致性，使用的一种机制，MySQL 通过二阶段提交来保证在crash recovery(崩溃恢复)时，不会出现数据丢失或数据不一致的情况。

二阶段提交的两个阶段：

• 准备阶段(Prepare phase)：在事务提交时，MySQL 的InnoDB引擎会先写入 redolog，并将其状态标记为prepare，表示事务已经准备提交但还未真正完成，此时的 redo 1og是预提交状态，还未标记为完成交。

• 提交阶段(Commit phase): 当redo log 的状态变为 pepare后，MySQLSener会写入 binlog(记录用户的DML操作)，binlog写入成动后，MySQL 会通过 lnnoDB，将 redo log 状态改为commit，完成整个事务的提交过程。

详情可以看这篇文章：两阶段提交

MySQL 的隔离级别是基于锁实现的吗？

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。

SERIALIZABLE 隔离级别是通过锁来实现的，READ-COMMITTED 和 REPEATABLE-READ 隔离级别是基于 MVCC 实现的。不过， SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制，就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读。

MySQL有哪些锁？

详细请查看：Mysql的锁

表级锁和行级锁了解吗？有什么区别？

MyISAM 仅仅支持表级锁(table-level locking)，一锁就锁整张表，这在并发写的情况下性非常差。InnoDB 不光支持表级锁(table-level locking)，还支持行级锁(row-level locking)，默认为行级锁。

行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。

表级锁和行级锁对比：

• 表级锁： MySQL 中锁定粒度最大的一种锁（全局锁除外），是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。不过，触发锁冲突的概率最高，高并发下效率极低。表级锁和存储引擎无关，MyISAM 和 InnoDB 引擎都支持表级锁。

• 行级锁： MySQL 中锁定粒度最小的一种锁，是 针对索引字段加的锁，只针对当前操作的行记录进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行级锁和存储引擎有关，是在存储引擎层面实现的。

行级锁锁的到底是什么？行级锁的使用有什么注意事项？

MySQL 的 InnoDB 存储引擎实现的行级锁，实际上锁定的是基于索引的行记录。这意味着，行锁针对的是数据表中的索引记录，而非数据表本身的物理行。

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

不过，很多时候即使用了索引也有可能会走全表扫描，这是因为 MySQL 优化器的原因。

InnoDB 有哪几类行锁？

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

• 记录锁（Record Lock）：也被称为记录锁，属于单个行记录上的锁。

• 间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。

• 临键锁（Next-Key Lock）：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题（MySQL 事务部分提到过）。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

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

一些大厂面试中可能会问到 Next-Key Lock 的加锁范围，详细可以查看：行级锁怎么加的？ 。

共享锁和排他锁

SELECT 的读取锁定主要分为两种方式：共享锁和排他锁。

这两种方式主要的不同在于LOCK IN SHARE MODE 多个事务同时更新同一个表单时很容易造成死锁。

申请排他锁的前提是，没有线程对该结果集的任何行数据使用排它锁或者共享锁，否则申请会受到阻塞。在进行事务操作时，MySQL会对查询结果集的每行数据添加排它锁，其他线程对这些数据的更改或删除操作会被阻塞（只能读操作），直到该语句的事务被commit语句或rollback语句结束为止。

SELECT... FOR UPDATE 使用注意事项：

1. for update 仅适用于innodb，且必须在事务范围内才能生效。

2. 根据主键进行查询，查询条件为like或者不等于，主键字段产生表锁。

3. 根据非索引字段进行查询，会产生表锁。

MVCC 实现原理？

详细请查看：ReadView

MVCC(Multiversion concurrency control) 就是同一份数据保留多版本的一种方式，进而实现并发控制。在查询的时候，通过read view和版本链找到对应版本的数据。

作用：提升并发性能。对于高并发场景，MVCC比行级锁开销更小。

MVCC 实现原理如下：

1. 首先获取事务自己的事务 ID；

2. 获取 ReadView；

3. 查询得到的数据，然后与 ReadView 中的事务版本号（m_ids，min_trx_id，max_trx_id）进行比较；

4. 如果不符合 ReadView 规则，就需要从 Undo Log 中（即根据roll_point）获取历史快照；

5. 最后返回符合规则的数据。 在隔离级别为读已提交（Read Committed）时，一个事务中的每一次 SELECT 查询都会重新获取一次Read View

Read View的规则

1. 如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前 已经提交的事务生成的，所以该版本的记录对当前事务可见。

2. 如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后 才启动的事务生成的，所以该版本的记录对当前事务不可见。

3. 如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：

• 如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。

• 如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

如果 MySQL中没有 MVCC，会有什么影响?

如果没有 MVCC，系统必须频繁地对读写操作进行加锁来保证数据的正确性，因为增加了锁的获取和释放的开销，会导致整体系统响应速度变慢，这种实现叫 LBCC (Lock-Based Concurrent Control).

快照读和当前读

表记录有两种读取方式。

• 快照读：读取的是快照版本。普通的SELECT就是快照读。通过mvcc来进行并发控制的，不用加锁。

• 当前读：读取的是最新版本。UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是当前读。

快照读情况下，InnoDB通过mvcc机制避免了幻读现象。而mvcc机制无法避免当前读情况下出现的幻读现象。因为当前读每次读取的都是最新数据，这时如果两次查询中间有其它事务插入数据，就会产生幻读。

select…for update会锁表还是锁行？

如果查询条件用了索引/主键，那么select ... for update就会加行锁。

如果是普通字段(没有索引/主键)，那么select ..... for update就会加表锁。

update 是锁行还是锁表？

首先，InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

1. 当执行update语句时，where中的过滤条件列，如果用到索引，就是锁行；如果无法用索引，就是锁表。

2. 如果两个update语句同时执行，第一个先执行触发行锁，但是第二个没有索引触发表锁，因为有个行锁住了，所以还是会等待行锁释放，才能锁表。

3. 当执行insert或者delete语句时，锁行。

MySQL是如何避免幻读的？

• 在快照读情况下，MySQL通过mvcc来避免幻读。

• 在当前读情况下，MySQL通过next-key来避免幻读（加行锁和间隙锁来实现的）。

next-key包括两部分：行锁和间隙锁。行锁是加在索引上的锁，间隙锁是加在索引之间的。

Serializable隔离级别也可以避免幻读，会锁住整张表，并发性极低，一般不会使用。

乐观锁和悲观锁是什么？

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

• 悲观锁：假定会发生并发冲突，会对操作的数据进行加锁，直到提交事务，才会释放锁，其他事务才能进行修改。实现方式：使用数据库中的锁机制。

• 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否数据是否被修改过。给表增加version字段，在修改提交之前检查version与原来取到的version值是否相等，若相等，表示数据没有被修改，可以更新，否则，数据为脏数据，不能更新。实现方式：乐观锁一般使用版本号机制或CAS算法实现。

MySQL只操作同一条记录也会死锁吗

在MySQL中，即使多个事务仅对同一条记录进行操作，也可能发生死锁。这通常与InnoDB存储引擎的锁管理机制有关。以下是一些触发这种情况的场景：

索引导致的锁竞争：如果事务在WHERE子句中使用不同的索引来查找相同的行，InnoDB可能会导致多个事务以不同顺序锁住这些索引，从而产生死锁。 自增锁（auto-increment lock）：在使用自增列时，不同事务可能在等待获取表级锁来增加自增值，某些情况下也会出现死锁。 外键约束：外键检查过程中可能会涉及多个表，多个事务可能会因为不同的锁顺序而陷入死锁。

MySQL 中如果发生死锁应该如何解决?

自动检测与回滚：MySQL自带死锁检测机制(innodb deadlock detect)，当检测到死锁时，数据库会自动回滚其中一个事务，以解除死锁。通常会回滚事务中持有最少资源的那个。也有锁等待超时的参数(innodblock wait timeout)，当获取锁的等待时间超过阈值时，就释放锁进行回滚

手动 kill 发生死锁的语句：可以通过命令，手动快速地找出被阻塞的事务及其线程ID，然后手动 kill它，及时释放资源

MySQL 中长事务可能会导致哪些问题?

• 长时间的锁竞争，阻塞资源：长事务持有锁的时间较长，容易导致其他事务在尝试获取相同锁时发生阻塞，从而增加系统的等待时间和降低并发性能。业务线程也会因为长时间的数据库请求等待而阻塞，部分业务的阻塞可能还会影响到别的服务，导致产生雪崩，最终使得服务全面崩盘，导致非常严重的线上事故。

• 死锁风险：长事务更容易产生死锁，因为多个事务可能在互相等待对方释放锁，导致系统无法继续执行。

• 主从延迟：主库需要长时间执行，然后传输给从库，从库又要重放好久，期间可能有很长一段时间数据是不同步的。

• 回滚导致时间浪费：如果长事务执行很长一段时间，中间突发状况导致抛错，使得事务回滚了，之前做的执行都浪费了。

读写分离和分库分表了解吗？

什么是读写分离？

见名思意，根据读写分离的名字，我们就可以知道：读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。 这样的话，就能够小幅提升写性能，大幅提升读性能。

一般情况下，我们都会选择一主多从，也就是一台主数据库负责写，其他的从数据库负责读。主库和从库之间会进行数据同步，以保证从库中数据的准确性。这样的架构实现起来比较简单，并且也符合系统的写少读多的特点。

如何实现读写分离？

不论是使用哪一种读写分离具体的实现方案，想要实现读写分离一般包含如下几步：

1. 部署多台数据库，选择其中的一台作为主数据库，其他的一台或者多台作为从数据库。

2. 保证主数据库和从数据库之间的数据是实时同步的，这个过程也就是我们常说的主从复制。

3. 系统将写请求交给主数据库处理，读请求交给从数据库处理。

什么是MySQL主从同步？是如何实现的？

主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。

因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

主要是通过二进制日志(BinaryLog，简称 binlog)实现数据的复制。主数据库在执行写操作时，会将这些操作记录到 binlog 中，然后推送给从数据库，从数据库重放对应的日志即可完成复制。

为什么要做主从同步？

1. 读写分离，使数据库能支撑更大的并发。

2. 在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。

3. 数据备份，保证数据的安全。

如何处理 MySQL 的主从同步延迟?

首先需要明确一个点延迟是必然存在的，无论怎么优化都无法避免延迟的存在，只能减少延迟时间。

常见解决方式有以下几种：

• 二次查询：如果从库查不到数据，则再去主库查一遍，由API 封装这个逻辑即可，算是一个兜底策略，比较简单。不过等于读的压力又转移到主库身上了，如果有不法分子故意查询必定查不到的查询，这就对主库产生冲击了。

• 强制将写之后立马读的操作转移到主库上。这种属于代码写死了，比如一些写入之后立马查询的操作，就绑定在一起，写死都走主库。不推荐，比较死板。

• 关键业务读写都走主库，非关键还是读写分离。比如用户注册这种，可以读写主库，这样就不会有登陆报该用户不存在的问题，这种访问量频次应该也不会很多，所以看业务适当调整此类接口。

• 使用缓存，主库写入后同步到缓存中，这样查询时可以先查询缓存，避免了延识的问题，不过又引入了缓存数据一致性的问题。

除此之外，也可以提一提配置问题，例如主库的配置高，从库的配置太低了，可以提升从库的配置等。如果面试官对 Mysql 比较熟，可能会追问一些偏 DBA 侧的问题，例如并行复制等

了解mysql的pxc集群架构吗？

特性维度传统主从复制架构PXC 集群架构架构模式​主从模式（异步/半同步）多主模式，每个节点均可读写数据一致性​最终一致性（存在延迟）强一致性，事务在所有节点提交或不提交复制延迟​不可避免极低延迟，近乎实时同步扩展性​读扩展方便，写扩展困难读扩展方便，写扩展受限（所有节点都需写入）故障切换​需要外部工具（如MHA）自动故障转移，内置高可用 数据同步流程：当一个节点收到写事务时，并不会立即提交。它会将此次操作产生的数据变更集合（Write Set）通过4567端口广播给集群中的所有其他节点。每个节点都会对接收到的Write Set进行冲突认证。只有在所有节点都认证通过（无冲突）后，该事务才会在所有节点上提交；反之，只要有一个节点认证失败，整个事务会在所有节点回滚，从而保证强一致性。

数据同步有两种方式：

• SST，用于新节点加入时的全量数据同步；

• IST，用于短暂离线的节点重新加入时，只同步它缺失的那部分增量数据，效率更高

还了解别的集群架构吗？PXC QMHA 3M 三种集群架构的区别？

特性PXC (Percona XtraDB Cluster)QMHA (基于MHA)3M (MMM)架构模式​多主同步​ (Multi-Master)单主异步​ (Master-Slave)主从代理​ (Master-Slave with Virtual IP)数据一致性​强一致性​ (所有节点数据实时同步)最终一致性​ (主从延迟导致数据可能不一致)最终一致性​ (主从延迟，一致性最弱)高可用实现​内置，节点故障自动剔除，客户端可写其他节点外部脚本，自动监控、主库故障切换外部脚本​ + 虚拟IP，管理主库切换性能影响​写性能受影响（事务需集群认证），读性能可扩展对主库性能几乎无影响，原生复制性能开销主要在网络切换和VIP变更功能透明​应用无感知，可连接任意节点读写应用需感知，读写分离需改造，切换后可能需修改配置应用基本无感知（通过VIP连接）复杂度​中等，配置相对复杂，但管理方便高，需要部署和管理MHA Manager和Node较低，架构和配置简单扩展性​读扩展性好，写扩展性差（所有节点都需写入）读扩展性好，写无法扩展读扩展性好，写无法扩展适用场景​数据一致性要求极高、读多写少的业务（如金融、账户）对数据一致性要求可接受秒级延迟、读多写少的业务已被淘汰，适用于旧版本MySQL的简单场景

分库分表

当单表的数据量达到1000W或100G以后，优化索引、添加从库等可能对数据库性能提升效果不明显，此时就要考虑对其进行切分了。切分的目的就在于减少数据库的负担，缩短查询的时间。

数据切分可以分为两种方式：垂直划分和水平划分。

垂直划分

垂直划分数据库是根据业务进行划分，例如购物场景，可以将库中涉及商品、订单、用户的表分别划分出成一个库，通过降低单库的大小来提高性能。同样的，分表的情况就是将一个大表根据业务功能拆分成一个个子表，例如商品基本信息和商品描述，商品基本信息一般会展示在商品列表，商品描述在商品详情页，可以将商品基本信息和商品描述拆分成两张表。 优点：行记录变小，数据页可以存放更多记录，在查询时减少I/O次数。

缺点：

• 主键出现冗余，需要管理冗余列；

• 会引起表连接JOIN操作，可以通过在业务服务器上进行join来减少数据库压力；

• 依然存在单表数据量过大的问题。

水平划分

水平划分是根据一定规则，例如时间或id序列值等进行数据的拆分。比如根据年份来拆分不同的数据库。每个数据库结构一致，但是数据得以拆分，从而提升性能。 优点：单库（表）的数据量得以减少，提高性能；切分出的表结构相同，程序改动较少。

缺点：

• 分片事务一致性难以解决

• 跨节点join性能差，逻辑复杂

• 数据分片在扩容时需要迁移

分库分表的类型(或策略)包括：

• 水平分表：将同一张表的数据按行划分，分散到多个表中。例如，可以按用户ID的范围将数据分为多个表(如user_1、user_2)。

• 垂直分表：将一张表的不同列拆分到多个表中，以减少每张表的字段数量和提高查询效率。例如，用户表可以分为基本信息表和详细信息表。

• 水平分库：将相同的表结构复制一份到另一个库中，每个库的表结构是一样的，可以减少单一数据库的读写压力，在大数量的情况下提高读写性能。例如，database1、database2 。

• 垂直分库：将数据分散到不同的数据库实例中。可以根据业务功能或模块进行分库，如将用户数据、订单数据分别存储在不同的数据库中。

分库分表sharding策略

• 哈希取模: hash(key) % NUM_DB

  • 如果未来需要增加表数量（如从64张扩容到128张），会导致大部分数据的哈希取模结果改变，需要进行大规模的数据迁移；可以考虑使用一致性哈希算法或ShardingSphere内置的COSID_MOD等算法。

• 范围: 可以是 ID 范围也可以是时间范围。

  • 注意：单纯的按时间范围或ID范围分片容易导致“热点”问题（如最新的月份数据特别活跃），可以考虑结合业务ID进行复合分片

• 映射表: 使用单独的一个数据库来存储映射关系

如果组长要求你主导项目中的分库分表，大致的实施流程是?

1. 先分析业务需求：确定数据量及增长趋势，评估分库分表的必要性。(需要一定的预判但是不要过度设计)

2. 设计分库分表方案：选择适合的分库和分表策略(水平、垂直、哈希、范围等)，并规划分库分表的结构。

3. 实现数据路由：根据分库分表策略设计数据路由机制，一般通过应用层代码或数据库中间件来实现，将请求路由到相应的数据库或表。

4. 数据迁移：将现有数据迁移到新的分库分表结构中，可以通过批量导入、ETL工具或脚本来完成

对数据库进行分库分表可能会引发哪些问题?

• 事务的问题：我们使用关系型数据库，有很大一点在于它保证事务的完整性。而分库之后单机事务就用不上了，必须使用分布式事务来解决，而分布式事务相对而言就比较重了，而且大部分的分布式事务只能保证最终一致性，所以业务上会存在数据不一致的场景

• 连表 JOIN 问题：在一个库中的时候我们还可以利用 JOIN 来连表查询，而跨库了之后就无法使用 JOIN 了。此时的解决方案就是在业务代码中进行关联，也就是先把一个表的数据查出来，然后通过得到的结果再去查另一张表，然后利用代码来关联得到最终的结果。这种方式实现起来稍微比较复杂，不过也是可以接受的。还有可以适当的冗余一些字段。比如以前的表就存储一个关联 ID，但是业务时常要求返回对应的 Name 或者其他字段。这时候就可以把这些字段冗余到当前表中，来去除需要关联的操作。或者通过宽表的形式查询，比如将数据全量存储至 ES 中，利用 ES 来查询数据。

• 全局 ID 唯一性问题：以前单库单表有接使用数据库的自增ID即可，但是分库分表之后，使用自增 ID 会号致重复主键的情况。此时需要利用雪花算法或者其他全局唯一ID发号器来生成唯一主键

• 排序问题：单表直接通过 orderby 进行排序即可，分库分表后直接利用数据库是无法实现排序的，要么利用分库分表中间件的能力进行汇总排序，要么自己在业务代码中排序，要么利用ES 存储全量数据排序查询。

• count 问题：其实和排序问题类似，单表可以直接 count，分库分表后无法支持，只能多表 count 然后业务代码中雾加，或者单独搞一个地方来维护总数，要么还是利用 ES

什么是分区表？

分区是把一张表的数据分成N多个区块。分区表是一个独立的逻辑表，但是底层由多个物理子表组成。

当查询条件的数据分布在某一个分区的时候，查询引擎只会去某一个分区查询，而不是遍历整个表。在管理层面，如果需要删除某一个分区的数据，只需要删除对应的分区即可。

分区一般都是放在单机里的，用的比较多的是时间范围分区，方便归档。只不过分库分表需要代码实现，分区则是mysql内部实现。分库分表和分区并不冲突，可以结合使用。

分区表类型

• range分区：按照范围分区。比如按照时间范围分区

• list分区：list分区和range分区相似，主要区别在于list是枚举值列表的集合，range是连续的区间值的集合。对于list分区，分区字段必须是已知的，如果插入的字段不在分区时的枚举值中，将无法插入

• hash分区：可以将数据均匀地分布到预先定义的分区中。

分区的问题？

1. 打开和锁住所有底层表的成本可能很高。当查询访问分区表时，MySQL 需要打开并锁住所有的底层表，这个操作在分区过滤之前发生，所以无法通过分区过滤来降低此开销，会影响到查询速度。可以通过批量操作来降低此类开销，比如批量插入、LOAD DATA INFILE和一次删除多行数据。

2. 维护分区的成本可能很高。例如重组分区，会先创建一个临时分区，然后将数据复制到其中，最后再删除原分区。

3. 所有分区必须使用相同的存储引擎。

MySQL单表多大进行分库分表？

目前主流的有两种说法：

1. MySQL 单表数据量大于 2000 万行，性能会明显下降，考虑进行分库分表。

2. 阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。

事实上，这个数值和实际记录的条数无关，而与 实际存储的数据大小有关，详细查看：[为什么说MySQL 一般单表不要超过 2000W 行](https://www..top/database/mysql/01-basement1-innodbstoragestructure.html#为什么说MySQL 一般单表不要超过 2000W 行)

高度为3的B+树，可以存放多少数据？

InnoDB存储引擎有自己的最小储存单元——页（Page）。

查询InnoDB页大小的命令如下：

可以看出 innodb 默认的一页大小为 16384B = 16384/1024 = 16kb。

在MySQL中，B+树一个节点的大小设为一页或页的倍数最为合适。因为如果一个节点的大小 < 1页，那么读取这个节点的时候其实读取的还是一页，这样就造成了资源的浪费。

B+树中非叶子节点存的是key + 指针；叶子节点存的是数据行。

对于叶子节点，如果一行数据大小为1k，那么一页就能存16条数据。

对于非叶子节点，如果key使用的是bigint，则为8字节，指针在MySQL中为6字节，一共是14字节，则16k能存放 16 * 1024 / 14 = 1170 个索引指针。

于是可以算出，对于一颗高度为2的B+树，根节点存储索引指针节点，那么它有1170个叶子节点存储数据，每个叶子节点可以存储16条数据，一共 1170 x 16 = 18720 条数据。而对于高度为3的B+树，就可以存放 1170 x 1170 x 16 = 21902400 条数据（两千多万条数据），也就是对于两千多万条的数据，我们只需要高度为3的B+树就可以完成，通过主键查询只需要3次IO操作就能查到对应数据。

所以在 InnoDB 中B+树高度一般为3层时，就能满足千万级的数据存储。