HonestyWorks TechNote

–></style>
<!– Google Loader –>
<script src='https://www.google.com/jsapi?key=ABQIAAAAe9qERUJLyhEYpukFWXvB2RSZWwoJ-vdC4a-3RcAkUJ91o_I5WBTN4RvyYSnvQp9WQetHrNNKDCtuDg' type='text/javascript'></script>
<!– Required Ajax APIs –>
<script type='text/javascript'>
google.load("jquery", "1.4.4");
</script>
<!– Syntax Highlighter BEGIN –>
<!– Syntax Highlighter END –>
<!–MultiCOl BEGIN –>
<script type='text/javascript'>
//<![CDATA[
//
// MULTICOL.js
// version 1.1
// Kotaro Imai : HOKYPOKY.(http://hokypoky.info)
// (c) Licensed GNU General Public License.
//
if(typeof info=="undefined"){var info={}}if(typeof info.hokypoky=="undefined"){info.hokypoky={}}info.hokypoky.Multicol=new function(){jQuery.fn.extend({multicol:function(a){this.each(function(){var b=parseInt($(this).css("line-height"));var c=(a.colNum?a.colNum:2);var l=(a.colMargin?a.colMargin:10);var d=parseInt($(this).width());var g=($(this).width()-l*(c-1))/c-0.1;$(this).find("> *:last").css({marginBottom:0}).end().find("img").each(function(){var i=this.height%b;if(i!=0){i=(i<b/2?-i:b-i)}$(this).css({marginBottom:i})}).end().css({width:g}).end();var k=parseInt($(this).height());var j=(parseInt(k)/parseInt(b))%parseInt(c);if(j!=0){k=k+b*(c-j)}$(this).css({height:k+"px",overflow:"hidden"}).wrapInner("<div class='multicolInner'></div>").end();var h=$(this).html();$(this).css({height:k/c+"px",width:d}).html("").end();for(var f=0;f<c;f++){var e=$(h).css({"float":f!=(c-1)?"left":"right",width:g,marginTop:-($(this).height()*f)+"px",marginRight:f!=(c-1)?l+"px":0+"px",overflow:"hidden"});$(this).append(e)}});return this}})};
//]]>
</script>
<!– MultiCol END–>
<!– Applying Multicol BEGIN –>
<script type='text/javascript'>
$(function(){
$("#book_toc").multicol({colNum:2, colMargin: 10});
});
</script>
<!– Applying Multicol END –>
<!– Hatena Star BEGIN –>
<script src='http://s.hatena.com/js/HatenaStar.js' type='text/javascript'></script>
<script type='text/javascript'>
Hatena.Star.Token = '57f63b420669be09b5e31309b9ddb6e0344040a8';
</script>
<!– Hatena Star END –>
<!– hatena_bookmark_anywhere.js BEGIN –>
<script type='text/javascript'>
//<![CDATA[

// どこでもはてブ – Hatena bookmark anywhere
// http://blog.masuidrive.jp/index.php/category/hba/
//
// The MIT License
// Copyright (c) 2008 Yuichiro MASUI <[email protected]>

var hatena_bookmark_anywhere_style; // falseを設定するとCSSでスタイルの指定が可能になる
var hatena_bookmark_anywhere_limit; // 表示件数
var hatena_bookmark_anywhere_collapse = true; // trueにすると、コメントの書いてないブクマを表示しない。指定しない場合は、表示件数を超えた場合のみ表示しない
var hatena_bookmark_anywhere_url; // 表示するURL 未指定の場合、このページ

function __hatena_bookmark_anywhere_receiver(json) {
try {
var html = "";

var escapeHTML = function(str) {
str = str.replace("&","&");
str = str.replace("\"","&quot;");
str = str.replace("'","'");
str = str.replace("<","<");
str = str.replace(">",">");
return str;
}

// http://juce6ox.blogspot.com/2007/11/cssdom.html
// by latchet
var addCSSRule = (document.createStyleSheet)
? (function(sheet){
return function(selector, declaration){
sheet.addRule(selector, declaration);
};
})(document.createStyleSheet())
: (function(sheet){
return function(selector, declaration){
sheet.insertRule(selector + '{' + declaration + '}', sheet.cssRules.length);
};
})((function(e){
e.appendChild(document.createTextNode(''));
(document.getElementsByTagName('head')[0] || (function(h){
document.documentElement.insertBefore(h, this.firstChild);
return h;
})(document.createElement('head'))).appendChild(e);
return e.sheet;
})(document.createElement('style')))

if(hatena_bookmark_anywhere_style!==false) {
addCSSRule("#hatena_bookmark_anywhere", "font-size: 90%; font-family: \"Arial\", sans-serif; color: #000;");
addCSSRule("#hatena_bookmark_anywhere * ", "margin: 0; padding: 0; text-align: left; font-weight: normal; font-family: \"Arial\", sans-serif;");
addCSSRule("#hatena_bookmark_anywhere .hatena_bookmark_anywhere_zero", "background-color:#edf1fd; border-top:1px solid #5279e7; list-style-position: inside; margin:2px 0 0 0;padding: 8px 5px 12px 8px;");
addCSSRule("#hatena_bookmark_anywhere ul", "background-color:#edf1fd; border-top:1px solid #5279e7; list-style-position: inside; margin:2px 0 0 0;padding: 8px 5px 12px 8px;");
addCSSRule("#hatena_bookmark_anywhere ul li", "list-style-type: circle; padding: 1px 0;");
addCSSRule("#hatena_bookmark_anywhere .hatena_bookmark_anywhere_user", "color: #00e; text-decoration: underline; margin: 0 2px;");
addCSSRule("#hatena_bookmark_anywhere .hatena_bookmark_anywhere_tags", "font-size: 90%; color: #66c; margin: 0 4px 0 2px;");
addCSSRule("#hatena_bookmark_anywhere .hatena_bookmark_anywhere_tags a", "text-decoration: none; color: #66c;");
addCSSRule("#hatena_bookmark_anywhere .hatena_bookmark_anywhere_go", "font-size: 90%; color: #66c; text-decoration: none;");
}

if(json==null) {
html += "このエントリーのはてなブックマーク (0) <a class=\"hatena_bookmark_anywhere_go\" href=\"http://b.hatena.ne.jp/entry/"+escapeHTML(hatena_bookmark_anywhere_url)+"\">はてなブックマークのページへ飛ぶ</a><br/>";
html += "<div class=\"hatena_bookmark_anywhere_zero\">";
html += "このページはまだブックマークされていません。";
html += "</div>";
}
else {
if((typeof hatena_bookmark_anywhere_limit)!="number") hatena_bookmark_anywhere_limit = 100;
if((typeof hatena_bookmark_anywhere_collapse)=="undefined" && json.bookmarks.length>hatena_bookmark_anywhere_limit) hatena_bookmark_anywhere_collapse = true;

html += "このエントリーのはてなブックマーク ("+json.count+") <a class=\"hatena_bookmark_anywhere_go\" href=\"http://b.hatena.ne.jp/entry/"+escapeHTML(hatena_bookmark_anywhere_url)+"\">はてなブックマークのページへ飛ぶ</a><br/>";
html += "<ul id=\"bookmarked_user\">";
for(var i=0; i<json.bookmarks.length&&hatena_bookmark_anywhere_limit>0; ++i) {
var bookmark = json.bookmarks[i];
var t = bookmark.timestamp.split(" ")[0].split("/");
var tags = [];
for(var j=0; j<json.bookmarks[i].tags.length; ++j) {
var tag = json.bookmarks[i].tags[j];
tags.push("<a href=\"http://b.hatena.ne.jp/"+bookmark.user+"/"+tag+"\">"+escapeHTML(tag)+"</a>");
}
if(hatena_bookmark_anywhere_collapse!=true || bookmark.comment!='') {
html += "<li><span class=\"__hatena_bookmark_anywhere_timestamp\">"+escapeHTML(t[0])+"年"+escapeHTML(t[1])+"月"+escapeHTML(t[2])+"日</span><img src=\"http://www.hatena.ne.jp/users/"+escapeHTML(bookmark.user.substring(0,2))+"/"+bookmark.user+"/profile_s.gif\" width=\"16\" height=\"16\"><a href=\"http://b.hatena.ne.jp/"+escapeHTML(bookmark.user)+"/"+escapeHTML(t.join(""))+"\" class=\"hatena_bookmark_anywhere_user\">"+escapeHTML(bookmark.user)+"</a><span class=\"hatena_bookmark_anywhere_tags\">"+tags.join(", ")+"</span>"+escapeHTML(bookmark.comment)+"</li>";
hatena_bookmark_anywhere_limit–;
}
}
html += "</ul>";
}

var wrap = document.createElement("div");
wrap.innerHTML = html;
document.getElementById("hatena_bookmark_anywhere").appendChild(wrap);
} catch(e) { }
}

function __hatena_bookmark_anywhere_loade() {
try {
if((typeof document.getElementById("hatena_bookmark_anywhere"))!="undefined") {
var script = document.createElement("script");
script.setAttribute("type","text/javascript");
if((typeof hatena_bookmark_anywhere_url)=="undefined") hatena_bookmark_anywhere_url = location.href.replace(/#.*/,"");
script.setAttribute("src","http://b.hatena.ne.jp/entry/json/?url="+hatena_bookmark_anywhere_url+"&callback=__hatena_bookmark_anywhere_receiver");
document.body.appendChild(script);
}
} catch(e) { }
}

try {
if(window.addEventListener) {
window.addEventListener("load", __hatena_bookmark_anywhere_loade, false);
}
else {
window.attachEvent("onload", __hatena_bookmark_anywhere_loade);
}
} catch(e) { }
//]]>
</script>
<!– hatena_bookmark_anywhere.js END –>
<!– Resize Image BEGIN –>
<script type='text/javascript'>
//<![CDATA[
/*
* Image Resize v0.5.0
* Copyright (c) 2009 Hisanaga Fukuoka
* URL: http://soft.fpso.jp/
*/
function ensure_img_exists()
{
if($('div#header-dummy img').size() == 0)
{
$('div#header-dummy').html("<a href='http://nippondanji.blogspot.com/'><img src='http://dl.dropbox.com/u/5700319/banner2.png' /></a>");
}
}
function set_original_image_size_wrapper()
{
set_original_image_size('div#header-dummy img');
};
function image_resize_wrapper()
{
image_resize('div#header-dummy ','div#header-dummy img',true);
}
function set_original_image_size(target)
{
jQuery(target).each(
function() {
var theImg = jQuery(this);
theImg.attr('src_width', s2n(theImg.width()));
theImg.attr('src_height', s2n(theImg.height()));
}
)
}
function image_resize(std_width_ele,resize_ele,height_resize){
var theDiv = jQuery(std_width_ele);
var w = get_element_width(theDiv);
if (w == 0) return;
jQuery(resize_ele).each(
function() {
var theImg = jQuery(this);
var iw = s2n(theImg.attr('src_width'));
var ih = s2n(theImg.attr('src_height'));
if (height_resize) {
if (iw > w) {
theImg.height(ih * (w / iw)).width(w);
}
else {
theImg.height(ih).width(iw);
}
} else {
if (iw > w) {
theImg.width(w);
}
else {
theImg.width(iw);
}
}
}
)
}
function s2n(val){
var num = parseInt(val,10);
if (isNaN(num)) {
return 0;
} else {
return num;
}
}
function get_element_width(ele){
var w = 0;
w = s2n(ele.width());
if (typeof document.documentElement.style.maxHeight != "undefined") {
w -= s2n(ele.css("padding-left"));
w -= s2n(ele.css("padding-right"));
} else {
w -= s2n(ele.css("padding-left"))*2;
w -= s2n(ele.css("padding-right"))*2;
}
return w;
}
jQuery(document).ready(ensure_img_exists);
jQuery(document).ready(set_original_image_size_wrapper);
jQuery(document).ready(image_resize_wrapper);
jQuery(window).resize(image_resize_wrapper);
//]]>
</script>
<!– Resize Image END–>
<!– OGP BEGIN –>
<meta content='http://nippondanji.blogspot.com/2009/03/mysql_25.html' property='og:url'/>
<meta content='漢(オトコ)のコンピュータ道: なぜMySQLのサブクエリは遅いのか。' property='og:title'/>
<meta content='blog' property='og:type'/>
<meta content='https://lh6.googleusercontent.com/-Om_IligLd5c/TTT9MTGM31I/AAAAAAAAAfw/ABg9hRZifTo/s800/logo.png' property='og:image'/>
<meta content='漢(オトコ)のコンピュータ道' property='og:site_name'/>
<meta content='195509520496175' property='fb:app_id'/>
<meta content='ちょっと硬派なコンピュータフリークのBlogです。' property='og:description'/>
<meta content='[email protected]' property='og:email'/>
<!– OGP END –>
<link href='https://www.blogger.com/dyn-css/authorization.css?targetBlogID=1906500952232920237&zx=d9f997cc-f70b-4ea2-a824-d150bd8d419d' media='none' onload='if(media!='all')media='all'' rel='stylesheet'/><noscript><link href='https://www.blogger.com/dyn-css/authorization.css?targetBlogID=1906500952232920237&zx=d9f997cc-f70b-4ea2-a824-d150bd8d419d' rel='stylesheet'/></noscript>
<meta name='google-adsense-platform-account' content='ca-host-pub-1556223355139109'/>
<meta name='google-adsense-platform-domain' content='blogspot.com'/>

<!– data-ad-client=ca-pub-0385968702055512 –>

</head>
<body>
<div class='navbar no-items section' id='navbar'>
</div>
<!– Facebook JS SDK –>
<div id='fb-root'></div>
<script>
//<![CDATA[
(function(d, s, id) {
var js, fjs = d.getElementsByTagName(s)[0];
if (d.getElementById(id)) return;
js = d.createElement(s); js.id = id;
js.src = '//connect.facebook.net/ja_JP/all.js#xfbml=1&appId=235228249824430';
fjs.parentNode.insertBefore(js, fjs);
}(document, 'script', 'facebook-jssdk'));
-//]]>
</script>
<div id='outer-wrapper'><div id='wrap2'>
<!– skip links for text browsers –>
<span id='skiplinks' style='display:none;'>
<a href='#main'>skip to main </a> |
<a href='#sidebar'>skip to sidebar</a>
</span>
<div id='content-wrapper'>
<div id='crosscol-wrapper' style='text-align:center'>
<div class='crosscol no-items section' id='crosscol'></div>
</div>
<div id='main-wrapper'>
<div class='main section' id='main'><div class='widget HTML' data-version='1' id='HTML13'>
<div class='widget-content'>
<div id='header-dummy'>
<a href="http://nippondanji.blogspot.com/">
<img src="http://3.bp.blogspot.com/-ww3fiX2xUxs/UgrhnbvcM9I/AAAAAAAABrs/7Oun379407A/s1600/banner2.png" />
</a>
</div>
<h4>
ちょっと硬派なコンピュータフリークのBlogです。
</h4>
</div>
<div class='clear'></div>
</div><div class='widget HTML' data-version='1' id='HTML8'>
<div class='widget-content'>
<style type="text/css">
@import url(http://www.google.com/cse/api/branding.css);
</style>
<div class="cse-branding-bottom" style="background-color:#000000;color:#FFFFFF">
<div class="cse-branding-form">
<form action="http://www.google.co.jp/cse" id="cse-search-box">
<div>
<input type="hidden" name="cx" value="partner-pub-0385968702055512:z3sexm-qk0l" />
<input type="hidden" name="ie" value="UTF-8" />
<input type="text" name="q" size="55" />
<input type="submit" name="sa" value="検索" />
</div>
</form>
</div>
<div class="cse-branding-logo">
<img src="http://www.google.com/images/poweredby_transparent/poweredby_000000.gif" alt="Google" />
</div>
<div class="cse-branding-text">
カスタム検索
</div>
</div>
</div>
<div class='clear'></div>
</div><div class='widget Blog' data-version='1' id='Blog1'>
<div class='blog-posts hfeed'>
<!–Can't find substitution for tag [defaultAdStart]–>

<div class="date-outer">

<h2 class='date-header'><span>2009-03-25</span></h2>

<div class="date-posts">

<div class='post-outer'>
<div class='post hentry uncustomized-post-template'>
<a name='826894812711775842'></a>
<h3 class='post-title entry-title'>
<a href='http://nippondanji.blogspot.com/2009/03/mysql_25.html' style='display:none;' title='permanent link'></a>
<a href='http://nippondanji.blogspot.com/2009/03/mysql_25.html'>なぜMySQLのサブクエリは遅いのか。</a>
</h3>
<div class='post-header-line-1'></div>
<div class='post-body entry-content'>
よくMySQLはサブクエリが弱いと言われるが、これは本当だろうか？半分は本当で半分は嘘である。MySQLのサブクエリだってなんでもかんでも遅いわけではない。落とし穴をしっかり避け、使いどころを間違えなければサブクエリも高速に実行できるのである。今日はMySQLがどんな風にサブクエリを実行し、どのような場合に遅いのかということについて説明しよう。<br />
<br />
EXPLAINで実行計画を調べた際に、select_typeにはクエリの種類が表示されるのだが、代表的なサブクエリには次の3つのパターンがある。<a name='more'></a><br />
<ol><li>SUBQUERY</li>
<li>DEPENDENT SUBQUERY</li>
<li>DERIVED</li>
</ol><br />
結論から言おう。<span style="color: red; font-weight: bold;">遅いのは2番目、DEPENDENT SUBQUERYである。</span>DEPENDENT SUBQUERYとはいわゆる相関サブクエリに相当するもので、サブクエリにおいて外部クエリのカラムを参照しているサブクエリのことである。そして、MySQLのオプティマイザはたびたび相関関係のないもの、つまり本来はSUBQUERYと分類されるべきものをDEPENDENT SUBQUERYであると判断してしまう。そのため、多くの場合においてサブクエリが遅くなってしまうのである。<br />
<br />
MySQLにおいてDEPENDENT SUBQUERYが何故遅いか？それはクエリの評価方法にある。<br />
<br />
現時点でのMySQL（バージョン5.1）では、サブクエリはまず外部クエリの条件から評価される。そして、外部クエリの条件に合致する行が見つかると、その行がサブクエリの条件に合致するかどうかが評価されるわけである。即ち、サブクエリにおいてフェッチしなければいけない行数が平均N行、外部クエリでフェッチされる行数がM行のとき、サブクエリにおいてM×N行の評価が行われることになる。これは膨大な計算量である。<br />
<br />
以下に具体例を挙げよう。<br />
<br />
<pre class="sql" name="code">mysql> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND Code IN
-> (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
+—-+——————–+———+——+—————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+——————–+———+——+—————+——+———+——+——+————-+
| 1 | PRIMARY | Country | ALL | NULL | NULL | NULL | NULL | 239 | Using where |
| 2 | DEPENDENT SUBQUERY | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using where |
+—-+——————–+———+——+—————+——+———+——+——+————-+
2 rows in set (0.00 sec)
</pre><br />
ここで利用しているテーブルは、<a href="http://dev.mysql.com/doc/#sampledb">MySQL公式のサンプルデータベース</a>であるworldデータベースのCountryおよびCityテーブルである。このクエリは「人口が特定の都市に半分以上が密集しているアジアの国」を探し出す。この実行計画が示すのは、<br />
<ul><li>まず外部クエリが評価され、CountryテーブルからContinent = 'Asia'にマッチする行がフェッチされる。</li>
<li>Countryテーブルからフェッチされた各行に対してサブクエリ(SELECT CountryCode FROM City…)が評価され、人口が国全体の半数以上密集している都市が存在するかどうかを調べる。</li>
</ul>CountryテーブルにはContinent = 'Asia'にマッチする行が51行あるので、Cityテーブルは51×4079=208,029回の評価が行われることになる。ちなみに、このクエリの実行には1秒程度かかってしまう。<br />
<br />
<pre class="sql" name="code">mysql> SELECT * FROM Country WHERE Continent = 'Asia' AND Coode IN
-> (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
+——+———–+———–+—————-+————-+———–+————+—————-+———-+———-+—————————————+—————————————-+—————————-+———+——-+
| Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 |
+——+———–+———–+—————-+————-+———–+————+—————-+———-+———-+—————————————+—————————————-+—————————-+———+——-+
| MAC | Macao | Asia | Eastern Asia | 18.00 | NULL | 473000 | 81.6 | 5749.00 | 5940.00 | Macau/Aomen | Special Administrative Region of China | Jiang Zemin | 2454 | MO |
| QAT | Qatar | Asia | Middle East | 11000.00 | 1971 | 599000 | 72.4 | 9472.00 | 8920.00 | Qatar | Monarchy | Hamad ibn Khalifa al-Thani | 2973 | QA |
| SGP | Singapore | Asia | Southeast Asia | 618.00 | 1965 | 3567000 | 80.1 | 86503.00 | 96318.00 | Singapore/Singapura/Xinjiapo/Singapur | Republic | Sellapan Rama Nathan | 3208 | SG |
+——+———–+———–+—————-+————-+———–+————+—————-+———-+———-+—————————————+—————————————-+—————————-+———+——-+
3 rows in set (1.08 sec)
</pre><br />
このようなDEPENDENT SUBQUERYが常に遅いかというと、そういうわけでもない。サブクエリが評価される際にインデックスを使用することが出来ればこのサブクエリの性能は改善される。次のように。<br />
<br />
<pre class="sql" name="code">mysql> ALTER TABLE City ADD INDEX (CountryCode);
Query OK, 4079 rows affected (0.30 sec)
Records: 4079 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM Country WHERE Continent = 'Asia' AND Coode IN
-> (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
+——+———–+———–+—————-+————-+———–+————+—————-+———-+———-+—————————————+—————————————-+—————————-+———+——-+
| Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 |
+——+———–+———–+—————-+————-+———–+————+—————-+———-+———-+—————————————+—————————————-+—————————-+———+——-+
| MAC | Macao | Asia | Eastern Asia | 18.00 | NULL | 473000 | 81.6 | 5749.00 | 5940.00 | Macau/Aomen | Special Administrative Region of China | Jiang Zemin | 2454 | MO |
| QAT | Qatar | Asia | Middle East | 11000.00 | 1971 | 599000 | 72.4 | 9472.00 | 8920.00 | Qatar | Monarchy | Hamad ibn Khalifa al-Thani | 2973 | QA |
| SGP | Singapore | Asia | Southeast Asia | 618.00 | 1965 | 3567000 | 80.1 | 86503.00 | 96318.00 | Singapore/Singapura/Xinjiapo/Singapur | Republic | Sellapan Rama Nathan | 3208 | SG |
+——+———–+———–+—————-+————-+———–+————+—————-+———-+———-+—————————————+—————————————-+—————————-+———+——-+
3 rows in set (0.02 sec)
</pre><br />
インデックスが使われている様子は、EXPLAINでも見て取れる。<br />
<br />
<pre class="sql" name="code">mysql> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND Coode IN
-> (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
+—-+——————–+———+——+—————+————-+———+——————–+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+——————–+———+——+—————+————-+———+——————–+——+————-+
| 1 | PRIMARY | Country | ALL | NULL | NULL | NULL | NULL | 239 | Using where |
| 2 | DEPENDENT SUBQUERY | City | ref | CountryCode | CountryCode | 3 | world.Country.Code | 18 | Using where |
+—-+——————–+———+——+—————+————-+———+——————–+——+————-+
2 rows in set (0.01 sec)
</pre><br />
<span style="color: #ff6600; font-weight: bold;">なぜインデックスをつけるのがCountryCodeカラムなの？</span>と思われるかも知れない。それ以前に、そもそもこのサブクエリには相関関係などないように見える。<span style="color: #ff6600; font-weight: bold;">なぜ相関関係がないのにDEPENDENT SUBQUERYになるのだろうか？</span>それは、MySQLがこのクエリを次のように書き換えて実行するからだ。<br />
<br />
<pre class="sql" name="code">mysql> SELECT * FROM Country WHERE Continent = 'Asia' AND EXISTS
-> (SELECT 1 FROM City WHERE Population > Country.Population / 2 AND CountryCode = Country.Code);
</pre><br />
<span style="color: #ff6600; font-weight: bold;">MySQLは内部的にINを直接処理することができないので、EXISTSに変換することでSQL的には相関のないサブクエリも相関サブクエリになってしまうのである。</span>これがまさにMySQLのサブクエリが遅い！と言われている原因だろう。<br />
<br />
この例では外部クエリのWHERE句の条件にマッチする行数が51行しかないので高速化が可能だが、マッチする行数が多い場合、たとえば外部クエリのWHERE句にサブクエリ以外の検索条件がない場合にはテーブルスキャンが発生してしまうことになり、外部クエリで用いられているテーブルが大きい場合には時間が掛かってしまうのである。従って、サブクエリが遅い！！と感じたときにはまず意図せずDEPENDENT SUBQUERYとなっていないかどうか、外部クエリからフェッチされる行数が膨大でないかどうか、サブクエリでインデックスが利用されているかどうかをチェックすると良い。サブクエリでインデックスをつけるべきカラムは、サブクエリが結果を返すカラム、上記の例で言えばCountryCodeである。さらに、サブクエリ内のWHERE句で指定されているカラムであるPopulationと複合インデックス(CountryCode,Population)を作成すればこのクエリはもっと高速になるだろう。<br />
<br />
冒頭で挙げた3つのパターンのうち、DEPENDENT SUBQUERY以外のサブクエリはとても高速である。EXPLAINでSUBQUERYと表示されるパターンについて見てみよう。<br />
<br />
<pre class="sql" name="code">mysql> EXPLAIN SELECT * FROM Country WHERE Population > ANY (SELECT Population FROM City);
+—-+————-+———+——+—————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———+——+—————+——+———+——+——+————-+
| 1 | PRIMARY | Country | ALL | NULL | NULL | NULL | NULL | 239 | Using where |
| 2 | SUBQUERY | City | ALL | NULL | NULL | NULL | NULL | 4079 | |
+—-+————-+———+——+—————+——+———+——+——+————-+
2 rows in set (0.00 sec)
</pre><br />
この例では敢えてインデックスが利用されないようなクエリを選んでいる。それでもこのクエリはとても速い。（実行時間は0.01秒とか）なぜならSUBQUERYの部分は一度だけしか評価されないからである。ホントか？と思う人はFLUSH STATUSをしてから上記のクエリを実行し、SHOW STATUS LIKE 'Handler%'でストレージエンジンへのアクセス回数を見てみよう。<br />
<br />
ちなみに、このクエリをMySQLは内部的に次のように書き換えて実行する。<br />
<br />
<pre class="sql" name="code">mysql> SELECT * FROM Country WHERE Population > (SELECT MAX(Population) FROM City);
</pre><br />
どの行（ANY）より大きいということは最大値（MAX）より大きいということになるので、上記のように書き換えても結果は同じになるというわけである。もちろんこのクエリではインデックスがあればさらに高速になる。インデックスをつけるべきカラムはもちろんCity.Populationである。その場合、EXPLAINの表示は次のようになる。<br />
<br />
<pre class="sql" name="code">mysql> EXPLAIN SELECT * FROM Country WHERE Population < ANY (SELECT Population FROM City);
+—-+————-+———+——+—————+——+———+——+——+——————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———+——+—————+——+———+——+——+——————————+
| 1 | PRIMARY | Country | ALL | NULL | NULL | NULL | NULL | 239 | Using where |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+—-+————-+———+——+—————+——+———+——+——+——————————+
2 rows in set (0.00 sec)
</pre><br />
Extraフィールドに面白い表示がされている。Select tables optimized awayは、テーブルがMyISAMである場合にMAX()/MIN()によってサブクエリの最適化が行われた事を意味するメッセージである。これが現れたらサブクエリは最強に最適化されまくってるので、これ以上手の入れようがないということである。（外部クエリは最適化の余地があるかも知れないが。） さて、最後に見るのはDERIVEDである。DERIVEDはSQL標準では「Subquery in the FROM clause」と呼ばれる。そのままやんけ！と叫びたくなる呼び名であるが、ここは気にせず話を進めよう。DERIVEDが利用されるのは例えば次のようなクエリである。<br />
<br />
<pre class="sql" name="code">mysql> EXPLAIN SELECT AVG(p) FROM (SELECT SUM(Population) p FROM Country GROUP BY Continent) CountryPop;
+—-+————-+————+——+—————+——+———+——+——+———————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——+—————+——+———+——+——+———————————+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 7 | |
| 2 | DERIVED | Country | ALL | NULL | NULL | NULL | NULL | 239 | Using temporary; Using filesort |
+—-+————-+————+——+—————+——+———+——+——+———————————+
2 rows in set (0.01 sec)
</pre><br />
id=1の項目では、テーブル名が<derived2>となっているが、id=2のテーブルからDerive（由来）するものであるということを示す。DERIVEDでは、サブクエリは前もって実行されてその結果がテンポラリテーブルに格納される。外部クエリはテンポラリテーブルを使ってクエリを実行することになる。DERIVEDで注意しないといけないのは、テンポラリテーブルのサイズが大きくなってしまうような場合である。MySQLのテンポラリテーブルは最初MEMORYストレージエンジンを利用して作成されるが、テーブルのサイズがmax_heap_table_sizeまたはtmp_table_sizeを超えてしまうとMyISAMに変換される。DERIVEDを利用する場合には、テンポラリテーブルがメモリ内に収まるようにするというのが一つの鍵である。もしテンポラリテーブルが大きくなってしまう場合は、max_heap_table_sizeおよびtmp_table_sizeを増やすといいだろう。 どうにもこうにもメモリに収まりきらないぐらい大きなテンポラリテーブルが必要な場合には、BIG_TABLESシステム変数を使うと良い。テンポラリテーブルが大きくなってしまった場合、MEMORYからMyISAMへの変換作業自体に時間が掛かってしまうことが問題になる場合がある。そんな時は最初からMyISAMを使えば良い。SET BIG_TABLES=1を設定すると、テンポラリテーブルは最初からMyISAMを利用して作成される。僅かではあるが無駄な変換が行われなくなるので効率化が期待出来る。 <span style="font-size: 130%;"><span style="font-weight: bold;">まとめ。</span></span> というわけでMySQLによるサブクエリの処理について見てきたが、きちんと気をつけて使えばサブクエリも高速に実行される。もちろんJOINに書き換えた方が速いのは言うまでもないが、SQL文のメンテナンスし易さなどを考えるとサブクエリで処理を書きたい！という人も居るのではないだろうか。そんな方は次の事に気をつけてサブクエリを使って頂きたい。<br />
<ul><li>サブクエリの種類</li>
<li>外部クエリとサブクエリの評価の順序</li>
<li>外部クエリにおいてフェッチされる行数<br />
</li>
<li>サブクエリで利用されるインデックス</li>
<li>テンポラリテーブルのサイズ</li>
</ul>実はMySQLのEXPLAINが表示するサブクエリにはもう一つ種類がある。それはUNCACHEABLE SUBQUERYというもので、サブクエリ内でRAND()などが利用されている時に表示される。UNCACHEABLE SUBQUERYはDEPENDENT SUBQUERYよりも重い。サブクエリ内でRAND()などを利用しないように心がけよう。もしUNCACHEABLE SUBQUERYを見かけたらクエリを書き換えるべし。 <a href="http://forge.mysql.com/wiki/New_Optimizer_Features_in_MySQL_6.0">MySQL 6.0では、サブクエリの最適化が加わる予定である。</a>相関のないIN (SELECT…)がDEPENDENT SUBQUERYに書き換えられるということがなくなるようにするMaterialization最適化、DEPENDENT SUBQUERYの実行速度を速くするsemi-join最適化手法や、DERIVEDにおいてテンポラリテーブルを用いる必要がないFROM Flattening最適化手法が盛り込まれる予定である。従ってMySQL 6.0では「MySQLのサブクエリは遅い！」と言われることはなくなるだろう。もうすぐリリースされる6.0.10αではFROM Flattening以外の最適化が使えるので、リリースされた暁にはブログで紹介したいと思う。<br />
<br />
<span style="color:red; font-style:bold">追記 2010/02/04：MySQL 6.0の開発は中止され、MySQLのバージョンは5.4、5.5、5.6…と続くようになりました。<a href="http://nippondanji.blogspot.com/2009/12/mysql-55.html">詳細はこちらのエントリで！</a></span>
<div style='clear: both;'></div>
</div>
<div class='post-footer'>
<div class='post-footer-line post-footer-line-1'>
<span class='post-author vcard'>
投稿者
<span class='fn'>Mikiya Okuno</span>
</span>
<span class='post-timestamp'>
時刻:
<a class='timestamp-link' href='http://nippondanji.blogspot.com/2009/03/mysql_25.html' rel='bookmark' title='permanent link'><abbr class='published' title='2009-03-25T08:37:00+09:00'>8:37</abbr></a>
</span>
<span class='reaction-buttons'>
</span>
<span class='star-ratings'>
</span>
<span class='post-comment-link'>
</span>
<span class='post-backlinks post-comment-link'>
<div style='vertical-align: middle; padding: 1px; float: right;'>
<a class='twitter-share-button' data-count='horizontal' data-url='http://nippondanji.blogspot.com/2009/03/mysql_25.html' data-via='nippondanji' href='http://twitter.com/share'>Tweet</a><script src='http://platform.twitter.com/widgets.js' type='text/javascript'></script>
</div>
<div style='vertical-align: middle; padding: 1px; float: right;'>
<a class='hatena-bookmark-button' data-hatena-bookmark-layout='standard' href='http://b.hatena.ne.jp/entry/http://nippondanji.blogspot.com/2009/03/mysql_25.html' title='このエントリーをはてなブックマークに追加'><img alt='このエントリーをはてなブックマークに追加' height='20' src='http://b.st-hatena.com/images/entry-button/button-only.gif' style='border: none;' width='20'/></a><script async='async' charset='utf-8' src='http://b.st-hatena.com/js/bookmark_button.js' type='text/javascript'></script>
</div>
<div style='vertical-align: middle; padding: 1px; float: right;'>
<iframe allowTransparency='true' frameborder='0' scrolling='no' src='//www.facebook.com/plugins/like.php?href=http://nippondanji.blogspot.com/2009/03/mysql_25.html%2F&send=false&layout=button_count&width=200&show_faces=false&action=like&colorscheme=light&font&height=21&appId=235228249824430' style='border:none; overflow:hidden; width:108px; height:21px;'></iframe>
</div>
</span>
<span class='post-icons'>
</span>
</div>
<div class='post-footer-line post-footer-line-2'>
<span class='post-labels'>
ラベル:
<a href='http://nippondanji.blogspot.com/search/label/mysql' rel='tag'>mysql</a>,
<a href='http://nippondanji.blogspot.com/search/label/performance%20tuning' rel='tag'>performance tuning</a>,
<a href='http://nippondanji.blogspot.com/search/label/sql' rel='tag'>sql</a>,
<a href='http://nippondanji.blogspot.com/search/label/subquery' rel='tag'>subquery</a>
</span>
</div>
<div class='post-footer-line post-footer-line-3'>
<span class='post-location'>
</span>
</div>
</div>
</div>
<div class='comments' id='comments'>
<a name='comments'></a>
<h4>
0
コメント:

</h4>
<dl id='comments-block'>
</dl>
<p class='comment-footer'>
<div class='comment-form'>
<a name='comment-form'></a>
<h4 id='comment-post-message'>コメントを投稿</h4>
<p>
</p>
<a href='https://www.blogger.com/comment/frame/1906500952232920237?po=826894812711775842&hl=ja' id='comment-editor-src'></a>
<iframe allowtransparency='true' class='blogger-iframe-colorize blogger-comment-from-post' frameborder='0' height='410px' id='comment-editor' name='comment-editor' src='' width='100%'></iframe>
<script src='https://www.blogger.com/static/v1/jsbin/976584016-comment_from_post_iframe.js' type='text/javascript'></script>
<script type='text/javascript'>
BLOG_CMT_createIframe('https://www.blogger.com/rpc_relay.html');
</script>
</div>
</p>
<div id='backlinks-container'>
<div id='Blog1_backlinks-container'>
</div>
</div>
</div>
</div>
<!–Can't find substitution for tag [defaultAdEnd]–>
<div class='inline-ad'>
</div>
<!–Can't find substitution for tag [adStart]–>

</div></div>