MySQL复制解决了什么问题:
实现在不同服务器上的数据分布
利用二进制日志增量进行
不需要太多的带宽
但是使用基于行的复制在进行大批量的更改时会对带宽带来一定的压力
特别是跨IDC环境下进行复制、
实现数据读取的负载均衡
需要其他组件配合完成
利用DNS轮训的方式把程序的读链接到不同的备份数据库
使用LVS，haproxy这样的代理方式
非共享架构，同样的数据分布在多台服务器上
增强了数据安全性
利用备库的备份来减少主库负载
复制并不能代替备份
方便进行数据库高可用架构的部署
避免单点失败
实现数据库高可用和故障切换
实现数据库在线升级
MySQL二进制文件
MySQL服务器日志
二进制日志（重要） 记录了所有对Mysql数据库的修改事件
包括增删改查时间和对表结构的修改事件 binlog命令行工具
慢查日志
通用日志
MySQL存储引擎层日志
innodb 重做日志redolog 回滚日志 undolog
二进制日志的格式
基于段的格式 binlog_format=STATEMENT
优点：
日志记录量相对较少，节约磁盘及网络I/O 提高性能
(只对一条记录修改或者插入
row格式所产生的日志量小于段生产的日志量)
缺点：
可能造成MySQL复制的主备服务器数据不一致
必须要记录上下文信息
保证语句在从服务器上执行结果和在主服务器上相同
特定函数如UUID()，user()这样非确定性函数还是无法复制
基于行的日志格式 binlog_format=ROW
Row格式可以避免MySQL复制中出现主从不一致的问题
同一SQL语句修改了10000条数据的情况下
基于段的日志格式只会记录这个SQL语句
基于行的日志会有10000条记录分别记录每一行的数据修改
优点：
使MySQL主从复制更加安全
对每一行数据的修改比基于段的复制高效
（误操作而修改了数据库中的数据，同时又没有备份可以恢复时，我们就可以通过分析二进制日志，对日志中记录的数据修改操作做反向处理的方式来达到恢复数据的目的）
缺点：
记录日志量较大
binlog_row_image=[FULL（记录所有 30列 如果只对1列修改会记录30列的修改）|MINIMAL（只会记录一列的修改前后变化）|NOBLOB]
混合日志格式 binlog_format=MIXED
混合基于段的和行的日志格式
特点
根据SQL语句由系统决定在基于段和基于行的日志格式中进行选择
数据量的大小由所执行的SQL语句决定
如何选择二进制日志的格式
建议
binlog_format=mixed
or
binlog_format=row (主从复制安全)
binlog_row_image=minimal 建议使用这种格式 减少I/O操作负载
基于MySQL二进制日志格式对复制的影响
基于SQL语句的复制（SBR）
优点：
生成的日志量小，节约网络传输I/O
并不强制要求主从数据库的表定义完全相同
相比于基于行的复制方式更为灵活
缺点：
对于非确定性时间，无法保证主从复制数据的一致性
如：特殊函数如UUID（） 主从服务器 数据不一致
对于存储过程，触发器，自定义函数进行的修改也可能造成数据不一致
相比于基于行的复制方式在从上执行时需要更多的行锁
基于行的复制（RBR）
优点：
可以应用于任何SQL的复制包括非确定函数，存储过程等。
如：特殊函数如UUID（）生成什么，直接在从服务器上更新为
可以减少数据库锁的使用
缺点：
要求主从数据库的表结构相同，否则可能会中断复制
无法在从服务器上单独执行触发器
对主从数据的一致性更加有保证，基于行的复制（RBR）
MySQL复制工作方式
1、主服务器将变更写入二进制日志
2、从服务器读取主服务器的二进制日志变更并写入到relay_log中
主服务器上会启动一个 二进制转储线程
基于日志点的复制
基于GTID的复制
3、在从服务器上重放relay_log中的日志
基于ＳＱＬ段的日志时在从服务器库上重新执行记录的ＳＱＬ
基于行的日志则时在从库上直接应用对数据库行的修改
配置MySQL复制
基于日志点的复制配置步骤
在主服务器DB上建立复制账号
create user 'repl' @'ip段' identified by ''password;
grant repliccation slave on . TO 'repl' @'ip段';
配置主数据库服务器
bin_log = mysql-bin
server_id = 100 (唯一的)
配置从数据库服务器
bin_log= mysql-bin
server_id = 101
relay_log = mysql-relay-bin 固定中继日志名字
log_slave_update = on [可选]
决定是否把slave线程存放的中继日志记录在从服务器中的二进制文件中 如果把从服务器会当作其他服务器的主服务器时 要配置
read_only=on[可选]
初始化从服务器数据
mysqldump --master-data=2-single-transaction
MYSQL复制 有时间再继续学习更多，目前先把主要的学习完毕。
第六章
当表中数据较少时，可以缓存到内存中 索引的作用不太起作用，当数据增加之后，索引作用将会很明显
MySQL支持的索引类型
在存储引擎层实现的，不同存储引擎底层实现可能不一样。
B-tree索引的特点
采用B+树存储数据，在B+树中叶子节点都包含指向下一个节点的指针
1.png
数据按照键值大小序存储，用指针链接。方便查找。
MyISAM通过数据物理位置来引用行，Innodb通过主键来引用行，叶子节点指向主键
B-tree 索引能够加快数据的查询速度
B-tree 索引更适合进行范围查找
在什么情况下可以用到B树索引
全值匹配的查询
如：order_sn = '98765432119900'
匹配最左前缀的查询
匹配列前缀查询
order_sn like '9876%'
匹配返回值的查询
order_sn >'98765432119900' and order_sn <'98765432119999'
精确匹配左前列并范围匹配另外一列
只访问索引的查询（覆盖查询）
B-tree 索引的使用限制
如果不是按照索引的最左列开始查找，则无法使用索引
使用索引时不能跳过索引中的左边的列
Not in 和 <> 操作无法使用索引
如果查询中由某个列的范围查询，则其右边所有列都无法使用索引
Hash索引的特点
innodb存储引擎根据Btree使用情况自行建立的 也成 自适应hash索引
Hash索引时基于Hash表实现的，只有查询条件精确匹配Hash索引中的所有列时，才能够使用到hash索引。
对于Hash索引中的所有列，存储引擎都会为每一行计算一个Hash码，Hash索引中存储的就是Hash码。
Hash索引的限制
Hash索引必须进行二次查找
Hash索引无法用于排序
Hash索引只能全值匹配，不支持部分索引查找，不支持范围查找
Hash索引中Hash码的计算可能存在Hash冲突
不适合重复值很多的列
为什么要使用索引？
索引大大减少了存储引擎需要扫描的数据量
innodb 一次I/O 最小存取单位是页，
一页为默认为16K， 索引小，一页可以存储索引数据越多，越快。
索引可以帮助我们进行排序以避免使用临时表
索引可以把随机I/O 变为顺序I/O
索引是不是越多越好？
索引会增加写操作的成本
太多的索引会增加查询优化器的选择时间
索引优化策略
索引列上不能使用表达式或函数
如:select ...... from product where to_days(out_date)-to_days(current_date)<=30
修改之后:select......from product where out_date <=date_add(current_date,interval 30 day) 索引列没有使用函数，可以使用索引。
MySQL 中B-tree 在innodb存储引擎中 索引大小不能超过767个字节，MyISAM不能超过1000个字节
前缀索引和索引列的选择性
create index index_name on table (col_name(n)); n是索引列宽度，取字符串的一部分 n 。
索引的选择性是不重复的索引值和表的记录数的比值，选择性越高，效率越快。
联合索引：
如何选择索引列的顺序
经常会被使用的列优先
选择性高的列优先
宽度小的列优先 （在不违反选择性的前提下）
覆盖索引：
优点：
可以优化缓存，减少磁盘IO操作
可以减少随机IO，变随机IO操作变为顺序IO操作
可以避免对Innodb主键索引的二次查询
可以避免MyISAM表进行系统调用
无法使用覆盖索引的情况：
存储引擎不支持覆盖索引（Memory存储引擎不支持覆盖索引）
查询中使用了太多的列
使用了双%号的like查询 （底层api限制，在内存中过滤）
使用索引来优化查询：
使用索引扫描来优化排序
1、通过排序操作
2、按照索引顺序扫描数据
索引的顺序和Order By子句的顺序完全一致
索引中所有列的方向（升序，降序）和Order By 子句完全一致
Order by中的字段全部在关联表中的第一张表中
利用索引优化锁
索引可以减少锁定的行数
索引可以加快处理速度，同时也加快了锁的释放
索引的维护和优化
删除重复和冗余的索引
例：在id列建立了主键、唯一、单列索引 是重复索引
index(a),index(a,b) 冗余索引
pt-duplicate-key-checker h = 127.0.0.1 检查索引
查找未被使用过的索引
使用下面的sql 可以查询出来 索引的使用次数
clipboard.png
更新索引统计信息及减少索引碎片
analyze table table_name
optimize table table_name 使用不当会导致锁表
网友评论