当前位置：首页 > news >正文

MySQL优化

news 文章来源：https://blog.csdn.net/qq_32845825/article/details/110378356 2025/5/10 20:25:19

一. 优化 SQL 查询语句

1.1. 分析慢查询日志

1.2. 优化 SQL 查询语句的性能

1.2.1 优化查询中的索引

1.2.2 减少表的连接（join）

1.2.3 优化查询语句中的过滤条件

1.2.4 避免使用SELECT *

1.2.5 优化存储过程和函数

1.2.6 使用缓存

二. 优化表结构

2.1. 避免使用表过多

2.2. 选择正确的数据类型

2.2.1. 数据类型应该与数据特征相匹配。

2.2.2. 避免使用可变长度类型。

2.2.3. 对于经常被更新的字段，应该选择能够快速排序和搜索的数据类型。

2.2.4. 对于经常被检索的字段，应该选择能够节省空间的数据类型。

2.2.5. 对于计算字段，应该选择能够存储计算结果的数据类型。

2.2.6. 对于不需要进行计算的字段，应该选择能够最大程度地节省空间的数据类型。

2.2.7. 对于需要存储大量数据的字段，应该选择能够存储大量数据的数据类型。

2.3. 创建适当的索引

2.4. 避免创建大量“临时”表

2.5. 使用合适的存储引擎

2.6. 数据库架构

三. 增加硬件资源

四. 优化参数配置

4.1 降低磁盘的写入次数

4.1.1 增大 redo log，减少落盘次数

4.1.2 通用查询日志、慢查询日志可以不开，binlog 可开启。

4.1.3 写 redo log 策略 innodb_flush_log_at_trx_commit 设置为 0 或 2

4.2 系统调优参数

五. 优化并发访问

六. 使用缓存技术

前言：

MySQL 优化的重要性在于它可以提高数据库的性能，从而提升系统的整体性能。在处理大量数据或高并发请求时，MySQL 优化可以显著减少查询时间，提高系统的响应速度，从而提升用户体验。

1. 提高系统性能：MySQL 优化可以显著提高系统的性能，减少查询时间，提高系统的响应速度。

2. 减少资源消耗：MySQL 优化可以减少服务器资源消耗，如内存、CPU 和磁盘空间。

3. 提升系统稳定性：MySQL 优化可以减少系统崩溃和故障的概率，提高系统的稳定性。

4. 降低维护成本：MySQL 优化可以减少系统维护成本，降低服务器宕机的时间。

5. 提高业务效率：MySQL 优化可以提高业务效率，减少用户等待时间，提高用户体验。

总之，MySQL 优化对于一个大型的、高并发的网站来说是非常重要的，它能够显著提高系统的性能和稳定性，降低维护成本，提升业务效率。

一. 优化 SQL 查询语句

尽量减少查询数据量、减少 I/O 操作、使用索引、避免全表扫描等。可以通过阅读和理解 MySQL 的官方文档、查询性能分析工具、分析慢查询日志等方式来提高 SQL 查询的性能。

1.1. 分析慢查询日志

了解哪些查询需要优化以及问题的严重性。

1.2. 优化 SQL 查询语句的性能

1.2.1 优化查询中的索引：

                        合理地创建和维护索引是优化查询性能的关键。
①在创建索引时，应该包含查询中经常用来过滤数据的列。
                        ②对经常作为查询条件的那些列设置索引。
                        ③对联合主键进行索引。
                        ④对经常出现在查询中的order by子句中的字段设置索引。
                        ⑤对于group by和group by子句中的字段设置索引。
                        ⑥在join语句中，尽量使用内联而不是子查询或临时表。

1.2.2 减少表的连接（join）：

尽量减少表的连接，通过选择合适的连接类型（如INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN等）来减少表的连接。

1.2.3 优化查询语句中的过滤条件：

                        合理地使用SELECT语句的WHERE子句，尽量避免全表扫描。
                        ①使用索引
                        ②减少 where 和 limit 的应用
                        ③避免使用 `in` 和 `not in`
                        ④避免使用` LIKE` 和 `前方引号`

1.2.4 避免使用SELECT *：

仅返回需要的列，避免使用SELECT *，因为这样会执行全表扫描，影响查询性能。

1.2.5 优化存储过程和函数：

尽量避免在存储过程和函数中使用复杂的查询，因为这样会影响存储过程和函数的性能。

1.2.6 使用缓存：

合理地使用缓存可以减少对数据库的访问次数，从而提高查询性能。

二. 优化表结构

合理规划表字段、使用适当的索引、设置表字段的数据类型和长度等，可以提高表查询和存储的性能。

2.1. 避免使用表过多：

过度建模或创建多余的表是常见的数据库设计问题。这会导致在查询和分析数据时引入额外的开销和复杂性。尽量合并不必要的表，以减少数据库的“肥胖症”。

2.2. 选择正确的数据类型：

为数据库中的每个列选择正确的数据类型非常重要，因为它会影响到数据的存储、检索和处理效率。例如，为数值列使用适当的数据类型（如Decimal或浮点数）比使用字符型列更高效。此外，避免为小数字量列分配过多的空间，因为这会导致插入和查询性能下降。

2.2.1. 数据类型应该与数据特征相匹配。

例如，如果字段存储日期，应该选择日期类型而不是字符串类型。

2.2.2. 避免使用可变长度类型。

例如，使用 VARCHAR 而不是 TEXT，因为 VARCHAR 可以更快地检索和更新数据。

2.2.3. 对于经常被更新的字段，应该选择能够快速排序和搜索的数据类型。

例如，对于一个用户 ID，应该选择整数类型而不是字符串类型。

2.2.4. 对于经常被检索的字段，应该选择能够节省空间的数据类型。

例如，对于一个地址，应该选择地理坐标类型而不是字符串类型。

2.2.5. 对于计算字段，应该选择能够存储计算结果的数据类型。

例如，对于一个总销售额，应该选择数字类型而不是文本类型。

2.2.6. 对于不需要进行计算的字段，应该选择能够最大程度地节省空间的数据类型。

例如，对于一个电话号码，应该选择整数类型而不是字符串类型。

2.2.7. 对于需要存储大量数据的字段，应该选择能够存储大量数据的数据类型。

例如，对于一个产品目录，应该选择能够存储大量图像和详细信息的字段类型。

2.3. 创建适当的索引：

索引可以大大提高查询性能。为经常用于查询和筛选数据的列创建索引，但请注意，索引也会影响插入和更新性能。

        在数据库中创建适当的索引可以提高查询性能和减少查询时间。
               2.3.1. 选择要创建索引的列：选择需要索引的列，这些列应该是经常用于查询的列。
               2.3.2. 确定索引类型：选择适当的索引类型，例如 B-tree 索引、哈希索引、全文索引等。不同类型的索引适用于不同的查询类型和数据类型。
               2.3.3. 创建索引：使用 SQL 语句创建索引。例如，使用 CREATE INDEX 语句创建 B-tree 索引： ```sql CREATE INDEX idx_name ON table_name (column_name); ```
               2.3.4. 优化索引：在创建索引后，可以尝试优化索引以提高查询性能。例如，可以添加或删除索引列，或更改索引类型。
               2.3.5. 维护索引：定期维护索引以保持其有效性。例如，可以定期更新索引以反映数据的变化。
           总之，创建适当的索引可以提高查询性能和减少查询时间。因此，在创建索引时，应该选择适当的列、类型和优化索引。

2.4. 避免创建大量“临时”表：

这些表往往会积累大量未完成的数据。这些表可能会占用大量的磁盘空间，并导致性能下降。

2.5. 使用合适的存储引擎：

数据库的存储引擎影响数据库的性能和可扩展性。选择适合应用程序需求的存储引擎。例如，InnoDB引擎适合事务性应用程序，MyISAM引擎适合读取操作较多的应用程。

2.6. 数据库架构：

在数据库设计过程中，选择合适的架构非常重要。例如，水平拆分数据和缓存数据可以提高查询性能。根据应用程序需求，选择合适的架构，以优化数据库性能。遵循这些建议，可以提高数据库的性能、可扩展性和可维护性。

三. 增加硬件资源

可以通过增加服务器内存、增加磁盘空间等方式，提高 MySQL 数据库服务器的性能。
硬件方面的优化可以有对磁盘进行扩容、将机械硬盘换为SSD，或是把CPU的核数往上提升一些，增强数据库的计算能力，或是把内存扩容了，让Buffer Pool能吃进更多数据，等等。但这个优化手段成本最高，但见效最快。

四. 优化参数配置

通过对 MySQL 配置参数进行调整，可以提高 MySQL 数据库服务器的性能。例如，可以调整 MySQL 的缓冲区大小、最大连接数、线程数等参数。

4.1 降低磁盘的写入次数

4.1.1 增大 redo log，减少落盘次数：

redo log 是重做日志，用于保证数据的一致，减少落盘相当于减少了系统 IO 操作。

innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size

4.1.2 通用查询日志、慢查询日志可以不开，binlog 可开启。

通用查询和慢查询日志也是要落盘的，可以根据实际情况开启，如果不需要使用的话就可以关掉。binlog 用于恢复和主从复制，这个可以开启。

查看相关参数的命令：

# 慢查询日志
show variables like 'slow_query_log%'
# 通用查询日志
show variables like '%general%';
# 错误日志
show variables like '%log_error%'
# 二进制日志
show variables like '%binlog%';

4.1.3 写 redo log 策略 innodb_flush_log_at_trx_commit 设置为 0 或 2

对于不需要强一致性的业务，可以设置为 0 或 2。

0：每隔 1 秒写日志文件和刷盘操作（写日志文件 LogBuffer --> OS cache，刷盘 OS cache --> 磁盘文件），最多丢失 1 秒数据

1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁 IO 操作

2：事务提交，立刻写日志文件，每隔 1 秒钟进行刷盘操作

4.2 系统调优参数

（1）非缓存

back_log 默认50，改为500。 back_log=500

back_log 值可以指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。

也就是说，如果MySQL的连接数据达到 max_connections 时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。可以从默认的50升至500。

wait_timeout 由默认的8小时，修改为30分钟。 wait_timeout=1800

数据库连接闲置时间，闲置连接会占用内存资源。可以从默认的8小时减到半小时。

max_connections 默认151，改为3000。 max_connections=3000

MySql的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量。

max_user_connection 默认0，改为800。 max_user_connections=800

用户的最大连接，最大连接数，默认为0无上限，最好设一个合理上限。

thread_concurrency 并发线程数，设为CPU核数的两倍。

比如有一个双核的CPU, 那 thread_concurrency 的值应该为4; 2个双核的cpu, thread_concurrency 的值应为8。

（2）全局缓存

key_buffer_size

用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，对MyISAM表性能影响最大的一个参数。

innodb_buffer_pool_size

缓存数据块和索引块，对InnoDB表性能影响最大。通过查询show status like 'Innodb_buffer_pool_read%'，保证 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests越高越好。

innodb_additional_mem_pool_size

InnoDB 存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，当数据库对象非常多的时候，适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率，当过小的时候，MySQL会记录Warning信息到数据库的错误日志中，这时就需要该调整这个参数大小。

innodb_log_buffer_size

InnoDB 存储引擎的事务日志所使用的缓冲区，一般来说不建议超过32MB。

query_cache_size

缓存MySQL中的ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。当某个表的数据有任何变化，都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以，当我们数据变化非常频繁的情况下，使用Query Cache可能得不偿失。根据命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大。可以通过命令show status like 'Qcache_%'查看目前系统Query catch使用大小。

（3）局部缓存

read_buffer_size

MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，可以通过增加该变量值以及内存缓冲区大小来提高其性能。

sort_buffer_size

MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小。

read_rnd_buffer_size

MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

record_buffer

每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，可能想要增加该值。

thread_cache_size

保存当前没有与连接关联但是准备为后面新的连接服务的线程，可以快速响应连接的线程请求而无需创建新的。

五. 优化并发访问

合理配置连接数、使用数据库事务、使用数据库连接池等方式，可以提高 MySQL 数据库的并发访问性能。

六. 使用缓存技术

通过使用缓存技术，如 Redis、Memcached 等，可以减少数据库查询和存储的负担，提高数据库的性能。

前言：

一. 优化 SQL 查询语句

1.1. 分析慢查询日志

1.2. 优化 SQL 查询语句的性能

1.2.1 优化查询中的索引：

1.2.2 减少表的连接（join）：

1.2.3 优化查询语句中的过滤条件：

1.2.4 避免使用SELECT *：

1.2.5 优化存储过程和函数：

1.2.6 使用缓存：

二. 优化表结构

2.1. 避免使用表过多：

2.2. 选择正确的数据类型：

2.2.1. 数据类型应该与数据特征相匹配。

2.2.2. 避免使用可变长度类型。

2.2.3. 对于经常被更新的字段，应该选择能够快速排序和搜索的数据类型。

2.2.4. 对于经常被检索的字段，应该选择能够节省空间的数据类型。

2.2.5. 对于计算字段，应该选择能够存储计算结果的数据类型。

2.2.6. 对于不需要进行计算的字段，应该选择能够最大程度地节省空间的数据类型。

2.2.7. 对于需要存储大量数据的字段，应该选择能够存储大量数据的数据类型。

2.3. 创建适当的索引：

2.4. 避免创建大量“临时”表：

2.5. 使用合适的存储引擎：

2.6. 数据库架构：

三. 增加硬件资源

四. 优化参数配置

4.1 降低磁盘的写入次数

4.1.1 增大 redo log，减少落盘次数：

4.1.2 通用查询日志、慢查询日志可以不开 ，binlog 可开启。

4.1.3 写 redo log 策略 innodb_flush_log_at_trx_commit 设置为 0 或 2

4.2 系统调优参数

五. 优化并发访问

六. 使用缓存技术

相关文章：

4.1.2 通用查询日志、慢查询日志可以不开，binlog 可开启。