当前位置:首页 > 开发 > 数据库 > 正文

使用use index优化sql查询

发表于: 2015-04-30   作者:cfyme   来源:转载   浏览次数:
摘要: 转载地址:http://www.cnblogs.com/edwardlost/archive/2010/12/31/1923105.html 先看一下arena_match_index的表结构,大家注意表的索引结构CREATE TABLE `arena_match_index` (  `tid` int(10) unsigned NOT NULL DEFAULT '0', 

转载地址:http://www.cnblogs.com/edwardlost/archive/2010/12/31/1923105.html

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

使用use index优化sql查询

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
sql语句优化 性能不理想的系统中除了一部分是因为应用程序的负载确实超过了服务器的实际处理能力外,
使用mysql缓存 转换不确定性函数:所谓不确定性函数是指函数的返回值不固定如CURDATE(),Now(),Uuid(
<iframe align="top" marginwidth="0" marginheight="0" src="http://www.zealware.com/46860.htm
导读:   优化数据库服务器时,您需要优化个人查询性能。这与优化服务器安装的其他影响硬件和软件
最近我应项目需求,在后台数据统计页面,需要将数据统计分为采集数据统计和非采集数据两个部分。当
From: http://www.cnblogs.com/lovewindy/archive/2005/02/19/105959.html 在《数据库原理》里面,
1、概述 2、数据库设计原则 1)熟悉需求 2)开发符合规范的数据库 3)审核数据库设计 审核组除了要
MSDN: Declare @d Datetime Set @d=getdate() SELECT Top 1* FROM [TGBus_Card].[dbo].[Ka_Card] W
SQL查询优化: 1 MySQL查询路径 MySQL查询路径如下图所示: 查询的生命周期大致包括:从客户端 到服
SQL查询优化: 1 MySQL查询路径 MySQL查询路径如下图所示: 查询的生命周期大致包括:从客户端 到服
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号