MySQL架构优化

1.分表

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

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

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

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

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

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

2.读写分离

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

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

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

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

主从复制的过程：

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语句执行

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

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

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

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

3.分库

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

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

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

集群分布式模型：

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

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

MyCat影射关系图：

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.优化多表查询