“MySQL” カテゴリのアーカイブ


レプリケーションの設定をしているDBがいつの間にかバイナリログによってディスク容量の空きが無くなっていた・・・
想像以上に早くディスク容量を消費する・・・
以下、バイナリログの削除方法と自動削除の設定方法。

http://wiki.bit-hive.com/tomizoo/pg/MySQL%20%A5%D0%A5%A4%A5%CA%A5%EA%A5%ED%A5%B0%A4%CE%BA%EF%BD%FC

Comments [mysql] MySQLのバイナリログ削除 はコメントを受け付けていません


mysqlのサブクエリに問題があることはなんとなく見聞きしていたが今日はこれについて深く追求せざるを得ない機会があったので調べてみました。
自分なりに理解したので簡単にまとめてみるが、以下のブログの記事が素晴らしいので詳しくはそちらもあわせて読むと良いでしょう。

漢(オトコ)のコンピュータ道: なぜMySQLのサブクエリは遅いのか。</title>
<style id='page-skin-1' type='text/css'><!–
/*
* Tekka
* style modified from glish: http://www.glish.com/css/
*
* Updated by Blogger Team
*/
/* Variable definitions
<Variable name="mainBgColor" description="Page Background Color"
type="color" default="#f5f5f5">
<Variable name="mainTextColor" description="Text Color"
type="color" default="#000000">
<Variable name="dateHeaderColor" description="Date Header Color"
type="color" default="#ffffff">
<Variable name="dateHeaderBgColor" description="Date Header Background Color"
type="color" default="#909090">
<Variable name="mainLinkColor" description="Link Color"
type="color" default="#0000ff">
<Variable name="mainVisitedLinkColor" description="Visited Link Color"
type="color" default="#800080">
<Variable name="sidebarBgColor" description="Sidebar Background Color"
type="color" default="#ffffff">
<Variable name="sidebarTextColor" description="Sidebar Text Color"
type="color" default="#000000">
<Variable name="sidebarTitleColor" description="Sidebar Title Color"
type="color" default="#000000">
<Variable name="sidebarLinkColor" description="Sidebar Link Color"
type="color" default="#0000ff">
<Variable name="sidebarVisitedLinkColor" description="Sidebar Visited Link Color"
type="color" default="#800080">
<Variable name="bodyFont" description="Text Font"
type="font" default="normal normal 100% 'Trebuchet MS', verdana, sans-serif">
<Variable name="startSide" description="Start side in blog language"
type="automatic" default="left">
<Variable name="endSide" description="End side in blog language"
type="automatic" default="right">
*/
body {
margin-top:0px;
margin-right:0px;
margin-bottom:0px;
margin-left:0px;
font-size: small;
background-color: #f5f5f5;
color: #000000;
}
#content-wrapper {
margin-top: 0px;
margin-right: 0px;
margin-bottom: 0;
margin-left: 5px;
}
h1 {
font-size:350%;
padding-left:15px;
text-transform:uppercase;
}
h1 a, h1 a:link, h1 a:visited {
text-decoration:none;
color: #000000;
}
.description {
text-align: center;
padding-top:10px;
}
h2.date-header {
border-bottom:none;
font-size: medium;
text-align:left;
margin-top:20px;
margin-bottom:14px;
padding:1px;
padding-left:17px;
color: #ffffff;
background-color: #707070;
font-weight: normal;
font-family: Trebuchet MS, Verdana, Sans-serif;
}
.post h3 {
font-size: 200%;
font-weight:bold;
text-shadow: 1px 1px 0px #fff;
}
.post h3:first-letter {
}
.post h3 a,
.post h3 a:visited {
color: #000000;
}
h4 {
font-size: medium;
color: #000000;
text-shadow: 1px 1px 0px #fff;
}
.post h4:first-letter {
color: #0B2660;
font-size: 150%;
padding: 0px 0px 0 0;
}
.post {
padding-left:20px;
margin-bottom:20px;
text-indent:10px;
padding-right:20px;
line-height:22px;
}
.post-footer {
margin-bottom:15px;
margin-left:0px;
font-size: 87%;
}
#comments {
margin-left: 20px;
}
.feed-links {
clear: both;
line-height: 2.5em;
margin-left: 20px;
}
#blog-pager-newer-link {
float: left;
margin-left: 20px;
}
#blog-pager-older-link {
float: right;
}
#blog-pager {
text-align: center;
}
#main {
float:left;
padding-bottom:20px;
padding:0px;
width:72%;
font: normal normal 100% 'Trebuchet MS', verdana, sans-serif;
word-wrap: break-word; /* fix for long text breaking sidebar float in IE */
overflow: hidden; /* fix for long non-text content breaking IE sidebar float */
}
.sidebar h2 {
font-weight:bold;
font-size: 150%;
color: #000000;
}
a {
text-decoration:none
}
a:hover {
border-bottom:dashed 1px red;
}
a img {
border: 0;
}
a {
color: #0000ff;
}
a:hover,
a:visited {
color: #800080;
}
blockquote {
padding: 8px;
background: aliceblue;
font-style: italic;
border: 1px dotted black;
}
#sidebar a {
color: #0000ff;
}
#sidebar a:hover,
#sidebar a:visited {
color: #800080;
}
#sidebar {
text-transform:none;
background-color: #ffffff;
color: #000000;
padding-left: 20px;
width: 25%;
float: right;
font: normal normal 100% 'Trebuchet MS', verdana, sans-serif;
border-left:2px dashed #000000;
word-wrap: break-word; /* fix for long text breaking sidebar float in IE */
overflow: hidden; /* fix for long non-text content breaking IE sidebar float */
}
.sidebar .widget {
padding-top: 4px;
padding-right: 8px;
padding-bottom: 0;
padding-left: 0;
}
.profile-textblock {
clear: both;
margin-left: 0;
}
.profile-img {
float: left;
margin-top: 0;
margin-right: 5px;
margin-bottom: 5px;
margin-left: 0;
}
.clear {
clear: both;
}
.comment-footer {
padding-bottom: 1em;
}

–></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>

<!–Can't find substitution for tag [adEnd]–>
</div>
<div class='blog-pager' id='blog-pager'>
<span id='blog-pager-newer-link'>
<a class='blog-pager-newer-link' href='http://nippondanji.blogspot.com/2009/03/mysqlexplain.html' id='Blog1_blog-pager-newer-link' title='次の投稿'>次の投稿</a>
</span>
<span id='blog-pager-older-link'>
<a class='blog-pager-older-link' href='http://nippondanji.blogspot.com/2009/03/dbt-2.html' id='Blog1_blog-pager-older-link' title='前の投稿'>前の投稿</a>
</span>
<a class='home-link' href='http://nippondanji.blogspot.com/'>ホーム</a>
</div>
<div class='clear'></div>
<br/><div id='hatena_bookmark_anywhere' target_url=''></div>
<div class='post-feeds'>
<div class='feed-links'>
登録:
<a class='feed-link' href='http://nippondanji.blogspot.com/feeds/826894812711775842/comments/default' target='_blank' type='application/atom+xml'>コメントの投稿 (Atom)</a>
</div>
</div>
</div></div>
</div>
<div id='sidebar-wrapper'>
<div class='sidebar section' id='sidebar'><div class='widget HTML' data-version='1' id='HTML9'>
<div class='widget-content'>
<style>
.dp-highlighter { font-family: "Consolas", "Courier New", Courier, mono, serif; font-size: 12px; background-color: #E7E5DC; width: 99%; overflow: auto; margin: 18px 0 18px 0 !important; padding: 1px 0 0 0 !important; }
.dp-highlighter ol, .dp-highlighter ol li, .dp-highlighter ol li span { margin: 0; padding: 0; border: none; }
.dp-highlighter a, .dp-highlighter a:hover { background: none; border: none; padding: 0; margin: 0; }
.dp-highlighter .bar { padding: 0 0 0 45px; }
.dp-highlighter.collapsed .bar, .dp-highlighter.nogutter .bar { padding-left: 0px; }
.dp-highlighter ol { list-style: decimal; background-color: #fff; margin: 0px 0px 1px 45px !important; padding: 0px;color: #5C5C5C; }
.dp-highlighter.nogutter ol, .dp-highlighter.nogutter ol li { list-style: none !important; margin-left: 0px !important; }
.dp-highlighter ol li, .dp-highlighter .columns div { background: none; list-style: decimal-leading-zero; list-style-position: outside !important; border-left: 3px solid #6CE26C; background-color: #F8F8F8; color: #5C5C5C; padding: 0 3px 0 10px !important; margin: 0 !important; line-height: 14px; }
.dp-highlighter.nogutter ol li, .dp-highlighter.nogutter .columns div { border: 0; }
.dp-highlighter .columns { background-color: #F8F8F8; color: gray; overflow: hidden; width: 100%; }
.dp-highlighter .columns div { padding-bottom: 5px; }
.dp-highlighter ol li.alt { background-color: #FFF; color: inherit; }
.dp-highlighter ol li span { color: black; background-color: inherit; }
.dp-highlighter.collapsed ol { margin: 0px; }
.dp-highlighter.collapsed ol li { display: none; }
.dp-highlighter.printing { border: none; }
.dp-highlighter.printing .tools { display: none !important; }
.dp-highlighter.printing li { display: list-item !important; }
.dp-highlighter .tools { padding: 3px 8px 3px 10px; font: 9px Verdana, Geneva, Arial, Helvetica, sans-serif; color: silver; background-color: #f8f8f8; padding-bottom: 10px; border-left: 3px solid #6CE26C; }
.dp-highlighter.nogutter .tools { border-left: 0; }
.dp-highlighter.collapsed .tools { border-bottom: 0; }
.dp-highlighter .tools a { font-size: 9px; color: #a0a0a0; background-color: inherit; text-decoration: none; margin-right: 10px; }
.dp-highlighter .tools a:hover { color: red; background-color: inherit; text-decoration: underline; }
.dp-about { background-color: #fff; color: #333; margin: 0px; padding: 0px; }
.dp-about table { width: 100%; height: 100%; font-size: 11px; font-family: Tahoma, Verdana, Arial, sans-serif !important; }
.dp-about td { padding: 10px; vertical-align: top; }
.dp-about .copy { border-bottom: 1px solid #ACA899; height: 95%; }
.dp-about .title { color: red; background-color: inherit; font-weight: bold; }
.dp-about .para { margin: 0 0 4px 0; }
.dp-about .footer { background-color: #ECEADB; color: #333; border-top: 1px solid #fff; text-align: right; }
.dp-about .close { font-size: 11px; font-family: Tahoma, Verdana, Arial, sans-serif !important; background-color: #ECEADB; color: #333; width: 60px; height: 22px; }
.dp-highlighter .comment, .dp-highlighter .comments { color: #008200; background-color: inherit; }
.dp-highlighter .string { color: blue; background-color: inherit; }
.dp-highlighter .keyword { color: #069; font-weight: bold; background-color: inherit; }
.dp-highlighter .preprocessor { color: gray; background-color: inherit; }
</style>
<script type="text/javascript"><!–
// 1.5.1
var dp={sh:{Toolbar:{},Utils:{},RegexLib:{},Brushes:{},Strings:{AboutDialog:'<html><head><title>About…

mysqlの実行計画を知るには

mysqlで実行計画を取得するには実行したいクエリーの前に EXPLAIN をつけるだけ。
実にシンプルだが、取得できるデータもシンプル極まりない(^_^;)

mysql> EXPLAIN SELECT COUNT(customer_id) FROM dtb_customer  WHERE  del_flg = 0  
AND customer_id IN (SELECT customer_id FROM dtb_order WHERE order_id IN 
(SELECT order_id FROM dtb_order_detail WHERE product_name LIKE '%とうもろこし%' ));
+----+--------------------+------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table            | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+------------------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | dtb_customer     | ALL  | NULL          | NULL | NULL    | NULL | 1834 | Using where |
|  2 | DEPENDENT SUBQUERY | dtb_order        | ALL  | NULL          | NULL | NULL    | NULL | 2023 | Using where |
|  3 | DEPENDENT SUBQUERY | dtb_order_detail | ALL  | NULL          | NULL | NULL    | NULL | 2163 | Using where |
+----+--------------------+------------------+------+---------------+------+---------+------+------+-------------+
3 rows in set (0.02 sec)

実行計画の読み取り方は こちら に詳しく載っています。

IN句はEXISTS句におきかわる

さて、上にあげたクエリーは IN句に二重にサブクエリーが入っている、ツッコミどころ満載のSQLである。(実はEC-CUBEの中でこのクエリーが使われている。)
普通は1番内側のサブクエリーから実行されていって結果が返ってくるという動きを想像してしまうしそのほうが効率がいいのは明らかだ。
ところがmysqlでは外側のクエリーから実行し、得たレコードについて内側のサブクエリーを繰り返し実行するという処理になる。
つまりIN句なしで検索した結果が3000行ある場合、その各行についてサブクエリーの走査が実行するされるのでとんでもなく時間がかかってしまう。

何故このようになるかというとmysqlのオプティマイザがIN句をEXIST句に置き換えてしまうため、サブクエリーが相関サブクエリーとなり、結果上に述べた実行計画になってしまう。

DEPENDENT SUBQUERY に注意

このように実行計画で DEPENDENT SUBQUERY となっているものについては、 nxn の走査が走ってしまう可能性があるのでできるだけ排除したほうがいいだろう。
できる限りJOINを使うように心がけたい。

Comments [tips]なぜmysqlのサブクエリは遅いのか はコメントを受け付けていません


なんでもCMS では「EAVモデル」というデータ構造を採用しています。
EAVとは Entity, Attribute, Value の略です。
この名称が一般的なのかは知りませんが、Magento というeCommerceシステムで EAVモデルという言葉が使われています。
http://4plus.jp/tech/2011/02/magentoeav.html

従来のRDBMSよるスキーマ構造とは大きく異なり、本来テーブルとして定義すべきものを全て Entity, Attribute, Value の3つのテーブルで管理します。(なんでもCMSではRecordというテーブルも使っています。)

これにより管理画面から自由にオブジェクト設定(=データベースのテーブル設計)ができ、動的に変更が可能な構成を実現しています。メタデータがDBに登録されているので画面の生成なども容易に自動化できるわけです。
EAVモデルはなんでもCMSにとっては欠かせない要素のひとつなのです。

逆にEAVモデルのデメリットは

  1. 正規化されたRDBMS設計のようにSQLでの取り扱いが簡単ではない
  2. データ量が増えてきたときのパフォーマンス(特定のカラムにインデックスをつけたりとかできない)

といったことが挙げられます。

1についてはO/Rマッパー層で吸収するコードを書いたので、あたかも普通のテーブル設計のようにプログラミングすることができるようになりました。

2についてはメリットとのトレードオフということで割り切って考えています。
CMSでそれほどデータ量が問題になるとも思えないですし、データ量の多いオブジェクトは専用のテーブルを作るなどのハイブリッドな設計にすればよいかと思います。

EAVモデルはCMSとの親和性が非常に高いということに気づいたのがなんでもCMS開発のきっかけでもあります。

Comments EAVモデル はコメントを受け付けていません


概要

先日MySQLのMaster-Slaveレプリケーションが何かの拍子に機能しなくなっていることがわかりました。
このような状況に陥ったときの修正手順についてまとめてみます。

環境:

  • Debian lenny
  • MySQL 5.0.51
  • 1台のマスタから1台のスレーブに対してレプリケーションしている構成

修正前のSlave状態

まず、現在のSlaveの状態を確認します。

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State:
                Master_Host: xxx.xxx.xxx
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysqld-bin.000005
        Read_Master_Log_Pos: 13341150
             Relay_Log_File: mysqld-relay-bin.000232
              Relay_Log_Pos: 98
      Relay_Master_Log_File: mysqld-bin.000005
           Slave_IO_Running: No
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 13341150
            Relay_Log_Space: 98
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: NULL
1 row in set (0.01 sec)

⇒「Slave_IO_Running: No 」になっています。
ちなみにマスタの状態は、、

mysql> show master status\G
*************************** 1. row ***************************
            File: mysqld-bin.000009
        Position: 42782848
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

となっています。明らかにずれています。

復旧手順

MasterのバックアップとPositionの確認

Masterのバックアップを取ると同時にLog Positionを確認しておきます。

(Master側で)
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
※ここで表示される FileとPositionの値を記録しておく。

バックアップはmysqldumpコマンドで行ないます。

$ mysqldump -u root -p --lock-all-tables --databases db1 db2 ...  > mysql.dmp

バックアップが終わったらロックを解除します。

(Master側で)
mysql> UNLOCK TABLES;

※ここまでの間、DBへの書き込みがブロックされることになります。

Slave側にバックアップを適用

Masterで取得したバックアップファイルをSlaveに適用します。

(Slave側で)
mysql> drop database db1 db2 ....;
$ mysql -u root -p < mysql.dmp

Slave側でMasterのpositionをセットしてレプリケーション開始

Masterバックアップ時に記録したFile名とPotisionをSlaveにセットしてレプリケーションを開始します。

(Slave側で)
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001',  MASTER_LOG_POS=4320981;
mysql> START SLAVE;

これで完了。Master側で SHOW MASTER STATUS, Slave側で SHOW SLAVE STATUSを実行してレプリケーションが機能していることを確認します。

Nagiosでのレプリケーション監視

WEB+DB PRESS vol.54に書いてあるとおりなのですがNagiosでレプリケーションの監視ができることを知りました。
標準プラグインで提供されている「check_mysql」を使用するのですがDebianの場合は
/etc/nagios-plugins/config/mysql.cfg に以下のように追記してコマンドを用意しました。

# 'check_mysql_slave' command definition
define command{
        command_name    check_mysql_slave
        command_line    /usr/lib/nagios/plugins/check_mysql -H '$HOSTADDRESS$' -u '$ARG1$' '$ARG2$'
}

そしてサーバの定義ファイルに

define service{
        use                             generic-service
        host_name                  localhost
        service_description       mysql
        check_command         check_mysql_slave!nagios!-S
}

のように設定します。

※監視用につかう nagiosユーザには REPLICATION CLIENTに加えて SUPERの権限が必要でした。

mysql> GRANT SUPER,REPLICATION CLIENT ON *.* TO [email protected];

※今回は既に一度レプリケーション構成を構築していた環境で再設定することについてまとめましたが、一からレプリケーション構成を構築する場合にはこれに加えていくつかの作業が必要となります。下記のリンクなどが参考になります。

参考

Comments MySQLのレプリケーションが不整合になってしまったときの再構築手順 はコメントを受け付けていません