慢mysql性能

Question

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

操作系统: Centos

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