MySQLではパーティションの利用機会は少ないと感じています。Oracleではパーティションをよく利用していました。 「MySQLであまり活用されないのはこういう背景なんじゃないか」と思うことを列挙してみます。
パーティションが効くような用途で使われにくい
まず、MySQLではパーティションが効くような用途での利用が少ないです。 MySQLはOLTP を得意とするデータベースです。OLTP系のワークロードでは、インデックスによる最適化が選択されます。
OLAP系ワークロードでは、大量のレコードをスキャンすることが多く、フルスキャンを効率化する手段としてパーティションが活用されます。
MySQLのパーティション機能自体の制約
MySQLのパーティション機能自体が弱いと言わざるをえません。
主キー、ユニークキーにパーティションキーを含める必要がある
例えば、「日付」カラムでレンジパーティションを切るとしたら、「日付」カラムを主キーに追加しなければなりません。 パーティション化のために、制約を緩める必要が出てきてしまいます。性能と制約のトレードオフが発生する場合があります。
mysql> CREATE TABLE orders (
-> order_id INT NOT NULL AUTO_INCREMENT,
-> order_date DATE NOT NULL,
-> customer_id INT NOT NULL,
-> total_amount INT NOT NULL,
-> PRIMARY KEY(order_id)
-> )
-> PARTITION BY RANGE (YEAR(order_date)) (
-> PARTITION p2023 VALUES LESS THAN (2024),
-> PARTITION p2024 VALUES LESS THAN (2025),
-> PARTITION p2025 VALUES LESS THAN (2026),
-> PARTITION pmax VALUES LESS THAN MAXVALUE
-> );
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
インデックスもパーティションされてしまう
MySQL (InnoDB) ではテーブルをパーティション化すると、セカンダリインデックスも、パーティションごとに作成されてしまいます（Oracleでいう ローカルパーティションインデックス）。 パーティションキー以外を含まない検索をする場合、パーティション化していない場合と比べてオーバーヘッドが増加し、性能が劣化する可能性があります。すべてのパーティションのインデックスをスキャンする必要があるためです。
セカンダリインデックスを「パーティション化しない」もしくは「テーブルと異なるキーでパーティション化」することはMySQLではできません。
パラレル処理されない
パーティションを跨ぐクエリを実行しても、各パーティションの処理が並列で実行されるわけではありません。
MySQLでパーティションを使うとき
MySQL ではパフォーマンス改善というよりデータの削除を簡単にするために使われるケースが多いのではないでしょうか。
MySQLで大量のデータを通常のDELETE 文で消去しようとすると、以下のリスクがあります。
日付でパーティショニングしておけば、不要な過去データのパーティションをALTER TABLE ... DROP PARTITIONで削除できます (もしくは、EXCHANGE PARTITION で追い出す) 。
これはメタデータの変更のみで済み、データファイルが丸ごと削除されるため、非常に高速です。上記のようなリスクを回避できます。
また、DELETEと違い、データファイルごと削除されるため、物理的なディスク容量が即座に解放されるというメリットもあります。
