Home > database > 使用伪’loose index scan’优化max

使用伪’loose index scan’优化max

September 26th, 2011 Leave a comment Go to comments

有时候我们会遇到以下的应用场景:

SELECT MAX(log_time)

FROM log_table

WHERE log_machine IN ($machines)

CREATE TABLE log_table (

id INT NOT NULL PRIMARY KEY,

log_machine VARCHAR(20) NOT NULL,

log_time DATETIME NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE INDEX ix_log_machine_time ON log_table (log_machine, log_time);

我们建立的索引为:(log_machine,log_time),当我们传入单个machine的时候,速度很快,但是当我们传入多个machines的时候,查询速度会一下子就降下来;

首先我们看看下面的查询:

root@test 10:21:15>explain select max(log_time) from log_table;

+—-+————-+——-+——+—————+——+———+——+——+—–

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+—-+————-+——-+——+—————+——+———+——+——+——————————+

| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |

+—-+————-+——-+——+—————+——+———+——+——+—–

当然我们的索引时建立在log_time上的单列索引,这个时候优化器发现不用扫描所有的叶子节点,而直接到最右叶子节点的尾部就可以得到最大的log_time;

同理当我们在in list中传入单个值的时候索引为 (log_machine, log_time):

root@test 10:16:18>explain SELECT MAX(log_time)

-> FROM log_table

-> WHERE log_machine IN (‘Machine 1’)

-> ;

+—-+————-+——-+——+—————+——+———+——+——+—–

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+—-+————-+——-+——+—————+——+———+——+——+—–

| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |

+—-+————-+——-+——+—————+——+———+——+——+—–

从执行计划中我们中,我们可以看到端倪,优化器和上面一样能够直接找到满足条件的最大log_time,不过这次优化器需要首先在索引中定位到Machine 1,接着在

这一系列前缀值都为machine 1的记录中,在直接在定位到最后到一条记录;

在mysql 5.5以前,mysql对loose index scan并不支持,这就意味着mysql的索引扫描通常需要一个确定的起点和终点,即使查询只需要其中一些不连续的行,

mysql也会扫描起点和终点范围内的所有行;

那么由于不支持loose index scan,现在我们传入的是一个list,mysql不得不把所有满足在list中的machine的记录查询出来,然后在这些记录中得到最大的log_time;

root@test 10:47:10>explain SELECT log_machine, MAX(log_time)

-> FROM log_table

-> WHERE log_machine IN (‘Machine 1′,’Machine 2′,’Machine 3′,’Machine 4’);

+—-+————-+———–+——-+———————+———————+—-

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+—-+————-+———–+——-+———————+———————+—-

| 1 | SIMPLE | log_table | range | ix_log_machine_time | ix_log_machine_time | 62 | NULL | 569160 | Using where; Using index |

+—-+————-+———–+——-+———————+———————+—-

root@test 10:47:15>SELECT log_machine, MAX(log_time)

-> FROM log_table

-> WHERE log_machine IN (‘Machine 1′,’Machine 2′,’Machine 3′,’Machine 4’);

+————-+———————+

| log_machine | MAX(log_time) |

+————-+———————+

| Machine 1 | 2010-05-07 23:59:13 |

1 row in set (0.65 sec)

我们看到有569160 行参加了运算;

这个时候如果我们换一种想法,把每个machine的最大log_time计算出来,然后在计算一次所有machine的中最大的log_time,这样不仅可以利用优化器能够直接得到每个machine的最大log_time的优化特点,而且还可大大减少参与计算的行,这样就可以明显提升性能:

root@test 10:47:17>SELECT log_machine, MAX(log_time)

-> FROM log_table

-> WHERE log_machine IN (‘Machine 1′,’Machine 2′,’Machine 3′,’Machine 4’)

-> group by log_machine;

+————-+———————+

| log_machine | MAX(log_time) |

+————-+———————+

| Machine 1 | 2010-05-07 23:59:13 |

| Machine 2 | 2010-05-07 23:58:42 |

| Machine 3 | 2010-05-07 23:58:41 |

| Machine 4 | 2010-05-07 23:56:29 |

+————-+———————+

4 rows in set (0.00 sec)

root@test 10:51:44>SELECT log_machine, MAX(log_time) max_log_time

-> FROM log_table

-> WHERE log_machine IN (‘Machine 1′,’Machine 2′,’Machine 3′,’Machine 4’)

-> group by log_machine

-> order by max_log_time desc

-> limit 1;

+————-+———————+

| log_machine | max_log_time |

+————-+———————+

| Machine 1 | 2010-05-07 23:59:13 |

+————-+———————+

1 row in set (0.00 sec)

root@test 10:52:21>explain SELECT log_machine, MAX(log_time) max_log_time

-> FROM log_table

-> WHERE log_machine IN (‘Machine 1′,’Machine 2′,’Machine 3′,’Machine 4’)

-> group by log_machine ;

+—-+————-+———–+——-+———————+———————+—-

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+—-+————-+———–+——-+———————+———————+—-

| 1 | SIMPLE | log_table | range | ix_log_machine_time | ix_log_machine_time | 62 | NULL | 18 | Using where; Using index for group-by |

+—-+————-+———–+——-+———————+———————+—-

1 row in set (0.00 sec)

执行时间从0.65s降到了0.00s,这个优化速度是非常明显的;

从执行计划中我们可以清楚看到优化器使用到了using index for group by,这样就可以让优化器使用“伪松散索引扫描”,最终达到优化的目的;

ref:http://explainextended.com/2010/05/08/max-and-min-on-a-composite-index/

Categories: database Tags:
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.