数据库的优化过程

前言

随着数据越来越多或者并发访问多时，系统的每一层都需要进行优化。增加服务器，冗余部署，限流等都是解决方案，那么对于 "有状态"的数据库怎样优化呢。数据库本身在IO CPU都有瓶颈。

下面讲讲数据库优化的几个阶段

1.优化sql、建索引

1. 首先找到慢sql，可以通过数据库配置慢sql的日志记录，或者通过阿里druid管理页面中看到sql的统计的情况。

数据库慢sql的记录配置

// 查看慢查询是否开启
show variables like ‘slow_query_log‘;
// 慢查询所规定的时间 MySQL5.21版以前long_query_time 参数的单位是秒，默认值是10。
// 这相当于说最低只能记录执行时间超过 1 秒的查询，怎么记录查询时间超过100毫秒的SQL语句记录呢？在mysql5.21+后版本支持毫秒记录
show variables like ‘long_query_time‘;
// 通过设置将其打开
set global slow_query_log=‘ON‘;
// 设置慢查询日志保存的位置
set global slow_query_log_file=‘/var/lib/mysql/test_1116.log‘;

2. explain 分析SQL语句，建立索引。或者service层进行业务计算

mysql中 执行语句 exlplain [你的sql语句]

返回结果如下，其中最重要的字段为：id、type、key、rows、Extra

以下为表格中某些具体的说明

select_type

1、SIMPLE：简单的select查询，查询中不包含子查询或者union
2、PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为primary
3、SUBQUERY：在select 或 where列表中包含了子查询
4、DERIVED：在from列表中包含的子查询被标记为derived（衍生），mysql或递归执行这些子查询，把结果放在零时表里
5、UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
6、UNION RESULT：从union表获取结果的select

type:

type包含的类型包括如下图所示的几种，从好到差依次是

system > const > eq_ref > ref > range > index > all

• system?表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
• const?表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
• eq_ref?唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
  ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
• range?只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
• index? ?Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
• all? ?Full Table Scan 将遍历全表以找到匹配的行

Extra

1. Using filesort

mysql对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是说mysql无法利用索引完成的排序操作成为“文件排序”

2. Using temporary

使用临时表保存中间结果，也就是说mysql在对查询结果排序时使用了临时表，常见于order by 和 group by

3. Using index

表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高
如果同时出现Using where，表明索引被用来执行索引键值的查找（参考上图）
如果没用同时出现Using where，表明索引用来读取数据而非执行查找动作

4. Using where

索引优点：不需要引入中间件，引入中间件就需要考虑的更多了。

需要思考的问题，那种索引在那些场景用？联合索引的最左匹配？索引建多少？索引的优缺点。

关于索引不再赘述有另外一篇文章。

2.加缓存

什么场景下缓存？

在优化sql无法解决问题的情况下，才考虑搭建缓存。毕竟你使用缓存的目的，就是将复杂的、耗时的、不常变的执行结果缓存起来，降低数据库的资源消耗。

缺点：加入缓存时就向上一步所说的一样，加入了中间件，增加了系统的复杂性，引入中间件带来新的问题。需要考虑数据库和缓存之间的一致性。以及使用缓存可能出现缓存雪崩、缓存穿透、缓存击穿、热key提前加载等问题。

3. 读写分离

使用主从复制的架构，主节点即可读也可写，主要是写。从节点作为读节点。主从架构实现数据库备份，实现数据库负载均衡，提高数据库可用性。

那么怎么去做读操作，写操作？1. 可以在业务层中区分读写的请求，去请求不同的数据库。2. 利用中间件mycat或者altas做读写分离，数据库之间的同步操作。

主从数据库之间是怎样做数据同步的呢？

如图所示，主库有一个log dump线程，将binlog传给从库
从库有两个线程，一个I/O线程，一个SQL线程，I/O线程读取主库传过来的binlog内容并写入到relay log,SQL线程从relay log里面读取内容，写入从库的数据库。
使用 读写分离的数据量 标准。

此时复杂性增高，如何保证主从之间的一致性。中从之间的延迟问题怎么解决？在从库中对刚写完的数据进行查找时是找不到的。根据CAP定理，主从架构本来就是一种高可用架构，是无法满足一致性的
哪怕你采用同步复制模式或者半同步复制模式，都是弱一致性，并不是强一致性。所以，推荐还是利用缓存，来解决该问题。
步骤如下:
1、自己通过测试，计算主从延迟时间，建议mysql版本为5.7以后，因为mysql自5.7开始，多线程复制功能比较完善，一般能保证延迟在1s内。不过话说回来，mysql现在都出到8.x了，还有人用5.x的版本么。
2、数据库的写操作，先写数据库，再写cache，但是有效期很短，就比主从延时的时间稍微长一点。
3、读请求的时候，先读缓存，缓存不存在(这时主从同步已经完成)，再读数据库。

如果主库崩了？数据在主库还未同步？

4. 分库分表

单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

当一张表的数据达到几千万时，查询一次所花的时间会变长。业界公认MySQL单表容量在 1千万 以下是最佳状态，因为这时它的BTREE索引树高在3~5之间。

主要两个维度，对于数据库垂直拆分或是水平拆分。对于表是垂直拆分还是水平拆分。垂直拆分也算是按业务进行划分，把常用的字段放一起，不常用的放一起。水平拆分指数据维度的拆分，比如按id散列分，按时间范围分等。

4.1 垂直拆分

垂直拆分原则一般是如下三点:
(1)把不常用的字段单独放在一张表。
(2)把常用的字段单独放一张表
(3)经常组合查询的列放在一张表中（联合索引）。

垂直拆分 分库

以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。

垂直拆分 分表

按字段为依据，按照字段的活跃性，将表中的字段拆分到不同表中。

结果：每个表的结构都不一样。之前的select * 现在需要从两个表里面查出来，这两个表之间有关联字段。

4.2 水平拆分

水平拆分不如垂直拆分，用垂直拆分，分成不同模块后，发现单模块的压力过大，你完全可以给该模块单独做优化，例如提高该模块的机器配置等。如果是水平拆分，拆成两张表，代码需要变动，然后发现两张表还不行，再变代码，再拆成三张表的？水平拆分模块间耦合性太强，成本太大，不是特别推荐。

水平拆分 分库

以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。

结果：每个库的结构都一样，每个库的数据都不一样。所有库的病机是全部数据。

水平拆分 分表

以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。

分库分表产生的问题：

1. 如果业务涉及到多库就会牵扯到分布式事务
2. 数据迁移，容量规划，扩容问题。比如按id散列时，你的数据库有两个那么对2取模之后得出要放的库或者要查的库在哪。如果需要扩容到三个库呢。建议利用对2的倍数取余具有向前兼容的特性（如对4取余得1的数对2取余也是1）来分配数据，避免了行级别的数据迁移，但是依然需要进行表级别的迁移，同时对扩容规模和分表数量都有限制。
3. 跨节点sql。如跨节点join、count、order by、group by以及聚合函数等。只要是进行切分，跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。因为它们都需要基于全部数据集合进行计算。count、group by等问题，一般的解决方案是分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。
4. 分布式唯一ID问题。BIGINT型可以使用Twitter的分布式自增ID算法Snowflake，VARCHAR类型可以考虑UUID。但是Snowflake也会有自己的问题，比如某些场景，生成的值大部分都是偶数。
5. sharding的时候还需要考虑自身的业务。比如根据用户ID分订单表，有些用户根本不下单，但是可能有些用户的订单量占比超过总量的80%，如果这些用户被sharding在了同一个库和表中，实际的sharding效果就会很差。这种情况就需要自定义分表规则。再比如按时间划分的话，相对来说新用户比老用户更活跃，产生的数据更多。

通过中间件分库分表：

sharding的中间件大概可以分成两大类，一种是基于jdbc的lib组件，一种是基于代理(Proxy)的中间件。

基于jdbc的lib组件，好处在于易于和Java服务集成、轻量；易于上手，无运维成本；业务直接到数据库，少一层proxy理论上性能更好。基于Proxy的中间件，需要在所有的数据源中间搭一个Proxy服务，Java的数据源只连接到Proxy上，由Proxy负责底层的分库分表，以及请求路由，优势在于解耦性比较高；可以找专门的DBA负责和运维Proxy，分库分表操作对于Java程序员透明化；易于实现监控、数据迁移、连接管理等功能；劣势就是运维成本的增加，小公司可能没有预算请专门的DBA和运维人员来做这个解耦工作。

lib组件包括：当当网sharding-sphere、蘑菇街TSharding；就是jar包使用起来无需额外部署，无其他依赖。

基于Proxy的中间件：TDDL、DBProxy、Atlas、oneproxy、vitess、mycat、cobar等。

其他方式的优化

• 数据库硬盘使用 SSD 固态

最后

分库分表如何平缓部署项目，平稳升级？

1. 停机部署

夜间部署，出现问题 回滚第二天晚上接着弄

• 写一个迁移程序，读老数据库，通过中间件写入新数据库1和新数据库2

2. 不停机部署

双写部署法

• 根据分库前后把数据分为历史数据和增量数据(新数据指写请求的sql)。同时对历史数据和增量数据进行操作。对于历史数据写一个迁移程序通过中间件的方式进行迁移，对于增量数据通过消息队列或者binlog进行存储。等历史数据分完之后将增量数据再迁移。

最好的方式还是binlog，消息队列对代码有严重的侵入性，会产生大量非业务代码，因为你要组装消息体。

双写部署带来的问题：

1. 如何区分历史数据和增量数据，通过主键id或通过记录创建时间。
2. 在迁移时系统对历史数据进行操作，如何处理新老数据库造成的不一致的问题。写请求都会记录到消息队列和binlog中后续会进行相同操作的。
3. 迁移之后怎么校验数据的一致性。先验数量是否一致，因为验数量比较快。至于验具体的字段，有两种方法:

有一种方法是，只验关键性的几个字段是否一致。

还有一种是 ，一次取50条(不一定50条，具体自己定，我只是举例),然后像拼字符串一样，拼在一起。用md5进行加密，得到一串数值。新库一样如法炮制，也得到一串数值，比较两串数值是否一致。如果一致，继续比较下50条数据。如果发现不一致，用二分法确定不一致的数据在0-25条，还是26条-50条。以此类推，找出不一致的数据，进行记录即可。

除了数据库，项目是如何平缓升级部署的？此处不延展讲，作为拓展。 部署的策略对比

References

• 数据库优化的几个阶段
• 分库分表的基本思想
• MySQL数据库之互联网常用分库分表方案
• 分库分表后如何部署上线
• explain用法和结果分析
• https://www.cnblogs.com/rjzheng/p/9597810.html

数据库的优化过程

原文：https://www.cnblogs.com/wei57960/p/13382877.html