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

old_passwordsの罠

 お名前.com VPS(KVM)に新サイトを展開していて、MySQLで作ったユーザがログインできない状態が発生した。

 MySQLはデフォルトリポジトリから導入して、設定ファイルは特別な記述をせず展開。
 CREATE USER文でユーザを作って、パスワードも設定。
 これでできあがったユーザでログインしようとすると失敗する。 Host値を書き換えてみたりするがダメ。

 何だろうな-、と思って、mysql.userテーブルを眺めてみると、既存ユーザのpasswordは16桁で、新しいユーザは41桁だった。
 16桁って言うのはMySQL4.1以前のハッシュ値で、4.1からは標準が41桁になっている(先頭に、旧ハッシュと新ハッシュを識別するための*記号が付与されていて、実際は40桁)
 mysql cliでSELECT PASSWORD(‘hoge’);してみると、41桁が帰ってくる。 でも、41桁ハッシュのアカウントは全てアクセス不能で16桁の物だけアクセスできる。

 試しにmy.cnfのmysqldセクションで、old_passwords=0を明示的に指定してrestartしてみると、41桁ハッシュのアカウントでログイン可能&16桁ハッシュのアカウントでもログイン可能となった。
 試しにold_passwords=1を明示的に指定すると・・・41桁ハッシュでも16桁ハッシュでもログイン可能であった。

・・・あれ?
 old_passwordsを明示的に指定してやらない場合、16桁ハッシュのアカウントしか認証できず、0でも1でも指定してやればどちらでも認証できる。 と言う、変な結果(不定値の条件バグだね)
 じゃあ、old_passwords値は無視されているのかというとそんなこともなく、
・0の場合はPASSWORD関数が生成するハッシュが41桁になる
・1の場合はPASSWORD関数が生成するハッシュが16桁になる
と言う動きをしていた。

 作成したユーザで認証できない場合、設定ファイルのold_passwords値をチェックしてみると良いと言うメモ。

(349)


カテゴリー: MySQL | タグ: , | コメントをどうぞ

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が小さい場合ほど効果的。
 まぁ、根本を直した方が良いんだけど、とりあえず、簡単な修正で処理時間を短縮できるメモ。

(219)


カテゴリー: MySQL | コメントをどうぞ

MySQLで過去x日間と言う条件を書く

 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秒減算する必要がある。

(166)


カテゴリー: MySQL | コメントをどうぞ

MySQLで特定日(月初・月末)を求めるクエリ

・基本
●先月の末日

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))

(206)


カテゴリー: MySQL | コメントをどうぞ

MySQLでランダムな文字列を差し込む – MySQL CHAR ROUND RAND VPS

 プチ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だけでランダム文字列を挿せる。

(279)


カテゴリー: MySQL | コメントをどうぞ

MySQL最適化メモ02 – MySQL EXPLAIN type 開発

 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万レコードを全域スキャンすると分単位かかったりする一番低速な動作。
 ~数十レコード程度のタイプ値保持等の用途以外でこれが出てきたらクエリ解析してインデックスを貼る。

(12)


カテゴリー: MySQL | コメントをどうぞ

MySQL最適化メモ01 – MySQL EXPLAIN type 開発 vps

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にした方が良い。

(20)


カテゴリー: MySQL | コメントをどうぞ