MySQL优化策略

当数据库出现性能瓶颈时，我们需要进行优化，目前有两类的优化策略

硬件层优化：增加机器资源，提升性能
软件层优化：SQL调优，表结构优化，读写分离，分库分表，数据库集群
数据库性能瓶颈的对外表现：

大量请求被阻塞：高并发场景下，连接数不够，大量请求处于阻塞状态
SQL操作变慢：比如查询上亿数据的表，没有命中索引进行了全表扫描
存储问题：磁盘不够了
主要简单解析一下软件层的优化

1.SQL调优
在这个方向上，投入少部分精力往往就能获得较大的收益，是进行数据库优化的第一选择手段
SQL调优的目的就是让那些慢SQL变快，手段就是让SQL执行尽量命中索引

第一步：开启SQL慢查询日志
如果你使用的是 Mysql，需要在 Mysql 配置文件中配置几个参数即可。

slow_query_log=on
long_query_time=1
slow_query_log_file=/path/to/log
1
2
3
第二步：查看慢SQL语句是否命中索引
常常会用到 explain 这个命令来查看 SQL 语句的执行计划，通过观察执行结果很容易就知道该 SQL 语句是不是全表扫描、有没有命中索引。

explain select id, age, gender from user where name = 'xxxx'
1
返回有一列叫“type”，常见取值有：

ALL、index、range、 ref、eq_ref、const、system、NULL(从左到右，性能从差到好)

ALL 代表这条 SQL 语句全表扫描了，需要优化。一般来说需要达到range 级别及以上。

2.表结构优化
以一个场景举例说明：

“user”表中有 user_id、nickname 等字段，“order”表中有order_id、user_id等字段，如果想拿到用户昵称怎么办？一般情况是通过 join 关联表操作，在查询订单表时关联查询用户表，从而获取导用户昵称。

但是随着业务量增加，订单表和用户表肯定也是暴增，这时候通过两个表关联数据就比较费力了，为了取一个昵称字段而不得不关联查询几十上百万的用户表，其速度可想而知。

这个时候可以尝试将 nickname 这个字段加到 order 表中(order_id、user_id、nickname)，这种做法通常叫做数据库表冗余字段。这样做的好处展示订单列表时不需要再关联查询用户表了。

冗余字段的做法也有一个弊端，如果这个字段更新会同时涉及到多个表的更新，因此在选择冗余字段时要尽量选择不经常更新的字段。

3.读写分离
将数据库分为两类，一类专门处理读请求，一类专门处理写请求，二者之间通过数据库的复制来进行数据同步，比如mysql主从复制。

mysql主从复制请参考：Mysql主从复制

那么如何实现数据库的读写分离呢？什么时候请求应该打到读库，什么时候请求应该打到写库？

有几个方案

方案1：应用程序自己根据业务逻辑判断

在代码中根据select，inset等进行路由分类，增删改等写操作命令发送给写库，查询命令发送给读库，这种方式的优点是性能好，缺点就是开发需要修改代码，而且数据库和应用程序还是强耦合的

方案2：中间件代理

在应用和数据库之间弄一个代理服务器，代理服务器负责识别出对数据库的请求是读还是写，并且分发到不同的数据库中，常见的数据库中间件代理有：

MySQL-Proxy：MySQL开源项目，通过其自带的lua脚本进行SQL判断
Atlas：是由奇虎360的Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysgl业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程
Amoeba：由陈思儒开发，作者曾就职于阿里巴巴。该程序由Java语言进行开发，阿里巴巴将其用于生产环境。但是它不支持事务和存储过程。
mycat：MyCat是基于阿里开源的Cobar产品而研发
4.分库分表
1.分库
将每个服务相关的表拆出来单独建立一个数据库，这其实就是“分库”了。
单数据库的能够支撑的并发量是有限的，拆成多个库可以使服务间不用竞争，提升服务的性能。

分库之前：
分库之后：

2.分表
当单表数据增量过快，业界流传是超过500万的数据量就要考虑分表了
分表有几个维度，一是水平切分和垂直切分，二是单库内分表和多库内分表。

水平拆分和垂直拆分
就拿用户表(user)来说，表中有7个字段：id,name,age,sex,nickname,description，如果 nickname 和 description 不常用，我们可以将其拆分为另外一张表：用户详细信息表，这样就由一张用户表拆分为了用户基本信息表+用户详细信息表，两张表结构不一样相互独立。但是从这个角度来看垂直拆分并没有从根本上解决单表数据量过大的问题，因此我们还是需要做一次水平拆分。

还有一种拆分方法，比如表中有一万条数据，我们拆分为两张表，id 为奇数的：1，3，5，7……放在 user1， id 为偶数的：2，4，6，8……放在 user2中，这样的拆分办法就是水平拆分了。

水平拆分的方式也很多，除了上面说的按照 id 拆表，还可以按照时间维度取拆分，比如订单表，可以按每日、每月等进行拆分。

每日表：只存储当天的数据。
每月表：可以起一个定时任务将前一天的数据全部迁移到当月表。
历史表：同样可以用定时任务把时间超过 30 天的数据迁移到 history表。
总结一下水平拆分和垂直拆分的特点：

垂直切分：基于表或字段划分，表结构不同。
水平切分：基于数据划分，表结构相同，数据不同。
单库内表拆分和多库内表拆分
拿水平拆分为例，每张表都拆分为了多个子表，多个子表存在于同一数据库中。比如下面用户表拆分为用户1表、用户2表。

单库内表拆分：

在一个数据库中将一张表拆分为几个子表在一定程度上可以解决单表查询性能的问题，但是也会遇到一个问题：单数据库存储瓶颈。

所以在业界用的更多的还是将子表拆分到多个数据库中。比如下图中，用户表拆分为两个子表，两个子表分别存在于不同的数据库中。

多库内表拆分：
一句话总结：分表主要是为了减少单张表的大小，解决单表数据量带来的性能问题。

5.数据库集群
————————————————
版权声明：本文为CSDN博主「Ysming88」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/m0_45861545/article/details/126072914