MySQL道普請便り

第2回 MySQLにはじめてのデータを入れてみる

この記事を読むのに必要な時間:およそ 6.5 分

MySQLをインストールしたはいいけれど,何のデータを入れようか思い付かない……ランダムデータだと練習する気にならなくて……公式のサンプルデータベースは英語で面白くない……あなたのMySQL,お漬物になっていませんか?

今回は,勉強用にMySQLに放り込むための(誰にでも手に入りそうで,意味がそれなりにわかりそうな)データをいくつか紹介します。飽くまでSQLの勉強用としてのデータですので,ストレステストに使うような類のデータではありませんので悪しからず。

デモンストレーション環境について

この回ではMySQLのインストールについては触れません。

この回で利用している環境は,CentOS 6.6にMySQL Yum Repositoryを導入し,執筆時点で最新のmysql-community-server-5.6.26-2.el6.x86_64をyumコマンドで以下の通りインストールしてあります。依存関係でmysqlコマンドなども一緒にインストールされている前提でいます。

$ sudo rpm -i http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
$ sudo yum install mysql-community-server

データの投入のためにワンライナーを使ったりLOAD DATA INFILEステートメントの不思議な構文を使ったりしていますが,それらを覚えたり気にしたりする必要はありません。⁠こんなサンプルデータはこんなクエリーの練習に使えるよ」という紹介ですので,興味があればMySQL :: MySQL 5.6 リファレンスマニュアルから詳細を探してみてください。

また,投入しているデータは「身近なものをMySQLにロードし,SQLを試す」ためのデータですので,⁠そのデータを本当にMySQLに投入するのが良いのか」などはあまり考えていません(あまり考えていませんが,テーブル設計は最後の2つを除いて一応考えてあります)⁠

サンプルデータその1,ls

さて,それではまず手始めに誰でもほぼ手に入れられそうなデータ……ということで,lsコマンドの出力結果をMySQLに入れてみます。

yumコマンドでMySQLをインストールした環境なので,MySQL関連のコマンドは/usr/binに入っています。この名前とファイルサイズをMySQLに入れてみましょう。繰り返しになりますが,ワンライナーはあまり気にしないでください。

mysql> CREATE DATABASE ls;
mysql> CREATE TABLE ls.ls(name varchar(255) NOT NULL, size int unsigned NOT NULL, PRIMARY KEY(name));
$ ls -l /usr/bin/my* | awk '{print "INSERT INTO ls.ls VALUES ('\''"$NF"'\'', "$5");"}' | mysql -u root
mysql> SELECT * FROM ls.ls;

ファイル名とファイルサイズを属性に持つテーブルにデータが入りました。WHERE name LIKE '%myisam%'でMyISAM関連のユーティリティーがあるなあ,とか,ORDER BY size DESCでファイルサイズの大きな順番に並べてみるとか,簡単なWHEREとORDER BYの練習に使ってみてください。

サンプルデータその2,vmstat

続いて,誰でもほぼ手に入れられそうなデータで時系列を持つものがいいかな……ということで,vmstatコマンドの出力結果をMySQLに入れてみます。

デモンストレーション環境がCentOS 6.6なので,他のディストリビューションやバージョンでは少し違うかも知れませんが,テーブルはこんなものを用意します(カラム名は出力結果と合わせるのではなく,何となく意味が分かるようにつけてあります)⁠

mysql> CREATE DATABASE vmstat;
mysql> CREATE TABLE vmstat.vmstat (
    ->   dt datetime NOT NULL,
    ->   running int unsigned NOT NULL,
    ->   blocking int unsigned NOT NULL,
    ->   swapped int unsigned NOT NULL,
    ->   free int unsigned NOT NULL,
    ->   buffer int unsigned NOT NULL,
    ->   cache int unsigned NOT NULL,
    ->   swap_in int unsigned NOT NULL,
    ->   swap_out int unsigned NOT NULL,
    ->   block_in int unsigned NOT NULL,
    ->   block_out int unsigned NOT NULL,
    ->   interapt int unsigned NOT NULL,
    ->   context_switch int unsigned NOT NULL,
    ->   cpu_user tinyint unsigned NOT NULL,
    ->   cpu_system tinyint unsigned NOT NULL,
    ->   cpu_idle tinyint unsigned NOT NULL,
    ->   cpu_iowait tinyint unsigned NOT NULL,
    ->   cpu_steal tinyint unsigned NOT NULL,
    ->   PRIMARY KEY(dt)
    -> );

ではデータを取ってみましょう。vmstatコマンドの出力結果を更に長くなったワンライナーでゴニョゴニョしています(本当は,こんなに長くなるくらいならPerlで書くのが筆者の好みですが)⁠

$ vmstat -n 1 10 | awk '$1 !~ /^[a-z]/{print "INSERT INTO vmstat.vmstat VALUES ('\''"strftime("%Y/%m/%d %H:%M:%S", systime())"'\'', "$1", "$2", "$3", "$4", "$5", "$6", "$7", "$8", "$9", "$10", "$11", "$12", "$13", "$14", "$15", "$16", "$17");"}' | mysql -u root

10秒待つとプロンプトが返ってきますCtrl+Cで止めてしまうとデータが投入される前にプロセスが終了してしまうので,待ってあげてください)⁠早速中身を確認してみましょう。

$ mysql -u root
mysql> use vmstat
mysql> SELECT * FROM vmstat LIMIT 1\G
*************************** 1. row ***************************
            dt: 2015-09-07 13:51:21
       running: 2
      blocking: 0
       swapped: 520252
          free: 90520
        buffer: 29632
         cache: 139960
       swap_in: 0
      swap_out: 0
      block_in: 18
     block_out: 41
      interapt: 16
context_switch: 12
      cpu_user: 0
    cpu_system: 0
      cpu_idle: 100
    cpu_iowait: 0
     cpu_steal: 0
1 row in set (0.00 sec)

dtカラムに記録された年月日時分秒がプライマリーキーで,出力結果のそれぞれの値がそれぞれのカラムに格納されています。

折角なので,他のターミナルでI/O負荷をかけるコマンドを叩いて,それを同じワンライナーでMySQLに記録してみました。GROUP BYを使って分単位で読み込みと書き込みの量を見てみます。

mysql> SELECT DATE_FORMAT(dt, '%H:%i') AS time, SUM(block_in), SUM(block_out) FROM vmstat GROUP BY time;
+-------+---------------+----------------+
| time  | SUM(block_in) | SUM(block_out) |
+-------+---------------+----------------+
| 13:51 |            18 |             89 |
| 13:54 |          4550 |            153 |
| 13:55 |         13758 |        6441349 |
| 13:56 |         16464 |       15020068 |
+-------+---------------+----------------+
4 rows in set (0.01 sec)

ちゃんと取得できているようです。

vmstatコマンドの出力結果は(標準的なLinux環境であれば)マシンが稼働している間中取得できますので,データの数を増やしていけば単一テーブルでSQL(主にGROUP BYを使用した集約関数でしょうか)の練習をするには充分身近ではないかと思います。

著者プロフィール

yoku0825

GMOメディア株式会社のDBA。日本MySQLユーザ会員。主に地雷担当。主食はMySQLだがPercona Server,MariaDB,InfiniDBにMroongaストレージエンジンなど,mysqldでありさえすれば雑食。

Twitter:@yoku0825

コメント

コメントの記入