🐡
MySQLパーティショニングやってみた
データは、ローカル環境にMySQLサーバーを立ち上げて、次のデータをinsertして使用しました。
-
情報学研究データリポジトリ ニコニコデータセット
- 「ニコニコ動画コメント等データ」の「動画メタデータ」
- 全件はさすがに多すぎるので150ファイル、約300万件分のデータをDBに追加
テーブル準備
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