工具篇-Mysql 性能优化

---

前言

随着业务和数据量的增加，同样的sql 可能在项目开始时查询只需要几毫秒，后续可能需要几十秒设置几分钟，那么对于此种情况，怎么进行优化？

---

一、Mysql 性能问题：

1.1 一条sql 的执行流程：

1.2 MySQL 可能出现的性能问题：

• 磁盘I/O过高：当MySQL数据库过于频繁的读写磁盘，会导致磁盘I/O过高，这通常是由于缓存设置不当或查询语句编写不合理导致的。

• CPU使用率过高：这可能是由于大量的全表扫描操作、排序操作、联接操作或者是CPU等待磁盘I/O操作导致的。

• 内存使用过高：如果MySQL的各种缓冲区和缓存设置过大，可能会导致内存使用过高，甚至可能导致系统的总体性能下降。

• 查询性能低：这可能是由于索引设计不合理、查询语句编写不合理、数据统计信息过期或不准确等原因引起的。

• 并发性能差：MySQL服务器在高并发环境下性能下降，可能是由于线程调度机制、锁机制、事务隔离级别、网络配置等原因引起的。

• 复制延迟：在主从复制环境中，如果从服务器复制和应用主服务器的更新操作出现延迟，可能会影响读服务的实时性，这可能是由于网络延迟、磁盘I/O、系统资源等各种原因导致的。

二、优化：

针对这些性能问题，通常可以通过一下方面进行处理：

• 硬件层面：对硬件进行升级；
• 软件层面：
  mysql 参数配置层面：调整MySQL参数；
  sql 语句层面：优化SQL语句；
  mysql 表和数据库层面：重新设计数据库和表结构；

2.1 硬件层面：

对于服务器硬件层面，通过以下方式也可以优化MySQL的性能：

• 增加内存：更大的内存可以让MySQL有更多的缓存空间，从而减少对磁盘的读写操作。特别是对于InnoDB存储引擎，大内存可以设置更大的缓冲池来增加性能。

• 使用更快的CPU：CPU的主频越高，处理能力越强，能够更快地处理MySQL的计算任务，特别是对于复杂的查询和排序操作。

• 使用SSD硬盘：相比于传统的HDD硬盘，SSD硬盘的读写速度更快，对于I/O密集型的数据库应用，使用SSD硬盘可以大幅提升性能。

• 使用RAID磁盘阵列：RAID可以通过将多块硬盘组成阵列，从而提高数据读写的速度和数据的可靠性。特别是RAID 10，由于其具有既反映速度又反映安全的特性，被广泛应用在数据库服务器中。

• . 优化网络设备：如果MySQL服务器需要处理大量的网络请求，那么网络设备的性能就会成为瓶颈。使用性能更好的网卡、交换机，以及优化网络参数等方式，都可以提高MySQL服务器处理网络请求的能力。

• 硬件负载均衡和分布式数据库：对于特别大的数据库系统，可能单一的数据库服务器已经无法满足需求。这时可以考虑使用硬件负载均衡设备，将查询请求分发到多台数据库服务器上，或者使用分布式数据库系统，将数据分片存储到多台服务器上。

2.2 软件层面：

虽然硬件升级可以提升数据库性能，但是如果应用设计不合理，SQL语句和索引设计等有问题，那么即使硬件再好也难以发挥作用，所以软件优化也是非常重要的。

2.2 .1 mysql 参数配置优化：

2.2.1.1 mysql 服务端连接优化：

1） 增加最大连接数：
MySQL允许同时打开的连接数量由max_connections变量决定，默认值通常为151。通过修改MySQL服务器的配置文件my.cnf/my.ini，可以增加最大连接数（一个mysql 实例共用的连接数），最大可以支持10w；

[mysqld]
max_connections = 500

查询目前服务的连接数：

show variables like 'max_connections';

注意，设置一个过高的连接数会增加服务器的内存使用，可能导致性能下降。每个连接都会分配一定的内存，用于存储临时数据、缓存查询结果等。大量的空闲连接占用的内存可能导致需要的数据无法放入内存，进而引发频繁的磁盘I/O，降低性能。

mysql 连接数设置参考：

• 硬件限制：根据服务器的CPU核数和内存大小决定。一般来说，如果服务器是双核，可设置的连接数可考虑为100-200，如果是四核，建议设置的连接数为200-400。至于内存大小，建议保证每个连接分配的内存总和不超过系统物理内存。

• 应用需求：如果应用本身是并发量大、连接需求高的，就需要适当提高最大连接数了。例如，一些高并发的网站、交易系统等。

• 并发用户数：根据业务的并发访问量。假如并发访问量在100以内，连接数设置为200就足够了；假如并发访问量在1000以内，连接数设置为2000就足够了。

2）设置合理的wait_timeout值：
wait_timeout 是MySQL服务器的一个系统变量，它定义了一个非交互连接可以保持空闲的秒数。一旦超过这个时间，服务器将自动关闭连接。这对于防止长时间空闲的连接占用系统资源非常有用。默认情况下，MySQL服务器的wait_timeout变量设定为8小时(28800秒)，如设置为5分钟。

[mysqld]
wait_timeout = 300

注意：MySQL服务器自动关闭一个连接后，当客户端尝试再次通过该连接发送请求，会收到一个错误，通常是 “MySQL server has gone away”。客户端需要处理这种情况，通常的做法是捕获这种错误，然后重新建立连接，再次发送请求。

如果使用的是连接池，那么当从连接池中获取一个被服务器关闭的连接时，也会遇到类似的错误。一些成熟的连接池实现，会在提供连接前检测连接的可用性，如果检测到连接已经被关闭，就会自动创建一个新的连接返回。

2.2.1.2 mysql 客户端端连接优化：

• 客户端对连接使用池化技术，避免频繁的创建和销毁连接；
• 客户端使用缓存技术，减少对mysql 服务端的访问；

2.2.1.3 mysql服务端主要缓存参数优化：

• innodb_buffer_pool_size：这是MySQL中最重要的调性能参数之一，它定义了InnoDB存储引擎用于缓存数据和索引的内存大小。如果设置过小，可能会导致大量的磁盘IO操作，降低查询性能。此参数通常设置为服务器总内存的50%-70%。但最终的取值需要确保操作系统和其他进程有足够的内存可用。如果服务器只运行MySQL，可能可以把此值设置得更高。

• innodb_log_file_size （redo log）和innodb_log_buffer_size（InnoDB日志大小和缓冲）：这些参数控制InnoDB用于写入日志的文件大小和缓冲区大小。通过增大这些值，可以减少磁盘写入操作，从而提高性能。innodb_log_file_size 一种通常的建议是设置此值为innodb_buffer_pool_size的1/4大小，然而太大的值可能会使数据库恢复（Crash Recovery）时间变长。可能需要根据系统的I/O能力以及系统崩溃恢复时间的需求来权衡。innodb_log_buffer_size参数设置的是InnoDB存储引擎的重做日志缓冲区的大小。这个缓冲区用于存储正在等待写入日志文件的重做日志，包括事务的提交、回滚等操作。默认值和取值范围：这个参数的默认值通常为8MB，最小为1MB，最大为4294967295 bytes。一般来说，设置为数据库每秒事务量的2倍是个不错的起点。对于一些具有大量写操作的系统，可以设为32M或者更高。

• query_cache_size：这个参数定义了MySQL用于缓存查询结果的内存大小。如果你的应用有很多相同的查询操作，增大这个值可以提高查询性能。但是，如果查询类型很多，结果很少重复，那么查询缓存可能并不会提高性能，反而会因为维护缓存而消耗性能。如果系统有很多相同的查询，可以设置更大的值，例如64MB或更高。如果查询结果很少重复，可能最好将查询缓存禁用（设置为0）。

• max_connections：此参数定义了MySQL允许同时打开的最大连接数。如果你的应用有大量的并发连接请求，1可能需要增大这个值.这取决于你预计的并发连接数1。通常的Web服务器可能需要数百到数千的连接数，然而，过高的值可能会消耗更多的系统资源。

• sort_buffer_size：当MySQL进行排序操作时，它会使用一个称为“排序缓冲区”的内存区域。增大这个值可以让排序操作更快，但是每个线程都会使用这么大的内存，因此要防止设置过大导致系统内存不足。此参数通常设置为1MB或更高。然而，由于MySQL会为每个客户端连接分配一个此大小的排序缓冲区，所以设置过大可能会消耗大量内存。

一般来说，调整这些参数时，都应该先在测试环境下进行，观察参数变化对数据库性能的影响后，再考虑在生产环境下应用。另外，MySQL的性能调优涉及到很多方面，这些参数只是其中的一部分。

2.2.2 mysql sql 语句优化：

2.2.2.1 开启慢日志：

slow_query_log和slow_query_log_file是MySQL中的两个系统变量，与慢查询日志相关。

• slow_query_log: 此变量用于控制是否开启慢查询日志的功能，如果设置为’ON’，则开启慢查询日志，设置为’OFF’则关闭。

• slow_query_log_file: 此变量用于指定慢查询日志的文件路径和文件名。如果没有设置，MySQL会默认在数据目录下创建一个名为'hostname-slow.log'的文件作为慢查询日志。

这两个设置可以在MySQL配置文件my.cnf（或my.ini）中设置，也可以在运行时动态设置。但是请注意，动态设置的话在重启MySQL服务后会失效，如果想要永久生效，需要写入配置文件。

设置示例：

在配置文件中设置：

[mysqld]
slow_query_log = 1
slow_query_log_file = /path/to/your/logfile.log
long_query_time = 1

重启MySQL服务后生效。

动态设置：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/path/to/your/logfile.log';
SET GLOBAL long_query_time = 1;

仅对当前运行的MySQL服务生效，重启后失效。

其中，long_query_time是设定慢查询的阈值，执行时间超过这个值的SQL语句会被记录到慢查询日志。
long_query_time是MySQL中的一个系统变量，用于配置慢查询的阈值，当一个查询的执行时间超过long_query_time所设置的秒数时，MySQL会将这个查询记录到慢查询日志中。但注意只有当慢查询日志被开启的情况下才会有效，也就是将slow_query_log设置为ON。
long_query_time的默认值是10.000000（10秒）。

2.2.2.2 慢日志分析：

MySQL的慢查询日志是一种诊断工具，用于发现那些执行特别慢的SQL语句。日志中包含了哪些SQL语句被什么事务调用，并且花费了多长时间的信息。

最简单的分析方式就是直接手动打开这个日志文件，学习并理解里面的信息。每条慢查询日志记录包含查询的时间、执行时间、返回的记录数等信息，这对于定位和优化问题查询非常有用。

一般来说，能在日志中找到的一些需要优化的SQL情况可以包括：

1. 执行时间过长的查询：这可能是最直接也是最常见的问题，可能需要优化SQL语句或者对相关数据表建立索引等。

2. 大量返回结果的查询：如果一个查询返回大量数据，即使执行时间很短，也可能是一个性能问题，因为可能会消耗大量的网络带宽和客户端处理时间。

然而，当慢查询日志文件过大，或者查询变得复杂时，手动分析可能会非常困难。这个时候，就可以使用一些工具来帮助我们分析，比较常用的工具有mysqldumpslow和pt-query-digest。

• mysqldumpslow：这是MySQL的一个自带工具，可以用来分析慢查询日志，列出哪些查询执行的最慢。它分析日志并将具有类似模式的查询进行分组。格式化和参数化后的查询可以帮助我们找出那些导致性能问题的操作。

• pt-query-digest：这是Percona Toolkit的一个工具，它的功能比mysqldumpslow更强大，可以按照各种条件对SQL进行分类，并且输出更多的详细报告信息。

2.2.2.3 慢SQL 优化：

1） 通过EXPLAIN sql 语句：
MySQL中的EXPLAIN是一个非常有用的工具，用于查看MySQL如何使用索引来处理SELECT语句，并查其他可能影响性能的信息。当你对查询进行优化时，理解EXPLAIN输出的结果有助于你确定查询的瓶颈。

EXPLAIN的结果包含一系列列，这些列中包含了查询的许多重要信息：

• id：表示查询的标识符 执行的顺序，如果id 不同 则从id 值大的先进行访问，如果id 一样则先访问表数据量的小的表；如果既有 一样又有不一样，则先大后小，一样的则先访问数据量小的表；

• select_type：表示查询的类型（例如 SIMPLE（简单查询），SUBQUERY（子查询），UNION（联合查询）等）；

• table：输出结果集的表；

• partitions： 访问的分区；

• type：表示MySQL如何找到用于满足查询的行，这是一个非常关键的列，它显示了查询的连接类型；

• possible_keys：可能用到的索引；

• key：实际使用的索引。如果为NULL，则没有使用索引；

• key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好，根据字段的类型有关；

• ref：显示了哪些列或者常量被用于查找索引列上的值 ；

• rows：MySQL预估需要读取的行数；

• filtered：表示返回结果的行数占需要读取行数的百分比，越高越好，100 代码 Innodb 返回的数据全部是server 需要的数据；

• Extra：包含MySQL解决查询的详细信息，如果查询有性能问题，Extra列中的信息通常能提供解决问题的线索；

2） 主要字段分析：
2.1) ·type：
type列是EXPLAIN输出结果中的一项，它显示MySQL在表中找到所需行的方式，或者说“访问类型”。以下是type列可能的值(从最好到最坏的顺序)：

• system: 表只有一行，这是最好的可能，这种类型的表被称为系统表。

• const: 表只有一个匹配行（主键或者唯一索引），在优化阶段就可以读到数据。

• eq_ref: 在所有的连接类型（JOIN类型）中，性能最好的。它返回的查询结果是对前一个表的每个行的单一行搜索，后面的被驱动表，通过唯一索引连接。

• ref: 这个连接类型返回的查询结果只包含有所有符合某个单一值的行，后面的被驱动表，通过非唯一索引连接。

• fulltext: 利用FULLTEXT 索引进行FULLTEXT搜索。

• ref_or_null: 类似ref类型，只是MySQL除了扫描符合条件的行外，还需扫描包含NULL值的行。

• index_merge: 表示使用了索引合并优化方法，使用了两个(index)进行了查询。

• unique_subquery: 类似eq_ref，用在某个子查询返回的结果小集合中重复查询时。

• index_subquery: 类似ref，用在某个子查询返回的结果大集合中重复查询时。

• range: 范围搜索，这个比较好，比如between、>、<、>=、<=、in 都属于范围类型，性能也相对可以接受，查询的字段使用了索引并且是范围查询。

• index: 对索引字段进行全扫描，效率比较差。

• ALL: 对全表的扫描，效率最低。也是我们进行数据库查询优化要尽量避免的。

2.1) ·Extra：

• using index 覆盖索引
• using where 没有使用索引，通过条件查询，存储引擎没有使用索引查询数据
• using Index Condition (默认开启)： 索引下推
• using filesort；没有直接用索引进行排序，使用了额外其他的字段
• using temporary使用了临时表

2）优化：

• 使用小表驱动大表，这样尽可能的减少扫描的sql；
• 建立索引，在join 列，where ，group ，order 建立索引 优化sql 避免出现全表扫描；

2.2.3 表结构和存储引擎优化：

1）表结构：

• 字段存储，选择正确的数据类型：尽可能选择最适合的数据类型。较小的列通常更好，因为它们使用更少的磁盘、内存和CPU缓存，并且它们更容易被放在CPU缓存中；
• 优化列的顺序：将经常一起查询的列放在一起，可以提高磁盘I/O性能；
• -尽量减少可以null； 使用默认值 或者不为null；
• 正规化和反正规化：正规化是把数据分解到多个相关联的表中，以避免数据的冗余；反正规化则是将多个表合并为一个表，以减少复杂的连接操作。需要根据实际需求采取不同的策略

2） 存储引擎：

• 选择合适的存储引擎：选择的存储引擎应根据业务特性和需要的功能。例如，如果需要事务支持、行级锁等功能，InnoDB可能会是一个好的选择。如果表主要用于读取操作，而且不需要事务，MyISAM可能更合适。
• 数据库设计：对于InnoDB，主键的选择很关键，最好使用自增的主键。因为InnoDB的数据是按主键顺序存储的。对于MyISAM，如果频繁进行全文索引搜索，可以考虑增加 ft_min_word_len 的值；

2.3 架构层面：

2.3.1 读写分离：

MySQL的读写分离主要是将数据库的读操作和写操作分发到不同的服务器上执行，以此来提升数据库系统的处理能力。读写分离的主要目的是利用更多的硬件资源、提高数据库系统的吞吐量、改善响应时间以及确保数据的备份性和安全性。

这里是一种常见的MySQL读写分离的实现方式：

• 主从复制：在主从复制模式下，有一个主数据库服务器和一个或多个从数据库服务器。所有的写操作（如INSERT，UPDATE，DELETE）都在主服务器上执行，然后这些写操作产生的数据变更会自动复制到从服务器。读操作可以在一个或多个从服务器上执行。

• 配置应用层的数据库连接：在应用层进行读写分离，将数据库的读写请求发送到不同的服务器。很多数据库中间件，例如MyCat，MaxScale等，或者编程语言的数据库驱动，例如Java的JDBC，都提供了读写分离的配置选项。

• 一致性问题：因为主从复制是异步进行的，所以存在主从延迟，可能导致读到过期的数据。解决方式主要是合理调度读请求，比如写操作后的立即读，发送到主库进行。

• 故障切换：如果主库出现故障，需要有机制将其中的写操作切换到备库，同时也要有故障恢复策略。

最终的实现方式会因应用需求和业务特性进行调整，但基本思想是利用更多服务器资源，减轻单一服务器的负载。

2.3.2 分库分表：

MySQL的分库分表指的是将数据分散在不同的数据库或不同的表中存储。分库分表通常用于解决单一数据库或表的容量和性能瓶颈。以下是分库分表的两种常见实现方式：

• 垂直拆分：根据业务逻辑，把不同业务模块的表存放在不同的数据库（分库）或者把一张表的不同列分派到不同的表中（分表）。比如按业务模块划分，用户相关的表放一个库，订单相关的表放另一个库。

• 水平拆分：根据表中数据的某种规则，将表中的数据存放到多个数据库或表中。比如根据用户ID的哈希值将数据分配到不同的数据库或表中，或者按照时间将数据分配到不同的表中。

由于分库分表会增加应用层的复杂性，所以在实施分库分表之前，需要评估性能和容量需求，以及带来的开发复杂度、数据一致性和可维护性等问题。

在实现上，你可以使用自己的应用代码来控制分库和分表的逻辑。还可以使用数据库中间件、如：MyCat、Sharding-JDBC、Sharding-Sphere等，来实现分库分表，它们可以提供更高级的特性，包括读写分离、事务支持、SQL解析等。

需要注意的是，分库分表后，跨库或跨表的事务、联表查询会非常复杂，所以在设计时尽可能将相关数据放置在同一个库或表内。

三、优化思路：

对于慢sql的优化，并不是一开始就从硬件层面进行介入，通常都是先从软件层面进行介入：

• 数据结构优化：设计合理的数据表结构与索引规则对MySQL性能提升至关重要。合理选择数据类型，避免null值，尽可能保持表的紧凑性。在需要多个列进行查询的场景下，应该考虑复合索引。同时，要避免使用全表扫描和使用冗余的索引
• 通常对于慢sql 需要通过 查看其执行计划，然后通过减少嵌套，适当增加索引来尽可能的较少扫描的数据；
• 使用缓存：将频繁访问的数据放在缓存（如Redis、memcached）中，可以减少MySQL的访问频率。
• 参数调优：调整MySQL配置参数，如内存缓冲区大小（例如InnoDB的buffer pool），连接数等，使其适应具体的硬件环境和业务场景；
• 通过垂直/水平拆分将一个大表拆分为若干个小表，可以有效缩短查询和维护的时间；
• 主从架构读写分离：使用Master-Slave机制，使得读和写分发到不同的服务器，减少数据库压力。主库处理写请求（insert、update、delete），读请求发给从库处理，从而分奇数据库压力；
• 硬件优化：考虑使用更为高效的硬件，如SSD硬盘、充足的内存、高性能的CPU。

---

总结：

mysql 的性能优化是一个细致且长期的过程，本文仅仅对优化的方向进行探究，在具体的实践中提供优化的方向和思路。