MySQL重要参数

diff_dba

于 2024-06-01 11:28:53 发布

阅读量537

点赞数 22

分类专栏： MySQL 文章标签： mysql adb 数据库

本文链接：https://blog.csdn.net/qq_45796594/article/details/139371237

版权

MySQL 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

1.1 连接数
mysql> show status like ‘%connect%’;
mysql> show variables like ‘%connect%’;
显示当前正在执行的MySQL连接。
在MySQL配置文件my.ini/my.cnf中添加或修改：
//最大连接数
max_connections=1000

1.2 timeout
mysql有好几种timeout的情况，我们先用sql来查一下timeout的情况
mysql> show global variables like ‘%timeout%’;
connect_timeout：连接超时，mysql连接共有6次握手，3次TCP协议这个跟connect_timeout参数没有关系，另外3次跟connect_timeout参数有关系，该参数主要是为了防止网络不佳时应用重连导致连接数涨太快，一般默认即可。
delayed_insert_timeout：这是为MyISAM INSERT DELAY设计的超时参数，在INSERT DELAY中止前等待INSERT语句的时间
interactive_timeout：服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。参数默认值：28800秒（8小时）
lock_wait_timeout：锁等待超时时间
net_read_timeout / net_write_timeout：这个参数只对TCP/IP链接有效，分别是数据库等待接收客户端发送网络包和发送网络包给客户端的超时时间，这是在Activity状态下的线程才有效的参数
slave_net_timeout：这是Slave判断主机是否挂掉的超时设置，在设定时间内依然没有获取到Master的回应就人为Master挂掉了
wait_timeout：服务器关闭非交互连接之前等待活动的秒数

1.3 修改时区
可以通过修改my.cnf
在[mysqld] 之下加
default-time-zone=timezone
来修改时区。如：
default-time-zone =‘+8:00’
修改完了记得记得重启msyql
注意一定要在[mysqld] 之下加，否则会出现unknown variable ‘default-time-zone=+8:00’

1.4 区分大小写
lower_case_table_names参数详解：
lower_case_table_names = 0
其中 0：区分大小写，1：不区分大小写

1.5 内存设置
#MyISAM*
#用于缓存MyISAM表的索引块。决定数据库索引处理的速度（尤其是索引读）
key_buffer_size=8G
#用于对MyISAM表全表扫描时使用的缓冲区大小
read_buffer_size=64K

connection级参数（为每个线程配置），500个线程将消耗500*256K的sort_buffer_size

sort_buffer_size=256K
#mysql重建索引时允许使用的临时文件最大大小
myisam_max_sort_file_size=100G
#InnoDB
#InnoDB buffer pool 里包含什么？
#数据缓存:InnoDB数据页面
#索引缓存:索引数据
#缓冲数据:脏页（在内存中修改尚未刷新(写入)到磁盘的数据）
#内部结构:如自适应哈希索引，行锁等。
#大的缓冲池可以减小多次磁盘I/O访问相同的表数据。在专用数据库服务器上，可以将缓冲池大小设置为服务器物理内存的80%。一般是60%-75%物理内存
innodb_buffer_pool_size=10G

1.6 模式设置
#关闭严格模式
#NO_ZERO_DATE,
#NO_ZERO_IN_DATE
#sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO

1.7 表空间
默认情况下，innodb引擎的所有表都存储在一个叫ibdata1的文件中，当数据量很大的时候，这个文件超级大，而且由于磁盘碎片造成很大的性能影响。但是我们可以让每张表一个ibdata文件，具体做法是在mysql配置文件中加入：
innodb_file_per_table=1
这样就修改了InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间。重启mysql服务即生效.
查看是否开启：
mysql> show variables like ‘%per_table%’;
关闭独享表空间
innodb_file_per_table=0关闭独立的表空间
mysql> show variables like ‘%per_table%’;

1.8 mysql8.0插入慢的问题解决方案
我们先来说解决方式再去解释，执行命令：
set global innodb_flush_log_at_trx_commit = 2;
set global sync_binlog = 100000;
innodb_flush_log_at_trx_commit 参数
安全性考虑，这个参数默认是 1
innodb_flush_log_at_trx_commit 默认值为 1，可设置为 0、1、2
innodb_flush_log_at_trx_commit 设置为 0，log buffer 将每秒一次地写入 log file 中，并且 log file 的 flush (刷到磁盘) 操作同时进行。该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。
innodb_flush_log_at_trx_commit 设置为 1，每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file，并且 flush (刷到磁盘) 中去。
innodb_flush_log_at_trx_commit 设置为 2，每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file. 但是 flush (刷到磁盘) 操作并不会同时进行。该模式下，MySQL 会每秒执行一次 flush (刷到磁盘) 操作。
sync_binlog 参数
sync_binlog 默认值为 1，可设置为 [0,N)
当 sync_binlog =0，像操作系统刷其他文件的机制一样，MySQL 不会同步到磁盘中去而是依赖操作系统来刷新 binary log。
当 sync_binlog =N (N>0) ，MySQL 在每写 N 次二进制日志 binary log 时，会使用 fdatasync () 函数将它的写二进制日志 binary log 同步到磁盘中去。
综上两条命令搞定：
set global innodb_flush_log_at_trx_commit = 2;
set global sync_binlog = 2000;
如果你想让他变回来变的更慢那就：
set global innodb_flush_log_at_trx_commit = 1;
set global sync_binlog = 1;

1.9 提高写入速度
(1).sync_binlog (这个很管用)
查询设置
show variables like ‘sync_binlog’;
说明：
sync_binlog 参数来控制数据库的 binlog 刷到磁盘上去。
设置 sync_binlog=1，多个事务同时提交，很大的影响 MySQL 和 IO 性能
设置 sync_binlog=100 或者sync_binlog=0。这样牺牲一定的一致性，可以获得更高的并发和性能
修改设置
set global sync_binlog=100
(2).innodb_flush_log_at_trx_commit（这个很管用）
默认为 1，这是数据库的事务提交设置参数，可选值如下：
0: 日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。
1：在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新。
2：在每个提交，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新。对日志文件每秒刷新一次。
针对同一个表通过进行批量插入，性能比较如下所示：
a. 相同条件下：innodb_flush_log_at_trx_commit=0，插入50W行数据所花时间25.08秒;
b. 相同条件下：innodb_flush_log_at_trx_commit=1，插入50W行数据所花时间17分21.91秒;
c. 相同条件下：innodb_flush_log_at_trx_commit=2，插入50W行数据所花时间1分0.35秒。
结论：设置为 0 的情况下，数据写入是最快的，能迅速提升数据库的写入性能，但有可能丢失上1秒的数据。
(3).temp_table_size, heap_table_size
这两个参数主要影响临时表temporary table 以及内存数据库引擎memory engine表的写入，设置太小，甚至会出现table is full的报错信息.
(4).innodb_buffer_pool_size
如果用 Innodb，那么这是一个重要变量。相对于 MyISAM 来说，Innodb 对于 buffer size 更敏感。MySIAM 可能对于大数据量使用默认的 key_buffer_size 也还好，但 Innodb 在大数据量时用默认值就感觉在爬了。 Innodb 的缓冲池会缓存数据和索引，所以不需要给系统的缓存留空间，如果只用 Innodb，可以把这个值设为内存的 70%-80%。和 key_buffer 相同，如果数据量比较小也不怎么增加，那么不要把这个值设太高也可以提高内存的使用率。
(5).innodb_additional_pool_size
这个的效果不是很明显，至少是当操作系统能合理分配内存时。但你可能仍需要设成 20M 或更多一点以看Innodb会分配多少内存做其他用途。
(6).innodb_log_file_size
对于写很多尤其是大数据量时非常重要。要注意，大的文件提供更高的性能，但数据库恢复时会用更多的时间。我一般用 64M-512M，具体取决于服务器的空间。
(7).innodb_log_buffer_size
默认值对于多数中等写操作和事务短的运用都是可以的。如果经常做更新或者使用了很多 blob 数据，应该增大这个值。但太大了也是浪费内存，因为 1 秒钟总会 flush（这个词的中文怎么说呢？）一次，所以不需要设到超过 1 秒的需求。8M-16M 一般应该够了。小的运用可以设更小一点。

2.0 统计信息不更新
在MySQL中，information_schema是一个元数据信息数据库，它包含了关于数据库和表的信息，比如表的结构、索引、列的数据类型等。information_schema数据库是由系统自动创建和维护的，不需要手动的进行操作。
然而，information_schema数据库中的统计信息在某些情况下可能会过期。统计信息是数据库用来优化查询计划的重要依据之一。当统计信息过期时，可能会导致查询性能下降，因为数据库无法准确地判断哪个查询计划是最优的。
为了解决这个问题，MySQL提供了一个参数information_schema_stats_expiry，用于设置information_schema中统计信息的过期时间。默认情况下，该参数的值是86400秒（1天），也就是说统计信息在1天后会自动过期。当然，我们也可以将该参数的值设置为0，表示统计信息永不过期。

[mysqld]
information_schema_stats_expiry=0
保存并关闭配置文件，然后重新启动MySQL服务。

注意事项
修改information_schema_stats_expiry参数需要重启MySQL服务才能生效；
设置information_schema_stats_expiry为0并不意味着永远不会更新统计信息，MySQL仍然会在某些情况下自动更新统计信息，比如表结构发生变化、执行ANALYZE TABLE命令等；
当统计信息过期时，MySQL会在查询时自动更新统计信息，但这可能会导致查询的第一次执行比较慢；
当设置information_schema_stats_expiry为0时，建议定期手动执行ANALYZE TABLE命令来更新统计信息，以确保查询的准确性和性能。
示例代码
下面是一个简单的示例，演示如何设置information_schema_stats_expiry参数为0：
– 创建一个测试表
CREATE TABLE test (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;

– 添加一些测试数据
INSERT INTO test (name) VALUES (‘Alice’), (‘Bob’), (‘Charlie’);

– 设置infromation_schema_stats_expiry为0
SET GLOBAL information_schema_stats_expiry = 0;

– 查询表的统计信息
SELECT * FROM information_schema.tables WHERE TABLE_NAME = ‘test’;

结论
通过设置information_schema_stats_expiry参数为0，可以确保information_schema中的统计信息永不过期，提高查询的准确性和性能。
然而，这也需要我们手动定期更新统计信息，以免出现查询性能下降的情况。在实际应用中，我们需要根据具体的业务需求来决定是否将information_schema_stats_expiry设置为0，并权衡其带来的影响。