MySQL -慢查询
MySQL -慢查询
提问于 2020-07-07 00:47:14
很抱歉发了这么长的帖子，但涉及的表格非常大。
当我运行下面的查询时，它通常需要大约1m来运行。但是，当我删除相关子查询时，查询时间缩短到15秒。所以我认为这才是真正的问题所在。
问题是，我真的不知道如何在没有相关查询的情况下获得求和结果。
我正在尝试运行的查询
SELECT cl_clients.vat as association_vat, cl_clients.name as association_name, cl_clients_with_regions.city_id, cl_clients_with_regions.nut_1, cl_clients_with_regions.nut_2, cl_clients_with_regions.nut_3,
company.vat as company_vat, company.name, company.section, company.division, company.cae,
SUM((
SELECT SUM((COALESCE((cl_finances.sales_community_market), 0) + COALESCE((cl_finances.sales_extra_market), 0)))
from cl_finances
where cl_finances.vat = company.vat
and cl_finances.year = 2018
)) as total_sum
FROM `cl_clients_with_regions`
JOIN `cl_client_intervention_areas` ON `cl_client_intervention_areas`.`city_id` = `cl_clients_with_regions`.`city_id`
JOIN `cl_clients` ON `cl_clients`.`vat` = `cl_client_intervention_areas`.`client_vat`
INNER JOIN cl_clients_with_regions company ON cl_clients_with_regions.vat = company.vat
WHERE `cl_clients_with_regions`.`encrypted_vat` IS NOT NULL
AND `cl_clients_with_regions`.`country` IS NOT NULL
AND `cl_clients_with_regions`.`cae` IS NOT NULL
AND `cl_clients_with_regions`.`cae` != ''
AND `cl_clients_with_regions`.`division` IS NOT NULL
AND `cl_clients_with_regions`.`section` = 'A'
AND `cl_clients_with_regions`.`nut_2` = 'Centro'
GROUP BY association_vat;CL客户端创建表语法
create table cl_clients
(
vat varchar(20) default '' not null,
encrypted_vat varchar(32) null,
temporary_vat enum ('0', '1', '') default '0' null,
sig_id int null,
phc_id int null,
client_manager int null,
name varchar(300) null,
brand varchar(255) null,
`group` varchar(200) null,
class_internacional varchar(45) null,
logoimage varchar(400) null,
social_capital varchar(100) null,
address varchar(300) null,
gps varchar(25) null,
parish varchar(70) null,
zip_code varchar(10) null,
city int null,
district int null,
country int default 75 not null,
headquarter int null,
person_title varchar(7) null,
person_contact varchar(350) null,
person_phone varchar(20) null,
person_cell varchar(15) null,
person_email varchar(150) null,
person_function varchar(150) null,
language varchar(10) null,
phone varchar(20) null,
mobile_phone varchar(20) null,
fax varchar(20) null,
email varchar(50) null,
email_alternative varchar(50) null,
website varchar(300) null,
pme varchar(500) null,
pme_lider varchar(500) null,
cae varchar(15) null,
cae_2 varchar(15) null,
certified enum ('1', '0', '') null,
sector varchar(350) null,
workers int null,
foundation_date date null,
size enum ('Micro Empresa', 'Pequena empresa', 'Média empresa', 'Grande empresa', 'Não aplicável', 'PME', '') default '' null,
company_type varchar(455) default 'Empresa' null,
type enum ('novo', 'antigo', 'analizar', '') default 'analizar' null,
entity enum ('client', 'prospect', 'suplier', 'potential', '') default 'prospect' null,
client_to_country int null,
partner enum ('1', '0', '') default '0' not null,
partner_dp enum ('0', '1', '') default '0' null,
lucrative enum ('1', '0', '') default '1' not null,
dun varchar(100) null,
debt enum ('50k', '100k', '500k', '1m', '5m', '+5m', '') null,
bankruptcy enum ('1', '0', '') default '0' null,
competitors enum ('1', '0', '') default '0' null,
dun_date date null,
bank_1 varchar(30) null,
bank_2 varchar(30) null,
bank_3 varchar(30) null,
status enum ('0', '1', '') default '1' null,
followup enum ('0', '1', '') default '1' null,
classification enum ('Prestige', 'Premium', 'Current', '') default 'Current' null,
associated enum ('0', '1', '') default '0' null,
industry_id int null,
valid enum ('0', '1', '') default '1' null,
imported_at timestamp null,
modified_by int default 0 null,
responsible_id int null,
partner_type int null,
iberinform_id int null,
iberinform_date timestamp null,
legal_form text null,
going_concern text null,
score int null,
sector_score int null,
tax_status text null,
irc_debtors_ledger enum ('0', '1') null,
ss_debtors_ledger enum ('0', '1') null,
association_class_id int null,
nature enum ('business', 'commercial', 'sectorial', 'other') null,
geographic_scope enum ('national', 'regional', 'local') null,
intervention_nut enum ('nut_1', 'nut_2', 'nut_3') null,
ch_is_associated enum ('0', '1') null,
bi enum ('0', '1') default '0' null,
updated_at timestamp null,
constraint encrypted_vat_UNIQUE
unique (encrypted_vat),
constraint vat
unique (vat),
constraint cl_clients_cl_caes_cae_fk
foreign key (cae) references cl_caes (cae)
on update cascade,
constraint clients_industry_id_fk
foreign key (industry_id) references cl_industries (id),
constraint cltns_asso_clss_id_fk
foreign key (association_class_id) references cl_association_classifications (id)
on update cascade on delete set null,
constraint country_fk_id
foreign key (country) references cl_countries (id)
on update cascade on delete cascade
);
create index cl_client_name_idx
on cl_clients (name);
create index cl_clients_bi_idx
on cl_clients (bi);
create index cl_clients_brand_idx
on cl_clients (brand);
create index cl_clients_company_type_idx
on cl_clients (company_type);
create index cl_clients_geo_index
on cl_clients (city, district);
create index cl_clients_geographic_scope_idx
on cl_clients (geographic_scope);
create index cl_clients_group_idx
on cl_clients (`group`);
create index cl_clients_nature_idx
on cl_clients (nature);
create index cltns_asso_clss_id_fk_idx
on cl_clients (association_class_id);
create index country_idx
on cl_clients (country);
create index fk_clients_industries1_idx
on cl_clients (industry_id);
alter table cl_clients
add primary key (vat);查询中使用的视图
create view cl_clients_with_regions as
select `grupoch`.`cl_clients`.`vat` AS `vat`,
`grupoch`.`cl_clients`.`encrypted_vat` AS `encrypted_vat`,
`grupoch`.`cl_clients`.`name` AS `name`,
`grupoch`.`cl_clients`.`brand` AS `brand`,
`grupoch`.`cl_clients`.`country` AS `country`,
`grupoch`.`cl_clients`.`district` AS `district`,
`grupoch`.`cl_clients`.`city` AS `city`,
`grupoch`.`cl_cities`.`id` AS `city_id`,
`grupoch`.`cl_cities`.`name` AS `city_name`,
`grupoch`.`cl_cities`.`nut_1` AS `nut_1`,
`grupoch`.`cl_cities`.`nut_2` AS `nut_2`,
`grupoch`.`cl_cities`.`nut_3` AS `nut_3`,
`grupoch`.`cl_clients`.`cae` AS `cae`,
`grupoch`.`cl_caes`.`description` AS `cae_designation`,
`grupoch`.`cl_caes_divisions`.`division` AS `division`,
`grupoch`.`cl_caes_divisions`.`division_designation` AS `division_designation`,
`grupoch`.`cl_caes_divisions`.`section` AS `section`,
`grupoch`.`cl_caes_divisions`.`section_designation` AS `section_designation`
from (((`grupoch`.`cl_clients` join `grupoch`.`cl_cities` on ((
(`grupoch`.`cl_clients`.`city` = `grupoch`.`cl_cities`.`city_cod`) and (`grupoch`.`cl_clients`.`district` =
`grupoch`.`cl_cities`.`district_id`)))) join `grupoch`.`cl_caes` on ((`grupoch`.`cl_caes`.`cae` = `grupoch`.`cl_clients`.`cae`)))
join `grupoch`.`cl_caes_divisions`
on ((`grupoch`.`cl_caes_divisions`.`division` = `grupoch`.`cl_caes`.`division_id`)))
where ((`grupoch`.`cl_clients`.`country` in (75, 185, 186)) and (`grupoch`.`cl_clients`.`vat` is not null));CL finances table语法
create table cl_finances
(
vat varchar(20) not null,
year int not null,
workers int null,
sells varchar(300) null,
sells_variation varchar(300) null,
international_sells varchar(300) null,
international_sells_variation varchar(300) null,
liquid_results varchar(300) null,
liquid_results_variation varchar(300) null,
capital varchar(300) null,
active_liquid varchar(300) null,
financial_autonomy varchar(300) null,
modified_by int default 0 null,
sales_profitability decimal(13, 2) null,
return_on_capital decimal(13, 2) null,
sales_community_market decimal(13, 2) null,
sales_extra_market decimal(13, 2) null,
created_at timestamp null,
updated_at timestamp null,
primary key (vat, year),
constraint clients_finances_vat
foreign key (vat) references cl_clients (vat)
on update cascade on delete cascade
);
create index cl_finances_year_idx
on cl_finances (year);
create index vat
on cl_finances (vat);
## Create table syntax for cl_client_intervention_areas
create table cl_client_intervention_areas
(
client_vat varchar(20) not null,
city_id int not null,
constraint fk_cl_client_intervention_areas_cl_cities1
foreign key (city_id) references cl_cities (id)
on update cascade on delete cascade,
constraint fk_cl_client_intervention_areas_cl_clients1
foreign key (client_vat) references cl_clients (vat)
on update cascade on delete cascade
);
create index fk_cl_client_intervention_areas_cl_cities1_idx
on cl_client_intervention_areas (city_id);
create index fk_cl_client_intervention_areas_cl_clients1_idx
on cl_client_intervention_areas (client_vat);该查询解释结果非常大
回答 2
Stack Overflow用户
回答已采纳
发布于 2020-07-07 01:38:16
我相信group by子句中的每一行都会执行子查询一次。你可以尝试下面的方法吗?如果它能让它变得更好。
我所做的更改是将相关查询作为内联视图执行，然后与主表/视图连接-- company.vat
SELECT cl_clients.vat as association_vat, cl_clients.name as association_name, cl_clients_with_regions.city_id, cl_clients_with_regions.nut_1, cl_clients_with_regions.nut_2, cl_clients_with_regions.nut_3,
company.vat as company_vat, company.name, company.section, company.division, company.cae,
SUM(temp_val.sum_val) as total_sum
FROM `cl_clients_with_regions`
JOIN `cl_client_intervention_areas` ON `cl_client_intervention_areas`.`city_id` = `cl_clients_with_regions`.`city_id`
JOIN `cl_clients` ON `cl_clients`.`vat` = `cl_client_intervention_areas`.`client_vat`
INNER JOIN cl_clients_with_regions company ON cl_clients_with_regions.vat = company.vat
LEFT JOIN (SELECT cl_finances.vat
,SUM((COALESCE((cl_finances.sales_community_market), 0) + COALESCE((cl_finances.sales_extra_market), 0))) as sum_val
FROM cl_finances
WHERE 1=1
AND cl_finances.year = 2018
GROUP BY cl_finances.vat
)temp_val
ON company.vat=temp_val.vat
WHERE `cl_clients_with_regions`.`encrypted_vat` IS NOT NULL
AND `cl_clients_with_regions`.`country` IS NOT NULL
AND `cl_clients_with_regions`.`cae` IS NOT NULL
AND `cl_clients_with_regions`.`cae` != ''
AND `cl_clients_with_regions`.`division` IS NOT NULL
AND `cl_clients_with_regions`.`section` = 'A'
AND `cl_clients_with_regions`.`nut_2` = 'Centro'
GROUP BY association_vat;Stack Overflow用户
发布于 2020-07-07 02:18:34
为了进一步解释上面的答案：
子查询永远不应该出现在select子句中，因为它们对每个返回/或计算返回的记录执行一次。随着数据库变得越来越大，这会导致查询变得越来越慢。
相反，子查询应该在FROM子句中，并且子查询的结果应该联接到并选择。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/62760720
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号