Zenn
🐡

MySQLパーティショニングやってみた

2024/04/10に公開
MySQL
tech

データは、ローカル環境にMySQLサーバーを立ち上げて、次のデータをinsertして使用しました。

テーブル準備

create database nico_movie; -- なんで nico_video にしなかったんだろう…
use nico_movie;
create table movies
(
    video_id    varchar(255)                           not null,
    title       varchar(255)                           not null,
    description text                                   null,
    watch_num   int unsigned default '0'               not null,
    comment_num int unsigned default '0'               not null,
    mylist_num  int unsigned default '0'               not null,
    category    varchar(255)                           null,
    tags        varchar(512)                           null,
    upload_time datetime     default CURRENT_TIMESTAMP not null,
    file_type   enum ('flv', 'swf', 'mp4')             not null,
    length      int unsigned default '0'               not null,
    size_high   int unsigned default '0'               not null,
    size_low    int unsigned default '0'               not null,
    primary key (video_id, upload_time)
);

データ投入

速度を考えない挿入スクリプトを作成。いつも使ってるRubyで実装しました。
なお、今回使用したデータは jsonl 形式のため、jsonl と mysql2 Gemを使用してパースしています。

# 下記のコマンドを実行した前提です。
# > gem install jsonl mysql2
require 'jsonl'
require 'mysql2'

client = Mysql2::Client.new(host: '127.0.0.1', port: 3306, username: 'root', database: 'nico_movie')
statement = client.prepare('insert into movies values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)')

Dir.glob('./jsonl/*').each do |path|
  source = File.read(path)
  parsed = JSONL.parse(source)

  parsed.each do |p|
    puts p['video_id']
    statement.execute(p['video_id'], p['title'], p['description'], p['watch_num'], p['comment_num'], p['mylist_num'], p['category'], p['tags'], p['upload_time'], p['file_type'], p['length'], p['size_high'], p['size_low'])
  end
end

一晩放置（！）

select してみる

mysql> select * from movies where upload_time between '2015-01-01' and '2015-12-31';
#:
236925 rows in set (2.83 sec)

mysql> select * from movies where upload_time between '2015-01-01' and '2015-12-31';
#:
236925 rows in set (3.25 sec)

mysql> select * from movies where upload_time between '2015-01-01' and '2015-12-31';
#:
236925 rows in set (2.67 sec)

パーティショニング適用

本題のパーティショニングを適用してみる。

alter table movies
partition by range columns (upload_time) (
    partition p_upload_time0 values less than ('2007-12-31'),
    partition p_upload_time1 values less than ('2008-12-31'),
    partition p_upload_time2 values less than ('2009-12-31'),
    partition p_upload_time3 values less than ('2010-12-31'),
    partition p_upload_time4 values less than ('2011-12-31'),
    partition p_upload_time5 values less than ('2012-12-31'),
    partition p_upload_time6 values less than ('2013-12-31'),
    partition p_upload_time7 values less than ('2014-12-31'),
    partition p_upload_time8 values less than ('2015-12-31'),
    partition p_upload_time9 values less than ('2016-12-31'),
    partition p_upload_time10 values less than ('2017-12-31'),
    partition p_upload_time11 values less than ('2018-12-31'),
    partition p_upload_time12 values less than ('2019-12-31'),
    partition p_upload_time13 values less than ('2020-12-31'),
    partition p_upload_time14 values less than ('2021-12-31'),
    partition p_upload_time15 values less than ('2022-12-31'),
    partition p_upload_time16 values less than ('2023-12-31'),
    partition p_upload_time17 values less than MAXVALUE
    );

再度 select してみる

mysql> select * from movies where upload_time between '2015-01-01' and '2015-12-31';
#:
236925 rows in set (1.42 sec)

mysql> select * from movies where upload_time between '2015-01-01' and '2015-12-31';
#:
236925 rows in set (0.97 sec)

mysql> select * from movies where upload_time between '2015-01-01' and '2015-12-31';
#:
236925 rows in set (0.92 sec)

パーティションを解除して再度実行してみる

mysql> ALTER TABLE `movies` REMOVE PARTITIONING;

mysql> select * from movies where upload_time between '2015-01-01' and '2015-12-31';
#:
236925 rows in set (3.55 sec)

mysql> select * from movies where upload_time between '2015-01-01' and '2015-12-31';
#:
236925 rows in set (2.70 sec)

わりと効果あるんだなぁ。

気になること

300万件弱のデータを使った感じ、最大3.5秒が最短0.9秒になった。細かいことは気にしないでこれだけ見るとお手軽に高速化できるのですごく良い感じである。
ただ、パーティションを使用するためにはプライマリキーじゃないと指定ができないという制約があるようで、実用上問題になる可能性があるのかなと思った。ここまでレコード数があるデータベースを触ったことがないのでなんともだがｗ

Discussion