MySQL分组查询Group By实现原理详解
<p style="color:rgb(51,51,51); font-size:14px; margin-top:0px; margin-bottom:0px; padding-top:5px; padding-bottom:5px; font-family:tahoma,arial,宋体; line-height:25.2000007629395px"> <p style="color:rgb(51,宋体; line-height:25.2000007629395px"> 在<a target="_blank" href="http://lib.csdn.net/base/mysql" rel="nofollow" class="replace_word" title="MySQL知识库" style="color:rgb(223,52,52); text-decoration:none; font-weight:bold">MySQL?中,GROUP BY 的实现同样有多种(三种)方式,其中有两种方式会利用现有的索引信息来完成 GROUP BY,另外一种为完全无法使用索引的场景下使用。下面我们分别针对这三种实现方式做一个分析。 <p style="color:rgb(51,宋体; line-height:25.2000007629395px"> 1、使用松散(Loose)索引扫描实现 GROUP BY <p style="color:rgb(51,宋体; line-height:25.2000007629395px"> 何谓松散索引扫描实现 GROUP BY 呢?实际上就是当 MySQL 完全利用索引扫描来实现 GROUP BY 的时候,并不需要扫描所有满足条件的索引键即可完成操作得出结果。 <p style="color:rgb(51,宋体; line-height:25.2000007629395px"> 下面我们通过一个示例来描述松散索引扫描实现 GROUP BY,在示例之前我们需要首先调整一下 group_message 表的索引,将 gmt_create 字段添加到 group_id 和 user_id 字段的索引中: <div class="jb51code" style="color:rgb(51,51); font-size:14px; margin:0px; padding:0px; line-height:25.2000007629395px; width:660px; overflow:hidden; clear:both; font-family:tahoma,宋体"> <div style="margin:0px; padding:0px; line-height:25.2000007629395px"> <div id="highlighter_455865" class="syntaxhighlighter sql" style="padding:0px; width:644px; line-height:25.2000007629395px; margin:1em 0px!important; position:relative!important; overflow:auto!important"> <div class="toolbar" style="line-height:13.1999998092651px; margin:0px!important; padding:0px!important; border:none!important; bottom:auto!important; float:none!important; height:11px!important; left:auto!important; outline:0px!important; overflow:visible!important; position:absolute!important; right:1px!important; top:1px!important; vertical-align:baseline!important; width:11px!important; font-family:Consolas,'Bitstream Vera Sans Mono','Courier New',Courier,monospace!important; z-index:10!important; color:white!important; background-color:rgb(108,226,108)!important"> <span style="line-height:21.6000003814697px; font-size:12px"><a target="_blank" href="http://www.52php.cn/article/85359.htm#" rel="nofollow" class="toolbar_item command_help help" style="color:rgb(0,102,153); text-decoration:none; font-size:14px; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; margin:0px!important; outline:0px!important; overflow:visible!important; padding:1px 0px 0px!important; position:static!important; right:auto!important; text-align:center!important; top:auto!important; vertical-align:baseline!important; width:auto!important; display:block!important">? |
为什么松散索引扫描的效率会很高?
2.使用紧凑(Tight)索引扫描实现 GROUP BY
3.使用临时表实现 GROUP BY
EXPLAIN
1
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!