データベースを勉強しなくてはいけない感じです。前職では基本的に既存の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が遅くなるらしいよ、みたいな話を聞いたとするわけです。初心者の僕は分からないからとりあえず調べるわけです。
なるほどインデクスの仕組みはそうなってるのか、じゃあvarcharでも変わらんやないのかと。
変わらないみたいですね（試してる人もいるし）。
でも、インデクスのデータが大きくなるっていうのはあるみたいですね。とか。でもほら、一応試してみようぜとなったとするじゃないですか。で、さっきの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は？とか調べるとまた新しい知識が…
なんだよデータベース、色々ありすぎてもうお腹いっぱいだよ！
とまあこんな調子で、じゃあ次はちょっとデータベーススペシャリストの問題解いてみようかなあ、平成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
