MySQL 性能优化总结

1 优化思路

 

作为架构师或者开发人员，说到数据库性能优化，你的思路是什么样的？

 

或者具体一点，如果在面试的时候遇到这个问题：你会从哪些维度来优化数据库，

你会怎么回答？

 

我们在第一节课开始的时候讲了，这四节课的目标是为了让大家建立数据库的知识

体系，和正确的调优的思路。

 

我们说到性能调优，大部分时候想要实现的目标是让我们的查询更快。一个查询的

动作又是由很多个环节组成的，每个环节都会消耗时间，我们在第一节课讲 SQL 语句的

执行流程的时候已经分析过了。

我们要减少查询所消耗的时间，就要从每一个环节入手。

 

 

 

2 连接——配置优化

 

第一个环节是客户端连接到服务端，连接这一块有可能会出现什么样的性能问题？

 

有可能是服务端连接数不够导致应用程序获取不到连接。比如报了一个 Mysql: error

1040: Too many connections 的错误。

我们可以从两个方面来解决连接数不够的问题：

 

1、从服务端来说，我们可以增加服务端的可用连接数。

如果有多个应用或者很多请求同时访问数据库，连接数不够的时候，我们可以：

 

（1）修改配置参数增加可用连接数，修改 max_connections 的大小：

show variables like 'max_connections' ; -- 修改最大连接数，当有多个应用连接的时候

 

（2）或者，或者及时释放不活动的连接。交互式和非交互式的客户端的默认超时时

间都是 28800 秒，8 小时，我们可以把这个值调小。

show global variables like 'wait_timeout' ; -- 及时释放不活动的连接，注意不要释放连接池还在使用的连接

 

2、从客户端来说，可以减少从服务端获取的连接数，如果我们想要不是每一次执行

SQL 都创建一个新的连接，应该怎么做？

 

这个时候我们可以引入连接池，实现连接的重用。

我们可以在哪些层面使用连接池？ORM 层面（MyBatis 自带了一个连接池）；或者

使用专用的连接池工具（阿里的 Druid、Spring Boot 2.x 版本默认的连接池 Hikari、老

牌的 DBCP 和 C3P0）。

 

我们这里说到了从数据库配置的层面去优化数据库。不管是数据库本身的配置，还

是安装这个数据库服务的操作系统的配置，对于配置进行优化，最终的目标都是为了更

好地发挥硬件本身的性能，包括 CPU、内存、磁盘、网络。

 

在不同的硬件环境下，操作系统和 MySQL 的参数的配置是不同的，没有标准的配置。

在我们这几天的课程里面也接触了很多的 MySQL 和 InnoDB 的配置参数，包括各种

开关和数值的配置，大多数参数都提供了一个默认值，比如默认的 buffer_pool_size，

默认的页大小，InnoDB 并发线程数等等。

 

这些默认配置可以满足大部分情况的需求，除非有特殊的需求，在清楚参数的含义

的情况下再去修改它。修改配置的工作一般由专业的 DBA 完成。

 

至于硬件本身的选择，比如使用固态硬盘，搭建磁盘阵列，选择特定的 CPU 型号这

些，更不是我们开发人员关注的重点，这个我们就不做过多的介绍了。

 

除了合理设置服务端的连接数和客户端的连接池大小之外，我们还有哪些减少客户

端跟数据库服务端的连接数的方案呢？

 

我们可以引入缓存。

 

3 缓存——架构优化

 

3.1 缓存

 

在应用系统的并发数非常大的情况下，如果没有缓存，会造成两个问题：一方面是

会给数据库带来很大的压力。另一方面，从应用的层面来说，操作数据的速度也会受到

影响。

 

我们可以用第三方的缓存服务来解决这个问题，例如 Redis。

 

 

 

运行独立的缓存服务，属于 架构层面 的优化。

为了减少单台数据库服务器的读写压力，在架构层面我们还可以做其他哪些优化措

施？

 

3.2 主从复制

 

如果单台数据库服务满足不了访问需求，那我们可以做数据库的集群方案。

集群的话必然会面临一个问题，就是不同的节点之间数据一致性的问题。如果同时

读写多台数据库节点，怎么让所有的节点数据保持一致？

 

这个时候我们需要用到复制技术（replication），被复制的节点称为 master，复制

的节点称为 slave。

 

主从复制是怎么实现的呢？

 

在第一节课我们说过，更新语句会记录 binlog，它是一 种逻辑日志。

有了这个 binlog，从服务器会获取主服务器的 binlog 文件，然后解析里面的 SQL

语句，在从服务器上面执行一遍，保持主从的数据一致。

 

这里面涉及到三个线程，连接到 master 获取 binlog，并且解析 binlog 写入中继日

志，这个线程叫做 I/O 线程。

 

Master 节点上有一个 log dump 线程，是用来发送 binlog 给 slave 的。

从库的 SQL 线程，是用来读取 relay log，把数据写入到数据库的。

这个是主从复制涉及到的三个线程。

 

做了主从复制的方案之后，我们只把数据写入 master 节点，而读的请求可以分担到

slave 节点。我们把这种方案叫做读写分离。

 

读写分离可以一定程度低减轻数据库服务器的访问压力，但是需要特别注意主从数

据一致性的问题。

 

我们在做了主从复制之后，如果单个 master 节点或者单张表存储的数据过大的时

候，比如一张表有上亿的数据，单表的查询性能还是会下降，我们要进一步对单台数据

库节点的数据进行拆分，这个就是分库分表。

 

3.3 分库分表

 

垂直分库，减少并发压力。水平分表，解决存储瓶颈。

垂直分库的做法，把一个数据库按照业务拆分成不同的数据库：

 

以上是架构层面的优化，可以用缓存，主从，分库分表。

 

第三个环节：

 

解析器，词法和语法分析，主要保证语句的正确性，语句不出错就没问题。由 Sever

自己处理，跳过。

 

第四步：优化器

 

4 优化器——SQL 语句分析与优化

 

优化器就是对我们的 SQL 语句进行分析，生成执行计划。

 

问题：在我们做项目的时候，有时会收到 DBA 的邮件，里面列出了我们项目上几个

耗时比较长的查询语句，让我们去优化，这些语句是从哪里来的呢？

 

我们的服务层每天执行了这么多 SQL 语句，它怎么知道哪些 SQL 语句比较慢呢？

第一步，我们要把 SQL 执行情况记录下来。

 

4.1 慢查询日志 slow query log

 

https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

 

4.1.1 打开慢日志开关

 

因为开启慢查询日志是有代价的（跟 bin log、optimizer-trace 一样），所以它默

认是关闭的：

 

show variables like 'slow_query%' ;

 

除了这个开关，还有一个参数，控制执行超过多长时间的 SQL 才记录到慢日志，默

认是 10 秒。

 

show variables like '%long_query%' ;

 

可以直接动态修改参数（重启后失效）。

set @@global.slow_query_log = 1 ; -- 1 开启， 0 关闭，重启后失效

set @@global.long_query_time = 3 ; -- mysql 默认的慢查询时间是 10 秒，另开一个窗口后才会查到最新值

show variables like '%long_query%' ;

show variables like '%slow_query%' ;

 

或者修改配置文件 my.cnf。

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

 

slow_query_log = ON

long_query_time = 2

slow_query_log_file = /var/lib/mysql/localhost-slow.log

 

模拟慢查询：

 

select sleep( 10 );

 

查询 user_innodb 表的 500 万数据（检查是不是没有索引）。

 

SELECT * FROM `user_innodb` where phone = '136' ;

 

4.1.2 慢日志分析

 

1、日志内容

 

show global status like 'slow_queries' ; -- 查看有多少慢查询

show variables like '%slow_query%' ; -- 获取慢日志目录

cat /var/lib/mysql/ localhost-slow.log

 

 

 

有了慢查询日志，怎么去分析统计呢？比如 SQL 语句的出现的慢查询次数最多，平

均每次执行了多久？人工肉眼分析显然不可能。