mysql 数据库分片前的准备

对一个库中的相关表进行水平拆分到不同实例的数据库中
1

如何选择分区键
1,分区键要尽量避免夸分片查询的发生
2,分区键要能尽量使个分片中的数据平均

如何存储无需分片的表
1,每个分片中存储一份相同的数据
2,使用额外的节点同意存储

如何在节点上部署分片
1,每个分片使用单一数据库,并且数据库名也相同
2,将多个分片表存储在一个数据库中,并在表名上加入分片号后缀
3,在一个节点中部署多个数据库,每个数据库包含一个分片

如何分配分片中的数据
1,按分区键的Hash值取模来分配分片数据
2,按分区键的范围来分配分片数据
3,利用分区键和分片的映射表来分配分片数据

如何生成全局唯一ID
1,使用auto_increment_increment 和 auto_increment_offset 参数
2,使用全局节点来生成ID
3,在redis等缓存服务器中创建全局ID

mysql索引优化策略

联合索引
如何进行选择索引列的顺序:
1,经常会被使用到的列优先
2,选择性高的列优先
3,宽度比较小的列优先

覆盖索引
优点:
1,可以优化缓存,减少磁盘IO操作
2,可以减少随机IO,变随机IO操作变为顺序IO操作
3,可以避免对Innodb主键索引的二次查询
4,可以避免MyIsam表进行系统调用

无法使用覆盖索引的情况
1,存储引擎不支持覆盖索引
2,查询中使用了太多的列
3,使用了双%号的like查询

使用索引来优化查询
使用索引扫描来优化排序:
1,索引的列顺序和order by字句的顺序完全一致
2,索引中所有列的方向(升序,降序)和 order by 字句完全一致
3,Order by 中的字段全部在关联表中的第一张表中

模拟Hash索引进行查询优化:
1,只能处理全值的全值匹配查找
2,所使用的Hash函数决定着索引键的大小

mysql btree索引和hash索引

B-tree

B-tree索引的特点:
1,B-tree索引以B+树的结构存储数据
1
2,B-tree索引能够加快数据的查询速度
3,B-tree索引更适合进行范围查找

在什么情况下可以用到B树索引:
1,全值匹配的查询 order_sn=‘4654654’;
2,匹配最左前缀的查询
3,匹配列前缀差选
4,匹配列前缀查询 order_sn like ‘44654%’;
5,匹配范围值的查询
6,精确匹配左前列并分为匹配另一列
7,只访问索引的查询

btree索引的使用限制
1,如果不是按照索引最左列开始查找,则无法使用索引
2,使用索引时不能跳过索引中的列
3,not in 和 <>操作无法使用索引
4,如果查询中有某个列的范围查询,则其右边所有列都无法使用索引

Hash索引
Hash索引的特点:
1,Hash索引是基于Hash表实现的,只有查询条件精确匹配Hash索引中的所有列时,才能够使用到hash索引
2,对于Hash索引中的所有列,存储引擎都会为每一行计算一个Hash码,Hash索引中存储的就是Hash码

Hash索引的限制
1,Hash索引必须进行二次查找
2,Hash索引无法用于排序
3,Hash索引不支持部分索引查找也不支持范围查找
4,Hash索引中Hash码的计算可能存在Hash冲突

为什么要使用索引
1,索引大大减少了存储引擎需要扫描的数据量
2,索引可以帮助我们进行排序以避免使用临时表
3,索引可以把随机I/O变为顺序I/O

索引不是越多越好
1,索引会增加写操作的成本
2,太多的索引会增加查询优化器的选择时间

mysql读写分离和负载均衡介绍

读写分离
程序实现读写分离
优点:
1,有开发人员控制什么样查询在从库中执行,因此比较灵活
2,由程序直接连接数据库,所以性能小号比较少
缺点:
1,增加了开发的工作量,使程序代码更加复杂
2,人为控制,容易出现错误

中间件实现读写分离
mysql-proxy(读写分离,负载均衡)
maxScale(读写分离,从服务器负载均衡)

优点:
1,由中间件根据查询语法分析,自动完成读写分离
2,对程序透明,对于已有程序不用做任何调整
缺点:
1,由于增加了中间层,所以对查询效率有损耗
2,对于延迟敏感业务无法自动在主库执行

如何实现读负载均衡
软件
1,LVS
2,HaPROXY
3,MaxScale
硬件:
1,F5

mysql MHA架构介绍

MHA提供了什么功能
1,监控主数据库服务器是否可用
2,当主DB不可用时,从多个从服务器中选举出新的主数据库服务器
3,提供了主从切换和故障转移功能
4,支持基于日志点复制,和GTID复制

MHA主从切换过程
1,尝试从出现故障的主数据库保存二进制日志
2,从多个备选服务器中选举出新的主服务器,在备选主服务器和其他从服务器之间同步差异二进制数据
3,应用从原主DB服务器上保存的二进制日志
4,提升备选主DB服务器为新的主DB服务器
5,迁移集群中的其他从DB作为新的主DB的从服务器

MHA配置步骤
1,配置集群内所有主机的SSH免认证登录
2,安装MHA-node软件包和MHA-manager软件包
3,建立主从复制集群
4,配置MHA管理节点
5,使用masterha_check_ssh和masterha_check_repl对配置进行检验
6,启动并测试MHA服务

MHA工具的优点
1,由perl语言开发的开源工具
2,可以支持基于GTID的复制模式
3,MHA在进行故障转移时更不易产生数据丢失
4,同一个监控节点可以监控多个集群

MHA工具的缺点
1,需要编写脚本或利用第三方工具来实现Vip(虚拟IP)的配置
2,MHA启动后只会对主数据库进行监控
4,需要基于SSH免认证配置,存在一定的安全隐患
5,没有提供从服务器的读负载均衡功能

MMM架构介绍

提供的功能
MMM监控mysql主从复制健康情况
在主库出现宕机时进行故障转移并自动配置其它从对新主的复制
提供了主,写虚拟IP,在主服务器出现问题时可以自动迁移虚拟IP

部署所需资源
1

mysql复制常见问题处理

由于数据损坏丢失所引起的主从复制错误
主库或从库意外宕机引起的错误
解决办法:
1,使用跳过二进制日事件
2,注入空事务的方式先恢复中断的复制链路
3,在使用其他方法来对比主从服务器上的数据

主库上的二进制日志损坏
解决办法:
1,通过change master命令来重新指定

备库上的中继日志损坏
在从库上进行数据修改造成的主从复制错误
不唯一的server_id或server_uuid
max_allow_packet设置引起的主从复制错误

mysql复制无法解决的问题
分担主数据库的写负载
自动进行故障转移及主从切换
提供读写分离功能