绪论

当有东谈主问你怎样对数据库进行优化时，好多东谈主第一反映猜想的等于SQL优化，怎样创建索引，怎样改写SQL，他们把数据库优化与SQL优化划上了等号。

虽然这不行算是齐备无理的恢复，只不外念念考的角度稍稍单方面了些，太“要领员念念维”化了，莫得站在更高级次来念念考恢复。那今天咱们就将视角拔高，站在架构的角度来聊聊这一问题，数据库优化不错从哪些维度脱手?

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

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

接下来咱们再望望每种优化该怎样实施。

一、架构优化

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

1、散播式缓存

有句老话说的好，性能不够，缓存来凑。当需要在架构层进行优化时咱们第一期间就会猜想缓存这个神器，在应用与数据库之间加多一个缓存工作，如Redis或Memcache。

当领受到查询肯求后，咱们先查询缓存，判断缓存中是否罕有据，罕有据就径直复返给应用，如若莫得再查询数据库，并加载到缓存中，这么就大大减少了对数据库的打听次数，自关联词然也提高了数据库性能。

不外需要防卫的是，引入散播式缓存后系统需要琢磨怎样搪塞缓存穿透、缓存击穿顺心存雪崩的问题。

浮浅聚合一下 缓存穿透、缓存击穿 和 缓存雪崩

缓存穿透：它是指当用户在查询一条数据的时候，而此时数据库顺心存齐没干系于这条数据的任何纪录。这条数据在缓存中没找到就会向数据库肯求赢得数据。它拿不到数据时，是会一直查询数据库，这么会对数据库的打听形成很大的压力。 缓存击穿：一个热门key刚好在某个期间点失效了，然而这时候蓦然来了无数对这个key的并发打听肯求，导致大并发肯求径直穿透缓存直达数据库，顷刻间对数据库的打听压力增大。 缓存雪崩：某一个期间段内，缓存聚积逾期失效，如果这个期间段内有无数肯求，而查询数据量繁密，所有这个词的肯求齐会达到存储层，存储层的调用量会暴增，引起数据库压力过大要使宕机。 2、读写分离

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

一般来说当你的应用是读多写少，数据库扛不住读压力的时候，给与读写分离，通过加多从库数目不错线性擢升系统读性能。

主库，提供数据库写工作;从库，提供数据库读智商;主从之间，通过binlog同步数据。

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

3、水平切分

水平切分，亦然一种常见的数据库架构优化妙技。

当你的应用业务数据量很大，单库容量成为性能瓶颈后，给与水平切分，不错箝制数据库单库容量，擢升数据库写性能。

当准备实施水平切分时，需要结合本色业务登科合理的分片键(sharding-key)，偶然候为了措置非分片键查接头题还需要将数据写到单独的查询组件，如ElasticSearch。

4、架构优化小结 读写分离主如若用于措置 “数据库读性能问题” 水平切分主如若用于措置“数据库数据量大的问题” 散播式缓存架构可能比读写分离更适用于高并发、大数据量大场景。 二、硬件优化

咱们使用数据库，不论是读操作已经写操作，最终齐是要打听磁盘，是以说磁盘的性能决定了数据库的性能。一块PCIE固态硬盘的性能是无为机械硬盘的几十倍不啻。这里咱们不错从朦拢率、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改写等操作 检讨优化后的本质期间和本质策画 如果优化效力不明显，访佛第四步操作 小结

咱们今天分裂从架构优化、硬件优化、DB优化、SQL优化四个角度探讨了怎样实施优化kaiyun官方网站，擢升数据库性能。然而群众已经要记着一句话，数据库系统莫得银弹， 要让适当的系统，作念合适的事情。