当前位置：首页 > news >正文

数据库与存储优化

news 2025/8/26 8:36:39

一、MySQL深度优化

索引优化
- B+树索引结构
  - 结构特点：
    - 平衡多路搜索树，所有数据存储在叶子节点，非叶子节点仅存键值和指针。
    - 叶子节点通过双向链表连接，支持范围查询高效遍历。
  - 优势：
    - 减少磁盘IO（高扇出，3~4层可存储千万级数据）。
    - 适合范围查询（如WHERE id BETWEEN 100 AND 200）。
- 覆盖索引
  - 定义：索引包含查询所需的所有字段，无需回表。
  - 示例：
```
-- 创建覆盖索引 
CREATE INDEX idx_cover ON user(name, age); 
-- 查询命中覆盖索引 
SELECT name, age FROM user WHERE name = 'Alice';
```
- 索引下推（ICP）
  - 原理：在存储引擎层过滤数据，减少回表次数。
  - 触发条件：
    - 查询条件包含索引列和非索引列。
    - 需在WHERE子句中使用索引前缀。
  - 查看ICP优化：
```
EXPLAIN SELECT * FROM user WHERE name = 'Alice' AND age > 20; -- Extra列显示"Using index condition"
```
锁机制
- 意向锁（Intention Locks）
  - 作用：快速判断表中是否存在行级锁，避免全表扫描。
  - 类型：
    - 意向共享锁（IS）：事务准备加行级共享锁。
    - 意向排他锁（IX）：事务准备加行级排他锁。
- 间隙锁（Gap Locks）
  - 功能：锁定索引记录间的间隙，防止幻读（Phantom Read）。
  - 示例：
```
-- 对id范围(5,10)加间隙锁 
SELECT * FROM user WHERE id > 5 AND id < 10 FOR UPDATE;
```
- 死锁排查
  - 步骤：
    1. 执行SHOW ENGINE INNODB STATUS，查看LATEST DETECTED DEADLOCK。
    2. 分析WAITING FOR THIS LOCK和HOLDS THE LOCK信息。
  - 解决：
    - 调整事务顺序，缩短事务时间。
    - 使用innodb_deadlock_detect = ON（默认开启）自动检测。
分库分表
- ShardingSphere分片策略
  - 取模分片：user_id % 4，数据均匀分布，但扩容需迁移数据。
  - 范围分片：按时间或ID范围分片，易导致数据倾斜。
  - 基因法路由：
    - 原理：将关联数据（如订单和订单明细）的基因值（如用户ID哈希）嵌入分片键，确保关联查询在同一分片。
    - 示例：
```
-- 订单表分片键 = user_id % 8 
-- 订单明细表分片键 = (order_id的基因部分) % 8
```

二、大数据存储优化

HBase
- LSM树结构
  - 写入流程：
    1. 数据先写入内存（MemStore）。
    2. MemStore满后刷写到磁盘（HFile）。
    3. 后台合并（Compaction）HFile，减少文件数。
  - 优势：高吞吐写入，适合时序数据。
- Region分裂机制
  - 触发条件：Region大小超过阈值（默认10GB）。
  - 分裂策略：
    - IncreasingToUpperBound：动态调整分裂阈值。
    - Disabled：手动控制分裂。
- RowKey设计
  - 原则：
    - 散列化：避免热点（如MD5(user_id).substr(0,4) + user_id）。
    - 有序性：时间戳反转（Long.MAX_VALUE - timestamp）。
  - 示例：
```
RowKey = 盐值（4位） + 用户ID + 时间戳反转
```
Elasticsearch
- 倒排索引
  - 结构：词项（Term） → 文档ID列表。
  - 优化：
    - 使用keyword类型避免分词开销。
    - 合并段（Force Merge）减少查询时的段数量。
- 分词器原理
  - 标准分词器：按空格和标点切分，过滤停用词。
  - IK分词器：
    - ik_smart：粗粒度切分（如“清华大学” → “清华大学”）。
    - ik_max_word：细粒度切分（如“清华大学” → “清华”,“大学”）。
  - 自定义词典：
```
PUT /my_index
{"settings": {"analysis": {"analyzer": {"my_ik": {"type": "custom","tokenizer": "ik_max_word","filter": ["my_stopwords"]}},"filter": {"my_stopwords": {"type": "stop","stopwords": ["的", "是"]}}}}
}
```
- DSL优化技巧
  - 避免深分页：使用search_after代替from/size。
  - 过滤器上下文：将term查询放入filter，利用缓存。
  - 冷热数据分离：按时间范围分索引，热数据使用SSD存储。
```
GET /logs-2023/_search
{"query": {"bool": {"filter": [{ "range": { "@timestamp": { "gte": "now-1d/d" }}}]}},"sort": [{"@timestamp": "desc"}],"size": 10,"search_after": [1698765432000]
}
```