Mysql性能优化指南

sgm_yagami

于 2022-09-19 16:46:46 发布

阅读量462

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/sgm_yagami/article/details/126937108

版权

查询过程示意图

当我们去优化MySQL查询性能的时候，了解查询过程可以更好的帮助我们在相应的环节上进行处理。

优化思路

当我们了解了MySQL的查询过程，结合实际经验，可以根据一下几个方面进行性能优化。

一、连接配置优化

服务端配置

服务端需要做的就是尽可能地多接受客户端的连接，或许你遇到过 error 1040: Too many connections 的错误？就是服务端的胸怀不够宽广导致的，格局太小！

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

增加可用连接数，修改环境变量 max_connections ，默认情况下服务端的最大连接数为151个；

mysql> show variables like 'max_connections';
+-----------------+-------+

| Variable_name   | Value |

+-----------------+-------+

| max_connections | 151   |

+-----------------+-------+

1 row in set (0.01 sec)

及时释放不活动的连接，系统默认的客户端超时时间是28800秒（8小时），我们可以把这个值调小一点。

mysql> show variables like 'wait_timeout';
+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| wait_timeout  | 28800 |

+---------------+-------+

1 row in set (0.01 sec)

客户端优化

客户端能做的就是尽量减少和服务端建立连接的次数，已经建立的连接能凑合用就凑合用，别每次执行个 SQL 语句都创建个新连接，服务端和客户端的资源都吃不消啊。

解决的方案就是使用 连接池 来复用连接。

二、架构优化

使用缓存

系统中难免会出现一些比较慢的查询，这些查询要么是数据量大，要么是查询复杂（关联的表多或者是计算复杂），使得查询会长时间占用连接。

如果这种数据的实效性不是特别强（不是每时每刻都会变化，例如每日报表），我们可以把此类数据放入缓存系统中，在数据的缓存有效期内，直接从缓存系统中获取数据，这样就可以减轻数据库的压力并提升查询效率。

读写分离（集群、主从复制）

项目的初期，数据库通常都是运行在一台服务器上的，用户的所有读写请求会直接作用到这台数据库服务器，单台服务器承担的并发量毕竟是有限的。

针对这个问题，我们可以同时使用多台数据库服务器，将其中一台设置为为小组长，称之为master节点，其余节点作为组员，叫做slave。用户写数据只往master节点写，而读的请求分摊到各个slave节点上。这个方案叫做读写分离。给组长加上组员组成的小团体起个名字，叫集群。

分库分表

分库分表中的节点的含义比较宽泛，要是把数据库作为节点，那就是分库；如果把单张表作为节点，那就是分表。

消息队列削峰

通过使用消息队列降低数据库的压力，不管同时有多少个用户请求，先存入消息队列，然后系统有条不紊地从消息队列中消费请求。

三、SQL分析与优化

慢查询

开启慢查询日志是有性能代价的，因此MySQL默认是关闭慢查询日志功能，使用以下命令查看当前慢查询状态。

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------------+

| Variable_name       | Value                                |

+---------------------+--------------------------------------+

| slow_query_log      | OFF                                  |

| slow_query_log_file | /var/lib/mysql/9e74f9251f6c-slow.log |

+---------------------+--------------------------------------+

2 rows in set (0.00 sec)

慢日志分析

有两种打开慢日志的方式。

修改配置文件my.cnf，此种修改方式系统重启后依然有效。

# 是否开启慢查询日志
slow_query_log=ON

long_query_time=2

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

动态修改参数（重启后失效）

mysql> set @@global.slow_query_log=1;
Query OK, 0 rows affected (0.06 sec)

mysql> set @@global.long_query_time=2;
Query OK, 0 rows affected (0.00 sec)

慢日志查询工具

慢日志查询的工具 mysqldumpslow ，为了演示这个工具，我们先构造一条慢查询：

mysql> SELECT sleep(5);

然后我们查询用时最多的1条慢查询：

[root@local~]# mysqldumpslow -s t -t 1 -g 'select' /var/lib/mysql/9e74f9251f6c-slow.log

Reading mysql slow query log from /var/lib/mysql/9e74f9251f6c-slow.log

Count: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost

SELECT sleep(N)