MySQL数据库的分表分库策略主要是为了应对海量数据的存储和管理,不仅可以提高数据库的查询效率,还可以降低单个数据库的存储压力。
数据库分库分表的策略主要有以下几种:
分库分表规则
水平分库
水平分库是将一个数据库中的数据,在不同的服务器上分别存储。可以根据业务需要将相同的表拆分到不同的服务器上。例如,一个电商业务可能有用户数据库、订单数据库和日志数据库等。将这三个库放在不同的服务器上,可以有效的提高系统的性能。同时水平分库还可以提高系统的可用性,当某一个服务器宕机时,系统仍然可以继续向正常的服务器发送请求。
水平分库的示例:
--创建users库
CREATE SCHEMA users;
--在users库中创建user表
CREATE TABLE users.user(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
PRIMARY KEY (id)
) ENGINE = InnoDB;
--在users库中创建order表
CREATE TABLE users.order(
id INT NOT NULL AUTO_INCREMENT,
user_id INT,
price DECIMAL(10,2),
PRIMARY KEY (id),
KEY idx_user_id (user_id)
) ENGINE = InnoDB;
--在users库中创建log表
CREATE TABLE users.log(
id INT NOT NULL AUTO_INCREMENT,
user_id INT,
content TEXT,
PRIMARY KEY (id),
KEY idx_user_id (user_id)
) ENGINE = InnoDB;
垂直分库
垂直分库是将一个数据库中的数据,按照不同的业务类型,分别存在不同的数据库中。例如,一个数据可能包含了用户信息、订单信息、商品信息等多种业务类型,将这些业务类型分别存在不同的数据库中,可以降低单一数据库的存储压力。
垂直分库的示例:
--创建user库
CREATE SCHEMA user;
--在user库中创建users表
CREATE TABLE user.users(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
age INT,
PRIMARY KEY (id)
) ENGINE = InnoDB;
--在product库中创建product表
CREATE SCHEMA product;
CREATE TABLE product.product(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
price DECIMAL(10,2),
PRIMARY KEY (id)
) ENGINE = InnoDB;
--在order库中创建order表
CREATE SCHEMA order;
CREATE TABLE order.order(
id INT NOT NULL AUTO_INCREMENT,
user_id INT,
product_id INT,
amount INT,
PRIMARY KEY (id),
KEY idx_user_id (user_id),
KEY idx_product_id (product_id)
) ENGINE = InnoDB;
水平分表
水平分表是将一个表中的数据,拆分成多个表,并且这些表的结构相同。例如,一个电商业务的订单表(order)可能存储了大量的订单数据,将这个表拆分成多个表(例如,按年、按月、按日),可以有效的提高系统的查询效率。
水平分表的示例:
--创建order表
CREATE TABLE order.`order`(
id INT NOT NULL AUTO_INCREMENT,
user_id INT,
product_id INT,
price DECIMAL(10,2),
amount INT,
order_time DATETIME,
PRIMARY KEY (id),
KEY idx_user_id (user_id),
KEY idx_product_id (product_id),
KEY idx_order_time (order_time)
) ENGINE = InnoDB;
--按年创建表
CREATE TABLE order.order_2021(
LIKE order.`order`
) ENGINE = InnoDB;
--按月创建表
CREATE TABLE order.order_202101(
LIKE order.`order`
) ENGINE = InnoDB;
CREATE TABLE order.order_202102(
LIKE order.`order`
) ENGINE = InnoDB;
--省略其他月份
--按日创建表
CREATE TABLE order.order_20210101(
LIKE order.`order`
) ENGINE = InnoDB;
CREATE TABLE order.order_20210102(
LIKE order.`order`
) ENGINE = InnoDB;
--省略其他日期
垂直分表
垂直分表是将一个表中的数据,按照不同的业务类型,拆分成多个表,并且这些表数据结构不同。例如,一个电商业务的商品表(product)包含了商品的基本信息、销量信息、促销信息等多个业务类型,将这些业务类型分别存在不同的表中,可以避免数据的冗余,提高系统的性能。
垂直分表的示例:
--创建product表
CREATE TABLE product.product(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
description TEXT,
price DECIMAL(10,2),
PRIMARY KEY (id)
) ENGINE = InnoDB;
--创建product_sale表
CREATE TABLE product.product_sale(
id INT NOT NULL AUTO_INCREMENT,
product_id INT,
count INT,
PRIMARY KEY (id),
KEY idx_product_id (product_id)
) ENGINE = InnoDB;
--创建product_promotion表
CREATE TABLE product.product_promotion(
id INT NOT NULL AUTO_INCREMENT,
product_id INT,
promotion_price DECIMAL(10,2),
promotion_start DATETIME,
promotion_end DATETIME,
PRIMARY KEY (id),
KEY idx_product_id (product_id)
) ENGINE = InnoDB;
以上是MySQL的常用分表分库策略。在实际应用中,我们可以选择合适的分表分库策略,根据实际业务需求进行优化和调整。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:mysql数据库分表分库的策略 - Python技术站