MySQL分表存储技巧汇总（mysql下分表存储）

MySQL分表存储技巧汇总

MySQL分表存储是数据库优化的重要手段之一，特别是在大型应用场景下，为了提高数据库的性能和查询速度，分表往往是必须的选择。但是，如果不合理地进行分表，反而会造成不必要的麻烦和额外的开销。

下面总结了一些MySQL分表存储的经验和技巧，供大家参考。

一、按照业务特性进行分表

根据业务的特性进行分表是最基本的原则。比如按月份、按城市、按分类等等来进行分表。这样可以使得表的数据量得到很好的控制，同时也有利于查询性能的提升。

例如，按照月份分表，可以采用如下代码：

CREATE TABLE `order_201901` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`order_no` varchar(50) NOT NULL,

`user_id` int(11) NOT NULL,

`amount` decimal(10,2) NOT NULL,

`create_time` datetime NOT NULL,

PRIMARY KEY (`id`),

KEY `idx_user_id` (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `order_201902` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`order_no` varchar(50) NOT NULL,

`user_id` int(11) NOT NULL,

`amount` decimal(10,2) NOT NULL,

`create_time` datetime NOT NULL,

PRIMARY KEY (`id`),

KEY `idx_user_id` (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

二、按照数据量进行分表

在处理大数据量的情况下，按照数据量进行分表也是很常见的做法。通常可以将数据分布在多个物理磁盘上，降低了单个磁盘的负载压力，提高了系统的稳定性。

例如，按照数据量分表，可以采用如下代码：

CREATE TABLE `order_1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`order_no` varchar(50) NOT NULL,

`user_id` int(11) NOT NULL,

`amount` decimal(10,2) NOT NULL,

`create_time` datetime NOT NULL,

PRIMARY KEY (`id`),

KEY `idx_user_id` (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `order_2` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`order_no` varchar(50) NOT NULL,

`user_id` int(11) NOT NULL,

`amount` decimal(10,2) NOT NULL,

`create_time` datetime NOT NULL,

PRIMARY KEY (`id`),

KEY `idx_user_id` (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

三、采用水平分表和垂直分表相结合

水平分表即将数据按行划分到不同的表中，而垂直分表即将一张表的列进行分割，把通用性低的列和通用性高的列分开存储。两种方法结合起来可以更好地解决数据库性能和架构问题，提高查询性能。

例如，水平分表和垂直分表相结合，可以采用如下代码：

CREATE TABLE `order_1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`order_no` varchar(50) NOT NULL,

`create_time` datetime NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `order_2` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`user_id` int(11) NOT NULL,

`amount` decimal(10,2) NOT NULL,

PRIMARY KEY (`id`),

KEY `idx_user_id` (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

四、使用分区表

MySQL 5.1以上版本提供了分区表的功能，可以将大表进行分区分批存储，使得单个分区的数据量得到了控制，同时也有利于查询性能的提升。

例如，使用分区表可以采用如下代码：

CREATE TABLE `order` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`order_no` varchar(50) NOT NULL,

`user_id` int(11) NOT NULL,

`amount` decimal(10,2) NOT NULL,

`create_time` datetime NOT NULL,

PRIMARY KEY (`id`),

KEY `idx_user_id` (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

PARTITION BY RANGE (YEAR(create_time))

(

PARTITION p2019 VALUES LESS THAN (2020),

PARTITION p2020 VALUES LESS THAN (2021),

PARTITION p2021 VALUES LESS THAN MAXVALUE

);

以上是MySQL分表存储的一些经验和技巧，涉及到的知识点较为广泛，需要根据不同的应用场景灵活运用。同时，需要注意的是，过度分表也可能会对系统的性能造成负面影响，所以需要在实践中不断总结和思考。