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. 优化多表查询
来源: http://www.bubuko.com/infodetail-2501822.html