Mysql优化策略

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

第二步：查看慢SQL语句是否命中索引

常常会用到 explain 这个命令来查看 SQL 语句的执行计划，通过观察执行结果很容易就知道该 SQL 语句是不是全表扫描、有没有命中索引。

explain select id, age, gender from  user where name = 'xxxx'

返回有一列叫“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表。

单库内表拆分：

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

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

多库内表拆分：

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

分库分表带来的复杂性

既然分库分表这么好，那我们是不是在项目初期就应该采用这种方案呢？不要激动，冷静一下，分库分表的确解决了很多问题，但是也给系统带来了很多复杂性，下面简要说一说。

• 1.跨库关联查询

  在单库未拆分表之前，我们可以很方便使用 join 操作关联多张表查询数据，但是经过分库分表后两张表可能都不在一个数据库中，如何使用 join 呢？

  有几种方案可以解决：

  • 字段冗余：把需要关联的字段放入主表中，避免 join 操作；

  • 数据抽象：通过ETL等将数据汇合聚集，生成新的表；

  • 全局表：比如一些基础表可以在每个数据库中都放一份；

  • 应用层组装：将基础数据查出来，通过应用程序计算组装；

• 2.分布式事务

  单数据库可以用本地事务搞定，使用多数据库就只能通过分布式事务解决了。

  常用解决方案有：基于可靠消息（MQ）的解决方案

• 3.排序、分页、函数计算问题

  在使用 SQL 时 order by， limit 等关键字需要特殊处理，一般来说采用分片的思路：

  先在每个分片上执行相应的函数，然后将各个分片的结果集进行汇总和再次计算，最终得到结果。

  分片：分片可以简单定义为将大数据库分布到多个物理节点上的一个分区方案。每一个分区包含数据库的某一部分，称为一个片

  分区：分区则是把一张表的数据分成 N 多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上。

• 4.分布式ID

  如果使用 Mysql 数据库在单库单表可以使用 id 自增作为主键，分库分表了之后就不行了，会出现id 重复。

  常用的分布式 ID 解决方案有：

  • UUID
  • 基于数据库自增单独维护一张 ID表
  • 号段模式
  • Redis 缓存
  • 雪花算法（Snowflake）
  • 百度uid-generator
  • 美团Leaf
  • 滴滴Tinyid

• 5.多数据源

  分库分表之后可能会面临从多个数据库或多个子表中获取数据，一般的解决思路有：客户端适配和代理层适配。

  业界常用的中间件有：

  • shardingsphere（前身 sharding-jdbc）
  • Mycat

5.数据库集群

具体请参考：Mysql集群方案简介