黄磊的博客 - 数据库
type: channel
link: https://www.huangleicole.com/category/database/
items: 数据库性能瓶颈破解：从慢查询优化到索引哲学
items: Redis持久化策略：AOF与RDB的抉择与性能调优
items: 缓存穿透、击穿、雪崩：一套组合拳解决方案的实战思考
items: 如何为你的MySQL数据库表设计高效且合理的索引（不只是CREATE INDEX）
items: 缓存应用：从频繁查库到高效缓存——我与Redis一起走过的坑与路
items: 数据库优化：从慢查询到高性能——我在索引和SQL优化上的实战心得

数据库性能瓶颈破解：从慢查询优化到索引哲学
type: item
date: 2024-11-10T16:26:00+08:00
description: AI摘要 作者从一次本地查询卡顿出发，通过EXPLAIN洞察全表扫描，用高区分度联合索引把10万行秒级降到毫秒，再总结最左前缀、列计算、隐式转换等失效陷阱，最终升华为“索引是空间换时间的设计债”，养成写SQL即脑补索引的本能。 此内容根据文章生成，仅用于文章内容的解释与总结 还记得刚开始工作那会儿，我对数据库所有的理解，就是“一个能存东西和取东西的黑盒子”。我的工作就是把SQL拼出来，能跑出正确的结果，就谢天谢地。性能？那是什么东西？直到我亲手搞垮了自己的本地开发环境。那是一次再普通不过的开发。我需要做一个后台功能：根据时间范围、状态和用户ID来筛选订单列表。功能很简单，我熟练地写出了SELECT * FROM orders WHERE user_id = ? AND status = ? AND create_time BETWEEN ? AND ?，然后在我的本地数据库里——它只有几千条测试数据——完美运行。然后，我开始往数据库里灌数据，模拟线上规模。当我导入到大概十万条订单数据时，再次点下那个查询按钮。我的IDE没有卡死，但那个进度条在那里转啊转，转了整整三秒钟。三秒钟，对于一个本该是毫秒级响应的操作来说，像一个世纪那么长。那种“慢”，不是生产环境告警的尖锐刺耳，而是一种私下的、只有我自己能察觉的“钝痛”。 它挑战了我作为一个程序员的尊严：我写的代码，连我自己都觉得慢。就是这份“耻感”，逼着我走上了优化之路。第一站：打开黑盒——学会“看见”数据库在做什么我的第一个老师，是 EXPLAIN 命令。在很长一段时间里，我觉得这个命令的输出是天书。直到我强迫自己把每一次查询都EXPLAIN一下，并像解谜一样去理解每一行输出。第一次深刻的教训，是关于“全表扫描”（Type: ALL）。我的那个订单查询，EXPLAIN的结果里，type字段明明白白地写着ALL，rows字段显示的是103221。这意味着什么？意味着数据库为了我这条小小的查询，像个愣头青一样，把十万多行数据从头到尾扫了一遍！这就好比你要在一本没有目录、没有索引、页码还是乱序的百万字小说里找一句特定的对话，你只能一页一页地用眼睛扫。为什么它要这么做？ 因为我当时的WHERE条件涉及三个字段：user_id, status, create_time。而我的表上，没有任何索引。数据库没有快速定位数据的能力，全表扫描是它唯一的选择。这个视觉化的理解（想象数据库在逐行扫描）是一个决定性的瞬间。我不再觉得数据库是魔法，它只是一个按规则办事的软件。而我的工作，就是为它制定更高效的规则。第二站：递出手术刀——创建第一个有效的索引知道了全表扫描是元凶，解决方案似乎显而易见：加索引。但加索引，也是一门学问。我踩的第一个坑就是：在status字段上建了一个单列索引。结果令人失望。EXPLAIN显示，索引确实被用上了（type: ref），但rows扫描行数只下降了一点点，从10万降到了几万。为什么？因为status字段的“区分度”太低了。订单状态无非就是“待付款”、“已付款”、“已完成”等寥寥几种。数据库通过status=’已付款’这个条件，依然要扫描几万行数据，然后再在这几万里去筛选user_id和create_time。我学到的第一个核心哲学：索引的价值在于其“筛选能力”，也就是区分度。 应该选择那些值尽可能唯一的字段来创建索引。于是，我删掉了那个鸡肋的status索引，创建了一个联合索引：idx_user_time (user_id, create_time)。为什么是这个顺序？user_id的区分度极高，能快速将数据范围从一个用户的十万条订单，缩小到某个特定用户的几十上百条订单。在同一个用户的几十条订单中，再按create_time进行排序和范围查询，就变得非常高效。这次，效果是颠覆性的。EXPLAIN的结果中，type变成了令人愉悦的range，而rows从十万级骤降到两位数。查询速度从秒级降到了毫秒级。第三站：与索引的脾气打交道——理解它的规则与边界成功的喜悦是短暂的，很快我就遇到了索引“失效”的诡异情况。案例一：最左前缀原则——电话簿的启示我创建了索引 (a, b, c)。但我的查询条件是 WHERE b = ? AND c = ?。索引又失效了！全表扫描再次出现。我苦苦思索，直到有人用“电话簿”的比喻点醒了我：联合索引 (姓氏， 名字) 就像一本电话簿，它是先按姓氏字母顺序排，同姓氏的再按名字排。你想直接找所有叫“小明”的人，这本按姓氏排序的电话簿就无能为力了，因为你不知道“小明”们分布在哪些姓氏下面。这就是最左前缀原则。我的索引是(user_id, create_time)，如果查询条件没有user_id，直接跳到了create_time，数据库就无法有效利用这棵索引树。…

Redis持久化策略：AOF与RDB的抉择与性能调优
type: item
date: 2023-11-29T22:10:00+08:00
description: AI摘要 Redis持久化需权衡RDB快照与AOF日志：RDB紧凑、恢复快，适合可丢分钟级数据的缓存；AOF每秒落盘，丢数少，但文件大、重启慢。生产最优方案是Redis 4+混合持久化：重写时先RDB再追加增量AOF，兼顾安全与速度；按业务容忍度调save、fsync、重写参数，并监控内存与磁盘，避免fork内存暴涨及AOF膨胀。 此内容根据文章生成，仅用于文章内容的解释与总结 作为开发者，我们喜欢Redis的速度。但当运维同学问起"这个Redis数据能丢吗？"，或者发现测试环境重启后缓存全空时，我们才真正开始关心持久化这个问题。Redis提供了两种主要的持久化方式：RDB（快照）和AOF（追加日志）。它们不是二选一的关系，而是需要根据业务场景精心搭配的工具。让我们抛开理论，从实际开发中的痛点出发，深入理解这两种策略。一、RDB（快照）：定时存档的游戏策略想象一下你在玩一个复杂的游戏。RDB就像是在特定节点（比如打完一个Boss后）手动存档。存档瞬间可能会卡一下，但之后如果游戏崩溃，你可以从这个存档点重新开始，只是会丢失存档后的进度。1. RDB是如何工作的？RDB的核心是创建某个时间点的数据快照。在Redis中，当触发保存条件时，它会fork出一个子进程。这个子进程拥有父进程内存数据的拷贝，然后负责将数据写入一个临时的RDB文件，写入完成后替换旧文件。关键配置（redis.conf）：# 保存条件：900秒内至少1个key变化，300秒内至少10个变化，60秒内至少10000个变化 save 900 1 save 300 10 save 60 10000 # 如果后台保存失败，是否停止接收写操作（一种保护机制） stop-writes-on-bgsave-error yes # 是否压缩RDB文件（压缩会消耗CPU，但节省磁盘空间） rdbcompression yes2. 开发中遇到的RDB典型问题问题场景：内存占用翻倍？当你执行SAVE（同步保存，会阻塞）或BGSAVE（后台保存）时，如果数据集很大（比如10GB），你会发现Redis的内存占用突然增加到近20GB。这是因为fork操作使用了写时复制（Copy-on-Write）机制。虽然子进程理论上共享内存，但只要父进程有写入操作，被修改的内存页就需要被复制，导致内存增长。解决方案与调优：避免在内存紧张时执行BGSAVE：监控系统内存，选择业务低峰期进行持久化。调整保存频率：如果数据不是特别关键，可以放宽保存条件，比如只保留save 3600 1（1小时内有变化就保存），减少fork频率。使用独立进程：对于超大数据集，可以考虑关闭Redis的RDB，使用外部工具（如redis-rdb-tools）定期生成快照。3. RDB的优势与适用场景优势：紧凑的单文件：非常适合用于备份和灾难恢复。你可以把某个时间点的RDB文件拉到另一个Redis实例直接恢复。最大化Redis性能：父进程除了fork瞬间有阻塞，其余时间全力处理请求，持久化由子进程完成。更快的重启恢复：恢复大数据集时，直接加载RDB比回放AOF日志要快得多。适用场景：允许分钟级数据丢失的缓存场景。需要做历史数据快照用于数据分析。磁盘空间紧张的环境。二、AOF（追加日志）：记录每一个操作的日记如果RDB是游戏存档，那AOF就像是把你游戏中的每一个操作（"向前走三步"、"向左转"、"攻击"）都记录下来。恢复时，只需要按照记录重新执行一遍所有操作即可。1. AOF是如何工作的？AOF将每一个会修改数据集的写命令（如SET, HSET, SADD）追加到文件末尾。当Redis重启时，会重新执行AOF文件中的所有命令来重建数据。关键配置：# 开启AOF appendonly yes # AOF文件名 appendfilename "appendonly.aof" # 同步策略：always-每个命令都同步，everysec-每秒同步，no-由操作系统决定 appendfsync everysec # AOF重写触发条件：当前AOF文件比上次重写后的大小增长了100%，并且至少64MB auto-aof-rewrite-percentage 100 auto-aof-rewrite-min-size 64mb2. 开发中遇到的AOF典型问题问题场景：AOF文件膨胀与恢复缓慢我们有一个存储用户会话的Redis，平时只是简单的SETEX和GET。但有一天，我发现AOF文件竟然有几十个GB，而实际数据量可能只有几百MB。更糟糕的是，Redis重启后，恢复过程极其缓慢。根源分析：这是因为很多Redis命令是重复的。比如一个计数器键user:100:count，被INCR了1000次。在AOF文件中就会有1000条INCR命令。但实际上，…

缓存穿透、击穿、雪崩：一套组合拳解决方案的实战思考
type: item
date: 2021-12-26T19:52:00+08:00
description: AI摘要 文章以一次大促雪崩事故切入，系统梳理缓存穿透、击穿、雪崩三大问题，给出布隆过滤器、互斥锁、逻辑过期、多级缓存、随机TTL、预热、熔断等组合拳式解决方案，并附完整Java代码与秒杀实战，强调监控、降级及先写策略的重要性，最终形成可复制的缓存防御体系。 此内容根据文章生成，仅用于文章内容的解释与总结 我以为缓存只是简单的查不到就去数据库，直到一次大促期间的缓存连环失效，让整个系统雪崩式崩溃，我才意识到缓存设计的复杂性远不止表面那么简单。一、事故现场：从缓存失效到系统崩溃的全过程1.1 问题代码还原先看看当时有问题的缓存实现：@Service public class ProductService { @Autowired private RedisTemplate<String, Product> redisTemplate; @Autowired private ProductMapper productMapper; /** * 获取商品详情 - 最初的简单实现 */ public Product getProduct(Long productId) { // 1. 先查缓存 String cacheKey = "product:" + productId; Product product = redisTemplate.opsForValue().get(cacheKey); if (product != null) { return product; } // 2. 缓存没有，查数据库 product = productMapper.selectById(productId); // 3. 写入缓存 if (product != null) { redisTemplate.opsForValue().set(cacheKey, product, 5, TimeUnit.MINUTES); } return product; } /** * 热门商品秒杀接口 */ @Transactional public boolean seckill(Long productId, Integer userId) { // 检查库存 Product product = getProduct(productId); // 使用上面的缓存方法 if (product.getStock() <= 0) { return false; } // 扣减库存 int rows = productMapper.decreaseStock(productId); if (rows > 0) { // 更新缓存中的库存 product.setStock(product.getStock() - 1); redisTemplate.opsForValue().set("product:" + productId, product, 5, TimeUnit.MINUTES); // 创建订单 createOrder(productId, userId); return true; } return false; } }1.2 事故时间线分析timeline title 缓存雪崩事故时间线 section 事故前 00:00 : 开始预热缓存 大部分商品缓存5分钟过期 00:05 : 第一波缓存同时失效 section 事故中 00:05 : 大量请求穿透到数据库 00:06 : 数据库连接池耗尽 00:07 : 应用服务器线程池打满 00:10 : 订单服务完全瘫痪 section 事故后 00:30 : 人工介入重启服务 01:00 : 逐步恢复服务二、缓存穿透：当查询不存在的数据时2.1 什么是缓存穿透？缓存穿透是指​查询一个一定不存在的数据​，由于缓存中不命中，每次都要去数据库查询，失去了缓存的意义。​攻击场景​：恶意用户用不存在的商品ID频繁请求。// 模拟攻击代码 public class CachePenetrationAttack { public void attack() { // 攻击者使用不存在的ID发起请求 for (int i = 1000000; i < 1000100; i++) { // 这些ID在数据库中都不存在 productService.getProduct((long) i); } } }2.2 布隆过滤器实战方案我实现的布隆过滤器解决方案：@Component public class BloomFilterService { private final BitSet bitSet; private final int size = 1000000; // 位图大小 private final int[]…

如何为你的MySQL数据库表设计高效且合理的索引（不只是CREATE INDEX）
type: item
date: 2021-04-19T19:28:00+08:00
description: AI摘要 文章从B+Tree底层剖析MySQL索引机制，提出“最左前缀、覆盖索引、高区分度”三大设计原则，结合ICP、回表、失效场景与EXPLAIN诊断流程，给出慢查询→验证→监控的闭环实战方法，强调索引设计是理解数据访问模式后的权衡艺术。 此内容根据文章生成，仅用于文章内容的解释与总结 作为开发者，我们每天都在用索引，但你是否曾遇到：明明已经加了索引，查询却依然慢如蜗牛？EXPLAIN命令的结果让人一头雾水？这通常是因为我们只学会了 CREATE INDEX的语法，却并未真正理解索引背后的工作原理和设计哲学。今天，我将从底层原理出发，带你走完索引设计的完整闭环。一、理解本质：索引是如何工作的？你可以把数据库想象成一本书，而索引就是这本书的​目录​。​全表扫描​：没有目录时，你想找某个主题的内容，只能一页一页地翻整本书。这就是 type: ALL的查询。​索引扫描​：有了目录，你可以快速定位到主题所在的页码，直接翻到那一页。这就是索引的作用。在MySQL（InnoDB引擎）中，这个“目录”主要采用 B+Tree​ 数据结构。理解它的特点至关重要：​有序性​：数据在叶子节点上是按顺序存储的（就像目录按拼音排序）。这是范围查询和排序高效的基础。​多路平衡​：树形结构很矮胖，通常只需要3-4次磁盘I/O就能从亿级数据中定位到记录。​叶子节点存储数据​：在InnoDB中，​主键索引（聚簇索引）的叶子节点直接存储了完整的行数据​。而​二级索引（非聚簇索引）的叶子节点存储的则是主键的值​。​关键推论​：通过二级索引查询时，数据库要先在二级索引的B+Tree中找到主键值，再拿着这个主键值回到主键索引的B+Tree中查找完整的行数据。这个过程称为​回表​。回表意味着额外的磁盘I/O，是性能杀手之一。二、设计索引的核心原则（实战心法）原则1：最左前缀原则 —— 组合索引的基石这是组合索引设计的黄金法则。假设有一个联合索引 INDEX idx_name_age_city (name, age, city)。​工作方式​：这个索引的排序规则是：先按 name排序，name相同再按 age排序，age相同再按 city排序。哪些查询能用到索引？✅ WHERE name = '张三'✅ WHERE name = '张三' AND age = 25✅ WHERE name = '张三' AND age > 20 AND city = '北京'（注意：age是范围查询后，city无法用索引排序，但可以用于过滤）✅ ORDER BY name, age（可以利用索引排序，避免filesort）哪些查询用不到或只能部分使用？❌ WHERE age = 25（跳过了 name，就像你无法使用按姓氏排序的目录直接查找所有25岁的人）❌ WHERE city = '北京'（同理）❌ WHERE name LIKE '%三'（通配符开头，无法利用有序性）​设计启示​：建立组合索引时，应​将等值查询的、区分度高的列放在最左边​。原则2：覆盖索引 —— 避免回表的终极武器如果一个索引包含（或称“覆盖”）了查询语句需要返回的所有字段，那么数据库就不需要回表，可以直接从索引中取得数据，效率极高。​例子​：表结构：users(id(PK), name, age, city)​查询1（需要回表）​：SELECT * FROM users WHERE name = '张三';即使有 INDEX(name)，也需要回表去主键索引取 age和 city字段。​查询2（覆盖索引，无需回表）​：SELECT id, name FROM users WHERE name = '张三';索引 INDEX(name)的叶子节点已经包含了 id和 name，直接返回，性能极佳。​查询3（优化为覆盖索引）​：-- 原查询，需要回表 SELECT id, name, age FROM users WHERE name = '张三' AND age > 20; -- 优化方案：建立覆盖索引 CREATE INDEX idx_name_age ON users(name, age); -- 或（如果id是必需的） CREATE INDEX idx_name_age_id ON users(name, age, id);这个 idx_name_age索引可以同时满足 WHERE条件的过滤，并且直接提供 age字段，如果查询的字段只有 id, name, age，则完全避免回表。在 Extra列会出现 ​Using index​。​设计启示​：对于高频查询，尽量设计成覆盖索引，这是最有效的优化手段之一。原则3：索引的区分度（基数）区分度 = 不重复的索引值数量 / 总记录数。区分度越高，索引过滤掉的数据就越多…

缓存应用：从频繁查库到高效缓存——我与Redis一起走过的坑与路
type: item
date: 2019-12-27T18:23:00+08:00
description: AI摘要 文章总结作者两年Redis实战踩坑经验，涵盖Cache-Aside等模式、穿透/击穿/雪崩三杀解决方案、分布式锁与一致性策略，并给出排行榜、UV统计、集群监控等代码样例，最终提炼七条最佳实践。 此内容根据文章生成，仅用于文章内容的解释与总结 ​ 本文将详细分享我在Redis缓存应用中的实战经验，包括缓存设计模式、缓存穿透/击穿/雪崩解决方案、分布式锁实现、缓存与数据库一致性保证等。每个知识点都配有真实的业务场景和代码实现。1. 缓存设计模式选择Cache-Aside模式（最常用）：@Service public class ProductService { private static final String PRODUCT_KEY_PREFIX = "product:"; private static final Duration CACHE_TTL = Duration.ofHours(2); public Product getProductById(Long id) { // 1. 先查缓存 String cacheKey = PRODUCT_KEY_PREFIX + id; Product product = redisTemplate.opsForValue().get(cacheKey); if (product != null) { return product; } // 2. 缓存未命中，查数据库 product = productMapper.selectById(id); if (product != null) { // 3. 写入缓存 redisTemplate.opsForValue().set(cacheKey, product, CACHE_TTL); } return product; } public void updateProduct(Product product) { // 1. 更新数据库 productMapper.update(product); // 2. 删除缓存（后续查询会自动重建） String cacheKey = PRODUCT_KEY_PREFIX + product.getId(); redisTemplate.delete(cacheKey); } }Read-Through/Write-Through模式：@Component public class CacheManager { @Autowired private RedisTemplate<String, Object> redisTemplate; /** * 读穿透：缓存不存在时自动加载 */ public <T> T getOrLoad(String key, Class<T> type, Supplier<T> loader, Duration ttl) { T value = (T) redisTemplate.opsForValue().get(key); if (value != null) { return value; } // 分布式锁，防止缓存击穿 String lockKey = "lock:" + key; if (tryLock(lockKey)) { try { // 双重检查，防止重复加载 value = (T) redisTemplate.opsForValue().get(key); if (value != null) { return value; } // 加载数据 value = loader.get(); if (value != null) { redisTemplate.opsForValue().set(key, value, ttl); } return value; } finally { releaseLock(lockKey); } } else { // 未获取到锁，短暂等待后重试 try { Thread.sleep(100); return getOrLoad(key, type, loader, ttl); } catch (InterruptedException e) { Thread.currentThread().interrupt(); return loader.get(); // 降级为直接查询 } } } }2. 缓存问题解决方案缓存穿透解决方案：@Service public class ProductService { /** * 解决缓存穿透：缓存空值+布隆过滤器 */ public Product getProductByIdWithProtection(Long id) { // 1. 参数校验…

数据库优化：从慢查询到高性能——我在索引和SQL优化上的实战心得
type: item
date: 2019-12-09T14:31:00+08:00
description: AI摘要 作者以MySQL为例，分享从盲目加索引到复合索引、EXPLAIN分析、深度分页、统计缓存、JOIN与反范式、读写分离、分库分表等实战技巧，强调持续监控与业务驱动的平衡优化。 此内容根据文章生成，仅用于文章内容的解释与总结 本文将深入分享我在MySQL数据库优化方面的实战经验，包括索引设计原则、EXPLAIN执行计划解读、慢查询优化、分库分表策略等。每个优化技巧都配有真实的SQL示例和性能对比数据。1. 索引设计：从盲目添加到了解原理初期错误的索引使用：-- 错误示范：盲目添加索引 ALTER TABLE `order` ADD INDEX idx_user_id (`user_id`); ALTER TABLE `order` ADD INDEX idx_status (`status`); ALTER TABLE `order` ADD INDEX idx_create_time (`create_time`); -- 实际查询语句 SELECT * FROM `order` WHERE user_id = 123 AND status = 'PAID' AND create_time BETWEEN '2019-01-01' AND '2019-01-31';​问题分析：​​ 多个单列索引，MySQL只能选择一个最优的，其他条件需要回表查询。正确的复合索引设计：-- 删除多余的单列索引，创建复合索引 ALTER TABLE `order` DROP INDEX idx_user_id, DROP INDEX idx_status, DROP INDEX idx_create_time; -- 创建复合索引，字段顺序很重要！ ALTER TABLE `order` ADD INDEX idx_user_status_time (`user_id`, `status`, `create_time`);复合索引设计原则：​最左前缀原则​：索引从左到右匹配​等值查询在前​：=、IN等条件放前面​范围查询在后​：BETWEEN、>、<等放后面​区分度高的在前​：选择性高的列放前面2. EXPLAIN执行计划深度解读实战分析：EXPLAIN SELECT o.*, u.username FROM `order` o LEFT JOIN `user` u ON o.user_id = u.id WHERE o.status = 'PAID' AND o.amount > 100 AND o.create_time >= '2019-01-01' ORDER BY o.create_time DESC LIMIT 20;EXPLAIN结果解读：+----+-------------+-------+------------+------+---------------+---------+---------+-------+--------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+---------+---------+-------+--------+----------+---------------------------------------+ | 1 | SIMPLE | o | NULL | ref | idx_status | idx_status | 1023 | const | 15680 | 33.33 | Using index condition; Using filesort | | 1 | SIMPLE | u | NULL | eq_ref| PRIMARY | PRIMARY | 4 | o.user_id | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+---------+---------+-------+--------+----------+---------------------------------------+关键字段分析：​type​: ref（普通索引扫描）vs eq_ref（主键/唯一索引）​key​: 实际使用的索引​rows​: 预估扫…