MySQL 优化

MySQL写入优化

1.写入优化

基本参数：

SELECT
@@unique_checks AS unique_checks,
@@FOREIGN_KEY_CHECKS AS FOREIGN_KEY_CHECKS,
@@autocommit AS autocommit,
@@innodb_flush_log_at_trx_commit AS innodb_flush_log_at_trx_commit,
@@innodb_autoextend_increment AS innodb_autoextend_increment,
@@innodb_log_buffer_size / 1024 / 1024 AS innodb_log_buffer_size,
@@innodb_log_file_size / 1024 / 1024 AS innodb_log_file_size

写入优化【开启】

SET GLOBAL unique_checks=0;
SET GLOBAL foreign_key_checks=0;
SET GLOBAL autocommit=0;
SET GLOBAL innodb_flush_log_at_trx_commit=0;
SET GLOBAL innodb_autoextend_increment=128;

写入优化【关闭】

SET GLOBAL unique_checks=1;
SET GLOBAL foreign_key_checks=1;
SET GLOBAL autocommit=1;
SET GLOBAL innodb_flush_log_at_trx_commit=1;
SET GLOBAL innodb_autoextend_increment=64;

2.日志功能

查询相关参数

开启日志

set global general_log=on; – 开启日志功能
set global general_log_file=’tmp/general.lg’; – 设置日志文件保存位置
– set global log_output=’table’; – 设置输出类型为 table
set global log_output=’file’; – 设置输出类型为file

关闭日志

set global general_log=on; – 开启日志功能

关于binlog日志开启连接：https://blog.csdn.net/intelrain/article/details/80451120