mysql 性能缓慢

Question

几天前我的 mysql 很慢，一些 sql 查询不再工作，我不知道这个问题是从哪里来的。

操作系统：森托斯

MySQL： 5.7 - InnoDB

MySQL -V：mysql Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using EditLine wrapper

这是我的查询

SELECT 
BTCountry.id,
BTCountry.name,
BTCountry.code,
BTCountry.last_data_from,
BTCountry.year_last_data_from AS current_year,
(SELECT 
SUM(cantidad) AS subCount 
FROM
`krakente_marketpins`.`bt_countries` AS `subBTCountry` 
INNER JOIN `krakente_marketpins`.`bus_trucks` AS `subBusTruck` 
ON (
`subBusTruck`.`pais_mercado_id` = `subBTCountry`.`id`
) 
WHERE `subBusTruck`.`pais_mercado_id` = `BTCountry`.`id` 
AND `subBusTruck`.`year_data_from` = 
`subBTCountry`.`year_last_data_from`
AND `subBusTruck`.month_data_from <= `subBTCountry`.`month_last_data_from`) AS ytd,
(SELECT 
SUM(cantidad) AS subCount 
FROM
`krakente_marketpins`.`bt_countries` AS `subBTCountry` 
INNER JOIN `krakente_marketpins`.`bus_trucks` AS `subBusTruck` 
ON (
`subBusTruck`.`pais_mercado_id` = `subBTCountry`.`id`
) 
WHERE `subBusTruck`.`pais_mercado_id` = `BTCountry`.`id` 
AND `subBusTruck`.`year_data_from` = `subBTCountry`.`year_last_data_from` - 1 
AND `subBusTruck`.`month_data_from` <= `subBTCountry`.`month_last_data_from`) AS last_year 
FROM
bt_countries AS BTCountry 
INNER JOIN `krakente_marketpins`.`bus_trucks` AS `BusTruck` 
ON (
`BusTruck`.`pais_mercado_id` = `BTCountry`.`id`
) 
WHERE `pais_mercado_id` IN (
'1',
'2',
'3',
'4',
'5',
'6',
'7',
'8',
'9',
'10',
'11',
'12'
) 
AND mercado_id = ('1') 
GROUP BY `BTCountry`.`id` 
ORDER BY `ytd` DESC 

我知道这个查询可以优化，但我需要知道为什么我的 mysql 现在工作不好。

我使用mysqltuner来获得建议，但没有运气。 这是我的调谐器日志

Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
join_buffer_size (> 256.0K, or always use indexes with JOINs)
table_open_cache (> 1024)
table_definition_cache(1024) > 5155 or -1 (autosizing if supported)
performance_schema = ON enable PFS
innodb_log_file_size should be (=1G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

我之前怎么说，查询几天前有效，但突然间这不再有效了。

另一方面，我有多个WP站点，这些站点突然变得非常非常慢，我认为它也来自mysql，因为如果我进入没有SQL的网站，站点可以正常工作。

我可以从哪里开始检查？

编辑

其解释查询

解释查询

Answer 1

这几乎是伪代码，但是，我敢打赌，您可以在只传递一次数据的情况下进行聚合。由于已对数据进行分组，因此无需在选择中再进行两次子查询。

SELECT 
BTCountry.id,
BTCountry.name,
BTCountry.code,
BTCountry.last_data_from,
BTCountry.year_last_data_from AS current_year,
ytd =         SUM(CASE WHEN BusTruck.year_data_from = bt_countries.year_last_data_from - 1 AND bus_trucks.month_data_from <= bt_countries.month_last_data_from THEN cantidad ELSE NULL END),
last_year =   SUM(CASE WHEN BusTruck.year_data_from = bt_countries.year_last_data_from AND bus_trucks.month_data_from <= bt_countries.month_last_data_from THEN cantidad ELSE NULL END)
FROM
bt_countries AS BTCountry 
INNER JOIN krakente_marketpins.bus_trucks AS BusTruck ON BusTruck.pais_mercado_id =BTCountry.id
WHERE
pais_mercado_id IN (
'1',
'2',
'3',
'4',
'5',
'6',
'7',
'8',
'9',
'10',
'11',
'12'
) 
AND mercado_id = ('1') 
GROUP 
BYBTCountry.id,
BTCountry.name,
BTCountry.code,
BTCountry.last_data_from,
BTCountry.year_last_data_from
ORDER 
BYytd 
DESC