携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第4天，点击查看活动详情

网上挺多一谈到数据库优化就是SQL优化，诚然，SQL优化很重要，但是既然我想写的是数据库优化，就不能只包含SQL方面，还有一些架构层面的设计之类的，当然重在宏观思想，明白为什么要这么干，比如为什么要分库分表，为什么解决什么问题，就不过多赘述详细怎么去做，那些网上又很多相关的教程（不是因为我懒，真的）。

一、优化思路

我们说到性能调优，大部分时候想要实现的目标是让我们的查询更快。一个查询的流程又是由很多个环节组成的,每个环节都会消耗时间。我们要减少查询所消耗的时间，就要从每一个环节入手。

二、连接——配置优化

第一个环节是客户端连接到服务端，可能会出现的问题主要是因为服务端连接数不够导致的应用端获取不到连接（Mysql: error 1040: Too many connections ）

解决方案：

1、服务端

1.1增加服务端的可用连接数，修改max_ connections的大小(之前了解过，默认151，最大支持修改到100000)

show variables like 'max_connections;

1.2及时释放不活动的连接（交互式和非交互式的客户端的默认超时时间都是28800秒，8小时，我们可以把这个值调小）

show global variables like 'wait_timeout;

1.3 数据库建立集群，采用主从复制，分担服务器访问压力

集群的话必然面临如何让多台数据库节点的数据保持一致的问题，这个时候我们需要用到复制技术(replication) ，被复制的节点称为master,复制的节点称为slave。slave 本身也可以作为其他节点的数据来源，这个叫做级联复制。

附加：MySQL的主从复制是怎么实现的？读写分离

在之前第一篇MySQL架构学习中，可以开启BinLog， MySQL所有更新语句都会记录到Server层的binlog。有了这个binlog，从服务器会不断获取主服务器的binlog文件,然后解析里面的SQL语句，在从服务器上面执行一遍，保持主从的数据一致，这就是实现了读写分离，写只写入master节点，而读的请求可以分担到slave节点

1.4 分库分表/分片缓解单表数据量过大而带来的性能问题

分库分表分为垂直拆分（分的是库，减少并发压力）和水平拆分（分的是表，解决存储瓶颈）

2、客户端

2.1 单个客户端减少新建连接数，使用连接池来减少连接的新增

常见的古老的DBCP，C3P0，阿里的Druid、Hikari（Spring Boot 2.X默认连接池）注意，连接池不是越大越好，有时候连接池越大效率反而越低（之前了解过，每一个连接服务端都需要创建一个线程去处理它，连接数越多，服务端线程数就会越多，性能开销就越大）在Hikari的github文档中,给出了一个PostgreSQL数据库建议的设置连接池大小的公式（建议机器核数*2+1）

2.2 使用缓存减少一些慢的查询

例如数据量大，关联的表多，计算逻辑复杂等占用连接时间长的查询。比如使用Redis，缓存适用于实时性不是很高的业务，比如报表数据，一次查询要2分钟，但是可能一天只更新一次。

三、优化器——SQL语句的分析与优化

优化器的作用就是对SQL语句进行优化分析，生成执行计划。想要优化首先需要从每天执行的一堆SQL中找到SQL执行比较慢的来进行优化

1、慢查询日志 slow query log

1.1 打开慢日志开关

因为开启慢查询日志是有代价的(跟binlog、optimizer-trace 一样)，所以它默认是关闭的： show variables like 'slow_query%';

还有控制执行超过多长时间的SQL才记录到慢日志的参数,，默认是10秒。如果改成0秒的话就是记录所有的SQL。 show variables like '%long_query%';

参数的两种修改方式:

1、set动态修改参数(重启后失效)。

set@@global.slow_query_log=1;  -1开启，0关闭，重启后失效
set@@global.long_query_time=3;   -默认10秒，另开一个窗口后才会查到最新值
		
show variables like '%long_query%';
show variables like '%slow_query%';

2、修改配置文件my.cnf。以下配置定义了慢查询日志的开关、慢查询的时间、日志文件的存放路径。

slow_query_log = ON
long_query_time= =2
slow_query_log_file =/*******/localhost-slow.log

1.2 慢日志分析

通过show variables like 'slow_query_log_file';可以找到慢日志的位置。 mysqldumpslow 是mysql自带的慢日志分析工具，毕竟你不能一条条筛选日志。 mysqldumpslow --help 可以查看基本的操作例如:查询用时最多的10条慢SQL:

mysqldumpslow -s t-t 10 -g 'select' /******/mysqlocalhost-slow.log

Count代表这个SQL执行了多少次; Time代表执行的时间，括号里面是累计时间; Lock表示锁定的时间，括号是累计; Rows表示返回的记录数，括号是累计。

通过查看并分析慢日志，我们可以知道是哪些sql执行速度比较缓慢（定位问题），接下来我们需要分析问题，明白为什么慢，详见四步趟过MySQL之第四步：MySQL优化学习（下）

四步趟过MySQL之第四步：MySQL优化学习（上）