主页 / dba / 问题 / 173907
Accepted
reaanb
Asked: 2017-05-19 00:21:54 +0800 CST

此查询是否有合适的索引？

  • 772

是否有任何合适的索引来支持以下查询？

SELECT DISTINCT p.id
FROM p
INNER JOIN l ON p.id = l.p1_id OR p.id = l.p2_id
WHERE p.s = 'Active'
AND (
    (l.s IN (1, 7) AND l.rd <= CURDATE())
    OR
    (l.s = 2 AND MONTH(l.td) = MONTH(CURDATE()) AND YEAR(l.td) = YEAR(CURDATE()))
)

表：

CREATE TABLE p (
  id int(11) NOT NULL,
  s varchar(10) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE l (
  id int(11) NOT NULL,
  p1_id int(11) NOT NULL,
  p2_id int(11) NOT NULL,
  s int(11) NOT NULL,
  rd date NOT NULL,
  td date DEFAULT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (p1_id) REFERENCES p (id) ON UPDATE CASCADE,
  FOREIGN KEY (p2_id) REFERENCES p (id) ON UPDATE CASCADE
) ENGINE=InnoDB;

解释：

+--+-----------+-----+----+-------------+---+-------+---+----+--------------------------------------------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra                                             |
+--+-----------+-----+----+-------------+---+-------+---+----+--------------------------------------------------+
| 1|SIMPLE     |l    |ALL |             |   |       |   |3960|Using where; Using temporary                      |
| 1|SIMPLE     |p    |ALL |PRIMARY      |   |       |   |5091|Using where; Using join buffer (Block Nested Loop)|
+--+-----------+-----+----+-------------+---+-------+---+----+--------------------------------------------------+

我尝试了一些基于 JOIN 和 WHERE 子句中的列的单列索引和复合索引，虽然 DBMS 使用了基于所有相关列的索引，但它们对评估的行数没有影响。

或者，能否以更有效的方式重写查询？

编辑：

ps 上的索引提供了一些性能改进，从 1.4 秒减少到 0.3 秒。

ALTER TABLE p
ADD INDEX (s);

新说明：

+--+-----------+-----+----+-------------+---+-------+-----+----+--------------------------------------------------------+
|id|select_type|table|type|possible_keys|key|key_len|ref  |rows|Extra                                                   |
+--+-----------+-----+----+-------------+---+-------+-----+----+--------------------------------------------------------+
| 1|SIMPLE     |p    |ref |PRIMARY,s    |s  |32     |const|5058|Using where; Using index; Using temporary               |
| 1|SIMPLE     |l    |ALL |             |   |       |     |3960|Range checked for each record (index map: 0x6); Distinct|
+--+-----------+-----+----+-------------+---+-------+-----+----+--------------------------------------------------------+

是否可以进一步改进？

编辑 2：

应用建议索引解释 Rick James 的 UNION 查询：

+--+------------+----------+-----+-------------+---+-------+-----+----+------------------------+
|id|select_type |table     |type |possible_keys|key|key_len|ref  |rows|Extra                   |
+--+------------+----------+-----+-------------+---+-------+-----+----+------------------------+
| 1|PRIMARY     |l         |range|srd,std      |srd|7      |     | 733|Using where; Using index|
| 2|UNION       |l         |range|srd,std      |std|7      |     |   2|Using where; Using index|
|  |UNION RESULT|<union1,2>|ALL  |             |   |       |     |    |Using temporary         |
+--+------------+----------+-----+-------------+---+-------+-----+----+------------------------+

一些统计数据：

SELECT s, COUNT(*) FROM l GROUP BY s
+-+--------+
|s|COUNT(*)|
+-+--------+
|1|     733|
|2|    3222|
|8|       5|
+-+--------+
      =3960

SELECT s, COUNT(*) FROM p GROUP BY s
+--------+--------+
|s       |COUNT(*)|
+--------+--------+
|Active  |    5059|
|Inactive|      32|
+--------+--------+
             =5091

ls 中的 8 是正确的，不应包含在上面的查询结果中。即使没有 ls=7 的行，我也需要包括这种可能性。

预期结果集包含 1144 条记录。

最后：

根据 Rick James 的建议，下面的查询加上索引pon(s, td)和中的索引相结合(r, td)，执行效率达到我希望达到的水平（~50 毫秒）：

SELECT DISTINCT p.id
FROM (
    SELECT p1_id AS id
        FROM l
        WHERE s = 1 AND rd <= CURDATE()
    UNION  ALL 
    SELECT p2_id
        FROM l
        WHERE s = 7 AND rd <= CURDATE()
    UNION  ALL 
    SELECT p1_id AS id
        FROM l
        WHERE s = 1 AND rd <= CURDATE()
    UNION  ALL 
    SELECT p2_id
        FROM l
        WHERE s = 7 AND rd <= CURDATE()
    UNION  ALL 
    SELECT p1_id
        FROM l
        WHERE s = 2 AND td >= CONCAT(LEFT(CURDATE(), 7), '-01') AND td < CONCAT(LEFT(CURDATE(), 7), '-01') + INTERVAL 1 MONTH
    UNION ALL
    SELECT p2_id
        FROM l
        WHERE s = 2 AND td >= CONCAT(LEFT(CURDATE(), 7), '-01') AND td < CONCAT(LEFT(CURDATE(), 7), '-01') + INTERVAL 1 MONTH
) x
JOIN p ON p.id = x.id
WHERE p.s = 'Active'
mysql index

2 个回答

  1. Best Answer

    这是一团糟，因为ORs。

    此外，尽量避免在函数中隐藏列，包括MONTH()和YEAR()。如果l.td永远不能大于CURDATE()，则更AND MONTH(l.td) = MONTH(CURDATE()) AND YEAR(l.td) = YEAR(CURDATE())改为（或本身与编译时常量相比的AND l.td >= CONCAT(LEFT(CURDATE(), 7), '-01')其他一些表达式）l.td

    让我们首先关注查询的这一部分：

    SELECT  l.id
    FROM  l
    WHERE  ( l.s IN (1, 7)
              AND  l.rd <= CURDATE()
           )
      OR  (l.s = 2
              AND  l.td >= CONCAT(LEFT(CURDATE(), 7), '-01') 
          )

    这会给你相同的结果集吗？...

    SELECT  l.id
    FROM  l
    WHERE  l.s IN (1, 7)
      AND  l.rd <= CURDATE()
    UNION  ALL 
SELECT  l.id
    FROM  l
    WHERE  l.s = 2
      AND  l.td >= CONCAT(LEFT(CURDATE(), 7), '-01')

    现在，它会运行得更快吗？

    INDEX(s,rd),
INDEX(s,td)

    ？

    在继续之前，我们需要知道每个表中有多少行，以及有多少行p是“活动的”。请EXPLAIN SELECT ...为此提供UNION提供。

    更多的

    INDEX(s)会很有帮助，因为大多数都是“活跃的”。

    的UNION解释似乎不错。它可能比使用OR. 所以现在......尝试制作

    SELECT ...
    FROM ( ...my UNION...) AS u
    JOIN p ON ...
    WHERE ... active

    原则是我们加快了速度OR，并使结果集有所下降。然后JOIN将过滤掉少数不“活跃”的。

    • 2

  2. 我会检查 ps 和 ls 是否真的需要是 VARCHAR 而不是 ENUM 或另一个具有所有状态值的表的 FOREIGN KEY。这将对您的查询有很大帮助，即使它仍然查找相同数量的记录。

    在任何情况下，我都会为 ps 和 ls 添加索引。也可以添加 l.rd 和 l.td 的索引，但这取决于这些表主要用于选择还是用于插入。

    我仍然觉得你的查询有点奇怪。您不想从 l 中选择吗？例子：

    SELECT DISTINCT(l.id)
FROM l
WHERE (
    (l.s IN (1, 7) AND l.rd <= CURDATE())
    OR
    (l.s = 2 AND MONTH(l.td) = MONTH(CURDATE()) AND YEAR(l.td) = YEAR(CURDATE()))
) AND (
    l.p1_id IN (SELECT p.id FROM p WHERE p.s = 'Active')
    OR
    l.p2_id IN (SELECT p.id FROM p WHERE p.s = 'Active')
)

    那会以相反的方式进行搜索。这对你有用吗？

    • 1

相关问题