MySQL根据大inverval日期查询(MySQL query by date with big inverval)

MySQL根据大inverval日期查询(MySQL query by date with big inverval)

我有2200万条记录的大桌子。 我想执行下一个查询：

select auto_alerts from alerts_stat where endDate > "2012-12-01"

为了提高性能，我为endData字段添加了BTREE索引：

CREATE INDEX endDate_index USING BTREE ON alerts_stat(endDate)

在我开始分析查询执行计划之后：

当我想在现在15到7天之前获取参数时：

explain select alerts_sp from alerts_stat where endDate between CURDATE() - 15 and CURDATE() - 7;

我得到了下一个执行计划来处理2,762,088行。

'1', 'SIMPLE', 'browser_plugin_alerts_stat', 'range', 'endDate_index', 'endDate_index', '4', NULL, '2762088', 'Using where'

当我将时间间隔增加一天时，我收到：

explain select alerts_sp from alerts_stat where endDate between CURDATE() - 15 and CURDATE() - 6;

EXPLAIN表示MySQL计划处理所有22,923,126行。

'1', 'SIMPLE', 'browser_plugin_alerts_stat', 'ALL', 'endDate_index', NULL, NULL, NULL, '22932390', 'Using where'

例如在WHERE过程22,925,642中选择无任何条件。

我可以改进执行计划吗？ 也许我在某处犯了错误，或者是正常的MySQL行为？

I have big table with 22 millions records. I want to execute next query:

select auto_alerts from alerts_stat where endDate > "2012-12-01"

To improve performance I added BTREE index for endData field:

CREATE INDEX endDate_index USING BTREE ON alerts_stat(endDate)

After I start to analyze query execution plan:

When I want to get parameters from 15 to 7 days before now:

explain select alerts_sp from alerts_stat where endDate between CURDATE() - 15 and CURDATE() - 7;

I got next execution plan to process 2,762,088 rows.

'1', 'SIMPLE', 'browser_plugin_alerts_stat', 'range', 'endDate_index', 'endDate_index', '4', NULL, '2762088', 'Using where'

When I increase interval by one day, i received:

explain select alerts_sp from alerts_stat where endDate between CURDATE() - 15 and CURDATE() - 6;

EXPLAIN said MySQL plan to process all 22,923,126 rows.

'1', 'SIMPLE', 'browser_plugin_alerts_stat', 'ALL', 'endDate_index', NULL, NULL, NULL, '22932390', 'Using where'

For example select without any conditions in WHERE process 22,925,642.

May I improve execution plan? Maybe I have mistake somewhere, or is normal MySQL behaivior?

最满意答案

当结果集超过所有行的8-9％时，MySQL会进行全表扫描。 对我来说，看起来你有一天在全表扫描方向上添加了MySQL。 您可以尝试强制索引来查看结果是否更好。

更新：

从我读过的内容来看，MySQL查询优化器往往在这样的边界情况下选择错误，所以它可以很容易地更好地强制索引。 否则，这是简单的查询，我没有更多的优化空间。

也许在这两列上创建覆盖索引并强制使用它们可能会产生最佳结果。

When result set exceeds 8-9% of all rows, MySQL does full table scan. To me, it looks like that one day you add swings MySQL in full table scan direction. You could try forcing index to see if result is better.

UPDATE:

From what I've read, MySQL query optimizer tends to choose wrong in borderline cases like this, so it could easily work better forcing an index. Otherwise, this is simple query, and I don't much more room for optimization.

Maybe creating a Covering index on these two columns and forcing its use could give best results.