一、环境

1.1 版本

• MySQL版本：5.7.25_{（单机版）}
• 操作系统：CentOS Linux release 8.2.2004

1.2 使用工具

• Xshell
• Navicat Premium Lite 17

二、问题分析及优化

2.1 现象

(1) 客服报"业务系统中与查询相关的操作均出现不同程度的卡顿"

(2) 在服务主机中观察到mysqld进程cpu占用异常

使用top -H -p 2523命令查看mysqld进程的详情（2523是mysqld的进程号）

诊断_01

    可以看出有单个线程占用CPU异常的高，导致整个MySQL进程占用cpu会飙升到1000%！可以根据PID进一步到数据库中进行跟踪查询查询较慢的sql，不过可以进一步以耗时比较高的进行排序后查看。

2.2 分析

使用Navicat工具登陆数据库后，使用如下命令进行过滤查看

select a.user,a.host,b.thread_os_id,b.thread_id,a.id processlist_id,a.command,a.time,a.state,a.info 
from information_schema.processlist a,`performance_schema`.threads b 
where a.id=b.processlist_id and a.command<>'Sleep'
-- and b.thread_os_id={pid}
order by time desc limit 15;

/*
关于表information_schema.processlist字段说明
· Id：链接 mysql 服务器线程的唯一标识，可以通过 kill 来终止此线程的链接。
· User：当前线程链接数据库的用户
· Host：显示这个语句是从哪个 ip 的哪个端口上发出的。可用来追踪出问题语句的用户
· db: 线程链接的数据库，如果没有则为 null
· Command: 显示当前连接的执行的命令，一般就是休眠或空闲（sleep），查询（query），连接（connect）
· Time: 线程处在当前状态的时间，单位是秒
· State：显示使用当前连接的 sql 语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state 只是语句执行中的某一个状态，一个 sql 语句，已查询为例，可能需要经过 copying · to tmp table，Sorting result，Sending data 等状态才可以完成
· Info: 线程执行的 sql 语句，如果没有语句执行则为 null。这个语句可以使客户端发来的执行语句也可以是内部执行的语句
*/

如果需要跟踪上图中占比最高的线程，可以使用and b.thread_os_id过滤查询，这里直接根据information_schema.processlist.time（单位 秒）进行倒序排列，可以看出更多查询较慢的SQL，便于整体分析。

查询得出如下结果，可以看出，对于查询较慢的SQL，可以一目了然的看出端倪

诊断_02

2.3 定位

    根据分析得出有几张表非常可疑，如t_duration_log、t_duration_day_log等，后查询得知，上述几张表的存储记录早已超过500w，甚至达到其数倍，因此要想在不大动业务情况下进行优化，只能从非业务侧进行，如SQL优化、索引优化、业务侧缩小哦查询范围等。

1) SQL优化

    针对查询较慢的SQL语句，执行计划后，针对性的优化。如针对以下表的查询语句，由于只需要查询获取到id列，因此不需要进行order排序，移除order by id desc后可以看出明显rows减少。

SQL优化前

SQL优化后

2) 索引优化

    根据查询出较慢的SQL，将SQL拷贝到单独窗口中（在Navicat中操作），在SQL最前端使用explain字段执行查询计划（或使用Navicat中的解释按钮），返回执行计划的信息，针对其中使用到全表索引或rows记录明显过多的SQL进行针对性的添加索引。
    如果由于表数据量较大，添加单个字段索引或联合索引耗时较长，可能需要业务侧停服进行操作。
    提示：explain执行计划中type依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL（一般来说，得保证查询达到range级别，最好达到ref）

三 参考

• MySQL5.7 虚拟列实现表达式索引
• Mysql优化之Explain详解、索引最佳实践
• MySQL查询默认排序与order by排序
• sql order by与索引之间的关系（where条件出现字段才有效）

附件

附件A MySQL配置文件

配置文件路径：/etc/my.cnf，内容如下

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[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=/data/mysql
socket=/tmp/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/data/mysql/mysql.pid
#pid-file=/var/lib/mysql/mysql.sock

character_set_server=utf8
#init_connect='SET NAMES utf8'
default-time-zone = '+8:00'

# 默认开启事件
event_scheduler=ON

#数据库唯一ID，主从的标识号绝对不能重复。
server-id=1
#开启bin-log，并指定文件目录和文件名前缀
#log-bin=mysql-bin
#需要同步liting数据库。如果是多个同步库，就以此格式另写几行即可。如果不指明对某个具体库同步，就去掉此行，表示同步所有库（除了ignore忽略的库）。
binlog-do-db=db_sat_boss
binlog-do-db=radius
#不同步mysql系统数据库。如果是多个不同步库，就以此格式另写几行；也可以在一行，中间逗号隔开。
binlog-ignore-db=mysql
#确保binlog日志写入后与硬盘同步
sync_binlog = 1
#跳过现有的采用checksum的事件，mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none
binlog_checksum = none
#bin-log日志文件格式，设置为MIXED可以防止主键重复。
binlog_format = mixed
#二进制日志自动删除/过期的天数。默认值为 0,表示不自动删除。
expire_logs_days    = 10
#如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志
max_binlog_size     = 100M
#表示 slave 将复制事件写进自己的二进制日志
log_slave_updates   = 1
#跳过主从复制中遇到的所有错误或指定类型的错误,避免 slave 端复制中断。
slave-skip-errors = all

#自增长起始值
auto-increment-offset = 1
#自增长步长
auto-increment-increment = 2
#临时表大小
tmp_table_size = 64M
#支持group_by
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION