数据库——集群与读写分离 ＜---＞设计优化【补】

在一些大型网站业务场景中，单台数据库服务器所能提供的并发量已经无法满足业务需求，为了满足这种情况，一般而言是通过主从同步的方式来同步数据，在此基础上，通过读写分离来提升数据库的并发和负载能力。

一般可以采用横向扩展和库表切分的方法实现数据库处理能力的提升，因为单机的硬件处理能力一定是有上限。由此而生的相关技术有：读写分离和负载均衡 。

---

MySQL的主从复制

需要实现集群多台机器共同对外提供服务，需要考虑的是如何实现读操作和写操作的工作划分。首先就需要部署主从复制，只有实现了主从复制，才能在此基础上实现读写分离。

MySQL所支持的复制类型

基于语句的复制，MySQL默认采用的是基于语句的复制，效率比较高

基于行的复制，把改变的内容复制过去，而不是把命令复制到从服务器上再次执行一次

混合类型复制，默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制

主从复制的工作过程[重点]

master负责完成写操作，slave负责执行复制操作

1、在每个事务更新数据完成之前，master主机在二进制日志种记录这些改变binlog。再写入二进制日志完成后，master通知存储引擎提交事务

2、从机slave将master的binlog复制到其中的中继日志relaylog。首先slave启动一个工作线程IO线程，IO线程在master上打开一个普通的连接，然后开始binlog dump process。binlog dump process从master的二进制日志种读取操作事件，如果已经跟上了master，它会休眠并等待master产生新的事件。IO线程将事件写入中继日志relay log

3、SQL从线程从中继日志读取事件，重放其中的事件而更新slave种的数据，使slave种的数据和master种的一致

注意：master的并行更新在slave种是串行执行的

主从节点配置

一般常见的主从集群会采用3个节点构成，1主多从

实验方式2种：采用centos+虚拟机、windows下通过不同的端口配置1主1从

windows下采用解压版，防止在多次安装的时候告诉已安装mysql不允许再次安装

master配置

端口号使用3306

mysql的安装路径下，名称为my.ini

[mysqld] # 服务器的编号值，各个服务器的编号不能重复 
server-id=6
# 开启二进制日志 
log-bin=mysql-bin 
# 设置日志的过期时间，避免占满磁盘 
expire-logs-days=7 
# binlog-ignore-db不使用主从复制的数据库 binlog-do-db可以设置需要进行主从复制的数据库
binlog-ignore-db=mysql 
# 设置3306端口 
port=3306 
# mysql服务器具体的安装路径 
basedir=D:/software/mysql-8.0.15-master 
# mysql的本地数据文件的存储路径 
datadir=D:/software/mysql-8.0.15-master/data 
# 允许最大连接数 
max_connections=200 
# 允许连接失败的次数 
max_connect_errors=10 
# 默认编码字符集 
character-set-server=utf8mb4 
# 默认存储引擎 
default-storage-engine=INNODB 
# 默认使用mysql_native_password插件进行口令认证 

default_authentication_plugin=mysql_native_password 
[mysql]
 # 设置mysql客户端默认字符集 
default-character-set=utf8mb4 
[client] 
# 设置mysql客户端连接服务端时默认使用的端口 
port=3306 
default-character-set=utf8mb4

初始化操作 mysqld --initialize --console

将 mysql 添加到系统服务中 mysqld --install mymaster

启动 master 节点的数据库服务 net start mymaster

使用 mysql 客户端连接数据库修改 root 的默认口令 ``

---

 MySQL读写分离

读写分离实际上就是在主从复制的基础上，只在主服务器上执行写操作，只在从服务器上执行读操作。 基本原理就是让主数据库处理事务性操作，而从数据库处理select查询，数据库的主从复制用来将事务性操作导致的数据变更同步到集群种的从数据库上 。

典型的实现方式就是基于中间代理层的实现：代理一般位于客户端和服务器之间，代理服务器接收到客户端请求后通过判断所执行的操作后转发到对应的后端数据库服务器上

---

主从复制的原理

首先启动master，然后启动slave， 在master中执行命令 show master status ，File表示实现复制功能的日志，就是二进制日志binlog。

Position表示二进制日志文件的偏移量，偏移量之后的操作都会同步到slave，在偏移量之前的则需要收到导入 。

master上面的任何修改操作都会记录到二进制日志中，slave上会启动一个IO线程，连接到master上请求读取二进制日志文件，注意这里会有一定的延迟。slave将读取到的二进制日志数据写入到本地的中继日志relay log中。

slave会开启一个SQL线程，定时检查relay log的修改，如果发现有更改的内容，则在本机上执行一次修改操作。这样就是实现了将master上的修改同步到slave上的目的 。

如果是一主多从时，主库既要负责写又要负责为几个从库提供二进制日志，工作压力有点大。所以在一般的生产环境后可以稍作调整，将二进制日志只发送给某个从节点。这个从节点再开启二进制日志，并将自己的二进制日志再次发送给其它从节点，或者这个从节点不记录，只是负责将二进制日志转发给其它从节点。这样的结构性能会比简单的一主多从的模式性能好很多，而且数据之间的延迟应该会稍好一些。

读写分离实现

Mysql读写分离可以基于第三方插件，例如Mycat或者基于程序读写分离（应用内部路由）

应用内路由

基于spring的AOP实现。 用aop来拦截spring项目的dao层方法，根据方法名称就可以判断要执行的sql类型(即是read还是write类型)，进而动态切换主从数据源。

---

设计优化

·设计数据库时，应该充分考虑数据库表和字段的设计以及存储引擎的选择

1、尽量使用整型数表示字符串。例如存储IP地址：inet_aton(字符串)和inet_ntoa(数值)

2、表的字段类型一般不采用enum和set类型，因为维护成本太高，可以采用关联表的方式来替代enum

3、使用decimal可以对浮点数进行精确存放，但是存储空间会随着数值的增大而增大；一般建议使用固定空间，例如double，但是double会损失存储精度。

4、尽可能使用not null约束，针对允许为null的字段可以考虑定义default。由于null值判断比较繁琐。例如不要使用 id int ，可以考虑使用 id int default 0

5、字段注释需完整，最好可以见名知意

6、一般建议单表的字段不易过多。一般20-30就是极限

7、可以有预留字段。

总之所有的设计过程就是在性能和需求之间平衡的结果

·充分利用MySQL自身提供的功能，例如索引等

需要记忆NF3和反范式

需要记忆常见的索引失效情形和执行计划查询的命令 exaplain

·横向扩展，引入MySQL集群、负载均衡和读写分离

·SQL语句优化

1、使用limit对查询结果的记录进行限定

2、避免使用 select * ，将需要查询的字段列表出来 【原因】

3、使用join代替子查询

4、拆分大的delete或者insert语句【delete和truncate table】

5、可以通过开启慢查询定位应用中的执行较慢的sql语句

6、一般不进行列计算。例如 select id from t_users where age+1=10; 但是针对列的计算操作会导致整表扫描，一般建议查询时尽可能将操作移动到等号的右边 select if from t_users where age=10-1

7、sql语句尽可能简单，因为一个sql语句只能在一个CPU中进行计算，将大语句拆分为小语句，可以减少锁定时间，避免出现一个大sql语句锁定整个库的访问

8、or尽可能修改为in，因为or的效率为O(n)，而in的效率是O(logN)。但是in的个数建议控制在200以内

9、避免使用%xxx样式的查询

10、尽可能使用同类型数据进行比较，例如 '123'=123

11、尽量避免在where子句中使用!=操作符，因为可能会出现使用全表扫描

12、对于连续值使用between/and，不使用in