2025最新面试题-mysql面试题(二)

问题背景 ：

某电商Saas平台的订单表 orders 数据量达到500万条，用户反馈分页查询订单时，翻页到第100页后响应时间超过5秒，严重影响用户体验。数据库版本为MySQL 8.0，表引擎为InnoDB。这里大致给大家演示，大家心里有就好

检查步骤

表结构与索引检查 ：

-- 表结构
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  order_time DATETIME,
  amount DECIMAL(10,2),
  status TINYINT,
  product_id INT,
  INDEX idx_user_id (user_id)
);

问题：分页查询基于 order_time 排序，但缺少 (order_time, user_id) 的联合索引。
现有索引仅覆盖 user_id，无法高效支持排序和分页。

慢查询日志分析：

-- SQL
SELECT * FROM orders
WHERE user_id = 1001
ORDER BY order_time DESC
LIMIT 100000, 20; -- 翻页到第5000页时需跳过10万条记录

EXPLAIN 结果显示：type=ALL（全表扫描），Extra=Using filesort（文件排序）。

性能瓶颈定位：

全表扫描：由于未命中覆盖索引，需要回表查询所有字段。

文件排序：ORDER BY 未利用索引排序。

高Offset分页：LIMIT 100000, 20 需要扫描前100000+20条记录，效率极低。

优化方案：

添加联合索引 ：
```
ALTER TABLE orders ADD INDEX idx_order_time_user_id (order_time DESC, user_id);
```
- 覆盖 ORDER BY order_time DESC, user_id 的排序需求，避免文件排序。
- 减少回表次数（若查询字段在索引中）。

优化SQL写法（延迟关联） ：

SELECT o.* 
FROM orders o
JOIN (
  SELECT id 
  FROM orders 
  WHERE user_id = 1001
  ORDER BY order_time DESC
  LIMIT 100000, 20
) AS tmp ON o.id = tmp.id;

子查询先通过覆盖索引快速定位主键，再通过主键关联回表，减少数据扫描量。

分页优化（游标分页） ：

-- 记录上一页最后一条记录的order_time和id
SELECT * FROM orders
WHERE user_id = 1001
  AND (order_time < '2023-10-01 12:00:00' OR (order_time = '2023-10-01 12:00:00' AND id < 12345))
ORDER BY order_time DESC, id DESC
LIMIT 20;

避免高Offset，通过游标（上一页的最后一条记录的排序字段值）定位下一页起始位置。

业务层优化 ：
- 限制用户最大翻页深度（如最多100页），引导通过搜索或过滤缩小范围。
- 异步加载或缓存热门数据。

优化效果：

查询时间从5秒降至50毫秒内。
EXPLAIN 显示：type=range（索引范围扫描），Extra=Using index（覆盖索引）。

面试官提问：

"请描述一个你解决的MySQL性能优化案例，并说明你的思路和结果。"

回答模板：

问题背景 ： "我曾在电商平台优化过一个订单分页查询的性能问题。当数据量达到500万时，翻页到深页码（如第100页）时响应时间超过5秒。"
分析过程 ：
- 检查表结构和索引，发现缺少支持排序和分页的联合索引。
- 通过慢查询日志和 EXPLAIN确认全表扫描和文件排序问题。
- 定位到高Offset分页导致大量无效数据扫描。"
优化方案 ：
- 添加 (order_time, user_id)的联合索引，覆盖排序和查询条件。
- 使用延迟关联技术，通过子查询先定位主键再回表，减少数据扫描量。
- 引入游标分页替代传统分页，避免高Offset问题。
- 业务层限制最大翻页深度。"
结果与总结 ：
- 优化后查询时间从5秒降至50毫秒，用户体验显著提升。
- 总结：索引设计需贴合查询模式，深分页需避免高Offset，业务逻辑与数据库优化需协同。"

MySQL索引失效性能优化案例

案例背景：

某社交平台的用户表 users 数据量增长至1000万条后，用户反馈根据“昵称”搜索时（如模糊查询 LIKE '%小明%'），查询耗时从毫秒级增至10秒以上。表引擎为InnoDB，已有索引如下：

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  nickname VARCHAR(50),
  age INT,
  city VARCHAR(20),
  created_at DATETIME,
  INDEX idx_nickname (nickname)
);

问题分析步骤：

慢查询定位 ：

-- 问题SQL
SELECT * FROM users 
WHERE nickname LIKE '%小明%' 
ORDER BY created_at DESC 
LIMIT 20;

用户频繁使用模糊搜索昵称，响应时间超过10秒。

执行计划分析 ：
```
EXPLAIN SELECT * FROM users WHERE nickname LIKE '%小明%';
```
- 结果：type=ALL（全表扫描），key=NULL（未使用索引），rows=10,000,000。
索引失效原因 ：
- 左模糊匹配 ：LIKE '%小明%' 导致索引失效（B+树无法利用前缀匹配）。
- 排序与索引不匹配 ：ORDER BY created_at 未在索引中，触发文件排序（Using filesort）。
- 回表开销 ：即使使用索引，仍需回表查询所有字段。

优化方案：

使用全文索引:

-- 添加全文索引
ALTER TABLE users ADD FULLTEXT INDEX ft_nickname (nickname);

-- 优化后SQL
SELECT * FROM users 
WHERE MATCH(nickname) AGAINST('+小明' IN BOOLEAN MODE)
ORDER BY created_at DESC 
LIMIT 20;

优势：全文索引支持任意位置的文本匹配，避免左模糊问题。
限制：需处理停用词，且中文需配合分词插件（如ngram）。

覆盖索引优化（若无法使用全文索引） ：

-- 新增联合索引（覆盖查询和排序字段）
ALTER TABLE users ADD INDEX idx_nickname_created_at (nickname, created_at);

-- 强制右模糊查询
SELECT * FROM users 
WHERE nickname LIKE '小明%'  -- 仅右模糊可利用B+树索引
ORDER BY created_at DESC 
LIMIT 20;

妥协点 ：牺牲左模糊能力，仅支持前缀匹配。

业务层调整 ：
- 限制模糊搜索的最小字符长度（如至少3个字）。
- 引入Elasticsearch等搜索引擎，实现高效全文检索。

优化效果：

使用全文索引后，查询时间从10秒降至100毫秒内。
EXPLAIN 显示：type=fulltext（全文索引扫描），Extra=Using where; Using filesort（仍需优化排序）。
若结合覆盖索引和游标分页，可进一步消除文件排序。

最好是使用搜索引擎，但是这里不使用搜索引擎的原因有两个，第一个是数据量不够，第二个是添加ES要增加额外的架构复杂度，并且这里没有聚合的需求，并且项目

面试回答技巧

面试官提问：

"请举例说明你遇到的MySQL索引失效场景，以及如何解决的。"

回答模板：

问题背景 ： "在社交平台的用户模糊搜索功能中，当用户量达到千万级时，LIKE '%xxx%'查询性能急剧下降。原因为 nickname字段的普通B+树索引因左模糊失效，导致全表扫描。"
分析过程 ：
- 通过 EXPLAIN确认索引未命中，发现 type=ALL和 Using filesort。
- 定位到 LIKE左模糊是核心问题，B+树索引无法支持随机字符串匹配。
- 评估是否可通过业务调整将左模糊改为右模糊（如搜索日志场景）。"
优化方案 ：
- 短期方案 ：添加全文索引（FULLTEXT），利用倒排索引加速任意位置匹配。
- 长期方案 ：引入Elasticsearch，专用于复杂搜索场景，并同步数据。
- 业务妥协 ：限制模糊搜索的最小输入长度，减少无效请求。"
结果与总结 ：
- 全文索引使查询耗时从10秒降至100毫秒，但中文分词需配置ngram插件。
- 核心教训：B+树索引仅适合前缀匹配，复杂文本搜索需结合专用工具。
- 后续通过ES实现了更灵活的搜索功能（如拼音搜索、同义词）。"

加分点：

原理深入 ：解释B+树索引的层序遍历机制，说明左模糊为何破坏前缀匹配。
扩展方案 ：
- 提到 INNODB_FT_DEFAULT_STOPWORD处理停用词。
- 使用 ngram_token_size调整中文分词粒度。
权衡思考 ：
- 全文索引的存储开销与写入性能影响。
- 业务是否接受“右模糊”的体验差异（如仅允许搜索“前缀”）。

话术：

我曾经做过一个电商的Saas平台，需求是我们的平台的订单表 orders 数据量达到500万条，用户反馈分页查询订单时，翻页到第100页后响应时间超过5秒，严重影响用户体验。

我最开始去排查的时候，检查表结构以及索引，发现缺少支持排序和分页的正常索引，并且由于数据偏移量较大，所以导致我们的项目没有正常使用到user_id的索引，通过由于没有正常的需求把控，导致用户可以随意的操作页数，进而倒置高偏移量查询，导致扫描了大量的无效数据，

我们经过讨论，确定了优化的方案，添加联合索引，将查询字段以及条件字段做为联合索引覆盖排序以及查询条件，使用了延迟关联的技术，通过子查询先定位主键再进行回表，减少数据扫描量，引入了游标分页的方式替代传统分页的方式，避免了高偏移量，并且辅助业务进行最大翻页深度的限制，

优化之后，我们的查询时间从5S降低到50ms，4用户反馈体验显著提升

常见索引失效场景归纳（面试备用）：

场景	示例	解决方案
左模糊查询	`LIKE '%abc'`	全文索引、右模糊、ES
对索引列使用函数/表达式	`WHERE YEAR(create_time)=2023`	改用范围查询（`BETWEEN`）
隐式类型转换	`WHERE id = '100'`（id为INT）	确保类型一致
OR条件部分无索引	`WHERE a=1 OR b=2`（b无索引）	为b添加索引，或改用UNION
联合索引跳过最左列	索引 `(a,b)`，查询 `WHERE b=2`	调整索引顺序或补充条件（如 `a IN(...)`）

通过此案例，不仅能展示对索引机制的深刻理解，还能体现从技术到业务的综合优化能力，这在面试中会极具说服力。

实际项目场景：高并发电商订单系统（雪花算法id与mysql结合的业务问题）

业务背景

某电商平台日订单量超过 1000 万 ，订单数据需分库分表存储（分 16 个库，每个库 64 张表）。系统采用雪花算法生成订单 ID（主键），但在以下环节出现性能问题：

写入性能下降 ：高并发下索引页频繁分裂，导致插入延迟。
分页查询卡顿 ：用户查看历史订单时，LIMIT 1000000, 10 查询耗时超过 3 秒。
时间范围查询低效 ：运营需按时间筛选订单，但直接解析雪花 ID 时间戳导致全表扫描。
分库分表后 ID 冲突 ：某次扩容后，因 worker_id 分配重复，导致多个分片生成重复 ID。

优化方案：

1. 写入性能优化（索引分裂问题）

问题分析 雪花 ID 在同一毫秒内局部乱序（如不同节点生成的 ID 交叉插入），导致主键索引页频繁分裂，写入性能下降。

解决方案

主键改用自增 ID + 冗余雪花 ID 保留雪花 ID 作为业务唯一标识（如订单号），但主键改用 MySQL 自增 ID，确保物理写入顺序性。

CREATE TABLE orders (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- 自增主键，保证物理有序
  order_no BIGINT UNSIGNED NOT NULL,    -- 雪花算法生成的业务 ID
  user_id BIGINT NOT NULL,
  create_time DATETIME NOT NULL,
  INDEX idx_order_no (order_no),
  INDEX idx_create_time (create_time)
) ENGINE=InnoDB;

效果

写入吞吐量提升 40% （索引分裂减少）。
业务层仍可通过 order_no 保证分布式唯一性。

2. 分库分表 ID 冲突（Worker ID 分配）

问题分析 手动配置 worker_id，扩容时因运维误操作导致两个分片使用相同的 worker_id，生成重复订单号。

解决方案

动态 Worker ID 分配 服务启动时，通过 ZooKeeper 临时节点申请 worker_id，确保全局唯一：

// 伪代码：基于 ZooKeeper 的 Worker ID 分配
public class SnowflakeWorker {
    private int workerId;

    public SnowflakeWorker() {
        String path = "/snowflake/workers";
        // 在 ZooKeeper 上创建临时顺序节点，返回序号作为 workerId
        String node = zk.create(path + "/worker-", EPHEMERAL_SEQUENTIAL);
        this.workerId = extractWorkerIdFromNode(node);  // 如节点名为 worker-0003，则 workerId=3
    }
}

效果

彻底避免 worker_id 冲突，支持动态扩容缩容。

3. 时间范围查询优化

问题分析 运营需查询 2023-10-01 至 2023-10-02 的订单，但直接通过雪花 ID 解析时间戳需全表扫描：

-- 低效查询（需解析 ID 的时间戳）
SELECT * FROM orders 
WHERE (order_no >> 22) BETWEEN start_timestamp AND end_timestamp;

解决方案

显式存储时间字段 + 复合索引 冗余 create_time 字段，并建立 (create_time, order_no) 索引：

ALTER TABLE orders ADD INDEX idx_time_order (create_time, order_no);

查询时直接利用时间字段过滤：

-- 高效查询（命中索引）
SELECT * FROM orders 
WHERE create_time BETWEEN '2023-10-01' AND '2023-10-02'
ORDER BY create_time, order_no LIMIT 1000;

效果

时间范围查询耗时从 2.5 秒 降至 50 毫秒 。

4. 分页查询优化（游标分页）

问题分析 用户查看历史订单时，传统分页 LIMIT 1000000, 10 需遍历大量无效数据。

解决方案

基于自增主键的游标分页 前端传递最后一条记录的 id，后端通过 WHERE id > {last_id} 实现高效分页：

-- 第一页
SELECT * FROM orders 
WHERE user_id = 123 
ORDER BY id ASC 
LIMIT 10;

-- 后续页（前端传递 last_max_id=100）
SELECT * FROM orders 
WHERE user_id = 123 AND id > 100 
ORDER BY id ASC 
LIMIT 10;

效果

分页查询耗时从 3 秒 降至 10 毫秒 。

5. 时间回拨容错

问题分析 某次服务器时钟同步异常，导致生成重复订单号，引发数据不一致。

解决方案

时钟监控 + 异常等待 在雪花算法代码中增加时钟回拨检测，若回拨时间小于阈值（如 100ms），则等待时钟追平：

public synchronized long nextId() {
    long currentTimestamp = System.currentTimeMillis();
    if (currentTimestamp < lastTimestamp) {
        long offset = lastTimestamp - currentTimestamp;
        if (offset <= 100) {
            Thread.sleep(offset);  // 等待时钟追平
            currentTimestamp = System.currentTimeMillis();
        } else {
            throw new RuntimeException("Clock moved backwards!");
        }
    }
    // ...正常生成逻辑
}

效果

避免因时钟回拨导致的数据冲突，系统可用性提升。

总结

通过上述优化，该电商订单系统实现了：

写入性能提升 40% ，分页查询耗时降低 99% 。
彻底解决分库分表后的 ID 冲突问题。
时间范围查询效率提升 50 倍 。
系统具备时钟回拨容错能力，保障数据一致性。

技术选型对比

组件/策略	优化前	优化后
主键设计	雪花 ID 作为主键	自增主键 + 雪花 ID 冗余
Worker ID 分配	手动配置	ZooKeeper 动态分配
分页查询	LIMIT OFFSET	游标分页（基于自增 ID）
时间范围查询	解析雪花 ID 时间戳	显式时间字段 + 复合索引

通过结合业务需求（高并发写入、分布式扩展、高效查询），选择针对性优化策略，实现性能与稳定性的平衡。

面试场景题：MySQL分库分表性能优化

场景描述： 某电商平台的订单表采用分库分表设计，分为16个库，每个库64张表，分片键为用户ID的哈希值。随着业务增长，出现以下问题：

用户查询自己的订单列表时响应时间变长。
运营按时间范围统计订单的查询性能极差。
高峰期部分分库负载过高，导致延迟增加。

问题分析及优化方案：

用户订单查询响应时间长 原因分析：

索引缺失：用户查询订单时可能基于用户ID和时间排序，若分片内未建立 (user_id, create_time) 的联合索引，会导致全表扫描。
数据倾斜：某些用户订单量极大，导致其所在分片数据量远超其他分片，查询效率下降。
分片键限制：仅用用户ID哈希分片，未考虑时间维度，可能导致单分片内数据冷热不均。

优化方案：

联合索引优化： 在每个分片表中创建 (user_id, create_time) 的联合索引，加速排序和过滤。

ALTER TABLE orders_${shard} ADD INDEX idx_user_time (user_id, create_time);

冷热数据分离： 将历史订单（如6个月前）归档到独立的历史库，减少当前分片的数据量。
动态分片调整： 对超高频用户（如大卖家）单独分片或采用用户ID+时间复合分片键，分散压力。

时间范围统计查询性能差 原因分析： 时间范围查询需跨所有分片扫描数据，导致大量IO和网络开销，且无法利用分片键直接定位。

优化方案：

异步ETL到分析型数据库： 将订单数据同步至列式存储数据库（如ClickHouse）或Elasticsearch，专用于复杂查询。

# 使用DataX或Canal同步数据
canal.adapter -> ClickHouse

时间分片冗余 ：

在分库分表基础上，按月份分片（如 order_202310），结合用户ID哈希，实现双维度分片。

// 分片策略伪代码：userHash % 16 + 时间戳前缀（如202310）
String shardKey = userIdHash + "_" + timestampPrefix;

并行查询聚合： 在应用层并行查询所有分片，合并结果后返回，减少串行延迟。

CompletableFuture<List<Order>> future1 = queryShard(shard1, startTime, endTime);
CompletableFuture<List<Order>> future2 = queryShard(shard2, startTime, endTime);
// ...合并所有结果

高峰期部分分库负载过高

原因分析：

哈希不均匀：用户ID哈希分布不均，导致某些分片数据量或请求量过高。
热点用户：少数高频用户集中访问同一分片。

优化方案：

一致性哈希优化： 采用一致性哈希算法替代简单哈希，扩容时仅迁移部分数据，减少负载波动。
动态负载均衡 ：监控分片负载，自动迁移热点数据至空闲分片。
本地缓存+限流 ：对热点用户订单数据缓存到Redis，并设置限流策略（如令牌桶），防止击穿数据库。

更多最新面试题以及其他Java资料添加wx:jgszxp134258

出售java/测试/大数据/云计算/AI方向/Python/Go/前端/区块链相关全套课程,入门,进阶架构都有,价格合适

另出租咕泡以及马士兵账号,可以直接向讲师提问,时间为半月

有意私聊

posted @ 2025-03-27 20:23 敬畏、阅读(129) 评论(0) 收藏举报

刷新页面返回顶部

敬畏、

2025最新面试题-mysql面试题(二)

优化方案 ：

优化效果 ：

面试官提问 ：

回答模板 ：

MySQL索引失效性能优化案例

案例背景 ：

问题分析步骤 ：

优化方案 ：

优化效果 ：

面试回答技巧

面试官提问 ：

回答模板 ：

加分点 ：

常见索引失效场景归纳（面试备用） ：

实际项目场景：高并发电商订单系统 （雪花算法id与mysql结合的业务问题 ）

业务背景

1. 写入性能优化（索引分裂问题）

2. 分库分表 ID 冲突（Worker ID 分配）

3. 时间范围查询优化

4. 分页查询优化（游标分页）

5. 时间回拨容错

总结

面试场景题：MySQL分库分表性能优化

高峰期部分分库负载过高

公告

优化方案：

优化效果：

面试官提问：

回答模板：

案例背景：

问题分析步骤：

优化方案：

优化效果：

面试官提问：

回答模板：

加分点：

常见索引失效场景归纳（面试备用）：

实际项目场景：高并发电商订单系统（雪花算法id与mysql结合的业务问题）