时间戳

首页 > 解决方案 > 使用 OR 进行查询，使查询执行缓慢

mysql - 使用 OR 进行查询，使查询执行缓慢

问题描述

我是新手，在使用 OR 查询时遇到问题！

当我在没有 OR 的情况下执行此操作时，执行时间为：0.02 秒

SELECT DISTINCT p.*,
                ht.html_title,
                ht.html_content,
                sc.sub_cat_name,
                sc.main_cat_id,
                us.username,
                c.name AS cityname
FROM   ad_product p,
       ad_html ht,
       ad_catagory_sub sc,
       ad_user us,
       ad_cities c
WHERE  ( ht.ad_id = p.id )
       AND ( sc.sub_cat_id = p.category )
       AND ( us.id = p.user_id )
       AND ( p.category = 216 )
       AND ( p.status = 'active' )
       AND ( c.id = p.city )
       AND ( p.city = 135 )
ORDER  BY p.created_at DESC;

当我在 OR 条件下尝试它时，大约需要 4.70 秒

SELECT DISTINCT p.*,
                ht.html_title,
                ht.html_content,
                sc.sub_cat_name,
                sc.main_cat_id,
                us.username,
                c.name AS cityname
FROM   ad_product p,
       ad_html ht,
       ad_catagory_sub sc,
       ad_user us,
       ad_cities c
WHERE  ( ht.ad_id = p.id )
       AND ( sc.sub_cat_id = p.category )
       AND ( us.id = p.user_id )
       AND ( p.category = 216
              OR p.parent_category = 216 )
       AND ( p.status = 'active' )
       AND ( c.id = p.city )
       AND ( p.city = 135 )
ORDER  BY p.created_at DESC;

解释：

+----+-------------+-------+------------+-------------+--------------------------------------------------------------------------------+--------------------------+---------+---------------------------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys                                                                  | key                      | key_len | ref                       | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+-------------+--------------------------------------------------------------------------------+--------------------------+---------+---------------------------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | const       | PRIMARY                                                                        | PRIMARY                  | 4       | const                     |     1 |   100.00 | Using temporary; Using filesort                    |
|  1 | SIMPLE      | p     | NULL       | index_merge | PRIMARY,id,parent_category,city,user_id_2,category,status,id_2,id_3,category_2 | category,parent_category | 4,4     | NULL                      | 67889 |     4.74 | Using union(category,parent_category); Using where |
|  1 | SIMPLE      | sc    | NULL       | eq_ref      | PRIMARY                                                                        | PRIMARY                  | 4       | abc_classified.p.category |     1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | us    | NULL       | eq_ref      | PRIMARY                                                                        | PRIMARY                  | 4       | abc_classified.p.user_id  |     1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | ht    | NULL       | ref         | ad_id                                                                          | ad_id                    | 4       | abc_classified.p.id       |     1 |   100.00 | NULL                                               |
+----+-------------+-------+------------+-------------+--------------------------------------------------------------------------------+--------------------------+---------+---------------------------+-------+----------+----------------------------------------------------+

我想我已经定义了正确的索引，有人可以指出我做错了什么吗？或者我怎样才能减少执行时间？另外，据我所知，为什么会发生这种情况？

标签： mysqlsqlperformanceconditional-statements

解决方案

我认为您的查询将受益于联合而不是或。这将创建 2 组数据。一个在 categoryid 上，另一个在父 categoryid 上。这将比定义 2 个负集更快。一组没有 categoryid = 216，一组没有父 categoryid = 216。

SELECT DISTINCT p1.*,
        ht.html_title,
        ht.html_content,
        sc.sub_cat_name,
        sc.main_cat_id,
        us.username,
        c.name AS cityname
FROM   ad_product p1 inner join ad_html ht on ht.ad_id = p1.id
    inner join ad_category_sub sc on sc.sub_cat_id = p1.category
    inner join ad_user us on  us.id = p1.user_id 
    inner join ad_cities c on c.id = p1.city   
WHERE ( p1.status = 'active' )
   AND ( p1.city = 135 )
   AND ( p1.category = 216)
UNION
SELECT DISTINCT p.*,
        ht.html_title,
        ht.html_content,
        sc.sub_cat_name,
        sc.main_cat_id,
        us.username,
        c.name AS cityname
FROM   ad_product p 
    inner join ad_html ht on ht.ad_id = p.id
    inner join ad_category_sub sc on sc.sub_cat_id = p.category
    inner join ad_user us on us.id = p.user_id
    inner join ad_cities c on c.id = p.city
WHERE ( p.status = 'active' )
  AND ( p.city = 135 )
  AND (p.parent_category = 216)
ORDER  BY p.created_at DESC;

推荐阅读