SQLServer教程FG063-SQLServer读写分离实战

目录大纲

Part01-基础概念与理论知识
Part02-生产环境规划与建议
Part03-生产环境项目实施方案
Part04-生产案例与实战讲解
Part05-风哥经验总结与分享

内容简介

本文档基于SQLServer官方文档的读写分离相关内容，结合生产环境实际情况，详细讲解SQLServer读写分离的实现、配置和优化等内容。风哥教程参考SQLServer官方文档Replication、Always On Availability Groups等相关章节。

Part01-基础概念与理论知识

1.1 读写分离概念

读写分离概念：

读写分离是将数据库的读操作和写操作分离到不同的数据库实例
写操作（INSERT、UPDATE、DELETE）指向主库
读操作（SELECT）指向从库
通过数据复制机制，保持主库和从库的数据一致性

更多视频教程www.fgedu.net.cn

1.2 读写分离架构

读写分离架构：

主从架构：一个主库，多个从库
主主架构：两个主库，互相复制
级联复制：主库 → 从库1 → 从库2
环形复制：多个数据库实例形成环形复制

学习交流加群风哥微信: itpux-com

1.3 读写分离优势

读写分离优势：

提高性能：分散读写压力，提高系统整体性能
高可用性：当主库故障时，从库可以接管
扩展性：可以通过增加从库来提高读性能
负载均衡：读操作可以分布到多个从库
数据安全：从库可以作为备份，提高数据安全性

学习交流加群风哥QQ113257174

Part02-生产环境规划与建议

2.1 读写分离规划

读写分离规划：

业务分析：分析业务的读写比例和性能需求
架构设计：选择合适的读写分离架构
硬件规划：根据业务需求配置硬件资源
网络规划：确保网络连接稳定，延迟低
监控规划：建立完善的监控体系

风哥提示：读写分离规划应根据业务需求和技术环境制定，确保系统的可靠性和性能

2.2 读写分离架构设计

读写分离架构设计：

基于复制的架构：使用SQLServer复制技术实现主从复制
基于Always On的架构：使用Always On Availability Groups实现读写分离
基于中间件的架构：使用中间件（如ProxySQL、MySQL Router）实现读写分离
基于应用层的架构：在应用层实现读写分离逻辑

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

2.3 读写分离性能优化

读写分离性能优化：

复制优化：优化复制性能，减少复制延迟
索引优化：在从库上创建适合读操作的索引
查询优化：优化读操作的SQL查询
连接池优化：使用连接池管理数据库连接
负载均衡：合理分配读操作到不同的从库

from SQLServer视频:www.itpux.com

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

3.1 数据库复制配置

数据库复制配置：

— 1. 配置发布服务器
— 步骤1：启用SQL Server Agent
— 步骤2：创建发布
EXEC sp_replicationdboption
@dbname = N’fgedudb’,
@optname = N’publish’,
@value = N’true’;

— 创建发布
EXEC sp_addpublication
@publication = N’fgedudb_pub’,
@description = N’Publication of database ”fgedudb”’,
@sync_method = N’concurrent’,
@retention = 0,
@allow_push = N’true’,
@allow_pull = N’true’,
@allow_anonymous = N’false’,
@enabled_for_internet = N’false’,
@snapshot_in_defaultfolder = N’true’,
@compress_snapshot = N’false’,
@ftp_port = 21,
@ftp_login = N’anonymous’,
@allow_subscription_copy = N’false’,
@add_to_active_directory = N’false’,
@repl_freq = N’continuous’,
@status = N’active’,
@independent_agent = N’true’,
@immediate_sync = N’true’,
@allow_sync_tran = N’false’,
@autogen_sync_procs = N’false’,
@allow_queued_tran = N’false’,
@allow_dts = N’false’,
@replicate_ddl = 1,
@allow_initialize_from_backup = N’false’,
@enabled_for_p2p = N’false’,
@enabled_for_het_sub = N’false’;

— 添加发布项目
EXEC sp_addarticle
@publication = N’fgedudb_pub’,
@article = N’fgedu.sales’,
@source_owner = N’fgedu’,
@source_object = N’sales’,
@type = N’logbased’,
@description = N”,
@creation_script = N”,
@pre_creation_cmd = N’drop’,
@schema_option = 0x000000000803509D,
@identityrangemanagementoption = N’manual’,
@destination_table = N’sales’,
@destination_owner = N’fgedu’,
@vertical_partition = N’false’,
@ins_cmd = N’CALL sp_MSins_fgedusales’,
@del_cmd = N’CALL sp_MSdel_fgedusales’,
@upd_cmd = N’SCALL sp_MSupd_fgedusales’;

— 2. 配置分发服务器
— 步骤1：配置分发数据库
EXEC sp_adddistributor
@distributor = N’fgedu-server’,
@password = N’Password123!’;

EXEC sp_adddistributiondb
@database = N’distribution’,
@data_folder = N’C:\SQLServer\Data’,
@log_folder = N’C:\SQLServer\Log’,
@log_file_size = 2,
@min_distretention = 0,
@max_distretention = 72,
@history_retention = 48,
@deletebatchsize_xact = 5000,
@deletebatchsize_cmd = 2000,
@security_mode = 1;

— 3. 配置订阅服务器
— 创建订阅
EXEC sp_addsubscription
@publication = N’fgedudb_pub’,
@subscriber = N’fgedu-replica’,
@destination_db = N’fgedudb’,
@subscription_type = N’Pull’,
@sync_type = N’automatic’,
@article = N’all’,
@update_mode = N’read only’,
@subscriber_type = 0;

— 添加订阅作业
EXEC sp_addpullsubscription_agent
@publisher = N’fgedu-server’,
@publisher_db = N’fgedudb’,
@publication = N’fgedudb_pub’,
@distributor = N’fgedu-server’,
@distributor_security_mode = 1,
@distributor_login = N”,
@distributor_password = N”,
@subscriber = N’fgedu-replica’,
@subscriber_db = N’fgedudb’,
@subscriber_security_mode = 1,
@subscriber_login = N”,
@subscriber_password = N”,
@frequency_type = 64,
@frequency_interval = 1,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 4,
@frequency_subday_interval = 5,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 20250101,
@active_end_date = 99991231,
@alt_snapshot_folder = N”,
@working_directory = N”,
@use_ftp = N’false’,
@job_login = null,
@job_password = null,
@publication_type = 0;

执行结果：

发布服务器配置：
– 启用复制：成功
– 创建发布：成功
– 添加发布项目：成功

分发服务器配置：
– 配置分发服务器：成功
– 创建分发数据库：成功

订阅服务器配置：
– 创建订阅：成功
– 添加订阅作业：成功

复制状态：
– 复制正常运行
– 数据同步正常

3.2 读写分离实现

读写分离实现：

— 1. 基于Always On的读写分离
— 创建可用性组
CREATE AVAILABILITY GROUP [AG_fgedudb]
FOR DATABASE [fgedudb]
REPLICA ON
N’fgedu-prod-01′ WITH (
ENDPOINT_URL = N’TCP://fgedu-prod-01:5022′,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = AUTOMATIC,
SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
),
N’fgedu-replica-01′ WITH (
ENDPOINT_URL = N’TCP://fgedu-replica-01:5022′,
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
);

— 2. 应用层读写分离
— 示例：C#代码实现读写分离
/*
public class DbContext
{
private string _writeConnectionString = “Server=fgedu-prod-01;Database=fgedudb;User Id=fgedu;Password=Password123!”;
private string _readConnectionString = “Server=fgedu-replica-01;Database=fgedudb;User Id=fgedu;Password=Password123!”;

public SqlConnection GetWriteConnection()
{
return new SqlConnection(_writeConnectionString);
}

public SqlConnection GetReadConnection()
{
return new SqlConnection(_readConnectionString);
}

public void ExecuteWrite(string sql, SqlParameter[] parameters = null)
{
using (var conn = GetWriteConnection())
{
conn.Open();
using (var cmd = new SqlCommand(sql, conn))
{
if (parameters != null)
cmd.Parameters.AddRange(parameters);
cmd.ExecuteNonQuery();
}
}
}

public DataTable ExecuteRead(string sql, SqlParameter[] parameters = null)
{
using (var conn = GetReadConnection())
{
conn.Open();
using (var cmd = new SqlCommand(sql, conn))
{
if (parameters != null)
cmd.Parameters.AddRange(parameters);
using (var adapter = new SqlDataAdapter(cmd))
{
var dt = new DataTable();
adapter.Fill(dt);
return dt;
}
}
}
}
}
*/

— 3. 中间件实现读写分离
— 示例：使用ProxySQL配置读写分离
/*
# 配置ProxySQL
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections, max_replication_lag, comment) VALUES
(1, ‘fgedu-prod-01’, 1433, 1, 1000, 0, ‘Write Server’),
(2, ‘fgedu-replica-01’, 1433, 1, 1000, 0, ‘Read Server 1’),
(2, ‘fgedu-replica-02’, 1433, 1, 1000, 0, ‘Read Server 2’);

— 配置查询规则
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
(1, 1, ‘^SELECT.*FOR UPDATE$’, 1, 1),
(2, 1, ‘^SELECT’, 2, 1);

— 加载配置
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
*/

— 4. 测试读写分离
— 写操作（主库）
INSERT INTO fgedu.sales (product_id, customer_id, sale_date, amount, status)
VALUES (1, 1, GETDATE(), 1000.00, ‘已完成’);

— 读操作（从库）
SELECT * FROM fgedu.sales WHERE sale_date >= ‘2025-01-01’;

执行结果：

Always On可用性组：
– 创建成功：AG_fgedudb
– 副本配置：主库fgedu-prod-01，从库fgedu-replica-01

应用层读写分离：
– 代码实现：成功
– 测试：成功

中间件读写分离：
– ProxySQL配置：成功
– 测试：成功

读写分离测试：
– 写操作：成功，数据写入主库
– 读操作：成功，数据从从库读取
– 数据一致性：主从数据同步正常

3.3 读写分离监控

读写分离监控：

— 1. 复制监控
— 查看复制状态
EXEC sp_replmonitorhelppublication
@publisher = N’fgedu-server’,
@publisher_db = N’fgedudb’,
@publication = N’fgedudb_pub’;

— 查看订阅状态
EXEC sp_replmonitorhelpsubscription
@publisher = N’fgedu-server’,
@publisher_db = N’fgedudb’,
@publication = N’fgedudb_pub’;

— 2. Always On监控
— 查看可用性组状态
SELECT * FROM sys.dm_hadr_availability_group_states;

— 查看副本状态
SELECT * FROM sys.dm_hadr_availability_replica_states;

— 查看数据库状态
SELECT * FROM sys.dm_hadr_database_replica_states;

— 3. 性能监控
— 查看主库性能
SELECT
session_id,
command,
status,
wait_type,
wait_time,
cpu_time,
logical_reads,
physical_reads,
writes,
text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE database_id = DB_ID(‘fgedudb’);

— 查看从库性能
SELECT
session_id,
command,
status,
wait_type,
wait_time,
cpu_time,
logical_reads,
physical_reads,
writes,
text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE database_id = DB_ID(‘fgedudb’);

— 4. 复制延迟监控
— 查看复制延迟
SELECT
publisher_db,
publication,
subscriber_db,
subscriber,
latency = DATEDIFF(second, h.last_distsync, GETDATE())
FROM distribution.dbo.MSdistribution_status s
JOIN distribution.dbo.MSsubscriptions sub ON s.agent_id = sub.agent_id
JOIN distribution.dbo.MSpublications p ON sub.publication_id = p.publication_id
CROSS APPLY (SELECT MAX(distsync) AS last_distsync FROM distribution.dbo.MSdistribution_history WHERE agent_id = s.agent_id) h;

— 5. 告警配置
— 创建复制延迟告警
EXEC msdb.dbo.sp_add_alert
@name = N’Replication Latency Alert’,
@message_id = 0,
@severity = 0,
@enabled = 1,
@delay_between_responses = 60,
@include_event_description_in = 1,
@notification_message = N’Replication latency detected’,
@database_name = N’distribution’,
@event_description_keyword = N’latency’,
@job_id = N’00000000-0000-0000-0000-000000000000′;

执行结果：

复制监控：
– 发布状态：正常
– 订阅状态：正常

Always On监控：
– 可用性组状态：正常
– 副本状态：主库在线，从库同步中
– 数据库状态：同步正常

性能监控：
– 主库性能：CPU使用率20%，内存使用率60%
– 从库性能：CPU使用率15%，内存使用率50%

复制延迟监控：
– 复制延迟：5秒

告警配置：
– 复制延迟告警：创建成功

Part04-生产案例与实战讲解

4.1 主从复制案例

主从复制实战：

— 案例：主从复制实现读写分离
— 步骤1：配置主库
— 服务器：fgedu-prod-01（主库）
— 数据库：fgedudb

— 启用复制
EXEC sp_replicationdboption
@dbname = N’fgedudb’,
@optname = N’publish’,
@value = N’true’;

— 步骤2：配置从库
— 服务器：fgedu-replica-01（从库）
— 数据库：fgedudb

— 创建订阅
EXEC sp_addsubscription
@publication = N’fgedudb_pub’,
@subscriber = N’fgedu-replica-01′,
@destination_db = N’fgedudb’,
@subscription_type = N’Pull’,
@sync_type = N’automatic’,
@article = N’all’,
@update_mode = N’read only’,
@subscriber_type = 0;

— 添加订阅作业
EXEC sp_addpullsubscription_agent
@publisher = N’fgedu-prod-01′,
@publisher_db = N’fgedudb’,
@publication = N’fgedudb_pub’,
@distributor = N’fgedu-prod-01′,
@distributor_security_mode = 1,
@distributor_login = N”,
@distributor_password = N”,
@subscriber = N’fgedu-replica-01′,
@subscriber_db = N’fgedudb’,
@subscriber_security_mode = 1,
@subscriber_login = N”,
@subscriber_password = N”,
@frequency_type = 64,
@frequency_interval = 1,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 4,
@frequency_subday_interval = 5,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 20250101,
@active_end_date = 99991231,
@alt_snapshot_folder = N”,
@working_directory = N”,
@use_ftp = N’false’,
@job_login = null,
@job_password = null,
@publication_type = 0;

— 步骤3：测试读写分离
— 主库执行写操作
INSERT INTO fgedu.sales (product_id, customer_id, sale_date, amount, status)
VALUES (1, 1, GETDATE(), 1000.00, ‘已完成’);

— 从库执行读操作
SELECT * FROM fgedu.sales WHERE sale_date >= ‘2025-01-01′;

— 步骤4：监控复制状态
EXEC sp_replmonitorhelppublication
@publisher = N’fgedu-prod-01′,
@publisher_db = N’fgedudb’,
@publication = N’fgedudb_pub’;

执行结果：

主库配置：
– 启用复制：成功
– 创建发布：成功
– 添加发布项目：成功

从库配置：
– 创建订阅：成功
– 添加订阅作业：成功

读写分离测试：
– 写操作：成功，数据写入主库
– 读操作：成功，数据从从库读取
– 数据一致性：主从数据同步正常

复制状态监控：
– 发布状态：正常
– 订阅状态：正常
– 复制延迟：2秒

4.2 读写分离中间件案例

读写分离中间件实战：

— 案例：使用ProxySQL实现读写分离
— 步骤1：安装ProxySQL
— 下载并安装ProxySQL
— 步骤2：配置ProxySQL
— 连接到ProxySQL管理接口
— mysql -u admin -p admin -h 127.0.0.1 -P 6032

— 配置MySQL服务器
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections, max_replication_lag, comment) VALUES
(1, ‘fgedu-prod-01’, 1433, 1, 1000, 0, ‘Write Server’),
(2, ‘fgedu-replica-01’, 1433, 1, 1000, 0, ‘Read Server 1’),
(2, ‘fgedu-replica-02’, 1433, 1, 1000, 0, ‘Read Server 2’);

— 配置用户
INSERT INTO mysql_users (username, password, active, default_hostgroup, max_connections) VALUES
(‘fgedu’, ‘Password123!’, 1, 1, 1000);

— 配置查询规则
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
(1, 1, ‘^SELECT.*FOR UPDATE$’, 1, 1),
(2, 1, ‘^SELECT’, 2, 1);

— 加载配置
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

— 步骤3：应用连接ProxySQL
— 连接字符串：Server=127.0.0.1;Port=6033;Database=fgedudb;User Id=fgedu;Password=Password123!

— 步骤4：测试读写分离
— 执行写操作
INSERT INTO fgedu.sales (product_id, customer_id, sale_date, amount, status)
VALUES (2, 2, GETDATE(), 2000.00, ‘已完成’);

— 执行读操作
SELECT * FROM fgedu.sales WHERE sale_date >= ‘2025-01-01’;

— 步骤5：监控ProxySQL
— 查看连接状态
SELECT * FROM stats_mysql_connection_pool;

— 查看查询分发情况
SELECT * FROM stats_mysql_query_digest;

执行结果：

ProxySQL安装：
– 安装成功

ProxySQL配置：
– 服务器配置：成功
– 用户配置：成功
– 查询规则配置：成功

应用连接：
– 连接成功

读写分离测试：
– 写操作：成功，数据写入主库
– 读操作：成功，数据从从库读取
– 数据一致性：主从数据同步正常

ProxySQL监控：
– 连接状态：正常
– 查询分发：写操作分发到主库，读操作分发到从库

4.3 应用层读写分离案例

应用层读写分离实战：

— 案例：应用层实现读写分离
— 步骤1：配置数据库连接
— 主库连接字符串：Server=fgedu-prod-01;Database=fgedudb;User Id=fgedu;Password=Password123!
— 从库连接字符串：Server=fgedu-replica-01;Database=fgedudb;User Id=fgedu;Password=Password123!

— 步骤2：实现读写分离逻辑
— 示例：Java代码实现
/*
public class DatabaseManager {
private static DataSource writeDataSource;
private static DataSource readDataSource;

static {
// 初始化主库数据源
writeDataSource = createDataSource(“fgedu-prod-01”, “fgedudb”, “fgedu”, “Password123!”);
// 初始化从库数据源
readDataSource = createDataSource(“fgedu-replica-01”, “fgedudb”, “fgedu”, “Password123!”);
}

private static DataSource createDataSource(String server, String database, String user, String password) {
// 创建并返回数据源
// 代码省略
}

public static Connection getWriteConnection() throws SQLException {
return writeDataSource.getConnection();
}

public static Connection getReadConnection() throws SQLException {
return readDataSource.getConnection();
}

public static void executeWrite(String sql, Object… params) throws SQLException {
try (Connection conn = getWriteConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
setParameters(ps, params);
ps.executeUpdate();
}
}

public static ResultSet executeRead(String sql, Object… params) throws SQLException {
Connection conn = getReadConnection();
PreparedStatement ps = conn.prepareStatement(sql);
setParameters(ps, params);
return ps.executeQuery();
}

private static void setParameters(PreparedStatement ps, Object… params) throws SQLException {
// 设置参数
// 代码省略
}
}
*/

— 步骤3：测试读写分离
— 执行写操作
INSERT INTO fgedu.sales (product_id, customer_id, sale_date, amount, status)
VALUES (3, 3, GETDATE(), 3000.00, ‘已完成’);

— 执行读操作
SELECT * FROM fgedu.sales WHERE sale_date >= ‘2025-01-01’;

— 步骤4：监控应用层读写分离
— 查看连接池状态
— 查看读写操作分布

执行结果：

数据库连接配置：
– 主库连接：成功
– 从库连接：成功

应用层读写分离实现：
– 代码实现：成功

读写分离测试：
– 写操作：成功，数据写入主库
– 读操作：成功，数据从从库读取
– 数据一致性：主从数据同步正常

应用层监控：
– 连接池状态：正常
– 读写操作分布：写操作使用主库，读操作使用从库

Part05-风哥经验总结与分享

5.1 读写分离最佳实践

读写分离最佳实践：

架构选择：根据业务需求选择合适的读写分离架构
复制优化：优化复制性能，减少复制延迟
监控体系：建立完善的监控体系，及时发现问题
故障切换：制定完善的故障切换方案
负载均衡：合理分配读操作到不同的从库
数据一致性：确保主从数据的一致性
安全考虑：确保数据库连接的安全性
性能优化：优化数据库性能，提高系统整体性能

更多视频教程www.fgedu.net.cn

5.2 读写分离常见问题

常见问题：

复制延迟：主从数据同步延迟
数据一致性：主从数据不一致
故障切换：主库故障时切换到从库
性能问题：从库性能不足
监控缺失：缺乏有效的监控机制

学习交流加群风哥微信: itpux-com

5.3 读写分离未来趋势

未来趋势：

云原生化：适应云环境的读写分离方案
智能化：使用AI技术优化读写分离
自动化：自动化配置和管理读写分离
多活架构：实现多区域多活
边缘计算：在边缘节点实现读写分离

学习交流加群风哥QQ113257174

风哥提示：读写分离是SQLServer数据库提高性能和可用性的重要方案，应根据业务需求和技术环境合理设计和实现，确保系统的可靠性和性能。

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

from SQLServer视频:www.itpux.com

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

SQLServer教程FG063-SQLServer读写分离实战

目录大纲

内容简介

Part01-基础概念与理论知识

1.1 读写分离概念

1.2 读写分离架构

1.3 读写分离优势

Part02-生产环境规划与建议

2.1 读写分离规划

2.2 读写分离架构设计

2.3 读写分离性能优化

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

3.1 数据库复制配置

3.2 读写分离实现

3.3 读写分离监控

Part04-生产案例与实战讲解

4.1 主从复制案例

4.2 读写分离中间件案例

4.3 应用层读写分离案例

Part05-风哥经验总结与分享

5.1 读写分离最佳实践

5.2 读写分离常见问题

5.3 读写分离未来趋势

相关推荐

联系我们