MySQL篇(纯享版)

为什么会有纯享版

之前的文档设计每一个问题上都有口语话和详细回答两个部分

速刷的时候其实很不想再去看一遍详细的东西, 而只关注于背诵

因此, 纯享版就诞生啦

v1.2 2024/10/05 根据小林的mysql做一波优化

v1.3 2024/10/27 纯享版是对于上下两篇内容的总结和优化，对于MySQL的其他深度的内容我还是摘出来单成一文了

1. MySQL事务的四大特性

MySQL事务有四个特性：原子性、一致性、隔离性和持久性。原子性保证事务中的操作要么全做要么全不做，比如银行转账，扣款和加款必须同时成功或同时失败；一致性确保事务执行前后，数据库从一个一致状态到另一个一致状态，比如转账后账户A减少的金额等于账户B增加的金额；隔离性保证 并发事务 互不干扰，有不同隔离级别来避免脏读、不可重复读等问题；持久性则确保事务提交后的数据不会丢失，即使系统崩溃，数据仍然存在。

补充——这些特性是如何实现的？

MySQL InnoDB 引|擎通过什么技术来保证事务的这四个特性的呢?

持久性是通过 redo_log(重做日志)来保证的

原子性是通过 undo_log(回滚日志)来保证的

隔离性是通过 MVCC(多版本并发控制) 或锁机制来保证的

一致性则是通过持久性+原子性+隔离性来保证

2. MySQL的事务隔离级别

MySQL支持四种事务隔离级别：读未提交、读已提交、可重复读和串行化。读未提交允许事务读取未提交的数据，可能导致脏读；读已提交只能读取已提交的数据，避免脏读，但可能出现不可重复读；可重复读保证在同一个事务中多次读取数据结果一致，避免脏读和不可重复读，但可能出现幻读；串行化是最高级别，事务串行执行，完全避免脏读、不可重复读和幻读，但会影响并发性能。

默认级别是 可重复读

补充——这些隔离级别是如何实现的？

对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了

对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问

对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的 区别在于创建 Read View 的时机不同，「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read view，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read view。（还是很好理解的）

3. MySQL脏读,幻读,不可重复读如何解释

脏读就是一个事务读取了另一个未提交事务的数据，之后如果该事务回滚，那么读取的数据就是无效的。不可重复读是指一个事务在多次读取同一数据时，由于另一个事务的修改，导致数据前后不一致。而幻读则是指事务在读取数据时，另一事务插入了新数据，导致读取的 「记录数量」发生了变化。不可重复读主要是数据修改的问题，而幻读是新增或删除数据导致的记录变化。

4. MySQL存储引擎MyISAM和InnoDB区别

MyISAM 和 InnoDB 是 MySQL 中两种常见的存储引擎。

MyISAM 不支持事务和外键，锁机制是表级锁，适合读多写少的场景，比如日志分析。而 InnoDB 支持事务和外键，采用行级锁，适合高并发的事务密集型场景，比如在线交易系统。

虽然MyISAM和InnoDB都支持B+Tree索引，但是存储结构略有不同

• InnoDB ：索引的叶子节点保存数据本身
• MyISAM：索引的叶子节点保存数据的物理地址

索引结构 : InnoDB 是聚簇索引，MyISAM 是非聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚簇索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

此外，MyISAM 全文索引支持更好，数据存储效率较高，但崩溃恢复能力弱；InnoDB 虽然占用更多存储空间，但崩溃恢复能力强，适合处理大规模数据。

补充

Memory : Memory引擎将数据存储在内存中，适用于对性能要求较高的读操作，但是在服务器重启或崩溃时数据会丢失。它不支持事务、行级锁和外键约束。

count 的效率 : InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快。

5. 存储引擎应该如何选择

选择 MySQL 存储引擎时，要根据应用需求来做选择。如果需要支持事务，InnoDB 是更好的选择，它支持 ACID 特性，适合高并发、频繁写操作的场景，同时还支持外键约束，能保证数据一致性。MyISAM 则不支持事务，但在读多写少的场景中表现优秀，查询速度快，占用资源少。

另外，MyISAM 原生支持全文索引，适合需要全文搜索的应用，而 InnoDB 从 MySQL 5.6 开始也支持，但性能可能不如 MyISAM。在数据恢复方面，InnoDB 支持自动崩溃恢复，数据安全性较高，MyISAM 恢复相对较慢。

此外，InnoDB 的表大小上限为 64TB，而 MyISAM 可以支持到 256TB。根据需求选择合适的存储引擎，可以获得最佳性能和数据完整性保障。

InnoDB引擎在事务支持、并发性能、崩溃恢复等方面具有优势，因此被MySQL选择为默认的存储引擎。

6. 自适应Hash索引有什么坏处

自适应哈希索引是 InnoDB 的优化功能，用来加速一些频繁访问的查询。但它有一些缺点。首先，它需要额外的内存，如果哈希表太大，会占用很多内存资源。其次，频繁写入时，维护哈希索引会增加 CPU 的负担。此外，在高并发环境中，更新哈希索引时可能会引发锁竞争，影响性能。这个索引对等值查询效果好，但对范围查询（比如 > 或 BETWEEN）帮助不大。最后，虽然它是自动管理的，但有时需要调整配置，这会增加数据库管理的复杂性。

7. MySQL使用规范有哪些

选择存储引擎时要根据应用场景来决定，比如 InnoDB 适合事务处理，而 MyISAM 适合读多写少的场景。选择字符集时，要确保它能支持所需的语言，比如使用 utf8mb4。SQL 语句一定要加注释，以提高可读性。尽量避免使用存储过程、视图、触发器和事件，因为它们会增加复杂性。文本字段最好不要和主表放在一起使用，以免影响性能。外键的使用要谨慎，尽量避免。要遵循命名规范，确保一致性。使用域名连接数据库会更方便管理。单表的列数要控制，避免大宽表。最后，设计数据库时要结合实际业务需求，确保结构合理。

8. 在建立索引的时候,需要考虑哪些因素

在建立索引时，要考虑以下几点：首先，了解你的查询模式，关注哪些查询最常用，并确保索引能覆盖这些查询，减少回表操作。其次，考虑数据分布，高选择性的列更适合索引，而低选择性的列通常不适合。然后，根据索引的类型选择合适的方案，比如 B-Tree 索引适合大多数查询，全文索引用于文本搜索。还要注意索引的覆盖范围，单列索引和复合索引各有优缺点，要根据实际需求选择。此外，要考虑索引的维护成本，比如写操作会增加开销和存储空间占用。表的大小也影响索引的设计，大表通常需要更精细的索引。定期评估和优化现有索引，并监控索引的使用情况。最后，索引列的顺序很重要，复合索引要遵循最左前缀原则，根据查询需求调整索引范围和条件。

9. 数据库的三范式是什

数据库的三范式包括第一范式、第二范式和第三范式。第一范式要求数据表中的每个列都包含原子值，第二范式要求每个非主键列都完全依赖于主键，第三范式则要求所有非主键列都与主键直接相关。简单来说，这些范式帮助数据库设计保持数据的整洁和一致性。

10. MySQL的explain有哪些列

MySQL的EXPLAIN可以帮助你了解查询的执行计划。它主要有以下几列：id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra。这些列告诉你每个表的扫描方式、使用的索引、预期的结果行数等信息，有助于优化查询性能。

11. 用explain分析举一个具体的例子

MySQL客户端连接成功以后，可以通过

SHOW GLOBAL STATUS LIKE `Com_______`;

(上面是七个下划线)，这个语句的作用是返回当前全局下语句的执行次数，可以反映数据库以什么操作为主（比如可以看到SELECT语句的频次较多），而慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位是秒，默认10s）的所有SQL语句的日志，默认慢查询日志未开启，需要在MySQL的配置文件（/etc/my.cnf）中配置

配置慢查询日志的操作：
进入/etc/my.cnf
文件中写入下面内容：
# 慢查询日志开关
slow_query_log=1
# 慢查询时间
long_query_time=2

按照上述操作以后，会在/var/lib/mysql目录下生成慢查询日志localhost-slow.log

使用 EXPLAIN 可以帮你了解查询是怎么执行的。比如，有一个简单的查询：SELECT * FROM employees WHERE department = 'Sales';。用 EXPLAIN 分析这个查询，你可以看到列出如 id、select_type、table、type、possible_keys、key、key_len、ref、rows 和 Extra 等信息。这些信息告诉你查询用到了哪些索引，预计扫描了多少行，查询的效率如何等，从而帮助你优化数据库性能。

12. MySQL语句有哪些种类

数据库中有几种不同的语言来处理不同的任务：

1. 数据定义语言 (DDL) 用于创建和管理数据库结构，比如创建表和索引。例如，你可以用 CREATE 语句创建一个表，ALTER 语句修改表结构，DROP 语句删除表，TRUNCATE 清空表数据但保留结构。
2. 数据操作语言 (DML) 用于处理表中的数据，比如插入、更新、删除和查询数据。比如，INSERT 用来添加数据，UPDATE 用来更新数据，DELETE 用来删除数据，而 SELECT 用来查询数据。
3. 数据控制语言 (DCL) 用于控制用户访问权限，比如 GRANT 授予权限，REVOKE 撤销权限。
4. 事务控制语言 (TCL) 用于管理事务，确保数据的一致性。例如，START TRANSACTION 开始事务，COMMIT 提交事务，ROLLBACK 回滚事务，还有 SAVEPOINT 和 ROLLBACK TO SAVEPOINT 用来设置和回滚到事务中的保存点。
5. 数据查询语言 (DQL) 主要是 SELECT 语句，用于查询数据。
6. 其他：例如，EXPLAIN 解释查询执行计划，SHOW 显示数据库对象的信息，DESCRIBE 显示表结构。

13. MySQL查询优化建议

在优化查询时，首先要避免全表扫描，最好在查询涉及的字段上建立索引，这样能显著提高查询速度。其次，尽量不要在 WHERE 子句中判断 NULL 值，创建表时就设置好字段为 NOT NULL 或使用特殊值代替。避免使用 != 或 <> 操作符，因为这些操作符会降低索引的利用率，尽量用 <、<=、=、>、>=、BETWEEN 或 IN。同时，使用 OR 连接的条件也会导致全表扫描，考虑用 UNION 合并查询。对于 IN 和 NOT IN，特别是当值是连续的，可以用 BETWEEN 替代，这样会更高效。LIKE 查询也要注意，避免用 %abc% 或 %abc，这会导致全表扫描，如果可能的话用全文检索，只有以 abc% 开头的 LIKE 查询才可能利用索引。对于参数化查询，避免在 WHERE 子句中使用参数，尽量强制使用索引来提高查询效率。此外，避免在 WHERE 子句中对字段进行表达式操作，这会导致全表扫描。最后，使用 EXISTS 替代 IN 可以提升查询效率，因为 EXISTS 会在找到结果后立即停止查询，而 IN 需要扫描整个子查询结果集。

对于使用索引的优化，我们可以用SQL提示（优化数据库的一个重要手段，简单来说就是SQL语句中人为的加入提示来优化操作）

explain select * from tb_user use index(idx_ser_pro) where profession = '软件工程';

explain select * from tb_user ignore index(idx_ser_pro) where profession = '软件工程';

explain select * from tb_user force index(idx_ser_pro) where profession = '软件工程';

上面就是认为的提示是否（强制/忽略）使用某一个索引以达到优化的目的

14. MySQL聚集索引是什么

在 MySQL 中，聚集索引是将数据表的记录按索引的顺序物理存储的方式。这意味着表里的数据行和索引行是一起存储的，数据的顺序和索引的顺序是一致的。聚集索引的叶节点包含了实际的数据行，所以它的优点是查询效率高，特别是对范围查询和排序操作更有利。不过，它也有一些缺点，比如插入数据时可能比较慢，因为要维持索引顺序，还可能导致更新代价高和存储空间占用多。

在 InnoDB 存储引擎中，如果你没有显式地定义主键，它会自动选择一个唯一的非空索引作为聚集索引。如果没有这样的索引，它还会自动生成一个隐藏的聚集索引。选择聚集索引时，最好选择那些常用来查询、排序的列，同时避免频繁更新的列，以减少维护成本。

补充

如果有主键，默认会使用主键作为聚簇索引的索引键;

如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键

在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键

15. MySQL非聚集索引是什么

非聚集索引（也叫做辅助索引或二级索引）与聚集索引不同，它不会改变数据的物理存储顺序。相反，非聚集索引的叶节点只包含指向实际数据行的指针，这些指针通常是聚集索引的键。一个表可以有多个非聚集索引，这样可以加速对不同列的查询。不过，非聚集索引会占用额外的存储空间，并且在数据插入、更新或删除时需要维护，可能会增加一些额外的开销。

补充 （聚集索引和非聚集索引的区别是什么）

数据存储 : 在聚簇索引中，数据行按照索引键值的顺序存储，也就是说，索引的叶子节点包含了实际的数据行。这意味着索引结构本身就是数据的物理存储结构。非聚簇索引的叶子节点不包含完整的数据行，而是包含指向数据行的指针或主键值。数据行本身存储在聚簇索引中。

索引与数据关系 : 由于数据与索引紧密相连，当通过聚簇索引查找数据时，可以直接从索引中获得数据行，而不需要额外的步骤去查找数据所在的位置（回表查询）。当通过非聚簇索引查找数据时，首先在非聚簇索引中找到对应的主键值，然后通过这个主键值 回溯 到聚簇索引中查找实际的数据行，这个过程称为“回表”。

唯一性 : 聚簇索引通常是基于主键构建的，因此每个表只能有一个聚簇索引，因为数据只能有一种物理排序方式。一个表可以有多个非聚簇索引，因为它们不直接影响数据的物理存储位置。

效率 : 对于范围查询和排序查询，聚簇索引通常更有效率，因为它避免了额外的寻址开销。非聚簇索引在使用覆盖索引进行查询时效率更高，因为它不需要读取完整的数据行。但是需要进行回表的操作，使用非聚簇索引效率比较低，因为需要进行额外的回表操作。

16. 为什么聚集索引不要选择频繁的更新的列

聚集索引不要选择频繁更新的列，主要是因为更新这些列会引发一系列性能问题。首先，数据需要重排来保持索引的顺序，这样会消耗大量资源。其次，更新会导致页分裂，增加了磁盘碎片和查询延迟。此外，频繁更新还会影响二级索引，因为它们也需要同步更新。最后，这些操作可能增加锁争用和死锁的风险，从而影响系统的整体性能。因此，选择聚集索引时，应尽量避免使用那些更新频繁的列。

17. MySQL回表查询是什么

回表查询是指在使用非聚集索引时，数据库需要先通过索引找到相关记录的主键，然后再根据这个主键去实际的数据表中查找具体的数据。这种操作发生在你查询的列不完全包含在索引中时。举个例子，如果你在一个表的某列上创建了非聚集索引，然后查询其他列的数据，数据库就需要先通过索引找到主键，再去表中获取这些列的数据。为了减少这种操作，可以使用覆盖索引，即索引中包含了所有查询需要的列，这样可以直接从索引中获取数据，不需要回表查询。

补充

从物理存储的角度来看，索引分为聚簇索引(主键索引)二级索引(辅助索引)

• 主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里;
• 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

所以，在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了这个过程就是回表。

18. MySQL的覆盖索引（联合索引）是什么

在 MySQL 中，覆盖索引就是一个索引包含了查询需要的所有列，这样查询就能完全从这个索引中获取数据，而不用再访问实际的数据行（不需要回表）。这可以显著提高查询速度，因为减少了对数据的访问次数，从而减少了磁盘 I/O 操作。如果你创建了一个覆盖索引，那么 MySQL 就能直接从索引里找到所有需要的数据，避免了回表的步骤，这对提高查询性能非常有效。但是，要注意覆盖索引可能会增加索引的大小，并且在插入、更新和删除数据时会带来额外的维护开销。

要注意存在 最左前缀原则，也就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循「最左前缀原则」，联合索引会失效这样就无法利用到索引快速查询的特性了。

概率越高，实际开发工作中建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到,

区分度就是某个字段 column 不同值的个数「除以」表的总行数，计算公式如下 $$ 区分度 = \frac{distinct(column)}{count(*)} $$

为什么会有「最左前缀原则」

下图举例一个用no和name构成的联合索引（顺序就是no，name）

可以看到联合索引的非叶子节点用no和name两个字段的值作为索引结构的KEY值，并且当在联合索引查询数据的时候，先按照no字段比较，在no字段相同的时候按照name字段比较。也就是说，B+Tree索引先按照no排序然后在no相同的情况按照name字段排序。

因此，索引在存储的结构依据构建联合索引时的「顺序」，因此存在「最左⬅️前缀原则」，也就是最左优先的方式进行匹配，如果不遵循的话联合索引就会失效

19. MySQL的索引结构?

在 MySQL 中，主要有两种索引结构：B+树索引和哈希索引。B+树索引是最常用的，尤其在 InnoDB 存储引擎中使用。它是一种平衡树，数据存储在叶子节点，支持范围查询和顺序访问。哈希索引则主要用于等值查询，速度很快，但不支持范围查询。InnoDB 使用 B+树来实现聚簇索引（按主键顺序存储数据）和辅助索引（存储主键指针）。哈希索引在 Memory 存储引擎中用得比较多。

补充

B+Tree 是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。

每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个 双向链表。

查询案例

select * from product where id = 5;

这条语句使用了主键索引査询 id 号为5的商品。查询过程是这样的，B+Tree 会自顶向下逐层进行查找:

将 5 与根节点的索引数据 (1，10，20) 比较，5 在1和 10 之间，所以根据 B+Tree的搜索逻辑，找到第二层的索引数据(1，4，7);

在第二层的索引数据(1，4，7)中进行查找，因为5在4和7之间，所以找到第三层的索引数据(4，5，6);

在叶子节点的索引数据(4，5，6)中进行查找，然后我们找到了索引值为5 的行数据。

数据库的索引和数据都是存储在硬盘的，我们可以 把读取一个节点当作一次磁盘 I/O 操作。那么上面的整个查询过程一共经历了3个节点，也就是进行了3次 I/O 操作。

B+Tree 存储千万级的数据只需要 3 - 4 层高度就可以满足，这意味着从千万级的表查询目标数据最多需要 3 - 4 次磁盘 I/O。所以B+Tree 相比于 B树和二叉树来说，最大的优势在于 查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3 - 4次。

20. 什么是前缀索引

前缀索引是一种只对字符串列的前几位进行索引的方式。它特别适用于字符串长度较长的列，当前几位就能有效区分记录时，可以显著减少索引的存储空间，提高查询性能。创建时需要指定前缀长度，比如对一个 URL 列的前 10 个字符进行索引。虽然前缀索引能减少存储需求，但可能不够精确，特别是当前缀相同时可能需要额外扫描。

补充

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

使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率

在创建表时，创建前缀索引的方式如下

CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
);

建表后，如果要创建前缀索引，可以使用这面这条命令

CREATE INDEX index name
ON table_name(column_name(length));

21. 什么情况下应不建或者少建索引?

在以下情况下，你可能不需要或应少建索引：1) 表数据量小，扫描整个表可能更快；2) 表的写操作频繁，索引维护成本高；3) 索引列的基数低（如性别），索引效果不佳；4) 频繁批量数据加载，索引维护增加开销；5) 临时表或短生命周期表，创建索引成本高；6) 查询模式不稳定，创建索引效果不确定。总之，索引应该根据实际需求和数据特征来决定。

22. MySQL常见索引失效的情况?

详见「MySQL索引补充」

在这说的不够明确，而且当时也不理解为什么，我在这篇文章中说的比较明确，小林真是太牛了🐮

在 MySQL 中，索引可能会失效，导致查询变慢。一些常见的情况包括在索引列上使用函数或表达式、数据类型不匹配导致隐式类型转换、使用 OR 条件时、前导模糊查询时、使用不等于操作符时、使用 IS NULL 或 IS NOT NULL、在复合索引中先用范围条件后再用等值条件、不满足最左前缀原则、负向查询以及数据分布不均匀。了解这些情况有助于优化你的查询性能。

补充

• 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效
• 当我们在查询条件中对索引列使用函数，就会导致索引失效。
• 当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。
• MySQL在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生 隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
• 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
• 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

23. 唯一索引比普通索引快吗?

唯一索引和普通索引在查询上的表现差不多，因为它们都用相似的方式来快速找到数据。但在插入和更新时，唯一索引会多一步检查，以确保新数据不会和已有的数据重复，这样会稍微慢一点。普通索引不需要这个检查，所以插入和更新会更快。删除数据对这两种索引的影响差不多，因为删除只是从索引中移除数据而已。

24. A,B,C三个字段组成联合索引, AB \ AC \ BC 三种情况下查询是否能命中索引?

对于一个联合索引(A, B, C)，查询是否能利用索引取决于查询条件是怎么写的。

1. 如果查询条件是 (A, B)，这个条件是从最左边开始的，所以能命中索引。
2. 如果查询条件是 (A, C)，虽然 A 能用索引，但因为跳过了 B，所以 C 的部分就不能用索引了，只能部分命中。
3. 如果查询条件是 (B, C)，这个查询没有从最左边的 A 开始，所以不能利用这个联合索引。
4. 如果查询条件只有 A，那也能命中索引，因为 A 是最左边的字段。
5. 如果查询条件只有 B，同样不能命中索引，因为它没有从最左边的 A 开始。
6. 如果查询条件是 (A, B, C)，这个条件包含了所有的字段，所以能完全利用索引。

25. B+树索引和哈希索引的区别?

B+树索引和哈希索引是数据库中两种常见的索引类型，它们在结构、适用场景和性能上有很大的不同。

B+树索引就像一个排序好的树。它把数据存储在树的叶子节点上，内部节点只是存储了键值和指向其他节点的指针。B+树的特点是它的数据是有序的，这让它在做范围查询（比如查找某个范围内的数据）时特别有用。它支持等值查询和范围查询，并且在插入和删除时会自动调整保持平衡。这种索引也比较“磁盘友好”，因为它的结构通常和磁盘页的大小匹配，所以减少了磁盘的读写操作。用B+树索引的场景包括需要频繁做范围查询、排序操作或者涉及多个字段的查询。

哈希索引则是基于哈希表的。它通过一个哈希函数把键值转换成哈希表中的位置，每个位置上存储了指向实际数据的指针。哈希索引的特点是数据是无序的，不适合做范围查询，但在做等值查询（比如查找具体的某个值）时效率非常高，查询速度几乎是瞬时的。哈希索引主要适合频繁的等值查询，不适合范围查询和排序操作。它通常存储在内存中，这样可以更快地访问数据。

26. 哈希索引的优势及不适用的场景?

哈希索引有几个明显的优点。首先，它处理等值查询（像 = 或 IN）非常快，因为哈希函数直接将数据映射到特定位置，使查找时间几乎是瞬时的。其次，插入和删除操作简单，不需要维护平衡，只需计算哈希值就能完成。哈希索引通常存储在内存中，访问速度快，非常适合需要快速访问数据的场景。设计得当的哈希函数可以减少冲突，提高效率。

不过，哈希索引也有一些不足之处。它不支持范围查询，比如 BETWEEN 或 >，因为数据在哈希表中无序，也无法处理排序操作。部分匹配查询（如 LIKE 'abc%'）也不适合使用哈希索引，因为无法进行前缀或模糊匹配。此外，哈希索引不适合多字段的组合查询，主要适用于单字段查询。处理哈希冲突也可能带来额外开销，而哈希表通常需要较大的内存，如果内存不足，可能会影响性能。

27. B树 和 B+树的区别?

B树和B+树是两种常见的索引结构，各有优缺点。

B树的每个节点既存储数据也存储索引信息，这使得它在范围查询时效率较低，因为数据分散在多个节点中，需要遍历多个节点来获取完整的数据。B树的内节点和叶节点结构复杂，叶节点不一定在同一层，导致树的高度可能不均匀。

相比之下，B+树将所有实际数据存储在叶节点中，内节点只存储索引键和指针，这样使得结构更简单。叶节点通过链表相连，这使得范围查询和顺序访问变得非常高效。B+树的叶节点都在同一层，树的高度均匀，内存使用也更加集中。

总的来说，B树适合需要频繁插入和删除操作的场景，因为它在节点上直接进行操作，而B+树则适合需要高效范围查询和顺序访问的场景，由于其叶节点的链表结构，使得这些操作更为高效。

B+ 树的特性是什么

所有叶子节点都在同一层 : 这是B+树的一个重要特性，确保了所有数据项的检索都具有相同的 I/O 延迟，提高了搜索效率。每个叶子节点都包含指向相邻叶子节点的指针形成一个链表，由于叶子节点之间的链接，B+树非常适合进行 范围查询和排序扫描。可以沿着叶子节点的链表顺序访问数据，而无需进行多次随机访问。

非叶子节点存储键值 : 非叶子节点仅存储键值和指向子节点的指针，不包含数据记录。这些键值用于指导搜索路径，帮助快速定位到正确的叶子节点。并且，由于非叶子节点只存放键值，当数据量比较大时，相对于B树，B+树的层高更少，查找效率也就更高。

叶子节点存储数据记录 : 与B树不同，B+树的叶子节点存储实际的数据记录或指向数据记录的指针。这意味着每次搜索都会到达叶子节点才能找到所需数据。

自平衡(多了解) : B+树在插入、删除和更新操作后会自动重新平衡，确保树的高度保持相对稳定，从而保持良好的搜索性能。每个节点最多可以有M个子节点，最少可以有ceil(M/2)个子节点(除了根节点)，这里的M是树的阶数。

28. 为什么说 B+树 比 B树更适合实际应用中作为数据库索引?

B树和B+树在许多方面都存在差异。

B树的每个节点都存储数据，因此在进行范围查询时需要遍历多个节点，效率较低。相对来说，B+树将所有数据存储在叶节点中，并通过链表将这些叶节点连接起来，这使得范围查询时只需遍历链表，查询效率更高。

B树的数据分布在所有节点中，导致内存使用较为分散，并且需要更多的磁盘I/O操作。相比之下，B+树的内节点只存储索引数据，实际数据集中在叶节点上，这样可以更高效地利用内存，减少磁盘I/O操作。

此外，B树的叶节点可能不在同一层，导致树的高度不均匀，影响查询速度。而B+树的所有叶节点都在同一层，使得树的高度更均匀，查询速度更快。

插入和删除操作方面，B树可能会影响整个树的平衡，操作较复杂；而B+树的操作主要集中在叶节点，对内节点影响较小，维护起来更简单。

最后，B树由于数据存储在所有节点中，空间利用率较低，而B+树的内节点只存储索引，空间利用率更高，整体结构也更简单，因此B+树在实现和维护上更为方便。

补充

B+树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下相比存储既存索引又存记录的 B树，B+树的非叶子节点可以存放更多的索引，因此 B+树可以比 B 树更「矮胖」，也就是层数更小，查询底层节点的磁盘 I/O 次数会更少。（B树虽然从平均时间代价来看会比B+树稍快一些，因为他可能从非叶子节点直接获取到了结果，但是这也是一种缺陷，说明B树查找节点时的「波动性」比较大）

B+ 树有大量的冗余节点(所有非叶子节点都是冗余索引)，这些冗余索引让 B+ 树在插入（插入时最多只涉及树的一条路径且可以自平衡，不需要复杂的自旋操作）、删除的效率都更高，比如删除根节点的时候，不会像B树那样会发生复杂的树形结构的变化（B树没有冗余节点，删除节点可能导致复杂的树形结构变化）

B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而B树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d表示节点允许的最大子节点个数为 d个。在实际的应用当中，d值是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3~4层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据。而二又树的每个父节点的儿子节点个数只能是2个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二又树检索到目标数据所经历的磁盘 I/O 次数要更多。（说明矮胖）

存在大量范围检索的场景，适合使用 B+树，比如数据库。而对于大量的单个索引查询的场景，可以考虑B树，比如nosql的MongoDB。

为什么不用跳表

B+树的高度在3层时存储的数据可能已达千万级别，但对于跳表而言同样去维护千万的数据量那么所造成的跳表层数过高而导致的磁盘 I/O 次数增多，也就是使用B+树在存储同样的数据下磁盘 I/O 次数更少。

1. MySQL如何做分库分表

MySQL 分库分表是为了应对数据量过大导致的性能问题。垂直分表是将一个大表按字段拆分，适合不同业务的数据独立存储，查询性能有所提升。水平分表则是按数据的某个字段值分片，把数据分散到多个表中，能解决大数据量问题。为了进一步提升性能，还可以同时进行分库和分表，将数据分散到多个库和表里。这些方法可以提升系统处理能力，但也增加了跨表查询和数据路由的复杂性。

补充

分库 是一种水平扩展数据库的技术，将数据根据一定规则划分到多个独立的数据库中。每个数据库只负责存储部分数据，实现了数据的拆分和分布式存储。分库主要是为了解决并发连接过多，单机 mysql扛不住的问题。

分表 指的是将单个数据库中的表拆分成多个表，每个表只负责存储一部分数据。这种数据的垂直划分能够提高查询效率，减轻单个表的压力。分表主要是为了解决单表数据量太大，导致查询性能下降的问题。

• 垂直分库 : 一般来说按照业务和功能的维度进行拆分，将不同业务数据分别放到不同的数据库中，核心理念 专库专用。按业务类型对数据分离，剥离为多个数据库，像订单、支付、会员、积分相关等表放在对应的订单库、支付库、会员库、积分库。垂直分库把一个库的压力分摊到多个库，提升了一些数据库性能，但并没有解决由于单表数据量过大导致的性能问题，所以就需要配合后边的分表来解决。
• 垂直分表 : 针对业务上字段比较多的大表进行的，一般是把业务宽表中比较独立的字段，或者不常用的字段拆分到单独的数据表中，是一种大表拆小表的模式。数据库它是以行为单位将教据加载到内存中这样拆分以后核心表大多是访问频率较高的字段，而且字段长度也都较短，因而可以加载更多数据到内存中，减少磁盘10，增加索引查询的命中率，进一步提升数据库性能。
• 水平分库 : 是把同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上，以此实现水平扩展，是一种常见的提升数据库性能的方式。这种方案往往能解决单库存储量及性能瓶颈问题但由于同一个表被分配在不同的数据库中，数据的访问需要额外的路由工作，因此系统的复杂度也被提升了。
• 水平分表 : 是在同一个数据库内，把一张大数据量的表按一定规则，切分成多个结构完全相同表，而每个表只存原表的一部分数据。水平分表尽管拆分了表，但子表都还是在同一个数据库实例中，只是解决了单一表数据量过大的问题，并没有将拆分后的表分散到不同的机器上，还在竞争同一个物理机的CPU、内存、网络 IO 等。要想进一步提升性能，就需要将拆分后的表分散到不同的教据库中，达到分布式的效果。

2. MySQL主从同步原理

MySQL主从同步是一种数据复制机制，主服务器负责记录所有数据的修改并将它们写入二进制日志。然后，从服务器会连接到主服务器，读取这些日志并将它们应用到自己的数据库中，这样主从数据库就能保持一致。整个过程包括主服务器生成二进制日志、从服务器接收并保存到中继日志，再由SQL线程执行这些变更。这种同步可以分为异步、半同步和组复制，不同模式有不同的性能和数据一致性保障。

3. MySQL主从同步延迟的原因和解决方法

MySQL主从同步延迟是指从服务器跟不上主服务器的更新速度，可能由主服务器负载过高、从服务器性能瓶颈、网络延迟、大事务以及锁争用等因素引起。解决办法包括优化主服务器和从服务器的性能、提高网络带宽、拆分大事务、调整并行复制配置、使用半同步复制等措施。通过这些方法，可以有效减少延迟，确保数据同步及时。

4. MySQL的全复制、半复制、异步复制都是什么?

MySQL有三种复制模式：异步复制、半同步复制和全同步复制。异步复制是默认模式，主服务器提交事务后不需要等从服务器的确认，性能很高但可能会有延迟。半同步复制会等待至少一个从服务器确认收到并写入日志后才完成事务，平衡了性能和数据一致性。全同步复制则等所有从服务器确认数据后才完成事务，数据一致性最强，但性能和延迟较差，适合对数据一致性要求极高的场景。

5. drop,delete与truncate的区别?

DROP、DELETE和TRUNCATE是处理数据库中数据和表结构的不同方式。DROP用于完全删除表或数据库，删除后无法恢复，性能较快但不可回滚。DELETE用于删除表中符合条件的行，可以回滚并触发DELETE触发器，但性能较慢。TRUNCATE则是快速删除表中所有行，但保留表结构，不会触发触发器，通常也不可回滚。整体上，DROP和TRUNCATE通常比DELETE更快。

6. 创建数据库表要注意什么?

创建数据库表时需要注意几个重要方面。首先，选择合适的数据类型以节省空间并提高查询效率。其次，定义主键以确保数据唯一性，每个表最好有一个不变的主键。然后，根据查询需求合理创建索引，但不要过多，避免影响写操作性能。表设计要遵循规范化原则，减少数据冗余，同时合理使用外键和约束来保持数据完整性。命名要清晰有意义，考虑大型表时可以使用分区来提高性能。选择合适的存储引擎，并为列设置合理的默认值。最后，保持良好的文档和注释，以提高可维护性。设计时还需要考虑未来的性能和扩展需求。

7. mvcc是什么?

MVCC（多版本并发控制）是一种管理数据库并发访问的方法，它通过维护数据的多个版本来提高并发性能和数据一致性。每次数据行被修改时，都会创建一个新版本，同时保留旧版本。每个事务在读取数据时只会看到它开始时的数据库状态，而不会受到其他事务修改的影响。MVCC的优势包括提高并发性、减少锁争用和提供一致性视图。然而，它也带来了一些挑战，比如存储开销、实现复杂性和写放大问题。

笔者声明

补充——MVCC实现原理（见MySQL事务相关）

8. 什么是当前读与快照读(一致性读)?

当前读和一致性读是两种不同的读取数据的方式。当前读会读取最新的已经提交的数据版本，并且通常会加锁，确保在读取期间数据不会被其他事务修改。它适用于需要获取最新数据并且可能会修改数据的场景。比如，执行 SELECT ... FOR UPDATE 或者 UPDATE 操作时就是当前读。而一致性读会获取事务开始时的数据快照，确保读取的数据在事务期间保持一致，不受其他并发事务影响。它适用于只读操作，比如普通的 SELECT 查询，这种方式通过MVCC机制实现，不会加锁。

9. MVCC解决了什么问题?

MVCC（多版本并发控制）通过维护数据的多个版本，允许多个事务同时读取和写入数据，而不互相阻塞。这样可以提高系统的并发性，减少锁争用，避免读写冲突，提升读性能，还能实现更高的隔离级别。总的来说，MVCC 能让数据库在高并发环境下运行得更平稳，减少死锁问题，同时提供一致的数据视图。

10. Mysql的常用函数有哪些?

MySQL中有很多常用的函数，比如字符串处理函数如CONCAT()和SUBSTRING()，日期时间函数如NOW()和DATE_FORMAT()，以及数学函数如ROUND()和FLOOR()。还有一些常用的聚合函数，如SUM()、AVG()和COUNT()，用于统计数据的总和、平均值和记录数。它们帮助你在数据库查询时进行各种数据处理和分析。

补充：

• 字符串函数

  CONCAT(str1, str2,..) : 连接多个字符串，返回一个合并后的字符串。

  SELECT CONCAT('Hello',' ','World')As Greeting;

  LENGTH(str) : 返回字符串的长度(字符数)

  SELECT LENGTH('Hello')As stringLength;

  SUBSTRING(str, pos, len) : 从指定位置开始，截取指定长度的子字符串。

  SELECT SUBSTRING('Hello world',1,5)As Substr;

  REPLACE(str, from_str, to_str) : 将字符串中的某部分替换为另一个字符串。

  SELECT REPLACE('Hello World',"World', 'My5OL')A5 Replacedstr;

• 数值函数

  ABS(num) : 返回数字的绝对值

  SELECT ABS(-10) AS AbsoluteValue;

  POWER(num, exponent) : 返回指定数字的指定幂次方

  SELECT POWER(2, 3) AS PowerValue;

• 日期和时间函数

  NOW() : 返回当前日期和时间

  SELECT NOW()As CurrentDateTime;

  CURDATE() : 返回当前日期。

  SELECT CURDATE()As currentDate;

• 聚合函数

  COUNT(column) : 计算指定列中的非NULL值的个数。

  SELECT COUNT(*)AS RowCount FROM my table;

  SUM(column) : 计算指定列的总和。

  SELECT SUM(price)As TotalPrice FRoM orders;

  AVG(column) : 计算指定列的平均值

  SELECT AVG(price)As AveragePrice FRoM orders;

  MAX(column) : 返回指定列的最大值。

  SELECT MAX(price)As MaxPrice FROM orders;

  MIN(column) : 返回指定列的最小值

  SELECT MIN(price)As MinPrice FRoM orders;

11. Mysql的常用数据类型?

MySQL提供了多种数据类型来处理不同的数据需求。数值类型包括整数类型（如TINYINT到BIGINT）和浮点数类型（如FLOAT和DOUBLE），适用于各种数值数据。字符串类型包括CHAR、VARCHAR、TEXT等，处理不同长度的文本数据。二进制类型如BLOB和VARBINARY用于存储二进制数据。日期和时间类型如DATE、TIME、DATETIME等，用于处理日期和时间信息。JSON类型用于存储JSON格式的数据。枚举和集合类型（ENUM和SET）则允许存储预定义的多个值。还有一些特殊类型，如BOOLEAN表示布尔值，SERIAL用于自动递增的整数。

12. Mysql的char和varchar的区别?

CHAR和VARCHAR都是用来存储字符串的，但它们有些不同。CHAR是固定长度的，不管你存多少字符，都会占用定义的长度，比如CHAR(10)总是占用10个字符的空间，即使你只存了3个字符。VARCHAR则是可变长度的，只有实际存储的字符才占用空间，加上一个额外的字节来记录长度，因此对于变化大的字符串更节省空间。

CHAR在 检索和比较 时速度较快，适合长度固定的字段，而VARCHAR更适合 长度不固定 的字段，但可能在性能上稍逊色。

CHAR 和 VARCHAR 的区别？

（1）char：

定长字符串，长度是固定时，长度不够的时候，会采取右补空格的方式，空间换时间后续修改不会造成页分裂的问题

char的长度范围是0-255

应用：存储固定长度：存储身份证号、订单号、国家编码、MD5值

问题：可能删除列末尾的空格信息 char(5) "abc"和"abc " 存储后会变得一样

（2）varchar：

可变长字符串，长度是可变的，只会存储实际的字符串内容，不会填充空格，时间换空间

varchar的长度范围是0-65535（MySQL 5.0.3之后的版本）

应用：存储可变长度：存储产品描述、用户地址、用户名称

问题：会导致分裂页和产生碎片；varchar会额外需要1到2个字节存储长度信息

varchar是变长的，在UPDATE时可能使行变得比原来更长，原来的位置无法扩展出新的空间出来，无法覆盖存放到原来的位置上。此时MySQL就会使用页分裂的方法扩展字段变长的空间。

列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节

varchar在内存中操作是按最长的方式在内存中进行操作的：排序，varcahr(100)是按100这个长度来进行

13. MySQL中int(1)和int(10)的区别?

INT(1)和INT(10)的区别在于显示宽度，而不是存储大小或数值范围。INT类型的数值范围是固定的，不论是INT(1)还是INT(10)，都能存储相同的范围。显示宽度只在你使用ZEROFILL属性时才会有影响，这时MySQL会将数字填充到指定的宽度。例如，INT(5) ZEROFILL会将值42显示为00042。存储大小始终是4个字节，显示宽度对存储没有影响。

14. MySQL主键自增达到最大值会发生什么?你会怎么调整?

当主键自增达到最大值时，插入新记录会报错，比如ERROR 1062 (23000): Duplicate entry。为了应对这种情况，你可以采取几种措施。首先，可以更改主键的数据类型，比如将INT改为BIGINT，这样可以支持更大的值。如果当前使用的是有符号类型，考虑切换到无符号类型以获得更大的正整数范围。其次，可以尝试重置自增值，但这要求确保没有重复的主键值，并可能需要删除或归档现有数据。对于大数据量的表，考虑将旧数据归档到其他表中。复用删除的主键值虽然可行，但通常不推荐，因为可能导致冲突。预防措施包括定期监控自增值，并在接近最大值时采取行动，和在设计数据库时合理规划主键的数据类型。

15. MySQL的blob和text有什么区别?

在MySQL中，BLOB和TEXT都是用于存储大量数据的字段类型，但它们有一些关键的区别。BLOB用于存储二进制数据，如图片、音频或视频，它不会进行字符集转换。而TEXT则用于存储大文本数据，如文章或日志，存储时会受到字符集和排序规则的影响。两者都有四种类型，分别支持不同的数据大小，从最大255字节到4GB。BLOB不使用字符集和排序规则，适合存储二进制数据，而TEXT使用字符集和排序规则，适合存储文本数据。索引方面，BLOB和TEXT字段不能被索引，除非指定前缀长度。由于数据可能非常大，它们的性能可能会受到影响。在存储和检索时，BLOB不会进行字符集转换，而TEXT会进行字符集转换。

16. Mysql中什么是表分区?

表分区就是把一个大表的数据分成多个小的独立部分，每个部分可以独立存储。这种方式可以提高查询效率、简化管理，并且优化存储资源的使用。MySQL支持几种分区方式，比如按范围、列表、哈希或键分区。范围分区适合按日期分区，列表分区适合按特定值分区，哈希分区则适合均匀分布数据，键分区则使用MySQL自带的哈希函数。通过这种方式，可以更高效地处理大量数据。

17. 表分区与分表的区别?

表分区和分表虽然都用于管理大数据，但它们的实现方式和适用场景不同。表分区是把一个表的数据按照规则分成多个小部分，但这些分区在逻辑上仍然是一个表，由数据库内部处理，用户不需要关注。分表则是把一个表拆分成多个独立的表，这些表在逻辑上是独立的，应用程序需要处理数据如何分布。分区主要用于单个数据库的性能优化和数据管理，而分表则用于跨多个数据库的扩展和负载均衡。

18. MySQL什么情况下会产生死锁?

MySQL会在一些特定情况下产生死锁。比如，当两个事务以不同的顺序锁定相同的资源，或者在间隙锁下进行范围查询时，就可能导致死锁。此外，高并发下插入自增列、外键约束、锁升级以及混合不同类型的锁，也都可能引发死锁。如果事务长时间持有锁或并发事务过多，都容易导致死锁。为了解决这些问题，可以利用自动检测和回滚功能、合理设计事务、统一锁定顺序、减少并发事务等方法来预防和处理死锁。

19. Mysql死锁常见解决方案?

MySQL中的死锁是指多个事务相互等待对方持有的锁，导致无法继续执行。为了避免死锁，可以通过优化SQL语句减少锁持有时间，统一事务访问顺序来避免循环等待，使用适当的隔离级别和锁机制来减少锁冲突。此外，合理使用索引、启用和分析死锁日志、设置自动重试机制、调整锁等待超时、使用分区表和行级锁，以及避免热点更新，都能有效降低死锁发生的几率。

20. 为什么要使用视图?什么是视图?

视图是数据库中的虚拟表，用于简化复杂查询和提高数据安全性。它将一个或多个表的数据封装成一个逻辑表，使得用户只需访问视图而不必关心底层表的复杂性。视图能够提供数据抽象、实现数据重用，并在某些情况下优化查询性能，但需要注意视图的更新、性能影响以及依赖管理等问题。

21. 什么是存储过程?有哪些优缺点?

存储过程是数据库中的一组预编译的 SQL 语句和逻辑控制结构，它能提高性能、简化开发、增强安全性，但也有调试困难、可移植性差、性能瓶颈和维护复杂等缺点。存储过程能减少网络流量、提高代码重用率，但在不同数据库间移植难度大，且在高并发环境下可能导致性能问题。

22. union和union all区别?

UNION 和 UNION ALL 都是用来合并 SQL 查询结果的。UNION 会去除重复的行，因为它会额外排序来找出重复行，这样可能会稍微慢一些。UNION ALL 保留所有结果，包括重复的行，所以通常性能更好，尤其是在数据量很大的时候。

返回结果

union all是直接连接，取到得是所有值，记录可能有重复；

union 是取唯一值，记录没有重复。

排序

union all只是简单的将两个结果合并后就返回。【直接合并】

union将会按照字段的顺序进行全量排序；【因为要去重，这个过程中隐含了排序】

效率

union all 要比union快很多，所以，如果可以确认合并的两个结果集中,不包含重复数据且不需要排序时的话，那么就使用union all。

23. MySQL的binlog是什么?

MySQL 的二进制日志（binlog）记录了对数据库进行的所有更改操作。它的主要功能包括在发生数据丢失时恢复数据，通过复制机制将主服务器的更改同步到从服务器，以及审计和分析数据库上的操作。二进制日志由多个文件组成，MySQL 会自动生成和轮换这些日志文件。

24. MySQL的redolog是什么?

MySQL 的重做日志（redo log）是 InnoDB 存储引擎的一个重要组件，用于确保数据的持久性和一致性。它会记录所有对数据库的修改操作，并在实际写入数据文件之前先写入重做日志。这样，如果数据库崩溃或意外关机，重做日志可以帮助恢复未完成的事务，提高性能，并确保数据一致性。

25. MySQL除了binlog和redolog，还有其他的什么log吗

MySQL 除了 binlog 和 redo log，还有几个其他重要的日志：错误日志记录服务器的错误和重要事件，查询日志记录所有的 SQL 语句，慢查询日志记录执行时间长的查询，中继日志用于复制环境中同步数据，而撤销日志和表空间日志则分别用于事务回滚和表空间管理。这些日志对维护和优化 MySQL 数据库非常重要。

---

以下是根据小林的mysql做的一波优化

1. MySQL如何避免重复插入数据?

方式一:使用UNIQUE约束

在表的相关列上添加UNIQUE约束，确保每个值在该列中唯一。例如:

CREATE TABLE users(
    id INT PRIMARY KEY AUTO INCREMENT,emai1 VARCHAR(255)UNIQUE,
    name VARCHAR(255)
);

如果尝试插入重复的email，MySQL会返回错误，

方式二:使用INSERT .. ON DUPLICATE KEY UPDATE

这种语句允许在插入记录时处理重复键的情况。如果插入的记录与现有记录冲突，可以选择更新现有记录:

INSERT INTO users(email, name)
VALUES('example@example.com','John Doe')
ON DUPLICATE KEY UPDATE name = VALUES(name);

方式三:使用INSERT IGNORE

该语句会在插入记录时忽略那些因重复键而导致的插入错误。例如:

INSERT IGNORE INTo users(email,name)
VALUE5('example@example.com','John Doe');

如果email已经存在，这条插入语句将被忽略而不会返回错误。

如何选择？

取决于具体的需求:

• 如果需要保证全局唯一性，使用UNIQUE约束是最佳做法。
• 如果需要插入和更新结合可以使用ONDUPLICATEKEY UPDATE。
• 对于快速忽略重复插入，INSERT IGNORE 是合适的选择

2. MySQL的关键字in和exist

在MySQL中， IN 和 EXISTS 都是用来处理子査询的关键词，但它们在功能、性能和使用场景上有各自的特点和区别。

IN关键字

IN 用于检査左边的表达式是否存在于右边的 列表或子査询的结果集 中。如果存在，则 IN 返回 TRUE，否则返回 FALSE

语法结构:

SELECT column name(s)
FROM table name
WHERE column name IN(value1,value2,...);

或

SELECT column name(s)
FROM table name
WHERE column name IN (SELECT column name FROM another table WHERE condition);

例子:

SELECT * FROM Customers
WHERE Country IN('Germany','France');

EXISTS关键字

用于判断子查询是否至少能返回一行数据。它不关心子查询返回什么数据，只关心是否有结果。如果子查询EXISTS有结果，则EXISTS 返回 TRUE，否则返回 FALSE。

语法结构:

SELECT column name(s)
FROM table name
WHERE EXISTS(SELECT column name FROManother table WHERE condition);

例子:

SELECT * FROM Customers
WHERE EXISTS(SELECT 1 FROM Orders WHERE Orders.CustomerID= Customers.customerID);

区别与选择:

• 性能差异:在很多情况下，EXISTS 的性能优于 IN，特别是当子查询的表很大时。这是因为EXISTS一旦找到匹配项就会立即停止查询，而 IN 可能会扫描整个子查询结果集。
• 使用场景:如果子查询结果集较小且不频繁变动， IN可能更直观易懂。而当子査询涉及外部查询的每一行判断，并且子查询的效率较高时，EXISTS 更为合适。
• NULL值处理: IN能够正确处理子査询中包含NULL值的情况，而 EXISTS 不受子查询结果中NULL值的影响，因为它关注的是行的存在性，而不是具体值。

3. SQL 查询语句的执行顺序是怎么样的？

所有的查询语句都是从FROM开始执行，在执行过程中，每个步都会生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入，最后一个步要产生的虚拟表即为输出结果。

(9)SELECT
(10)DISTINCT<column>,
(6)AGG_FUNC <column> or <expression>,...
(1)FROM <left_table>
	(3)<join type> J0IN <right_table>
	(2)0N <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(7)WITH {CUBE|ROLLUP}
(8)HAVING <having_condtion>
(11)ORDER BY <order_by_list>
(12)LIMIT <limit_number>;

4. 执行一条 SQL 请求的过程是什么？

可以看到， MySQL的架构共分为两层:Server 层和存储引擎层

Server 层负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数(如日期、时间、数学和加密函数等)和所有跨存储引擎的功能(如存储过程、触发器、视图等。)都在 Server 层实现。

存储引擎层负责数据的存储和提取。支持InnoDB、MyISAM、Memory等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MVSQL5.5 版本开始，InnoDB 成为了MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

01连接器 : 建立连接，管理连接、校验用户身份

• 连接过程需要经过TCP三次握手（MySQL基于TCP协议进行传输，如果MySQL服务没有启动，会报错无法连接）
• TCP建立连接以后，开始验证用户名和密码（验证失败就会受到Aceesss denied for user的错误，结束执行）
• 验证成功以后，连接器获得该用户的权限然后保存（之后的操作都基于连接开始时读取到的权限进行操作，这也就意味着如果该用户的连接没有中断管理员提升了该用户的权限，该用户也必须重新连接才能使用最新的权限）

如何查看MySQL服务被多少个客户端连接？

执行show processlist命令

比如上图的显示结果，共有两个用户名为 root的用户连接了 MySQL 服务，其中id为6的用户的 Command 列的状态为 sleep ，这意味着该用户连接完 MSQL服务就没有再执行过任何命令，也就是说这是一个空闲的连接，并且空闲的时长是 736 秒(Time 列)。

空闲连接的最大时长

MySQL定义了空闲连接的最大空闲时长，wait_timeout参数控制，默认时间是8小时，如果空闲时间超过了这个时间连接器就会自动断开，也可以使用kill connection + id这个指令手动断开，处于空闲状态的连接被服务端主动断开以后客户端不会立马知道，等待客户端发起下一次请求就会报错失去连接

最大连接数

MySQL用max_connections参数控制最大连接数，超过最大连接数就会拒接接下来的连接请求，报错提示Too many connection

MySQL的长连接和短连接

MySQL也有短连接和长连接的概念，如短连接为连接MySQL服务（TCP三次握手）->执行SQL->断开MySQL服务（TCP四次挥手）；而长连接在建立连接和断开连接期间可以执行多次SQL操作。一般推荐长连接，但是使用长连接后可能会占用内存增多，因为 MSQL在执行查询过程中临时使用内存管理连接对象，这些连接对象资源只有在连接断开时才会释放。如果长连接累计很多，将导致 MySQL 服务占用内存太大，有可能会被系统强制杀掉，这样会发生 MySQL 服务异常重启的现象。

解决长连接占用内存的问题：定期断开长连接、客户端主动重置连接（使用实现了mysql_reset_connection()函数的接口，不需要重连和重新做权限验证，将连接恢复到刚刚创建完成时的状态）

02查询缓存（MySQL8.0之后的版本将这一步删掉）

连接器得工作完成后，客户端就可以向 MySQL 服务发送 SQL语句了，MySQL 服务收到 SQL语句后，就会解析出SQL语句的第一个字段，看看是什么类型的语句。

如果 SQL是查询语句(select语句)，MVSQL就会先去查询缓存(Query Cache )里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key为 SQL查询语句，value 为 SQL 语句查询的结果。

如果查询的语句命中查询缓存，那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。

但是其实查询缓存挺鸡肋的。对于更新比较频繁的表，查询缓存的命中率很低的，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。如果刚缓存了一个查询结果很大的数据，还没被使用的时候，刚好这个表有更新操作，查询缓冲就被清空了相当于缓存了个寂寞。所以后续版本将这一步删掉了

03解析SQL

通过解析器对 SQL语句进行词法分析、语法分析，然后构建语法树（判断是否满足语法要求，没毛病就会构建语法树），方便后续模块读取表名、字段、语句类型。如果输入的语法不正确就会报错，但是表不存在或者字段不存在并不是解析器的任务，解析器只负责检查语法和构建语法树，并不会负责查表或者字段是否存在

04执行SQL

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

比如执行select * from test;这条语句，但是test表并不存在，MySQL就会在执行SQL查询语句的prepare阶段报错

所以强调一下，表不存在或者字段不存在这一步骤不是在解析阶段做的事情

优化阶段 : 基于查询成本的考虑，选择查询成本最小的执行计划

结合explain就可以显示SQL的执行计划，总之MySQL结合执行计划中所有成本消耗，选择出成本最小的执行计划来执行

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

这里提一下索引下推

索引下推能够减少二级索引在查询时的回表操作，提高査询的效率，因为它将 Server 层部分负责的事情，交给存储引擎层去处理了（普通的全表扫描的时候就是这样，每次Server层从存储引擎层中获取一行记录，然后这个记录就在Server层中判断条件，符合就发送给客户端，不符合就跳过，但是「索引下推」是Server先向存储引擎获取到一条记录，这条记录暂时还没发往Server层直接判断是否符合条件，符合的才发给Server层然后直接发给客户端，所以省去了很多）。

举一个具体的例子有一张用户表并对 age 和 reward 字段建立了联合索引(age,reward)

执行select * from t_user where age > 20 and reward = 100000

说明

联合索引当遇到范围查询(>，<)就会停止匹配，也就是 age 可以用到联合索引，但是 reward 不能

不使用索引下推(MySQL 5.6 之前的版本)时，执行器与存储引擎的执行流程是这样的:

1. Server 层首先调用存储引擎的接口定位到满足査询条件的第一条二级索引记录，也就是定位到 age>20 的第一条记录;
2. 存储引擎根据二级索引的 B+ 树快速定位到这条记录后，获取主键值，然后进行回表操作，将完整的记录返回给Server层
3. Server 层判断该记录的 reward 是否等于 100000，如果成立则将其发送给客户端;否则跳过该记录;
4. 接着，继续向存储引擎索要下一条记录，存储引擎在二级索引定位到记录后，获取主键值，然后回表操作，将完整的记录返回给 Server 层;
5. 如此往复，直到存储引擎把表中的所有记录读完。

因此没有索引下推的时候，每查询到一条二级索引记录，都要进行回表操作，然后将记录返回给 Server，接着 Server 再判断该记录的 reward 是否等于 100000。

而使用索引下推后，判断记录的 reward 是否等于 100000 的工作交给了存储引擎层，过程如下:

1. Server 层首先调用存储引擎的接口定位到满足査询条件的第一条二级索引记录，也就是定位到 age>20 的第一条记录;
2. 存储引擎定位到二级索引后，先不执行回表操作，而是先判断一下该索引中包含的列(reward列)的条件(reward 是否等于 100000)是否成立。如果条件不成立，则直接跳过该二级索引。如果成立，则执行回表操作，将完成记录返回给 Server 层
3. Server 层判断其他的査询条件(本次査询没有其他条件)是否成立，如果成立则将其发送给客户端;否则跳过该记录，然后向存储引警索要下一条记录，
4. 如此往复，直到存储引擎把表中的所有记录读完。

使用了索引下推后，虽然 reward 列无法使用到联合索引，但是因为它包含在联合索引(age，reward)里，所以直接在存储引擎过滤出满足 reward =100000 的记录后，才去执行回表操作获取整个记录。相比于没有使用索引下推，节省了很多回表操作。 当发现执行计划里的 Extr 部分显示了Using index condition，说明使用了索引下推

5. 数据管理里，数据文件大体分成几种数据文件？

我们每创建-个 database(数据库)都会在 /var/lib/mysql/ 日录里面创建一个以database 为名的目录，然后保存表结构和表数据的文件都会存放在这个目录里。

比如有一个名为 my_test 的 database，该 database 里有一张名为 t_order 数据库表。

就会发现目录结构是

[root@gf ~]#ls /var/lib/mysql/my test
db.opt
t order.frm
t order.ibd

可以看到，共有三个文件，这三个文件分别代表着，

• db.opt，用来存储当前数据库的 默认字符集和字符校验规则。
• torder.frm ，t_order 的 表结构 会保存在这个文件。在 MySQL 中建立一张表都会生成一个.frm 文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。
• t_order.ibd，t_order 的 表数据 会保存在这个文件。表数据既可以存在共享表空间文件(文件名:ibdata1)里，也可以存放在独占表空间文件(文件名:表名字.ibd)。这个行为是由参数 innodb_file_per_table 控制的，若设置了参数 innodb_file_per_table 为1，则会将存储的数据、索引等信息单独存储在一个独占表空间，从 MySQL 5.6.6 版本开始，它的默认值就是1了，因此从这个版本之后，MySQL 中每一张表的数据都存放在一个独立的 .ibd 文件。

6. 为什么“性别”这个字段不建议使用索引

实际上与索引创建规则之一区分度有关，性别字段假设有100w数据，50w男、50w女，区别度几乎等于0。

区分度的计算方式 : select count(DlSTINCT sex)/count(*) from sys_user

对于性别字段不适合创建索引，是因为 select * 操作，还得进行50w次回表操作，根据主键从聚簇索引中找到其他字段，这一部分开销从上面的测试来说还是比较大的，所以从性能角度来看不建议性别字段加索引，加上索引并不是索引失效，而是回表操作使得变慢的。

既然走索引的查询的成本比全表扫描高，优化器就会选择全表扫描的方向进行查询，这时候建立的性别字段索引就没有启到加快查询的作用，反而还因为创建了索引占用了空间

7. 什么自增ID更快一些，UUID不快吗，它在B+树里面存储是有序的吗?

自增的主键的值是顺序的，所以 Innodb 把每一条记录都存储在一条记录的后面，所以自增 id 更快

• 下一条记录就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费
• 新插入的行一定会在原有的最大数据行下一行，mysql定位和寻址很快，不会为计算新行的位置而做出额外的消耗
• 减少了页分裂和碎片的产生

但是 UUID 不是递增的，MSQL 中索引的数据结构是 B+Tree，这种数据结构的特点是索引树上的节点的数据是有序的，而如果使用 UUID 作为主键，那么每次插入数据时，因为无法保证每次产生的 UUID 有序，所以就会出现新的 UUID 需要插入到索引树的中间去,这样可能会频繁地导致页分裂，使性能下降。

而且，UUID 太占用内存。每个 UUID 由 36 个字符组成，在字符串进行比较时，需要从前往后比较，字符串越长，性能越差。另外字符串越长，占用的内存越大，由于页的大小是固定的，这样一个页上能存放的关键字数量就会越少，这样最终就会导致索引树的高度越大，在索引搜索的时候，发生的磁盘 10 次数越多，性能越差。

8. 索引优化详细讲讲

常见优化索引的方法:

• 前缀索引优化 : 使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值,有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小系引项的大小。

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

• 主键索引最好是自增的 : 如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需(要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。

  如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就 可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为 页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

• 防止索引失效（详细看上面关于「索引失效的常见情况」）

9. mysql的是怎么解决并发问题的?

• 锁机制 : Mysql提供了多种锁机制来保证数据的一致性，包括行级锁、表级锁、页级锁等。通过锁机制，可以在读写操作时对数据进行加锁，确保同时只有一个操作能够访问或修改数据。
• 事务隔离级别 : Mysq!提供了多种事务隔离级别，包括读未提交、读已提交、可重复读和串行化。通过设置合适的事务隔离级别，可以在多个事务并发执行时，控制事务之间的隔离程度，以避免数据不一致的问题。
• MVCC(多版本并发控制):MysqI使用MVCC来管理并发访问，它通过在数据库中保存不同版本的数据来实现不同事务之间的隔离。在读取数据时，Mysql会根据事务的隔离级别来选择合适的数据版本，从而保证数据的一致性。

10. 日志文件分成了几种？

• redo_log 重做日志，是Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复;
• undo_log 回滚日志，是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和MVCC。
• bin_log 二进制日志，是 Server 层生成的日志，主要用于 数据备份和主从复制;
• relay_log 中继日志，用于 主从复制 场景下，slave通过io线程拷贝master的 bin_log 后本地生成的日志
• 慢查询日志，用于记录执行时间过长的sql，需要设置阈值后手动开启

11.COUNT(1)、COUNT(*) 与 COUNT(列名) 的区别详解

MySQL官方文档指出：

"InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference."

在MySQL中，这三种COUNT用法看似相似，但实际上有重要区别：

1. COUNT(1)

• 工作原理：统计结果集中所有行的数量，不管行内容如何
• 特点：
  • 不检查任何列的值，只计算行数
  • 每行都被视为"1"进行计数
  • 性能通常与COUNT(*)相当，有时略优
• 使用场景：当你只需要知道总行数而不关心具体数据时

SELECT COUNT(1) FROM users;  -- 统计users表的总行数

2. COUNT(*)

• 工作原理：统计结果集中所有行的数量，包括NULL值行
• 特点：
  • 统计所有行，不考虑任何列的值
  • 在大多数现代数据库优化器中，COUNT(*)和COUNT(1)性能几乎相同
  • 语义上更明确表示"计算所有行"
• 使用场景：标准方式获取表的总行数

SELECT COUNT(*) FROM orders;  -- 统计orders表的总行数

3. COUNT(列名)

• 工作原理：统计指定列中非NULL值的数量
• 特点：
  • 只计算指定列不为NULL的行
  • 如果列有NULL值，这些行不会被计数
  • 性能上可能略差，因为需要检查列值
• 使用场景：需要知道某列有效值(非NULL)的数量时

SELECT COUNT(email) FROM customers;  -- 统计有email的客户数量

使用建议

1. 当需要总行数时，优先使用COUNT(*)，因为语义最明确
2. 当需要特定列的非NULL值数量时，使用COUNT(列名)
3. 在极少数需要微优化的情况下，可以考虑COUNT(1)

示例对比

-- 假设表中有5行数据，其中一行的name列为NULL
SELECT COUNT(*) FROM table;    -- 返回5
SELECT COUNT(1) FROM table;    -- 返回5
SELECT COUNT(name) FROM table; -- 返回4

12.MySQL 中有哪几种锁

1. 粒度：页锁、行锁、表锁

1. 表锁（Table-level Locking）：
   1. 表锁是对整个表进行锁定，当一个事务获取了表级锁后，其他事务无法同时对该表进行写操作，但可以进行读操作。
   2. 表锁是最粗粒度的锁，会锁定整个表，因此并发访问时可能会出现大量的锁竞争和阻塞。
   3. 表锁适用于对整个表进行操作的场景，如ALTER TABLE、TRUNCATE TABLE等操作。
2. 行锁（Row-level Locking）：
   1. 行锁是对表中的行进行锁定，当一个事务获取了某行的锁后，其他事务无法同时对该行进行写操作，但可以对其他行进行写操作。
   2. 行锁是最细粒度的锁，可以提高并发性能，减少锁竞争和阻塞，但也会增加锁管理的开销。
   3. 行锁适用于对单个行进行操作的场景，如UPDATE、DELETE等操作。
3. 页锁（Page-level Locking）：
   1. 页锁是对表中的页（数据页）进行锁定，每个页通常包含多行数据。页锁介于表锁和行锁之间，可以减少锁的粒度，提高并发性能。
   2. 页锁的粒度介于表锁和行锁之间，适用于部分表级操作和行级操作。

2. 模式：乐观锁、悲观锁

在数据库中，乐观锁和悲观锁是两种不同的并发控制策略，用于处理多个事务同时访问相同数据的情况。

1. 乐观锁：
   1. 乐观锁的基本思想是假设并发访问的事务之间不会发生冲突，因此在读取数据时不会进行加锁，而是在提交更新时检查是否有其他事务对数据进行了修改。
   2. 在MySQL中，实现乐观锁通常通过在表中增加一个版本号（Version）字段或者使用时间戳（Timestamp）字段来标识数据的版本，每次更新数据时都会更新版本号或时间戳。
   3. 当事务A读取数据时，会记录当前的版本号或时间戳；在提交更新时，会比较记录的版本号或时间戳与数据库中的版本号或时间戳是否一致，如果一致则提交更新，否则认为数据已经被其他事务修改，需要进行冲突处理。
2. 悲观锁：
   1. 悲观锁的基本思想是在数据访问过程中，认为其他事务可能会对数据进行修改，因此在读取数据时就会进行加锁操作，以防止其他事务对数据进行修改。
   2. 在MySQL中，实现悲观锁通常通过使用SELECT ... FOR UPDATE语句来锁定数据行，这会在读取数据时对数据行进行排它锁定，阻止其他事务对数据行进行修改。
   3. 当事务A使用SELECT ... FOR UPDATE语句读取数据时，会对数据行进行锁定，直到事务A提交或回滚之前，其他事务无法对同一数据行进行修改。

选择乐观锁还是悲观锁取决于具体的业务场景和性能需求。乐观锁适用于并发度较高、冲突较少的场景，可以减少锁的竞争和提高并发性能；而悲观锁适用于并发度较低、冲突较多的场景，可以确保数据的一致性和完整性。

3. 行锁类别：共享锁、排它锁

在MySQL中，行锁可以分为两种基本类型：共享锁（Shared Lock）和排他锁（Exclusive Lock），也称为共享锁和排它锁。

这两种锁对应了数据库事务的两种基本操作：读取（共享锁）和修改（排它锁）。共享锁允许多个事务同时读取同一行数据，但阻止其他事务对该行数据进行修改；排它锁只允许一个事务对某一行数据进行修改，阻止其他事务读取或修改相同行的数据。【很简单，就是读锁和读锁兼容，但是读锁和写锁互斥；反过来，写锁和任何锁互斥】

在MySQL中，通过使用SELECT ... FOR UPDATE语句可以获取行级排他锁，而通过使用SELECT ... LOCK IN SHARE MODE语句可以获取行级共享锁。这些语句可以在事务中用于控制对数据行的锁定和并发访问。

4. 行锁算法：记录锁、间隙锁、临键锁、插入意向锁

1. 记录锁（Record Lock）：
   1. 记录锁是针对表中的单个数据行进行的锁定，用于保护该行数据不被其他事务修改。当事务对某一行数据进行读取或修改时，会对该行数据进行记录锁定，直到事务结束或提交。
   2. 记录锁是MySQL中最常见的行级锁，用于保护数据的一致性和完整性。
2. 间隙锁（Gap Lock）：
   1. 间隙锁是针对索引中的一个范围（间隙）进行的锁定，用于防止其他事务在间隙中插入新的数据。当事务查询某一范围的数据时，会对该范围进行间隙锁定，阻止其他事务在该范围中插入新的数据。
   2. 间隙锁可以避免幻读（Phantom Read）现象，确保查询结果的一致性。
3. 临键锁（Next-Key Lock）：
   1. 临键锁是记录锁和间隙锁的组合，用于同时锁定索引中的一个范围和范围内的数据行。当事务查询某一范围的数据时，会对该范围及其中的数据行进行临键锁定，阻止其他事务在该范围中插入新的数据或修改已有数据。
   2. 临键锁可以避免间隙锁导致的幻读问题，确保查询结果的完整性和一致性。
4. 插入意向锁（Insert Intention Lock）：
   1. 插入意向锁是一种特殊的锁，用于表示事务准备在索引中的某个位置插入新数据。当事务准备在某个索引中插入新数据时，会获取对该索引的插入意向锁，阻止其他事务在同一索引位置插入新数据。
   2. 插入意向锁可以保证并发插入操作的正确性，避免多个事务同时插入相同索引位置导致的冲突。

13. 高并发场景下，如何安全修改同一行数据

在高并发场景下安全地修改同一行数据是一个挑战性的问题，需要考虑并发控制、事务管理和数据一致性等方面。以下是一些常见的解决方案：

1. 乐观锁（Optimistic Locking）： 乐观锁是一种基于版本控制的并发控制机制，在进行数据更新操作前，先读取数据并获取版本号或时间戳等标识，然后在更新时检查标识是否与预期一致，如果一致则执行更新操作，否则认为数据已经被其他事务修改，需要进行冲突处理。乐观锁通常适用于读操作比写操作频繁的场景，能够减少锁的竞争。
2. 悲观锁（Pessimistic Locking）： 悲观锁是一种基于锁的并发控制机制，通过在事务开始时获取锁来保护数据的一致性，防止其他事务同时修改同一行数据。在高并发场景下，悲观锁可能会导致大量的锁竞争和阻塞，影响系统的性能和吞吐量。
3. 分布式锁（Distributed Lock）： 在分布式系统中，可以使用分布式锁来保护共享资源的访问，防止多个节点同时修改同一行数据。常见的分布式锁实现包括基于数据库的悲观锁、基于缓存的分布式锁、基于ZooKeeper、Redis等的分布式锁。
4. 数据库事务（Database Transaction）： 使用数据库事务来管理数据的一致性和并发访问。通过将需要修改的数据操作放在一个事务中，并设置合适的隔离级别和事务范围，可以保证数据的一致性和并发安全。
5. 队列与异步处理： 将并发修改操作转换为异步任务，并使用队列来保证任务的顺序执行，可以避免并发修改同一行数据的问题。这种方式通常适用于对实时性要求不高的业务场景。
6. 业务逻辑调整： 重新设计业务逻辑，避免或者减少对同一行数据的并发修改操作，可以降低并发冲突的可能性。例如，通过分段或者分批处理数据，将大量的并发操作转换为串行操作，从而减少并发冲突。

综上所述，安全地修改同一行数据需要综合考虑并发控制、事务管理、分布式环境和业务需求等因素，并选择合适的并发控制机制和解决方案。在实际应用中，可以根据具体的业务场景和性能需求来选择最合适的方案。

14. Undo log是如何回滚事务的

在数据库中，当一个事务需要回滚时，数据库系统会利用 Undo 日志（Undo log）来实现事务的回滚操作。Undo 日志记录了事务执行过程中对数据所做的修改操作的逆操作，以便在事务回滚时可以正确地撤销这些修改。

下面是 Undo 日志是如何回滚事务的简要步骤：

1. 识别事务范围： 当一个事务开始时，数据库系统会为该事务分配一个唯一的事务 ID，并标记所有该事务所做的修改操作所在的 Undo 日志范围。
2. 记录修改操作： 在事务执行过程中，每个修改操作都会记录在 Undo 日志中，包括修改的数据页、修改前后的数据值等信息。
3. 撤销修改操作： 当需要回滚事务时，数据库系统会逆序遍历该事务的 Undo 日志记录。对于每一条修改操作，数据库系统会执行相应的逆操作，将数据恢复到事务开始之前的状态。
4. 释放 Undo 日志空间： 在回滚操作完成后，数据库系统会释放该事务所占用的 Undo 日志空间，以便后续的事务使用。

需要注意的是，Undo 日志的使用可以实现数据库的事务回滚功能，但同时也增加了系统的开销，包括存储空间的占用和回滚操作的执行时间。因此，在设计数据库系统时，需要权衡 Undo 日志的使用和性能开销，并根据具体的应用场景进行优化。

15. 自增主键会遇到什么问题

自增主键是一种常见的主键类型，通常用于为表中的每一行自动生成唯一的标识符。虽然自增主键具有很多优点，比如简单、方便、唯一等，但在某些情况下也会遇到一些问题，主要包括：

1. 不适合分布式环境： 在分布式环境下，多个节点同时生成自增主键可能会导致冲突和重复。因为每个节点都有自己的自增计数器，无法保证全局唯一性。
2. 不适合大规模批量插入： 在大规模批量插入数据时，由于每次插入都需要获取并更新自增计数器，可能会导致性能瓶颈。
3. 中间插入可能会导致不连续的ID： 如果在中间位置插入数据（例如删除某些行后再插入新行），会导致ID的不连续，可能会对一些依赖于连续ID的应用产生影响。
4. 可预测性和安全性差： 自增主键的值通常是连续递增的，因此可以被猜测。在某些情况下，这可能会暴露数据，并增加安全风险。
5. 数据迁移和备份： 自增主键在数据迁移和备份时可能会带来一些额外的复杂性，因为需要考虑如何保持主键的唯一性。

为了解决这些问题，可以考虑使用其他类型的主键，例如全局唯一标识符（GUID）或分布式ID生成器。这些方法可以在分布式环境下生成全局唯一的主键，避免了自增主键可能遇到的问题。另外，还可以通过数据库的序列（Sequence）或者应用层的UUID等机制来生成唯一标识符。选择合适的主键类型取决于具体的业务需求和数据库架构。

16. 了解MySQL锁升级吗

在 MySQL 中，锁升级是指当一个事务持有一个共享锁（读锁）时，如果需要对同一个资源进行写操作，则需要将共享锁升级为排他锁（写锁），这个过程被称为锁升级。

MySQL 中的锁升级机制可以简单描述如下：

1. 共享锁（S锁）： 共享锁是允许多个事务同时持有的锁，用于读取操作。多个事务可以同时持有共享锁，并且不会相互阻塞。
2. 排他锁（X锁）： 排他锁是独占锁，用于写入操作。当一个事务持有排他锁时，其他事务无法同时持有任何类型的锁。

在 MySQL 中，锁升级的具体实现方式可以根据存储引擎的不同而有所不同。在 InnoDB 存储引擎中，MySQL 使用了一种称为 next-key locks 的技术来处理锁升级。该技术基于索引来管理锁，允许事务在索引上的记录上持有排他锁，以及在记录之间的间隙（gap）上持有共享锁。

当一个事务在某个索引记录上持有共享锁，并且需要对该记录进行修改时，MySQL 会尝试将共享锁升级为排他锁。为了实现这一点，MySQL 会在需要修改的记录和其前后的间隙上设置排他锁，以确保其他事务无法插入新的记录或修改现有的记录，从而保护正在进行的修改操作的完整性。

需要注意的是，MySQL 在锁升级时可能会导致锁冲突和性能问题。因此，为了避免不必要的锁升级，应该尽量减少事务中持有锁的时间，以及避免事务中持有过多的锁。

17. 慢SQL你是怎么优化的

优化慢 SQL 的过程通常涉及以下几个步骤：

1. 识别慢 SQL： 首先，需要识别出执行时间较长的 SQL 查询或操作，可以通过数据库系统提供的性能监控工具、慢查询日志、数据库性能分析工具等来获取这些信息。
2. 分析执行计划： 对于识别出的慢 SQL，需要分析其执行计划，了解数据库是如何执行这些查询的。执行计划可以告诉你查询是如何使用索引、连接表以及执行其他操作的。
3. 优化查询语句： 根据执行计划和具体的业务需求，对慢 SQL 进行优化。优化的方式可能包括：
   1. 重写查询语句，消除不必要的子查询、联合查询、OR 条件等，使查询更简洁、更高效。
   2. 确保查询条件使用了合适的索引，可以通过创建新索引、优化已有索引、使用索引提示等方式来提高查询性能。
   3. 使用覆盖索引（Covering Index）来避免回表操作，从而减少查询的磁盘读取。
   4. 避免在 WHERE 子句中对字段进行函数操作，这会导致索引失效。
   5. 使用 UNION ALL 替换 UNION，除非需要进行去重操作，这可以避免 MySQL 执行额外的去重操作。
   6. 避免使用全表扫描，尽可能利用索引来加速查询。
4. 优化数据结构和配置： 除了优化查询语句，还可以考虑优化数据库的数据结构和配置参数，例如：
   1. 对数据库表进行垂直拆分或水平拆分，将大表拆分为多个小表，减少单表的数据量。
   2. 调整数据库引擎、缓冲池大小、连接池大小等配置参数，以提高数据库的性能和吞吐量。
5. 性能测试和验证： 在进行优化之后，需要进行性能测试和验证，确保优化后的 SQL 查询在生产环境中可以达到预期的性能提升，并且不会对系统的稳定性和可靠性造成影响。
6. 持续监控和调优： 优化慢 SQL 是一个持续的过程，需要定期监控数据库性能，并根据实际情况进行调优。可以使用性能监控工具、慢查询日志等来持续监控系统的性能，并及时处理新出现的慢 SQL。

18. 如何优化深分页limit 1000000

深分页（Deep Pagination）通常指的是从大量数据集中查询某一页数据，比如在搜索结果中显示第 1000000 条记录。这种情况下，传统的 OFFSET 和 LIMIT 方法会导致性能问题，因为数据库引擎需要扫描并跳过大量数据才能到达目标页。

下面是一些优化深分页查询的方法：

1. 使用游标分页（Cursor Pagination）： 游标分页是一种基于记录位置的分页方式，不需要使用 OFFSET 和 LIMIT，而是使用上一页或下一页的最后一条记录作为游标，然后查询相邻的记录。这样可以避免跳过大量数据，提高查询效率。
2. 使用物理分页（Keyset Pagination）： 物理分页是一种基于记录的排序顺序和唯一键的分页方式，适用于需要按特定顺序分页查询的情况。在物理分页中，每一页的记录都有一个唯一的标识符或者排序键，通过记录的唯一键来定位和查询下一页数据。
3. 缓存热门数据： 如果可能的话，可以将热门数据缓存到内存或者其他存储介质中，以减少深分页查询的压力。这样可以避免每次查询都需要从数据库中检索大量数据。
4. 优化查询性能： 如果深分页是必要的，并且无法避免，可以考虑优化查询性能，包括：
   1. 确保使用了合适的索引来加速查询，尽可能减少全表扫描和排序操作。
   2. 分析查询计划，优化数据库的配置和参数，以提高查询性能。
   3. 考虑使用数据库分区技术，将数据按照某种规则进行分区，以减少每次查询的数据量。
5. 分页预处理： 如果深分页查询是在用户请求之前就可以预先知道的，可以在后台预处理并缓存结果集，然后根据用户请求直接返回预处理结果，而不需要进行深分页查询。

总的来说，优化深分页查询需要综合考虑数据库的性能、数据量、查询需求等多个因素，并根据具体情况采取合适的优化策略。

19. Binlog有几种录入格式与区别

MySQL的binlog（二进制日志）是用于记录MySQL数据库中发生的修改操作的一种日志文件，它记录了数据库的修改操作，包括增删改等操作，可以用于数据恢复、数据备份、数据复制等场景。MySQL的binlog有多种录入格式，主要包括以下几种：

1. Statement-Based Replication (SBR，基于语句的复制)： 在这种格式下，binlog会记录执行的SQL语句。当主服务器执行一个SQL语句时，它会记录这个语句的内容。在从服务器上，这些语句会被重新执行。这种格式的优点是binlog较小，但由于有些SQL语句无法被精确地记录，可能会引发一些复制问题，比如由于主从服务器上的数据不同步而导致数据不一致的情况。
2. Row-Based Replication (RBR，基于行的复制)： 这种格式下，binlog会记录每一行数据的变化。当主服务器执行一个修改操作时，它会将受影响的行的内容记录到binlog中，从服务器上会根据这些记录来对自己的数据进行相应的修改。这种格式下的binlog通常比较大，但可以保证复制的精确性，避免了基于语句的复制可能出现的问题。
3. Mixed Format (混合格式)： 这种格式结合了基于语句和基于行的复制。MySQL会根据具体的情况来选择使用哪种方式来记录binlog。通常，对于可以精确记录的SQL语句，MySQL会选择基于语句的复制，而对于无法精确记录的SQL语句，则会选择基于行的复制。

这些binlog录入格式各有优劣，可以根据具体的业务需求和数据库架构来选择合适的格式。Statement-Based Replication通常用于读写比较平衡、数据更新操作较少的场景；Row-Based Replication则更适用于数据更新操作频繁、对数据一致性要求高的场景。Mixed Format则可以兼顾两者的优点，在不同的场景下选择合适的复制方式。

20. 一条SQL的执行过程是怎样的

1、在打开客户端后，最初需要和sql服务器建立连接，账号认证和校验权限

2、服务器先检查查询缓存，如果命中了缓存，返回缓存中的结果，否则进入下一阶段（5.7后关闭）

3、服务器端进行SQL解析（分析器解析语法），再由优化器（索引）生成对应的执行计划

4、MySQL根据优化器生成的执行计划，再调用**存储引擎的API（执行器）**来执行查询，返回结果到客户端

分析器：进行语法分析和语义分析，并生成解析树，根据 MySQL 规则进一步检查解析树是否合法。比如检查要查询的数据表或数据列是否存在等。

优化器：优化查询语句，提高数据库的性能，根据各种优化算法生成最优的查询执行计划，从而最大程度地减少查询语句的执行时间。