解释“explain"的结果在mysql中查询 [英] Expain the result of "explain" query in mysql

问题描述

我正在为 mysql 表使用索引.

我的查询是这样的

 EXPLAIN SELECT * FROM `logs` WHERE userId =288 AND dateTime BETWEEN '2010-08-01' AND '2010-08-27'

我对该表日志的字段 userId 进行了索引，和解释查询的结果如下.

id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE 日志 ref userId userId 4 const 49560 使用 where

问题是我的索引是否真的有用?"...

提前致谢

@fastmultiplication

我认为在这两个字段上建立索引可能会增加 mysql 的负载，因为会有很多具有唯一性(用户 ID 和日期时间)的条目.我尝试在 userId_dateTime 上添加索引，结果是

id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE 日志 ref userId_dateTime userId_dateTime 4 const 63455 使用 where

解决方案

您的查询正在使用索引，是的，它们很有用.您可能会发现以下文档页面很有用:

解释输出格式
MySQL 如何使用索引
多列索引

还有:

多列索引 vs 多索引

MySQL 通常会使用返回最少行数的索引.在您的第一个示例中，MySQL 使用 userId 索引将行数缩小到 49560.这意味着 userId 不包含唯一值(如果包含，您不需要日期范围条件).由于 dateTime 列上没有索引，因此它必须扫描每一行以找到符合您的日期范围条件的行.

在您的第二个示例中，您似乎在 userId 和 dateTime 上创建了一个复合(多列)索引.在这种情况下，似乎 MySQL 无法将索引的后半部分用于 BETWEEN 子句—我不知道为什么.可能值得尝试使用两个单独的索引，而不是多列索引.您可能还想尝试将 BETWEEN 替换为:

'2010-08-01' >= AND <= '2010-08-27'

这应该是相同的，但请参阅以下错误报告，这可能会影响您的 MySQL 版本:

优化器在 JOIN 条件下不使用 BETWEEN 的索引

I am using indexing for mysql tables.

My query was like this

 EXPLAIN SELECT * FROM `logs` WHERE userId =288 AND dateTime BETWEEN '2010-08-01' AND '2010-08-27' 

I have indexing on field userId for this table logs, and the result of explain query is like below.

id  select_type     table     type  possible_keys   key     key_len     ref     rows    Extra
1      SIMPLE         logs     ref      userId      userId      4       const   49560   Using where

The question is "My indexing is really useful or not?"...

Thanks in advance

@fastmultiplication

I thought that indexing on both this field might increase load on mysql as there will be lot of entries with unique (userId and dateTime). I have tried adding indexing on both userId_dateTime and the result is

id  select_type     table   type    possible_keys            key        key_len     ref     rows    Extra
1      SIMPLE        logs    ref     userId_dateTime    userId_dateTime     4       const   63455   Using where

解决方案

Your query is using indexes, and yes, they are useful. You might find the following doc pages useful:

EXPLAIN Output Format
How MySQL Uses Indexes
Multiple-Column Indexes

Also:

Multiple column index vs multiple indexes

MySQL will usually use the index that returns the smallest number of rows. In your first example, MySQL is using the userId index to narrow down the number of rows to 49560. That means that userId does not contain unique values (if it did, you wouldn't need the date range condition). As there is no index on the dateTime column, it then has to scan each row to find the ones that meet your date range criteria.

In your second example, you appear to have created a compound (multiple-column) index on userId and dateTime. In this case, it appears as though MySQL is not able to use the latter half of the index for the BETWEEN clause—I'm not sure why. It may be worth trying it with two separate indexes, rather than a multiple-column index. You may also want to try replacing BETWEEN with:

'2010-08-01' >= AND <= '2010-08-27'

This should be identical, but see the following bug report, which may affect your version of MySQL:

Optimizer does not use index for BETWEEN in a JOIN condition

这篇关于解释“explain"的结果在mysql中查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！