记一次线上SQL慢查询

现象

客户生产环境 ，工时日报列表，查询4月份的数据，才8千条数据，列表响应耗时30s

排查过程

1、SQL监控

监控 sql 列表，在数据库中执行，耗时37s左右

2、explain 分析

使用 explain 分析执行过程，发现 emp_info 没有使用到索引。

3、检查索引

检查通用环境，发现是有索引字段 idx_origin_id 的，但是客户环境没有。

新增索引，重新执行sql, 发现执行时间，还需要5秒左右

4、检查SQL

• 再次检查 sql, 发现在where条件中，存在 emp.is_delete = 0， 这样的话，又会重新查询人员表，注释掉之后，执行时间在2秒左右。

• 但是速度有点慢，继续，检查sql语句，发现select中，存在两个关联数据字典的查询。对出参字段，做查询动作，也会影响查询速度。将这两个子查询放到from后面，执行时间在0.2s左右。

到此为止，以为优化完了，但是，在工时日报列表，选择 2023-03时 （总数28000左右），影响时间还是需要10秒左右。继续监控sql, 在数据库中执行，也是10秒 ，将sql改为单表查询，也是10秒，说明与人员表无关（没有截图）

当数据从几千到几万时，耗时成倍增加，怀疑瓶颈是 mysql 配置（可能是I/O磁盘次数太多）。

5、检查 my.ini

想到华星客户，工时日报列表，有30万数据。列表影响非常快，在数据表中单表查询工时日报表，也是几百毫秒。检查其他客户的mysql my.cnf (linux 文件名称为my.cnf, window 文件名称为 my.ini), 发现有很多设置参数。

• 检查此客户的mysql my.ini, 只有默认参数。

• 联系运维，确认是不是参数配置少了。重新调整参数（包括服务启动参数）。调整后（部分截图）

• 让实施重启数据库服务，再次验证，sql查询时间。只需要1秒左右

说明，现在查询速度是正常的。

my.ini（my.cnf） 文件

客户1

服务器配置 8核 32G内存

// my.ini

客户2

服务器配置 500G

// my.cnf
[client]
default-character-set=utf8mb4
port=3306

[mysql]
default-character-set=utf8mb4

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake=true
skip-name-resolve

server-id=1
max_connections=4000
port=3306
mysqlx_port=33060

mysqlx_socket=/tmp/mysqlx.sock
basedir=/data/mysql
datadir=/mysql/data
socket=/mysql/mysql.sock
pid-file=/mysql/mysqld.pid

log-output=FILE
general-log=0
general_log_file=/mysql/logs/mysql.log
log-bin=/mysql/logs/mysql.bin
log-error=/mysql/logs/error.log
slow-query-log=1
slow-query-log-file=/mysql/logs/slow.log
long_query_time=5
lc-messages-dir=/data/mysql/share
lc-messages="en_US"

innodb_write_io_threads=32
innodb_read_io_threads=32
innodb_purge_threads=1
##尝试获取元数据锁的超时时间（以秒为单位），此超时适用于所有使用元数据锁的语句，包括表、视图、存储过程和存储函数的dml和ddl操作
lock_wait_timeout=7200

##InnoDB事务在放弃之前等待行锁的时间长度（以秒为单位）。默认值为50秒。一个试图访问被另一个InnoDB事务锁定的行的事务，在发出以下错误之前，最多要等待这么多秒才能对该行进行写访问：
#当锁等待超时发生时，当前语句将回滚（而不是整个事务）。要回滚整个事务，请使用--innodb rollback on timeout选项启动服务器
#ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
innodb_lock_wait_timeout=50

tmpdir=/mysql
back_log=5000
innodb_flush_log_at_trx_commit=0
skip_innodb_doublewrite
expire_logs_days=2

log_bin_trust_function_creators=0

max_allowed_packet=1024M

default-storage-engine=INNODB
innodb_buffer_pool_size=200G
innodb_log_buffer_size=64M
innodb_log_file_size=512M
innodb_thread_concurrency=0
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=5000
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
binlog_format=row
join_buffer_size=15M
sort_buffer_size=15M
read_rnd_buffer_size=48M
read_buffer_size=48M
table_open_cache=4096
thread_cache_size=64
tmp_table_size=512M
key_buffer_size=512M
log_timestamps=SYSTEM
lower_case_table_names=1
wait_timeout=86400
interactive_timeout=86400
max_connect_errors=300000
#thread_stack=128
open_files_limit=20000
default-authentication-plugin=mysql_native_password
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
event_scheduler=on

bind-address=0.0.0.0
#sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO"
#skip-grant-tables
#skip-log-bin

总结

已优化完成，优化方向主要有三个

1、工时日报、月报列表关联了员工档案表，优化了SQL语句

2、优化了MYSQL数据库配置参数。

3、优化了系统运行内存。