sql - 为什么这个索引不起作用(Mysql)

Question

我有这张表:

CREATE TABLE  `maindb`.`daily_info` (
  `di_date` date NOT NULL,
  `di_sid` int(10) unsigned NOT NULL default '0',
  `di_type` int(10) unsigned NOT NULL default '0',
  `di_name` varchar(20) NOT NULL default '',
  `di_num` int(10) unsigned NOT NULL default '0',
  `di_abt` varchar(1) NOT NULL default 'a',
  PRIMARY KEY  (`di_date`,`di_sid`,`di_type`,`di_name`,`di_abt`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

当我使用这个查询时:

explain
SELECT  MONTH(di_date) as label1, DAYOFMONTH(di_date) as label2, sum(di_num) as count , di_abt as abt
FROM   `daily_info`
WHERE  di_sid=6
       AND di_type = 4
       AND di_name='clk-1'
       AND di_date > '2009-10-01' AND di_date < '2009-10-16'
GROUP BY
       DAYOFMONTH(di_date)
ORDER BY
       TO_DAYS(di_date) DESC

我得到:

1, 'SIMPLE', 'daily_info', 'range', 'PRIMARY', 'PRIMARY', '3', '', 2500, 'Using where; Using temporary; Using filesort'

实际上，如果 key 有效并且查询将通过 di_date、di_sid 和 di_type 进行过滤，则它只需要搜索几十行。

索引(或查询)有什么问题？

谢谢!

Answer 1

您在第一个索引列上使用范围条件，这会消除对其他列进行过滤的可能性。

此索引中没有单个连续范围包含那些且仅包含满足条件的记录。

MySQL无法做到SKIP SCAN这将跳过 di_date 的不同值.这就是它做到最好的原因:使用 range访问过滤器di_date并使用 WHERE过滤所有其他字段。

要么像这样重新创建索引(最好的决定):

PRIMARY KEY  (`di_sid`,`di_type`,`di_name`,`di_date`,`di_abt`)

或者，如果您无法重新创建索引，您可以模拟 SKIP SCAN :

SELECT  MONTH(di.di_date) as label1, DAYOFMONTH(di.di_date) as label2, sum(di.di_num) as count , di.di_abt as abt
FROM    (
        SELECT  DISTINCT di_date
        FROM    daily_info
        WHERE   di_date > '2009-10-01' AND di_date < '2009-10-16'
        ) do
JOIN    daily_info di
ON      di.di_date <= do.di_date
        AND di.di_date>= do.di_date
        AND di_sid = 6
        AND di_type = 4
        AND di_name = 'clk-1'
GROUP BY
        DAYOFMONTH(di.di_date)
ORDER BY
        TO_DAYS(di.di_date) DESC

确保Using index for group-by和 Range checked for each record都在计划中。

这个条件:

di.date <= do.date
AND di.date >= do.date

用于代替简单的 di.date = do.date强制范围检查。

有关模拟 SKIP SCAN 的更详细说明，请参阅我博客中的这篇文章:

• Emulating SKIP SCAN

更新:

后一个查询实际上使用了等值连接和 MySQL无需技巧即可对其进行优化。

上面的技巧只适用于范围查询，即。 e.当最内层循环应该使用 range访问权限，而不是 ref访问权限。

如果你必须做类似 di_name <= 'clk-1' 的事情，它会很有用

这个查询应该可以正常工作:

SELECT  MONTH(di.di_date) as label1, DAYOFMONTH(di.di_date) as label2, sum(di.di_num) as count , di.di_abt as abt
FROM    (
        SELECT  DISTINCT di_date
        FROM    daily_info
        WHERE   di_date > '2009-10-01' AND di_date < '2009-10-16'
        ) do
JOIN    daily_info di
ON      di.di_date = do.di_date
        AND di_sid = 6
        AND di_type = 4
        AND di_name = 'clk-1'
GROUP BY
        DAYOFMONTH(di.di_date)
ORDER BY
        TO_DAYS(di.di_date) DESC

确保di使用 ref使用 key_len = 33 可以在此处访问整个子项

更新 2

在您的查询中，您正在使用 GROUP BY 中的这些表达式:

MONTH(di_date)
TO_DAYS(di_date)
di_abt

现在的查询将对 1st 的所有值求和, 2nd等等任何月份和年份。

我。 e.对于第一组，它会将 Jan 1st, 2000 中的所有值相加, 然后 Feb 1st, 2000等

然后它将返回MONTH 的任何 随机值, 任何 TO_DAYS 的随机值和 di_abt 的任何随机值来自每个组。

你的条件现在是在一个月之内，所以现在还可以，但是如果你的条件会跨越多个月(更不用说几年)，他们查询会产生意想不到的结果。

您真的要按日期分组吗？