数据库优化详解分析

柔情只为你懂 2024-04-01 17:01 62阅读 0赞

**目录**

一、框架优化

1.1.分布式缓存

1.2.读写分离

1.3.分库分表 水平切分

二、硬件优化

2.1.吞吐率：单位时间内读写的数据量

2.2.IOPS：每秒IO操作的次数

三、DB优化

3.1.Oracle

3.2.MySQL

3.3.Postgres

3.4. 达梦

四、SQL优化

4.1.合理使用索引

4.2.执行计划

4.3.索引失效情况

--------------------

数据库优化一般说的都是查询优化，主要的优化手段有：

![0d41342f816b45c18bb2a3796aaf3760.png][]

## 一、框架优化 ##

一般来说在高并发的场景下对架构层进行优化其效果最为明显，常见的优化手段有：

> 1、分布式缓存
> 
> 2、读写分离
> 
> 3、分库分表

每种优化手段又适用于不同的应用场景。

### 1.1.分布式缓存 ###

性能不够，缓存来凑。当需要在架构层进行优化时我们第一时间就会想到缓存这个神器，在应用与数据库之间增加一个缓存服务，如Redis或Memcache。

![52915fc303504ce483226caba7148bb5.png][]

当接收到查询请求后，我们先查询缓存，判断缓存中是否有数据，有数据就直接返回给应用，如若没有再查询数据库，并加载到缓存中，这样就大大减少了对数据库的访问次数，自然而然也提高了数据库性能。

不过需要注意的是，引入分布式缓存后系统需要考虑如何应对**缓存穿透、缓存击穿和缓存雪崩**的问题。

**缓存穿透（key不存在导致）**

> 缓存穿透说简单点就是大量请求的 key 根本不存在于缓存中，导致请求直接到了数据库上，根本没有经过缓存这一层。比如某个攻击者故意制造我们缓存中不存在的 key 发起大量请求，导致大量请求落到数据库上，使得数据库由于性能的原因崩溃，导致系统异常

解决办法

> 1、缓存无效 key  
> 如果缓存和数据库都查不到某个 key 的数据就写一个到 Redis 中去并设置过期时间，可以解决请求的 key 变化不频繁的情况，如果攻击者的恶意攻击，每次构建不同的请求 key，会导致 Redis 中缓存大量无效的 key 。很明显，这种方案并不能从根本上解决此问题
> 
> 2、使用布隆过滤器  
> 布隆过滤器是一个非常神奇的数据结构，通过它我们可以非常方便地判断一个给定数据是否存在于海量数据中
> 
> 具体是这样做的：把所有可能存在的请求的值都存放在布隆过滤器中，当用户请求过来，先判断用户发来的请求的值是否存在于布隆过滤器中。不存在的话，直接返回请求参数错误信息给客户端，存在的话才会走下面的流程

**缓存击穿（某一个key过期引起）**

> 如果缓存中的某个热点key数据过期了，此时大量的请求访问了该热点数据，就无法从缓存中读取，直接访问数据库，数据库很容易就被高并发的请求冲垮，这就是缓存击穿的问题
> 
> 击穿其实可以看做是雪崩的一个子集，解决方法一般有两种，设置热点数据永不过期和设置互斥锁
> 
> 所谓的互斥锁，就是保证同一时间只有一个业务线程更新缓存，对于没有获取互斥锁的请求，要么等待锁释放后重新读取缓存，要么就返回空值或者默认值

**缓存雪崩（在同一时间大面积key失效导致）**

> 缓存雪崩描述的就是这样一个简单的场景：缓存在同一时间大面积的失效，后面的请求都直接落到了数据库上，造成数据库短时间内承受大量请求
> 
> 还有一种缓存雪崩的场景是：有一些被大量访问数据（热点缓存）在某一时刻大面积失效，导致对应的请求直接落到了数据库上
> 
> 以上两种常见，就好比雪崩一样，摧枯拉朽之势，数据库的压力可想而知，可能直接就被这么多请求弄宕机了

解决办法

> 针对 Redis 服务不可用的情况：
> 
> 1.  采用 Redis 集群，避免单机出现问题整个缓存服务都没办法使用
> 2.  限流，避免同时处理大量的请求
> 
> 针对热点缓存失效的情况：
> 
> 1.  设置不同的失效时间比如随机设置缓存的失效时间
> 2.  缓存永不失效

### 1.2.读写分离 ###

一主多从，读写分离，主动同步，是一种常见的数据库架构优化手段。

一般来说当你的应用是**读多写少**，数据库扛不住**读压力**的时候，采用读写分离，通过增加从库数量可以线性提升系统读性能。

![665d3b53835542fea9e00e660b47ab9f.png][]

主库，提供数据库写服务；从库，提供数据库读能力；主从之间，通过binlog同步数据。

当准备实施读写分离时，为了保证高可用，需要实现故障的自动转移，主从架构会有潜在主从不一致性问题。

### 1.3.分库分表 水平切分 ###

水平切分，也是一种常见的数据库架构优化手段。

当你的应用 **业务数据量很大**，**单库容量成为性能瓶颈后**，采用水平切分，可以降低数据库单库容量，提升数据库写性能。

![f17b17450aac461b93076141ce49d906.png][]

当准备实施水平切分时，需要结合实际业务选取合理的分片键（sharding-key），有时候为了解决非分片键查询问题还需要将数据写到单独的查询组件，如ElasticSearch。

**架构优化小结**

> 1.  读写分离主要是用于解决 “数据库读性能问题”
> 2.  分表分库，水平切分主要是用于解决“数据库数据量大的问题”
> 3.  分布式缓存架构可能比读写分离更适用于高并发、大数据量大场景。

## 二、硬件优化 ##

使用数据库，不管是读操作还是写操作，最终都是要访问磁盘，所以说磁盘的性能决定了数据库的性能。一块PCIE固态硬盘的性能是普通机械硬盘的几十倍不止。这里我们可以从**吞吐率**、**IOPS**两个维度看一下机械硬盘、普通固态硬盘、PCIE固态硬盘之间的性能指标。

### 2.1.吞吐率：单位时间内读写的数据量 ###

>  *  机械硬盘：约100MB/s ～ 200MB/s
>  *  普通固态硬盘：200MB/s ～ 500MB/s
>  *  PCIE固态硬盘：900MB/s ～ 3GB/s

### 2.2.IOPS：每秒IO操作的次数 ###

>  *  机械硬盘：100 ～200
>  *  普通固态硬盘：30000 ～ 50000
>  *  PCIE固态硬盘：数十万

通过上面的数据可以很直观的看到不同规格的硬盘之间的性能差距非常大，当然性能更好的硬盘价格会更贵，在资金充足并且迫切需要提升数据库性能时，尝试更换一下数据库的硬盘不失为一个非常好的举措，你之前遇到SQL执行缓慢问题在你更换硬盘后很可能将不再是问题。

## 三、DB优化 ##

SQL执行慢有时候不一定完全是SQL问题，手动安装一台数据库而不做任何参数调整，再怎么优化SQL都无法让其性能最大化。要让一台数据库实例完全发挥其性能，首先我们就得先优化数据库的实例参数。

数据库实例参数优化遵循三句口诀：**日志不能小、缓存足够大、连接要够用。**

数据库事务提交后需要将事务对数据页的修改刷（ fsync）到磁盘上，才能保证数据的持久性。这个刷盘，是一个随机写，性能较低，如果每次事务提交都要刷盘，会极大影响数据库的性能。数据库在架构设计中都会采用如下两个优化手法：

*  先将事务写到日志文件RedoLog（WAL），将随机写优化成顺序写
 *  加一层缓存结构Buffer，将单次写优化成顺序写

所以日志跟缓存对数据库实例尤其重要。而连接如果不够用，数据库会直接抛出异常，系统无法访问。

接下来我们以Oracle、MySQL（InnoDB）、POSTGRES、达梦为例，看看每种数据库的参数该如何配置。

### 3.1.Oracle ###

> 参数分类参数名参数值备注数据缓存SGA\_TAGET、MEMORY\_TARGET
> 
> 物理内存70-80%越大越好
> 
> 数据缓存DB\_CACHE\_SIZE
> 
> 物理内存70-80%越大越好
> 
> SQL解析SHARED\_POOL\_SIZE4-16G
> 
> 不建议设置过大监听及连接PROCESSES、SESSIONS、OPEN\_CURSORS
> 
> 根据业务需求设置一般为业务预估连接数的120%
> 
> 其他SESSION\_CACHED\_CURSORS大于200软软解析

### 3.2.MySQL ###

> 参数分类参数名参数值备注数据缓存INNODB\_BUFFER\_POOL\_SIZE
> 
> 物理内存50-80%一般来说越大性能越好
> 
> 日志相关Innodb\_log\_buffer\_size16-32M根据运行情况调整
> 
> 日志相关sync\_binlog1、100、01安全性最好监听及连接max\_connections
> 
> 根据业务情况调整可以预留一部分值文件读写性能innodb\_flush\_log\_at\_trx\_commit2安全和性能的折中考虑其他wait\_timeout，interactive\_timeout28800避免应用连接定时中断

### 3.3.Postgres ###

> 参数分类参数名参数值备注数据缓存SHARED\_BUFFERS
> 
> 物理内存10-25%  
> 数据缓存CACHE\_BUFFER\_SIZE
> 
> 物理内存50-60%  
> 日志相关wal\_buffer8-64M不建议设置过大过小监听及连接max\_connections根据业务情况调整一般为业务预估连接数的120%
> 
> 其他maintenance\_work\_mem512M或更大  
> 其他work\_mem8-16M原始配置1M过小其他checkpoint\_segments32或者更大

### 3.4. 达梦 ###

> 参数分类参数名参数值备注数据缓存MEMROY\_TARGET、MEMROY\_POOL
> 
> 物理内存90%  
> 数据缓存BUFFER物理内存60%
> 
> 数据缓存数据缓存MAX\_BUFFER
> 
> 物理内存70%最大数据缓存监听及连接max\_sessions
> 
> 根据业务需求设置一般为业务预估连接数的120%

## 四、SQL优化 ##

SQL优化很容易理解，就是通过给查询字段添加索引或者改写SQL提高其执行效率，一般而言，SQL编写有以下几个通用的技巧：

### 4.1.合理使用索引 ###

> 索引少了查询慢；索引多了占用空间大，执行增删改语句的时候需要动态维护索引，影响性能 选择率高（重复值少）且被where频繁引用需要建立B树索引；一般join列需要建立索引；复杂文档类型查询采用全文索引效率更好；索引的建立要在查询和DML性能之间取得平衡；复合索引创建时要注意基于非前导列查询的情况

union的使用

使用UNION ALL替代UNION
    UNION ALL 的执行效率比 UNION 高，UNION 执行时需要排重；UNION需要对数据进行排序

select \* 的使用

避免select * 写法
    执行SQL时优化器需要将 * 转成具体的列；每次查询都要回表，不能走覆盖索引。

JOIN字段建议建立索引
    一般JOIN字段都提前加上索引
    
    避免复杂SQL语句
    提升可阅读性；避免慢查询的概率；可以转换成多个端查询，用业务端处理

避免where 1=1写法
    避免order by rand()类似写法
    RAND()导致数据列被多次扫描

### 4.2.执行计划 ###

要想优化SQL必须要会看执行计划，执行计划会告诉你哪些地方效率低，哪里可以需要优化。我们以MYSQL为例，来认识一下执行计划。

通过explain + sql 执行语句，可以查看执行计划：

![2e6544af24f74abdabc72cf00fd007f0.png][]

> 字段解释id每个被独立执行的操作标识，标识对象被操作的顺序，id值越大，先被执行，如果相同，执行顺序从上到下
> 
> 1、select\_type：查询中每个select 字句的类型table被操作的对象名称，通常是表名，但有其他格式
> 
> 2、partitions：匹配的分区信息(对于非分区表值为NULL)
> 
> 3、type：连接操作的类型
> 
> 4、possible\_keys：可能用到的索引
> 
> 5、key：优化器实际使用的索引(**最重要的列**) 从最好到最差的连接类型为const、eq\_reg、ref、range、index和ALL。当出现ALL时表示当前SQL出现了“坏味道”
> 
> 6、key\_len：被优化器选定的索引键长度，单位是字节
> 
> 7、ref：表示本行被操作对象的参照对象，无参照对象为NULLrows查询执行所扫描的元组个数（对于innodb，此值为估计值）
> 
> 8、filtered：条件表上数据被过滤的元组个数百分比
> 
> 9、extra：执行计划的重要补充信息，当此列出现Using filesort , Using temporary 字样时就要小心了，很可能SQL语句需要优化

SQL优化实例

**源数据库结构**

CREATE TABLE `a`(    `id`          int(11) NOT NULL AUTO_INCREMENT,    `seller_id`   bigint(20)                                       DEFAULT NULL,    `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,    `gmt_create`  varchar(30)                                      DEFAULT NULL,    PRIMARY KEY (`id`));CREATE TABLE `b`(    `id`          int(11) NOT NULL AUTO_INCREMENT,    `seller_name` varchar(100) DEFAULT NULL,    `user_id`     varchar(50)  DEFAULT NULL,    `user_name`   varchar(100) DEFAULT NULL,    `sales`       bigint(20)   DEFAULT NULL,    `gmt_create`  varchar(30)  DEFAULT NULL,    PRIMARY KEY (`id`));CREATE TABLE `c`(    `id`         int(11) NOT NULL AUTO_INCREMENT,    `user_id`    varchar(50)  DEFAULT NULL,    `order_id`   varchar(100) DEFAULT NULL,    `state`      bigint(20)   DEFAULT NULL,    `gmt_create` varchar(30)  DEFAULT NULL,    PRIMARY KEY (`id`));

**待优化的SQL**

查询当前用户在当前时间前后10个小时的订单情况，并根据订单创建时间升序排列

select a.seller_id,       a.seller_name,       b.user_name,       c.state
    from a,     b,     c 
    where a.seller_name = b.seller_name  and b.user_id = c.user_id  and c.user_id = 17  and a.gmt_create    BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)    AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)order by a.gmt_create

**SQL优化**

ALTER TABLE b MODIFY `user_id` INT ( 10 ) DEFAULT NULL;
    ALTER TABLE c MODIFY `user_id` INT ( 10 ) DEFAULT NULL;
    ALTER TABLE c ADD INDEX `idx_user_id` ( `user_id` );
    ALTER TABLE b ADD INDEX `idx_user_id_sell_name` ( `user_id`, `seller_name` );
    ALTER TABLE a ADD INDEX `idx_sellname_gmt_sellid` ( `gmt_create`, `seller_name`, `seller_id` );

### 4.3.索引失效情况 ###

1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。

2、or语句前后没有同时使用索引。

当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效

3、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引

数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。

4、在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。

5、查询条件务必以索引列开头，否则索引失效

**联合索引失效情况**  
例如创建的索引是 key index （b,c,d）为联合索引，那么

有效情况： b || bc || bcd 这三种情况单独使用都是有效的

失效情况：bd || cd || d || c 这些情况下是无效的，组合列跨列使用都不能使索引生效

联合索引是最左匹配原则

在索引的排序上 , 是先按b排序 , 再按c排序 , 再按d排序

表数据如下：

![61896e64126c44419d422ca76ef62537.png][]key 是主键 , b c d 创建了联合索引

生成的索引结构为:

先按照 b列排序，如果b列相同，继续按照c列排序，最后按照d列排序 结构如下

数据在联合索引 bcd 排序如下

### ![13b8696e39664bc2b5c4d1679709cfda.png][] ###

**SQL执行顺序**

1.  FROM：获取第一张表，称为原表table1，获取第二张表，称为原表table2，将两张表做笛卡尔积，生成第一张中间表Temp1。
2.  ON：根据筛选条件，在Temp1上筛选符合条件的记录，生成中间表Temp2。
3.  JOIN：根据连接方式的不同，选择是否在Temp2的基础上添加外部行。左外就把左表在Temp2中筛选掉的表添加回来生成Temp3，右外则是右表。
4.  WHERE：筛选表中的记录，对中间表temp3的记录进行过滤，生成temp4。
5.  GROUP BY：根据聚合键对表进行分组，即在temp4的基础上做分组，生成temp5。
6.  WITH：应用cube或rollup生成超组，在Temp5的基础上添加超组，生成Temp6。
7.  HAVING：对组进行筛选，生成temp7。
8.  SELECT：选取字段，对temp7进行字段的选取，生成temp8。
9.  DISTINCT：对字段进行去重，对temp8进行去重，生成temp9。
10. ORDER BY：按照排序键对表进行排序，对temp9中的表进行排序，生成temp10。
11. LIMIT（TOP）：对表进行分页，对temp10进行分页，生成temp11。

[0d41342f816b45c18bb2a3796aaf3760.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/a304f9b5e60a49fba059d2ac7a92979a.png
[52915fc303504ce483226caba7148bb5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/80798bc2b7a94c5f927d5cb735836ba9.png
[665d3b53835542fea9e00e660b47ab9f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/75af8af4c61344868e3d8d37981abc8f.png
[f17b17450aac461b93076141ce49d906.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/a1e7afb8752845039c46a68d745f5b7a.png
[2e6544af24f74abdabc72cf00fd007f0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/47e0116c44f046549523634aaf8d1257.png
[61896e64126c44419d422ca76ef62537.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/0cdafa0dd2d64914bdc080d5b4d981c7.png
[13b8696e39664bc2b5c4d1679709cfda.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/14a4db7dd4fe422da8bdcebb5837e8d2.png