Closed2023/09/24にクローズ138

データベース関連の用語をざっくりまとめる

ハガユウキ

■マイグレーション
マイグレーションという言葉はソフトウェア業界的には、データマイグレーションのことを指していると思う。
データマイグレーションとは、データを別の環境に移動させるプロセスのこと。データの内容自体はそのままでマイグレーションする場合と、異なる環境で扱えるようにデータを変換してマイグレーションする場合がある。Railsのマイグレーション機能だと、カラムの追加や削除もやってくれるので、Railsの場合、新しい環境を作るプロセスもマイグレーションって言葉に含まれている気がする。

■スキーマ
スキーマとは、データベースにどのような種類のデータをどのような構造で格納するかを定義したのもの。スキーマは、スキーマファイルに書かれている。スキーマファイルを想像するとイメージしやすい。

https://e-words.jp/w/スキーマ.html

ハガユウキ

https://knooto.info/erd-crows-foot/
鳥の足記法毎回忘れて、その度にこの記事１００回以上見ている気がする笑

ハガユウキ

railsだとenumを使うのが一般的だが、他のやり方としては別テーブルとして切り出すのが良いのか、

ハガユウキ

Mermaid.jsについてはこの記事がわかりやすい。

ハガユウキ

mysqlのtimestamp型とdatetime型の違いを今度ふかぼってみたい。
timestampは2038年問題的なのがあるが機能性は高い。
datetimeを基本的には使う方針で良さそう

ハガユウキ

mysqlの文字列の型はtextかvarcharが一般的なのかな
varcharは最大文字数を指定しないといけなくて、その文字数以上の文字が入れられたときに辛いのかなと思った、、
電話番号とか、明らかに最大文字数が決まっているのはvarcharで別に良いが、他はtextにしておくのが良いのか、、
調べると、varchar(255)が一般的っぽいので、それを使うか

railsのstringはvarchar(255)を表していたのか。てなると、やっぱvarchar(255)は一般的だな

ハガユウキ

my sqlだとこれで、今月の日付を取得できるくさい
https://hacknote.jp/archives/10377/

SELECT 
    * FROM
    my_table
WHERE
    DATE_FORMAT(create_date, '%Y%m') = DATE_FORMAT(NOW(), '%Y%m')

ハガユウキ

「UNION ALL」を使うことで、複数の検索結果を重複を許して統合し、1つの結果として取得することができる
UNIONだと、重複したレコードは統合しちゃう
この記事がわかりやすかった
https://medium-company.com/sql-union/

ハガユウキ

オプションはオプショナルメニューというらしい。なるほど

ハガユウキ

■マルチカラムアトリビュート
マルチカラムアトリビュートとは、複数の値を取りうるような属性をテーブルに格納するために、テーブルに似たようなカラムを複数個用意してしまうテーブル設計のアンチパターン。この設計で開発を進めると色々うまくいかなくなる

値のパターンが増えた時に、また似たようなカラムを無限に追加しないといけない未来がやってくる
ある値だけ持つSQL文を書く場合、NULLを大量に書かないといけなくなる
https://catpot.dev/db-multi-column-attribute/
解決策は中間テーブルを用意すればOK

ハガユウキ

SQLでjoinをする場合、indexの有無や統計情報(データの量・分布)から最適なアルゴリズムが以下の３種類の中から選ばれる。ちなみにpostgreは3種類全部サポートしているけど、MySQLはNested Loop Joinしかサポートしていない

- Nested Loop Join (遅い)
  - テーブル1とテーブル2に対してすべての組み合わせを試す
  - 計算量はO(n * m)なので、そこそこ遅い
- Hash Join(普通)
  - テーブル2に対して、1度フルスキャンしてHashMapを作る
  - 計算量はO(n + m)なので、Nested Loop Joinよりはマシ
- Merge Join (早い)
  - ソート済のテーブル1とテーブル2に対して一度だけフルスキャン
  - JOINに使うカラムにはindexを貼る
  - 計算量はO(n + m)だが、処理的には、Hash Joinより早いそう

ハガユウキ

■ビュー

ビューとは、実テーブルから作成される仮想的なテーブルのこと。ビューは、 CREATE VIEW文で定義することができて、ビューの名前だったり、どんなSELECTを文を実行するのかを指定する。
ビューはFROM句に指定する。
ビューからデータを取り出そうとすると、ビューに定義したSELECT文が実行されて、仮想的なテーブルが作られる

SELECT *
FROM ビュー名

ビューのメリット
- 実際にテーブルを作ってデータを保存しているわけではないので、ハードディスクの容量を節約できる
- 集計する際によく使うSELECT文とかをビューで定義することで、簡単に利用することができる。その結果、GROUP BYとかCOUNTを使うSELCT文を毎回書かなくて良くなる
ビューを使う上での注意点
- ビューの中でビューを使うことはできなくはないが、パフォーマンスが落ちるので、基本やらない方が良い
- ビュー定義ではORDER BYが使えないそう(未検証)

ハガユウキ

■サブクエリ

サブクエリとは、一言で言うと、一時的に利用するためのビュー。サブクエリにはASで名前をつける必要がある。
普通のサブクエリ以外にも、単一列かつ単一行を返すスカラ・サブクエリなど、相関サブクエリなどがある
スカラサブクエリのスカラとは、『単一』のって意味。スカラサブクエリの良いところは、単一の値なので、比較演算子に対して使えること。
相関サブクエリとは、外側のクエリの値をサブクエリ内で使用する様なクエリのこと。しかし普通のサブクエリの方がパフォーマンスが良かったりするので、相関サブクエリの使い道はあまりないかなという印象。相関サブクエリで書けるクエリは、別のクエリで書き換えられるし。

ハガユウキ

EXPLAINとは、「クエリを発行してどんな処理が行われたのかを表す結果」（実行計画という）を取得するためのステートメント。
実行計画を確認しながら、クエリを書くと、パフォーマンスの良いクエリを描きやすい

ハガユウキ

ワンちゃんSUM(oi.quantity - oi.cancel_quantity)って書き方ができるぽい

ハガユウキ

■Docker Engineについてもうちょい深ぼる
Docker Engineは三つの構成要素でできている

Docker CLI　(docker run とか)
REST API
Dockerデーモン(実際にコンテナを作ったり、イメージビルドをしたりするもの)

docker run等のDocker CLIを実行すると、UNIXドメインソケット/var/run/docker.sockを介してREST APIが叩かれ、Dockerデーモンが実際にコンテナを立てたり、イメージビルド等の処理を行います。

UNIXドメインソケットとは、ファイルシステムを介してプロセス間通信を行うための仕組みのこと。プロセスはサーバーのメモリ上で実行中のプログラムのこと。

ハガユウキ

昔、テーブル設計の仕事をした時に、20年やっているめちゃつよ先輩に、『仕様がこうでも、実装(この場合だと設計)はそれに引きづられる必要性はない』って言われて、ぐうの音も出なかったな笑
仕様があらかじめ決まってても、ビジネスの都合上で仕様が変わることはいくらでもあるので、柔軟に変更できる設計ってすごいなって初めて感じた瞬間でした笑(柔軟に変更できると、ビジネスの仕様変更にも強いし、その仕様変更に付随する追加の開発や修正とかも最小限に収まる。結果的に早くプロダクトをユーザーに届けることができる)

あと、昔オブジェクト指向の本を読んだ時、未来に何が起こるかを想定して設計するのではなくて、未来に何が起こっても柔軟に変更できる様に設計すると良いよってのを見て、その時すごくハッとしたのを思い出した笑
設計って奥が深い、、

ハガユウキ

■普通にイメージからdockerコンテナを作るパターン
MySQLのイメージからコンテナを作る(dstはdestinationの略)

docker run --name db -it  --env-file ./database_design/db_modeling_1/part_3/.development.env --mount type=volume,src=mysqlvolume,dst=/var/lib/mysql mysql

このコマンドを実行する前にmysqlvolumeを作っておく

ハガユウキ

docker run オプション イメージ名 実行したいコマンド

docker runコマンドは、「docker pull」、「docker create」、「docker start」という3つの一連のコマンドをまとめて実行する利便性を重視したコマンド。「実行したいコマンド」は省略でき、省略したときは、イメージの制作者が設定した既定のコマンドが指定されたことになる。hpptdイメージの場合、コマンドを省略すると、「Apacheを実行し、通信を待ち続けるためのコマンド」が実行される

docker execコマンドとは、指定したコンテナの中でプロセスを実行するためのコマンド

docker exec -it 稼働中のコンテナ名 実行したいコマンド

exitコマンドで「実行したいコマンド」を終了させることができる。このコマンドを終了させただけなので、コンテナ自体は起動したままである。
動作中のコンテナの中に入って作業したい場合、コンテナのなかでシェルを実行すればOK

docker exec -it db bash

ハガユウキ

MySQLでは、ユーザーをユーザー名＋接続元ホスト名で管理している。MySQLにログインするためには、以下のコマンドを実行する　-pに直接パスワードを指定しないのは、指定するとコマンド履歴に残ってしまうため。-uの後ろにはログインするユーザー名を指定する。-hで接続元ホスト名を指定できる。-hを省略した場合、接続元ホストはlocalhostと判定される

mysql -u root -p

ハガユウキ

My SQLのログインユーザー情報は、mysqlという名前のデータベースのuserテーブルに格納されている。

show databases;で全てのDBを確認できる。
select database();で現在使っているDBを確認できる
show tables;で現在使っているデータベースにある全てのテーブルを確認できる
use DB名;でどのDBを使うか選択できる
select current_use()rで現在ログインしているユーザーを確認できる
create database データベース名でデータベースを作れる
create table users ( id int, name varchar(255) );でusersテーブルを作れる

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

mysql> select user, host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

mysql> select current_user()
    -> ;
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+

ハガユウキ

docker inspectでコンテナの詳細情報を確認できる。grepする際に -数字をオプションで加えると、ヒットした行の前後の行を取得できる。Aを追加すると後ろの行だけ、Bを追加すると前の行だけ取得できる

docker inspect db | grep Mounts -A2
        "Mounts": [
            {
                "Type": "volume",

ある項目のある場所だけみたいなら、docker inspectにformatオプションを指定すると良さげ

docker inspect db --format="{{.NetworkSettings.IPAddress}}"
172.17.0.2

ハガユウキ

docker run --name db -it  -e MYSQL_ROOT_PASSWORD=password mysql

このコマンドでもMySQLのDBコンテナは作れるが、マウントをしていないので、コンテナを削除するとDBのデータが消える。コンテナを停止⇨再スタートすると、コンテナの削除はしていないので、DBのデータは消えない。てなると、やっぱDBコンテナ作るならマウントした方が良いか。

ハガユウキ

改めて復習しよう
■マウントとは
ホストのディレクトリやボリュームを、マウントオプションを指定したコンテナのあるディレクトリに共有すること。マウントしている間、コンテナの元のディレクトリの内容は隠され、ホストのディレクトリやボリュームの情報がコンテナのマウント先のディレクトリに共有される。マウントしている間にコンテナを操作して、マウント先のディレクトリに変更を加えた場合、マウント元のディレクトリやボリュームにも反映される(逆も言える)。マウントを解除すると、コンテナのディレクトリは元のディレクトリに戻る。マウント元のディレクトリやボリュームには変更が残ったままになる(これはバインドマウントで検証済み)。コンテナを削除してもデータを消したくない時や、Docker ホストでコードを変更したらコンテナに自動で反映されてほしい時にマウントを行う。コンテナを停止しただけだったら、マウントの設定は消えなかった。コンテナを削除したらマウントの設定は消える(コンテナを消したらそもそもコンテナが消えるので、これは正常な挙動)。マウントは主にバインドマウントとボリュームマウントの２種類がある

■バインドマウント
Dockerホスト(Docker Engineをインストールしたコンピュータ)に、あらかじめディレクトリを作っておきそれをコンテナのあるディレクトリにマウントする方法
ソースコードとかは書き換えながら挙動を確認したいので、バインドマウントを使う。そうしないと、毎回ファイルを書き換えるたびにコンテナを作り直す羽目になっちゃう

docker run --name db -it --mount type=bind,src=$(pwd)/database_design/db_modeling_1/part_3/mount,dst=/home -e MYSQL_ROOT_PASSWORD=password mysql

■ボリュームマウント
Docker Engine上で確保した領域(ボリューム)をコンテナのあるディレクトリにマウントする方法
Docker ホストのディレクトリ構成を意識せずにマウントしたかったり、Dockerホストから変更させたくないようなファイルをマウントする際に使うと良さげ。DBコンテナのデータを
保存する場所としてボリュームは適しているので、DBコンテナを作る際はボリュームマウントをした方が良い。DBコンテナをマウントしないで起動すると、DBコンテナを消したときに、データも消えるので、データを別の部分で安全に保存するために、DBコンテナを作るときはボリュームマウントをした方が良い。ボリュームマウントをするなら、事前にボリュームを作っておく必要がある。

docker volume create mysql-data
docker run --name db -it  -e MYSQL_ROOT_PASSWORD=password --mount type=volume,src=mysql-data,dst=/var/lib/mysql mysql

なるほど、匿名ボリュームって概念があるのか、、
MySQLに限らずPostgreSQLの公式イメージも自動的に匿名volumeを作るようになっています。
だから、MySQLイメージをボリュームマウントせずにdocker run したら、よく分からんボリュームができていたのか、、
ボリュームマウントしてdocker runしたら匿名イメージはできなかったが、バインドマウントでは匿名ボリュームができていた。また、何もマウントオプションを指定せずにrunしても、匿名ボリュームが作られた。
つまり、ボリュームマウントのオプションを指定してたら、そっちのボリュームが優先されるってことか。

ハガユウキ

--mountオプションを初めて知った、、いつも-vでどうにかやってたけど、わかりやすくて良さげ。シンプルさを求めるなら、ーvのが良いけど

ハガユウキ

■ホストとは
ネットワークに繋がれたコンピュータのこと
■Dockerホストとは
Docker Engineをインストールしたコンピュータのこと
■Dockerで仮想ネットワークを作るとどんなことができる
dockerではbridgeネットワーク、hostネットワーク、noneネットワークの３種類の仮想ネットワークが最初から存在している。bridgeネットワークをよく使う

docker network ls
NETWORK ID     NAME             DRIVER    SCOPE
32e9146a3beb   bridge           bridge    local
048f3a824be8   host             host      local
c6e23346cd39   none             null      local

自分でオリジナルのDocker ネットワークを作ることもできる
■ネットワークとは
複数のコンピュータを繋いでデータの送受信できるようにするもの

ハガユウキ

■bridgeネットワークとは

dockerの基本的なネットワーク。dovker runするときにネットワークのオプションを指定しないと、このネットワークにコンテナが自動で属すことになる
dockerホストやDocker コンテナはこの一つのbridgeネットワークに属す。コンテナはネットワークに属しているので、IPアドレスが割り当てられている
コンテナにどんなIPアドレスが割り当てられているかを見るには、docker inspect コンテナ名をすればOK。もしくは、docker network inspect bridge をやって、bridgeネットワークにどのコンテナが属しているかを確認する際に、IPアドレスの情報も載っているので、そこで確認する。
Dockerホストはdocker0というネットワークインタフェースを通じて、bridgeネットワークに接続しているそう。
bridgeネットワークでは、IPマスカレードを使って、Docker ホストの１つのIPアドレスと複数コンテナが持つプライベートIPアドレスを紐づけているそう
同じbridgeネットワークに属してるコンテナたちは、自分自身に割り当てられているIPアドレスを使って互いに自由に通信できる。(pオプションとかは、Dockerホストのポートとコンテナのポートをマッピングするためのオプションなので、コンテナ同士の通信には関係ない。なので、そこの心配は不要)

// -c 4は４回指定したら終わるオプション
// 0% packet lossに慣ればちゃんとコンテナ間で疎通できている
ping -c 4 コンテナのIPアドレス

コンテナを破棄したらIPアドレスが変わる恐れがある。あと、コンテナ名で通信がしたい。そのような場合、オリジナルのDockerネットワークを作ると解決する。

■Dockerネットワークとは

Dockerで作れる任意のネットワークのこと。Docker ネットワークを作ると、その数だけ、Docker ホスト上には、br-(DockerのネットワークIDの先頭)という名前のネットワークインターフェースが作られる。おそらくこのインターフェースを通じて、Docker ホストがDocker ネットワークに参加している。
Dockerネットワークは以下のコマンドで作れる。IPアドレスの範囲を指定しない場合、既存のネットワークと重複しない適当なIPアドレスの範囲が使われる

docker network create ネットワーク名

コンテナをDockerネットワークに属させるためには、コンテナを作る前なら、docker run時に --netオプションを使って、--net Dockerネットワーク名を指定する。既存のコンテナを他のネットワークに属させたいなら、以下のdocker network connectコマンドを使う。検証してみて、一つのコンテナが複数のネットワークに属すことはできたが、一つのコンテナは一つのネットワークにだけ属させた方が分かりやすくて良さそう。

// コンテナをネットワークに繋げる
docker network connect ネットワーク名　コンテナ名

// コンテナをネットワークから切断する
docker network disconnect ネットワーク名　コンテナ名

ハガユウキ

■Dockerの仮想ネットワークはいつ使うのか
基本的には１コンテナ１プロセスなので、複数のプロセスを組み合わせてシステムを作りたいときに使う。それぞれのコンテナを一つのネットワークに属させることで、コンテナ間で通信ができる。まだ、バックエンドアプリケーションのコンテナからDBコンテナへクエリを投げたりとかがイメージできてないので、そこら辺はおいおい詰めていく

ハガユウキ

ハブとかスイッチとかそこら辺のネットワークの知識が薄いので、結構まだあやふやに理解しているなという印象
Dockerは、composeよりネットワークのがむずい気がする笑

ハガユウキ

書いてて思うけど、この書いたことって大体忘れるから、コードベースで理解できるならコードで理解したほうがいいなと思った。高校数学の公式や定理ってめちゃくちゃ覚えないといけないってイメージだけど、照明を理解できれば、中学数学の知識だけで完結するから、そもそも覚える必要がないんよな。それと一緒で、ソフトウェアも結局何かしらの言語で書かれているわけだから、コード読んでどんな仕組みで動いているのかさえ分かれば、覚えるのってもはや言語だけなんよね。コードを読む過程でいろんな学びもある。もしコードを読んで仕組みから理解しないと、こうすればこうなるや、これはこうですなどを無限に覚えないといけなくなる。枝を集めている感じ。幹があれば、枝の知識は幹から説明できるのでもはや覚える必要もないし、新しい枝が出てきても幹からどんな振る舞いをするのか、どんな挙動をするのかを瞬時に理解できる。コードを読んで仕組みから理解するのはめちゃくちゃ大事だなと改めて思った(仕組みをコードから理解したら今度はそれを作ってみると余計身につくかも)。

ハガユウキ

■Dockerfileからイメージを作る方法

Dockerfileを使ってイメージを作る場合、「イメージに含めたいファイル」と「Dockerfile」を一つのディレクトリに置く。その後docker buildを実行すればOK
Dockerfileは、ベースイメージ(元となるイメージ)に対して、どのような変更を加えるかを書くファイル。
Dockerイメージは、一連のイメージレイヤーから構築される。Dockerfileに書いた一つの命令コマンドごとに一つのイメージレイヤーが作られる。イメージレイヤー自身もイメージであり、そのイメージレイヤーを多重に積み重ねることで、作りたい最終形のイメージとなる。ちょいややこしいのが、docker image hisotryコマンドでイメージがどんなレイヤーで構成されるかを確認できるが、FROM命令ではベースイメージを指定しているので、一つのレイヤーが表示されるって感じではなく、FROM命令に指定したベースイメージのレイヤーが表示される。
このディレクトリに置いているファイルはイメージに含まれてしまうので、使わない余計なファイルはこのディレクトリに置かないようにする。
Dockerfileからイメージを作成することを「ビルド」と呼ぶ
Dockerfileを置いたディレクトリをカレントディレクトリとして、以下のビルドコマンドを実行する。このとき作成するイメージ名及びタグ名は、-tオプションで指定する

docker build -t praha-sample .

docker image hisotry イメージ名でイメージのレイヤーを確認できる。
docker commitでコンテナからイメージを作ることもできるが、こちらの場合、Dockerfile的なのがないので、どのような操作が行われてきたのかが分からない。あと、docker image hisotryを実行しても、イメージに対してどのような操作が行われてきたかの履歴が残ってないので確認できない。
作成したイメージからコンテナを作ることができる(これは普通)

ハガユウキ

■COPY命令

COPY命令は、Dockerfileを置いたディレクトリ内のファイルやフォルダをイメージにコピーしたいときに使う。ADD命令と似ているがADD命令は挙動がわかりづらいためCOPY命令のが推奨されているそう。
■RUN命令と「CMD、ENTRYPOINT」の違い
RUN命令はdocker buildを実行するタイミング(つまり、イメージを生成するとき)に実行する。イメージの時点で実行しておきたいコマンド（パッケージやライブラリのインストール、ファイルコピー、変更など）を実行するために書く。
CMDとENTRYPOINTは、コンテナを起動したタイミングで、コンテナの中で実行したいコマンドを実行するために書く。
一つのRUNにまとめたい場合、シェルの&&を使う

RUN コマンド1 && コマンド2 && コマンド3

行が長くなってしまう場合、行を分割できる\を使う

RUN コマンド1 \
&& コマンド2 \
&& コマンド3

■CMD命令とENTRYPOINT命令の違い

前者はコマンドの指定を任意にする(指定しなかったらデフォルトで指定してあるコマンドが実行される)。後者はコマンドの指定を強制とする。なので、CMDの方が推奨されている。
CMDやENTRYPOINTを指定しない場合、ベースイメージの設定値が引き継がれる。
CMDやENTRYPOINTは、それぞれ、Dockerfileに一つしか記述できない
コンテナをずっと起動しっぱなしにしたいなら、CMDやENTRYPOINTには、「ずっと動きっぱなしでいるコマンド」を指定する必要がある。
■RUNを複数回書くと何がいけないのか？
RUNを複数回かくと、それに応じてレイヤーが作成される。‘毎回 layer を作成する == 全コマンド結果保存’（キャッシュ）ということでもあるので、イメージのサイズが大きくなりがち。
まとめないことによって、どの部分でbuildに失敗したとかがわかりやすくなる。
イメージのサイズがデカくなると、以下の問題点がある
ストレージを圧迫する
Docker HubやAWS ECRなどのレジストリにpush/pullする際に時間がかかる
ビルドに時間がかかる
結果的にイメージのサイズが小さい方が、生産性だったり、サービスの提供スピードが上がるので、小さいことに越したことはない。
ベースイメージを軽量なものを選ぶ
マルチステージビルドを使う
不要なパッケージやライブラリをインストールしない
RUN命令をなるべく一回で書く(こいつに関してはケースバイケース。キャッシュが作られると良いパターンもあるので)
これをするだけでもイメージのサイズが改善されるそう。

■VOLUME命令
VOLUME命令は、永続化を期待しているディレクトリをイメージの利用者に伝えるための命令。
■EXPOSE命令
EXPOSE命令は、コンテナがどのポート番号を受け付けるようになるかを指定する。EXPOSEしただけだとDocker ホストからコンテナにはアクセスできないので、-pオプションでホストのポートとコンテナの公開しているポートをマッピングしてあげる必要がある。EXPOSEはポートマッピングを強要するものではなく、どのポートを公開する意図なのかというのを伝える、ある種のドキュメントとしの役割を担う。
■Dockerfileのキャッシュについて
レイヤーごとにビルド工程をキャッシュしているので、もう一度イメージをビルドすると、そのキャッシュが使われて、早くイメージビルドすることができる。
docker buildする際にキャッシュを使うかは次の基準で決まる。
FROMで指定しているベースイメージのキャッシュが変わった
Dockerfile自体の命令が変わった
ADDやCOPYしているファイルの対象が変わった
キャッシュを使わずにイメージビルドをしたい場合、以下の--no-cacheオプションを使う

docker build -t myimage . --no-cache

ハガユウキ

アソシエーション：モデル同士の関連性のこと
リレーション：テーブル同士の関連性のこと

ハガユウキ

商品テーブルのレコードが中間テーブルを経由して、商品テーブルのレコードを参照するような設計は、閉包テーブルモデルという設計パターンに似ている。今まで自然にやっていたけど、これに名前があったとは笑
SQLアンチパターンに確か載ってたから、暇なときじっくり読むか、、

ハガユウキ

イミュータブルデータモデリングの概要だけ理解するなら、このスライドがわかりやすい

時間あるときこの記事も読んどくか

ハガユウキ

start や end は自動詞にも他動詞にもなれるんだけども、例えば Campaigns というテーブルに対して、start した datetime を格納するためのカラムを追加するときは、Campaign が主語になり、「キャンペーンが始まる」という意味の自動詞にするのが自然とのこと。こうすると、自然言語では「The Campaign starts at 9 PM today.」とかなるので、starts_at と命名するのが自然っぽい。

めちゃくちゃ参考になるな…普通に過去分詞_atと命名していた、、

自動詞を使うときは気をつけよう

ハガユウキ

４章の最初あたりまで読んだ。
dockerを一通り勉強した後に読むと、理解しやすいかも

ハガユウキ

わかりやすい
Dockerを知れば知るほど、インフラの領域とか面白いなって思えてくる

ハガユウキ

あとで読む

ハガユウキ

ところが、2017年DockerはCE（コミュニティエディション）とEE（エンタープライズエディション）２つのソリューションの提供を開始しました。

docker-ceはコミュニティエディション(無料版)なのか
docker-eeはエンタープライズエディション(有償版)なのか
自分のubuntu環境でサクッと試したいなら、ceがおすすめだそう

ハガユウキ

■dockerイメージについて今一度深ぼる
dockerイメージとは、コンテナを動かすために必要な依存関係をパッケージ化したもの。dockerイメージは一つのファイルというより概念的な意味合い(Dockerは、複数のイメージレイヤを抽象的な Docker イメージという単位で扱えるようにしている)。dockerイメージは複数のイメージレイヤで構成されている。dockerイメージは複数のイメージレイヤで構成されているけど、利用する人からしたら一つのdockerイメージにしか見えない。dockerは各イメージレイヤ上のファイルシステムを一つのファイルシステムとして扱うことができる。それぞれのイメージレイヤは読み込み専用(Read only)であり、イメージレイヤは親子関係(親イメージの情報)を持つ。dockerイメージの実体は、OSの領域に存在している。

■イメージレイヤには何が含まれている
イメージレイヤの中には、

dockerコンテナの実行に必要なLinuxファイルシステム(/ディレクトリ以下の/etc, /bin, /sbin, /usr等のディレクトリ階層及びファイル)
メタ情報(アプリケーションを動かすために必要なデフォルトのコマンドや引数の指定、外に公開するポート番号の情報、ボリューム領域などの情報)
を含む

■なんらかのイメージを指すとき

通常、何らかの Docker イメージを指すときには、そのイメージの最上位に位置するイメージ・レイヤを指します（デフォルトでは latest タグというタグを持つイメージ・レイヤ）。そのイメージ・レイヤに親子関係を持つレイヤがあれば、イメージの取得時など、自動的にまとめてダウンロードしたり、アップロードしたりできます。

つまり、なんらかのイメージを指している時、そのイメージの最上位のイメージレイヤのことを指している。

■latest: Pulling from library/hello-worldについて
libraryはdocker公式イメージ専用の名前空間。docker pullなどの実行時に名前空間の指定がなければ、自動的に、公式イメージ(library)のイメージを取得する。

■ docker.io/library/hello-world:latest について
docker.ioはレジストリ(DockerHub)、名前空間(この文脈における名前空間は、イメージを格納するリポジトリのこと)は、library(公式イメージ)。その中のhello-worldイメージのタグ名がlatestをダウンロードしたという意味(そのイメージ・レイヤに親子関係を持つレイヤがあれば、まとめてプルしてくれる)。ややこしいが、docker image lsをやると、REPOSITORYって項目が出てくるが、あれはイメージ名を表している。

■GraphDriverのUpperDirについて
docker image inspect イメージ名を実行すると、UpperDirという項目を確認できる。UpperDirに書かれているパスは、Docker を実行しているホスト上で、イメージの実体を保存しているディレクトリである。

sudo ls -l /var/lib/docker/overlay2/a084a0121998212c6baf859b9199eb940e4686310aad0570ac01aae562804aa9/diff
total 16
-rwxrwxr-x 1 root root 13256 Sep 23  2021 hello

このhelloファイルはfileコマンドを実行することで、ファイルタイプを確認できる。このhelloファイルは実行ファイル(ELFが出たら実行ファイル。実行ファイルはバイナリ形式)である。つまり、hello-world:latestイメージのファイルシステムには、helloというバイナリファイルしかないことがわかる。

sudo file /var/lib/docker/overlay2/a084a0121998212c6baf859b9199eb940e4686310aad0570ac01aae562804aa9/diff/hello
/var/lib/docker/overlay2/a084a0121998212c6baf859b9199eb940e4686310aad0570ac01aae562804aa9/diff/hello: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), statically linked, stripped

hello-world はイメージレイヤが１つしかないため、ホスト上ではこの１つのディレクトリ内に hello-world Docker イメージの内容物を全て含む。そのため、複数のイメージ・レイヤで構成する Docker イメージがあれば、ホスト上に複数のディレクトリが存在することになる。また、メタ情報にもイメージ・レイヤを必要とする(メタ情報は概念としてのイメージレイヤであり、ホスト上では実体としてのファイルやディレクトリなどはない)

■Dockerは何をする
Dockerは全ての依存関係(Linuxファイルシステムやメタ情報)をDockerイメージとしてパッケージ化して、イメージの中にあるファイルをコンテナとして動かす。

■Linuxのプロセスはどのようにして動いているのか
OSの領域が記憶装置内のファイルシステム内にあり、メモリを経由して、CPUに命令を実行してもらう。一般的なプロセスは、OSの領域にあるなんらかのバイナリのプログラムをもとに、カーネルのユーザー空間でプログラムを実行する。この実行状態のプログラムをプロセスと呼ぶ。プロセスは、OS上のさまざまなリソースにアクセスする。
コンテナはアイソレートした状態のプロセスのこと(わかりやすさのためにこう解釈しているけど、厳密に言うと、プロセスをアイソレートさせるための空間をコンテナなのかなと思った)。お互いのプロセス空間やファイルシステム、ネットワークなどがアイソレートしている

■コンテナを実行するとは
Dockerは、Linuxプロセスを実行する際、Linuxカーネルの名前空間技術やcgroupを使って、アイソレート(分離)した状態のプロセスを実行している。
コンテナは別のコンテナの状態を知ることができない(ここら辺はネットワークとかを使えばうまくいくのか..？)
名前空間の代表的な例がPID 名前空間。PID名前空間を使うことで、プロセスIDを名前空間ごとに持つことができる。新しく作成したPID名前空間の最初のプロセスのPIDは、作成した名前空間内で見ると1となる。このプロセスは、名前空間を作る元となった親の名前空間でも別のPIDを持つ。

■chrootとは
UNIX系OSで使われるコマンドおよびプログラムの一つで、現在のシステムのルートディレクトリを指定したディレクトリに変更する。
各Dockerイメージは、ファイルシステムをマウント名前空間で分離する。マウント名前空間を使うと、名前空間内で行ったマウント操作を、他の名前空間には反映させないといったことができる。コンテナが見えている自分のファイルシステムは、chrootのようにホスト上のあるディレクトリをマウントしている

■コンテナとdockerの関係性
Linuxでもコンテナを実行することはできる。
Docker はdocker仕様のコンテナを動かすためのプラットフォームの総称であり、プログラムの実装である。
(Dockerコンテナの方が、ポータビリティに優れていたり、イメージの共有ができたりとか、Docker を採用する方が色々楽ができそう)

■Docker コンテナを実行する
Dockerコンテナ(以下コンテナと呼ぶ)を実行する際に、コンテナ用に読み書きできるイメージレイヤが自動的に作成されます。このイメージ・レイヤは通常のイメージ用と同じく、親子関係を持つ。
Dockerコンテナを実行すると、イメージの実体が存在するディレクトリを、コンテナの / にマウントする。
コンテナ内のプロセスが実行完了すると、コンテナも実行終了になる。
コンテナを実行するとは、つまり、ホスト上にある複数のディレクトリやファイル(イメージの実体)を１つにファイルシステム内にマウントして見えるようにし、そのファイルシステム内にあるプログラムを特別な状態（名前空間の分離など）で起動することである。

■Docker のライフサイクル
以下のコンテナ起動から終了までの流れを、Dockerのライフサイクルと呼ぶ

Docker イメージを準備する
Docker コンテナ用の名前空間（PID, mount, …等々）を分離（isolate）した環境を作成
Docker は、Docker イメージの中にあるファイル（バイナリなどのプログラム）を、その名前空間内で実行
実行したプログラムが処理完了すると、Docker コンテナも終了（停止）する

■FROM命令に指定するscratchとは
FROM命令にscratchを指定すると、何もない空っぽのイメージレイヤが作成される。

■CMD命令について今一度ふかぼる
CMD命令は、コンテナ実行時に、コンテナが見えるファイルシステム上であるプログラムを自動で実行しなさいという命令

■docker historyのmissingについて
missingと表示される行は、他のシステムにおいてビルドされていることを示している。
共通するイメージレイヤはイメージ間で共有できる

■WORKDIR命令について
WORKDIR命令は、RUN, CMD, ENTRYPOINT, ADD, COPYの際の、コンテナ内の作業ディレクトリを指定する
WORKDIR が存在していなければ作成される
基本、命令で指定したコマンドを実行する場所は、イメージレイヤのルートだが、WORKDIR命令を使うと、次のイメージレイヤでもWORKDIRが引き継がれる。

■RUN命令について
RUN 命令は、現在のイメージよりも上にある新しいレイヤでコマンドを実行し、その結果をコミット（確定）
commit する。

■COPY命令について
COPY 命令では、追加したいファイル、ディレクトリを <コピー元> で指定すると、これらをイメージのファイルシステム上のパス <コピー先> に追加する

■Dockerfileでバインドマウントってどうやってやるの？
Dockerfileはイメージの構築方法を定義するものであり、その使用方法を定義するものではない。イメージをどのように使うかを定義したい場合、docker-composeを使う。

■docker-composeについて
まとめてコンテナを起動したいときとか、コンテナをどのように起動するかを定義したいときに使う。
docker composeを使うためには、Composeファイル(docker-compose.yml)が必要
docker-compose.ymlにサービス、ネットワーク、ボリュームなどを定義する
docker composeにおけるサービスは、ほぼコンテナのこと。
ネットワークを定義していない場合、docker-compose up時に自動的に、このプロジェクト専用のbridgeネットワークが作成される
同一bridgeネットワークにコンテナが属していれば、コンテナのポートの公開を明示しなくても、お互い内部通信が可能。

ハガユウキ

■マルチステージビルド
マルチステージビルドを使うことで、プログラムのビルドのために使ったファイルとかを除いて、実行ファイルだけをイメージレイヤーとして、イメージを作ることができる。
イメージを軽量化することができる。そして、Docker fileが読みやすくなるので、保守性も上がる
マルチステージビルドを行うには、Dockerfile内にFROM命令を複数記述する。各FROM命令のベースイメージは異なるものとなり、各FROM命令から新しいビルドステージが始まる。
最初のFROM命令のステージを0 として順次割り振る。ステージにASで名前をつけることもできるので、この整数値はあまり意識しなくて良いかも。
↓ マルチステージビルドを採用したDockerfile

ROM golang:1.20 AS build
WORKDIR /usr/src/myapp
COPY sample.go ./
RUN go mod init sample-mod
RUN go build -o ./sample

FROM gcr.io/distroless/base
COPY --from=build /usr/src/myapp/sample /
CMD ["/sample"]

(go build のoオプションで、ビルドしたファイルを置くディレクトリと、ビルドしたファイルのファイル名を指定できるそう)
( gcr.io/distroless/base は実行用のイメージとして最小限のものを備えているイメージで、GoやRustなどのビルドされたアプリケーションの実行に適している。イメージのサイズは16MB)
↓ マルチステージビルドを採用しなかったDockerfile

FROM golang:1.20 AS build
WORKDIR /usr/src/myapp
COPY sample.go ./
RUN go mod init sample-mod && \
    go build -o /sample
CMD ["/sample"]

イメージサイズが結構違うのは、当たり前なんだけど、結構違ってて、びびった笑
22.3MBがマルチステージビルドによって生成されたdockerイメージ
804MBが普通にビルドして生成したdockerイメージ

ハガユウキ

実行ファイル（じっこうファイル、Executable、Executable file）とは、コンピュータがタスクを実行するための解釈可能な命令を含むファイルである[1]。実行可能ファイル、実行可能プログラム、実行形式ファイル、あるいは単に実行形式とも呼ばれる。これと対になるのがデータファイルであり、これは実行ファイルにより解析されることで初めてコンピュータにとって意味があるものとなる。

実行ファイルをターミナルで実行すると、プログラムが実行されるってのはとりあえず理解できた。
このファイルはバイナリで構成されている。

ハガユウキ

この記事、めちゃためになるから暇な時読むか

ハガユウキ

この記事に書いてある２つのスライド、どっちもわかりやすいな

ハガユウキ

Dockerfileはイメージの構築方法を定義するものであり、その使用方法を定義するものではありません。代わりにdocker-composeを使ってみてください。ディレクトリをマウントしてくれるシンプルなdocker-compose.ymlは次のようになります：

DockerfileとDocker Composeの用途の違いを知るには、これを見ると良いかも

ハガユウキ

デフォルトだと、dockerコンテナで作られるmysqlは/var/lib/mysqlにデータを格納しているくらしい。
なので、volumeをこのディレクトリにマウントしたら良さそう

ハガユウキ

composeのrestartは、upで起動する際の、コンテナが停止した時のポリシーを設定するそう。
alwaysだと、コンテナが停止した時に、終了ステータスに関わらず再起動してくれるそう（明示的にdocker-compose stopで止めた場合は除く）

わかりやすい

.envファイル:
ホストの環境変数をdocker-compose.yml中で $...として使えるが、ホストにその環境変数が設定されていなかった場合の回避策のために使うファイル。あるいは、ホストにその環境変数が設定されていればそちらが使われるので$ ...のデフォルト値を設定するファイルとも解釈できる。
env_file
立ち上げるコンテナに設定したい環境変数の設定を外部ファイルに書くときに使うdocker-compose.yml内の項目
https://qiita.com/SolKul/items/989727aeeafcae28ecf7

MYSQ Lのイメージの場合、あらかじめ拡張子が.shまたは.sqlのファイルを入れたディレクトリを用意し、/docker-entrypoint-initdb.d/ディレクトリへバインドマウントしておけば、コンテナ実行時に自動で行ってくれるそう。

ハガユウキ

restart: alwaysがあると、本当は失敗しているのに、コンテナが動いたまんまで、失敗に気づきづらいから、入れない方がいいかもな

ハガユウキ

テーブルを作成する前に、どのデータベースを作るかしてしないといけないの初めて知った。
DB作った後に、そのDBに対してテーブルを使いたいなら、間にuseを入れる必要があるな。

https://stackoverflow.com/questions/4005409/error-1046-no-database-selected-how-to-resolve

ハガユウキ

MySQLイメージからコンテナを作るときに、MYSQL_ROOT_PASSWORD という環境変数は必須で指定しないといけないそう。これはMYSQLのスーパーユーザーであるrootのパスワードを設定するために指定するそう。

ハガユウキ

DDLとDMLの違い

DML(Data Manipulation Language)・・・レコードの挿入、更新、検索などを行う言語。代表的なSQLはSELECT(検索)、INSERT(挿入)、UPDATE(更新)、DELETE(削除)など。
DDL(Data Definition Language)・・・データ構造を定義する言語。代表的な言語はCREATE(表領域作成)、DROP（表領域の削除)、ALTER(変更),TRUNCATE(表領域内のレコード削除)など。

データ操作言語と、データ定義言語だから、言葉からなんとなく意味は分かってたけど、あってて良かった。

ハガユウキ

SQL文が大きく分けて３つに分類できるってことか

DML
DDL
DCL (データ制御言語　COMMITとかROLLBACKとかの、トランザクションを制御するために使用)
https://xtech.nikkei.com/it/article/lecture/20061211/256500/#:~:text=SQLには三�%[…]��います。

ハガユウキ

今更ながら、シェルスクリプトってなんやねんと思ったからwikiを見る

シェルスクリプト (英語: shell script) は主にオペレーティングシステムのシェルまたはコマンドラインインタプリタから実行可能なコマンドの一連の流れをファイルにして再利用できるようにしたものである。

ざっくり理解すると、シェルで実行したい一連のコマンドをファイルにしたものが、シェルスクリプトか。

狭義では、UNIXシェルで用いられるスクリプト言語だそう。

シェルスクリプトの拡張子はsh

シェルスクリプトの一行目に書く奴のことを、シバンシェバングというそう。
実行するシェルスクリプトのインタプリタを指定している。
シバンを以下のように記載することで、シェルスクリプトがbashを使用して実行されるようになる

#!/bin/bash

mysql imageにはbashが入っているから、bashで良いのか

ハガユウキ

mysqlイメージの初期化の説明を見た

コンテナが初めて起動されると、指定された名前の新しいデータベースが作成され、提供された構成変数で初期化されます。さらに、/docker-entrypoint-initdb.dにある拡張子.sh, .sql, .sql.gzのファイルがアルファベット順に実行されます。このディレクトリにSQLダンプをマウントすることで、mysqlサービスに簡単に入力することができ、貢献したデータをカスタムイメージとして提供することができます。SQLファイルは、デフォルトではMYSQL_DATABASE変数で指定されたデータベースにインポートされます。

https://hub.docker.com/_/mysql

調査に結構時間かけたけど、実装に取り掛かると実はできなかったパターンはあるあるなので、ある程度調べたら調査は見切って実装に早く取り掛かると、このようなことはふせげるのかなと思った、、(実装できそうならより深く調べる感じ)

ハガユウキ

あと、シェルスクリプトを採用しようと検討する際に、「ここどうやってやるの？」を深く詰めとけばよかったか(MySQLの環境変数とか)
そもそも、シェルスクリプトを使わなくてもdockerイメージのデフォルトの機能でシンプルにできないかを考えずに、安直に脳死でシェルスクリプトを採用しようとしたところが原因か。気をつけなければ、、

ハガユウキ

MySQLのポート番号はデフォルトでは「3306」
dbコンテナのポートでもこいつを結構使っているイメージ

ハガユウキ

healthcheckもcomposeファイルに入れた方が本当は良い気がするけど、あんまよくわかっていないので一旦スルー

ハガユウキ

■文字コードとは
文字をコンピュータで表現する際に、どのようなバイト表現にするかを定めたもの
ややこしいが、文字コードという言葉は、文字集合と、符号化方式の２つの概念を持つ。
■文字集合とは
ざっくり考えると、文字の集合のこと。文字には、対応する番号が振られている。JIS X 0208やUnicode、ASCIIが含まれている
■符号化方式とは
エンコーディングルールと呼ばれている。符号化方式とは、ざっくり考えると、文字集合をどのようなバイト列に変換するかのルールを定めているものである。
例えば、JIS X 0208を符号化する方式としては、ISO-2022-JP(俗にいうJISコード)、Shift_JIS、EUCなど。
Unicodeを符号化する方式としては、UTF-8、UTF-16などがある。
■JISとUnicodeのざっくりとした違い
Unicodeの方が表現できる文字数が多そう

ハガユウキ

■MYSQLのcharsetとは
charsetとは、文字集合とエンコーディングの両方の意味を含んだもの。
(結構ややこしいのが、文字コードのエンコーディングルールでUTF-8というのがあるが、charsetにもutf8という似たような名前のcharsetがあるので注意。charsetのutf8は、文字集合がUnicode、エンコーディングがUTF-8である)
charsetでutf8を使うと、1文字を１~3バイトで表しているので、４バイト文字である絵文字はうまく表現できない。
その場合、charsetをutf8mb4に変えれば良い。基本UTF-8は普通１〜４バイトであるが、MySQLだとutf8/utfmb4でそこらへんが違う。
charsetのutf8は1文字を１~3バイトで表しているが、utf8mb4は1文字を１〜４バイトで表している。
ほとんどの日本語文字は３バイト
一部の漢字と絵文字などが４バイト

■MySQLで設定可能な文字コードの一覧を取得する

show character set

■照合順序について
MySQL ではデータベース、テーブル、カラムのそれぞれに対してcharsetと照合順序を設定することができる。
(何も指定しなかったらデフォルトの照合順序が使われる。デフォルトの照合順序を確認したかったら、上のコマンドを実行して、Default collationを見る)
MySQLではcharsetごとにデフォルトの照合順序が設定されている。照合順序(collation)とはDB上でどの文字とどの文字を同値と扱うかという設定
(大文字と小文字を区別するかどうかや、バイナリで比較するかどうかなど。)
charsetには複数の照合順序が用意されている。データベースやテーブルを作るときにcharsetと一緒に照合順序を指定できる
charsetごとにどのような照合順序が用意されているかは以下のコマンドで確認できる

show collation where charset = 'cp932';

■照合順序の構成について
照合順序は以下の意味で構成されている

charset_言語名_比較法

比較法については以下が代表的。
ci: 大文字と小文字を区別しない
cs: 大文字と小文字を区別する
bin: バイナリ比較を行う

ハガユウキ

■MySQLに設定されている現在の文字コードを確認するコマンド

show variables like "chara%";

■mysqldとは
mysqldの最後はデーモンのd。デーモンはバックグラウンドプロセスを指す。mysqldはmysqlクライアントから送られてきたSQLを処理して、結果をクライアントに返す処理を受け持っている。
mysqldはMySQLサーバーとも呼ばれる。mysqldのクライアントプログラムとしては、mysqlコマンドラインクライアントが一般的。クライアントをGUIで扱いたいなら、sequelAceやTablePlusを使う。MySQLのパッケージには、mysqld以外にもクライアントプログラムも含まれている。
mysqldの中のサーバ下位層にストレージエンジンというのがあり、SQ Lの発行はストレージエンジンに対して行われる
(ストレージエンジンでよく使われるのはInnoDBである。InnoDBはトランザクションをサポートしている)
つまり、SQLを実行する際は、ざっくり以下のことが行われていると考えられる

クライアントに実行したいSQL文を渡す
クライアントがmysqldにSQL文を発行する
mysqldは発行されたSQL文の結果をキャッシュしているならキャッシュを返す。ないなら、SQL文を解析して、下位層のストレージエンジンに対して発行する
結果が出たらクライアントに返す

■クライアントについて
クライアントは、mysqldに接続することでデータベースへアクセスする。そのため、クライアントプログラムを使用するためには、mysqldが稼働していなければならない。
mysqldが起動するとき、クライアントプログラムからのネットワーク接続を待機して、それらのクライアントに変わってデータベースへのアクセスを管理する。

ハガユウキ

my.cnfで設定しておくと良さそうな環境変数

- character_set_server  : DB作成時のmysqldのデフォルトの文字コードを表す
- default-character-set  : クライアント側の文字コードを表す。クライアント側に指定する
- collation-server  : mysqldのグローバルのcollationを表す。(collationとはDB上でどの文字とどの文字を同値と扱うかという設定)

MySQLでは、サーバー、クライアント、サーバー/クライアント間、データベースごと、テーブルごと、カラムごとにcharsetとcollationを指定できる。もちろん全て統一した方が良い。

ハガユウキ

こんな感じで良い気がしてきた
my.cnf

[mysqld]
character_set_server=utf8mb4
collation-server=utf8mb4_bin

[client]
default-character-set=utf8mb4

寿司ビールでこの記事が結構わかりやすかった。

clientとmysqlは大体同じ

ハガユウキ

utf8mb4のcollationについては以下の記事がわかりやすかった

ハガユウキ

show variables like ‘collation%‘;

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_bin        |
+----------------------+--------------------+

上のコマンドでcollation関係の変数を確認できる
collation_databaseがutf8mb4_binじゃないのが気になるが、グローバルでutf8mb4_binが設定されているので大丈夫。
MySQLでは、charsetやcollationをグローバル、データベース、テーブルやカラムレベルで設定することができる。collationをグローバルに設定するなら、collation_serverというシステム変数を使えば良い。

ハガユウキ

上のmy.cnfをセットしてコンテナを立ち上げると、確かにシステム変数が変わっていた

+--------------------------+--------------------------------+
| Variable_name            | 意味                         |
+--------------------------+--------------------------------+
| character_set_client     | クライアントが送ってくるとサーバーが想定している文字コード                       |
| character_set_connection | クエリを実行する文字コード                       |
| character_set_database   | 使っているデータベースの文字コード                        |
| character_set_filesystem | ファイル名の文字コード                        |
| character_set_results    | クライアントに結果を返すときの文字コード                        |
| character_set_server     | DB作成時のデフォルトの文字コード                       |
| character_set_system     | システムの使用する文字コード                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+

ハガユウキ

これめちゃくちゃ参考になるな

ハガユウキ

■UNIX時間とは
協定世界時 (UTC) での1970年1月1日午前0時0分0秒（UNIXエポック）からの形式的な秒数として表される時刻のこと。
1970年1月1日が起点になった理由は特にないそう。

■MySQLのTIMESTAMP型とDATETIME型の違いについて

DATETIMEは日付と時刻の組み合わせ
TIMESTAMPはエポック (‘1970-01-01 00:00:00’ UTC) からの秒数（つまり、TIMESTAMP型のカラムにはUNIX TIMEが格納される）
- おそらく、内部的にはこの秒数で保存していて、表示する際にはその秒数を日付や時刻に変換していると思われる
DATETIMEのサポート有効範囲は“1000-01-01 00:00:00″から“9999-12-31 23:59:59″までサポート
TIMESTAMPでは“1970-01-01 00:00:01″UTC から“2038-01-19 03:14:07″UTCまでサポート
TIMESTAMP型の値は、保存時にはタイムゾーンの値からUTCの値へ、読み出し時にはUTCの値からタイムゾーンの値へと変換される。これに対しDATETIMEはタイムゾーンの影響を受けることなく文字列として認識され保存される。また、読み出し時にも文字列認識により、そのままの状態で出力されます。
DATETIMEのデフォルト値は“NULL”。TIMESTAMPのデフォルト値は“0000-00-00 00:00:00″。
TIMESTAMPのサポート範囲は“2038-01-19 03:14:07UTCまで“であり、これは日本標準時(JST)に置き換えると“2038-01-19 12:14:07″となる。
- “2038-01-19 12:14:07″を超えた値をTIMESTAMP型のカラムに格納すると、デフォルト値が格納されてしまう。
- これが2038問題
DATETIME(6)とか書けば、マイクロ秒までサポートできる。（02.144702の14 ~の部分がマイクロ秒の単位を秒変換したもの。マイクロ秒は1秒の100万分の一秒を表す単位）

今から新規開発するなら、DATETIMEを採用した方が良い

https://e-words.jp/w/マイクロ秒.html （

ハガユウキ

■MySQLのCURRENT_TIMESTAMP関数とNOW関数の違い

NOW関数は、CURRENT_TIMESTAMP関数と同じ。
NOW 関数は現在の日付と時刻を取得するために使用する
引数は省略可能。指定する場合は小数秒の精度を 0 から 6 で指定できる。

NOW([fsp])

UTCで保持する場合、タイムゾーンをどうするか

ハガユウキ

■標準時とは
ざっくり考えると、各国、各地域で標準として定められている時刻のこと。日本の場合、JST

■タイムゾーンとは

同じ標準時を利用する地域や区分のこと
インターネット上のコンピューターは世界中にあるため、それぞれが各地域の時刻を使うと通信に混乱が起きる。
そこで、コンピューターやプログラムの時計をタイムゾーンの標準時に合わせる。標準時は、イギリスのグリニッジ天文台における基準時刻との差で表す。

ハガユウキ

■UTCオフセットとは
UTC オフセットとは、特定の時刻ゾーンと UTC（経度ゼロ度の時刻）の間の時間と分の差のこと。

このエムスリーの記事めちゃ参考になるけど、なぜ、UTCでカラムに保存してはダメなのだろうか、、(なぜ日付を保存する際にUTCオフセット情報をつけた)
まだ完全にUTCを理解できてないな。。

ハガユウキ

MySQLのDATETIMEにオフセット情報を持たせるのは、調べた限りだと無理っぽいな、、
やっぱDBのDATETIMEにはUTCで保存するしかないか、、

ハガユウキ

DBサーバーのDATETIMEにはUTCで保存しておく
ユーザー側からサーバにアクセスがあった際は、アクセス元のタイムゾーンを取得して、動的にアプリケーションサーバーにタイムゾーンを設定する。
そのタイムゾーンでDBサーバーにアクセスする。

以下の手順で対応すれば、タイムゾーンごとに正確な時刻を表示できそうな気がする。
コード上では動的に設定したタイムゾーンを考慮して、日時を出力してくれるようなクラス(RailsだとTimeWittZone)を使えば良さそう。

ハガユウキ

今更ながら、主キーについてふかぼる
■主キーとは

テーブル内でレコードを一意に特定できるようなカラムのこと。主キーに選ばれたカラムには、すべてのレコードで異なるを持つ必要がある（なので、主キーはNULLが入ることが許されない）
主キー制約(PRIMARY KEY句で設定できる)をあるカラムに設定することで、そのカラムが主キーとしての働きを持つことができる。
主キーにされたカラムは、すべてのレコードで異なる値を持ち、かつNULLが入ることが許されないので、unique制約とNOT NULL制約を持つことになる。
PRIMARY KEYはテーブルごとに一つしか設定できない。
プライマリーキーが設定されたカラムには自動的にインデックスが作成される。
- 作成されたインデックスの名前は PRIMARY で、Non_unique が 0 となっているので重複した値を格納することができない
- show index from テーブル名 \Gで作成されたインデックスを確認できる
  https://www.javadrive.jp/mysql/table/index8.html

ハガユウキ

ALTER TABLEはテーブル定義を変更するためのDMLだったのか。

ハガユウキ

MySQLで主キーにUUIDを採用しようとして、色々考えた

MySQLではクラスターインデックスというフォーマットが強制されている
- そのせいで、単調増加ではない値が連続でインサートされた時に、大きく性能が劣化してしまう(インサート完了時間がめちゃ遅くなる)。
- なので、UUID v4を採用してしまうと、UUID v4は単調増加ではない完全ランダムな値なので、めちゃ性能が劣化する。
- てなると残りは、UUID v7とUUID v1。UUID v1はDockerとの相性が悪いせいで一意制を担保するのが難しいそう。
- UUID v7を検討したが、結構複雑なロジックを読み解かないといけない & そのロジックを持つ関数を作らないといけないので個人的に辛いなと感じた(アプリケーション側で提供されているライブラリを使えば、アプリケーション側からは簡単に入れられるが、SQLで直に入れるとのが難しい)
  実力者ならここの壁は突破できそうだが、個人的にはこれを実装するのは辛いなと感じた笑(DBが標準でサポートしてくれい)
- てなると、残りは、ULID or オートインクリメントだが、ULIDもUUID v7と同様の問題を持つ
- なので、純粋にUUIDを使いたいなら、postgreがいいのではと思った(UUID v4を使ってもあまり性能劣化がないそう & postgreではUUID v4をめっちゃ簡単に使える)

現状の自分の実力を考慮すると、よくわかっていないもの(UUID v7)を採用するのは怖いので、MySQLを採用するなら、オートインクリメントで管理しつつ、アプリケーション側ではハッシュidにして、生のidでリクエストできない(ハッシュidでしかリクエストできない)ような仕組みを作るしかないかなーと思った…

ハガユウキ

MySQLには2つのタイムゾーンに関する変数がある。time_zone という変数の値で、MySQLサーバーの現在のタイムゾーンが決まる。
デフォルトはUTCなので、東京時間で保存したのに、UTC時間で保存される。なので、-9時間された時間がDBには保存されてしまう。これを東京時間にするには、タイムゾーンに関する変数を設定して、MySQLサーバーが日本時間でデータを保存してくれるように設定しなければならない。(もし、アプリケーションサーバーを通してデータを表示、タイムゾーンの時間に変換、保存するって場合は、MySQLサーバのタイムゾーンに関する環境変数はUTCで良いと思う。クエリをDBに書いて調査する場合にちょっとめんどくさそうな気もするけど)

↓ MySQLのタイムゾーンに関する変数
■system_time_zone

ホストマシーンのタイムゾーンを表す
サーバー起動後のホストマシーンのタイムゾーンから、この値は設定される(その後、この値は変わらない)
mysqldを起動する前にTZ環境変数を設定すれば、この値を指定した値に書き換えてくれるそう。

■time_zone
MySQLサーバーが現在動作しているタイムゾーンを表す
time_zone の初期値は'SYSTEM'で、サーバーのタイムゾーンがシステムのタイムゾーンと同じであることを示す。
以下のコマンドで、タイムゾーンに関する変数にどんな値が設定されているかを確認できる。

mysql>  show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | JST    |
| time_zone        | SYSTEM |
+------------------+--------+

以下のコマンドを実行して、時間がおかしくね？ってなったら、MySQLサーバーのタイムゾーンに関する設定がおかしいかも

mysql> select current_timestamp;
+---------------------+
| current_timestamp   |
+---------------------+
| 2023-04-07 01:23:39 |
+---------------------+
1 row in set (0.00 sec)

今考えると、system_time_zoneを強制的に書き換えるのってなんか違和感あるな。そのホストマシーンがいるタイムゾーンから導き出される値なのに。
やっぱtime_zoneで設定する方が自然だな。

ハガユウキ

dockerのコンテキストとは、docker buildコマンドを実行した場所って意味なのか。
docker-compose.ymlでcontextって項目を作ると、使えるそう。
今は使わないけど、複数コンテナを作る際に使うかも

ハガユウキ

とりあえず、docker-composeでTZの指定は消して、my.cnfでtime_zoneを設定するように修正した。
my.cnf

[mysqld]
character_set_server=utf8mb4
collation-server=utf8mb4_bin
default-time-zone="Asia/Tokyo"

[client]
default-character-set=utf8mb4

time_zoneがちゃんと変わってた。docker-compose.ymlのTZは消したので、system_time_zoneがちゃんと書き変わってなかった(これは意図した挙動)。

mysql> show variables like "%time_zone%"
    -> ;
+------------------+------------+
| Variable_name    | Value      |
+------------------+------------+
| system_time_zone | UTC        |
| time_zone        | Asia/Tokyo |
+------------------+------------+

ハガユウキ

■外部キーとは

外部キーとは、外部キー制約を指定する列のこと
外部キーには、外部のテーブルの特定の列に含まれている値しか指定できないようになる。
外部キーは別のテーブルの主キーを参照する。外部キーが主キーを参照することで、テーブルが関連付け(リレーション)される。

■外部キー制約とは

外部キー制約とは、外部のテーブルの特定の列に含まれている値しか指定できないようにする制約のこと
FOREIGN KEY句を使用して外部キー制約を作る
外部キー制約を作る時に、ON DELLETEで親レコード削除時のオプションを指定することができる(ON UPDATEで親レコード更新時のオプションも指定できるけど、あんま使っているイメージはない)

■一般的な整合性という言葉の意味とは
２つの事柄の間にズレや矛盾がないこと

■よくわかっていないこと

外部キー制約を張ったキーにnullを入れることは可能か？そもそもNullは入れるべきではないか？(それをどうやってMySQLで設定する？)
親子関係のある状態で、親のデータを消そうとしたら、子供の外部キーはどうなる？
https://en.wikipedia.org/wiki/Foreign_key
https://www.javadrive.jp/mysql/table/index11.html
https://e-words.jp/w/外部キー.html

ハガユウキ

■ストレージエンジンとは

ストレージエンジンとは、テーブルにデータを書き込んだり読み出したりするプログラム
ストレージエンジンは、MySQLサーバの下位層に存在している。
MySQLではデフォルトで、InnoDBというストレージエンジンが採用されている。
MySQLサーバーはSQL文の結果のキャッシュがなければ、SQL文を解析して、下位層のストレージエンジンに対してSQL文を発行する
MySQLの場合、テーブルごとにストレージエンジンを分けられる。(つまり何も設定してなかったらテーブルはInnoDBというストレージエンジンを使う)
InnoDB内のデータが格納されるファイルのことをテーブルスペースと呼ぶ。テーブルを一つ作成するごとに、InnoDBにテーブルスペースが一つ作られる。

以下のコマンドでテーブルごとにどのストレージエンジンを使っているか確認できる

mysql> show table status \G
*************************** 1. row ***************************
          Name: users
        Engine: InnoDB
       Version: 10
    Row_format: Dynamic
          Rows: 0
Avg_row_length: 0
   Data_length: 16384
Max_data_length: 0
  Index_length: 0
     Data_free: 0
Auto_increment: 1
   Create_time: 2023-03-30 00:07:52
   Update_time: NULL
    Check_time: NULL
     Collation: utf8mb4_0900_ai_ci
      Checksum: NULL
Create_options:
       Comment:
1 row in set (0.00 sec)

ハガユウキ

■MySQLのデータの保管場所について

MySQサーバーによって管理されるデータは、原則データディレクトリと呼ばれるディレクトリに格納される
- あくまで原則であって、InnoDBなどのストレージエンジンでは必ずしもそうではない(InnoDBのデータに関するファイルはデフォルトのデータディレクトリに入っていたから、ストレージエンジンがInnodDBの場合、データがデフォルトのデータディレクトリにあるってわけではないのかも)
- このデータディレクトリをdockerのボリュームでマウントしたりする。
データディレクトのサブディレクトリにデータベースディレクトリがある。inodbに関するファイルも置いてある
データディレクトリがどこにあるかを確認したいなら、システム変数を確認すればOK
おそらくMySQLのデフォルトだと/var/lib/mysqlがデータディレクトリ

mysql> show variables like "%datadir%"
    -> ;
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.17 sec)

データディレクトリのサブディレクトリにデータベースディレクトリがあり、その中身を確認するとテーブルに関するファイルが存在する
- .frmファイルは、フィールド定義などのテーブル定義等を記録するファイル
- .ibdファイルは、データとインデックスの両方を記録するファイル(dockerでmysqlコンテナを作ったらなぜかこっちしかなかった。)

ここら辺まじで奥が深いから、これ以上深追いするのはやめよう笑
マジで出れなくなる笑。
とりあえず、MySQLサーバーの階層にInnoDBが存在していて、キャッシュがなければInnoDBに対してクエリを発行しているのと、データディレクトリ配下にデータベースの情報だったりInnoDBの情報が格納されているってことだけ覚えておこう。

ハガユウキ

■unique制約

unique制約をカラムに設定すると、そのカラムには重複した値を入れることができなくなる。
PRIMARY KEY制約とに似ているが、UNIQUE制約の場合、NULを格納することができる(PRIMARY KEY制約でそれはできない)
- UNIQUE制約でNULLを許容したくないなら、NOT NULL制約を使う
作成されたインデックスの名前は対象となっているカラム名と同じになる。
UNIQUE制約が設定されたカラムには自動でインデックスが作成されている。(これはPRIMARY KEY制約でも同じ)
https://www.javadrive.jp/mysql/table/index9.html

ハガユウキ

usersテーブルを作った

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  phone VARCHAR(11) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL UNIQUE,
  PRIMARY KEY(id)
) COMMENT="ログインユーザー";

ALTER TABLE users RENAME INDEX phone TO index_users_on_phone;
ALTER TABLE users RENAME INDEX email TO index_users_on_email;

ハガユウキ

このコマンドでテーブル内のカラムの詳細情報を見れる

mysql> show columns from users;
+-------+-----------------+------+-----+---------+----------------+
| Field | Type            | Null | Key | Default | Extra          |
+-------+-----------------+------+-----+---------+----------------+
| id    | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255)    | NO   |     | NULL    |                |
| phone | varchar(11)     | NO   | UNI | NULL    |                |
| email | varchar(255)    | NO   | UNI | NULL    |                |
+-------+-----------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

ハガユウキ

以下のコマンドでテーブルに存在しているインデックスを確認できる。
当たり前っちゃ当たり前なんだけど、PRIMARY KEY制約でちゃんとインデックスが作られるのはほんとなんだなと、コマンドを通して理解できるのが良いなと思った

mysql> show index from users \G
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: users
   Non_unique: 0
     Key_name: index_users_on_phone
 Seq_in_index: 1
  Column_name: phone
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 3. row ***************************
        Table: users
   Non_unique: 0
     Key_name: index_users_on_email
 Seq_in_index: 1
  Column_name: email
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
3 rows in set (0.01 sec)

ハガユウキ

https://shiro-secret-base.com/?p=928
この記事のサーティワンフレーバーの解説わかりやすい。MySQLのENUM型は、既存の値を更新するときに結構大変なのか。

ハガユウキ

この記事のCORSの説明地味に分かりやすい笑

ハガユウキ

SUMとCASEを組み合わせれば、条件ごとの集約を一つのクエリでシンプルに書くことができそう。

SELECT p.product_id,
  SUM(CASE b.status WHEN 'FIXED' THEN 1 ELSE 0 END) AS count_fixed,
  SUM(CASE b.status WHEN 'OPEN' THEN 1 ELSE 0 END) AS count_open
FROM BugsProducts p
INNER JOIN Bugs b USING (bug_id)
WHERE p.product_id = 1
GROUP BY p.product_id;

わざわざCASE使わなくても、IFでも行けそう。

/*30才以上の合計*/
SELECT SUM(IF( age>=30 , age , 0 )) FROM test;

ハガユウキ

郵便番号のカラムって一意な郵便番号が入るのかと思っていたけど、
一つの郵便番号が複数の町名に紐づくパターンが全然あるから、冷静に考えると郵便番号のカラムは一意じゃないな。

ハガユウキ

https://urashita.com/archives/33115
MySQLって外部キーにnull行けるんか
知らんかった

ハガユウキ

group by とcaseどっち使えば良いのって時に、この記事参考になるかも

ハガユウキ

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

CHECK制約のCONSTRAINTの部分省略できたんか、、
これを省略すると、CHECK制約にオリジナルの名前がつけれないだけか。

ハガユウキ

この記事、多対多のDDL を作るときに参考になった
中間テーブルの外部キーの組み合わせに対してユニーク制約を入れるって考え方は思いつかなかった
外部キーが少ないところにだけつけるか。

ハガユウキ

UNIQUE [index_name] (col_name1, col_name2, ...))

この書き方をすれば、複数のカラムの組み合わせに対してUNIQUE制約を設定できるそう

ハガユウキ

MySQLにはinformation_schemaというデータベースがあって、データベースやテーブルに関するメタ情報を格納しているそう。
テーブルやカラムのメタ情報みたいときに使えそう

mysql> select * from information_schema.TABLES where table_name = "test";

ハガユウキ

INSERT文のテーブル名の後ろの列リストは、テーブルの全列に対してインサートを行う場合、省略できる。
このとき、VALUES句の値が暗黙のうちに左から順に各列に割り当てられる

ハガユウキ

insert文を作るつもりが、いつの間にかvimのマクロを調べまくっていた、、笑
vim1年半くらい使っててなんで今まで使ってなかったんだろうってレベルで衝撃を受けた、、
これがあれば単純作業が簡単にできて、サボれそうな気がする笑

ハガユウキ

クーポンは消費税込みの金額に対して使うのか、税込前の金額に対して使うのかを調べてみた。
消費税込みの金額に対してクーポンが使えるそう。つまり、1000円 + 100円(消費税)で税込価格が1100円に対して400円オフのクーポンを利用した場合、消費者は700円(税込)払えば良いことになる。店側は国や自治体に消費税を納める必要があるが、クーポンを使っても、クーポンを利用する前の税込金額の消費税(100円)を納める必要があるそう。
小売店がメーカーから受け取ったクーポンなら、メーカーから小売店に対して後でキャッシュバックがありそうだけど、自社でクーポンを発行している場合、
無駄に消費税を払っている気がする..
→ chat gptに聞いてみたところ、自社クーポンによって割引された金額が、自社が実際に負担する割引額を超える場合、自社は実際に支払われた金額に対してしか消費税を納めなくても良いそう。ここら辺ややこしいので、とりあえず、自社が必要以上に負担しなくても良い仕組みがあることを覚えとこう

ハガユウキ

The DATE_FORMAT() function formats a date as specified.

なるほど。こいつでgroup byもできるそう

ハガユウキ

あるテーブルに対してSUM関数を実行して、ついでに日付のカラムを取ってくるとエラーになった。
理由は、SUM関数を用いると、複数の行を一つの行にまとめるから。その一行になった集計結果に対して複数行ある日付カラムが対応できずにエラーになるそう。
なので、この場合は日付カラムに対してgroup byをやればOK

ハガユウキ

結合に内部結合と外部結合があって、
内部結合はINNER JOINの１種類。
外部結合は
LEFT JOIN = LEFT OUTER JOIN
RIGHT JOIN = RIGHT OUTER JOIN
FULL JOIN = FULL OUTER JOIN
の３種類か。
左外部結合は内部結合と違って結合条件に一致しない行でも、左の行は強制表示するのか（編集済み）

ハガユウキ

mysql のFLOORを使えば、切り捨てができる。これは他のプログラミング言語でも共通な関数名(もしくはメソッド名）って感じだな。

table plus上でviewを作れた

ハガユウキ

勘違いしていたけど、リプライの集合がスレッドってわけではなくて、チャンネルに投稿されたメッセージとリプライを含めてスレッドなのか。

ハガユウキ

ワークスペースって冷静に考えるとなんだろうか

ワークスペースってトークルームのことか
ワークスペースごとに以下の違いがある(当たり前か)
メンバーが違う
チャンネルが違う
ワークスペースがないと何も始まらないな。

ハガユウキ

多対多の中間テーブル作る際に、複合ユニーク制約をつけるのを忘れないようにしないとな。
(つけないと、データの整合性が失われる)
あと、１対１を作る際は、ユニーク制約を外部キーに付与するのも忘れないようにしないとな。

ハガユウキ

mysqlデーモンのタイムゾーンを設定すると、SELECT NOW()でJSTの日時が取得できるが、カラムに保存するときはUTCになっているくさいな。アプリケーション側でここの変換処理を実装したい…

ハガユウキ

COUNT(*)はNULLが含まれている行を除外しないでカウントする。なのでテーブルの全行を数えたい時に使える。
COUNT(カラム名)は、NULLを除外してカウントする。NULLが含まれているカラムの行を除外してカウントしたい時に使える。

ハガユウキ

MySQLで日時を加算したいときは、DATE_ADD
時間を加算したいときはADDTIMEが良さそう

ハガユウキ

確かに外部キー制約を課したカラムは、NULLを許容しているな。
(NullがYESになっている。主キー制約を課したカラムはちゃんとNULLを許容していない)

mysql> show columns from chat_messages;
+-----------------+-----------------+------+-----+-------------------+-------------------+
| Field           | Type            | Null | Key | Default           | Extra             |
+-----------------+-----------------+------+-----+-------------------+-------------------+
| id              | bigint unsigned | NO   | PRI | NULL              | auto_increment    |
| body            | varchar(255)    | NO   |     | NULL              |                   |
| send_at         | datetime        | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| user_id         | bigint unsigned | YES  | MUL | NULL              |                   |
| chat_channel_id | bigint unsigned | YES  | MUL | NULL              |                   |
| chat_thread_id  | bigint unsigned | YES  | MUL | NULL              |                   |
+-----------------+-----------------+------+-----+-------------------+-------------------+
6 rows in set (0.01 sec)

https://urashita.com/archives/33115

SHOW COLUMNS構文は指定したテーブル内のカラム情報を表示する

ハガユウキ

サブクエリのSELECT文に対しては、；をつけない。
確か、；はクエリの終わりを表すからだった気がする。

ハガユウキ

そういえば、テーブル名.*でそのテーブルのカラム全て取得できるなと、理解した笑
今まで、カラムベタ書きしてた気がする笑

ただ、*は取得対象ではないカラムに暗黙的に依存してしまうので、そこは気をつけないと。

ハガユウキ

データ構造とは
データ構造とは、データを効率的に扱うための組み合わせ方のこと。https://zenn.dev/masahiro_toba/books/436c018f5cd4e2/viewer/60d037
インデックスとは何か？
- インデックスとは、レコードを特定の列の値に着目して、適切なデータ構造で整理したもの。
- インデックスの種類により、用いられるデータ構造は異なる
- MySQL においては、B+treeというデータ構造が主流
- B＋treeを用いることで、すべてのレコードを見ることなく、レコードの検索ができる
B+treeの特徴
- ルートノードと子ノードは、キーと子ノードへのポインタを持つ
- リーフノード(末端のノード)は、キーと実際のデータ（または実際のデータへのポインタ）、次のリーフノードへのポインタを持っている
  - つまりインデックスに格納されているデータはソートされていると分かる。
- 二分探索木、AVL木のどちらも子ノードを最大２つまでしかもてないが、B＋treeでは一つのノードが2個以上の子ノードを持つことができる。このおかげで下に潜っていく回数を減らせる。つまり、計算量が減る。
- このキーってカラムに格納される値のことか。検索対象のキーがノードが持つキーより小さかったら左のノードへ、検索対象のキーがノードが持つキーより大きかったら右のノードへいく。ルートノードからその試行を繰り返して最終的にリーフノードに到達したら値をゲットできるってことか。クラスタインデックスなら、レコードを取得できる。つまり、普通に対象レコードを取得しようとすると計算量がo(n)なので、レコード量に応じて計算量が線形的に爆上がりする。インデックスを利用してレコードを取得しようとすると、計算量がo(logN)なので、レコード量が増えても緩やかな計算量の増加しかしない。だからインデックスを貼るとデータ取得が早くなるのか。計算量が少なくなるから早くなるのか。https://zenn.dev/hk_206/articles/ec5f4e347caff4
クラスタインデックスとは
- クラスタインデックスとは、リーフノードにインデックス対象のカラムの値だけでなく、全てのカラムの値が埋め込まれたインデックスのこと。B + treeがベースとなって実装されている
- プライマリーキーやユニークキーのインデックスは、クラスタインデックスである。
- クラスタインデックスのメリットは、主キーのリーフノードに到達した時に、データのフェッチも完了していること。つまり、データのフェッチがすごく早い
  - データとインデックスが別々に格納されているタイプのストレージエンジンでは、インデックスからデータの位置を読み取って、その後データファイルからデータをフェッチするので、２段階の操作が必要になってしまう。
- クラスタインデックスのデメリットは、インデックスのサイズが大きくなってしまうこと（リーフノードにすべてのカラムの値を格納しているので）。また、セカンダリインデックスを用いた検索が遅くなってしまう。
セカンダリインデックスとは
- セカンダリインデックスとは、ユニークではない列に対して作成される。
- セカンダリインデックスのリーフノードには主キーのidが含まれる
- セカンダリインデックスを使った検索は、まずセカンダリインデックスの B+ 木を辿って主キーを取得し、次にクラスタ化インデックスの B+ 木を辿ってレコードを取得する。
- セカンダリインデックスにSELECTで取得するのに必要な情報が全て格納されていれば、クラスタ化インデックスを辿る手順をスキップすることができるので高速である。このようにレコードを取得する際にセカンダリインデックスで完結する場合のことをカバリングインデックスと呼ぶ。https://techlife.cookpad.com/entry/2017/04/18/092524
  https://rooter.jp/data-format/mysql-innodb-index/

どのように実行計画が決定しているかは以下のスライドとqiitaが参考になりそう
SQLクエリをパーサが解釈して、解釈結果とカタログマネージャから提供された統計情報(インデックス情報、レコード数、カーディナリティ)を元にオプティマイザが選択可能な複数の実行計画を作成する。その際、各実行計画のコストもオプティマイザが計算する。その後、プラン評価され実行計画が１つに決定する

ハガユウキ

この図わかりやすいな。insert, delete, updateをすると、インデックスが更新されるって事実は意外と大事な気がする。パフォーマンスに関わったような気がするな。この画像はインデックスを貼った際の挙動を表しているけど、そもそもインデックスを貼っていない場合、普通にテーブル全スキャンになりそう。てか、実際にインデックスを使ったのか、テーブル全スキャンをしたのかは、EXPLAINで実行計画を取得すれば良いのか。
(EXPLAINとは、「クエリを発行してどんな処理が行われたのかを表す結果」（実行計画という）を取得するためのステートメント。)

ハガユウキ

インデックスが機能しないパターン

中間一致、後方一致のLIKE
- 要はLike使う時に検索文字列の前に％が合ったらインデックスが効かないってこと
インデックス列を計算対象として演算を行なっている
IS NULLを使っている(MySQLの最新バージョンだと機能するくさい)
インデックス列に対して関数を使っている
インデックス列に対して否定系を使っている

意外と少ないな。こんな少なかったっけ。

ハガユウキ

ORDER BYとインデックスの関係について

そもそもソート処理は高コストな処理
そのため、ソート対象のカラムに対してインデックスを貼るのは定石（じょうせき）
インデックスを貼ることで、クエリ実行時にソート対象のカラムを利用したソートが発生しなくなる。
- つまり、インデックスを貼ることで、ソート処理を排除できる
- インデックスはすでにソート済みのデータが格納されているためである
- リーフノードが次のリーフノードのポインタを持っているので、ソートしたデータを取得するのは確かに強そう。
  https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0111
  https://miyakolog.com/mysql-index-sort/

ハガユウキ

MySQLにおけるインデックスを貼った方がいいカラム

JOINの結合条件で利用している内部表(結合対象のテーブル)のカラム
- カラムの値が一致する内部表のレコードをインデックスを利用して見つけることができるので、フルテーブルスキャンしなくて済む。フルテーブルスキャンの場合、一致する行を見つけるために計算量がo(n)だけど、インデックスを利用した場合、計算量はo(n)に抑えることができる。
- この際、JOINの駆動表(JOINする際に最初にアクセスされるテーブル)は、行数が少ない方が結合回数が少ないので良い。FROMに書いたテーブルが駆動表になるとはかぎらない。どちらのテーブルを駆動表にするかは、オプティマイザが勝手にやってくれるので、人間はそこは気にしなくていい。
- EXPLAINを使えばどっちが駆動表かを確認できる
- MySQLではJOIINのアルゴリズムにNested Loopが採用されている。２重ループで結合している
WHERE句の条件に指定しているカラム
OREDER BYに指定しているカラム
NULL値が多いデータから、NULL値以外の検索をするカラム
原則カーディナリティ(選択度)が高いカラムにインデックスを貼った方が良い。
- 男か女かのカラムにインデックスを貼っても半分までしか絞り込めない。(この場合性別カラムのカーディナリティは２)
- カーディナリティはshow indexコマンドで確認できる
- カーディナリティが低いカラムに対してインデックスを作ってもフルスキャンした方が楽なので、そもそも作る意味がないらしい

↓ 参考記事

https://zenn.dev/hk_206/articles/ec5f4e347caff4#原則はカー�[…]1�貼っていく（編集済み）

ハガユウキ

ここ一ヶ月くらい仕事のモチベーションが全く上がってなかったけど、「昨日の自分より１％でも強くなる」って思うようにしたら、すごくモチベーション上がってきた
がんばろ

ハガユウキ

EXPLAINの読み方

id
- クエリの実行順序を表す
- 複数のクエリがまとめて実行される場合、同一のidが複数表示される
- JOINの場合は、駆動表、内部表の順番で表示される
select_type
- クエリの種類を表す
- クエリの種類とはJOIN、サブクエリ、UNIONおよびそれらの組み合わせ
- 単一テーブルに対するクエリとJOINの場合、select_typeはSIMPLEになる
table
- アクセス対象のテーブルを表す
type
- レコードアクセスタイプを表す
  - つまり、対象のテーブルに対して、どのような方法でアクセスするかを示す。
  - 致命的なクエリはこのフィールドを見れば一目でわかるので、とても重要なフィールド。
  - indexまたはALLを見かけたら、すかさずクエリチューニングをする
- constは、PRIMARY KEYまたはユニークインデックスによるアクセス。最速
- eq_refは、JOIN時にPrimary keyもしくはユニークインデックスを利用したアクセス
- refは、ユニークではないインデックス(PRIMARY or UNIQUE)ではないインデックスを使って等価計算（WHERE key = value）を行ったときに使われるアクセスタイプ
- rangeは、インデックスを用いた範囲検索を表す
- indexは、フルインデックススキャン。インデックス全体をスキャンする必要があるのでとても遅い
- ALLは、フルテーブルスキャン。インデックスが全く使われないことを表す
possible_keys
- オプティマイザがテーブルのアクセスに利用可能なインデックスの候補として挙げたキーの一覧を表す。
- インデックスがないならこのフィールドにはNULLが入る
key
- オプティマイザによって選択されたキー。
- インデックスを適用していないなら、このフィールドにはNULLが入る
ref
- 検索条件で、keyと比較されている値やカラムの種類を表す。定数が指定されている場合は、constと表示される
rows
- そのテーブルからフェッチされる行数の見積もり。あくまでも大まかな見積もりなので、実際にフェッチされる正確な行数ではない。
Extra
- 意外と重要なフィールド。
- Extraフィールドは、そのクエリを実行するためにオプティマイザがどのような戦略を選択したかということを示すフィールドである。
- Using Whereは、WHERE句に検索条件が指定されており、かつインデックスを見ただけではWHERE句の条件を全て適用することが出来ない場合に表示される。
- Using indeは、クエリがインデックスだけを用いて解決できることを示している。
- まだまだあるけど一旦省略

EXPLAINを実行する場合、実際のデータ(本番データ)を利用することが非常に大切である。どの実行計画を選択したかが違ってくるので。
↓ 参考記事

ハガユウキ

初めてEXPLAINを実行した

EXPLAIN SELECT specific_chat_messages.*
FROM (
  SELECT *
  FROM work_spaces
  WHERE id = 1
) AS specific_work_spaces
INNER JOIN (
  SELECT *
  FROM chat_channels
  WHERE id = 5
) AS specific_chat_channels
INNER JOIN (
  SELECT *
  FROM chat_messages
  WHERE body LIKE "%MySQL%"
) AS specific_chat_messages
ON specific_chat_channels.id = specific_chat_messages.chat_channel_id;

実行計画みたけど、大丈夫そう。
最後の行のtypeでrefが使われているのは、一対多の外部キーによるインデックスだからか(このインデックスはユニークじゃない)。

ハガユウキ

EXPLAIN ANALYZE初めて知った。。

EXPLAIN ANALYZEは実際にクエリを実行して各ステップでの実行時間を測定してくれます。そのため、クエリのどのステップに時間がかかっているかをより正確に掴むことができます。

EXPLAIN と併用し使うとよさそう

ハガユウキ

EXPLAINで表示されている表の順序がJONの順序を表している。
つまり、EXPLAINで実行計画を見れば、どのテーブルが駆動表か、内部表か、がわかる。

ハガユウキ

joinを使うクエリでなんでフルテーブルスキャンするねんって思ったけど、JOINのアルゴリズム的に普通のことか。
駆動表をwhereとかで絞れるなら、検索対象のカラムに対してインデックスを貼った方が良いけど、駆動表に対してのwhereがないなら、ALLでフェッチするのは普通だな。

EXPLAIN SELECT chat_threads.*
FROM chat_threads
INNER JOIN user_chat_threads
ON chat_threads.id = user_chat_threads.chat_thread_id;

ハガユウキ

そうか、駆動表とか内部表ってJOINの文脈で出るんだった。
結合するときは駆動表のレコード数を少なくした方が、駆動表へのアクセス数 + (駆動表へのアクセス数 * 内部表へのアクセス数)が最小になるので、JOINのコストが最小になる。

ハガユウキ

親テーブルと子テーブルをJOINしたクエリをEXPLAINで解析すると、親テーブルを駆動表とするパターンが多くて何でって思ってたけど、
駆動表のレコード数が小さい方がJOINのコストが最小になるから、だからそういう実行計画を選択してたんか

ハガユウキ

なぜ後方一致と中間一致だとインデックスが効かなくなるのか
- そもそも前方一致でなぜインデックスが効くのかというと、検索文字列の先頭に一致するものを特定できるため。
  - ただ、さっき先頭一致に対してEXPLAINをしたらTYPEがALLだったので、曖昧検索したいなら、フルテキストインデックスのが良いかも
- 後方一致と中間一致は、そもそもどこで一致するのかわからんので、フルテーブルスキャンになっちゃう。

ハガユウキ

中間一致、後方一致のクエリをどうにかして改善できないか試す

フルテキストインデックスを使えばうまくいきそう。
- フルテキストインデックスとは、値の一部を取得するのに最適化されているインデックス
- MySQLでフルテキストインデックスを利用するためには、事前にフルテキストインデックスを貼って、クエリでMATCH関数を利用する必要がある
- フルテキストインデックスでは、対象カラムの文字列を分割して、この分割したそれぞれの文字でインデックスが作られる

ハガユウキ

// あるテーブルのカラム情報を確認するためのコマンド
show columns from users;

// あるテーブルにどんなインデックスが存在するかを確認するコマンド
show index from users;

show index from chat_messages;を実行したら、MySQLでは外部キーにもインデックスが自動で作られてんだなってことをちゃんと確認できた。
後、show columns from users;で外部キーのカラムがデフォルトではNULLを許容するのも確認できた

ハガユウキ

フルテキストインデックスの例

// テーブル定義
FULLTEXT KEY ft_index_chat_messages_on_body (body) WITH PARSER ngram

  // クエリ
  SELECT *
  FROM chat_messages
  WHERE MATCH(body) AGAINST ("MySQL" IN NATURAL LANGUAGE MODE)

この記事が参考になった

ハガユウキ

mysql大体わかってきたから、ポスグレもやってみたい

ハガユウキ

select
	item_id,
	item_name
from
	items
where
	supplier_id = '123'
and
	item_category_id = 456;

テーブル・インデックスの情報は以下です。

create table items (
	item_id integer primary key,
	item_name varchar(100),
	item_category_id integer,
	supplier_id varchar(10)
);

CREATE INDEX idx_supplier ON items (supplier_id);  
CREATE INDEX idx_category ON items (item_category_id);

そうか、なんか勘違いしていたけど、複数のインデックスが貼られている場合、どちらのインデックスも使われるってわけではなくて、処理の早い方のインデックスが採用されるのか。
EXPLAINで実行計画見ればいい話か。

ハガユウキ

BLOB（Binary Large OBjectの略）型とTEXT型は、VARBINARY型やVARCHAR型で扱うことができなかった大きなサイズのファイルや文字列を扱うために利用します。

ハガユウキ

そうか、外部キー制約がNULLを許容するってことは、つまり、親が存在しない子供を強要するってことか。
NULLもNULLという値としてインデックスに含まれるのか。
これ参考になった。
記事が複数の人によって編集されるってことは全然あり得るので、多対多になるのか。ただこの仕様は最初からあるわけではないので、article_idにユニーク制約を設定すれば、多対多のテーブル設計で一対多を表現できるのか。
ユーザーと記事に対してもリレーションを貼った方がいいかも。いやでもこれモデルのアソシエーションの話か

ハガユウキ

Boolean型のカラムが何かのイベントだった場合、この記事を見た方がいいかも
(要は、別テーブルとして定義するか日付型のカラムを用意するかを書いている
https://zenn.dev/stomk/articles/3cf43aadf24531)

ハガユウキ

閉包テーブルはこの記事が地味にわかりやすい

ハガユウキ

on delete設定するとき見よう

ハガユウキ

csvからインポート、面白そうだからやってみよう
https://sql55.com/query/bulk-insert.php

ハガユウキ

マクロな視点と、ミクロな視点を持つってのはめちゃくちゃ大事。
どちらが良いってわけではなくて、状況に応じて様々な視点で見て、知って、最適な選択肢や解決策を考えるのが大事。
https://career-selection.com/macromicro

このスクラップは2023/09/24にクローズされました