快捷導(dǎo)航

使用use index優(yōu)化sql查詢(xún)的詳細(xì)介紹

更新時(shí)間：2013年06月11日 17:11:14 作者：

本篇文章是對(duì)使用use index優(yōu)化sql查詢(xún)進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下

先看一下arena_match_index的表結(jié)構(gòu)，大家注意表的索引結(jié)構(gòu)

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：

復(fù)制代碼代碼如下:

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的查詢(xún)條件顯示可能使用的索引有`begintime`和`dg`，但是由于使用了order by begintime排序mysql最后選擇使用`begintime`索引，explain的結(jié)果為：

復(fù)制代碼代碼如下:

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 | 
+----+-------------+-------------------+-------+---------------+-----------+---------+------+--------+-------------+

explain的結(jié)果顯示使用`begintime`索引要掃描22w條記錄，這樣的查詢(xún)性能是非常糟糕的，實(shí)際的執(zhí)行情況也是初次執(zhí)行（還未有緩存數(shù)據(jù)時(shí)）時(shí)需要30秒以上的時(shí)間。

實(shí)際上這個(gè)查詢(xún)使用`dg`聯(lián)合索引的性能更好，因?yàn)橥惶焱粋€(gè)小組內(nèi)也就幾十場(chǎng)比賽，因此應(yīng)該優(yōu)先使用`dg`索引定位到匹配的數(shù)據(jù)集合再進(jìn)行排序，那么如何告訴mysql使用指定索引呢？使用use index語(yǔ)句：

復(fù)制代碼代碼如下:

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 | 
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-----------------------------+

explain結(jié)果顯示使用`dg`聯(lián)合索引只需要掃描757條數(shù)據(jù)，性能直接提升了上百倍，實(shí)際的執(zhí)行情況也是幾乎立即就返回了查詢(xún)結(jié)果。

在最初的查詢(xún)語(yǔ)句中只要把order by begintime去掉，mysql就會(huì)使用`dg`索引了，再次印證了order by會(huì)影響mysql的索引選擇策略！

復(fù)制代碼代碼如下:

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 | 
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-------------+

通過(guò)上面的例子說(shuō)mysql有時(shí)候也并不聰明，并非總能做出最優(yōu)選擇，還是需要我們開(kāi)發(fā)者對(duì)它進(jìn)行“調(diào)教”！

您可能感興趣的文章: