MySQL架构优化实战系列2:主从复制同步与查询性能调优

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL架构优化实战系列2:主从复制同步与查询性能调优

努力酱 2017-05-02 15:43:00 浏览1585
展开阅读全文

一、主从复制同步部署

 

1、概念


  • 主从复制:2台以上mysql服务器, 做负载均衡, 主服务器负责增删改 , 从服务器负责查询

  • 同步原理:mysql开启bin-log日志,主服务器所有的增删改操作会记录到bin-log日志;然后主服务器把bin-log日志发送 给 从服务器 , 从服务器重放bin-log日志 确保数据同步


2、开启bin-log日志


  • 配置 my.cnf 文件 并重启 mysql


[root@localhost etc]# vim /etc/my.cnf

 

20160624103010295.jpg

 

[root@localhost etc]# service mysql restart


  • 开启之后 mysql-bin对应的文件 已经出现


[root@localhost var]# cd /usr/local/mysql/var && ll


20160624103019727.jpg


  • 通过 show master status 命令查看 最新一个binlog日志 及开始行数


mysql> show master status;


20160624103042968.jpg


  • 查看binlog日志内容 可见 最新一行日志在位置107


$ /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/var/mysql-bin.000001


20160624103050874.jpg


  • 测试删除数据 可见 binlog文件新增日志内容


20160624103056463.jpg


20160624103102225.jpg


3、bin-log日志相关命令


  • flush logs


新建一个binlog日志,增删改日志在新文件中插入,新的日志end-log-positon 是107行,107行记录了mysql内部日志。


20160624103109294.jpg


  • reset master


清空所有bin-log日志 只保留 mysql-bin.000001 文件


  • mysqlbinlog


查看bin-log日志/usr/local/mysql/bin/mysqlbinlog/usr/local/mysql/var/mysql-bin.00001


  • show binlog events 查看binlog记录事件


20160624103117824.jpg


  • mysqlbinlog mysql -uroot -psmudge smudge_database


20160624103126532.jpg


重放bin-log日志、恢复数据:其实就是再把日志中的sql语句执行一边而已。(注意:select 语句 和delete语句 不可以放在一起重放 因为你最后还是得不到数据)


恢复原理就是:执行之前的insert语句,或者之前的update语句 
如果你的单纯的delete物理删除,别想恢复了,因为再次执行的还是delete语句


4、create 创建用户 + grant用户授权


  • 主服务器查看用户密码


mysql> select host,user,password from mysql.user;


20160624103134290.jpg


  • 添加主服务器用户密码


CREATE USER 'kang'@'192.168.206.132' IDENTIFIED BY 'smudge';


创建用户kang 可以在ip为192.168.206.132主机上访问数据库


20160624103143818.jpg


给用户kang授权所有的库的权限


20160624103149537.jpg


5、主服务器配置


主服务器ip:192.168.206.128


  • 配置主服务器my.cnf 文件


vim /etc/my.cnf


20160624103157978.jpg


  • 配置之后刷新binlog文件


flush logs with read lock 确保获得一致性快照,等待主从binlog日志同步完毕达到数据一致


  • 或者使用mysqldump备份sql 文件


将主服务器一致都是sql文件备份,传递到从服务器

mysqldump -uroot -psmudge smudge -l -F > '/home/smudge.sql'


-l 是指锁表 防止新数据插入

-F 是刷新 生成一个新的binlog日志


(如果你数据库中有merge表 容易会提示Unable to open underlying table which is differently defined or ofnon-MyISAM type ordoesn't exist when using LOCK TABLES )


20160624103205836.jpg


  • 使用scp隧道传输命令 传递文件


scp /home/smudge.sql 192.168.206.132:/home


20160624103213826.jpg


6、从服务器配置


  • 恢复一部分主服务器备份的数据


新建smudge库


20160624103221613.jpg


mysql导入sql文件


[root@localhost ~]# mysql -uroot -psmudge smudge < /home/smudge.sql


  • 配置从服务器my.cnf文件


vim /etc/my.cnf


其中用户名和密码就是上述我们在主服务器添加的信息


20160624103228536.jpg


(如果你的mysql版本5.1(mysql>status查看)之前的,配置这4项,启动之后就不必使用change master 命令 进行主动同步)


保存并重启mysql


  • 查看主服务器master binlog文


20160624103235151.jpg


  • 启动slave进程,开启主从同步


因为我的mysql版本是5.7的, 所以我使用change master命令


20160624103244667.jpg


  • show slave status 查看从服务器状态


20160624103251989.jpg


表明同步功能已经开启


7、从服务器常用命令


start slave 启动复制线程

stop slave 停止复制线程

show master logs 查看主数据库日志 

change master to master_host ,master_user 动态切换主数据库

show processlist 查看运行进程 (主动服务器都适用)


8、常见错误排错


  • show slave status 检查主动状态


20 数值为NO

21 数值为NULL


表明同步出现了故障,可能是slave服务器执行了写操作或者从服务器重启有事务回滚操作。


  • 解决


从服务器: stop slave 关闭复制线程

主服务器:show master status 查看最新二进制文件和位置偏移量

从服务器执行:change master to master_host ...
master_log_file='mysql-bin.000005',master_log_pos=759 命令


二、查询性能优化


1、查询执行基础知识


  • mysql执行查询过程


  客户端将查询发送到服务器
② 服务器检查查询缓存 如果找到了就从缓存返回结果 否则进行下一步
③ 服务器解析,预处理和优化查询,生成执行计划
④ 执行引擎调用存储引擎api执行查询
⑤ 服务器将结果发送回客户端


20160624103300912.jpg


  • mysql客户端/服务器协议


该协议是半双工通信,可以发送或接收数据,但是不能同时发送和接收决定了mysql的沟通简单又快捷;


缺点:无法进行流程控制,一旦一方发送消息,另一方在发送回复之前必须提取完整的消息,就像抛球游戏,任意时间,只有某一方有球,而且有球在手上,否则就不能把球抛出去(发送消息)


  • mysql客户端发送/服务器响应


可以设定max_packet_size这个参数控制客户端发送的数据包(一旦发送数据包,唯一做的就是等待结果)


服务器发送的响应由多个数据包组成, 客户端必须完整接收结果,即使只需要几行数据,也得等到全部接收 然后丢掉,或者强制断开连接。(这两个方法好挫,所以我们使用limit子句呀!!)


也可以理解,客户端从服务器 "拉" 数据 ,实际是服务器产生数据 "推"到客户端, 客户端不能说不要 是必须全部装着!


常用的Mysql类库 其实是从客户端提取数据 缓存到array(内存)中,然后进行 foreach 处理。


但是对于庞大的结果集装载在内存中需要很长时间,如果不缓存,使用较少的内存并且可以尽快工作,但是应用程序和类库交互时候,服务器端的锁和资源都是被锁定的。


  • 查询状态
     

每个mysql连接都是mysql服务器的一个线程 任意一个给定的时间都有一个状态来标识正在发生的事情。


使用 show full processlist 命令查看 


20160624103309252.jpg 

mysql中一共有12个状态:休眠、查询、锁定、分析和统计、拷贝到磁盘上的临时表、排序结果、发送数据,通过这些状态 知道 "球在谁手上"。


  • 查询缓存


解析一个查询,如果开启了缓存,mysql会检查查询缓存,发现缓存匹配,返回缓存之前,检查查询的权限。


2、优化数据访问


查询性能低下最基本的原因是访问了太多的数据,分析两方面:


① 查明应用程序是否获取超过需要的数据 通常意味着访问了过多的行或列

    查明mysql服务器是否分析了超过需要的行


  • 向服务器请求了不需要的数据


一般请求不需要的数据,再丢掉他们,造成服务器额外的负担,增加网络开销,消耗了内存和cpu。


典型的错误:

① 提取超过需要的行 => 添加 limit 10 控制获取行数
② 多表联接提取所有列 => select fruit.* from fruit left join fruit_juice where
.....
③ 提取所有的列 => select id,name... from fruit ... (有时提取超过需要的数据便于复用)

 

  • mysql检查了太多数据


简单的开销指标:执行时间、检查的行数、返回的行数
 

以上三个指标写入了慢查询日志 可以使用 mysqlsla工具进行日志分析:


① 执行时间:执行时间只是参考 不可一概而论 因为执行时间 和服务器当时负载有关

② 检查和返回的行:理想情况下返回的行和检查的行一样,但是显示基本不可能 比如联接查询

  检查的行和访问类型: 使用explain sq语句,观察typ列


20160624103318157.jpg


typ列:(访问速度依次递增)


① 全表扫描(full table scan)
② 索引扫描(index scan)
③ 范围扫描(range scan)
④ 唯一索引查找(unique index lookup)
⑤ 常量(constant)


可见type列为index即sql语句,基于索引扫描:


rows列为12731,即扫描了12731行 extra列为using index,即使用索引过滤不需要的行


mysql会在3种情况下使用where子句,从最好到最坏依次是:


① 对索引查找应用where子句来消除不匹配的行 这发生在存储层
② 使用覆盖索引(extra 列 "using index") 避免访问行 从索引取得数据过滤不匹配的行 这发生在服务层不需要从表中读取行
③ 从表中检索出数据 过滤不匹配的行(extra:using where)


如果发现访问数据行数很大,尝试以下措施:


① 使用覆盖索引 ,存储了数据 存储引擎不会读取完整的行
② 更改架构使用汇总表
③ 重写复杂的查询 让mysql优化器优化执行它


3、重构查询的方式


优化有问题的查询,其实也可以找到替代方案,提供更高的效率。


  • 复杂查询和多个查询


mysql一般服务器可以每秒50000个查询,常规情况下,使用尽可能少的查询 有时候分解查询得到更高的效率。


  • 缩短查询


分治法,查询本质上不变,每次执行一小部分,以减少受影响的行数。比如清理陈旧的数据,每次清理1000条:

delete from message where create < date_sub(now(),inteval 3 month)  

limit 1000


防止长时间锁住很多行的数据。


  • 分解联接


把一个多表联接分解成多个单个查询 然后在应用程序实现联接操作


20160624103328399.jpg


第一眼看上去比较浪费,因为增加了查询数量,但是有重大的性能优势:


① 缓存效率高,应用程序直接缓存了表 类似第一个查询直接跳过

② 对于myisam表来说 每个表一个查询有效利用表锁 查询锁住表的时间缩短

③ 应用程端进行联接更方便扩展数据库

④ 使用in() 避免联表查询id排序的耗费

⑤ 减少多余行的访问 , 意味着每行数据只访问一次 避免联接查询的非正则化的架构带来的反复访问同一行的弊端


分解联接应用场景:


① 可以缓存早期查询的大量的数据

② 使用了多个myisam表(mysiam表锁 并发时候 一条sql锁住多个表 所以要分解)

③ 数据分布在不同的服务器上

④ 对于大表使用in() 替换联接

    一个联接引用了同一个表很多次


  • 提取随机行


20160624103337582.jpg


  • 分组查询

20160624103344311.jpg


  • 外键


只有Innodb引擎支持外键,myisam可以添加外键但是没有效果。


主表添加主键id,从表添加外键id引用主表的id。


表student

20160624103351900.jpg


表student_extend

20160624103358797.jpg


为student_extend添加外键,外键指向student表中的id列,在delete时触发外键。


表student数据

20160624103407527.jpg


表student_extend数据
20160624103413709.jpg


删除表student一条数据,则外键表就会触发外键,删除对应数据:


delete from student where id = 2;


20160624103420761.jpg


  • 优化联合查询


20160624103427613.jpg


  • 优化max() min()


其中 name 没有索引。


20160624103434432.jpg


对一个表同时进行select和update。

本文来自云栖社区合作伙伴"DBAplus",原文发布时间:2016-06-24

网友评论

登录后评论
0/500
评论
努力酱
+ 关注