下面,主要是验证在MySQL主从复制环境下,存储过程,函数,触发器,事件的复制情况,这些确实会让人混淆。

手边开发的后端项目一直以来都用的.NET MVC框架,访问数据库使用其自带的EF
CodeFirst模式,写存储过程的能力都快退化了

1.大表的数据修改最好分批处理。

首先,创建一张测试表

闲来无事,自己写了条分页存储过程,网上类似的文章多的是,这里只列了一种情况,依据分析函数生成行号来实现分页查询

1000万行的记录表中删除更新100万行记录,一次只删除或更新5000行数据。每批处理完成后,暂停几秒中,进行同步处理。

mysql> create table test.t1(name varchar(10),age int);
Query OK, 0 rows affected (0.10 sec)

 

2.如何修改大表的表结构。

 

环境:sqlServer 2014

对表的列的字段类型进行修改,改变字段宽度时还是会锁表,无法解决主从数据库延迟的问题。

存储过程

创建数据库过程不再敖述,这里直接进入重点:

解决办法:

创建存储过程

1、首先创建了一张TestAdmin表,主键为ID字段int类型且自增

1.创建一个新表。

delimiter //
CREATE procedure p1 (IN name varchar(10),IN age int)
 BEGIN
 insert into test.t1 values(name,age);
END//
delimiter ;
1 create table TestAdmin (
2 Id int identity(1,1) primary Key,
3 Name varchar(Max),
4 Age int
5 )

2.在老表上创建触发器同步老表数据到新表。

通过查看二进制日志,可以看到该DDL语句已被记录

2、接着批量导入1000条模拟数据

3.同步老表数据到新表。

# at 120
#161010 23:18:38 server id 1  end_log_pos 339 CRC32 0xae3dcfda     Query    thread_id=2    exec_time=0    error_code=0
use `test`/*!*/;
SET TIMESTAMP=1476112718/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`(IN name varchar(10),IN age int)
BEGIN
 insert into test.t1 values(name,age);
END
/*!*/;
DELIMITER ;
 1 declare @count int
 2 --这里定义模拟数据条数
 3 set @count=1000
 4 
 5 
 6 while(@count>0)
 7 begin
 8 insert into TestAdmin (Name,Age) values ('zhuyuan'+convert(varchar,@count),@count)
 9 set @count=@count-1
10 end

1 select * from TestAdmin

4.删除老表。

 

图片 1

5.将新表重新命名为老表。

执行存储过程

导入完成,开始分页:

可以使用命令,完成上面的工作:

mysql> call p1('tom',10);
Query OK, 1 row affected (0.08 sec)

mysql> select * from t1;
+-------+------+
| name  | age  |
+-------+------+
| tom   |   10 |
+-------+------+
1 rows in set (0.01 sec)

大致思路为首先按一定排序规则查询出所有数据,然后为每一行自动生成行号,然后再对生成行号后的表进行where语句筛选处理

pt-online-schema-change –alter=”modify c varchar(150) not null default
‘’” –user=root –password=root d=sakia, t=表名 –charset=utf8 –execute

 

3、我们先为主表每行插入一列相同数据生成表V1,目的主要是为了后面的分析函数可以对表进行单行聚合

3.优化not in 和 <> 的查询

查看二进制日志中,记录的是还是call p1(‘tom’,10)操作记录对应的SQL语句

1 select *,1 as SameRow from TestAdmin

例子:

# at 574
#161010 23:23:54 server id 1  end_log_pos 653 CRC32 0xc532cfae     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1476113034/*!*/;
BEGIN
/*!*/;
# at 653
#161010 23:23:54 server id 1  end_log_pos 833 CRC32 0x2982c7a8     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1476113034/*!*/;
insert into test.t1 values( NAME_CONST('name',_utf8'tom' COLLATE 'utf8_general_ci'), NAME_CONST('age',10))
/*!*/;
# at 833
#161010 23:23:54 server id 1  end_log_pos 864 CRC32 0xdf106f41     Xid = 56
COMMIT/*!*/;

图片 2

select customer_id ,firstname,email from customer where customer_id

由此可见,对于存储过程,在主从复制中,记录的是存储过程对应的DML操作,而不是调用动作本身。

  • (表V1)

not in (select customer_id from payment)

 

4、再对表V1进行生成行号处理,利用sqlServer自带分析函数ROW_NUMBER()可实现该功能

会多次查询payment 表,如果payment表记录很多效率将很低。

函数

1 select ROW_NUMBER() over(partition by SameRow order by Id) as Row,* from (select *,1 as SameRow from TestAdmin)m

改写后的sql

创建函数

生成表V2

select a.customer_id ,a.firstname,a.email from customer a left join
payment b

CREATE FUNCTION f1 (string VARCHAR(5))
RETURNS VARCHAR(20) DETERMINISTIC
RETURN CONCAT('f1',string);

图片 3

on a.customer_id=b.customer_id where b.customer_id is null;

二进制日志中的记录如下:

  • (表V2)

4.对汇总表的优化查询

# at 1246
#161010 23:34:01 server id 1  end_log_pos 1480 CRC32 0x3a1eb0a2     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1476113641/*!*/;
CREATE DEFINER=`root`@`localhost` FUNCTION `f1`(string VARCHAR(5)) RETURNS varchar(20) CHARSET utf8
    DETERMINISTIC
RETURN CONCAT('f1',string)
/*!*/;

这时我们已经有一张具有索引行号的表V2,后面的操作就清晰了

select count(1) from product_comment where product_id=999;

 

5、假设我们需要每页10条数据,且查询第二页

创建汇总表:

执行函数

1 select * from (select ROW_NUMBER() over(partition by SameRow order by Id) as Row,* from (select *,1 as SameRow from TestAdmin)m)o where o.Row between 1*10+1 and 2*10

create table product_comment_cnt (product_id ,cnt int);

在这里,其实要分两种情况,一是binlog_format为statement,另一种情况为row

图片 4

select sum(cnt) from (

当binlog_format为statement时

6、再做一次封装,为它创建一个存储过程,便于我们以后再次调用

select cnt from product_comment_cnt where product_id=999 union all

mysql> show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.02 sec)

mysql> insert into t1(name) values(f1('steve'));
Query OK, 1 row affected (0.07 sec)

mysql> select * from t1;
+---------+------+
| name    | age  |
+---------+------+
| tom     |   10 |
| f1steve | NULL |
+---------+------+
2 rows in set (0.00 sec)
1 create proc select_page
2 (
3 @pageIndex int,--当前页码
4 @pagecount int--每页条数
5 )
6 as
7 begin
8 select * from (select ROW_NUMBER() over(partition by SameRow order by Id) as Row,* from (select *,1 as SameRow from TestAdmin)m)o where o.Row between @pageIndex*@pagecount+1 and (@pageIndex+1)*@pagecount
9 end

select count(1) from product_comment where product_id =999 and
timestr>date(now())

查看该语句对应的二进制日志中的内容

图片 5

) a

# at 1480
#161010 23:37:58 server id 1  end_log_pos 1559 CRC32 0xf1f2c4a2     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1476113878/*!*/;
BEGIN
/*!*/;
# at 1559
#161010 23:37:58 server id 1  end_log_pos 1673 CRC32 0x0c9a73c5     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1476113878/*!*/;
insert into t1(name) values(f1('steve'))
/*!*/;
# at 1673
#161010 23:37:58 server id 1  end_log_pos 1704 CRC32 0x45419118     Xid = 67
COMMIT/*!*/;

存储过程创建成功!

每天定时更新汇总表,再加上当天的数据。

可见在statement的二进制日志格式下,复制的调用函数这个操作本身。

7、我们来试一下,假设要查询第5页,每页10条

 

 

1 select_page 5,10

当binlog_format为row时

图片 6

mysql> set session binlog_format='row';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1(name) values(f1('tiger'));
Query OK, 1 row affected (0.03 sec)

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注