10月に札幌でMySQL勉強会というのがありまして、その時はもくもく作業して成果発表という形だったのですが、そのときにRailsとMySQLを組み合わせて色々試したのでそれを記事にしたいと思います。
今回はパーティショニング用のgemを使っていない状態です(後で見たら存在していることに気づきました...
パーティショニングについてですが、DB内部で物理的にファイルを分けるようなものです。
例えば、MySQLのDELETE文はとても遅いですが、パーティション自体を消す場合はTRUNCATE並に早いです(1ヶ月ごとにパーティションを切って、月頭に3ヶ月より前のログを削除する時とかに便利
他にも、MySQLのオプティマイザが必要なパーティションを探しだして、そのパーティションに絞ってから検索してくれるメリットもあります。
基本形はこちらです
class CreateNoPartItems < ActiveRecord::Migration
def change
create_table :no_part_items do |t|
t.integer :user_id, null: false
t.integer :item_id, null: false
t.datetime :deleted_at
t.timestamps
end
add_index :no_part_items, [:user_id]
add_index :no_part_items, [:deleted_at]
end
end
これにレンジパーティションとリストパーティションを張ってみます
レンジパーティションを貼ったのがこちら
class CreatePartRangeItems < ActiveRecord::Migration
def change
create_table :part_range_items do |t|
t.integer :user_id, null: false
t.integer :item_id, null: false
t.datetime :deleted_at
t.timestamps
end
add_index :part_range_items, [:id]
add_index :part_range_items, [:user_id]
add_index :part_range_items, [:deleted_at]
# ADD PRIMARY
execute "ALTER TABLE part_range_items DROP PRIMARY KEY"
execute "ALTER TABLE part_range_items add primary key(id, deleted_at)"
remove_index :part_range_items, [:id]
# ADD PARTITION
execute "
ALTER TABLE part_range_items
PARTITION BY RANGE (TO_DAYS(deleted_at))
(
PARTITION p20120101 VALUES LESS THAN (TO_DAYS('2012-01-01')) ENGINE = InnoDB,
PARTITION p20120201 VALUES LESS THAN (TO_DAYS('2012-02-01')) ENGINE = InnoDB,
PARTITION p20120301 VALUES LESS THAN (TO_DAYS('2012-03-01')) ENGINE = InnoDB,
PARTITION p20120401 VALUES LESS THAN (TO_DAYS('2012-04-01')) ENGINE = InnoDB,
PARTITION p20120501 VALUES LESS THAN (TO_DAYS('2012-05-01')) ENGINE = InnoDB,
PARTITION p20120601 VALUES LESS THAN (TO_DAYS('2012-06-01')) ENGINE = InnoDB,
PARTITION p20120701 VALUES LESS THAN (TO_DAYS('2012-07-01')) ENGINE = InnoDB,
PARTITION p20120801 VALUES LESS THAN (TO_DAYS('2012-08-01')) ENGINE = InnoDB,
PARTITION p20120901 VALUES LESS THAN (TO_DAYS('2012-09-01')) ENGINE = InnoDB,
PARTITION p20121001 VALUES LESS THAN (TO_DAYS('2012-10-01')) ENGINE = InnoDB,
PARTITION p20121101 VALUES LESS THAN (TO_DAYS('2012-11-01')) ENGINE = InnoDB,
PARTITION p20121201 VALUES LESS THAN (TO_DAYS('2012-12-01')) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB
);
"
end
end
少々奇妙なことに、わざわざidカラムにインデックスを張ってから消してます
これは、レンジパーティションを貼る際に、idとレンジの対象となるカラム(今回だとdeleted_atですね)で複合プライマリを作る必要があります。
しかしながら、AUTO_INCREMENTを持ったプライマリキーをそのままドロップしちゃうと、MySQLに怒られてしまいます
そこで、同時にインデックスも張っておいてからidカラムをprimaryから外すということをやっています
また、複合プライマリになりますが、実運用上はidカラムを見ればいいのでModel側ではこういう設定を行います
class PartRangeItem < ActiveRecord::Base
attr_accessible :user_id, :item_id, :deleted_at
self.primary_key = :id
# self.primary_keys = :id, :deleted_at
end
self.primary_key = :id とすることで、いつも通りに使えます
ちなみに、リストパーティションもレンジパーティションと同じ要領です
class CreatePartTwoItems < ActiveRecord::Migration
def change
create_table :part_two_items do |t|
t.integer :user_id, null: false
t.integer :item_id, null: false
t.boolean :is_deleted
t.timestamps
end
add_index :part_two_items, [:id]
add_index :part_two_items, [:user_id]
add_index :part_two_items, [:is_deleted]
# ADD PRIMARY
execute "ALTER TABLE part_two_items DROP PRIMARY KEY"
execute "ALTER TABLE part_two_items add primary key(id, is_deleted)"
remove_index :part_two_items, [:id]
# ADD PARTITION
execute "
ALTER TABLE part_two_items
PARTITION BY LIST(is_deleted) (
PARTITION pTrue VALUES IN (1),
PARTITION pFalse VALUES IN (0)
);
"
end
end
最後に、この記事とは別ですが宣伝
Example.find(1, lock: "LOCK IN SHARE MODE")
みたいな書き方が個人的にどうよ？って思ったので
Example.find(1, lock: :read)
みたいにできるgem作りました。もしよければどうぞ〜