應用use index優化sql查詢的具體引見。本站提示廣大學習愛好者：（應用use index優化sql查詢的具體引見）文章只能為提供參考，不一定能成為您想要的結果。以下是應用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
接著看上面的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;
+----+-------------+-------------------+-------+---------------+-----------+---------+------+--------+-------------+
| 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的成果顯示應用`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;
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-----------------------------+
| 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成果顯示應用`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;
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-------------+
| 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有時刻也其實不聰慧，並不是總能做出最優選擇，照樣須要我們開辟者對它停止“調教”！
