データベースとは

一定の形式で整理されたデータの集まりです。データベースを効率よく管理、運用するソフトウェアをデータベース管理システムと言います。

MySQLとは

MySQLは、オープンソースで公開されている関係データベース管理システム(リレーショナルデータベース)です。リレーショナルデータベースを略して、RDBと言ったりします。
オープンソースのRDBには,MySQLの他にSQLiteやPostgreSQLなどがあります。
こちらのサイトでは、データベースランキング2位になっております。
MySQLはWordpressにも使われています。

MySQLの特徴

• wordpressで使われている
• オープンソースで無料
• Windows、Mac OS、LinuxなどOSで利用が可能
• 大規模なサービスでも運用可能
• GUIツールがある(phpMyAdmin、MySQL Workbenchなど)

環境構築

Homebrew をインストール(インストール済みの方はしなくていいです)

HomebrewはMacのバージョン管理ツールです。Mysqlをインストールするために使います。

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

mysqlをインストール

brewコマンドでMysqlをインストールします。

brew install mysql

mysqlのサーバーを起動

mysqlのサーバーを下記のコマンドで起動しましょう。正常に作動がすれば、successと表示されます。

mysql.server start
Starting MySQL
 SUCCESS!

mysqlにログイン

mysqlにはIDとパスワードがあり、デフォルトでは、IDがrootで、パスワードが未設定となっております。なので、下記のコマンドでログインすることができます。

$ mysql -u root -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.18 Homebrew

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

MySQLから抜け出す

MySQLにログインできたら、mysql>のようなコマンドが左側に表示されます。exitコマンドを入力すると抜け出すことができます。

mysql> exit
Bye

パスワードを設定

mysqlのパスワードがないとセキュリティ上、よくないので、下記のコマンドでパスワードを設定します。

mysql_secure_installation

パスワード付きのユーザーにログイン

パスワード付きのユーザー(今回はroot)にログインするには、下記のようなコマンドを実行します。

mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.18 Homebrew

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

データベースシステム用語集

データベースとテーブル

データベースは複数のテーブルを持っています。基本的にプロジェクトごとに1つのデータベースを持ちます。
例えば、「test-project」というデータベースがあり、その中に複数テーブルを作ります。
テーブルは入れるデータの種類ごとに別々の名前でテーブルを作成します。
例えば、ユーザーのメールアドレスやパスワードを入れるテーブルを「users」テーブルと定義したり、投稿データを入れるテーブルを「posts」テーブルとしたり、タスクデータを入れるテーブルを「tasks」テーブルとしたりします。

カラムとレコード

カラムとは、テーブルの縦の列のことを言います。カラムごとで、入れるデータの種類を定義します。
レコードはテーブルの横の行で、実際にデータを追加していきます。

コマンドを実行してデータベースを操作しよう

データベースの一覧を表示

rootユーザーでmysqlにログインして、SHOW DATABASESを実行して、データベース一覧を表示しましょう。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.26 sec)

データベースへの作成

新しくデータベースを作成しましょう。

mysql> CREATE DATABASE test_project;

データベースの作成ができたら、確認しましょう。

mysql> SHOW DATABASES;

test_projectのデータベースを選択

mysql> USE test_project;

テーブル一覧を表示

テーブルは今のところないので「Empty set (0.01 sec)」と表示されれば良いです。

mysql> SHOW TABLES;
Empty set (0.01 sec)

テーブルを作成

mysql> CREATE TABLE users(
    id INT(11) AUTO_INCREMENT NOT NULL, 
    name VARCHAR(30) NOT NULL ,
    age INT(3) NOT NULL,
    PRIMARY KEY (id));

Query OK, 0 rows affected, 2 warnings (0.04 sec)

テーブルの一覧表示

mysql> show tables;
+------------------------+
| Tables_in_test_project |
+------------------------+
| users                  |
+------------------------+

参考

https://www.sejuku.net/blog/8763#i
https://www.kagoya.jp/howto/webhomepage/mysql/

この記事は ラクス Advent Calendar 2019 の5日目の記事です。

弊社、株式会社ラクスパートナーズ は特定派遣の会社なのでいくつかの現場に行かせてもらっているのですが、いまだに MySQL8.0 を使用している現場は見たことがありません。まあ、MySQLに性能や機能を求めてない現場ばかりだったというのもりますが。

本記事ではMySQL8.0で追加されたWITH句(CTE)を紹介します。速度面はではく、SQLの可読性の面を取り扱いMySQLのバージョンアップでミドル自体の性能向上以外にも、コードの可読性、メンテ性の向上も図れることを示せたらなと思います

本記事は公式ドキュメントに添います。引用はこのドキュメントからになります
MySQL :: MySQL 8.0 Reference Manual :: 13.2.13 WITH (Common Table Expressions)

使用するデータも公式にあるサンプルを使用します。下記サイトの Expert Guides - world database にあるデータを使用しています
MySQL :: Other MySQL Documentation

WITH句

WITH句はサブクエリ結果に名前をつけ、クエリ実行中のみ有効な一時テーブルを作成する機能です。使い方は他のDBMSと変わりません

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

このSQLではWITHのブロックで cte1、 cte2 の二つの名前のついた一時テーブルを作成します。以降のSELECT文ではその名前を使って一時テーブルに対し操作を行っています

例

スペイン語を話すヨーロッパの都市を抽出する WHERE句の副問合せ、JOIN句、WITH句 を用いた例を示します

WHERE句の副問合せ

副問合せは読みづらいし、書きづらいです。データ量が多くなれば速度も出ません。

SELECT * FROM city WHERE CountryCode IN (
  SELECT CountryCode FROM countrylanguage
  WHERE Language = 'Spanish' AND CountryCode IN (
    SELECT Code FROM country WHERE Continent = 'Europe'
  )
);

JOIN句

いままでよくやる手法です。副問合せよりマシですが、JOIN一つ一つ追わないと意図の把握ができません

SELECT a.* FROM city a
JOIN countrylanguage b
  ON b.CountryCode = a.CountryCode AND b.Language = 'Spanish' 
JOIN country c 
  ON c.Code = b.CountryCode AND c.Continent = 'Europe';

WITH句

JOINでつないでいたテーブルをWITH句に切り出し意味の分かるエイリアスを付けました。SELECT文は簡潔になり、WITH句を読まなくても意図がわかると思います

トランザクションが使える場面では似たことを TEMPORARY TABLE を使って行うと思いますが、WITH句を使うことで1つのクエリ内で完結できるのがいいですね

WITH europe_using_spanish AS (
  SELECT a.CountryCode FROM countrylanguage a
  JOIN country b ON b.Code = a.CountryCode 
    AND a.Language = 'Spanish' AND b.Continent = 'Europe'
)
SELECT a.* FROM city a
JOIN europe_using_spanish b ON b.CountryCode = a.CountryCode;

---

示したのは簡単な例でしたがそれでも違いが出るので、うまく使えることができれば可読性が高くメンテナンス性の高いクエリを書けるようになると思います。

新機能も性能も上がった新バージョンがあるのに、前バージョンでインデックスやパーテションをいじって速度アップを図るのは疲れますよね。MySQL8.0に上げることで性能だけでなくSQL、アプリのコードの改善も可能になりますヨ。ということで、バージョンアップの検討材料の材料になればと思います。

GCPのMYSQLを使うと、非常に料金が高い。
比べてみると、

同じレベルの速度を出すサーバー。
conoha の 1,750円/月 に比べると、 10,000円/月 位に跳ね上がる。
なんと6倍だ。

しかも、1つならまだしも2つとなると、さらに金がかかる。

自分で作ったサーバーは安定しない。。。
と思ってGCPに乗り換えたが、結局redisの設定間違っていて攻撃されまくっていたのが原因だと判明したので、conoha に戻すことにした。

そこで、エクスポートしたときのメモ。

・GCPのデータエクスポート時は、サーバーは止まらない。
・バケットにエクスポートされるので、ファイル名をダウンロードして取得
・1つのMYSQLに複数のデータベースを入れているとうまくインポートできない場合がある。

その際は、DBを一つずつエクスポートしてインポートを繰り返そう。

テーブルコメントの参照方法

-- 簡易ならこっち
show table status like '【テーブル名】';

-- 出力項目絞りたいならこっち
select TABLE_NAME, TABLE_COMMENT
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = '【スキーマ名(DB名)】' and TABLE_NAME = '【テーブル名】'
;

カラムコメントの参照方法

-- 簡易ならこっち
show full columns from 【テーブル名】;

-- 出力項目絞りたいならこっち
select TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = '【スキーマ名(DB名)】' and TABLE_NAME = '【テーブル名】'
order by ORDINAL_POSITION
;