谷歌平台推广_2022广告行业发展现状及趋势_深圳网络推广团队_搜索优化软件

---

前言

MySQL性能优化是一个综合性的过程，涉及多个方面，以下是对MySQL性能优化的详细解析：

---

一、连接配置优化

1、调整连接和线程配置

对于高并发应用，应适当增加 max_connections 的值，以支持更多的并发连接。同时，thread_cache_size 的调整可以减少线程的创建和销毁开销，从而提升性能。

max_connections = 500
thread_cache_size = 50

如下sql可查询数据库的最大连接数。

show variables like 'max_connections';

2、调整缓冲池大小

InnoDB Buffer Pool 是 InnoDB 存储引擎用于缓存数据和索引的主要内存区域，其大小对数据库性能至关重要。通常建议设置为物理内存的 60% 到 80%，以确保大部分数据可以保存在内存中，减少磁盘 I/O。

innodb_buffer_pool_size = 8G

• 尽量减少和服务端建立连接的次数，不要每次执行个SQL语句都创建新连接。
• 常见的数据库连接池有DBCP、C3P0、阿里的Druid、Hikari，后两者目前是比较主流的。
• 连接池并不是越大越好，比如Druid的默认最大连接池大小是8，Hikari默认最大连接池大小是10，盲目地加大连接池的大小，系统执行效率反而有可能降低。
• 对于每一个连接，服务端会创建一个单独的线程去处理，连接数越多，服务端创建的线程自然也就越多。而线程数超过CPU个数的情况下，CPU势必要通过分配时间片的方式进行线程的上下文切换，频繁的上下文切换会造成很大的性能开销。
• Hikari官方给出了一个PostgreSQL数据库连接池大小的建议值公式，CPU核心数*2+1。假设服务器的CPU核心数是4，把连接池设置成9就可以了。这种公式在一定程度上对其他数据库也是适用的，

3、连接超时时间

wait_timeout：设置连接超时时间，当一个连接在指定时间内没有活动时，将被自动关闭。
及时释放不活动的连接，系统默认的客户端超时时间是28800秒（8小时），我们可以把这个值调小一点

show variables like 'wait_timeout';

4、查询缓存大小

• MySQL 提供了查询缓存功能，可以将常用查询的结果缓存起来，以提高查询响应速度。但是在高并发写入的情况下，查询缓存的失效较为频繁，可能会带来性能问题。因此，在 MySQL 8.0 中，查询缓存被移除了。在较旧版本中，应根据应用场景决定是否使用查询缓存。

query_cache_size：启用查询缓存可以提高查询性能，但对于更新频繁的数据库，启用查询缓存可能会降低性能。

二、架构优化

1、使用缓存

系统中难免会出现一些比较慢的查询，这些查询要么是数据量大，要么是查询复杂（关联的表多或者是计算复杂），使得查询会长时间占用连接。
如果这种数据的实效性不是特别强（不是每时每刻都会变化，例如每日报表），我们可以把此类数据放入缓存系统中，在数据的缓存有效期内，直接从缓存系统中获取数据，这样就可以减轻数据库的压力并提升查询效率。

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

• 读写分离、主从复制的架构
  • 可以同时使用多台数据库服务器，将其中一台设置为master节点，其余节点作为slave。用户写数据只往master节点写，而读的请求分摊到各个slave节点上，这样组成读写分离集群。
  • 为保证master节点的写操作也同步到各个slave节点上，主从节点的数据一致性，采用主从复制策略：
    • binlog日志是实现MySQL主从复制功能的核心组件。master节点会将所有的写操作记录到binlog中，slave节点会有专门的I/O线程读取master节点的binlog，将写操作同步到当前所在的slave节点。

3、分库分表

集群的架构对减轻主数据库服务器的压力有非常好的效果，但是随着业务数据越来越多，如果某张表的数据量急剧增加，单表的查询性能就会大幅下降，而这个问题是读写分离也无法解决的，毕竟所有节点存放的是一模一样的数据啊，单表查询性能差，说的自然也是所有节点性能都差。这时我们可以把单个节点的数据分散到多个节点上进行存储，这就是分库分表。

水平分，主要是为了解决存储的瓶颈；
垂直分，主要是为了减轻并发压力。

参考大佬文章：分库分表的魅力

4、消息队列削峰

通常情况下，用户的请求会直接访问数据库，如果同一时刻在线用户数量非常庞大，极有可能压垮数据库（参考明星出轨或公布恋情时微博的状态）。
这种情况下可以通过使用消息队列降低数据库的压力，不管同时有多少个用户请求，先存入消息队列，然后系统有条不紊地从消息队列中消费请求。

---

三、表结构优化

1、数据规范化与反规范化

• 规范化：通过规范化设计减少数据冗余，降低维护成本和存储开销。一般采用第三范式（3NF）来设计表结构。
• 反规范化：对于性能要求较高的查询，适当的反规范化（如添加冗余字段）可以减少 JOIN 操作的次数，提高查询性能。

2、合理选择数据类型

使用最小的数据类型满足存储需求，例如，如果一个整数列的值范围在 0 - 255 之间，使用 TINYINT 类型而不是 INT 类型。 对于字符串列，根据实际字符串长度选择合适的数据类型，如 VARCHAR 或 CHAR。VARCHAR 适用于长度可变的字符串，而 CHAR 适用于长度固定的字符串。

3、拆分大表

当表的数据量非常大时，单表操作的性能会下降。可以考虑通过 垂直拆分 和 水平拆分 来优化表结构：

• 垂直拆分：将一张表中不常用的字段分离到另一个表中，减少单张表的字段数量，从而提升查询性能。
• 水平拆分：将大表的数据按某种规则（如按 ID 或时间）拆分到多个表中，以减少单表的数据量，提高查询效率。

4、图片、音频、视频存储

不要直接存储大文件，而是要存储大文件的访问地址。

5、不要用外键、触发器和视图功能

这也是「阿里巴巴开发手册」中提到的原则。原因有三个：

• 降低了可读性，检查代码的同时还得查看数据库的代码；
• 把计算的工作交给程序，数据库只做好存储的工作，并把这件事情做好；
• 数据的完整性校验的工作应该由开发者完成，而不是依赖于外键，一旦用了外键，你会发现测试的时候随便删点垃圾数据都变得异常艰难。

四、索引优化以及关键字使用

1、索引优化

SHOW INDEX FROM table_name; 可以查看表中的索引情况

• 创建合适的索引：根据查询条件创建合适的索引，可以显著提高查询效率。经常在 WHERE、JOIN 和 ORDER BY 子句中使用的列应该创建索引。
• 使用复合索引：在多列作为查询条件时，建议创建组合索引。组合索引的效率优于对多个列单独创建索引。但要注意组合索引的顺序，它应遵循最左前缀匹配原则，即索引从最左边开始匹配，这样才能有效地被查询利用。
• 覆盖索引：如果查询的列都在索引中，MySQL可以直接通过索引获取数据，而不需要回表查询，这称为覆盖索引。
• 避免在数据值分布非常不均匀的列上创建索引：例如，一个列的大部分值都是相同的，索引的效果可能不明显。
• 避免过多索引：虽然索引可以提高查询效率，但过多的索引会占用磁盘空间，并降低写入性能，同时过多的索引会增加数据插入、更新和删除的成本，因为每次数据变更都需要更新相关的索引。
• 使用EXPLAIN分析查询：通过EXPLAIN命令查看查询的执行计划，了解MySQL是如何执行查询的，从而找出性能瓶颈。

2、索引类型选择

• B - Tree 索引：是 MySQL 中最常用的索引类型，适用于大多数情况，包括等值查询、范围查询和排序操作。
• Hash 索引：适用于等值查询，但不支持范围查询和排序操作。在内存表（MEMORY 存储引擎）中，Hash 索引可以提供非常快的查询速度。

3、查询优化

• 避免使用SELECT 【*】：尽量只选择需要的列，避免使用SELECT *，以减少数据传输量和处理时间。
• 尽量使用JOIN来代替子查询，因为JOIN通常比子查询更高效。
• 优化WHERE子句，避免全表扫描：确保WHERE子句中的条件能够使用索引，避免全表扫描。
• 避免使用OR 、IN、NOT IN：这些操作可能会导致全表扫描，影响查询性能。
• 避免NULL值判断：在WHERE子句中，如果对字段进行NULL值判断，索引将不起作用
• 避免 != 或 <> 操作符：在WHERE子句中使用!=或<>操作符会使MySQL无法使用索引，因为这些操作符的匹配模式使得MySQL无法快速过滤出特定的数据集。最好使用=、<、>等支持索引的操作符。
• LIKE查询优化：LIKE查询在处理部分匹配时可能导致全表扫描。尤其是在使用通配符%作为开头时（如%abc%），MySQL无法使用索引，因为需要逐行匹配整个字符串。最好限制通配符只在后缀位置（如abc%），这样MySQL可以有效利用索引来加速查询。
• 使用EXISTS替代IN：当涉及子查询时，EXISTS比IN在某些情况下效率更高，因为EXISTS一旦找到符合条件的记录，就会停止进一步的查找，而IN则必须先执行整个子查询，然后将结果返回。

五、硬件优化

• 足够的内存，mysql使用缓冲区来缓存数据和索引文件，因此需要足够多的RAM来确保大部分的数据能被缓存。
• 快速的存储设备，使用更快的存储设备（SSD），提高IO吞吐量，减少延迟。
• 使用RAID，将数据存在多个磁盘上，提高读写能力和可靠性。

六、慢查询

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 |
+---------------------+--------------------------------------+

除了上面两个变量，我们还需要确定“慢”的指标是什么，即执行超过多长时间才算是慢查询，默认是10S，如果改成0的话就是记录所有的SQL。

mysql> show variables like '%long_query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

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、慢日志分析

mySQL不仅保存慢日志文件，还提供了慢日志查询的工具mysqldumpslow，

[root@aaa ~]# mysqldumpslow -s t -t 1 -g 'select' /var/lib/mysql/aaa-slow.logReading mysql slow query log from /var/lib/mysql/aaa-slow.log
Count: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhostSELECT sleep(N)

• Count：表示这个SQL执行的次数
• Time：表示执行的时间，括号中的是累积时间
• Locks：表示锁定的时间，括号中的是累积时间
• Rows：表示返回的记录数，括号中的是累积数

参考大佬文章：mysql高性能分析

4、查看运行中的线程

我们可以运行**【show full processlist】**查看MySQL中运行的所有线程，查看其状态和运行时间，找到不顺眼的，直接kill。

• Id：线程的唯一标志，可以使用Id杀死指定线程
• User：启动这个线程的用户，普通账户只能查看自己的线程
• Host：哪个ip和端口发起的连接
• db：线程操作的数据库
• Command：线程的命令
• Time：操作持续时间，单位秒
• State：线程的状态
• Info：SQL语句的前100个字符

5、查看服务器运行状态

使用【SHOW STATUS】查看MySQL服务器的运行状态，有session和global两种作用域，一般使用【ike+通配符】进行过滤。

6、查看存储引擎运行信息

SHOW ENGINE用来展示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息；事务的锁等待情况；线程信号量等待；文件IO请求；Buffer pool统计信息等等数据。