聊聊数据库优化的4大手段

正如上图所示，数据库优化可以从架构优化，硬件优化，DB优化，SQL优化四个维度入手。

此上而下，位置越靠前优化越明显，对数据库的性能提升越高。我们常说的SQL优化反而是对性能提高最小的优化。

业务优化

• 查询跨度不能太大，限制三个月
• 数据归档，只查询最近一年的数据，老数据去历史数据里查询
• 导数数据，异步任务进行。

接下来我们再看看每种优化该如何实施。

一般来说在高并发的场景下对架构层进行优化其效果最为明显，常见的优化手段有：分布式缓存，读写分离，分库分表等，每种优化手段又适用于不同的应用场景。

要点：

• 1. 读写分离
• 2. 读多写少，加分布式缓存（redis）
• 3. 写多、海量数据：分库分表
• 4. 复杂查询，es 全文索引

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

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

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

IOPS：每秒IO操作的次数

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

通过上面的数据可以很直观的看到不同规格的硬盘之间的性能差距非常大。

不仅仅只有硬盘要优化，还有CPU，内存，网络等，具体看哪个是瓶颈。

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

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

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

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

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

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

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

mysql> SHOW VARIABLES LIKE 'slow_query%';
+---------------------+---------------------------------------------------------------------+
| Variable_name       | Value                                                               |
+---------------------+---------------------------------------------------------------------+
| slow_query_log      | OFF                                                                 |
| slow_query_log_file | C:ProgramDataMySQLMySQL Server 5.7DataLAPTOP-UHQ6V8KP-slow.log |
+---------------------+---------------------------------------------------------------------+
2 rows in set, 1 warning (0.02 sec)

mysql> SHOW VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.01 sec)

参数说明如下：

• slow_query_log：慢查询开启状态
• slow_query_log_file：慢查询日志存放的位置（一般设置为 MySQL 的数据存放目录）
• long_query_time：查询超过多少秒才记录

• 配置文件

[mysqld]
log-slow-queries=dirfilename
long_query_time=n

• sql

SET GLOBAL slow_query_log=ON/OFF;
SET GLOBAL long_query_time=n;
set global log_queries_not_using_indexes=on; 默认值为off 表示没有使用索引的sql查询也会记录下来

1. 使用 explain 分析SQL的执行计划，是否索引失效

   • https://andyoung.blog.csdn.net/article/details/122412683
   • sql 优化 5大步骤+10个案例，堪称SQL优化万能公式
   • 聊聊sql优化的15个小技巧

2. 以小表驱动大表

   • 详解 Mysql LEFT JOIN和JOIN查询区别及原理

   • MySQL IN、Exist关联查询时，我们为什么建议小表驱动大表？

3. 使用UNIOn ALL替代UNIOn

4. 避免使用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE。可以是话用UNIOn代替

5. 减少数据回表

• 生动的解释下什么是 MySQL 的“回表”？

6. 主键 id 优先自增，分布式使用 雪花ID，或者redis自增，自增步长

我们今天分别从架构优化、硬件优化、DB优化、SQL优化四个角度探讨了如何实施优化，提升数据库性能。但是大家还是要记住一句话，数据库系统没有银弹， 要让适合的系统，做合适的事情。

MySQL如何优化？ https://mp.weixin.qq.com/s/_E6szZ0WFtCImYKUfygSMA