NingG +

MySQL 最佳实践：分库分表

2014-10-20

概要

关于 MySQL 的分库分表，有几个基本问题：

  1. 为什么需要分库分表？解决什么问题？
  2. 如何分库分表？
  3. 分库分表，需要考虑哪些因素？

分库分表

为什么？

MySQL 的 InnoDB 存储引擎，使用 B+Tree 结构存储索引和数据，当单表的数据量很大时，出现几个现象：

  1. 单机硬件性能问题：
    1. 单台数据库的存储能力不够：单个 DB 的磁盘不足以存储大量数据；
    2. 单机的网络、CPU都有瓶颈
  2. 单机服务能力问题：
    1. 数据读取效率低：因为 B+Tree 树深度增加，数据读取效率降低；
    2. 事务并发效率低：并发更新、并发新增时，可能会锁表，限制了事务并发的效率，同时，数据量大时，索引的数据量也很大，更新索引的效率也很低；
  3. 升级扩展问题：
    1. 单表过大，限制了表结构调整相关操作，限制业务升级；

备注：

自增ID 与 锁，多事务并发新增数据时，自增ID，需要依赖加锁，来解决并发引发的一致性问题：

  1. 自增长计数器：auto-increment counter，每个包含自增长 ID 的表，都维护一个
  2. 表锁：对自增长计数器锁定，AUTO-INC Locking，是一种特殊的表锁：不是在事务提交后释放，而是在获取自增 ID 后，就释放
  3. 优化：使用互斥量（mutex），替代 表锁（AUTO-INC Locking），进行优化

怎么做？

进行分库分表，要考虑哪些因素呢？

  1. 路由规则：从业务角度分析，确定一条数据分配到哪个库、哪个表；
  2. 分库分表的维度：从业务角度出发，根据哪些字段，分库、分表？

分库分表，常见问题：

  1. 多维度的分库分表：从业务角度分析，可能会进行多维度的分库分表（举例：按照用户维度分表，但业务上，又需要根据商品维度查询）一般解决思路：
    1. 业务侧：双写，维持多维度的分库分表（数据冗余一份）
    2. 数据分级：主维度同步更新 + 次维度异步更新（bin log），解决多维度的分库分表
    3. 搜索中心：引入集中的搜索中心，解决多维度分表的业务需求，只维护单一维度的分库分表
  2. 联合查询：分库分表之后，对于联合查询问题，一般需要全表扫描，效率极低，建议使用搜索中心
  3. 跨库事务：从业务上，避免跨库事务，分布式事务的引入，会加大系统复杂度

核心问题：分库分表，单表多大合适?

实践经验：

整体思路

分库、分表，整体思路，有 2 个：

  1. 垂直分库：将单库中数据，拆到多个库中；例如，拆出：用户库、订单库等等；（综合业务，拆分为多个子业务）
  2. 水平分表：按照某个路由规则，将数据分散到多个子表中；

垂直分库：

水平分表：

补充：分区表

MySQL 5.1 版引入的分区是一种简单的水平分表方案：

  1. 建表的时候，加上分区参数，对应用透明的无需修改代码
  2. 分区表是一个独立的逻辑表，但是底层由多个物理子表组成
  3. 索引也是按照分区的子表定义，没有全局索引

特别说明：

分区表，因为物理上是多个子表，因此，可以为每个分区表单独指定数据文件和索引文件的存储路径。

  1. MyISAM 存储引擎：非聚簇索引，可以单独指定 2 个文件地址；
  2. InnoDB 存储引擎：聚簇索引，只需要指定一个数据文件地址即可；

创建分区表

分区表分为 RANGE,LIST,HASH,KEY 四种类型，并且分区表的索引是可以局部针对分区表建立的

创建分区表：

CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    amount DOUBLE NOT NULL,
    order_day DATETIME NOT NULL,
    PRIMARY KEY(id, order_day)
) ENGINE=Innodb PARTITION BY RANGE(YEAR(order_day)) (
    PARTITION p_2010 VALUES LESS THAN (2010),
    PARTITION p_2011 VALUES LESS THAN (2011),
    PARTITION p_2012 VALUES LESS THAN (2012),
    PARTITION p_catchall VALUES LESS THAN MAXVALUE);

这段语句表示将表内数据按照order_dy的年份范围进行分区：2010年一个区,2011一个,2012一个,剩下的一个.

Note：

替代方法就是使用HASH：

CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    amount DOUBLE NOT NULL,
    order_day DATETIME NOT NULL
) ENGINE=Innodb PARTITION BY HASH(id DIV 1000000);

这种分区表示每100W条数据建立一个分区,且没有阈值范围的影响.

补充说明：可以为每个分区表，设定单独的数据文件和索引文件位置，具体示例：

-- MyISAM 存储引擎
CREATE TABLE th (id INT, adate DATE)
engine='MyISAM'
PARTITION BY LIST(YEAR(adate))
(
  PARTITION p1999 VALUES IN (1995, 1999, 2003)
    DATA DIRECTORY = '/data/data'
    INDEX DIRECTORY = '/data/idx',
  PARTITION p2000 VALUES IN (1996, 2000, 2004)
    DATA DIRECTORY = '/data/data'
    INDEX DIRECTORY = '/data/idx',
  PARTITION p2001 VALUES IN (1997, 2001, 2005)
    DATA DIRECTORY = '/data/data'
    INDEX DIRECTORY = '/data/idx',
  PARTITION p2002 VALUES IN (1998, 2002, 2006)
    DATA DIRECTORY = '/data/data'
    INDEX DIRECTORY = '/data/idx'
);

-- InnoDB 存储引擎
CREATE TABLE thex (id INT, adate DATE)
engine='InnoDB'
PARTITION BY LIST(YEAR(adate))
(
  PARTITION p1999 VALUES IN (1995, 1999, 2003)
    DATA DIRECTORY = '/data/data',
  PARTITION p2000 VALUES IN (1996, 2000, 2004)
    DATA DIRECTORY = '/data/data',
  PARTITION p2001 VALUES IN (1997, 2001, 2005)
    DATA DIRECTORY = '/data/data',
  PARTITION p2002 VALUES IN (1998, 2002, 2006)
    DATA DIRECTORY = '/data/data'
);

使用分区表

使用上，跟普通表一样，无特约束。

分区表的优点

分区表的优点：

  1. 局部查询：根据查找条件，也就是 where后面的条件，查找只查找 部分分区
  2. 磁盘吞吐量：跨多个磁盘来分散数据查询，来获得更大的查询吞吐量；

参考来源

原文地址：https://ningg.top/mysql-best-practice-tips-split-database-and-table/
微信公众号 ningg, 联系我

同类文章:

微信搜索: 公众号 ningg, 联系我, 交个朋友.

京ICP备19013747号-1
Top