mysql - mysql性能慢
问题描述
几天前我的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 的站点,该站点工作正常。
我可以从哪里开始检查?
编辑
它的解释查询
解决方案
这几乎是伪代码,但是,我敢打赌,您可以在只传递一次数据的同时进行聚合。由于您已经对数据进行了分组,因此您无需在选择中再进行两次子查询。
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
推荐阅读
- pandas - 日期时间索引数据帧日志时间刻度(x 轴)绘图
- android - 安卓。WindowBackground 中心图标不像活动图标那样居中
- c# - 如何从后面的代码中设置 html 输入日期标签的值
- python - python和pyqt5 - 单击按钮后传递变量
- javascript - 无法让 alignItems 在 React 中工作
- c - C: 文件 I/O - 来自书籍与现实的代码示例。省略.txt?
- java - 在链表中拆分值
- javascript - 关于 FIREBASE => Angular , 当我们使用 Push 方法写入数据时如何读取 FIREBASE 中的数据
- python - From fancyimpute import KNN - 如何安装包
- powershell - Powershell Return-Job 有时会返回空值