PakuReserve(パクレゼルヴ) R&D Laboratory

お久しぶりです。

以前、ボトルネックになっているSQLを探していたら以下のような事がありました。

EXPLAIN SELECT * FROM user_master
	WHERE user_id IN (
		SELECT user_id FROM game_user_status);

とすると

+----+--------------------+------------------+-----------------+---------------+---------+---------+------+------+-------------+
| id | select_type        | table            | type            | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+--------------------+------------------+-----------------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY            | user_master      | ALL             | NULL          | NULL    | NULL    | NULL |   86 | Using where |
|  2 | DEPENDENT SUBQUERY | game_user_status | unique_subquery | PRIMARY       | PRIMARY | 4       | func |    1 | Using index |
+----+--------------------+------------------+-----------------+---------------+---------+---------+------+------+-------------+

86*1　
で普通に意図した結果が取得できます。

しかし、サブクエリ中でGROUP BYすると酷いことになりました。

EXPLAIN SELECT * FROM user_master
	WHERE user_id IN (
		SELECT user_id FROM game_user_status GROUP BY user_id);

+----+--------------------+------------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type        | table            | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+--------------------+------------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY            | user_master      | ALL   | NULL          | NULL    | NULL    | NULL |   86 | Using where |
|  2 | DEPENDENT SUBQUERY | game_user_status | index | NULL          | PRIMARY | 4       | NULL |   67 | Using index |
+----+--------------------+------------------+-------+---------------+---------+---------+------+------+-------------+

GROUP BYすると

86*67

と、検索ロジックに大きな違いが出てきます。

で、原因は”IN”句

MySQLは内部的にINを直接処理することができないので、EXISTSに変換することでSQL的には相関のないサブクエリも相関サブクエリになってしまうのである。これがまさにMySQLのサブクエリが遅い！と言われている原因だろう。
なぜMySQLのサブクエリは遅いのか。

だからってGROUP BY使っただけでそこまでしなくても・・・

と、いう事で以下のように書き換えると解決できるようです。

EXPLAIN SELECT * FROM user_master um
WHERE EXISTS
(SELECT 1 FROM game_user_status gus WHERE um.user_id = gus.user_id);

+----+--------------------+-------+--------+---------------+---------+---------+-----------------------+------+-------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref                   | rows | Extra       |
+----+--------------------+-------+--------+---------------+---------+---------+-----------------------+------+-------------+
|  1 | PRIMARY            | um    | ALL    | NULL          | NULL    | NULL    | NULL                  |   86 | Using where |
|  2 | DEPENDENT SUBQUERY | gus   | eq_ref | PRIMARY       | PRIMARY | 4       | warui_test.um.user_id |    1 | Using index |
+----+--------------------+-------+--------+---------------+---------+---------+-----------------------+------+-------------+

個人のブログの方でご指摘いただきました内容を解答例とさせていただいています。
安直にINを利用するのは良くないという例でした。

ちなみにgroup by してもしなくても取得結果が変わらない場合もあります。
今回の例なんかはgroup by しなくても取得結果は変わりません。

むやみやたらと色々つければいいって物でも無い　いい例です。

のびーにょ MySQL, Tips, データベース

久しぶりの投稿です。
HNはチョコボールでしたがMoriMoriMoriMoriに変えます。
無意味にCamelCaseで。

管理画面でdivブロックのタイトル背景色なんかを設定する項目で、
入力項目に正しいカラーネーム、カラーコードが入力されているかどうかをチェックする
バリデーションの関数を紹介します。

// 有効なカラーネーム(147色)もしくはカラーコードかどうかチェック
function checkColor($str, $name)
{
	if($str == ''){
		return '';
	}
	// カラーネーム配列(147色)
	$arrColorName = array('aliceblue','antiquewhite','aqua','aquamarine','azure','beige','bisque','black','blanchedalmond','blue','blueviolet','brass','brown','burlywood','cadetblue','chartreuse','chocolate','coolcopper','copper','coral','cornflower','cornflowerblue','cornsilk','crimson','cyan','darkblue','darkbrown','darkcyan','darkgoldenrod','darkgray','darkgreen','darkkhaki','darkmagenta','darkolivegreen','darkorange','darkorchid','darkred','darksalmon','darkseagreen','darkslateblue','darkslategray','darkturquoise','darkviolet','deeppink','deepskyblue','dimgray','dodgerblue','feldsper','firebrick','floralwhite','forestgreen','fuchsia','gainsboro','ghostwhite','gold','goldenrod','gray','green','greenyellow','honeydew','hotpink','indianred','indigo','ivory','khaki','lavender','lavenderblush','lawngreen','lemonchiffon','lightblue','lightcoral','lightcyan','lightgoldenrodyellow','lightgreen','lightgrey','lightpink','lightsalmon','lightseagreen','lightskyblue','lightslategray','lightsteelblue','lightyellow','lime','limegreen','linen','magenta','maroon','mediumaquamarine','mediumblue','mediumorchid','mediumpurple','mediumseagreen','mediumslateblue','mediumspringgreen','mediumturquoise','mediumvioletred','midnightblue','mintcream','mistyrose','moccasin','navajowhite','navy','oldlace','olive','olivedrab','orange','orangered','orchid','palegoldenrod','palegreen','paleturquoise','palevioletred','papayawhip','peachpuff','peru','pink','plum','powderblue','purple','red','richblue','rosybrown','royalblue','saddlebrown','salmon','sandybrown','seagreen','seashell','sienna','silver','skyblue','slateblue','slategray','snow','springgreen','steelblue','tan','teal','thistle','tomato','turquoise','violet','wheat','white','whitesmoke','yellow','yellowgreen');

	// 精査用に小文字に統一
	$str = strtolower($str);
	if(!preg_match("/^[0-9a-f]{6}$/",$str) && !preg_match("/^[0-9a-f]{3}$/",$str) && !in_array($str, $arrColorName)){
		return $name.'のカラー指定が不正です。<br />';
	}
	return '';
}

データベースへの登録の際、カラムには、カラーネームの場合はそのまま入れ、
カラーコードの場合は、「#ffcc33」のように先頭に#を付けて登録してあげたほうが良いかと思います。
なぜなら、出力の度にカラーコードの場合は#を付ける、
といった判定をする処理を入れるのはパフォーマンスが悪いからです。
微々たる差異ですが。

出力の際のコーディング(ベタ書き)です。

if($title['bgcolor'] == ''){
	echo '＜div style="text-align:center;">'
}else{
	echo '＜div style="text-align:center;background-color:'.$title['bgcolor'].';">';
}
echo 'ブロックタイトル';

XHTMLでは16進数のカラーコードの英字は小文字で指定した方が良いので、
XHTML前提で利用する場合、「FFCCCC」のように入力された場合は強制的に
「#ffcccc」のように小文字にして(#もつけて)登録してあげてもいいかもしれません。
また、入力欄にカラーコードの場合、#つきで入力されても通るようにしてあげるとさらに親切かと。

fontタグのcolor属性の値やbodyタグのtext属性の値等は、
3桁の16進数での表記は当然ダメですよ！

■ダメな例

＜body text="#000">
＜font color="#fff">

ついでに書きますが、XHTMLでの属性checkedの誤った書き方。

<input type="radio" checked />

属性=”属性値”という書き方が基本なので、

<input type="radio" checked="checked" />

と書きましょう！

以上、MoriMoriMoriMoriMoriMoriでした。

MoriMoriMoriMori HTMLとか, MySQL, PHP, データベース

DBの設計上、NULLが入っている場合の集計の際にちょっとハマりました。

下記のような注文テーブル(orders)があったとします。

DESC orders;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| total     | int(11) | YES  |     | NULL    |                |
| sub_total | int(11) | YES  |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+

SELECT * FROM orders;
+----+-------+-----------+
| id | total | sub_total |
+----+-------+-----------+
|  1 |  1050 |       500 |
|  2 |  1575 |      NULL |
+----+-------+-----------+

各注文のtotalからsub_totalを差し引いた金額(エイリアスをcostとする)を取得したい時、

SELECT (total - sub_total) AS cost FROM orders;

このようにすると、NULLの場合は0として計算してくれるだろうと思っていたら甘かったです。
id: 1のcostは550、id: 2のcostはNULLとなります。

SELECT (IFNULL(total,0) - IFNULL(sub_total,0))
AS cost FROM orders;

NULLが入っている可能性がある場合は、このようにIFNULLを使ってやればNULLならば0として計算してくれます。

IFNULL(expr1,expr2)

expr1がNULLでない場合はexpr1を返し、それ以外の場合はexpr2を返します。

MoriMoriMoriMori MySQL, データベース

・フォームから送信された日付をDBにINSERT、UPDATEできる形にするための整形
・DBからSELECTで引っ張ってきたDATETIME型の日付の整形

上記を例として日付の整形ってよく出食わします。
sprintf、substr、explode、正規表現を使う等、いろんなやり方があると思いますが…
自分が使っている方法をまとめてみます。

※例 DATETIME型の文字列を整形して表示

$time = '2008-07-10 21:02:23';
$data = sscanf($time, '%d-%d-%d %d:%d:%d');

sscanf()はprintf()の入力版で、文字列を指定したフォーマットに基づいて配列で抜き出します。
(フォーマットについてはprintf()のマニュアル参照)

$dataは下記のようになります

Array
(
    [0] => 2008
    [1] => 7
    [2] => 10
    [3] => 21
    [4] => 2
    [5] => 23
)

一発で変数に入れたい場合はlist()関数を使いましょう。

list($year, $month, $day, $hour, $minute, $second) =
	sscanf($time, '%d-%d-%d %d:%d:%d');
echo $year.'年'.$month.'月'.$day.'日 '.$hour.'時'.$minute.'分'.$second.'秒';

//【結果】2008年7月10日 21時2分23秒

こんな感じにすれば取り敢えず整形して表示できますが、一桁の月、日はゼロで穴埋めしたかったりする場合もあったりするので、ここは関数sprintf()を使って整形します。

echo sprintf('%d年%02d月%02d日 %02d時%02d分%02d秒',
	$year, $month, $day, $hour, $minute, $second);

//【結果】2008年07月10日 21時02分23秒

sprintf()の引数では配列を指定できませんが、vsprintf()を使うと配列を指定でき、

list($year, $month, $day, $hour, $minute, $second) =
	sscanf($time, '%d-%d-%d %d:%d:%d');
echo sprintf('%d年%02d月%02d日 %02d時%02d分%02d秒',
	$year, $month, $day, $hour, $minute, $second);

の二行を一行でやっつけちゃえます。

echo vsprintf('%d年%02d月%02d日 %02d時%02d分%02d秒',
	sscanf($time, '%d-%d-%d %d:%d:%d'));

関数やメソッドで用意しておくと使い回しできて良いかと思われます。

MoriMoriMoriMori MySQL, PHP, Tips, データベース

お疲れ様です
のびーにょです

今日はDBオペレート時の話
自分はUPDATE文やDELETE分流す時には指差し確認を行っています。

なぜかって
怖いから

昔、本番環境のDB触ってるときにUPDATE文流す時に、”WHERE”句を付けずに実行した事があります。
会員のゲーム状況を保存しているテーブルで、それが一気に全部書き変わっちゃって・・・

あの時のあせりと言ったらもう、やばかったですよ。

幸い200件ほどしかテーブルにデータが入っていなかった(ゲームごとに違うテーブルでリリース直後だった)ので
手作業で戻すことができました。

また、上司が間違ってBBSのデータをdropしたことがありました。
あの時はバックアップから復旧したはずですけど、バックアップ取ってから書き込まれた内容は消えてしまっています。

そういう経験を見たり、自分でやっちゃってからは必ず指差しで”WHERE”句が付いていることを確認してから実行しています。

変更する物を先にSELECTで変更する内容、件数を確認してSELECT文を改変してUPDATE、DELETE文にするのもいい方法です。

それでも指差し確認してますけど。

皆様もDBを操作する場合には十分ご注意ください。

と、言っても自身がミスしない限りはきっと無理なんだろうなぁと思います。

プログラム組んでるとDBなんて日常的に操作する物だから、慣れで適当になってしまいますよね・・・

それでも自分で指差し確認をしているのはあの恐怖をもう二度と経験したくないからです。

皆さんも頭の片隅に入れておいて下さいね。

のびーにょ Tips, データベース