程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 使用use index優化sql查詢的詳細介紹

使用use index優化sql查詢的詳細介紹

編輯:MySQL綜合教程

先看一下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有時候也並不聰明,並非總能做出最優選擇,還是需要我們開發者對它進行“調教”!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved