MySQL优化：快速生成按公司统计License类型的计数列

问题背景 #

• 数据表：tbl_users，约15K行数据，包含UserName、Company、License等字段
• 需求：按公司分组，统计每个公司的L1、L2类型License数量，将两类数量分别作为独立列展示
• 现状：原用子查询的SQL执行时间超3分钟，无法满足实时需求；单独统计单类License仅需0.05秒；自行编写的联表查询存在语法错误，且不想通过存储过程实现

现有慢查询SQL #

select t.Company, 
  (select count(*) from tbl_users u where u.Company = t.Company and u.License = 'L1') as 'Count L1', 
  (select count(*) from tbl_users u where u.Company = t.Compamy and u.License = 'L2') as 'Count L2' 
  from tbl_users t group by Company;

注：此SQL存在笔误：t.Compamy应为t.Company，但即使修正笔误，性能依然很差——因为每个分组的子查询都会全表扫描一次，重复扫描次数等于公司数量，导致效率极低。

解决方案 #

方案1：条件聚合（推荐，性能最优） #

只需扫描一次表即可完成统计，执行效率远高于子查询版本：

select 
  Company,
  count(case when License = 'L1' then 1 end) as 'Count L1',
  count(case when License = 'L2' then 1 end) as 'Count L2'
from tbl_users
group by Company;

或者用sum实现，效果一致：

select 
  Company,
  sum(case when License = 'L1' then 1 else 0 end) as 'Count L1',
  sum(case when License = 'L2' then 1 else 0 end) as 'Count L2'
from tbl_users
group by Company;

方案2：修正后的联表查询 #

针对你写的联表查询，修正语法错误后也能高效执行：

select 
  base.Company,
  tL1.L1 as 'Count L1',
  tL2.L2 as 'Count L2'
from (select distinct Company from tbl_users) base
left join (
  select Company, count(*) as L1 
  from tbl_users 
  where License = 'L1' 
  group by Company
) tL1 on base.Company = tL1.Company
left join (
  select Company, count(*) as L2 
  from tbl_users 
  where License = 'L2' 
  group by Company
) tL2 on base.Company = tL2.Company;

修正点说明：

• 原SQL中group by Company as tL1语法错误，别名应放在子查询末尾
• 原SQLfrom tbl_users,多了逗号，且直接用tbl_users会导致重复行，改用select distinct Company获取唯一公司列表作为基础表
• 原SQL中company_member字段不存在，改为Company

性能优化建议 #

为进一步提升查询速度，建议给tbl_users表创建联合索引：

CREATE INDEX idx_company_license ON tbl_users(Company, License);

该索引能让分组和条件过滤操作直接利用索引完成，无需扫描全表。

内容的提问来源于stack exchange，提问作者RunnerIain