MySQL技能完整学习列表13、MySQL高级特性——1、分区表（Partitioning）——2、复制（Replication

​

目录

1、分区表（Partitioning）

为什么使用分区表

分区类型

示例

1. RANGE分区示例

2. LIST分区示例

3. HASH分区示例

2、复制（Replication）

为什么使用复制

复制类型

如何设置复制

1. 配置主服务器

2. 配置从服务器

注意事项与监控

3、集群（Clustering）

为什么使用集群

MySQL集群类型

示例：设置InnoDB Cluster

1. 安装MySQL

2. 配置MySQL服务器

3. 创建复制用户

4. 配置InnoDB Cluster

5. 测试集群

注意事项与监控

1、分区表（Partitioning）

分区表是MySQL中一种将数据分散存储在多个物理子表中的技术，但从逻辑上看，它们仍然被当作一个表来对待。这种技术可以极大地提高大型数据库的性能、管理和可维护性。

为什么使用分区表

1. 性能提升：通过将数据分散到多个物理部分，查询可以并行处理，从而提高性能。
2. 管理方便：可以独立地备份、恢复或优化某一个分区，而不影响其他分区。
3. 数据归档：对于具有时间序列数据的应用，可以使用分区表按日期归档数据。
4. 均匀I/O分布：可以将不同的分区放在不同的物理设备上，从而平衡I/O负载。

分区类型

1. RANGE分区：基于列的值范围来分区。
2. LIST分区：基于列的离散值来分区。
3. HASH分区：基于用户定义的表达式的哈希值来分区。
4. KEY分区：类似于HASH分区，但MySQL服务器提供哈希函数。
5. COLUMNS分区：可以使用多个列的值进行RANGE或LIST分区。

示例

1. RANGE分区示例

假设有一个销售记录表sales_records，其中有一个sale_date列记录了销售日期。我们可以按月进行RANGE分区：

CREATE TABLE sales_records (
    id INT NOT NULL,
    product_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
)
PARTITION BY RANGE( YEAR(sale_date) * 12 + MONTH(sale_date) ) (
    PARTITION p0 VALUES LESS THAN (2023*12+1),
    PARTITION p1 VALUES LESS THAN (2024*12+1),
    PARTITION p2 VALUES LESS THAN (2025*12+1)
);

2. LIST分区示例

如果我们按照地区销售，可以使用LIST分区：

CREATE TABLE sales_records (
    id INT NOT NULL,
    region ENUM('North', 'South', 'East', 'West') NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
)
PARTITION BY LIST(region) (
    PARTITION pNorth VALUES IN ('North'),
    PARTITION pSouth VALUES IN ('South'),
    PARTITION pEast VALUES IN ('East'),
    PARTITION pWest VALUES IN ('West')
);

3. HASH分区示例

假设我们想根据product_id的哈希值进行分区：

CREATE TABLE sales_records (
    id INT NOT NULL,
    product_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
)
PARTITION BY HASH(product_id)
PARTITIONS 4;  -- 创建4个分区

以上仅为简单的示例，实际应用中可能需要考虑更复杂的场景和需求。在使用分区表之前，建议深入了解其工作原理和限制，以确保它适合你的应用场景。

2、复制（Replication）

MySQL的复制功能允许数据从一个MySQL数据库服务器（称为主服务器或Master）复制到一个或多个MySQL数据库服务器（称为从服务器或Slave）。这种复制是异步的，并且从服务器可以处理来自应用的读请求，从而分摊主服务器的读负载。

为什么使用复制

1. 负载均衡：通过将读请求分发到多个从服务器，可以提高应用的整体吞吐量。
2. 数据备份：可以使用从服务器作为数据备份，防止主服务器发生故障。
3. 数据分析：可以在从服务器上执行复杂的数据分析查询，而不影响主服务器的性能。

复制类型

1. 基于语句的复制（Statement-Based Replication, SBR）：复制SQL语句到从服务器并执行。
2. 基于行的复制（Row-Based Replication, RBR）：复制实际更改的行数据到从服务器。
3. 混合复制（Mixed-Based Replication）：根据情况选择SBR或RBR。

如何设置复制

1. 配置主服务器

编辑my.cnf或my.ini配置文件：

[mysqld]
server-id=1                 # 服务器唯一ID
log-bin=mysql-bin           # 启用二进制日志
binlog-do-db=mydatabase     # 需要复制的数据库名

重启MySQL服务。

创建复制用户并授权：

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

查看主服务器状态：

SHOW MASTER STATUS;

记下File和Position的值，稍后在从服务器上配置时会用到。

2. 配置从服务器

编辑my.cnf或my.ini配置文件：

[mysqld]
server-id=2                 # 服务器唯一ID，确保与主服务器不同
relay-log=mysql-relay-bin   # 中继日志文件名

重启MySQL服务。

在从服务器上配置主服务器信息：

CHANGE MASTER TO 
    MASTER_HOST='master_ip_address', 
    MASTER_USER='repl_user', 
    MASTER_PASSWORD='password', 
    MASTER_LOG_FILE='noted_file_from_master_status', 
    MASTER_LOG_POS=noted_position_from_master_status;

启动从服务器复制线程：

START SLAVE;

检查从服务器状态：

SHOW SLAVE STATUS\G;

确保Slave_IO_Running和Slave_SQL_Running的值都是“Yes”。如果是其他值，检查错误日志以解决问题。

注意事项与监控

1. 数据一致性：使用复制时，必须确保主从服务器的数据保持一致。可以使用工具如pt-table-checksum来检查数据一致性。
2. 网络延迟：如果主从服务器之间存在网络延迟，可能导致复制延迟。可以监控Seconds_Behind_Master指标来检查延迟情况。

3、集群（Clustering）

MySQL集群是一种数据库架构，旨在通过在多台服务器之间分布数据和查询负载来提高性能、可靠性和可扩展性。这种架构通常用于需要高可用性、容错和可扩展性的大型应用。

为什么使用集群

1. 高可用性：通过冗余配置，可以在服务器故障时保持服务运行。
2. 负载均衡：通过在多个节点之间分布查询，可以提高查询吞吐量。
3. 可扩展性：可以方便地添加更多服务器来扩展性能。
4. 容错性：通过数据复制，可以在硬件故障时防止数据丢失。

MySQL集群类型

1. MySQL Replication Cluster：基于主从复制，用于读写分离和负载均衡。
2. MySQL NDB Cluster：一个高性能分布式数据库系统，使用NDB存储引擎。
3. MySQL Group Replication：一个基于多主复制的插件，提供同步复制和自动故障检测。
4. InnoDB Cluster：一个集成的解决方案，结合了Group Replication、MySQL Router和MySQL Shell。

示例：设置InnoDB Cluster

1. 安装MySQL

在每个节点上安装MySQL。确保所有节点上的MySQL版本相同。

2. 配置MySQL服务器

编辑my.cnf文件，确保以下设置：

[mysqld]
server-id=1                 # 唯一服务器ID，每个节点不同
gtid_mode=ON               # 启用GTID模式
enforce_gtid_consistency=ON # 强制GTID一致性
log_bin=mysql-bin          # 启用二进制日志

3. 创建复制用户

在主节点上执行：

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

4. 配置InnoDB Cluster

使用MySQL Shell：

mysqlsh --uri root@localhost:3306

在MySQL Shell中执行：

var cluster = dba.createCluster('myCluster');
cluster.addInstance('root@node2:3306'); // 添加其他节点
cluster.status(); // 检查集群状态

5. 测试集群

在主节点上创建数据库和表，并插入一些数据。然后，从其他节点上查询数据，以确保复制正常工作。

注意事项与监控

1. 网络稳定性：集群中的节点需要稳定的网络连接以确保数据一致性。
2. 监控复制状态：定期检查复制状态以确保数据同步。可以使用SHOW STATUS命令或第三方监控工具。
3. 负载均衡：使用负载均衡器（如MySQL Router）来分发查询到不同的节点，以提高性能。
4. 扩展性计划：在设计集群时考虑未来的扩展需求，以确保可以平滑地添加新节点。

​