代码之家 › 专栏 › 技术社区 › Filipe Ferminiano

无法在mysql索引上强制索引

greatest-n-per-group query-optimization mysql

Filipe Ferminiano · 技术社区 · 8 年前

我试图优化一个查询,但解释查询给出mysql没有使用任何索引。

这是我的问题:

explain
 SELECT t1.* 

    FROM crypto_screener.prices as t1 FORCE INDEX (PRIMARY)

    where t1.id = (
      select t2.id
      from (select *
              from
              crypto_screener.prices FORCE INDEX (allfilters)
              where date > '2017-12-07'
              ) as t2
      where t2.symbol = t1.symbol
      order by t2.id desc
      limit 1
      )
;

这是 show index from prices;

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
prices  0   PRIMARY 1   id  A   57718   NULL    NULL        BTREE       
prices  0   priceid 1   id  A   57718   NULL    NULL        BTREE       
prices  1   pricefilters    1   symbol  A   369 NULL    NULL    YES BTREE       
prices  1   pricefilters    2   date    A   57718   NULL    NULL    YES BTREE       
prices  1   datefilters 1   date    A   506 NULL    NULL    YES BTREE       
prices  1   symbolfilters   1   symbol  A   421 NULL    NULL    YES BTREE       
prices  1   allfilters  1   id  A   57718   NULL    NULL        BTREE       
prices  1   allfilters  2   symbol  A   57718   NULL    NULL    YES BTREE       
prices  1   allfilters  3   date    A   57718   NULL    NULL    YES BTREE

查询现在不会使用表中的数据量运行。如何对此进行优化?

1 回复 | 直到 8 年前

O. Jones 8 年前

看起来你想要最高 id 每个不同项的值 symbol 特定日期范围内的值。

下面是你如何做到这一点。首先,使用子查询获取 symbol, id 显示最高值的值 身份证件 每个符号的值,用于具有所需日期的表子集。

                 SELECT symbol, MAX(id) id
                   FROM prices
                  WHERE date >  '2017-12-07'
                  GROUP BY symbol

此子查询可以通过上的复合索引进行优化 date, symbol, id . 为什么?它随机访问索引到第一个符合条件的日期,然后按顺序扫描索引,查找符号和id的不同值。

您可以测试该子查询。确保它能给你合理的结果。

然后在主查询中使用它,如下所示:

 SELECT t1.*
   FROM prices t1
   JOIN (
                 SELECT symbol, MAX(id) id
                   FROM prices
                  WHERE date >  '2017-12-07'
                  GROUP BY symbol
        ) t2 ON t1.id = t2.id AND t1.symbol = t2.symbol

现在,因为你的 身份证件 值都是唯一的,您可以简化 ON 略去条件 AND t1.symbol = t2.symbol .

如果有适当的复合索引,这应该运行得非常快。

Pro提示1 :不要仅仅为了更好的度量而创建额外的索引。确保它们可以帮助您运行所需的查询。

Pro提示2 : date > '2017-12-07' 在您指定的日期午夜排除行,但在该日期午夜之后获取所有行。如果您的 date 列为 DATETIME ,使用 > 可能不正确。如果数据类型为 DATE ,则筛选器的含义与 date >= '2017-12-08' .

Pro提示3 :试图强制MySQL的查询计划器使用某些索引通常会适得其反。更好的方法是确保您拥有正确的索引。