代码之家 › 专栏 › 技术社区 › Tatu Ulmanen

奇怪的MySQL选择执行时间

indexing mysql

Tatu Ulmanen · 技术社区 · 14 年前

我很难弄清楚为什么类似查询的执行时间彼此相差如此之大。我有一个简单的 SELECT 这样的查询:

SELECT
    `location_id`,
    `datetime`,
    `year`,
    `month`,
    `load`
FROM  load_report_rows
WHERE location_id = '16583'
AND   load_report_id = '1'
AND   year = '2010'

location_id 到“18260”,查询突然需要2.7012秒。中使用的所有三个字段 WHERE 子句被索引,两个查询都返回8760行。这个 EXPLAIN 对于此查询,返回以下信息:

id               1
select_type      SIMPLE
table            load_report_rows
type             index_merge
possible_keys    load_report_id,location_id,year
key              location_id,load_report_id,year
key_len          4,4,4
ref              NULL
rows             3349
extra            using intersect(location_id,load_report_id,year); using where

MySQL query profiler为每个查询提供以下信息:

+--------------------------------+----------+----------+
| Status                         | Query 1  | Query 2  |
+--------------------------------+----------+----------+
| starting                       | 0.000023 | 0.000023 |
| checking query cache for query | 0.000072 | 0.000068 |
| checking permissions           | 0.000010 | 0.000068 |
| opening tables                 | 0.000012 | 0.000012 |
| system lock                    | 0.000005 | 0.000004 |
| table lock                     | 0.000008 | 0.000008 |
| init                           | 0.000050 | 0.000026 |
| optimizing                     | 0.000030 | 0.000014 |
| statistics                     | 0.000461 | 0.001048 |
| preparing                      | 0.000022 | 0.000043 |
| executing                      | 0.000003 | 0.000004 |
| sending data                   | 0.100939 | 2.649942 |
| end                            | 0.000013 | 0.000040 |
| end                            | 0.000004 | 0.000004 |
| query end                      | 0.000004 | 0.000004 |
| freeing items                  | 0.000012 | 0.000013 |
| closing tables                 | 0.000008 | 0.000008 |
| logging slow query             | 0.000002 | 0.000003 |
| cleaning up                    | 0.000006 | 0.000005 |
+--------------------------------+----------+----------+

这个 sending data 第二个查询的stage要长得多。其中包括哪些步骤?以下是相关字段的表结构(如果有帮助):

`id`              int(11)
`load_report_id`  int(11)
`location_id`     int(11)
`datetime`        datetime
`year`            int(4)
`month`           int(2)
`load`            decimal(16,8)

PRIMARY KEY  (`id`)
KEY `load_report_id` (`load_report_id`)
KEY `location_id` (`location_id`)
KEY `year` (`year`)
KEY `month` (`month`)

有什么办法可以导致第二个查询运行得如此缓慢?

4 回复 | 直到 14 年前

Ike Walker 14 年前

我有几个建议。

首先,我将添加一个多列索引来覆盖这3列。这样,您可以扫描单个索引,而不是执行索引合并:

ALTER TABLE load_report_rows
  ADD KEY location_report_year_idx (location_id,load_report_id,year);

新的索引使位置id索引有些多余,因此您可能也要考虑删除它。

SELECT
    `location_id`,
    `datetime`,
    `year`,
    `month`,
    `load`
FROM  load_report_rows
WHERE location_id = 16583
AND   load_report_id = 1
AND   year = 2010

Community CDub 7 年前

sending data 是一种误导性的描述。它实际上包括 二者都 执行查询所花费的时间和通过电报发送结果所花的时间。 source 1 source 2 source 3

由于两个查询都产生大致相同的数据量,所以差异必须在执行阶段。有几种可能性:

location_id 是18260年。尝试按照@Ike Walker的建议添加多列索引。
可能在第二个查询中返回的行在整个磁盘上都是碎片。这将使MySQL花费大量时间等待磁盘寻找下一个位置。尝试 optimizing

Brent Baisley 14 年前

我猜是缓存。您正在导致MySQL对您的值(字符串/数字)进行排序/转换。您正在搜索的所有字段都是int,但是您正在向mysql传递一个要搜索的字符串。去掉你正在搜索的数字周围的引号,看看会发生什么。

Chris Henry 14 年前

这个 sending data state指的是MySQL实际通过网络发送数据的时间。所以速度慢很可能是因为id 18260返回的行比第一个查询多很多。