USE INDEX
在你查询语句中表名的后面,添加 USE INDEX 来提供你希望 MySQ 去参考的索引列
表,就可以让 MySQL 不再考虑其他可用的索引。
Eg:SELECT * FROM mytable USE INDEX (mod_time, name) ...

IGNORE INDEX
如果你只是单纯的想让 MySQL 忽略一个或者多个索引,可以使用 IGNORE INDEX 作
为 Hint。
Eg:SELECT * FROM mytale IGNORE INDEX (priority) ...

FORCE INDEX
为强制 MySQL 使用一个特定的索引,可在查询中使用 FORCE INDEX 作为 Hint。
Eg:SELECT * FROM mytable FORCE INDEX (mod_time) ...

FORCE INDEX 通常用来对查询强制使用一个或者多个索引。 MySQL 通常会根据统计信息选择正确的索引,但是当查询优化器选择了错误的索引或者根本没有使用索引的时候,这个提示将非常有用。

IGNORE INDEX 提示会禁止查询优化器使用指定的索引。在具有多个索引的查询时,可以用来指定不需要优化器使用的那个索引,还可以在删除不必要的索引之前在查询中禁止使用该索引。

FORCE INDEX/IGNORE INDEX 的语法:

SELECT *** FROM TABLE [{USE|IGNORE|FORCE} INDEX (key_list)] WHERE ……

下面的例子是使用 IGNORE INDEX 以后,执行计划的变化情况,默认的执行计划是按照主键索引进行扫描,如果我们使用 IGNORE INDEX 忽略主键索引,则会按照全表扫描执行:

mysql> desc select count(*) from test3 where id = 1 \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: test3

type: const

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: const

rows: 1

Extra: Using index

1 row in set (0.00 sec)

mysql> desc select count(*) from test3 ignore index (primary) where id = 1 \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: test3

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 862560

Extra: Using where

1 row in set (0.00 sec)

使用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  | 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;
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-----------------------------+
| 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有时候也并不聪明,并非总能做出最优选择,还是需要我们开发者对它进行“调教”!


评论关闭
IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!