Mysql性能优化指南
发布时间：2022-09-22 14:35:25 所属栏目：教程 来源：
导读： 查询过程示意图
当我们去优化MySQL查询性能的时候，了解查询过程可以更好的帮助我们在相应的环节上进行处理。
当我们了解了MySQL的查询过程，结合实际经验，可以根据一下几个方面进行性能优化。
当我们去优化MySQL查询性能的时候，了解查询过程可以更好的帮助我们在相应的环节上进行处理。
当我们了解了MySQL的查询过程，结合实际经验，可以根据一下几个方面进行性能优化。
|
查询过程示意图 当我们去优化MySQL查询性能的时候，了解查询过程可以更好的帮助我们在相应的环节上进行处理。 当我们了解了MySQL的查询过程，结合实际经验，可以根据一下几个方面进行性能优化。 一、连接配置优化 服务端配置 服务端需要做的就是尽可能地多接受客户端的连接，或许你遇到过 error 1040: Too many connections 的错误？就是服务端的胸怀不够宽广导致的，格局太小！ 我们可以从两个方面解决连接数不够的问题： 客户端优化 客户端能做的就是尽量减少和服务端建立连接的次数，已经建立的连接能凑合用就凑合用，别每次执行个 SQL 语句都创建个新连接，服务端和客户端的资源都吃不消啊。 解决的方案就是使用 连接池 来复用连接。 二、架构优化 使用缓存 系统中难免会出现一些比较慢的查询，这些查询要么是数据量大，要么是查询复杂（关联的表多或者是计算复杂），使得查询会长时间占用连接。 如果这种数据的实效性不是特别强（不是每时每刻都会变化，例如每日报表），我们可以把此类数据放入缓存系统中，在数据的缓存有效期内，直接从缓存系统中获取数据，这样就可以减轻数据库的压力并提升查询效率。 读写分离（集群、主从复制） 项目的初期，数据库通常都是运行在一台服务器上的，用户的所有读写请求会直接作用到这台数据库服务器，单台服务器承担的并发量毕竟是有限的。 针对这个问题，我们可以同时使用多台数据库服务器，将其中一台设置为为小组长，称之为master节点，其余节点作为组员，叫做slave。用户写数据只往master节点写，而读的请求分摊到各个slave节点上。这个方案叫做读写分离。给组长加上组员组成的小团体起个名字，叫集群。 分库分表 分库分表中的节点的含义比较宽泛，要是把数据库作为节点，那就是分库；如果把单张表作为节点，那就是分表。 消息队列削峰 通过使用消息队列降低数据库的压力，不管同时有多少个用户请求mysql优化教程，先存入消息队列，然后系统有条不紊地从消息队列中消费请求。 三、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) 更多关于mysqldumpslow的使用方式，可以查阅官方文档，或者执行 mysqldumpslow --help 寻求帮助。 四、存储引擎与表结构 选择存储引擎 一般情况下，我们会选择MySQL默认的存储引擎存储引擎InnoDB，但是当对数据库性能要求精益求精的时候，存储引擎的选择也成为一个关键的影响因素。 建议根据不同的业务选择不同的存储引擎，例如： 优化字段 字段优化的最终原则是： 使用可以正确存储数据的最小的数据类型。 整数类型 MySQL提供了6种整数类型，分别是： 不同的存储类型的最大存储范围不同，占用的存储的空间自然也不同。 例如，是否被删除的标识，建议选用tinyint，而不是bigint。 字符类型 你是不是直接把所有字符串的字段都设置为varchar格式了？甚至怕不够，还会直接设置成varchar(1024)的长度？ 如果不确定字段的长度，肯定是要选择varchar，但是varchar需要额外的空间来记录该字段目前占用的长度；因此如果字段的长度是固定的，尽量选用char，这会给你节约不少的内存空间。 非空 非空字段尽量设置成NOT NULL，并提供默认值，或者使用特殊值代替NULL。 因为NULL类型的存储和优化都会存在性能不佳的问题，具体原因在这里就不展开了。 不要用外键、触发器和视图功能 这也是「阿里巴巴开发手册」中提到的原则。原因有三个： 图片、音频、视频存储 不要直接存储大文件，而是要存储大文件的访问地址。 大字段拆分和数据冗余 大字段拆分 其实就是前面说过的垂直分表，把不常用的字段或者数据量较大的字段拆分出去，避免列数过多和数据量过大，尤其是习惯编写SELECT *的情况下，列数多和数据量大导致的问题会被严重放大！ 字段冗余 原则上不符合数据库设计范式，但是却非常有利于快速检索。比如，合同表中存储客户id的同时可以冗余存储客户姓名，这样查询时就不需要再根据客户id获取用户姓名了。因此针对业务逻辑适当做一定程度的冗余也是一种比较好的优化技巧。 （编辑：PHP编程网 - 湛江站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
浙公网安备 33038102330483号