使用use index优化sql查询的详细介绍
2022-11-12 09:30:30
内容摘要
这篇文章主要为大家详细介绍了使用use index优化sql查询的详细介绍,具有一定的参考价值,可以用来参考一下。
对此感兴趣的朋友,看看idc笔记做的技术笔记!
先看一下arena_match_
文章正文
这篇文章主要为大家详细介绍了使用use index优化sql查询的详细介绍,具有一定的参考价值,可以用来参考一下。
对此感兴趣的朋友,看看idc笔记做的技术笔记!
先看一下arena_match_index的表结构,大家注意表的索引结构
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <code>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 </code> |
代码如下:
1 2 | <code>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; </code> |
代码如下:
1 2 3 4 5 6 7 | <code>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 | +----+-------------+-------------------+-------+---------------+-----------+---------+------+--------+-------------+ </code> |
实际上这个查询使用`dg`联合索引的性能更好,因为同一天同一个小组内也就几十场比赛,因此应该优先使用`dg`索引定位到匹配的数据集合再进行排序,那么如何告诉mysql使用指定索引呢?使用use index语句:
代码如下:
1 2 3 4 5 6 7 | <code>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 | +----+-------------+-------------------+------+---------------+------+---------+-------------+------+-----------------------------+ </code> |
在最初的查询语句中只要把order by begintime去掉,mysql就会使用`dg`索引了,再次印证了order by会影响mysql的索引选择策略!
代码如下:
1 2 3 4 5 6 7 | <code>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 | +----+-------------+-------------------+------+---------------+------+---------+-------------+------+-------------+ </code> |
注:关于使用use index优化sql查询的详细介绍的内容就先介绍到这里,更多相关文章的可以留意
代码注释