一次线上SQL调优，从一分钟到一秒以内的极限操作

1. 产品原始需求，根据从表数据的统计数据对主表数据进行排序。
   线上的原始sql如下，它做了左连接4个从表，然后分组统计，并根据统计相关字段排序。

SELECT
    em.enterprise_id,
    em.tenant_id,
    em.company_name,
    count(eb.id) as branch_count,
    count(ep.id) as project_count,
     count(ec.id) as cred_count,
     count(er.id) as register_count,
FROM
   enterprise_main em
   left join enterprise_branch eb on em.enterprise_id = eb.enterprise_id
   left join enterprise_project ep on em.enterprise_id = ep.enterprise_id
    left join enterprise_credential ec on em.enterprise_id = ec.enterprise_id
    left join enterprise_person er on em.enterprise_id = er.enterprise_id
where em.is_delete = 0 and eb.is_delete = 0 and ep.is_delete=0 group by em.id order by
 ,cred_count desc ,register_count desc;

原始的这个sql脚本根本无法计算出结果，直接是SQL超时。这个sql脚本存在很大的问题。主表与4个从表的关系都是一对多，这样主表连接多个从表会导致笛卡尔集非常大。
首先第一步降低笛卡尔积的大小，上面的on条件用于生成派生表，where条件过滤派生表的数据集。sql优化一个基本准则就是将过滤数据多的条件放在前边。
如果主表的数据量用m表示, 这三个从表的数据量都是主表的n倍，那么这个连表结果集的大小就是 m*(nm)⁴ 即 n⁴m⁵ 。实际的主表数据是3000条左右，这里的n假设是50，那么这个结果集就是个天文数字，所以根本不可能计算出结果。一对多的情况下，多表连接就容易出现笛卡尔集过大的问题。
具体问题具体分析，需求中只会用到连表的分组统计count进行排序，并没有使用连表数据的具体数据，其实这里是不需要连表的，我们可以使用子查询去计算count，从而避免连表生成大笛卡尔集。

SELECT
    em.enterprise_id,
    em.tenant_id,
    em.company_name, 
		(SELECT COUNT(*) from enterprise_branch eb WHERE  eb.enterprise_id=em.enterprise_id  and eb.is_delete=0    ) branch_count,
		(SELECT COUNT(*) from enterprise_project ep WHERE ep.enterprise_id=em.enterprise_id  and ep.is_delete=0) project_count,
		(SELECT COUNT(*) from enterprise_credential ec WHERE ec.enterprise_id=em.enterprise_id  and ec.is_delete=0  ) cred_count,
		(SELECT COUNT(*) from enterprise_person ep  WHERE ep.enterprise_id=em.enterprise_id and ep.is_delete=0 ) register_count
FROM  enterprise_main em WHERE  em.is_delete=0     order by branch_count desc, project_count desc  ,cred_count desc ,register_count desc;

此次优化后，sql查询变得简单了，相当于只查主表数据，外加每条主数据执行下几个count计算，优化后的sql查询用时在42秒左右。此次sql优化后，SQL脚本至少能得出结果了，但耗时还是太长。在使用EXPLAIN指令查看执行计划后，发现子查询是全表扫描，这才发现原来连表字段没有索引。这还真是个大坑，这个公司主键 enterprise_id 是租户相关的分片字段，它竟然没有建索引。反应过来，直接给这5张表都创建BTree索引。

-- 创建BTree索引
ALTER TABLE enterprise_main ADD INDEX enterprise_id_index(enterprise_id) USING BTREE;
ALTER TABLE enterprise_branch ADD INDEX enterprise_id_index(enterprise_id) USING BTREE;
ALTER TABLE enterprise_project ADD INDEX enterprise_id_index(enterprise_id) USING BTREE;
ALTER TABLE enterprise_credential ADD INDEX enterprise_id_index(enterprise_id) USING BTREE;
ALTER TABLE enterprise_person ADD INDEX enterprise_id_index(enterprise_id) USING BTREE;

在添加索引后，SQL耗时已经降到了2秒了。为了进一步提高查询速度，我又将索引类型改为了哈希索引，本以为哈希索引会提高查询速度，最终的结果却是两者速度差不多。其主要是enterprise_id的区分度不高、会出现大量的哈希冲突、需要高频的回表查询比对。

此时已经大致达成了本次优化目标，不久后随着数据量的上涨，这个查询又变得很慢了。
这时已不能使用动态实时统计的方案了，只能在enterprise_main表增加统计字段branch_count 、project_count等，用xxljob定时将统计数据更新在相应字段上。但是定时统计存在一个问题，数据不是实时的， 两次定时任务的执行间隙期间与真实数据有差异。这似乎是无法解决的问题，但是不要忘记mysql数据库的触发器，触发器可以做到实时更新数据。所以最终方案是定时任务统计+触发器实时更新，它能完美地解决这个问题。

-- 新增数据时'加1'
CREATE DEFINER=`root`@`localhost` TRIGGER `update_branch_count_by_add` AFTER INSERT ON `enterprise_branch` FOR EACH ROW BEGIN
    IF  (NEW.is_delete = 0  and NEW.enterprise_id is not null) THEN
        UPDATE enterprise_main SET branch_count=branch_count + 1 WHERE enterprise_id = NEW.enterprise_id and is_delete=0;
    END IF;
END

-- 删除数据时'减1'
CREATE DEFINER=`root`@`localhost` TRIGGER `update_branch_count_by_soft_del` AFTER UPDATE ON `enterprise_branch` FOR EACH ROW BEGIN
    IF  (OLD.is_delete=0 and  NEW.is_delete > 0 ) THEN
        UPDATE enterprise_main SET branch_count=branch_count - 1 WHERE enterprise_id = NEW.enterprise_id and is_delete=0 and branch_count>0 ;
    END IF;
    IF  (OLD.is_delete>0 and  NEW.is_delete = 0 ) THEN
        UPDATE enterprise_main SET branch_count=branch_count + 1 WHERE enterprise_id = NEW.enterprise_id and is_delete=0;
    END IF;
END