Mysql性能优化指南
- 2023-12-21 15:17:42
- 孙广明
- 原创 856
查询过程示意图
当我们去优化MySQL查询性能的时候，了解查询过程可以更好的帮助我们在相应的环节上进行处理。
优化思路
当我们了解了MySQL的查询过程，结合实际经验，可以根据一下几个方面进行性能优化。
一、连接配置优化
1、服务端配置
服务端需要做的就是尽可能地多接受客户端的连接，或许你遇到过 error 1040: Too many connections 的错误？就是服务端的胸怀不够宽广导致的，格局太小！我们可以从两个方面解决连接数不够的问题：（1）增加可用连接数，修改环境变量 max_connections ，默认情况下服务端的最大连接数为151个；
mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.01 sec)
（2）及时释放不活动的连接，系统默认的客户端超时时间是28800秒（8小时），我们可以把这个值调小一点。
mysql> show variables like 'wait_timeout'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 28800 | +---------------+-------+ 1 row in set (0.01 sec)
2、客户端优化
客户端能做的就是尽量减少和服务端建立连接的次数，已经建立的连接能凑合用就凑合用，别每次执行个 SQL 语句都创建个新连接，服务端和客户端的资源都吃不消啊，解决的方案就是使用连接池来复用连接。二、架构优化
1、使用缓存
系统中难免会出现一些比较慢的查询，这些查询要么是数据量大，要么是查询复杂（关联的表多或者是计算复杂），使得查询会长时间占用连接。如果这种数据的实效性不是特别强（不是每时每刻都会变化，例如每日报表），我们可以把此类数据放入缓存系统中，在数据的缓存有效期内，直接从缓存系统中获取数据，这样就可以减轻数据库的压力并提升查询效率。2、读写分离（集群、主从复制）
项目的初期，数据库通常都是运行在一台服务器上的，用户的所有读写请求会直接作用到这台数据库服务器，单台服务器承担的并发量毕竟是有限的。针对这个问题，我们可以同时使用多台数据库服务器，将其中一台设置为为小组长，称之为master节点，其余节点作为组员，叫做slave。用户写数据只往master节点写，而读的请求分摊到各个slave节点上，这个方案叫做读写分离，给组长加上组员组成的小团体起个名字，叫集群。
3、分库分表
分库分表中的节点的含义比较宽泛，要是把数据库作为节点，那就是分库；如果把单张表作为节点，那就是分表。4、消息队列削峰
通过使用消息队列降低数据库的压力，不管同时有多少个用户请求，先存入消息队列，然后系统有条不紊地从消息队列中消费请求。三、SQL分析与优化
1、慢查询
开启慢查询日志是有性能代价的，因此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)
2、慢日志分析
有两种打开慢日志的方式。（1）修改配置文件my.cnf，此种修改方式系统重启后依然有效。
# 是否开启慢查询日志 slow_query_log=ON long_query_time=2 slow_query_log_file=/var/lib/mysql/slow.log
（2）动态修改参数（重启后失效）
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)
3、慢日志查询工具
（1）慢日志查询的工具 mysqldumpslow ，为了演示这个工具，我们先构造一条慢查询：mysql> SELECT sleep(5);
（2）然后我们查询用时最多的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)
- Count： 表示这个SQL执行的次数。
- Time： 表示执行的时间，括号中的是累积时间。
- Locks： 表示锁定的时间，括号中的是累积时间。
- Rows： 表示返回的记录数，括号中的是累积数。
四、存储引擎与表结构
1、选择存储引擎
一般情况下，我们会选择MySQL默认的存储引擎存储引擎InnoDB，但是当对数据库性能要求精益求精的时候，存储引擎的选择也成为一个关键的影响因素。建议根据不同的业务选择不同的存储引擎，例如：- 查询操作、插入操作多的业务表，推荐使用MyISAM；
- 临时表使用Memory；
- 并发数量大、更新多的业务选择使用InnoDB；
- 不知道选啥直接默认。
2、优化字段
字段优化的最终原则是： 使用可以正确存储数据的最小的数据类型。3、整数类型
MySQL提供了6种整数类型，分别是：- tinyint
- smallint
- mediumint
- int
- integer
- bigint
4、字符类型
你是不是直接把所有字符串的字段都设置为varchar格式了？甚至怕不够，还会直接设置成varchar(1024)的长度？如果不确定字段的长度，肯定是要选择varchar，但是varchar需要额外的空间来记录该字段目前占用的长度；因此如果字段的长度是固定的，尽量选用char，这会给你节约不少的内存空间。5、非空
非空字段尽量设置成NOT NULL，并提供默认值，或者使用特殊值代替NULL。因为NULL类型的存储和优化都会存在性能不佳的问题，具体原因在这里就不展开了。不要用外键、触发器和视图功能这也是「阿里巴巴开发手册」中提到的原则。原因有三个：- 降低了可读性，检查代码的同时还得查看数据库的代码；
- 把计算的工作交给程序，数据库只做好存储的工作，并把这件事情做好；
- 数据的完整性校验的工作应该由开发者完成，而不是依赖于外键，一旦用了外键，你会发现测试的时候随便删点垃圾数据都变得异常艰难。
6、图片、音频、视频存储
不要直接存储大文件，而是要存储大文件的访问地址。7、大字段拆分和数据冗余
大字段拆分 其实就是前面说过的垂直分表，把不常用的字段或者数据量较大的字段拆分出去，避免列数过多和数据量过大，尤其是习惯编写SELECT *的情况下，列数多和数据量大导致的问题会被严重放大！字段冗余 原则上不符合数据库设计范式，但是却非常有利于快速检索。比如，合同表中存储客户id的同时可以冗余存储客户姓名，这样查询时就不需要再根据客户id获取用户姓名了。因此针对业务逻辑适当做一定程度的冗余也是一种比较好的优化技巧。
五、业务优化
严格来说，业务方面的优化已经不算是MySQL调优的手段了，但是业务的优化却能非常有效地减轻数据库访问压力，这方面一个典型例子就是淘宝，下面举几个简单例子给大家提供一下思路：- 以往都是双11当晚开始买买买的模式，最近几年双11的预售战线越拉越长，提前半个多月就开始了，而且各种定金红包模式层出不穷，这种方式叫做预售分流。这样做可以分流客户的服务请求，不必等到双十一的凌晨一股脑地集体下单；
- 双十一的凌晨你或许想查询当天之外的订单，但是却查询失败；甚至支付宝里的小鸡的口粮都被延迟发放了，这是一种降级策略，集结不重要的服务的计算资源，用来保证当前最核心的业务；
- 双十一的时候支付宝极力推荐使用花呗支付，而不是银行卡支付，虽然一部分考量是提高软件粘性，但是另一方面，使用余额宝实际使用的阿里内部服务器，访问速度快，而使用银行卡，需要调用银行接口，相比之下操作要慢了许多。
暂时没有记录
