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

mysql配置优化和分组报错问题解决

article 2024/9/20 5:36:45

【需求】

我有一个很鸡肋的需求，mysql数据库安装没有配置my.ini,用的默认配置，导致很多的配置不能一次配置永久生效，需要每次重启后都要重新执行才可以生效，重装代价太大，客户不同意，服务器并发量很高，重新配置怕出其他的问题，所以只能编辑一个脚本，每次电脑重启3分钟后执行这个命令

自己实操过的配置，修改后效果很明显

1. 内存相关配置

innodb_buffer_pool_size：

这是 InnoDB 存储引擎用来缓存数据和索引的内存。该值越大，缓存命中率越高，查询速度也就越快。一般建议设置为物理内存的 50% 到 80% 左右。
设置方法：
innodb_buffer_pool_size = 4G # 根据你的物理内存大小调整

query_cache_size（MySQL 8.0 中已被废弃）：

旧版 MySQL 可以启用查询缓存，用于缓存 SELECT 查询的结果。若数据更新较为频繁，建议关闭查询缓存，避免缓存频繁失效。
设置方法：
query_cache_size = 64M query_cache_type = 1

tmp_table_size 和 max_heap_table_size：

这两个参数决定了临时表的最大内存使用。如果查询涉及大量的临时表操作（如 GROUP BY、ORDER BY 操作），可以适当增加这些值。
设置方法：
tmp_table_size = 128M max_heap_table_size = 128M

2. 连接数与并发控制

max_connections：

控制最大连接数，防止过多的连接导致服务器资源耗尽。根据业务需求设置合适的连接数。
设置方法：
max_connections = 500 # 根据实际并发量调整

thread_cache_size：

控制线程缓存的大小，减少线程创建和销毁的开销。
设置方法：
thread_cache_size = 32

3. 日志与IO相关配置

innodb_log_file_size：

日志文件大小，日志文件过小可能会影响写入性能，适当增大日志文件大小可减少日志文件的频繁写入。
设置方法：
innodb_log_file_size = 512M

innodb_flush_log_at_trx_commit：

控制事务提交时日志刷新的方式。1 为最安全（每次提交事务都写入磁盘），2 和 0 提供更好的性能但降低了安全性。适当选择可以提升写入效率。
设置方法：
innodb_flush_log_at_trx_commit = 2

4. 索引与查询优化

innodb_io_capacity：

控制 InnoDB 存储引擎的 I/O 处理能力。可以根据磁盘 I/O 性能设置该值，适当提升可以提高系统的吞吐量。
设置方法：
innodb_io_capacity = 1000 # 如果使用 SSD，可适当调高

innodb_read_io_threads 和 innodb_write_io_threads：

这两个参数分别控制 InnoDB 的读/写 I/O 线程数。多核 CPU 和高性能磁盘环境下，可以增加这两个值以提升性能。
设置方法：
innodb_read_io_threads = 4 innodb_write_io_threads = 4

5. 查询优化

join_buffer_size：

控制 Join 操作时的内存缓存大小。如果涉及大量的 JOIN 操作，可以增加该值。
设置方法：
join_buffer_size = 8M

sort_buffer_size：

控制排序操作时的缓存大小。如果查询中包含大量的排序操作，如 ORDER BY，可以增大这个值。
设置方法：
sort_buffer_size = 4M

6. 其他

table_open_cache：

控制表缓存的大小，适当增加这个值，可以减少表的打开和关闭操作，提高查询效率。
设置方法：
table_open_cache = 2000

innodb_flush_method：

控制刷写数据到磁盘的方式。可以选择 O_DIRECT 以减少文件系统缓存对性能的影响，提高写入性能。
设置方法：
innodb_flush_method = O_DIRECT

7.mysql分组报错问题解决

查询SQLMODE

select @@GLOBAL.sql_mode;

设置方法：
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

以上执行后请执行刷新

设置方法：
flush privileges;