mysql-3-性能优化-慢sql开启，配置文件

我的搬砖日常

已于 2022-11-21 22:30:25 修改

阅读量1.8k

点赞数

文章标签： mysql 数据库

于 2022-11-10 23:33:09 首次发布

本文链接：https://blog.csdn.net/weixin_44383484/article/details/127797447

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

1 配置文件位置

位置	解释
/etc/my.cnf (linux) my.ini (win)	配置文件位置
/var/lib/mysql	数据库的库的位置
/usr/share/mysql/	mysql.server命令及配置文件	frm:存放表结构。myd:存放表数据。 myi:存放表索引
/usr/bin	mysqladmin mysqldump等命令

2慢sql配置

查看是否开启慢sql,以及目录位置

show variables like 'slow_query%';

超过多长时间的 SQL 才记录到慢日志，默认是 10 秒。

show variables like '%long_query%';

开启慢sql ,3秒记录到日志 (重启生效，重启生效，重启生效)

set @@global.slow_query_log=1; -- 1 开启，0 关闭，重启后失效 
set @@global.long_query_time=1; -- mysql 默认的慢查询时间是 10 秒，另开一个窗口后才会查到最新值

或者修改配置文件 vim /etc/my.cnf

[mysqld]
slow_query_log = ON 
long_query_time=1 
slow_query_log_file =/var/lib/mysql/localhost-slow.log

重启mysql（yum方式安装）

systemctl restart mysqld

MySQL 提供了 mysqldumpslow 的工具，在/usr/bin下执行命令。 yum方式安装

mysqldumpslow --help

查询15条慢sql

mysqldumpslow -s t -t 15 /var/lib/mysql/localhost-slow.log

mysqldumpslow -s t -t 10 -g 'select' /var/lib/mysql/localhost-slow.log

Count 代表这个 SQL 执行了多少次；

Time 代表执行的时间，括号里面是累计时间；

Lock 表示锁定的时间，括号是累计；

Rows 表示返回的记录数，括号是累计

2配置详解

2.1 innodb_buffer_pool_size

查询cpu和内存数

 cat /proc/cpuinfo | grep "processor" |wc -l
 free -h

2.2 分组查询异常 5.7以上不兼容5.6 （选择配置文件方式一定）

异常信息：this is incompatible with sql_mode=only_full_group_by

sql方式设置只对未来创建的表起作用。以前建的还是不起作用，还是要配置文件方式设置

5.7版本以上，分组的时候：不合理的分组会报错。

查询支持的类型

show variables like '%sql_mode';

去除掉 ONLY_FULL_GROUP_BY 这个重新设置即可

set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'


[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M


datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
###########################################
#主从必须实时一致
#sync_binlog
#Innodb_flush_log_at_trx_commit
# 表锁时间
#lock_wait_timeout
# time_zone
#不建议修改线程
# Innodb_thread_concurrency 
#行锁
#Innodb_lock_wait_timeout
#Innodb_max_purge_lag
############################################################

#服务端口号 默认3306
port = 3306
#设置client连接mysql时的字符集,防止乱码
init_connect= `SET NAMES utf8mb4`

#一般设置物理存储的60% ~ 70% 默认128M 
innodb_buffer_pool_size = 1024M
join_buffer_size = 128M
#排序 order by 默认2M  4M或8M 
sort_buffer_size = 8M 
#默认2M
read_rnd_buffer_size = 8M 
read_buffer_size = 8M 


#内部内存临时表的最大值 ，默认16M 设置成32M 96M 128M。
#比如大数据量的group by ,order by时可能用到临时表，
#超过了这个值将写入磁盘，系统IO压力增大
max_heap_table_size = 100663296
tmp_table_size = 100663296





#在innodb_log_group_home_dir中的redoLog文件数, redoLog文件内容是循环覆盖写入。
#默认值 2
innodb_log_files_in_group = 3

#MySql5.7官方建议尽量设置的大些，可以接近innodb_buffer_pool_size的大小
#之前设置该值较大时可能导致mysql宕机恢复时间过长，现在恢复已经加快很多了
#该值减少脏数据刷新到磁盘的频次
#最大值innodb_log_file_size * innodb_log_files_in_group <= 512GB,单文件<=256GB
#默认值 1024M
innodb_log_file_size = 1024M

#提高刷新脏页数量和合并插入数量，改善磁盘I/O处理能力
#可根据磁盘近期的IOPS确定该值 
#默认值200
innodb_io_capacity = 500
#默认值2000
innodb_io_capacity_max = 2000

#设置最大连接数 默认200
max_connections = 1000


#MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭。默认8小时 ，设置了30分
#同时配置才能生效
interactive_timeout = 1800
wait_timeout = 1800


#慢sql 开启慢sql
slow_query_log = ON  
# 超过1秒记录到慢sql日志 （考虑0.5）
long_query_time=1 
#慢sql日志位置
slow_query_log_file =/var/lib/mysql/localhost-slow.log




#添加密码验证插件
plugin-load-add=validate_password.so
#
##服务器在启动时加载插件，并防止在服务器运行时删除插件
validate-password=FORCE_PLUS_PERMANENT


 #设置编码
character-set-server=utf8mb4
#表名区分大小写  0：区分(默认) 1：不区分
 lower_case_table_names=1
[client]
 #设置编码
default-character-set=utf8mb4
 
[mysql]
 #设置编码
default-character-set=utf8mb4

查询分析

当出现慢sql时，可以开启这个功能。执行sql，帮助我们查看sql具体执行步骤，帮助我们分析。

直接点击剖析即可查看大体情况。很多时候不需要看当时执行sql的 cpu，和Io情况。

1show profile

mysql提供分析执行当前sql时，服务器的资源消耗情况。调优使用

默认是关闭,并保存最近15次的运行结果

查看是否支持 OFF是以关闭，ON是以开启

show variables like 'profiling';

开启

set profiling=on;

查看sql, Duration 是2.0 是 sql执行时间为 2秒

show profiles;

诊断sql具体情况用 Query_ID 查询 8就是上图的。

show profile cpu,block io for query  8;

哪些是我们需要注意得，出现了就是很危险的？

converting HEAP to MyISAM 查询结果太大，内存都不够用了。往磁盘上搬了。

Creating tmp table 创建临时表。(拷贝数据到临时表，用完临时表还要在删除，所以效率慢)

Copying to tmp table on disk 把内存中临时表复制到磁盘，危险

locked