スベリミナル効果

技術的なことを書こうと思ってたけど何でも書くことにします


通常用

ふざけ用
何でもコメントください。誤り指摘コメントなどには「コメン(ゴメン)」ト誤ります(1スベリ)
ダジャレ好きの方はこちらにどうぞ(更新してないけど)→スベリブログ2.0

データベース学習の第0歩 ~すぐ試せるようにする~

データベースを勉強しなくてはいけない感じです。前職では基本的に既存のDBを使うか多少テーブルが増える程度だったし、オンプレの機器の中で大したデータ量も持たないDBだったので、負荷的な側面とかほぼ考えることが無かったわけです。


さーて、じゃあデータベースの何をどうやって勉強しようか、みたいなところは様々な側面があるのでここでは語らないとして、とにかく試してみたいときに試せるようにしたいじゃないですか。しかも、汎用的にしたいなと、色んなパターン試したいと。でも、毎回SQLコマンド叩くのもめんどい… ということで、現状の結論がコレでした。



mysqllib.sh (名前が適当なことに対する文句は受け付けておりません)

#!/bin/bash
. userpass.txt
database=testdb

do_init() {
    if which dnf > /dev/null ; then
        sudo dnf install mysql
    elif which apt-get > /dev/null ; then
        sudo apt-get install mysql-server
    fi
}
do_start() {
    sudo service mysql restart
    sudo mysql -u $USER -p$PASS -e "CREATE DATABASE IF NOT EXISTS $database"
}

_exec_sql() {
    sudo mysql -u $USER -p$PASS $database -e "$(set -f; echo $@)"
}

show_tables() {
    _exec_sql "show tables;"
}

case "$1" in
    init|i) do_init ;; # initial setup
    start) do_start ;; # start mysql server
    show) show_tables ;; # show tables
    sql) shift; _exec_sql "$@" ;; # exec sql
    create|create_sample) create_sample_table ;; # create sample tables
    t|test|test_sample) test_sample_query ;; # exec test query for sample tables
    clean|clean_sample) clean_sample_table ;; # clean sample tables
    *)  set +x
        echo "Usage: $0 {mode} [options]"
        echo "  mode:"
        # Usageの表示: 'xxx)  ${method} ;; ' をパースして出力するだけ
        sed -n -E "s/^[ \t]*([\|0-9a-z_-]+)\) *[a-z0-9 \$_\;-]+ *[^ ]* ;; *#* *(.*)/    \1 \t ... \2/p" ${BASH_SOURCE[0]}
        exit 1
        ;;
esac


余計な処理もありますが… WSLのUbuntu20.02で動いてると思います。一応userpass.txtにパスワードを逃している。適当に設定すれば不要になるでしょう。

USER=root
PASS=hogehoge


とりあえず実行してみる。

$ ./myssqllib.sh init
$ ./myssqllib.sh start
$ なんやかんやDB作ったり
$ ./myssqllib.sh show
+--------------------------+
| Tables_in_testdb         |
+--------------------------+
| TestIntKey               |
| TestVarcharKey           |
| 商品別出荷実績           |
| 当月商品出荷実績         |
| 月末商品在庫             |
+--------------------------+

こんな感じで動くわけですね。で、ミソは定義だけして使ってない関数です。例えば、DBのPKをIntじゃなくてvarcharで作ると、それがインデクスになり、インデクスがcharだとIntよりSELECTが遅くなるらしいよ、みたいな話を聞いたとするわけです。初心者の僕は分からないからとりあえず調べるわけです。

use-the-index-luke.com

なるほどインデクスの仕組みはそうなってるのか、じゃあvarcharでも変わらんやないのかと。

www.it-swarm.jp.net

変わらないみたいですね(試してる人もいるし)。

yakst.com

でも、インデクスのデータが大きくなるっていうのはあるみたいですね。とか。でもほら、一応試してみようぜとなったとするじゃないですか。で、さっきのmysqllib.shを使うとこんな感じに作れるんですね。


BigData.sh (糞ネーミング)

#!/bin/bash -xe
create_sample_table() {
    _exec_sql "
CREATE TABLE TestIntKey (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);
CREATE TABLE TestVarcharKey (
    id VARCHAR(100) PRIMARY KEY,
    name VARCHAR(100)
);"
_insert_data
}

_insert_data() {
    set +x
    n=0
    for i in {1..1000}; do
        sql=""
        for j in {1..100}; do
            n=$(($n+1))
            name=${n}_abcdefghijklmnopqlstuvwxyz01234567890
            sql=$sql"INSERT INTO TestIntKey (name) VALUES ('$name');INSERT INTO TestVarcharKey VALUES ('$name','$name');"
        done
        #echo _exec_sql $sql
        _exec_sql $sql
    done
}

test_sample_query() {
    name=9999_abcdefghijklmnopqlstuvwxyz01234567890
    time _exec_sql "select * from test_int_key where id=9999;"
    time _exec_sql "select * from test_varchar_key where id='$name';"
}

clean_sample_table() {
    _exec_sql "DROP TABLE TestIntKey, TestVarcharKey;"
}

. mysqllib.sh


実行してみると。

$ ./BigData.sh create #だいぶ待つよ
$ ./BigData.sh test
+------+--------------------------------------------+
| id   | name                                       |
+------+--------------------------------------------+
| 9999 | 9999_abcdefghijklmnopqlstuvwxyz01234567890 |
+------+--------------------------------------------+

real    0m0.007s
user    0m0.004s
sys     0m0.002s
+--------------------------------------------+--------------------------------------------+
| id                                         | name                                       |
+--------------------------------------------+--------------------------------------------+
| 9999_abcdefghijklmnopqlstuvwxyz01234567890 | 9999_abcdefghijklmnopqlstuvwxyz01234567890 |
+--------------------------------------------+--------------------------------------------+

real    0m0.006s
user    0m0.006s
sys     0m0.000s

./BigData.sh clean # すぐ消せる、createミスっててもすぐやり直せる


なるほど、よくわからんね(わからんのかい!)。分からんけど、まあ10万程度のこのレベルじゃなんの差も無さそうと。せっかくだから、テーブルの容量も見てみましょうか。show で show tablesをしてたけど、そこにテーブルサイズ取得するsqlも加えておこう、便利そうだし。

参考: MySQLでDBとテーブルのサイズを確認するSQL - Qiita


show_tables() {
    _exec_sql "show tables;"
    _exec_sql " SELECT
    table_name, engine, table_rows AS tbl_rows,
    avg_row_length AS rlen,
    floor((data_length+index_length)/1024/1024) AS allmb,
    floor((data_length)/1024/1024) AS dmb,
    floor((index_length)/1024/1024) AS imb
    FROM information_schema.tables WHERE table_schema=database()
    ORDER BY (data_length+index_length) DESC;"
}
$ myssqllib.sh show
(略)
+--------------------------+--------+----------+------+-------+------+------+
| TABLE_NAME               | ENGINE | tbl_rows | rlen | allmb | dmb  | imb  |
+--------------------------+--------+----------+------+-------+------+------+
| TestVarcharKey           | InnoDB |    96654 |  223 |    20 |   20 |    0 |
| TestIntKey               | InnoDB |    99740 |   79 |     7 |    7 |    0 |
| 当月商品出荷実績         | InnoDB |        6 | 2730 |     0 |    0 |    0 |
| 月末商品在庫             | InnoDB |        5 | 3276 |     0 |    0 |    0 |
| 商品別出荷実績           | NULL   |     NULL | NULL |  NULL | NULL | NULL |
+--------------------------+--------+----------+------+-------+------+------+


ふむう?確かに、データ量的には2倍くらいになるはずだが、インデックスされる分含めて3倍になってるっぽいですね(合ってるのかな…)

ただ、tbl_rowsは同じ値になるはずなんだけどな…一応確認してみよう、というときにもmysqllib.shを使えるんですね。

$ ./mysqllib.sh sql "select count(*) from TestVarcharKey;"
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
$ ./mysqllib.sh sql "select count(*) from TestIntKey;"
+----------+
| count(*) |
+----------+
|   100000 |
+----------+

(※アスタリスク展開されないようにダブルクオートで囲んでね。中身では展開無理やり回避してますが…駄目なケースもあるかもね…)


合ってるやん。なんやねんそのtbl_rowsは?とか調べるとまた新しい知識が…

blog.d-shimizu.io

なんだよデータベース、色々ありすぎてもうお腹いっぱいだよ!



とまあこんな調子で、じゃあ次はちょっとデータベーススペシャリストの問題解いてみようかなあ、平成29年の午前IIの10問目、書いてみねーと分からんわこんなん!みたいときも、myssqllib.shを使うと

#!/bin/bash -xe

create_sample_table() {
    _exec_sql "
CREATE TABLE 月末商品在庫 (
    商品コード VARCHAR(100) PRIMARY KEY,
    商品名 VARCHAR(10),
    在庫数 int
);
CREATE TABLE 当月商品出荷実績 (
    商品コード VARCHAR(100),
    商品出荷日 date,
    出荷数 int
);
CREATE VIEW 商品別出荷実績 (商品コード, 出荷実績数, 月末在庫数)
    AS SELECT 月末商品在庫.商品コード, SUM(出荷数), 在庫数
    FROM 月末商品在庫 LEFT OUTER JOIN 当月商品出荷実績 ON 月末商品在庫.商品コード = 当月商品出荷実績.商品コード
    GROUP BY 月末商品在庫.商品コード, 在庫数;

INSERT INTO 月末商品在庫 VALUES ('S001', 'A', 100);
INSERT INTO 月末商品在庫 VALUES ('S002', 'B', 250);
INSERT INTO 月末商品在庫 VALUES ('S003', 'C', 300);
INSERT INTO 月末商品在庫 VALUES ('S004', 'D', 450);
INSERT INTO 月末商品在庫 VALUES ('S005', 'E', 200);
INSERT INTO 当月商品出荷実績 VALUES ('S001', '2017-03-01', 50);
INSERT INTO 当月商品出荷実績 VALUES ('S003', '2017-03-05', 150);
INSERT INTO 当月商品出荷実績 VALUES ('S001', '2017-03-10', 100);
INSERT INTO 当月商品出荷実績 VALUES ('S005', '2017-03-15', 100);
INSERT INTO 当月商品出荷実績 VALUES ('S005', '2017-03-20', 250);
INSERT INTO 当月商品出荷実績 VALUES ('S003', '2017-03-25', 150);
"
}
test_sample_query() {
    _exec_sql "SELECT SUM(月末在庫数) AS 出荷商品在庫合計 FROM 商品別出荷実績 WHERE 出荷実績数 <= 300;"
}
clean_sample_table() {
    _exec_sql "DROP TABLE 月末商品在庫, 当月商品出荷実績; DROP VIEW 商品別出荷実績;"
}

. mysqllib.sh


これだけで動き確かめたりできるわけですね。いや、写経は要りますが、間違えたとか後でもう一回確認したいとかスクリプトにしておくとなんだかんだ便利なのでね。これで今後はSQLのことだけに集中できるってもんです。いやー、我ながらこれは便利やなー。




あれ?私がこれからメインで使うのって、SQLServerだったような… orz