使用use index优化sql查询

liuming794

　　转自：http://www.cnblogs.com/edwardlost/archive/2010/12/31/1923105.html
　　先看一下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
　　接着看下面的sql：
　　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;
　　这条sql的查询条件显示可能使用的索引有`begintime`和`dg`，但是由于使用了order by begintime排序mysql最后选择使用`begintime`索引，explain的结果为：
　　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;
　　+----+-------------+-------------------+-------+---------------+-----------+---------+------+--------+-------------+

　　|>　　+----+-------------+-------------------+-------+---------------+-----------+---------+------+--------+-------------+
　　|  1 | SIMPLE      | arena_match_index | range | begintime,dg  | begintime | 8       | NULL | 226480 | Using where |
　　+----+-------------+-------------------+-------+---------------+-----------+---------+------+--------+-------------+
　　explain的结果显示使用`begintime`索引要扫描22w条记录，这样的查询性能是非常糟糕的，实际的执行情况也是初次执行（还未有缓存数据时）时需要30秒以上的时间。
　　实际上这个查询使用`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;
　　+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-----------------------------+

　　|>　　+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-----------------------------+
　　|  1 | SIMPLE      | arena_match_index | ref  | dg            | dg   | 7       | const,const |  757 | Using where; Using filesort |
　　+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-----------------------------+
　　explain结果显示使用`dg`联合索引只需要扫描757条数据，性能直接提升了上百倍，实际的执行情况也是几乎立即就返回了查询结果。
　　在最初的查询语句中只要把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;
　　+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-------------+

　　|>　　+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-------------+
　　|  1 | SIMPLE      | arena_match_index | ref  | begintime,dg  | dg   | 7       | const,const |  717 | Using where |
　　+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-------------+
　　通过上面的例子说mysql有时候也并不聪明，并非总能做出最优选择，还是需要我们开发者对它进行“调教”！