MySQL分库分表总结讲解

什么是MySQL分库分表

MySQL分库分表是指将一个大的数据库按照一定规则分割为多个子数据库，每个子数据库分布于不同的物理服务器上，同样地，将一张大表根据一定条件分割为多张小表。

分库分表的主要目的是解决单个库或单表数据量过大导致查询性能缓慢、写入性能降低，以及瓶颈问题等。

MySQL分库分表的策略

MySQL分库分表的策略主要有以下几种:

• 垂直分表：将一张表按照列进行分表，将不常用的列分离出去形成一张新表，保证常用的列在同一张表，以提高查询效率。

• 水平分表：将一张表按照行进行分表，将表中的数据均分到多张表中，并使用相同的表结构，以提高写入和查询效率。

• 分库：将一个库中的多个表按照业务分散到多个库中，以提高并发和查询效率。

• 分区：将一个表按照时间、ID等分段方式进行分割成多个小表，以提高查询效率。

MySQL分库分表示例

垂直分表示例

我们现在有一个名为users的表，其结构如下：

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL DEFAULT '',
  `password` varchar(50) NOT NULL DEFAULT '',
  `email` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `age` int(3) NOT NULL DEFAULT '18',
  `status` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我们可以将其进行垂直分表。将email和gender两个字段分离出去，形成一张新表user_info：

CREATE TABLE `user_info` (
  `id` int(11) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

同时，将users表中的这两个字段移除：

ALTER TABLE `users` 
DROP COLUMN `email`,
DROP COLUMN `gender`;

在实际使用中，一般不会仅按照这种方式进行分表，而是根据实际业务需要进行决策。

水平分表示例

我们现在有一个名为orders的表，其结构如下：

CREATE TABLE `orders` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT(20) NOT NULL DEFAULT 0,
  `order_name` VARCHAR(50) NOT NULL DEFAULT '',
  `order_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我们可以将其进行水平分表。将orders表根据user_id字段进行分表，将相同user_id的数据放在同一个表中。

例如：
我们可以将所有的user_id以mod 10的方式进行分类，将user_id除以10之后得到的余数，作为后缀，例如：
orders_0表：存储user_id为0、10、20的订单数据；
orders_1表：存储user_id为1、11、21的订单数据；
……

分库示例

假如我们有一个电商网站，涉及到goods、user、order三个表，同时访问量较大，我们可以将其进行分库，将三个表分别分散到不同的库中。

例如：
我们可以将goods表单独放在一台物理服务器上的一个库中，将user表和order表放在另外一台物理服务器上的另一个库中，以减少单个库的访问压力。

总结

MySQL分库分表是一种提高数据查询效率和写入效率的方案，在处理大数据量或高并发的业务场景下不可避免。基于不同的业务需求和数据访问情况，选择适合的分库分表策略非常重要。