面试题：MySQL分区表设计中的复杂场景处理与性能调优

MySQL分区表结构设计

分区依据：
- 地域维度：假设地域信息存储在region字段中，可以按照地域进行范围分区。例如，如果有多个省份，可以按照省份名称的字典序等方式进行范围划分。
- 时间维度：假设时间字段为create_time，采用按时间（如按月）的范围分区。例如，每个月的数据存储在一个分区中。
表结构示例：

CREATE TABLE your_table_name (
    id INT NOT NULL AUTO_INCREMENT,
    region VARCHAR(50) NOT NULL,
    create_time DATETIME NOT NULL,
    other_data VARCHAR(255),
    PRIMARY KEY (id, region, create_time)
)
PARTITION BY RANGE COLUMNS(region, create_time) (
    PARTITION p0 VALUES LESS THAN ('A', '2023 - 01 - 01 00:00:00'),
    PARTITION p1 VALUES LESS THAN ('B', '2023 - 02 - 01 00:00:00'),
    -- 以此类推，根据实际地域和时间范围划分
);

主从复制配置

主服务器配置：
- 修改MySQL配置文件（通常是my.cnf或my.ini）：

[mysqld]
log - bin=mysql - bin
server - id = 1

重启MySQL服务使配置生效。
在主服务器上创建用于主从复制的用户并授予权限：

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

获取主服务器状态：

SHOW MASTER STATUS;

记录File和Position的值，后续从服务器配置会用到。

从服务器配置：
- 修改MySQL配置文件：

[mysqld]
server - id = 2

重启MySQL服务。
在从服务器上配置主服务器信息：

CHANGE MASTER TO
    MASTER_HOST ='master_host_ip',
    MASTER_USER ='replication_user',
    MASTER_PASSWORD = 'password',
    MASTER_LOG_FILE ='master_log_file_name',
    MASTER_LOG_POS = master_log_position;

启动从服务器复制：

START SLAVE;

检查从服务器状态：

SHOW SLAVE STATUS \G;

确保Slave_IO_Running和Slave_SQL_Running都为Yes，且Seconds_Behind_Master的值正常（通常为0或接近0）。

读写分离配置

中间件方式（以MyCat为例）：
- 安装MyCat：从MyCat官网下载并安装MyCat。
- 配置schema.xml：

<schema name="your_schema" checkSQLschema="false" sqlMaxLimit="100">
    <table name="your_table_name" dataNode="dn1" rule="rule1"/>
</schema>
<dataNode name="dn1" dataHost="host1" database="your_database"/>
<dataHost name="host1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native">
    <heartbeat>select user()</heartbeat>
    <writeHost host="master" url="jdbc:mysql://master_host_ip:3306" user="root" password="password">
        <readHost host="slave1" url="jdbc:mysql://slave1_host_ip:3306" user="root" password="password"/>
        <readHost host="slave2" url="jdbc:mysql://slave2_host_ip:3306" user="root" password="password"/>
    </writeHost>
</dataHost>

配置rule.xml：根据需求配置分片规则。例如，若按地域分片：

<function name="hash - mod" class="io.mycat.route.function.PartitionByMod">
    <property name="count">3</property>
</function>
<rule name="rule1">
    <columns>region</columns>
    <algorithm>hash - mod</algorithm>
</rule>

程序代码方式：
- 在应用程序中，通过代码判断读写操作。例如，在Java中使用JDBC：

// 读操作
if (isReadOperation) {
    Connection conn = DriverManager.getConnection("jdbc:mysql://slave_host_ip:3306/your_database", "root", "password");
    // 执行读SQL
} else {
    Connection conn = DriverManager.getConnection("jdbc:mysql://master_host_ip:3306/your_database", "root", "password");
    // 执行写SQL
}

故障恢复及数据一致性保障

主服务器故障：
- 从服务器提升为主服务器。在提升前，确保从服务器的数据是最新的。可以通过SHOW SLAVE STATUS \G查看Seconds_Behind_Master，若为0或接近0，可使用STOP SLAVE;停止从服务器复制，然后使用RESET MASTER;将从服务器提升为主服务器。
- 重新配置其他从服务器指向新的主服务器。修改从服务器的CHANGE MASTER TO语句，更新主服务器信息。
从服务器故障：
- 关闭故障从服务器，修复故障（如硬件问题、网络问题等）。
- 重新配置从服务器。按照主从复制配置步骤，重新配置从服务器连接到主服务器，使用CHANGE MASTER TO和START SLAVE;恢复从服务器复制。
数据一致性保障：
- 定期进行数据校验。可以使用工具如pt - table - checksum来检查主从服务器数据是否一致。
- 确保主从复制延迟在可接受范围内。监控Seconds_Behind_Master指标，若延迟过大，分析原因（如网络问题、主服务器负载高、从服务器性能瓶颈等）并解决。

星途面试题库

面试题：MySQL分区表设计中的复杂场景处理与性能调优

知识考点

面试题答案

MySQL分区表结构设计

主从复制配置

读写分离配置

故障恢复及数据一致性保障