场景描述：使用top命令发现mysql占用cpu利用率非常高，相关表的数据量已经达到百万级别；

mysql很占用内存 mysql占用内存和cpu_联合索引

 

解决方法

  1、show full processlist;

    发现存在大量sleep线程；而大量sleep线程会占用连接数，当超过max_connections后无法建立新连接、并且可能仍有一些内存未释放，数量太多的话，是会消耗大量无谓的内存的，从而影响性能；

// 设置最大连接数以及最长等待时间	(注这部分配置可以在代码里也可以在mysql配置文件里)
    SHOW VARIABLES LIKE 'max_connections';
	SET GLOBAL max_connections=5000;

	show global variables like 'wait_timeout'
	SET GLOBAL wait_timeout=6000;

mysql很占用内存 mysql占用内存和cpu_SQL_02

 

  2、发现有些sql一直在执行，虽然执行时间很短

    需要在代码中定位到该SQL语句，使用explain 关键字对其分析，看是否是索引失效了；后续再看如何优化SQL

      

 

  3、慢查询SQL日志

// 检查是否开启了慢查询
show variables like '%slow_query_log%'
set GLOBAL slow_query_log = ON/OFF

    如何后台程序使用了druid数据源，也可以通过后台可视化界面更直观查看

mysql很占用内存 mysql占用内存和cpu_SQL_03

mysql很占用内存 mysql占用内存和cpu_联合索引_04

 

  4、去业务逻辑代码里找出最频繁执行的相关sql，进行分析

    本场景里业务平台没有任何操作mysql占用cpu的利用率就高飙，结合实际场景，发现大概率是数据上报相关代码出了问题，因为只有数据上报相关业务会一直执行；

    然后在顺着这个思路在继续细分，看具体是哪个业务逻辑里的相关sql出现了问题；

  

  5、SQL优化

// 原SQL

SELECT
	id,
	device_no,
	type,
	count,
	create_time,
	update_time 
FROM
	t_device_warning 
WHERE
	device_no = '4654987456489' 
	AND type = 3 
	AND date_add( update_time, INTERVAL 1 MINUTE ) > '2023-01-11 21:30:41' 
ORDER BY
	update_time DESC 
	LIMIT 1

  注意查询条件中的时间比较：它是将表中update_time这一列的值加一分钟后再与某一个某一个常量值进行比较，这样明显效率更低，更何况当数据量非常大的时候；（违法了索引列不要参与计算原则）

    所以建议是在进行比较时尽量不要采取修改表中数据的策略，而是后端程序将'常量值'做相应的修改后再进行传输，本例修改如下

SELECT
	id,
	device_no,
	type,
	count,
	create_time,
	update_time 
FROM
	t_device_warning 
WHERE
	device_no = '4654987456489' 
	AND type = 3 
	-- 后端处理完常量值后再进行传输
	AND update_time > '2023-01-11 21:30:41' 
ORDER BY
	update_time DESC 
	LIMIT 1

   后续可以再使用explain关键字判断是否走了相应索引；此处查询条件有多个，故可以尝试使用联合索引，但需注意字段的先后顺序(最左前缀匹配原则)；

    在建立联合索引时对字段进行先后排序时，原本应该是需要将对目标表的所有相关SQL都找出来进行分析的，以便达到最优效果；(本次实战是本人第一次进行相关优化，有些运气成分)

    尽量使用扩展索引(修改原有索引)，取代新建索引

 

  索引相关操作

// 新建单列索引
CREATE INDEX idx_update_time ON t_device_warning (update_time);
// 联合索引
CREATE INDEX idx_devNo_type_update ON t_device_warning (device_no,type,update_time);
// 显示索引
show index from t_device_warning
// 删除索引
DROP INDEX idx_update_time ON t_device_warning