一个统计投票的sql,当数据量1万以下,没发现什么问题,当用户表接近一万,投票数突破两万的时候,连表统计的sql越来越慢。做了两步优化:
1.增加联合索引 (数据量多的放前面)
alter table t_voting_relation add index index_relation_optionid_tid(option_id,theme_id);
2.统计票数的时候,不要放到子查询里面,放到最外面统计
SELECT a.themeId,
a.pId,
a.themeName,
a.themeDesc,
a.banner,
a.endDate,
a.optionName,
a.author,
a.totalCount,
a.creationConcept
FROM (SELECT p.theme_id themeId,
p.pid pId,
t.theme_name themeName,
t.theme_desc themeDesc,
t.banner banner,
t.end_date endDate,
p.option_name optionName,
p.author author,
p.creation_concept creationConcept,
((select count(*) totalCount
from t_voting_detail d
where d.option_id = p.pid) + p.plus) totalCount
FROM t_voting_options p
改为
SELECT a.themeId,
a.pId,
a.themeName,
a.themeDesc,
a.banner,
a.endDate,
a.optionName,
a.author,
((select count(1) totalCount
from t_voting_detail d
where d.option_id = a.pid) + a.plus) totalCount,
a.creationConcept
FROM (SELECT p.theme_id themeId,
p.pid pId,
t.theme_name themeName,
t.theme_desc themeDesc,
t.banner banner,
t.end_date endDate,
p.option_name optionName,
p.author author,
p.creation_concept creationConcept,
p.plus plus
FROM t_voting_options p