2. 首页 > MariaDB教程 > 正文

MariaDB教程FG067-MariaDB表分区与分片实战

教程整理：风哥教程  |  更新时间：2026-01-02  |  教程分类：MariaDB教程  |  文档学习：3

内容简介：本文主要介绍MariaDB表分区与分片的实现与管理，包括表分区的基本概念、分片的基本概念、分区与分片的区别等内容。通过实际案例讲解表分区实现、分片实现和性能优化，帮助读者掌握MariaDB表分区与分片的技能。风哥教程参考MariaDB官方文档Partitioning等相关内容。

Part01-基础概念与理论知识

1.1 表分区的基本概念

表分区（Table Partitioning）是指将一个大表分割成多个小表，每个小表称为一个分区。表分区的主要作用包括：

  • 提高查询性能：只需要扫描相关分区，减少查询数据量
  • 管理方便：可以单独管理每个分区，如备份、删除等
  • 提高可用性：单个分区故障不影响其他分区
  • 优化存储：可以将不同分区存储在不同的存储设备上

1.2 分片的基本概念

分片（Sharding）是指将数据分散存储在多个数据库服务器上，每个服务器称为一个分片。分片的主要作用包括：

  • 水平扩展：通过增加服务器来提高系统性能和容量
  • 负载均衡：将请求分散到多个服务器，减少单个服务器的负载
  • 提高可用性：单个分片故障不影响其他分片

1.3 分区与分片的区别

分区与分片的主要区别：

  • 存储位置：分区数据存储在同一个数据库实例中，分片数据存储在多个数据库实例中
  • 管理方式：分区由数据库管理系统自动管理，分片需要应用程序或中间件管理
  • 扩展能力：分区扩展受限于单个服务器的资源，分片可以通过增加服务器无限扩展
  • 复杂度：分区实现简单，分片实现复杂
更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 表分区规划

表分区规划建议：

  • 选择合适的分区类型：根据数据特点选择合适的分区类型，如范围分区、列表分区、哈希分区等
  • 分区键选择：选择频繁查询的列作为分区键，如时间、地区等
  • 分区数量：根据数据量和查询模式确定分区数量，避免过多或过少的分区
  • 存储规划：可以将不同分区存储在不同的存储设备上，提高性能
  • 备份策略：根据分区的重要性和访问频率制定不同的备份策略

2.2 分片规划

分片规划建议：

  • 分片策略：选择合适的分片策略，如范围分片、哈希分片、列表分片等
  • 分片键选择：选择唯一且分布均匀的列作为分片键，如用户ID、订单ID等
  • 分片数量：根据数据量和性能需求确定分片数量
  • 服务器规划：确保分片服务器的硬件配置一致，避免性能瓶颈
  • 数据一致性：确保分片数据的一致性，避免数据丢失或不一致

2.3 性能优化建议

性能优化建议：

  • 索引优化：为分区表和分片表创建合适的索引
  • 查询优化：在查询中使用分区键和分片键，减少数据扫描
  • 缓存策略：使用缓存减少数据库访问
  • 并行处理：使用并行查询提高处理速度
  • 监控与调优：定期监控分区和分片的性能，及时调优
学习交流加群风哥微信: itpux-com

Part03-生产环境项目实施方案

3.1 表分区实现

更多学习教程公众号风哥教程itpux_com

# 表分区实现
MariaDB [(none)]> # 1. 创建范围分区表
CREATE TABLE fgedu_orders (
id INT PRIMARY KEY,
order_date DATE,
customer_id INT,
amount DECIMAL(10,2)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION pfuture VALUES LESS THAN MAXVALUE
);
# 2. 创建列表分区表
CREATE TABLE fgedu_users (
id INT PRIMARY KEY,
name VARCHAR(50),
city VARCHAR(50)
) ENGINE=InnoDB
PARTITION BY LIST (city) (
PARTITION pbeijing VALUES IN (‘Beijing’),
PARTITION pshanghai VALUES IN (‘Shanghai’),
PARTITION pguangzhou VALUES IN (‘Guangzhou’),
PARTITION pother VALUES IN (DEFAULT)
);
# 3. 创建哈希分区表
CREATE TABLE fgedu_products (
id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2)
) ENGINE=InnoDB
PARTITION BY HASH (id)
PARTITIONS 4;
# 4. 查看分区信息
SHOW CREATE TABLE fgedu_orders;
# 5. 维护分区
# 添加分区
ALTER TABLE fgedu_orders ADD PARTITION (
PARTITION p2024 VALUES LESS THAN (2025)
);
# 删除分区
ALTER TABLE fgedu_orders DROP PARTITION p2020;
# 合并分区
ALTER TABLE fgedu_orders REORGANIZE PARTITION p2021, p2022 INTO (
PARTITION p2021_2022 VALUES LESS THAN (2023)
);

3.2 分片实现

# 分片实现
# 1. 使用ProxySQL实现分片
# 配置ProxySQL分片规则
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, max_connections) VALUES
(10, ‘192.168.1.100’, 3306, 1, 1000),
(11, ‘192.168.1.101’, 3306, 1, 1000);
# 配置分片规则
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
(10, 1, ‘^SELECT.*FROM fgedu_users WHERE id BETWEEN 1 AND 10000$’, 10, 1),
(11, 1, ‘^SELECT.*FROM fgedu_users WHERE id BETWEEN 10001 AND 20000$’, 11, 1);
# 2. 使用应用程序实现分片
# 示例：基于用户ID的分片
def get_shard(user_id):
shard_id = user_id % 2
if shard_id == 0:
return ‘192.168.1.100:3306’
else:
return ‘192.168.1.101:3306’
# 3. 使用MaxScale实现分片
# 配置MaxScale分片规则
[Shard Router]
type=service
router=shardrouter
servers=server1,server2
user=maxscale
password=maxscale_password
shard_key=id
shard_count=2

3.3 分区与分片管理

# 分区与分片管理
MariaDB [(none)]> # 1. 分区管理
# 查看分区信息
SHOW CREATE TABLE fgedu_orders;
# 查看分区统计信息
SELECT PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_NAME = ‘fgedu_orders’;
# 优化分区
ALTER TABLE fgedu_orders OPTIMIZE PARTITION p2023;
# 重建分区
ALTER TABLE fgedu_orders REBUILD PARTITION p2023;
# 分析分区
ALTER TABLE fgedu_orders ANALYZE PARTITION p2023;
# 2. 分片管理
# 监控分片状态
SELECT * FROM mysql_servers;
# 分片数据迁移
# 示例：将用户ID 1-5000从分片1迁移到分片2
INSERT INTO shard2.fgedu_users SELECT * FROM shard1.fgedu_users WHERE id BETWEEN 1 AND 5000;
DELETE FROM shard1.fgedu_users WHERE id BETWEEN 1 AND 5000;
# 分片扩容
# 示例：添加新分片
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, max_connections) VALUES
(12, ‘192.168.1.102’, 3306, 1, 1000);
学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 表分区实战案例

场景描述：为订单表创建范围分区，按年份分区。

# 表分区实战
# 1. 创建订单表
MariaDB [(none)]> CREATE TABLE fgedu_orders (
-> id INT PRIMARY KEY,
-> order_date DATE,
-> customer_id INT,
-> amount DECIMAL(10,2)
-> ) ENGINE=InnoDB
-> PARTITION BY RANGE (YEAR(order_date)) (
-> PARTITION p2020 VALUES LESS THAN (2021),
-> PARTITION p2021 VALUES LESS THAN (2022),
-> PARTITION p2022 VALUES LESS THAN (2023),
-> PARTITION p2023 VALUES LESS THAN (2024),
-> PARTITION pfuture VALUES LESS THAN MAXVALUE
-> );
# 2. 插入测试数据
MariaDB [(none)]> INSERT INTO fgedu_orders(id, order_date, customer_id, amount) VALUES
-> (1, ‘2020-01-01’, 1001, 100.00),
-> (2, ‘2021-01-01’, 1002, 200.00),
-> (3, ‘2022-01-01’, 1003, 300.00),
-> (4, ‘2023-01-01’, 1004, 400.00);
# 3. 查看分区信息
MariaDB [(none)]> SELECT PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_NAME = ‘fgedu_orders’;
+—————-+————+
| PARTITION_NAME | TABLE_ROWS |
+—————-+————+
| p2020 | 1 |
| p2021 | 1 |
| p2022 | 1 |
| p2023 | 1 |
| pfuture | 0 |
+—————-+————+
# 4. 查询分区数据
MariaDB [(none)]> SELECT * FROM fgedu_orders PARTITION (p2021);
+—-+————+————-+——–+
| id | order_date | customer_id | amount |
+—-+————+————-+——–+
| 2 | 2021-01-01 | 1002 | 200.00 |
+—-+————+————-+——–+

执行结果：

# 表分区创建成功
MariaDB [(none)]> SELECT PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_NAME = ‘fgedu_orders’;
+—————-+————+
| PARTITION_NAME | TABLE_ROWS |
+—————-+————+
| p2020 | 1 |
| p2021 | 1 |
| p2022 | 1 |
| p2023 | 1 |
| pfuture | 0 |
+—————-+————+
# 查询分区数据
MariaDB [(none)]> SELECT * FROM fgedu_orders PARTITION (p2021);
+—-+————+————-+——–+
| id | order_date | customer_id | amount |
+—-+————+————-+——–+
| 2 | 2021-01-01 | 1002 | 200.00 |
+—-+————+————-+——–+

4.2 分片实战案例

场景描述：使用ProxySQL实现基于用户ID的分片。

# 分片实战
# 1. 配置ProxySQL
[root@proxysql ~]# mysql -u admin -padmin -h 127.0.0.1 -P 6032
# 2. 添加后端服务器
MariaDB [(none)]> INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, max_connections) VALUES
-> (10, ‘192.168.1.100’, 3306, 1, 1000),
-> (11, ‘192.168.1.101’, 3306, 1, 1000);
# 3. 配置用户
MariaDB [(none)]> INSERT INTO mysql_users(username, password, default_hostgroup, active, max_connections) VALUES
-> (‘appuser’, ‘apppassword’, 10, 1, 1000);
# 4. 配置分片规则
MariaDB [(none)]> INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
-> (10, 1, ‘^SELECT.*FROM fgedu_users WHERE id BETWEEN 1 AND 10000$’, 10, 1),
-> (11, 1, ‘^SELECT.*FROM fgedu_users WHERE id BETWEEN 10001 AND 20000$’, 11, 1),
-> (12, 1, ‘^INSERT INTO fgedu_users.*id BETWEEN 1 AND 10000$’, 10, 1),
-> (13, 1, ‘^INSERT INTO fgedu_users.*id BETWEEN 10001 AND 20000$’, 11, 1);
# 5. 加载配置
MariaDB [(none)]> LOAD MYSQL SERVERS TO RUNTIME;
MariaDB [(none)]> LOAD MYSQL USERS TO RUNTIME;
MariaDB [(none)]> LOAD MYSQL QUERY RULES TO RUNTIME;
# 6. 保存配置
MariaDB [(none)]> SAVE MYSQL SERVERS TO DISK;
MariaDB [(none)]> SAVE MYSQL USERS TO DISK;
MariaDB [(none)]> SAVE MYSQL QUERY RULES TO DISK;
# 7. 测试分片
[root@client ~]# mysql -u appuser -papppassword -h 192.168.1.200 -P 6033
MariaDB [(none)]> INSERT INTO fgedu_users(id, name, email) VALUES(1, ‘User1’, ‘user1@fgedu.net.cn’);
MariaDB [(none)]> INSERT INTO fgedu_users(id, name, email) VALUES(10001, ‘User10001’, ‘user10001@fgedu.net.cn’);
# 8. 验证数据分布
[root@db1 ~]# mysql -u root -p -e “SELECT * FROM fgedu_users;
”
+—-+——-+——————+
| id | name | email |
+—-+——-+——————+
| 1 | User1 | user1@fgedu.net.cn |
+—-+——-+——————+
[root@db2 ~]# mysql -u root -p -e “SELECT * FROM fgedu_users;
”
+——-+———-+———————-+
| id | name | email |
+——-+———-+———————-+
| 10001 | User10001 | user10001@fgedu.net.cn |
+——-+———-+———————-+

执行结果：

# 分片配置成功
[root@db1 ~]# mysql -u root -p -e “SELECT * FROM fgedu_users;
”
+—-+——-+——————+
| id | name | email |
+—-+——-+——————+
| 1 | User1 | user1@fgedu.net.cn |
+—-+——-+——————+
[root@db2 ~]# mysql -u root -p -e “SELECT * FROM fgedu_users;
”
+——-+———-+———————-+
| id | name | email |
+——-+———-+———————-+
| 10001 | User10001 | user10001@fgedu.net.cn |
+——-+———-+———————-+

4.3 分区与分片性能优化案例

场景描述：优化分区表和分片表的性能。

# 性能优化实战
# 1. 分区表性能优化
# 创建索引
MariaDB [(none)]> CREATE INDEX idx_order_date ON fgedu_orders(order_date);
# 优化查询
MariaDB [(none)]> EXPLAIN SELECT * FROM fgedu_orders WHERE order_date BETWEEN ‘2023-01-01’ AND ‘2023-12-31’;
+——+————-+————+——-+—————+—————+———+——+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+————-+————+——-+—————+—————+———+——+——+————————–+
| 1 | SIMPLE | fgedu_orders | range | idx_order_date | idx_order_date | 4 | NULL | 1 | Using where;
Using index |
+——+————-+————+——-+—————+—————+———+——+——+————————–+
# 2. 分片表性能优化
# 优化ProxySQL配置
MariaDB [(none)]> UPDATE mysql_servers SET max_connections = 2000 WHERE hostgroup_id IN (10, 11);
# 启用查询缓存
MariaDB [(none)]> SET GLOBAL query_cache_size = 1000000;
# 3. 监控性能
# 监控分区表性能
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE ‘Handler_read%’;
# 监控分片性能
[root@proxysql ~]# mysql -u admin -padmin -h 127.0.0.1 -P 6032 -e “SELECT * FROM stats_mysql_connection_pool;
”

执行结果：

# 分区表查询优化
MariaDB [(none)]> EXPLAIN SELECT * FROM fgedu_orders WHERE order_date BETWEEN ‘2023-01-01’ AND ‘2023-12-31’;
+——+————-+————+——-+—————+—————+———+——+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+————-+————+——-+—————+—————+———+——+——+————————–+
| 1 | SIMPLE | fgedu_orders | range | idx_order_date | idx_order_date | 4 | NULL | 1 | Using where;
Using index |
+——+————-+————+——-+—————+—————+———+——+——+————————–+
# 分片性能监控
[root@proxysql ~]# mysql -u admin -padmin -h 127.0.0.1 -P 6032 -e “SELECT * FROM stats_mysql_connection_pool;
”
+————–+———-+———-+—————–+—————–+————+—————-+————————+
| hostgroup_id | srv_host | srv_port | status | ConnUsed | MaxConn | MaxConnUsed | Queries |
+————–+———-+———-+—————–+—————–+————+—————-+————————+
| 10 | 192.168.1.100 | 3306 | ONLINE | 10 | 2000 | 50 | 1000 |
| 11 | 192.168.1.101 | 3306 | ONLINE | 8 | 2000 | 45 | 950 |
+————–+———-+———-+—————–+—————–+————+—————-+————————+
风哥提示：安全开发是防止SQL注入的第一道防线

Part05-风哥经验总结与分享

5.1 表分区最佳实践

风哥提示：在使用表分区时，应遵循最佳实践，确保分区的有效性和性能。
  • 选择合适的分区类型：根据数据特点选择合适的分区类型，如时间数据使用范围分区，离散数据使用列表分区
  • 合理设计分区键：选择频繁查询的列作为分区键，避免使用随机值作为分区键
  • 控制分区数量：分区数量不宜过多，一般建议不超过100个分区
  • 定期维护分区：定期优化、重建和分析分区，保持分区的性能
  • 监控分区性能：定期监控分区的使用情况和性能，及时调整分区策略

5.2 分片最佳实践

  • 选择合适的分片策略：根据数据特点选择合适的分片策略，如用户数据使用哈希分片，时间数据使用范围分片
  • 合理设计分片键：选择唯一且分布均匀的列作为分片键，避免数据倾斜
  • 考虑数据一致性：确保分片数据的一致性，避免数据丢失或不一致
  • 设计分片扩容方案：提前规划分片扩容方案，确保系统可以平滑扩容
  • 监控分片性能：定期监控分片的使用情况和性能，及时调整分片策略

5.3 常见问题与解决方案

  • 分区键选择不当：选择频繁查询的列作为分区键，避免使用随机值
  • 分区数量过多：控制分区数量，一般建议不超过100个分区
  • 分片数据倾斜：选择分布均匀的分片键，避免数据集中在某个分片
  • 分片扩容困难：设计合理的分片扩容方案，如使用一致性哈希
  • 性能问题：优化索引，使用缓存，合理设计查询
# 表分区与分片示例
— 表分区示例
CREATE TABLE fgedu_orders (
id INT PRIMARY KEY,
order_date DATE,
customer_id INT,
amount DECIMAL(10,2)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION pfuture VALUES LESS THAN MAXVALUE
);
— 分片示例
— ProxySQL分片配置
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, max_connections) VALUES
(10, ‘192.168.1.100’, 3306, 1, 1000),
(11, ‘192.168.1.101’, 3306, 1, 1000);
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
(10, 1, ‘^SELECT.*FROM fgedu_users WHERE id BETWEEN 1 AND 10000$’, 10, 1),
(11, 1, ‘^SELECT.*FROM fgedu_users WHERE id BETWEEN 10001 AND 20000$’, 11, 1);

通过以上措施，可以有效实现MariaDB表分区与分片，提高系统的性能和可扩展性。

from MariaDB视频:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息