我有一个MySQL(InnoDB)上的大表,其中包含产品资产(1300万行)。以下是我的数据库的简要架构:
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小时才返回结果。请问如何优化查询?!
t.name
上没有索引,我认为ORDER BY
语句会降低查询性能。如果您仅删除ORDER BY
,速度会更快吗? - Wiseguy