パーティションって何よ？

100万レコードとかになるとテーブルが重くなる。
そのテーブルを細かく分けるという技。
保存する時やデータを取得する時は、パーティションを指定して取得、保存が可能。

また、今まで通りの操作でも正常に動作する。

分割方法

パーティショニングしたいカラムが PRIMARY KEY に含まれている必要がある。
なので、インデックス張替え。

id だけのインデックスから
id , created の複合インデックスに変更。

ALTER TABLE yourtable DROP PRIMARY KEY, ADD PRIMARY KEY(id,created);

複合にしても id での並び替え検索の時間は変わらない

パーティションを区切る

p201711 と TO_DAYSの中身は1ヶ月変えておく。
でないと、1ヶ月ずれてデータが入ってしまう。

ALTER TABLE yourtable
PARTITION BY RANGE (TO_DAYS(created)) (
    PARTITION p201711 VALUES LESS THAN (TO_DAYS('2017/12/01 00:00:00')),
    PARTITION p201712 VALUES LESS THAN (TO_DAYS('2018/01/01 00:00:00')),
    PARTITION p201801 VALUES LESS THAN (TO_DAYS('2018/02/01 00:00:00')),
    PARTITION p201802 VALUES LESS THAN (TO_DAYS('2018/03/01 00:00:00'))
);

php でパーティション名を一括取得

何十年先分もつくっておきたい。
そんな贅沢なあなたに。

        $i = 1;

        while($i < 360){


            $p_tuki = $i-1;
            $p_name = date('Ym', strtotime('+'.$p_tuki.' month'));

            $raigetu = date('Y/m', strtotime('+'.$i.' month'));

echo "PARTITION p".$p_name." VALUES LESS THAN (TO_DAYS('".$raigetu."/01 00:00:00')),<br>";

            $i++;
        }

        die;

パーティション区切れた？

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME =  'yourtable';

パーティションがが区切れてますね

パーティションを指定して検索

SELECT * FROM yourtable PARTITION (p201801, p201802) WHERE c < 5;

パーティション張替え ( パーティションを一旦削除。データは消えない。 )

ALTER TABLE yourtable REMOVE PARTITIONING;

データの保存は？

新規保存時は、自動的に今年の日時が入るので何も考えなくて良い。
UPDATE時はパーティションを指定してupdateしたいので、日付も渡してパーティション名を取得。

cakephp3 で使おう。

//    パーティションを指定して保存
    public function pSave($data)
    {
        $con = ConnectionManager::get($this->getConnection()->config()['name']);

//        $last = $con->insert('mes PARTITION ('.$this->getPartitionName().')', [
//                'social_id' => s('id'),
//                'ip' => '111.222.333.444',
//                'body' => "%ここにテスト内容が入るよ%"
//            ]
//        );


//        update時は view から createdを送ってくること。
        if(!empty($data['id'])){

            $partition_name = "p".dtf($data['created'], "Ym");
            unset($data['created']);
            $last = $con->update($this->getTable().' PARTITION ('.$partition_name.')',$data,['id' => $data['id']]);

        } else {
            $last = $con->insert($this->getTable().' PARTITION ('.$this->getPartitionName().')',$data);
        }

        $last_id = $last->lastInsertId();

        return $last_id;

    }

//    インサートスべきパーティション名を返す
    public function getPartitionName()
    {
        return "p".date("Ym");
    }

レッツ！パーティション MYSQL。