Rails3からMySQLのパーティショニングを扱う時の注意点

10月に札幌でMySQL勉強会というのがありまして、その時はもくもく作業して成果発表という形だったのですが、そのときにRailsとMySQLを組み合わせて色々試したのでそれを記事にしたいと思います。

今回はパーティショニング用のgemを使っていない状態です(後で見たら存在していることに気づきました...

パーティショニングについてですが、DB内部で物理的にファイルを分けるようなものです。

例えば、MySQLのDELETE文はとても遅いですが、パーティション自体を消す場合はTRUNCATE並に早いです(1ヶ月ごとにパーティションを切って、月頭に3ヶ月より前のログを削除する時とかに便利

他にも、MySQLのオプティマイザが必要なパーティションを探しだして、そのパーティションに絞ってから検索してくれるメリットもあります。

基本形はこちらです

20121013061506_create_no_part_items.rb

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

これにレンジパーティションとリストパーティションを張ってみます

レンジパーティションを貼ったのがこちら

20121013061526_create_part_range_items.rb

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側ではこういう設定を行います

part_range_item.rb

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 とすることで、いつも通りに使えます

ちなみに、リストパーティションもレンジパーティションと同じ要領です

20121013061604_create_part_two_items.rb

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作りました。もしよければどうぞ〜