使用use index优化sql查询的详细介绍

先看一下arena_match_index的表结构，大家注意表的索引结构

复制代码 代码如下:

CREATE TABLE `arena_match_index` (
`tid` int(10) unsigned NOT NULL DEFAULT '0',
`mid` int(10) unsigned NOT NULL DEFAULT '0',
`group` int(10) unsigned NOT NULL DEFAULT '0',
`round` tinyint(3) unsigned NOT NULL DEFAULT '0',
`day` date NOT NULL DEFAULT '0000-00-00',
`begintime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
UNIQUE KEY `tm` (`tid`,`mid`),
KEY `mid` (`mid`),
KEY `begintime` (`begintime`),
KEY `dg` (`day`,`group`),
KEY `td` (`tid`,`day`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

复制代码 代码如下:

SELECT round FROM arena_match_index WHERE `day` = '2010-12-31' AND `group` = 18 AND `begintime` < '2010-12-31 12:14:28' order by begintime LIMIT 1;

复制代码 代码如下:

mysql> explain SELECT round FROM arena_match_index WHERE `day` = '2010-12-31' AND `group` = 18 AND `begintime` < '2010-12-31 12:14:28' order by begintime LIMIT 1;
+----+-------------+-------------------+-------+---------------+-----------+---------+------+--------+-------------+
| id | select_type | table| type | possible_keys | key| key_len | ref | rows | Extra|
+----+-------------+-------------------+-------+---------------+-----------+---------+------+--------+-------------+
| 1 | SIMPLE| arena_match_index | range | begintime,dg |<STRONG> </STRONG>begintime<STRONG> </STRONG>| 8| NULL | 226480 | Using where |
+----+-------------+-------------------+-------+---------------+-----------+---------+------+--------+-------------+

实际上这个查询使用`dg`联合索引的性能更好，因为同一天同一个小组内也就几十场比赛，因此应该优先使用`dg`索引定位到匹配的数据集合再进行排序，那么如何告诉mysql使用指定索引呢？使用use index语句：

复制代码 代码如下:

mysql> explain SELECT round FROM arena_match_index use index (dg) WHERE `day` = '2010-12-31' AND `group` = 18 AND `begintime` < '2010-12-31 12:14:28' order by begintime LIMIT 1;
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-----------------------------+
| id | select_type | table| type | possible_keys | key | key_len | ref| rows | Extra|
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-----------------------------+
| 1 | SIMPLE| arena_match_index | ref | dg| dg | 7| const,const | 757 | Using where; Using filesort |
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-----------------------------+

在最初的查询语句中只要把order by begintime去掉，mysql就会使用`dg`索引了，再次印证了order by会影响mysql的索引选择策略！

复制代码 代码如下:

mysql> explain SELECT round FROM arena_match_index WHERE `day` = '2010-12-31' AND `group` = 18 AND `begintime` < '2010-12-31 12:14:28' LIMIT 1;
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-------------+
| id | select_type | table| type | possible_keys | key | key_len | ref| rows | Extra|
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-------------+
| 1 | SIMPLE| arena_match_index | ref | begintime,dg | dg | 7| const,const | 717 | Using where |
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-------------+