7月 29
以前のエントリーで集計クエリを利用する方法を説明しましたが、それだとDatetimeのフィールドを「年月」ごとに、っていう集計はできませんでした。購買履歴データを月別に件数集計したいときなんかは非常に困るんですよね。ので、それを解決する方法です。
select_column = {“d_column”: ”DATE_FORMAT(pub_date, ‘%%Y%%m’)”}
_list = PurchaseRecord.objects.extra(select=select_column)
_list = _list.values(“d_column”).annotate(cnt=Count(‘id’))
上記はMySQLを利用しているケースの書き方です。
あ、横長になると見難いから2行に分けただけで、1行で大丈夫ですからね。
「ORマッパー使ってるのにデータベースに依存ってなによ」
な感じではありますが、extraを使っている部分は直接クエリが流れるみたいですね。
つまり、「DATE_FORMAT(pub_date, ‘%%Y%%m’)」の部分は
select *,DATE_FORMAT(pub_date, ‘%Y%m’) as d_column
from app_purchaserecord
といった風に、追加されるのです。で、追加されたカラムを使って集計、という流れですね。うっかりミスりやすいのが「%」を二つにする点でしょうか。
あと、データベースを変えたら「select_column～」の部分に修正が必要です。使用するデータベースに依存した書き方になります。ここは要注意ですね。
7月 27
DjangoでMySQLを利用する場合、初期状態だとMyISAMでテーブルが作成されます。まぁ後からalterすればよい話ではあるのですが、なんかそれだと違うかなぁと。
一昔前はInnoDBはMyISAMほどパフォーマンスが優れず、更新系のテーブルと参照系のテーブルでストレージエンジンを使い分けたものですが、今はストレージエンジンによるパフォーマンスの差も少ないため、行レベルロックができるInnoDBを積極的に採用するケースが増えてるみたいですね。まぁ数千万行をレコードから数ミリ秒でレスポンスを返さないといけないケースとかではどうなるのかわかりませんが。
DATABASE_OPTIONS = {
“init_command”: “SET storage_engine=INNODB”,
}
settings.pyに上記を記載するだけでsyncdbするときにInnoDBでテーブル作成してくれます。が、この記述はテーブル作成が終わって運用にはいったら削除するように、とのことです。
MySQLとの接続が確立された後にわざわざストレージエンジンを指定するコマンドを送っているっぽいので、普段は無駄な処理が発生しちゃうっていうことなんですかね。
12月 30
ApacheBenchでこのブログのパフォーマンスを計測したら、
Requests per second: 9.94[#/sec]
という、思っていたより残念な結果でした。うーん、思っていたより低いなぁ。。。
で、とりあえずmysql_connectをmysql_pconnectに変えてみたのですが。
Requests per second: 10.01 [#/sec]
と、大して変わらず。。。
めんどくさいけど、ApacheとMySQLの設定から見直そう。PHPをDSOで使ってるのも影響してるのかなぁ。。。
9月 18
timestampdiff(day, from_date, till_date)
これだけです。
第一引数は「month」、「year」で月、年の差も取れます。第二第三引数はtimestampじゃなくてもいいです。まぁTime型だとダメなんだろうけど、Date型だったらちゃんと差分が取得できました。
「登録から～日の情報」みたいな取得の仕方をすることが多いので、こういう関数は使用頻度が高いです。
11月 20
ネストしてもいいのですが、可読性が悪かったりパフォーマンスが悪くなったり。
select * from table_0 i
left join table_1 j on i.id = j.id
left join table_2 k on i.id = k.id
つまり、「table_0に対してtable_1とtable_2を結合しまっせ。」ということです。ネストしてないのでとっても読みやすいです。パフォーマンスも、よい気がします（計測しろよというご指摘はごもっともです）。
11月 13
MySQLでトリガーを使う
の続きといいますか、おんなじような内容なのですけが、ストアドの使い方です。これもいたって簡単です。
delimiter //
create procedure ストアド名(in 引数名 データ型)
begin
実行する処理、引数はそのままの名前で使える
end;
//
delimiter ;
大量の更新処理なんかには便利でしょう。
11月 03
MySQL5.xでのお話です。バックアップは
mysqldump -u root –password=xxxxxxxx データベース名 > 保存先パス
保存先はMySQLが書き込める権限を持っている必要があるみたいです。mysqldumpを実行するユーザが書き込めればいいのかなぁ。今度検証しておこう。
リストアは簡単です。
mysql -u root –password=xxxxxxxx データベース名 < バックアップファイル
バックアップって言っても、バイナリじゃなくてDDLとDMLがぎっしり書かれたテキストファイルなので、リストアするのも「このファイルに書かれた大量のSQLをこのデータベースに実行しよう」ってイメージですね。
そういえばmysqldump実行中って、書き込み制御してくれるのかなぁ。
10月 28
select last_day(date_add(now(), interval -1 month))
まぁなんてことない手法なのですが、なんとなく便利だったので記録。「今日の一ヶ月前の月の末日」っていう意味ですね。
10月 24
MySQLも5.xからトリガーが使えるようになりました。SQL SERVERやPostgreSQLでストアド、トリガーを多用していたハセテツとしては大変ありがたいわけです。
delimiter //
これで書き始めます。デリミタは「;」ですが、トリガーの記述の中でもセミコロンを使うために一時的に「デリミタはスラスラだよ」と宣言しておきます。べつにスラスラじゃなくてもOKでしょう。
create trigger トリガー名称
before insert on 対象テーブル for each row begin
declare target_id tinyint;
select hoge_id into target_id from マスタデータテーブル where hoge_code = new.hoge_code;
set new.log_hoge_id = target_id;
end; //
まぁなんのこっちゃわからんサンプルですが、declareで変数宣言、select intoで変数へ代入、setで値を設定しています。この例は「ログデータテーブルに値を突っ込む前にマスタテーブルから値を引っ張ってきておいて、それをinsertします」っていう感じです。
終わったら
delimiter ;
でデリミタを元に戻すのをお忘れなく。
テーブル例も書いたほうがいいんだろうけど、申し訳ないけど割愛。特定テーブルの値が更新されたら更新ログを残す、なんてことも「after update」でできるので、フロントエンド側のコードをごちゃごちゃさせずに複雑な処理がさせられると思います。
9月 17
mysqlcheck -o DB名
これだけです。ドキュメントを読む限り、内部的にcheck table、repair table、analyze table、optimize tableを呼んでいるようです。
「対象はmyisamだけ」みたいなこともどこかで読んだのですが、公式ドキュメントにはそういう記述はなし。4.xの話なのでしょうか。あ、ハセテツは5.0を利用してます。
必要なのはanalyze table、optimize tableだけで、これらはinnodbにも対応しているから特に問題はないでしょう。
