カテゴリー別アーカイブ: MySQL

　MySQLの便利機能でGROUP_CONCATってのがある。

SELECT primary_id, GROUP_CONCAT(item_id) AS item_id_list FROM stock GROUP BY primary_id ORDER BY primary_id ASC LIMIT 50

みたいにすると、stockテーブルのprimary_idでグループ化して、そのグループ内のitem_idをカンマ区切りで返してくれる。
　このとき、item_id_listはBLOBになったりする。
　こんな感じでもっと複雑なテーブルについてきたとき、ORDERやLIMITの関係でtmpテーブルでの作業が発生するんだけど、BLOBがMEMORYエンジンに乗らないのでボトルネックになっていた。
　さくらVPSやお名前.com VPS(KVM)では、物理マシンに比べてディスクI/Oが弱いのでTmp to Diskになると著しく遅くなるので改善したい。
　ORDERやLIMITがINDEXで直ちに確定できない場合に、巨大なデータセットがtmpになるので、とりあえずこれをサブクエリにして

SELECT s.*, GROUP_CONCAT(item_id) AS item_id_list FROM (SELECT primary_id FROM stock GROUP BY primary_id ORDER BY primary_id ASC LIMIT 50) p LEFT JOIN stock s ON s.primary_id = p.primary_id GROUP BY s.primary_id ORDER BY s.primary_id ASC

みたいにした方が高速になった。
　この場合は、外のクエリは結局tmpの処理があるんだけど、LIMIT処理がサブクエリ中でかかっているから、JOINの段階でデータサイズが小さくなってCopy to Tmp Tableの時間が短縮できる。
　中間処理テーブルが大きくてLIMITが小さい場合ほど効果的。
　まぁ、根本を直した方が良いんだけど、とりあえず、簡単な修正で処理時間を短縮できるメモ。

(1257)

Tweet

　MySQLで指定日数前の日付を取得する場合、

DATE(DATE_ADD(NOW(), INTERVAL -日数 DAY))

で取得出来る。

createdateに日付を入れていて、最近7日以内に作られたレコードを求める場合、

SELECT * FROM my_table WHERE createdate >= DATE(DATE_ADD(NOW(), INTERVAL -7 DAY))

みたいになる。　ちなみに -0 DAYなら今日になる。

　関連して、日付の曜日を取得する場合、

WEEKDAY(日付)

で取得する。　0が月曜で始まる。

　今週の月曜日の日付は、

DATE(DATE_ADD(NOW(), INTERVAL -WEEKDAY(NOW()) DAY))

で取得出来る。

　今週の月曜日からの記録を切り出す場合、

createdate >= DATE(DATE_ADD(NOW(), INTERVAL -WEEKDAY(NOW()) DAY))

で取得。

　先週の月曜から金曜までの記録を切り出す場合、

createdate >= DATE(DATE_ADD(NOW(), INTERVAL -WEEKDAY(NOW())-7 DAY)) AND createdate < DATE(DATE_ADD(NOW(), INTERVAL -WEEKDAY(NOW())-3 DAY))

※BETWEENは以上以下。　時刻なしのDATEは0時0分0秒扱いだから1秒減算する必要がある。

(951)

Tweet

・基本
●先月の末日

DATE( DATE_SUB(NOW(), INTERVAL DAYOFMONTH(NOW()) DAY) )

現在 – 今月の今までの日数 = 先月末日の現時刻 で、DATEで日付に丸める。

●今月の初日

DATE( DATE_SUB(NOW(), INTERVAL DAYOFMONTH(NOW())-1 DAY) )

現在 – (今月の今までの日数-1) = 今月初日の現時刻 で、DATEで日付に丸める。

●今月の末日

LAST_DAY(NOW())

●Nヶ月前の初日

DATE( DATE_SUB( DATE_SUB(NOW(), INTERVAL DAYOFMONTH(NOW()) -1 DAY), INTERVAL N MONTH))

●Nヶ月前の末日

LAST_DAY( DATE( DATE_SUB( DATE_SUB(NOW(), INTERVAL DAYOFMONTH(NOW()) -1 DAY), INTERVAL N MONTH)))

　月次集計バッチで前月の期間を指定する場合・・・

SELECT * FROM logs WHERE logs.created >= DATE(DATE_SUB(NOW(), INTERVAL DAYOFMONTH(NOW()) DAY)) AND logs.created < DATE(DATE_SUB(NOW(), INTERVAL DAYOFMONTH(NOW())-1 DAY))

(1070)

Tweet

　プチMySQLメモ

　以前、intにランダムな数値を差し込んだ。

　今回はランダムな文字を差し込んでみる。

1文字だけを差し込む場合、

UPDATE vps SET vps_id = CHAR(ROUND(RAND()*25)+65);

　CHAR関数は、与えた数値を文字コードとして評価して文字を出力する。
　65が大文字Aで、+25番が大文字Z。　大文字と小文字を混ぜたい場合、コードが連続していないので、ランダムで大文字範囲と小文字範囲を分岐することになる。

　既定文字数差し込む場合にはCONCAT（文字列連結）を使って・・・

UPDATE vps SET vps_id = CONCAT(CHAR(ROUND(RAND()*25)+65),CHAR(ROUND(RAND()*25)+65));

　とりあえずこれでSQLだけでランダム文字列を挿せる。

(1525)

Tweet

　MySQL最適化メモ01の続きで、typeの残り。　ref_or_nullより遅いタイプ。

・index_merge
　複数のインデックスを結合したタイプ。

SELECT * FROM vps_table WHERE column_one > 10 AND column_two > 20

・range
　インデックスの範囲を限定する（2値で挟み込むか、いくつかの候補から得る）場合のタイプ。

SELECT * FROM vps_table WHERE column_one > 10 AND column_one < 20

・index
　インデックス全域をスキャンする場合のタイプ。
　インデックスはデータ全域よりも小さいはずなのでALLよりはマシだけど、インデックスに適した動作をしていない(木やハッシュの探索で確定できず、インデックスを最後まで読まなければならない)

・ALL
　データ全域をスキャンする場合のタイプ。
　さくらのVPS 4GBでキャッシュされていても、1万レコードを全域スキャンすると分単位かかったりする一番低速な動作。
　～数十レコード程度のタイプ値保持等の用途以外でこれが出てきたらクエリ解析してインデックスを貼る。

(130)

Tweet

MySQLのEXPLAIN結果の読み方。

typeフィールド値を高速な順にメモる。

・const
　ユニークなキー（PK or UNIQUE）に対して定数で検索する場合に出る型。

　SELECT * FROM my_table WHERE id=1;

・eq_ref
　ユニークなキー同士で対応が取れる場合の型。

　SELECT * FROM my_table, sub_table WHERE my_table.unique_column = sub_table.id;

・ref
　インデックスが貼られたカラムで対応を取るか、式によって求められる場合の型。
　インデックスがユニークに出来る場合はUNIQUEにしたほうがeq_refになって高速。

　SELECT * FROM my_table, sub_table WHERE my_table.index_column = sub_table.id;

・ref_or_null
　その名の通り、refにnullが加わっている場合（インデックスカラムがNOT NULLではない場合）
　NULLは不定なので、セーフなデフォルト値を設定してNOT NULLにした方が良い。

(370)

Tweet