在一张千万级表中一起查询统计出来类型ID和类型名称的最优语句

 

假设在一张千万级表中有两个需要的字段，分别为类型ID和类型名称，并且类型ID和类型名称在同一条数据内是一致对应的

如果只通过一条SQL语句的方式查询出来，常规情况下一般只需要通过分组（group by）或者去重（distinct）来操作，实际测试分别如下：

-- 前提是 product_id OR product_name OR product_id,product_name 字段列需要加上索引

SELECT product_id,product_name from table1 GROUP BY product_id ;

--》》》或者

SELECT product_id,product_name from table1 GROUP BY product_name ;

--》》》亦或者

SELECT product_id,product_name from table1 GROUP BY product_id,product_name ;

但是如上语句执行过程中性能差的不是一点半点，具体运行时间如下

SELECT count(1) from table1 ;

; > OK > 时间: 8.325s

总测试数据量 23466238 条，且 product_id, product_name 分别建有索引，测试使用的是自己本地笔记本电脑

看下每条语句实际的运行时间情况

SELECT product_id from table1 GROUP BY product_id 
> OK 
> 时间: 0.001s 

SELECT product_name from table1 GROUP BY product_id 
> OK 
> 时间: 173.959s 

SELECT product_id,product_name from table1 GROUP BY product_id （这条查询的才是最终需要的结果）
> OK 
> 时间: 176.596s

从以上结果中不难看出，只有当group by 后的字段和查询展示字段一致时，走了索引，查询速度极快，展示不一致时无法走索引。

同样的我们将 group by 后的字段更换为 product_name 以及更换语句为 distinct 方式的效果和 group by product_id 的情况是一样的，有兴趣的朋友可以自己试试看

SELECT DISTINCT product_id from table1;
SELECT DISTINCT product_name from table1;
SELECT DISTINCT product_id,product_name from table1;

另外就会有人会说为什么不用联合索引，那我们顺带看下联合索引下的效果（需要创建 product_id,product_name 的联合索引）

SELECT product_id,product_name from table1 GROUP BY product_id,product_name
> OK
> 时间: 85.269s

但从查询时间上看是有一定提升的，但由于我个人本地使用场景限制，联合索引当前两个字段的联合索引使用有限，且这个速度依然是很不理想的

既然联合索引，单个分别创建索引都无法满足实时快速查询两个字段的组合结果，那我们是否可以尝试改变查询方式去实现

在实际优化测试中发现给单语句查询添加主键之后对查询性能没有大的影响，那么如果单从主键入手，先查寻到主键，在通过主键去查询所需要的数据是否会变快，请看测试数据如下

SELECT a.product_id,a.product_name from table1 a where a.id in (
SELECT max(id) from table1 GROUP BY product_id
);

　> OK
　> 时间: 77.054s

不难看出，上边的这个查询语句通过改造，一定程度上已经有了进一步的提升，但是在实际应用中还不够，存在太大差距了

既然表主键和索引字段结合有一定的提升空间，且但索引在单字段查询时能够异常的快，那是否可以通过两个单字段索引和主键分别结合，在这个基础上在做进一步关联，继续往下看

先使用单表主键结合测试

SELECT max(id) id,product_id from table1 GROUP BY product_id
> OK
> 时间: 0.016s

SELECT max(id) id,product_name from table1 GROUP BY product_name
> OK
> 时间: 0.009s

单从这个结果来看，已经超出预期很多了，最后将这两个子查询结合一下不就可以了，改造后的语句如下

SELECT a.product_id b.product_name from
(SELECT max(id) id,product_id from table1 GROUP BY product_id ) a, 
(SELECT max(id) id,product_name from table1 GROUP BY product_name) b WHERE a.id = b.id ORDER BY a.product_id；
> OK
> 时间: 0.006s

最终结论是通过主键和单字段索引的结合做关联查询是成功的，在千万级数据表内实现毫秒级的查询优化成果是值得推荐的，最后完美收官！

table1