集群管理"/>
MySQL基础10——初识集群管理
change buffer
innodb存储引擎对非唯一辅助索引的 DML 操作进行缓冲;包含 insert buffer,delete buffer,
purge buffer;
change buffer 是一颗B+树,所有的非唯一辅助索引的 change buffer 存储在一棵B+树上,存储
在共享存储表空间;
对于非唯一辅助索引的 DML 操作不是每一次直接插入到索引页,先判断插入的非唯一辅助索引页
是否在缓冲区中,如果在,直接插入;如果不在,则先放到一个 change buffer 对象中,然后再
以一定的频率将 change buffer当中的数据合并到索引页中;
自适应哈希索引
hash索引的时间复杂度是 O(1);
自适应hash索引通过缓冲池的B+树页来构建的;innodb存储引擎会自动根据访问的频率和模式来
自动地为某些热点页建立hash索引;启用后,读取和写入速度提高2倍,辅助索引的操作性能提高
5倍;
只能用于等值查询,不能用于范围查询;
B+树层高问题
B+树的一个节点对应一个数据页;B+树的层越高,那么要读取到内存的数据页越多,io次数越
多;
innodb一个节点16kB;
假设:
key为10byte且指针大小6byte,假设一行记录的大小为1kB;
那么一个非叶子节点可存下16kB/16byte=1024个(key+point);每个叶子节点可存储1024行数
据;
结论:
2层B+树叶子节点1024个,可容纳最大记录数为: 1024 * 16 = 16384;
3层B+树叶子节点1024 * 1024,可容纳最大记录数为:1024 * 1024 * 16 = 16777216;
4层B+数叶子节点1024 * 1024 * 1024,可容纳最大记录数为:1024 * 1024 * 1024 * 16 =
17179869184;
自增 id
超过类型最大值会报错;
假设采用 bigint 1秒插入1亿条数据,大概需要5849年才会用完索引;
主从复制
原理图
【图源自网络】
binlog
产生时机,事务提交后;
重启生成新的binlog;
flush logs 生成新的binlog;
作用
使用binlog恢复数据(增量数据);
用来主从复制(replication);
开启
[mysqld] log-bin=mysql-bin
# 每提交n次事务,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入 磁盘。
sync_binlog=1
# 开启两段式事务提交
# 1.prepare,先将redolog持久化到磁盘;
# 2.prepare成功,再将事务日志持久化到binlog;
# 3.再在redolog上持久化commit操作;
innodb_support_xa=1
总结
1.binlog 文件会随服务的启动创建一个新文件;
2.通过 flush logs 可以手动刷新日志,生成一个新的 binlog 文件;
3.通过 show master status 可以查看 binlog 的状态;
4.通过 reset master 可以清空 binlog 日志文件;
5.通过 mysqlbinlog 工具可以查看 binlog 日志的内容;
6.通过执行 DML 操作,mysql 会自动记录 binlog;
数据恢复
show master status;
show binlog events in 'mysql57-bin.000001';
# 查看binlog
mysqlbinlog mysql-bin.0000xx
# 恢复数据
mysqlbinlog mysql-bin.0000xx | mysql -u 用户名 -p 密码 数据库名
# mysqlbinlog mysql-bin.000002 --start-position 154 --stop-position 755 | mysql -uroot -p123456
# --start-datetime:从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间
# --stop-datetime:从二进制日志中读取指定小于时间戳或者等于本地计算机的时间
# --start-position:从二进制日志中读取指定 position 事件位置作为事件开始
# --stop-position:从二进制日志中读取指定 position 事件位置作为事件截至
读写分离
mysql-proxy
项目发展是有序的,从单节点到多个节点,怎么做到无痛升级?应用程序尽量不改动,使用proxy
层来实现功能;
mysql-proxy是mysql官方提供的mysql中间件服务,上游可接入若干个mysql-client,后端可连接
若干个mysql-server。它使用mysql协议,任何使用mysql-proxy的下游无需修改任何代码,即可
迁移至mysql-proxy上。
原理
go-mysql-transfer lua脚本 读从 写主 读要求一致性高 去主数据库读
mysql-proxy向用户提供了6个 hook 点;让用户通过Lua脚本来完成功能,这些 hook 点是以函数
的形式提供的,用户可以实现这些函数,在不同事件、不同操作发生时,来定义我们需要的行为。
connect_server()
mysql-client与mysql-proxy建立连接时,mysql-proxy会调用该函数。用户可以实现该函
数,来实现负载均衡工作。注意:若有多个mysql-server后端,而用户没有实现这个函数,
那么mysql-proxy默认采用轮询(round-robin)策略。
reqd_handshake()
mysql-server向mysql-proxy返回握手数据包时,mysql-proxy会调用该函数;用户可以实现
该函数,来实现权限验证工作。
read_auth()
mysql-client向mysql-proxy发送认证数据包(包括用户名、密码以及数据库)时,mysql
proxy会调用该函数;
read_auth_result()
mysql-server向mysql-proxy返回认证回包时,mysql-proxy会调用该函数;
read_query()
mysql-client经由mysql-proxy向mysql-server发送query数据包时,mysql-proxy会调用该
函数;用户可以在这层实现修改请求内容、请求路由、亦或直接返回请求等操作;
read_query_result()
mysql-server经由mysql-proxy向mysql-client发送query回包时,mysql-proxy会调用该函
数;用户可以在这层做合并数据包或者对结果集进行修改;
分表分库
水平拆分和垂直拆分;
为了解决由于数据量过大而导致数据库性能降低的问题,采取分库分表从而达到提升数据库性能的
目的;
什么时候分表分库? 当一个表的数据量达到500w条或者表文件大小大于2G;(可以根据分区来做到均匀分布,一般是hash分区)
垂直分表
水平分表
垂直分库
水平分库
垂直拆分
特点
-
每个库(表)的结构都不一样;
-
每个库(表)的至少有一列数据一样;
-
并集是全量数据;
优点
-
业务清晰;
-
维护简单;
缺点
-
若单表数据量大,该表依然查询效率低;
-
关联查询困难;
问题
-
主键避重 (主键可能会出现重复)
-
公共表处理
-
事务一致性 (分布式事务)
-
跨节点关联查询
-
跨节点分页、排序函数;
原则
原则一:能不分就不分,1000 万以内的表,不建议分片,通过合适的索引,读写分离等方式,可
以很好的解 决性能问题。
原则二:分片数量尽量少,分片尽量均匀分布在多个 DataHost 上,因为一个查询 SQL 跨分片越
多,则总体 性能越差,虽然要好于所有数据在一个分片的结果,只在必要的时候进行扩容,增加
分片数量。
原则三:分片规则需要慎重选择,分片规则的选择,需要考虑数据的增长模式,数据的访问模式,
分片关联 性问题,以及分片扩容问题,最近的分片策略为范围分片,枚举分片,一致性 Hash 分
片,这几种分片都有利于 扩容
原则四:尽量不要在一个事务中的 SQL 跨越多个分片,分布式事务一直是个不好处理的问题
原则五:查询条件尽量优化,尽量避免 Select * 的方式,大量数据结果集下,会消耗大量带宽和
CPU 资源, 查询尽量避免返回大量结果集,并且尽量为频繁使用的查询语句建立索引。
实现
-
在应用层实现;
-
使用代理层;
Mycat
MyCat是目前最流行的基于Java语言编写的数据库中间件,是一个实现了MySql协议的服务器,其
核心功能是分库分表。配合数据库的主从模式还可以实现读写分离。
更多推荐
MySQL基础10——初识集群管理
发布评论