MySQL教程 2023-05-08

MySQL分表垂直水平和组合的三种分表方案（mysql三种分表）

MySQL分表：垂直、水平和组合的三种分表方案

MySQL是当前应用最广泛的关系型数据库管理系统之一，它支持多种分表方案，其中较为常见的就是垂直、水平和组合三种分表方案。下文将详细介绍这三种方案的特点和实现方法，并给出相应示例。

1. 垂直分表

垂直分表方案是指将一张表中的列按照一定的规则划分到不同的表中，每个表只存储一部分列。这样的好处在于可以将访问频率低的列从主表中提取出来，减少了单张表的列数和行数，从而优化查询效率。

举个例子，假设一个人员表包含以下列：id、name、age、gender、address、phone、eml。我们可以将id、name和age从主表中分离出来，新建一张名为person_basic的表，只存储这三个列的信息。这样在查询person_basic表的时候，无需扫描其它的列，查询速度更快。

下面给出一个简单的垂直分表示例：

CREATE TABLE person (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  age INT(11) NOT NULL,
  gender CHAR(1) NOT NULL DEFAULT 'M',
  address VARCHAR(100),
  phone VARCHAR(20),
  eml VARCHAR(50),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE person_basic (
  id INT(11) NOT NULL,
  name VARCHAR(20) NOT NULL,
  age INT(11) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

通过上面的示例可以看出，我们创建了一个person表和一个person_basic表，其中person表包含全部列，而person_basic表只包含id、name和age三个列。

2. 水平分表

水平分表方案是指将一张表中的数据按照一定的规则划分到不同的表中，每个表存储一部分行数据。这样的好处在于可以将一张表的数据分散到多个物理存储位置上，同时也可以减少单张表的数据量，提高查询效率。

举个例子，假设一个订单表包含以下列：id、user_id、product_id、price、quantity、created_at、updated_at。我们可以将订单表按照用户ID先后顺序分成多张表，比如按照用户ID的末两位分成100张表，每张表存储一个范围内的用户数据。这样在查询某个用户的订单时，只需查询对应的数据表，而不需要查询整张表，查询速度更快。

下面给出一个简单的水平分表示例：

CREATE TABLE orders (
  id INT(11) NOT NULL AUTO_INCREMENT,
  user_id INT(11) NOT NULL,
  product_id INT(11) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  quantity INT(11) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE orders_00 (
  id INT(11) NOT NULL AUTO_INCREMENT,
  user_id INT(11) NOT NULL,
  product_id INT(11) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  quantity INT(11) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE orders_01 (
  id INT(11) NOT NULL AUTO_INCREMENT,
  user_id INT(11) NOT NULL,
  product_id INT(11) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  quantity INT(11) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
...... // 其他表的创建省略，一共创建100张表

CREATE PROCEDURE insert_order (
  orderId INT,
  userId INT,
  productId INT,
  price DECIMAL(10,2),
  quantity INT,
  createdAt DATETIME,
  updatedAt DATETIME
)
BEGIN
  DECLARE tableId INT;
  SET tableId = userId % 100;
  SET @sql = CONCAT('INSERT INTO orders_', LPAD(tableId, 2, '0'), ' VALUES (?, ?, ?, ?, ?, ?, ?)');
  PREPARE stmt FROM @sql;
  EXECUTE stmt USING orderId, userId, productId, price, quantity, createdAt, updatedAt;
  DEALLOCATE PREPARE stmt;
END;

通过上面的示例可以看出，我们创建了一个orders表和100张orders_XX表，其中XX表示当前表存储的用户ID的末两位数字，每张表存储一部分订单数据。我们还创建了一个insert_order存储过程，用来将订单数据插入对应的订单表中，插入时需要根据用户ID的末两位数字来决定插入哪个表。

3. 组合分表

组合分表方案是指将垂直和水平分表方案组合在一起，将一张表按照不同的规则分散到多张表中。这样可以将两种方案的优势结合起来，进一步提高查询效率。

举个例子，假设我们已经进行了垂直分表和水平分表，现在有一个需求是查询某个用户的姓名和订单数量。我们可以先查找此用户的ID，然后通过此ID查询订单表中对应的数据，最终将两张表的数据合并在一起，即可得到查询结果。

下面给出一个简单的组合分表示例：

CREATE TABLE person_basic (
  id INT(11) NOT NULL,
  name VARCHAR(20) NOT NULL,
  age INT(11) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE orders_00 (
  id INT(11) NOT NULL AUTO_INCREMENT,
  user_id INT(11) NOT NULL,
  product_id INT(11) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  quantity INT(11) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE PROCEDURE query_name_and_order_count (
  userId INT
)
BEGIN
  DECLARE tableName VARCHAR(20);
  DECLARE userName VARCHAR(20);
  DECLARE orderCount INT;
  SET @sql = CONCAT('SELECT name FROM person_basic WHERE id = ', userId);
  PREPARE stmt1 FROM @sql;
  EXECUTE stmt1 INTO userName;
  DEALLOCATE PREPARE stmt1;
  SET tableName = CONCAT('orders_', LPAD(userId % 100, 2, '0'));
  SET @sql = CONCAT('SELECT COUNT(*) FROM ', tableName, ' WHERE user_id = ', userId);
  PREPARE stmt2 FROM @sql;
  EXECUTE stmt2 INTO orderCount;
  DEALLOCATE PREPARE stmt2;
  SELECT userName, orderCount;
END;

通过上面的示例可以看出，我们创建了一个person_basic表和多张orders_XX表，其中每张表存储一部分订单数据。我们还创建了一个query_name_and_order_count存储过程，用来查询指定用户的姓名和订单数量。过程中先查询person_basic表得到用户姓名，然后根据用户ID的末两位数字从对应的订单表中查询订单数量，最终将两个结果合并得到查询结果。

综上所述，MySQL提供了多种分表方案，包括垂直、水平和组合三种方案。在实际应用中，可以根据数据特点和查询需求选择合适的分表方案，以提高数据库性能和查询效率。

数据运维技术 » MySQL分表垂直水平和组合的三种分表方案（mysql三种分表）

分享到：

相关推荐