当前位置： 移动技术网 > IT编程>数据库>Mysql > 使用use index优化sql查询的详细介绍

使用use index优化sql查询的详细介绍

2017年12月12日  | 移动技术网IT编程  | 我要评论

埃及魔砖,2011年2月22日,hk23

先看一下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有时候也并不聪明，并非总能做出最优选择，还是需要我们开发者对它进行“调教”！

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网