数据库与存储优化

JAY.LIN 收录于未分类

2025-03-06 约 1488 字预计阅读 3 分钟

https://bing.ee123.net/img/rand?artid=146078062

数据库与存储优化

一、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]
}

三、总结与实战建议

MySQL优化重点 ：
- 索引设计遵循最左前缀原则，避免冗余索引。
- 分库分表优先考虑基因法路由，减少跨分片查询。
HBase核心实践 ：
- RowKey设计需平衡散列与查询需求。
- 预分区（Pre-split）避免自动分裂带来的性能波动。
Elasticsearch调优 ：
- 使用 _bulk 接口批量写入，提升吞吐量。
- 定期清理旧索引，结合ILM（索引生命周期管理）自动化。

通过结合业务场景选择合适的存储方案，并持续监控关键指标（如MySQL的QPS、Elasticsearch的段合并频率），可显著提升系统性能和稳定性。

目录

数据库与存储优化

数据库与存储优化

一、MySQL深度优化

二、大数据存储优化

三、总结与实战建议