Mysql不使用索引超大表

I have next table:

我有下一张桌子:

CREATE TABLE `test` (
 `fingerprint` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
 `country` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
 `loader` int(10) unsigned NOT NULL,
 `date` date NOT NULL,
 `installer` int(10) unsigned DEFAULT NULL,
 `browser` varchar(5) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `version` varchar(5) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `os` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `language` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 PRIMARY KEY (`fingerprint`, `date`),
 KEY `date_1` (`date`),
 KEY `date_2` (`date`,`loader`,`installer`,`country`,`browser`,`os`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Right now it contains 10M records and will increase per 2M records / day.

现在它包含10M记录,每天每2M记录增加一次。

My question, why MySQL use "Using Where" on next query:

我的问题,为什么MySQL在下一个查询中使用“使用位置”:

explain select count(*) from test where date between '2013-08-01' and '2013-08-10'
1   SIMPLE  test    range   date_1,date_2   date_1  3       1601644 Using where; Using index

Update, why next question have type - All and Using where then:

更新,为什么下一个问题有类型 - 全部和使用在哪里:

explain select * from test use key(date_1) where date between '2013-08-01' and '2013-08-10'
1 SIMPLE test ALL date_1 null null null 3648813 Using where

2 个解决方案

#1

It does use the index.

它确实使用索引。

It says so right there: Using where; Using index. The "using where" doesn't mean full scan, it means it's using the WHERE condition you provided.

它就是这么说的:用在哪里;使用索引。 “使用where”并不意味着完全扫描,这意味着它使用您提供的WHERE条件。

The 1601644 number also hints at that: it means it expect to read roughly 1.6M records, not the whole 10M in the table, and it correlates with your ~2M/day estimate.

1601644号码也暗示:它意味着它预计会读取大约160万条记录,而不是表中的整个10M记录,并且它与您的~2M /天估计相关。

In short, it seems to be doing well, it's just a lot of data you will retrieve.

简而言之,它似乎做得很好,它只是你要检索的很多数据。

Still, it's reading the table data too, when it seems the index should be enough. Try changing the count(*) with count(date), so date is the only field mentioned in the whole query. If you get only Using index, then it could be faster.

不过,当看起来索引应该足够时,它也会读取表格数据。尝试使用count(date)更改计数(*),因此date是整个查询中提到的唯一字段。如果只获得使用索引,那么它可能会更快。

#2

Your query is not just "Using where", it is actually "Using where; Using index". This means the index is used to match your WHERE condition and the index is being used to perform lookups of key values. This is the best case scenario, because in fact the table has never been scanned, the query could be processed with the index only.

您的查询不仅仅是“使用位置”,它实际上是“使用位置;使用索引”。这意味着索引用于匹配WHERE条件,索引用于执行键值的查找。这是最好的情况,因为实际上从未扫描过表,只能使用索引处理查询。

Here you can find a full description of the meaning of the output you are looking at.

在这里,您可以找到您正在查看的输出含义的完整描述。

Your second query only shows the "Using where" notice. This means the index is only used to filter rows. The data must be read from the table (no "Using index" notice), because the index does not contain all the row data (you selected all columns, but the chosen index only covers date). If you had a covering index (that covers all columns), this index would probably be used instead.

您的第二个查询仅显示“使用位置”通知。这意味着索引仅用于过滤行。必须从表中读取数据(没有“使用索引”通知),因为索引不包含所有行数据(您选择了所有列,但所选索引仅涵盖日期)。如果您有覆盖索引(涵盖所有列),则可能会使用此索引。

#1