mysql – 如何在大表上优化计数SQL查询

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql – 如何在大表上优化计数SQL查询，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2367字，纯文字阅读大概需要4分钟。

内容图文

我有一个关于mysql(innodb)的大表,它包含产品资产(13百万行).这里是我的数据库的一个小模式：

product <-many2one-- file_item --one2many--> family --many2one--> download_type

* file_item *表是包含数百万行的大表.我尝试使用以下sql查询按下载类型计算产品：

select t.name as type, 
count(p.product_id) as n 
from file_item p 
inner join family f on f.id = p.family_id 
inner join type t on f.id_type = t.id 
group by t.id order by t.name;

* file_item *表上有3个索引：

> product_family_idx(product_id,family_id)
> family_idx(family_id)
> product_idx(product_id)
解释输出：

+----+-------------+-------+--------+-----------------------------------+---------+---------+-------------------+----------+---------------------------------+
| id | select_type | table | type   | possible_keys                     | key     | key_len | ref               | rows     | Extra                           |
+----+-------------+-------+--------+-----------------------------------+---------+---------+-------------------+----------+---------------------------------+
|  1 | SIMPLE      | p     | ALL    | FAMILY_IDX,PRODUCT_FAMILY_IDX     | NULL    | NULL    | NULL              | 13862870 | Using temporary; Using filesort | 
|  1 | SIMPLE      | f     | eq_ref | PRIMARY,TYPE_ID                   | PRIMARY | 4       | MEDIA.p.FAMILY_IDX|        1 |                                 | 
|  1 | SIMPLE      | t     | eq_ref | PRIMARY                           | PRIMARY | 4       | MEDIA.f.TYPE_ID   |        1 |                                 | 
+----+-------------+-------+--------+-----------------------------------+---------+---------+-------------------+----------+---------------------------------+

查询需要1个多小时才能返回结果.
请问我如何优化查询？

解决方法:

这是您的原始查询：

select t.name as type,  
count(p.product_id) as n  
from file_item p  
inner join family f on f.id = p.family_id  
inner join type t on f.id_type = t.id  
group by t.id order by t.name; 

您需要进行两项重大更改：

主要变化#1：重构查询

SELECT A.ProductCount,B.name type
FROM
(
    SELECT id_type id,COUNT(1) ProductCount
    FROM
    (
        SELECT p.id_type
        FROM (SELECT family_id,id_type FROM file_item) p
        INNER JOIN (SELECT id FROM family) f on f.id = p.family_id
    ) AA
    GROUP BY id_type
) A
INNER JOIN type B USING (id)
ORDER BY B.name;

主要变化#2：创建支持重构查询的索引

ALTER TABLE file_item ADD INDEX family_type_idx (family_id,id_type);

试试看 ！！！

内容总结

以上是互联网集市为您收集整理的mysql – 如何在大表上优化计数SQL查询全部内容，希望文章能够帮你解决mysql – 如何在大表上优化计数SQL查询所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端