《Mysql應(yīng)用使用use index優(yōu)化sql查詢的詳細(xì)介紹》要點(diǎn)：
本文介紹了Mysql應(yīng)用使用use index優(yōu)化sql查詢的詳細(xì)介紹，希望對您有用。如果有疑問，可以聯(lián)系我們。
先看一下arena_match_index的表結(jié)構(gòu),大家注意表的索引結(jié)構(gòu)
MYSQL入門
代碼如下:
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的結(jié)果為：
代碼如下:
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條記錄,這樣的查詢性能是非常糟糕的,實(shí)際的執(zhí)行情況也是初次執(zhí)行(還未有緩存數(shù)據(jù)時(shí))時(shí)需要30秒以上的時(shí)間.
實(shí)際上這個(gè)查詢使用`dg`聯(lián)合索引的性能更好,因?yàn)橥惶焱粋€(gè)小組內(nèi)也就幾十場比賽,因此應(yīng)該優(yōu)先使用`dg`索引定位到匹配的數(shù)據(jù)集合再進(jìn)行排序,那么如何告訴mysql使用指定索引呢?使用use index語句：
MYSQL入門
代碼如下:
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í)行情況也是幾乎立即就返回了查詢結(jié)果.
在最初的查詢語句中只要把order by begintime去掉,mysql就會使用`dg`索引了,再次印證了order by會影響mysql的索引選擇策略!
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 |
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-------------+
通過上面的例子說mysql有時(shí)候也并不聰明,并非總能做出最優(yōu)選擇,還是需要我們開發(fā)者對它進(jìn)行“調(diào)教”!
轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/5325.html
