Mysql

1.多表join的时候，小表驱动大表

在Mysql的 Nested Loop Join 中

驱动表（outer table）：首先被扫描的表。

被驱动表（inner table）：对驱动表每一行，根据 Join 条件去查找匹配行的表。

核心原则：过滤后剩余行数少的表，应该作为驱动表，这样可以减少被驱动表的访问次数。这就是小表

执行过程：

扫描驱动表（全表扫描或索引扫描）。

对驱动表的每一行，根据连接条件在被驱动表中查找（通常用索引 B+Tree 查找）。

如果被驱动表使用二级索引且需要回表，则访问主键索引。

小表驱动大表，大表负责命中索引。

比如

1

select * from A straight_join B on A.a = B.a;

数据库会全表扫A，然后每拿到一行就去比较条件 A.a=B.a，去B表里面查，B表命中索引的查询。实际上就是一个搜索树，查询的时间复杂度近似log2^B^，然后加上一次回表，可能就是2Log2 ^B^,所以总体的时间复杂度为A+2log2^B^*A，如果是覆盖索引的话，复杂度可降为 O(A + log₂(B) × A)

所以我的们A越小越好，join的本质就是查驱动表，然后扫被驱动表，当然是查的越少越好了

2.一条 UPDATE 语句发过来，从网络接收开始，到最终落盘，会经过哪些核心模块的处理

Mysql是一个分层的，核心模块包括网络层、SQL层和存储引擎层。

如果以一条 UPDATE t SET c = 2 WHERE id = 1; 语句为例，它的生命周期是这样的：

网络层：

首先，客户端通过TCP连接发送这条SQL。我的网络模块基于Java NIO实现，会接收这个请求，并将其传递给SQL层。

SQL层 - 解析与执行：

• SQL解析器：SQL层会解析这条字符串，生成一个抽象语法树（AST）。
• 执行器：然后，执行器会解释这棵树。对于这条UPDATE语句，它知道要去表t中找到id=1的行，并更新c列。

存储引擎层 - 事务与数据处理：这是最核心的部分。

• 事务管理器：执行器会向事务管理器申请开启一个事务。
• 访问数据：执行器请求存储引擎去获取id=1的行。存储引擎会先去 Buffer Pool（内存缓冲池）里查找，如果数据页不在内存，会通过 IO模块 从磁盘加载。
• 并发控制：在读取和修改数据时，为了保证隔离性，这里会涉及到 MVCC 和 锁管理器。UPDATE 是一种“当前读”，所以它会读取最新的已提交版本，并在这行数据上加一个 排他锁（X Lock），防止其他事务同时修改。
• 执行修改：获取到锁之后，执行器会在 Buffer Pool 中修改对应的数据页。但它不是直接覆盖旧数据，而是会生成一个 undo日志，记录下修改前的样子，用于回滚和支持MVCC。
• 记录日志：在修改内存数据页之前，必须先将这次操作的详细信息写入 redo日志（WAL） 的内存缓冲区。这是为了保证持久性。
• 提交事务：当客户端发起 COMMIT 时，日志管理器 会确保对应的 redo 日志被刷入磁盘。只要 redo 日志落盘了，即使此时宕机，数据也能恢复，所以我们就可以认为事务提交成功了。
• 数据落盘：至于 Buffer Pool 里的脏数据页，则由一个后台线程根据一定的策略（比如LRU）异步地刷回磁盘，这个过程不影响事务的提交响应。

3.为什么选择 WAL？它相比于直接写数据文件，核心优势是什么？写日志和更新内存数据页的顺序是怎样的？

选择 WAL 的核心优势在于将随机IO转换为了顺序IO，极大地提升了写入性能并保证了数据不丢失。

• 性能提升：数据库的数据页在磁盘上是离散存储的，修改它们需要大量的随机磁盘寻址，非常慢。而日志文件是追加写入的，是顺序IO，速度比随机IO快几个数量级。通过 WAL，事务提交时只需要保证日志落盘即可，脏数据页可以异步、批量地刷回磁盘，大大降低了事务提交的延迟。
• 顺序保证：这个顺序是绝对不能颠倒的，必须是先写日志（Log），再更新内存页（Buffer Pool）。这就是“Write-Ahead Logging”（预写日志）这个名字的由来。
  • 原因：如果反过来，先修改了内存中的数据页，然后系统在写日志之前宕机了。那么当系统重启时，内存中的修改会全部丢失，而日志里又没有记录这次操作，这个更新就永远地丢失了，这违反了事务的持久性（Durability）。而只要保证日志先写入，即使系统在数据页刷盘前宕机，重启后也可以通过扫描 redo 日志来恢复数据，保证了数据的完整性。”

4.当一个叶子节点分裂时，具体逻辑是怎样的？如何处理并发问题？

当向一个叶子节点插入数据，发现它已经满了的时候，会触发分裂操作，逻辑如下：

1. 叶子节点分裂： 找到中间位置的 key，将节点平分成两个。将这个中间 key 连同指向新节点的指针一起“上提”到父节点中。
2. 内部节点分裂： 如果因为子节点的“上提”导致父节点也满了，那么父节点（内部节点）也需要分裂。找到中间位置的 key，将该 key 单独“上提”到它的父节点中，而该 key 左右两侧的 key 和指针则分别构成两个新的内部节点。这个过程可能会一直递归到根节点。
3. 根节点分裂： 如果根节点也需要分裂，那么分裂后会产生一个新的根节点，此时 B+ 树的高度加一。

关于并发问题，这是一个非常关键的点。对B+树的这种结构性修改（如分裂或合并）必须是原子的，否则可能导致树的结构被破坏。

• 当一个线程需要修改一个B+树节点时，它会先获取这个节点的 Latch。在分裂过程中，它会同时持有父节点和要分裂的子节点的 Latch，操作完成后再释放。这种方式只锁定了必要的节点，允许其他不相关的读写操作继续进行。

Lock 和 Latch 区别

• 保护对象：Lock（锁） 是在事务层面，用来保护逻辑数据，比如表中的一行记录。它的目的是保证事务的隔离性。Latch（闩锁） 是在线程层面，用来保护内存中的物理数据结构，比如 Buffer Pool 中的一个数据页、B+树的一个节点或者一个共享的内存链表。它的目的是保证多线程访问共享内存结构时的线程安全。
• 持有时间：Lock 的持有时间很长，可能会贯穿整个事务，直到事务提交或回滚才释放。Latch 的持有时间非常短，通常只在一次原子操作的临界区内持有，比如修改一个 B+ 树节点，操作一完成马上就释放。
• 死锁：Lock 会涉及到死锁问题，需要数据库有专门的死锁检测机制。而 Latch 通常通过规定获取顺序（比如在B+树中总是从父节点到子节点获取）来避免死锁，所以一般认为 Latch 是无死锁的。

简单来说，Lock 是给数据库用户（事务）用的，保证业务逻辑的正确性；Latch 是给数据库内核开发者用的，保证内核数据结构的正确性。”

为什么选择B+树？在IO上看

1. • 关键在于“高扇出” (High Fan-out)： 数据库的数据是存储在磁盘上的，I/O 操作非常昂贵。我们需要一种“矮胖”的数据结构，而不是“瘦高”的。
   • 平衡二叉树为什么不行？ 因为它是二叉的，每个节点最多两个子节点。一棵存储百万数据的 AVL 树，深度会非常高（约 log₂(n)），导致需要进行很多次磁盘 I/O 才能找到数据。
   • B+ 树为什么行？ B+ 树的非叶子节点只存储索引（key）而不存储数据（data）。这意味着在同样大小的磁盘页（比如 16KB）中，B+ 树的非叶子节点可以存放成百上千个索引指针，这就是“高扇出”。因此，一棵三到四层的 B+ 树就能存储上千万甚至上亿的数据，查询时只需要 3-4 次磁盘 I/O。
   • B 树相比 B+ 树的劣势： B 树的非叶子节点也存数据，导致其“扇出”没有 B+ 树那么高，树的高度会相对更高，I/O 次数更多。
2. 哈希表的另一个致命缺点： 除了哈希冲突，哈希索引不支持范围查询。而数据库中 WHERE age > 20 这样的范围查询非常普遍，这是 B+ 树的叶子节点通过双向链表连接起来所能高效支持的。

5.慢查询的的过程

• 第一步：开启慢查询日志。 在 MySQL 中配置 slow_query_log 和 long_query_time，让数据库自动记录超过阈值的慢 SQL。
• 第二步：分析慢查询日志。 使用 mysqldumpslow 等工具，对日志文件进行分析，找出出现频率最高、查询时间最长的 SQL。
• 第三步：使用 EXPLAIN 分析执行计划。 针对找到的慢 SQL，使用 EXPLAIN 查看其执行计划，重点关注 type（是否为 ALL 全表扫描）、key（是否用上了索引）、Extra（是否出现了 Using filesort, Using temporary）等关键字段。

通过在 xxx 字段上增加联合索引，并利用索引覆盖，我们将这条 SQL 的查询时间从 2 秒优化到了 50 毫秒，接口的 P99 响应时间也从 2.2 秒降低到了 200 毫秒。

6.SQL优化

1.查询前xxx

• 使用limit
• 使用rank函数，ROW_NUMBER(): 不考虑并列，给出连续排名 (1, 2, 3, 4)。RANK(): 考虑并列，但会跳过排名。比如两个第二名，下一个就是第四名 (1, 2, 2, 4)。DENSE_RANK(): 考虑并列，且不跳过排名 (1, 2, 2, 3)。 在“取 Top N”的场景下，RANK() 或 DENSE_RANK() 通常是更合适的选择。

2.联合索引怎么走？

A，B，C，where a > ? and b = ? c != ?，怎么走

只会使用到联合索引的 A 部分，而 B 和 C 部分将无法有效地利用索引来缩小查询范围。

优化器首先会使用索引来处理 a > ? 这个条件。它会在 (A, B, C) 索引树上进行 范围扫描 (range scan)，找到所有满足 a 大于给定值的索引记录。这部分是高效的。

这是最关键的一点。当索引遇到了一个范围查询（如 >、<、BETWEEN），那么这个范围查询列（也就是 A）右边的所有索引列（也就是 B 和 C）都会失效，无法再用于进一步的索引查找。

• 为什么会失效？ 联合索引的排序是严格按照 A, B, C 的顺序来的。它首先按 A 排序，在 A 值相同的情况下，再按 B 排序，以此类推。当你执行 a > ? 时，你筛选出的是一个 A 值的范围。在这个范围里，B 的值是无序的（或者说，只是在每个单独的 A 值内部有序，但整体是无序的）。因此，数据库无法利用索引去快速定位满足 b = ? 的记录，只能一条一条地去过滤。

3.分页查询优化

如果数据量特别大的时候，分页查询慢该怎么办？

首先我们先去分析下为什么数据量大的情况下，分页查询会很慢

• 第一数据量太大
• 第二数据库处理分页的方法太笨

比如LIMIT 10000 10

• 第一步： 把整张表的数据全捞出来（全表扫描），按年龄排好序（文件排序）。
• 第二步： 吭哧吭哧数到第100010条，再给你返回最后10条。

不仅如此，如果用了普通索引，还需要去先查索引，这个很快，再去回表查，这个很慢，更何况我们有那么多的回表

还有排序呢？大多数时候，分页查询都会带有排序，比如按时间、按ID排序。

数据库不仅要查数据，还得根据你的排序要求重新排一次，特别是在数据量大的时候，排序的开销就变得非常大。

优化场景：单表limit优化

1. 子查询分页，绕过全表扫描，直接定位到目标数据！

1
2
3
4

-- 先查索引定位ID，再捞数据
SELECT * FROM user 
WHERE id >= (SELECT id FROM user ORDER BY age LIMIT 100000, 1)
LIMIT 10;

用覆盖索引快速找到第100000条的ID，直接从这个ID开始拿数据，跳过前面10万次回表。但是不适用于结果集不以ID连续自增的分页场景。实际情况不可能是ID连续的，加上过滤字段的话

1. JOIN联表

1
2
3

SELECT * FROM user t1
JOIN (SELECT id FROM user ORDER BY age LIMIT 100000,10) t2 
ON t1.id = t2.id;

先用索引快速拿到10个目标ID，再一次性联表查完整数据，减少回表次数。 跟子查询差不多的

1. 覆盖索引

1

SELECT age, name FROM user ORDER BY age LIMIT 100000,10;  

查询什么，什么加联合索引

优化场景：分库分表查询

假设订单表分了3个库，每个库分了2张表（共6张表），按用户ID分片。

1

SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 10;  

实际执行：

1、 每张表都老老实实查100万+10条数据（共600万+60条）；
2、 把所有数据汇总到内存，重新排序（600万条数据排序，内存直接炸穿）；
3、 最后忍痛扔掉前100万条，给你10条结果；

所存在的问题：

1：数据分散，全局排序难
各分片数据独立排序，合并后可能乱序，必须全量捞数据重排。

2：深分页=分片全量扫描
每张表都要查 offset + limit 条数据，性能随分片数量指数级下降。

3：内存归并压力大
100万条数据 × 6个分片 = 600万条数据在内存排序，分分钟OOM！

优化方案：

1.禁止跳页，只能一页一页的翻找

按时间倒序，拿前10条 ，记住上一页最后一条的时间 ，这样依次类推

2.二次查询法

第一轮查询：每张分片查缩小范围的数据；

1
2
3
4

-- 每张分片查 (offset / 分片数量) + limit 条  
SELECT create_time FROM orders  
ORDER BY create_time DESC  
LIMIT 33334, 10;  -- 假设总offset=100万，分6个分片：100万/6 ≈ 166666  

从所有分片结果中，找到最小的 create_time （比如 2023-09-20 08:00:00 ）。 2、 第二轮查询：根据最小时间戳查全量数据；

1
2
3
4
5

SELECT * FROM orders  
WHERE create_time >= '2023-09-20 08:00:00'  
ORDER BY create_time DESC  
LIMIT 10;  

3.直接使用ES进行查询

7.Limit

LIMIT子句主要用于限制查询结果集返回的行数。它有两个核心的应用场景：

数据分页：这是LIMIT最广为人知的用途。通过结合OFFSET（或使用LIMIT的逗号语法），我们可以实现数据的分页展示。

获取Top N记录：当我们需要获取排序后的前N条记录时，LIMIT与ORDER BY结合使用就非常强大。

实现原理：

没有ORDER BY的情况

如果查询语句中没有ORDER BY，比如 SELECT * FROM users LIMIT 10;

• 实现原理: 这种情况下，MySQL的执行非常简单高效。它会按照存储引擎中数据的物理顺序（对于InnoDB通常是主键顺序）或者某个它认为最快的扫描顺序，顺序地读取数据行，并进行计数。当读取到LIMIT指定的行数（这里是10条）后，MySQL会立即停止扫描，直接返回结果。

带有ORDER BY的情况

• 情况A: ORDER BY的字段有索引
  • 查询示例: SELECT * FROM articles ORDER BY publish_time DESC LIMIT 10; (假设publish_time有索引)
  • 实现原理: 这是最高效的方式。MySQL优化器会直接利用publish_time的索引。因为索引本身就是有序的，所以MySQL可以直接在索引上进行倒序扫描，找到前10个满足条件的索引条目，然后通过回表（如果需要）获取完整的数据行。
  • 特点: 速度非常快，因为它避免了全表扫描和文件排序，扫描的范围被索引精确地限定在了所需的前10条记录。
• 情况B: ORDER BY的字段没有索引
  • 查询示例: SELECT * FROM users ORDER BY score DESC LIMIT 10; (假设score没有索引)
  • 实现原理: 这是性能最低效的方式。MySQL无法利用索引来获取有序数据，只能：
    1. 全表扫描: 读取表中的所有数据行。
    2. 文件排序 (Filesort): 将读取到的所有数据行加载到内存（如果内存足够大，即sort_buffer_size）或临时磁盘文件（如果内存不够）中，按照score字段进行排序。
    3. 取前N条: 在排序完成后的结果集中，取出前10条记录返回。
  • 特点: 性能开销巨大，因为它需要读取全表数据并进行代价高昂的排序操作。这正是LIMIT导致全表扫描的典型场景。

全表扫描，没有ORDER BY的LIMIT查询，ORDER BY的字段上有合适的索引

全表扫描，ORDER BY的字段没有索引，深度分页问题 ，WHERE条件与ORDER BY字段冲突，导致无法使用索引

8.如何查询表中最新的五百条数据

要查询表中最新的500条数据，最高效且最常用的方法是利用索引进行倒序排序，并结合 LIMIT 子句。

1. 根据自增主键 id 查询，直接进行order by ，因为是主键，所以有聚簇索引，查询效率高
2. 根据创建时间 create_time 查询，要进行加索引，MySQL会利用create_time的B+树索引。同样，它会从索引的末端开始倒序扫描，找到500个索引条目，然后通过回表（如果需要）获取完整的数据行。

9.海量数据如何分页查询

我们传统的分页：

1
2
3
4
5
6

-- 查询第100页，每页20条
SELECT * 
FROM massive_table 
ORDER BY create_time DESC 
LIMIT 20 OFFSET 2000; -- (100-1) * 20 = 2000

为什么它在海量数据下会崩溃？

这个查询的性能瓶頸在于OFFSET。当OFFSET的值非常大时（比如OFFSET 1000000，即深度分页），数据库的执行过程是：

1. 扫描数据: 数据库需要从头开始，扫描出 OFFSET + LIMIT 条记录（即 2000 + 20 = 2020 条）。如果ORDER BY的字段没有索引，这里就是全表扫描+文件排序；即使有索引，也需要进行大量的索引扫描。
2. 丢弃数据: 然后，数据库会丢弃掉前面的OFFSET条记录（2000条），只保留最后的LIMIT条（20条）作为结果返回。

解决办法：

1. 键集分页/游标分页

不再使用OFFSET来“跳过”记录，而是使用上一页最后一条记录的唯一值（或有序值）作为“书签”或“游标”，来定位下一页的起始位置。

1
2
3
4
5

SELECT * FROM massive_table 
WHERE (create_time, id) < ('2023-10-27 10:00:00', 12345)
ORDER BY create_time DESC, id DESC 
LIMIT 20;

性能比较快，但是不能指定页码

2.延迟关联 ，这是一种针对LIMIT OFFSET的优化方案，适用于必须保留“跳转到第N页”功能的场景。

将扫描和丢弃大量数据的过程，在开销更小的索引层面完成，而不是在包含所有字段的主表上完成。

1. 子查询: 先在覆盖索引（Covering Index）上使用LIMIT OFFSET快速定位到目标数据行的主键ID。这个过程很快，因为只操作索引，不涉及主表数据。
2. JOIN关联: 然后将子查询的结果（只包含少量主键ID）与原表进行JOIN，获取完整的行数据。

1
2
3
4
5
6
7

SELECT t1.* 
FROM massive_table AS t1
JOIN (
    -- 这一步在索引上完成，速度很快
    SELECT id FROM massive_table ORDER BY create_time DESC LIMIT 20 OFFSET 1000000
) AS t2 ON t1.id = t2.id;

虽然性能提示比较大，但是offset过大还是性能会下降

3.使用ES，等一些搜索引擎来完成

10.全文搜索怎么实现

我会从“最不应该用的方法”开始，逐步讲到“数据库内置方案”，最后再介绍“业界标准的专业方案”。

最原始的“土方法” - LIKE '%keyword%'

1. 性能灾难：LIKE查询，特别是以%开头的模糊查询，无法有效利用数据库的B-Tree索引。对于海量数据，这将导致全表扫描，查询性能会随着数据量的增长而急剧下降，最终拖垮整个数据库。
2. 它只是简单的字符串匹配，完全不具备“全文搜索”的核心能力，没有分词，没有词干提取，没有相关性排序

数据库内置的“半专业”方案 - FULLTEXT 索引

倒排索引 (Inverted Index) 这是全文搜索的技术基石。与我们常用的B-Tree索引（数据 -> 索引）不同，倒排索引是（关键词 -> 数据）的映射。

在创建FULLTEXT索引时，数据库会对指定的文本列进行分词，将长文本拆解成一个个独立的词语（token）。索引构建: 然后，它会创建一个索引，记录下每个词语出现在哪些文档（数据行）中当我们搜索“搜索引擎”时，数据库会先对搜索词进行分词，得到“搜索”和“引擎”，然后去倒排索引中查找同时包含这两个词的文档列表（文档1），并根据相关性算法（如TF-IDF）进行排序，最后返回结果。

1. 但是他也是有性能瓶颈的，全文搜索的计算仍然会消耗数据库的大量CPU和I/O资源
2. 他的功能也是有限的，对中文的搜索效果不好
3. 无法水平扩展，不能增强搜索能力

专用搜索引擎 (Elasticsearch)

1. 分布式架构: ES天生就是分布式的。它会将索引数据分割成多个分片（Shard），并将这些分片均匀地分布在集群的多个节点上。这使得ES可以通过增加节点来水平扩展，轻松应对PB级别的数据和高并发的查询请求。
2. 极其强大的分析器 (Analyzer)：ES提供了高度可定制的文本分析流程。一个分析器由字符过滤器（Character Filters）、分词器（Tokenizer）和词元过滤器（Token Filters）组成，可以实现非常复杂的文本处理，如去除HTML标签、使用IK等中文分词器、同义词转换、拼音转换等。
3. 先进的相关性排序: ES使用更先进的BM25等相关性算法，能够提供远比数据库更精准的搜索结果排序。
4. 丰富的功能: 除了全文搜索，ES还提供了强大的聚合（Aggregations）、地理位置搜索、自动补全等高级功能。

在这个里面最重要的就是数据同步，可以使用Canal或者Logstash

11.分库分表的策略

• 原则：当单一数据库实例的连接数/QPS成为瓶颈，或单一数据表的数据量成为瓶颈时，就需要考虑分库分表。
• 什么时候分库？
  • 当业务并发量非常大，单一数据库实例的连接数或处理能力无法支撑时。
  • 策略：垂直分库（按业务拆分）。将不同业务模块的数据（如用户库、订单库、商品库）拆分到不同的数据库实例中，将流量分散到多个服务器。
• 什么时候分表？
  • 当单一数据表的数据量过大（如超过千万行），导致查询和 DML 操作性能严重下降时。
  • 策略：水平分表（按规则拆分）。将一个大表按照某种规则拆分成多个结构相同的小表。
    • Range 模式：按时间或 ID 范围划分。优点是易于扩展，但可能导致数据热点。
    • Hash 模式：对某个字段（如 user_id）进行哈希取模。优点是数据分布均匀，但扩容时需要数据迁移。