mysql Using temporary Using filesort 优化

最新推荐文章于 2023-04-06 00:11:42 发布

zzm_

最新推荐文章于 2023-04-06 00:11:42 发布

阅读量136

点赞数

文章标签： python 数据库

原文链接：https://my.oschina.net/b1ack2ephyr/blog/672917

版权

2019独角兽企业重金招聘Python工程师标准>>>

问题是这样的：有两个表News，Tags,他们的关系是ManyToMany，表如下(django ORM)：


class News(models.Model):
    """ news """
    title = models.CharField(max_length=128,verbose_name="新闻标题")
    rank = models.IntegerField(verbose_name="新闻ranking")
    news_time = models.DateField(verbose_name="发布时间")
    publisher = models.CharField(max_length=128,verbose_name="新闻来源")
    news_url = models.URLField(verbose_name="新闻网页链接")
    content = RichTextField(verbose_name="新闻内容")
    hash_digest = models.CharField(max_length=64,verbose_name="哈希摘要",unique=True)

class Tags(models.Model):
    """ tags """
    tag = models.CharField(max_length=32,verbose_name="标签")
    tag_hash = models.CharField(max_length=64,unique=True,verbose_name="标签标示")
    search_times = models.IntegerField(default=0,verbose_name="搜索次数")
    included_items_num = models.IntegerField(default=0,verbose_name="tag所含条目数量")
    news = models.ManyToManyField(News,verbose_name="关联内容")

然后我想根据tag_hash检索出响应的新闻,说形象点，就是找出含有关键字所对应的新闻，代码大概是这样的:

News.objects.filter(tags__tag_hash='c13dceabcb143acd6c9298265d618a9f',#`中国` 的 hash
                                news_time__lte='2016-05-10',
                                news_time__gte= '2011-09-01'
                               ).order_by('-news_time','-rank')

最终生成的sql：

SELECT
     `news_news`.`id`,
     `news_news`.`title`,
     `news_news`.`rank`,
     `news_news`.`news_time`,
     `news_news`.`content`
FROM `news_news`
INNER JOIN `news_tags_news`
ON ( `news_news`.`id` = `news_tags_news`.`news_id` )
INNER JOIN `news_tags`
ON ( `news_tags_news`.`tags_id` = `news_tags`.`id` )
WHERE (
     `news_tags`.`tag_hash` = 'c13dceabcb143acd6c9298265d618a9f'
      AND `news_news`.`news_time` >= '2011-09-01'
      AND `news_news`.`news_time` <= '2016-05-10'
)
ORDER BY `news_news`.`news_time` DESC, `news_news`.`rank` DESC LIMIT 9;

放到数据库当中查询，slow-query-log中出现：

##
# slow-query.log
##

# Time: 160510 11:14:15
# User@Host: qiulimao[qiulimao] @ [localhost] Id: 114472
# Query_time: 1.767350 Lock_time: 0.000136 Rows_sent: 9 Rows_examined: 23103
SET timestamp=1462850055;

好吧，explain一下：

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: news_tags
type: const
possible_keys: PRIMARY,tag_hash
key: tag_hash
key_len: 194
ref: const
rows: 1
Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: news_tags_news
type: ref
possible_keys: tags_id,news_tags_news_news_id_index,news_tags_news__tags_id
key: tags_id
key_len: 4
ref: const
rows: 6232
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: news_news
type: eq_ref
possible_keys: PRIMARY,search_result_index
key: PRIMARY
key_len: 4
ref: news.news_tags_news.news_id
rows: 1
Extra: Using where
3 rows in set (0.00 sec)

啊啊啊，我的索引用得挺好的啊，就是有Using temporary; Using filesort,查询的结果不是很多到无所谓，要是多了来，就没法玩了。

>>> .......

>>>.............

>>>.................

>>> 折腾很久之后：

最终，除了不用排序之外，我真的想不出什么能够消除filesort的方法了.........

看看大家能不能支招，

目前我有的思路是：做子查询

select news_table.id,
news_table.news_time
FROM
    (select
         news_news.id,
         news_news.news_time
         from news_news
         order by news_time desc,rank desc)
as news_table 
INNER JOIN `news_tags_news`
ON ( `news_table`.`id` = `news_tags_news`.`news_id` )
INNER JOIN `news_tags`
ON ( `news_tags_news`.`tags_id` = `news_tags`.`id` )
WHERE (
     `news_tags`.`tag_hash` = 'a4ce03da95bb1b3da0dc0629e20563ec'
      AND `news_table`.`news_time` >= '2011-09-01'
      AND `news_table`.`news_time` <= '2016-05-10'
);