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で使ってるのも影響してるのかなぁ。。。
12月 01
#!/usr/bin/python
import MySQLdb
conn = MySQLdb.connect(db=”DB名”, host=”ホスト名”, user=”ユーザ名”, passwd=”パスワード”)
cur = conn.cursor()
query = “select * from hoge”
cur.execute(query)
result = cur.fetchall()
cur.close()
con.close()
for row in result:
print row[0]
PythonでもMySQLにつなげてみました。事前にMySQL-Pythonがインストールされていることが条件です。Windowsならexeがあるし、Linuxならeasy_installを使えば便利かと。
ハセテツの例では、事前に結果セットを取得して、それをループしています。これとは別にカーソルを使ってMySQLから順に取得していく方法もあります。
個人的に、いつまでもDBにつないでおきたくなかったのでこちらの手法をとっています。まぁメモリ消費しちゃうじゃんかというデメリットもありますが。
お好きな手法で実装していただければと思います。
9月 18
timestampdiff(day, from_date, till_date)
これだけです。
第一引数は「month」、「year」で月、年の差も取れます。第二第三引数はtimestampじゃなくてもいいです。まぁTime型だとダメなんだろうけど、Date型だったらちゃんと差分が取得できました。
「登録から～日の情報」みたいな取得の仕方をすることが多いので、こういう関数は使用頻度が高いです。
8月 20
クエリで
load data local infile ‘ファイルパス’ into table テーブル名;
を実行するだけなのですが、「local」をつけないと動いてくれなかったのでメモ。SSHでログインしていたときは「load data infile」だけでインポートできていたので、気がつかずに苦労しました。
3月 12
MySQL/Ruby と Ruby/MySQL の二つがあるようです。どっちがどっちか混乱しましたが、MySQL/Rubyがgemでとってこれて、Cで書かれたAPIのようです。Ruby/MySQL はRubyで書かれているようで、比較的低速らしいです。今回はMySQL/Rubyを利用しました。
gem install mysql
で一式インストールできます。PHPのPEARやRubyのgemは、一度つかったらやめられません。これは堕落なのでしょうか。
で、今回はWindows上にMySQL5.0をインストールしてつないでみたのですが、バリバリ文字化けました。DBはUTF8で作ってあって、ソースも以下のようにUTF8であることを明記していました。
require ‘rubygems’
require “mysql”
require ‘iconv’
$KCODE = ‘UTF-8′
db = Mysql::connect(“ホスト名”, “ユーザ名”, “パスワード”, “DB名称”)
rs = db.query ‘クエリ’
rs.each do |item|
p item[0]
end
なんでなんで？と調べていたら、my.ini（あー、iniってあたりがWindowsだなー、Linuxだとmy.cnfだったかな？）の[mysqld]に
default-character-set=utf8
skip-character-set-client-handshake
の追記が必要らしい。これはクライアントからの接続時に「文字コードはUTF8でっせ」とデフォルトで設定してあげるためのものらしいです。「set names utf8」と同じ意味ですね。これで文字化けずにWindows上でRubyとMySQLを利用することが出来るようになりました。
これまではC#とSQL Serverで統計とか集計のプログラム書いてたけど、Rubyの方がラクチンなんですよね。Railsばっかり注目されてますけど、Rubyは便利ですよ。
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実行中って、書き込み制御してくれるのかなぁ。
