数据库优化
数据库优化可以从架构、硬件、DB 、SQL 优化四个维度入手。
此上而下，位置越靠前优化越明显，对数据库的性能提升越高。我们常说的 SQL 优化反而是对性能提高最小的优化。
接下来我们再看看每种优化该如何实施。
一、架构优化
在高并发的场景下一般都会对架构层进行优化，其效果也最为明显，最常见的优化手段有：分布式缓存，读写分离，分库分表等，每种优化手段又适用于不同的应用场景、解决不同的问题，具体需要结合业务情况做判断。
1、分布式缓存
有句老话说的好，性能不够，缓存来凑。当需要在架构层进行优化时我们第一时间就会想到缓存这个神器，在应用与数据库之间增加一个缓存服务，如 Redis 或 Memcache。
当接收到查询请求后，我们先查询缓存，判断缓存中是否有数据（扩展：布隆过滤器 BloomFilter），有数据就直接返回给应用，如若没有再查询数据库，并加载到缓存中，这样就大大减少了对数据库的访问次数，自然而然也提高了数据库性能。
不过需要注意的是，引入分布式缓存后系统需要考虑如何应对缓存穿透、缓存击穿和缓存雪崩的问题。
简单理解一下 缓存穿透、缓存击穿 和 缓存雪崩
缓存穿透：它是指当用户在查询一条数据的时候，而此时数据库和缓存都没有关于这条数据的任何记录。这条数据在缓存中没找到就会向数据库请求获取数据。它拿不到数据时，是会一直查询数据库，这样会对数据库的访问造成很大的压力。
缓存击穿：一个热点 key 刚好在某个时间点失效了，但是这时候突然来了大量对这个 key 的并发访问请求，导致大并发请求直接穿透缓存直达数据库，瞬间对数据库的访问压力增大。
缓存雪崩：某一个时间段内，缓存集中过期失效，如果这个时间段内有大量请求，而查询数据量巨大，所有的请求都会达到存储层，存储层的调用量会暴增，引起数据库压力过大甚至宕机。
2、读写分离
一主多从，读写分离，主动同步，是一种常见的数据库架构优化手段。
一般来说当你的应用是读多写少，数据库扛不住读压力的时候，采用读写分离，通过增加从库数量可以线性提升系统读性能。
主库，提供数据库写服务； 从库，提供数据库读能力； 主从之间，通过 binlog 同步数据。
当准备实施读写分离时，为了保证高可用，需要实现故障的自动转移，主从架构会有潜在主从不一致性问题。
3、水平切分
水平切分，也是一种常见的数据库架构优化手段。
当你的应用业务数据量很大，单库容量成为性能瓶颈后，采用水平切分，可以降低数据库单库容量，提升数据库写性能。
当准备实施水平切分时，需要结合实际业务选取合理的分片键（sharding-key），有时候为了解决非分片键查询问题还需要将数据写到单独的查询组件，如 ElasticSearch。
4、架构优化小结
读写分离主要是用于解决 “数据库读性能问题”
水平切分主要是用于解决“数据库数据量大的问题”
分布式缓存架构可能比读写分离更适用于高并发、大数据量大场景。
二、硬件优化
我们使用数据库，不管是读操作还是写操作，最终都是要访问磁盘，所以说磁盘的性能决定了数据库的性能。一块 PCIE 固态硬盘的性能是普通机械硬盘的几十倍不止。北京java培训这里我们可以从吞吐率、IOPS 两个维度看一下机械硬盘、普通固态硬盘、PCIE 固态硬盘之间的性能指标。
吞吐率：单位时间内读写的数据量
机械硬盘：约 100MB/s ～ 200MB/s
普通固态硬盘：200MB/s ～ 500MB/s
PCIE 固态硬盘：900MB/s ～ 3GB/s
IOPS：每秒 IO 操作的次数
机械硬盘：100 ～200
普通固态硬盘：30000 ～ 50000
PCIE 固态硬盘：数十万
通过上面的数据可以很直观的看到不同规格的硬盘之间的性能差距非常大，当然性能更好的硬盘价格会更贵，在资金充足并且迫切需要提升数据库性能时，尝试更换一下数据库的硬盘不失为一个非常好的举措，你之前遇到 SQL 执行缓慢问题在你更换硬盘后很可能将不再是问题。
三、DB 优化
SQL 执行慢有时候不一定完全是 SQL 问题，手动安装一台数据库而不做任何参数调整，再怎么优化 SQL 都无法让其性能最大化。要让一台数据库实例完全发挥其性能，首先我们就得先优化数据库的实例参数。
数据库实例参数优化遵循三句口诀： 日志不能小、缓存足够大、连接要够用。
数据库事务提交后需要将事务对数据页的修改刷（ fsync）到磁盘上，才能保证数据的持久性。这个刷盘，是一个随机写，性能较低，如果每次事务提交都要刷盘，会极大影响数据库的性能。数据库在架构设计中都会采用如下两个优化手法：
先将事务写到日志文件 RedoLog（WAL），将随机写优化成顺序写
加一层缓存结构 Buffer，将单次写优化成顺序写
所以日志跟缓存对数据库实例尤其重要。而连接如果不够用，数据库会直接抛出异常，系统无法访问。
接下来我们以 Oracle、MySQL（InnoDB）、POSTGRES、达梦为例，看看每种数据库的参数该如何配置。
1、Oracle
2、MySQL
3、POSTGRES
4、达梦数据库
四、SQL 优化
SQL 优化很容易理解，就是通过给查询字段添加索引或者改写 SQL 提高其执行效率，一般而言，SQL 编写有以下几个通用的技巧：
1）合理使用索引
索引少了查询慢；索引多了占用空间大，执行增删改语句的时候需要动态维护索引，影响性能 选择率高（重复值少）且被 where 频繁引用需要建立 B 树索引；一般 join 列需要建立索引；复杂文档类型查询采用全文索引效率更好；索引的建立要在查询和 DML 性能之间取得平衡；复合索引创建时要注意基于非前导列查询的情况
2）使用 UNION ALL 替代 UNION
UNION ALL 的执行效率比 UNION 高，UNION 执行时需要排重；UNION 需要对数据进行排序
3）避免 select * 写法
执行 SQL 时优化器需要将 * 转成具体的列；每次查询都要回表，不能走覆盖索引。
4）JOIN 字段建议建立索引
一般 JOIN 字段都提前加上索引
5）避免复杂 SQL 语句
提升可阅读性；避免慢查询的概率；可以转换成多个短查询，用业务端处理
6）避免 where 1=1 写法
7）避免 order by rand()类似写法
RAND()导致数据列被多次扫描
1、执行计划
要想优化 SQL 必须要会看执行计划，执行计划会告诉你哪些地方效率低，哪里可以需要优化。我们以 MYSQL 为例，来认识一下执行计划。
通过 explain sql 可以查看执行计划，如：
2、SQL 优化实战
这里为大家准备了一套 SQL 优化的综合实战，一步一步带你走一遍完整 SQL 优化的过程。
在执行优化之前我们需要先认识一下原始表及待优化的 SQL。
1）原数据库表结构
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`)
);2）待优化的 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；3）原表数据量：
4）原执行时间
0.21s，执行速度还挺快
5）原执行计划
真是糟糕的执行计划。 (全表扫描，没有索引； 临时表； 排序)
①初步优化思路：
SQL 中 where 条件字段类型要跟表结构一致，表中 user_id 为 varchar(50)类型，实际 SQL 用的 int 类型，存在隐式转换，也未添加索引。将 b 和 c 表 user_id 字段改成 int 类型。
因存在 b 表和 c 表关联，将 b 和 c 表 user_id 创建索引
因存在 a 表和 b 表关联，将 a 和 b 表 seller_name 字段创建索引
利用复合索引消除临时表和排序
②初步优化 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);
③查看优化后的执行时间
通过执行计划可以看到，执行时间从 0.21s 优化成了 0.01s，执行时间近乎缩短 20 倍。
④查看优化后的执行计划
执行计划显示从全表扫描优化成了走索引，rows 减少，但是此时出现了 2 个告警。
⑤通过 show warning 语句 查看告警信息
提示 gmt_crteate 的格式不对，mysql 进行了隐式转换导致不能使用索引。
⑥继续优化，修改 gmtc-create 的格式
alter table a modify "gmt_create" datetime DEFAULT NULL;⑦再次查看执行时间
⑧再次查看执行计划
至此，我们的优化过程结束，结果非常完美。
3、SQL 优化小结
这里给大家总结一下 SQL 优化的套路：
查看执行计划 explain sql
如果有告警信息，查看告警信息 show warnings;
查看 SQL 涉及的表结构和索引信息
根据执行计划，思考可能的优化点
按照可能的优化点执行表结构变更、增加索引、SQL 改写等操作
查看优化后的执行时间和执行计划
如果优化效果不明显，重复第四步操作
共有 0 条评论