MySQL基础10——初识集群管理

集群管理"/>

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协议的服务器，其

核心功能是分库分表。配合数据库的主从模式还可以实现读写分离。