数据库相关优化方案

Bertha 。 2022-11-16 02:30 128阅读 0赞

数据库优化方案很多，主要分为两大类：软件层面、硬件层面。  
软件层面包括：**SQL 调优、表结构优化、读写分离、数据库集群、分库分表、缓存**等；  
硬件层面主要是**增加机器性能**。

# 一、SQL 调优 #

## 1）开启慢 SQL 记录 ##

如果你使用的是 Mysql，需要在 Mysql 配置文件中配置几个参数即可。

slow_query_log=on
    long_query_time=1
    slow_query_log_file=/path/to/log

## 2）调优的工具 ##

用PL/Sql Developer工具查看 SQL 语句的执行计划（快捷键F5），通过观察执行结果很容易就知道该 SQL 语句是不是全表扫描、有没有命中索引。  
比如

select e.nick_name from org_employee e 
    select e.nick_name from org_employee e where e.emp_name in '60052760'

![在这里插入图片描述][20210406093158349.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70]  
可以看出，第一条语句执行了全表扫描（TABLE ACCESS FULL）而第二条走了索引扫描(INDEX SCAN)。

## 3）表结构优化 ##

以一个场景举例说明：  
“user”表中有 user\_id、nickname 等字段，“order”表中有order\_id、user\_id等字段，如果想拿到用户昵称怎么办？一般情况是通过 join 关联表操作，在查询订单表时关联查询用户表，从而获取导用户昵称。

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

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

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

## 4）架构优化 ##

当单台数据库实例扛不住，我们可以增加实例组成集群对外服务。  
当发现读请求明显多于写请求时，我们可以让主实例负责写，从实例对外提供读的能力；  
如果读实例压力依然很大，可以在数据库前面加入缓存如 redis，让请求优先从缓存取数据减少数据库访问。  
缓存分担了部分压力后，数据库依然是瓶颈，这个时候就可以考虑分库分表的方案了

## 5）硬件优化 ##

在前期业务量比较小的时候，升级硬件数据库性能可以得到较大提升；但是在后期，升级硬件得到的收益就不那么明显了。

# 二、分库、分表 #

## 1）分库类型 ##

单应用单数据库  
多应用单数据库  
多应用多数据库

## 1）分表方式：水平分割（按行）、垂直分割(按列) ##

## 2）分表场景 ##

A： 根据经验，mysql表数据一般达到百万级别，查询效率就会很低。  
B： 一张表的某些字段值比较大并且很少使用。可以将这些字段隔离成单独一张表，通过外键关联，例如考试成绩，我们通常关注分数，不关注考试详情。

## 3）水平分表策略 ##

**按时间分表**：当数据有很强的实效性，例如微博的数据，可以按月分割。  
**按区间分表**：例如用户表 1到一百万用一张表，一百万到两百万用一张表。  
**hash分表**：通过一个原始目标id或者是名称按照一定的hash算法计算出数据存储的表名。

# 三、读写分离 #

当一台服务器不能满足需求时，采用读写分离【写: update/delete/add】的方式进行集群。  
一台数据库支持最大连接数是有限的，如果用户的并发访问很多，一台服务器无法满足需求，可以集群处理。mysql集群处理技术最常用的就是读写分离。

**主从同步**：数据库最终会把数据持久化到磁盘，集群必须确保每个数据库服务器的数据是一致的。从库读主库写，从库从主库上同步数据。  
**读写分离**：使用负载均衡实现，写操作都往主库上写，读操作往从服务器上读。

# 四、缓存 #

## 1）缓存分类 ##

本地缓存：HashMap/ConcurrentHashMap、Ehcache、Guava Cache等  
缓存服务：Redis/Tair/Memcache等

## 2）使用场景 ##

短时间内相同数据重复查询多次且数据更新不频繁，这个时候可以选择先从缓存查询，查询不到再从数据库加载并回设到缓存的方式。此种场景较适合用单机缓存。  
高并发查询热点数据，后端数据库不堪重负，可以用缓存来扛。

## 3）缓存作用 ##

减轻数据库的压力，减少访问时间。

## 4）缓存选择 ##

如果数据量小，并且不会频繁地增长又清空（这会导致频繁地垃圾回收），那么可以选择本地缓存。具体的话，如果需要一些策略的支持（比如缓存满的逐出策略），可以考虑Ehcache；如不需要，可以考虑HashMap；如需要考虑多线程并发的场景，可以考虑ConcurentHashMap。

其他情况，可以考虑缓存服务比如用Redis。

# 五、GVM调优 #

通过监控系统（如没有现成的系统，自己做一个简单的上报监控的系统也很容易）上对一些机器关键指标（gc time、gc count、各个分代的内存大小变化、机器的Load值与CPU使用率、JVM的线程数等）的监控报警，也可以看gc log和jstat等命令的输出，再结合线上JVM进程服务的一些关键接口的性能数据和请求体验，基本上就能定位出当前的JVM是否有问题，以及是否需要调优。

# 六、异步/多线程 #

针对某些客户端的请求，在服务端可能需要针对这些请求做一些附属的事情，这些事情其实用户并不关心或者用户不需要立即拿到这些事情的处理结果，这种情况就比较适合用异步的方式处理这些事情。

## 1）异步作用 ##

A：缩短接口响应时间，使用户的请求快速返回，用户体验更好。  
B：避免线程长时间处于运行状态，这样会引起服务线程池的可用线程长时间不够用，进而引起线程池任务队列长度增大，从而阻塞更多请求任务，使得更多请求得不到技术处理。  
C：线程长时间处于运行状态，可能还会引起系统Load、CPU使用率、机器整体性能下降等一系列问题，甚至引发雪崩。异步的思路可以在不增加机器数和CPU数的情况下，有效解决这个问题。

## 2）异步实现 ##

A：额外开辟线程，这里可以采用额外开辟一个线程或者使用线程池的做法，在IO线程（处理请求响应）之外的线程来处理相应的任务，在IO线程中让response先返回。  
B：使用消息队列（MQ）中间件服务

参考文章  
https://blog.csdn.net/u013628152/article/details/82184809  
https://blog.csdn.net/zhoupan301415/article/details/78257783

[20210406093158349.png]: /images/20221022/1161eff0ebb84da9bef5b295ee4d83ef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70]: /images/20221022/56d85ed36ae243f591f5eec3d60b122e.png