【沉浸式解决问题】优化MySQL中多表union速度慢的问题

一、问题描述

同事要实现一个搜索指标名称的关键词，然后按年份统计数量的接口，跟我说时间太长查不出来，无法实现，所以我上手解决一下。

---

二、场景还原

1. 数据库表

数据按省进行了分库存储，每个库5-6个省表，每张表目前大约两千万的数据量

2. 查询sql

先简单写个sql实现需求，这里先不考虑查询的关键词，逻辑就是把每一张表里面的数量按年份count统计，通过union拼接起来，再次按年份SUM求和。

SELECT y, SUM(c) 
FROM (
SELECT `year` y, count(*) c FROM guangdong_data_queries GROUP BY `year`
UNION ALL
SELECT `year`, count(*) FROM guangxi_data_queries GROUP BY `year`
UNION ALL
SELECT `year`, count(*) FROM hainan_data_queries GROUP BY `year`
UNION ALL
SELECT `year`, count(*) FROM henan_data_queries GROUP BY `year`
UNION ALL
SELECT `year`, count(*) FROM hubei_data_queries GROUP BY `year`
UNION ALL
SELECT `year`, count(*) FROM hunan_data_queries GROUP BY `year`
) AS a
GROUP BY a.y

---

三、解决方案

1. 索引

mysql优化第一步，增加索引
查看了一下，果然原表都没有索引，给year和指标名称增加后快了很多，由于是补记录的，忘记当时具体时间了

2. 视图

索引加完了就该考虑减少查询的数据量了
像这种模糊查询然后统计数量的，实际上可以先按照查询字段和统计字段分组，然后统计存到一个视图或者新的表里，提前隐藏掉其他的分类维度以实现缩小数据量，我这个表里面还有地区等分类字段，2000w的数据按指标名称和年份分组后就只有200w了，以后即使是模糊查询也是从这200w的基础上进行查询。

原理就是之前是先模糊匹配再去重，现在是先去重再模糊匹配，把指标名称相同但是年份不同的数据提前统计了，当然就快了，适用于数据不是实时变化更新的，我这个是以前的指标数据，正好符合。

当然，别忘记加索引哦

3. 分组

还能再优化吗，可以的！
可以考虑把上一步生成的结果，再次按年份进行分组统计数量，把年份及对应数量以json的格式存到一个字段，例如

{
	2024:1000
	2023:900
	2022:800
}

这样可以使模糊查询的表再次缩小，假设每个指标平均涉及20个年份，那么查询的数据表大小就只有10w了，模糊匹配就很简单了，之后在内存中把年份统计字段取出来，遍历按年份加起来，相当于把最终要的统计数据提前计算出来了，同样的还可以把其他分类统计字段也存到一个字段里

4. 并行

还可以再优化吗？可以的！而且是很大的优化！
那就是union本身的执行的是串行的（【沉浸式解决问题】Mysql中union连接的子查询是否并行执行），而在连接的子查询非常多的时候，这无疑是致命的，可以通过多线程创建多个连接分别进行查询，在内存中合并最终结果，只要数据库扛得住，就能把速度优化到一个查询的时间！！！

---

后记

sql优化还有很多的方法和技巧，除了sql本身，也要更多的考虑业务端的优化。

---

喜欢的点个关注吧><！祝你永无bug！

/*
                   _ooOoo_
                  o8888888o
                  88" . "88
                  (| -_- |)
                  O\  =  /O
               ____/`---'\____
             .'  \\|     |//  `.
            /  \\|||  :  |||//  \
           /  _||||| -:- |||||-  \
           |   | \\\  -  /// |   |
           | \_|  ''\---/''  |   |
           \  .-\__  `-`  ___/-. /
         ___`. .'  /--.--\  `. . __
      ."" '<  `.___\_<|>_/___.'  >'"".
     | | :  `- \`.;`\ _ /`;.`/ - ` : | |
     \  \ `-.   \_ __\ /__ _/   .-` /  /
======`-.____`-.___\_____/___.-`____.-'======
                   `=---='
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
            佛祖保佑       永无BUG
*/