数据库与存储优化

一、MySQL深度优化

1. 索引优化

   • 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"

2. 锁机制

   • 意向锁（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（默认开启）自动检测。

3. 分库分表

   • ShardingSphere分片策略
     • 取模分片：user_id % 4，数据均匀分布，但扩容需迁移数据。
     • 范围分片：按时间或ID范围分片，易导致数据倾斜。
     • 基因法路由：
       • 原理：将关联数据（如订单和订单明细）的基因值（如用户ID哈希）嵌入分片键，确保关联查询在同一分片。
       • 示例：

         -- 订单表分片键 = user_id % 8 
         -- 订单明细表分片键 = (order_id的基因部分) % 8

---

二、大数据存储优化

1. 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 + 时间戳反转

2. 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的段合并频率），可显著提升系统性能和稳定性。