我们常说的数据库优化，可以从哪些维度入手？

原副标题：他们常说的资料库强化，能从什么样层次侧发力？

当没人问你怎样对资料库展开强化时，许多人第三化学反应想不到的就是SQL强化，怎样建立检索，怎样重写SQL，他们把资料库强化与SQL强化画下了等号。

总之这不能称得上完全严重错误的提问，或者说思索的视点稍稍狭隘了些，太“开发人员观念”化了，没站在更多层面来思索提问。那那时他们就将视点神化，站在构架的视点来谈谈而此问题，资料库强化能从什么样层次侧发力？

正示意图右图，数据库强化能从构架强化，硬体强化，DB强化，SQL强化五个层次侧发力。

沙塘镇而下，边线越靠前强化越显著，对资料库的操控性提高越高。他们常说的SQL强化反倒是对操控性提高最轻的强化。

接下去他们再看一看五种强化该如何实行。

构架强化

一般而言在高mammalian的情景下对构架层展开强化其效用最显著，常用的强化方式有：分布式控制系统内存，随机存取分立，科艾麻该户等，五种强化方式又适用于于不同的应用领域情景。

分布式控制系统内存

有句俗话说的好，操控性不如，内存来凑。当须要在构架层展开强化时他们第三时间就会想不到内存这个宝物，在应用领域与资料库之间增加一个内存服务项目，如Redis或Memcache。

当转交到查阅允诺后，他们先查阅内存，推论内存中与否有统计数据，有统计数据就直接回到给应用领域，倘若没再查阅资料库，并读取到内存中，这样就大大增加了对资料库的出访单次，顺理成章也提高了资料库操控性。

不过须要特别注意的是，导入分布式控制系统内存后控制系统须要考量怎样应付内存反射、内存打穿和内存暴风雪的问题。

随机存取分立

一主多从，随机存取分立，主动同步，是一种常用的资料库构架强化方式。

一般而言当你的应用领域是读多写少，资料库扛不住读压力的时候，采用随机存取分立，通过增加从库数量能线性提高控制系统读操控性。

主库，提供资料库写服务项目；从库，提供资料库读能力；主从之间，通过binlog同步统计数据。

当准备实行随机存取分立时，为了保证高可用，须要实现故障的自动转移，主从构架会有潜在主从不一致性问题。

水平切分

水平切分，也是一种常用的资料库构架强化方式。

当你的应用领域业务统计数据量很大，单库容量成为操控性瓶颈后，采用水平切分，能降低资料库单库容量，提高资料库写操控性。

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

构架强化小结

硬体强化

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

吞吐率：单位时间内随机存取的数据量

IOPS：每秒IO操作的单次

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

DB强化

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

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

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

所以日志跟内存对资料库实例尤其重要。而连接如果不如用，资料库会直接抛出异常，控制系统无法出访。

接下去他们以Oracle、MySQL（InnoDB）、POSTGRES、达梦为例，看一看五种资料库的参数该怎样配置。

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软软解析

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避免应用领域连接定时中断

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或者更大

达梦资料库

参数分类参数名参数值备注统计数据内存MEMROY_TARGET、MEMROY_POOL物理内存90%

统计数据内存BUFFER物理内存60%统计数据内存统计数据内存MAX_BUFFER物理内存70%最大统计数据内存监听及连接max_sessions根据业务需求设置一般为业务预估连接数的120%

SQL强化

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

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

UNION ALL的执行效率比UNION高，UNION执行时须要排重；UNION须要对统计数据展开排序

执行SQL时强化器须要将 * 转成具体的列；每次查阅都要回表，不能走覆盖检索。

一般JOIN字段都提前加上检索

提高可阅读性；避免慢查阅的概率；能转换成多个短查阅，用业务端处理

RAND()导致统计数据列被多次扫描

执行计划

要想强化SQL必须要会看执行计划，执行计划会告诉你什么样地方效率低，哪里能须要强化。他们以MYSQL为例，来认识一下执行计划。

通过explain sql 能查看执行计划，如：

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

SQL强化实战

这里为大家准备了一套SQL强化的综合实战，一步一步带你走一遍完整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`)

);

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；

0.21s，执行速度还挺快

真是糟糕的执行计划。(全表扫描，没检索；临时表；排序)

初步强化思路：

初步强化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;

再次查看执行时间

再次查看执行计划

至此，他们的强化过程结束，结果非常完美。

SQL强化小结

这里给大家总结一下SQL强化的套路：

小结

他们那时分别从构架强化、硬体强化、DB强化、SQL强化四个视点探讨了怎样实行强化，提高资料库操控性。但是大家还是要记住一句话，资料库控制系统没银弹， 要让适合的控制系统，做合适的事情。