代码之家 › 专栏 › 技术社区 › Ian

可以使用哪些索引来改进此查询?

distinct indexing optimization mysql

Ian · 技术社区 · 16 年前

此查询选择特定日期范围内的所有唯一访问者会话:

select distinct(accessid) from accesslog where date > '2009-09-01'

我有以下字段的索引:

无障碍
日期
其他一些领域

下面是解释如下:

mysql> explain select distinct(accessid) from accesslog where date > '2009-09-01';
+----+-------------+-----------+-------+----------------------+------+---------+------+-------+------------------------------+
| id | select_type | table     | type  | possible_keys        | key  | key_len | ref  | rows  | Extra                        |
+----+-------------+-----------+-------+----------------------+------+---------+------+-------+------------------------------+
|  1 | SIMPLE      | accesslog | range | date,dateurl,dateaff | date | 3       | NULL | 64623 | Using where; Using temporary |
+----+-------------+-----------+-------+----------------------+------+---------+------+-------+------------------------------+


mysql> explain select distinct(accessid) from accesslog;
+----+-------------+-----------+-------+---------------+----------+---------+------+---------+-------------+
| id | select_type | table     | type  | possible_keys | key      | key_len | ref  | rows    | Extra       |
+----+-------------+-----------+-------+---------------+----------+---------+------+---------+-------------+
|  1 | SIMPLE      | accesslog | index | NULL          | accessid | 257     | NULL | 1460253 | Using index |
+----+-------------+-----------+-------+---------------+----------+---------+------+---------+-------------+

为什么带有日期子句的查询不使用accessid索引?

在某些日期范围内,是否有其他索引可用于加快对不同accessid的查询?

编辑-分辨率

减小列宽 accessid 从varchar 255到char 32,查询时间缩短了约75%。

添加一个 date+accessid 索引对查询时间没有影响。

6 回复 | 直到 16 年前

Josh Davis 16 年前

一个索引 (date,accessid) 能够帮助。但是,在调整索引之前,我建议您检查 accessid 列。 EXPLAIN 表示键的长度为257字节,这对于ID列来说似乎很长。你使用的是 VARCHAR(256) 对于 无障碍 ?如果是这样,你不能用更紧凑的类型吗?如果是数字,应该是 INT ( SMALLINT , BIGINT 如果是字母数字标识,可以吗? 真的? 256个字符长吗?如果它的长度是固定的,你不能用吗 CHAR ( CHAR(32) 例如)相反?

Michael 16 年前

你的问题是你的条件是一个range子句(在日期列上)。

由于MySQL不能在范围条件之后使用索引列,因此日期-accessid的多列索引可能对这种情况没有帮助。理论上,在这种情况下,他们应该能够使用它来覆盖计算,但是在MySQL中,这似乎是一个缺点,我从来没有让它在这种情况下成功地使用多列索引。

您可以尝试在(date,accessid)上创建一个索引,希望它可以用来覆盖查询(这样您就不需要访问任何表),但我没有太大的希望。你做不了什么。

编辑:

我的回答是客气的 High Performance MySQL - Second Edition 如果你必须认真开发MySQL的话,这是值得的。

paxdiablo 16 年前

为什么带有日期子句的查询不使用accessid索引?

因为使用日期索引更有效。这是因为它可能会更快地减少搜索空间。

至少有一个DBMS(DB2/Z,我对MySQL不太了解)可以从日期+accessID索引中获益,因为访问ID将在该索引中的日期内排序。DBMS将使用DATE+ACCESSID键有效地使用WHERE子句来减少搜索空间和返回该空间中accessID的不同值。

我不知道MySQL是否那么聪明。我的建议是试试看(这是大多数数据库优化问题的最佳答案)。

James Anderson 16 年前

查询使用“日期”索引,因为这是在WHERE子句中使用的索引。

这是唯一明智的选择,如果它使用了访问ID索引,它将需要读取所有的访问ID行,然后检查它之前的日期,然后才决定它是否是不同的。

如果这是一个很大的表,那么日期和accessid上的复合索引可能会有所帮助。

Jonathan Leffler 16 年前

为什么带有日期子句的查询不使用accessid索引?

因为使用日期索引可以忽略表中的大部分数据。很有可能,该表主要保存历史数据,其中许多数据引用的日期比当前月份的开始日期早得多,因此日期标准是有选择的,通过允许优化器忽略大多数数据,可以减少优化器的工作量。

如果它使用了accessid索引,那么它还必须读取每一行(以及每个索引条目),以查看日期是否符合搜索条件。这意味着读取整个索引和整个表——事实上,在上下文中忽略索引会更好,但我从“如果它使用了accessid索引”开始。

在某些日期范围内,是否有其他索引可用于加快对不同accessid的查询?

根据优化器的复杂程度,对(日期,accessid)的索引可能会改进一些事情。它可以对索引的前一列进行范围搜索,后一列意味着它不需要引用表中的数据来建立accessID——信息在索引中。因此,这可能会将访问索引和表的查询转换为只访问索引的查询,这将减少所需的I/O量,从而提高查询的性能。

如果您有其他条件需要来自其他列的数据,或者您需要返回的不仅仅是唯一的accessID值,那么您最终会读取部分表数据;与扫描整个表相比,这可能仍然是一个胜利。

Jonathan Leffler 16 年前

我没有办法测试它,但我肯定尝试添加一个 同时跨越accessID和日期的索引 .

索引优化,如果经常喜欢炼金术。不同的DBMS行为不同,有时您需要简单地尝试(和失败)各种组合。我不是说这是不可能解释的。在很多情况下都是这样,但在一定程度上是这样的。通常情况下,它只是更快更容易跟随你的直觉。