mysql - 优化 django 查询

Question

在网站上实现了一项新功能，就好像它是一个“社交网络”。

用户进行的各种操作、接收消息等。他们的 friend 可以看到所有 Action 。

class userMuralEntry(models.Model):
   user_src = models.ForeignKey(userInfo, related_name="user_src")
   user = models.ManyToManyField(userInfo, related_name="user_dest")
   user_follow = models.ManyToManyField(userInfo, related_name="user_follow")
   private = models.BooleanField(default=False)
   content = models.TextField()
   date = models.DateTimeField(default=datetime.now)
   last_update = models.DateTimeField()

   object_type = models.ForeignKey(muralType)

   content_type = models.ForeignKey(ContentType)
   object_id = models.PositiveIntegerField()
   content_object = generic.GenericForeignKey('content_type', 'object_id')

每次我访问我的首页时，我都会收到所有 friend 的操作，除了私有(private)操作。所以，我做了这个 django 模型查询

  mural_list = db.userMuralEntry.objects.filter(Q(user__pk__in=friend_list)|Q(user_src__pk__in=friend_list)).order_by('-last_update')
  m = mural_list.exclude(
  Q(private=True),
  ~Q(user_src=me_db) & ~Q(user=me_db))

  m = m.distinct()

所有这些代码都会生成这个大查询(此处带有解释输出)

SELECT DISTINCT myps3t_usermuralentry.id, 
                myps3t_usermuralentry.user_src_id, 
                myps3t_usermuralentry.private, 
                myps3t_usermuralentry.content, 
                myps3t_usermuralentry.DATE, 
                myps3t_usermuralentry.last_update, 
                myps3t_usermuralentry.object_type_id, 
                myps3t_usermuralentry.content_type_id, 
                myps3t_usermuralentry.object_id 
FROM   myps3t_usermuralentry 
       LEFT OUTER JOIN myps3t_usermuralentry_user 
         ON ( myps3t_usermuralentry.id = 
              myps3t_usermuralentry_user.usermuralentry_id ) 
       LEFT OUTER JOIN myps3t_userinfo t4 
         ON ( myps3t_usermuralentry.user_src_id = t4.id ) 
WHERE  ( ( myps3t_usermuralentry_user.userinfo_id = 20877 
            OR myps3t_usermuralentry.user_src_id = 20877 ) 
         AND NOT ( myps3t_usermuralentry.private = 1 
                   AND ( NOT (( t4.id = 21095 
                                AND NOT ( t4.id IS NULL ) )) 
                         AND NOT ( myps3t_usermuralentry.id IN 
                                   (SELECT u1.usermuralentry_id 
                                    FROM 
                                   myps3t_usermuralentry_user u1 
                                                                WHERE  ( 
                                   u1.userinfo_id = 
                                   21095 
                                   AND 
                                   u1.usermuralentry_id 
                                   IS 
                                   NOT 
                                   NULL 
                                                                       )) ) ) ) 
       ) 
ORDER  BY myps3t_usermuralentry.last_update; 



mysql> explain SELECT DISTINCT `myps3t_usermuralentry`.`id`, `myps3t_usermuralentry`.`user_src_id`, `myps3t_usermuralentry`.`private`, `myps3t_usermuralentry`.`content`, `myps3t_usermuralentry`.`date`, `myps3t_usermuralentry`.`last_update`, `myps3t_usermuralentry`.`object_type_id`, `myps3t_usermuralentry`.`content_type_id`, `myps3t_usermuralentry`.`object_id` FROM `myps3t_usermuralentry` LEFT OUTER JOIN `myps3t_usermuralentry_user` ON (`myps3t_usermuralentry`.`id` = `myps3t_usermuralentry_user`.`usermuralentry_id`) LEFT OUTER JOIN `myps3t_userinfo` T4 ON (`myps3t_usermuralentry`.`user_src_id` = T4.`id`) WHERE ((`myps3t_usermuralentry_user`.`userinfo_id` = 20877  OR `myps3t_usermuralentry`.`user_src_id` = 20877 ) AND NOT (`myps3t_usermuralentry`.`private` = 1  AND (NOT ((T4.`id` = 21095  AND NOT (T4.`id` IS NULL))) AND NOT (`myps3t_usermuralentry`.`id` IN (SELECT U1.`usermuralentry_id` FROM `myps3t_usermuralentry_user` U1 WHERE (U1.`userinfo_id` = 21095  AND U1.`usermuralentry_id` IS NOT NULL)))))) ORDER BY `myps3t_usermuralentry`.`last_update` DESC LIMIT 20;
+----+--------------------+----------------------------+-----------------+-------------------------------------------------------------------------------------------+-------------------+---------+---------------------------------------------------+--------+------------------------------------+
| id | select_type        | table                      | type            | possible_keys                                                                             | key               | key_len | ref                                               | rows   | Extra                              |
+----+--------------------+----------------------------+-----------------+-------------------------------------------------------------------------------------------+-------------------+---------+---------------------------------------------------+--------+------------------------------------+
|  1 | PRIMARY            | myps3t_usermuralentry      | ALL             | myps3t_usermuralentry_99bd10ae                                                            | NULL              | NULL    | NULL                                              | 665410 | Using temporary; Using filesort    |
|  1 | PRIMARY            | myps3t_usermuralentry_user | ref             | usermuralentry_id,myps3t_usermuralentry_user_bcd7114e                                     | usermuralentry_id | 4       | fabriciols_ps3t.myps3t_usermuralentry.id          |      2 | Using where; Using index; Distinct |
|  1 | PRIMARY            | T4                         | eq_ref          | PRIMARY                                                                                   | PRIMARY           | 4       | fabriciols_ps3t.myps3t_usermuralentry.user_src_id |      1 | Using where; Using index; Distinct |
|  2 | DEPENDENT SUBQUERY | U1                         | unique_subquery | usermuralentry_id,myps3t_usermuralentry_user_bcd7114e,myps3t_usermuralentry_user_6b192ca7 | usermuralentry_id | 8       | func,const                                        |      1 | Using index; Using where           |
+----+--------------------+----------------------------+-----------------+-------------------------------------------------------------------------------------------+-------------------+---------+---------------------------------------------------+--------+------------------------------------+
4 rows in set (0.00 sec)

mysql> show indexes from myps3t_usermuralentry ;
+-----------------------+------------+--------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                 | Non_unique | Key_name                       | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------------+------------+--------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| myps3t_usermuralentry |          0 | PRIMARY                        |            1 | id              | A         |      665410 |     NULL | NULL   |      | BTREE      |         |
| myps3t_usermuralentry |          1 | myps3t_usermuralentry_99bd10ae |            1 | user_src_id     | A         |       22180 |     NULL | NULL   |      | BTREE      |         |
| myps3t_usermuralentry |          1 | myps3t_usermuralentry_ae71a55b |            1 | object_type_id  | A         |           8 |     NULL | NULL   |      | BTREE      |         |
| myps3t_usermuralentry |          1 | myps3t_usermuralentry_e4470c6e |            1 | content_type_id | A         |          13 |     NULL | NULL   |      | BTREE      |         |
+-----------------------+------------+--------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)

我对索引做了一些调整，但它太慢了......一个有 3-4 个 friend 的用户大约需要 5 秒!

该表目前有 50 万行。

一些改进的想法？我的 django 代码很乱吗？最好由我自己进行查询？

Answer 1

您需要再读一遍:https://docs.djangoproject.com/en/dev/topics/db/queries/

有多种方法可以简化您的代码。例如:

mural_list = db.userMuralEntry.objects.filter(Q(user__pk__in=friend_list)|Q(user_src__pk__in=friend_list))

相当于:

mural_list = db.userMuralEntry.objects.filter(user__pk__in=friend_list)|db.userMuralEntry.objects.filter(user_src__pk__in=friend_list)

和

m = mural_list.exclude(
  Q(private=True),
  ~Q(user_src=me_db) & ~Q(user=me_db))

相当于

m = mural_list.exclude(private=True).exclude(user_src=me_db).exclude(user=me_db)

请注意，最好将 order_by 子句放在末尾，以避免 order_by 查询上的外部联接。

@fabriciols 在此分享了最终优化的 SQL:https://chat.stackoverflow.com/transcript/message/2795524#2795524