Help us understand the problem. What is going on with this article?

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

More than 5 years have passed since last update.

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

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

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

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

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

https://github.com/miio/mysql-partition-test

基本形はこちらです

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

最後に、この記事とは別ですが宣伝

https://github.com/miio/activerecord-locking-symbolic

Example.find(1, lock: "LOCK IN SHARE MODE")

みたいな書き方が個人的にどうよ?って思ったので

 Example.find(1, lock: :read)

みたいにできるgem作りました。もしよければどうぞ〜

miio@github
RailsとMySQLにお熱になってしまった、PHPとMySQLやってるソシャゲ会社勤務の人
infiniteloop
「ソースコードでなんでも生み出す」なんでもない記号から、とんでもないモノを生み出す。日々技術を磨き続け、あらゆる難題に答えていく札幌のシステム会社です。
https://www.infiniteloop.co.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした