MySQL分表分库&读写分离相关

我会带着你远行 2022-05-27 03:21 245阅读 0赞

# MySQL架构优化 #

## 1.分表 ##

当项目上线后，数据将会几何级的增长，当数据很多的时候，读取性能将会下降，更新表数据的时候也需要更新索引，所以我们需要分表，当数据量再大的时候就需要分库了。

a.水平拆分：数据分成多个表

b.垂直拆分：字段分成多个表

c.插入/更新/删除数据和查询统计 MyISAM存储引擎有一个MERGE存储引擎，可以将多个表合成一个表，就可以进行这四种操作   InnoDB用alter able可以将变成MyISAM存储引擎，然后使用MERGE引擎

面试题:MERGE存储引擎将N个子表合并，那么在数据库中如何存储？

答案：MERGE是将N个真实的表组成一个大表，但是实际上还是存储的N个表

## 2.读写分离 ##

当数据不断增多的时候，数据库压力增大，可以把读和写分离开，读是一些机器，写是另一些机器，对应主从服务器，主服务器是写操作，从服务器读操作，可以有多个从服务器，而且大多数业务是读操作，京东，淘宝大量浏览商品，是读操作。

在主服务器写的同时，数据同步到从服务器，保持数据的完整性（主从复制）

主从复制的原理：基于主服务器的二进制日志（binlog）跟踪所有的对数据库的完整更改实现。因此，要实现主从复制，必须在主服务器上启动二进制日志。

主从复制是异步复制，所以有三个线程参与。主服务器一个线程（IO线程） 从服务器两个（IO线程和SQL线程）

![Center][]

主从复制的过程：

1）从数据库执行一个start slave开启主从复制

2）从数据库的IO线程会通过主数据库授权的用户请求连接主数据库，并请求主数据库的binlog日志指定位置 指定的命令为change master

3）主数据库收到IO请求，负责复制的IO线程根据请求读取指定的binlog文件信息，返回给从数据库IO线程，返回的信息除了日志文件，还有本次返回的日志内容和binlog名称和位置，binlog名称和位置会写在master-info文件中

4）从数据库获取内容和位置（binlog），写入到relaylog（从数据库）中继日志的最末端，并将新的binlog文件名和位置记录到Master-info文件中，方便下一次主数据库的binlog文件日志，指定位置从而方便定位

5）从数据库的SQL线程实时监测本地relaylog新增内容，解析为SQL语句执行

![Center 1][]

主从复制的弊端\-->延迟的解决方案：

1.定位问题\-->找到延迟瓶颈（是IO压力大\-->升级硬件/换成SSD（固态硬盘））

2.单线程从relaylog执行MySQL语句延迟\-->使用MySQL5.6以上版本多线程或者Tungsten第三方并行复制

3.若都不行，则直接分库

## 3.分库 ##

很早以前是使用Cobar方案（阿里开源但后续没有更新）

现在是使用MyCat，他是基于Cobar，使用的是MySQL通讯协议实现了分库，是一个代理服务器，不是普通的Web代理服务器，而是在应用服务器和后台数据库之间，有一个特性是无状态，容易部署负载均衡

原理：应用服务器传SQL语句\-->路由解析转发到不同的后台数据库\-->结果汇总返回

集群分布式模型：

![Center 2][]

(负载均衡一般使用在：网络优化/单点登录/集群分布式/高并发)

MyCat把逻辑数据库和数据表对应到真实的数据库和数据表，因此开发者只需要关心逻辑上的相关操作就行了，遮蔽了物理差异性

MyCat影射关系图：

![Center 3][]

MyCat工作流程;

1.应用服务器向MyCat发送SQL语句：select \* from user where id in(30,31,32)

2.MyCat前端通信模块与应用服务器通信，交给SQL解析模块

3.SQL解析模块解析完交给SQL路由模块

4.SQL路由模块id取模，余数为0，是db1，余数为1，是db2，以此类推

5.把SQL拆解为select \* from user where id in (30,31,32),交给SQL执行模块对应db1，db2，db3...

6.SQL执行模块通过后端分别在db1，db2，db3...执行语句，返回结果到数据集合并模块，然后返回给应用服务器

## 4.慢查询分析  调参数 ##

慢查询：指的是执行超过一定时间SQL查询语句，把这个记录到慢查询日志，方便开发人员看日志找问题。

主要是三个参数：long\_query\_time 定义慢查时间 slow\_query\_log 设置慢查询开关 slow\_query\_log\_file 设置慢查询日志文件路径

配置慢查询方法：

方法1：

set long\_query\_time=1;

set slow\_query\_log=on;

set slow\_query\_log\_file=’/data/slow.log’;

方法2：

/etc/my.conf设置参数开启慢查询，使用explain命令分析SQL，输出结果含义官方文档有

## 5.其他优化总结 ##

1.优化查询

a.使用explain分析

b.索引对查询速度的影响（注意like关键字、多列索引、OR关键字，见本人的另一篇文章MySQL的概述及其优化）

2.优化数据库结构

1.分表

2.增加中间表

3.优化多表查询

转载网址：[https://blog.csdn.net/wkyworkuno/article/details/55805466][https_blog.csdn.net_wkyworkuno_article_details_55805466]

[Center]: /images/20220527/2d50f37de92e4c5babd1f4cb2275afa1.png
[Center 1]: /images/20220527/7c1369c1943948458ab9889fb3ce11cb.png
[Center 2]: /images/20220527/e7459b607fd84834af3a92db30055de0.png
[Center 3]: /images/20220527/ec4f15c7d49f4ce0939eb14d31137bc1.png
[https_blog.csdn.net_wkyworkuno_article_details_55805466]: https://blog.csdn.net/wkyworkuno/article/details/55805466