■設計
SQLアンチパターン勉強会 - Qiita
https://qiita.com/iwata@github/items/9a47ddababd8e0a82662
ユーザ情報を保存する時のテーブル設計 - そーだいなるらくがき帳
https://soudai.hatenablog.com/entry/2018/05/01/204442
ソーシャルゲームのためのMySQL入門 - Technology of DeNA
https://engineer.dena.jp/2010/11/mysql-for-socialgame.html
MySQLテーブル設計入門
https://www.slideshare.net/yoku0825/mysql-47591576
■データベースの文字コード
utf8_unicode_ciではなく、utf8_general_ciにしておくのが無難
MySQLのutf8_general_ciとutf8_unicode_ciの違い
http://program.sagasite.info/wiki/index.php?MySQL%E3%81%AEutf8_general_ci%E3%81%A8utf8_unicode_ci%E3...
utf8_unicode_ci に対する日本の開発者の見解
http://blog.kamipo.net/entry/2015/03/08/145045
MySQLの照合順序
http://qiita.com/Vit-Symty/items/159c27d7d62c78ee9ce7
※今はutf8だけでなく、utf8mb4についても検討する
■少数を管理する
0.00〜100.00 までの値を管理する場合、合計5桁で小数部分が2桁なので、以下のように指定する
-100.00〜100.00 までの値を管理する場合も、同様の定義で格納できる（マイナスがついていても格納できる）
rate DECIMAL(5, 2) NOT NULL COMMENT '受注率',
※MySQL バージョン 3.23 より前のバージョンでは引数に、符号と小数点に必要なスペースを含める必要があったらしい
【MySQL】データ型一覧 - ysklog
http://ysklog.net/mysql/1797.html
数値型(データ型)のまとめ - [MySQL/SQL] ぺんたん info
http://pentan.info/sql/mysql/mysql_type_num.html
■真偽値を管理する
MySQLに boolean の型は無いので、以下のようにして対応する
verified TINYINT(1) UNSIGNED NOT NULL COMMENT 'データ確認',
■暗黙の型変換
code が 528 と 528A のデータがあった場合、以下のように検索すると両方ヒットしてしまうので注意
SELECT * FROM products WHERE code = 528;
以下のように型を明示的にして検索する必要がある
SELECT * FROM products WHERE code = '528';
PDOの場合、以下のようにして型を明示できる
PDO::PARAM_STR
PDO::PARAM_INT
PDO::PARAM_BOOL
MySQLが文字列と数値の比較の際に自動的に変換してしまう件 | 創作メモ帳
https://sousaku-memo.net/php-system/123
SQLの暗黙の型変換はワナがいっぱい | 徳丸浩の日記
https://blog.tokumaru.org/2009/09/implicit-type-conversion-of-SQL-is-trap-full.html
■ロックによる排他制御
MySQLのプロセス確認と強制終了（デッドロック解除）の方法は、サーバメモの Command.txt の「MySQLでプロセスを確認＆強制終了」を参照
ロックについては、このファイル内の「トランザクション」についても参照（トランザクション分離レベルについては注意が必要）
以下は以前に試したときのメモ
FOR UPDATE による行ロックの具体例がある
（この内容以外にも、トランザクション分離レベルについては注意が必要。またボタンを連打されなようにする処理も入れておくべき）
Webアプリケーションへの同時アクセス対策メモ | refirio.org
http://refirio.org/view/367
CREATE TABLE や TRUNCATE TABLE を実行した場合は、暗黙的にコミットされてロックが外れる（テンポラリテーブルの場合は外れない）
…など例外的な挙動もあるようなので注意する。要勉強
以下は参考になりそうなサイト
嵐のコンサートがあるとダブルブッキングしてしまうホテル予約システムを作ってみた | 徳丸浩の日記
https://blog.tokumaru.org/2015/05/blog-post.html
MySQLのINSERT/UPDATE時におこる不整合対策 - Slow Dance
http://d.hatena.ne.jp/LukeSilvia/20110123/p1
MySQL - InnoDBのロック関連まとめ - Qiita
https://qiita.com/mizzwithliam/items/31fb68217899bd0559e8
MySQL テーブルのロック - とみぞーノート
http://wiki.bit-hive.com/tomizoo/pg/MySQL%20%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%AE%E3%83%AD%E...
デッドロックを回避するために - 技術ブログ | 株式会社クラウディア
https://cloudear.jp/blog/?p=1335
MySQLでINSERTのデッドロックに嵌る人を1人でも減らすために - ichirin2501's diary
https://ichirin2501.hatenablog.com/entry/2015/12/24/164916
doc/innodb.md at master - ichirin2501/doc
https://github.com/ichirin2501/doc/blob/master/innodb.md
MySQLでSELECT FOR UPDATEと行ロックの挙動を検証してみた - JUST FOR FUN
http://taiga.hatenadiary.com/entry/2018/02/12/170109
MySQLのトランザクション処理中にALTERするとコミットされる - ペチパーノート
http://butterbull.hatenablog.com/entry/2014/06/12/150756
ソシャゲエンジニアの自分が開発に必須だなと思った知識（MySQL編） - Qiita
https://qiita.com/west-hiroaki/items/ea6ee53765282a9c86cb
第75回 MySQLのさまざまなタイムアウトオプションについて：MySQL道普請便り｜gihyo.jp … 技術評論社
https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0075
InnoDB の行レベルロックについて解説してみる - あらびき日記
https://abicky.net/2016/11/30/082130/
MySQL のデッドロックを調査した - エムティーアイ エンジニアリングブログ
https://tech.mti.co.jp/entry/2017/12/27/190733
なぜあなたは SHOW ENGINE INNODB STATUS を読まないのか - そーだいなるらくがき帳
https://soudai.hatenablog.com/entry/2017/12/20/030013
MySQL(InnoDB)の行ロック - フリエン生活
https://free-engineer.life/mysql-innodb-record-locks/
■トランザクション
■ロールバック
【忘備録】InnoDBでもロールバックが効かない文
http://mementomori.info/%E3%80%90%E5%BF%98%E5%82%99%E9%8C%B2%E3%80%91innodb%E3%81%A7%E3%82%82%E3%83%...
MySQLでTruncateはRollback出来るのか？ | SRIA BLOG - 宮城県仙台市のWEBシステム開発・スマホアプリ開発
https://www.sria.co.jp/blog/2014/08/mysql-can-do-rollback-truncate/
MySQLの「暗黙のトランザクションコミット」対策：トランザクション中でも安全にCREATE TABLEなどをする方法 - Qiita
https://qiita.com/suin/items/3527297a22632f3db31d
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.3.3 暗黙的なコミットを発生させるステートメント
https://dev.mysql.com/doc/refman/5.6/ja/implicit-commit.html
■トランザクション分離レベル
MySQLのInnoDBのトランザクション分離レベルは、デフォルトで REPEATABLE READ となっている
REPEATABLE READ はトランザクション開始後にテーブルの値を変更しても、SELECT で参照できるのは変更前の値
これにより、例えば予約システムなどで
1. トランザクションを開始する
2. 現在の予約数を取得する
3. 取得した予約数が一定件数以下なら予約データを登録する。一定件数以上ならエラーにする
という処理を書いたとき、2で取得できるデータは1時点のもの
つまり3に到達した時点で予約数はさらに増えている可能性がある
つまり「定員が100人のイベントに105人予約されてしまった」という現象が起こりうる
トランザクション分離レベルを「READ COMMITTED」にすれば変更後の値を参照でき、Oracle、PostgreSQL、SQL Server などではデフォルト設定となっている
そちらの方が直感的なので、変更しておくと余計なトラブルを防ぐことができる（常に変更して良いものかは要検討）
以下のSQLを発行することで、その接続だけトランザクション分離レベルを変更できる
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
MySQLでトランザクションの4つの分離レベルを試す - FAT47の底辺インフラ議事録
http://d.hatena.ne.jp/fat47/20140212/1392171784
MySQLのデフォルトのトランザクション分離レベルは SELECT がスナップショットを参照する - ngyukiの日記
http://ngyuki.hatenablog.com/entry/2013/02/02/202558
[RDBMS][SQL]トランザクション分離レベルについて極力分かりやすく解説 - Qiita
https://qiita.com/PruneMazui/items/4135fcf7621869726b4b
InnoDBにおけるトランザクション分離レベルについて - Qiita
https://qiita.com/taisho6339/items/1097c67a54cedf9504d6
現在のトランザクション分離レベルは、以下のSQLで確認できる
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
第47回 トランザクション分離レベルを変更する：MySQL道普請便り｜gihyo.jp … 技術評論社
https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0047
MySQLのautocommitとトランザクション分離レベルのメモ - Qiita
https://qiita.com/rubytomato@github/items/562a1638191aacaeb333
MySQLがこのような挙動になっているのは、バイナリログの整合性を担保するための名残らしい
MySQL InnoDBのネクストキーロック おさらい - SH2の日記
http://d.hatena.ne.jp/sh2/20090112
■一斉アクセスを再現してテストする
abツールやec2-benchを使うことで、一斉アクセスを再現してテストできる
詳細は、Tuning.txt の「Apache チューニング」「ec2-bench」を参照
また、例えばPHPならプログラム内に sleep(5) などを入れることで処理待ちをさせることができる
そのうえで複数ブラウザから同時にアクセスさせるなどして、一斉アクセスを再現するという手もある
abツールやec2-benchは「特定のページへの1リクエスト」によるテストになりがちなので、
後者の方がより運用中環境に近い状況を再現できる可能性はある
■監視
MySQLの監視 ~ mackerel-plugin-mysqlを読み解く - そーだいなるらくがき帳
https://soudai.hatenablog.com/entry/mackerel-plugin-mysql
■チューニング
MySQLのExplainを確認する - woshidan's blog
https://woshidan.hatenablog.com/entry/2015/06/20/165817
MySQLのexplainとかについてしらべたときのメモ - Qiita
https://qiita.com/lastcat_/items/de7b530a94fbcf9ba646
漢(オトコ)のコンピュータ道: MySQLのEXPLAINを徹底解説!!
http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
漢(オトコ)のコンピュータ道: MySQLを高速化する10の方法
http://nippondanji.blogspot.jp/2009/02/mysql10.html
ヤフー社内でやってるMySQLチューニングセミナー大公開
https://www.slideshare.net/techblogyahoo/mysql-58540246
Cygamesを支えるPHPと、その高速化の取り組み // Speaker Deck
https://speakerdeck.com/cygames/cygameswozhi-eruphpto-sofalsegao-su-hua-falsequ-rizu-mi
秒間100万クエリを受け付ける大規模ソーシャルゲームのバックエンドDBシステムの設計・運用ノウハウ // Speaker Deck
https://speakerdeck.com/cygames/miao-jian-100mo-kueriwoshou-kefu-keruda-gui-mo-sosiyarugemufalsebatu...
MySQL with InnoDB のインデックスの基礎知識とありがちな間違い - クックパッド開発者ブログ
http://techlife.cookpad.com/entry/2017/04/18/092524
MySQLでWhere句内でOR演算子を使うと「インデックスが効かなくなる」は本当か
https://zenn.dev/nakasone/articles/ce989fa4cbe4c2
これだけ見れば大丈夫！ーMySQLパフォーマンス監視のツボ（クエリ編） | 株式会社インフィニットループ技術ブログ
http://www.infiniteloop.co.jp/blog/2012/03/mysql-tuning-cacti-query/
これだけ見れば大丈夫！ーMySQLパフォーマンス監視のツボ（システム編） | 株式会社インフィニットループ技術ブログ
https://www.infiniteloop.co.jp/blog/2013/08/mysql-tuning-cacti-system/
MySQL クエリーキャッシュ 【チューニング方法とかも】 - Qiita
https://qiita.com/ryurock/items/9f561e486bfba4221747
MySQL クエリーキャッシュ 【チューニング方法とかも】 - Qiita
https://qiita.com/ryurock/items/9f561e486bfba4221747
DSAS開発者の部屋:クエリキャッシュは切ったほうがいいんじゃなイカ？
http://dsas.blog.klab.org/archives/52021866.html
MySQLでプライマリキーをUUIDにする前に知っておいて欲しいこと | Raccoon Tech Blog [株式会社ラクーンホールディングス 技術戦略部ブログ]
https://techblog.raccoon.ne.jp/archives/1627262796.html
■mysqldump
mysqldumpまとめ - Qiita
https://qiita.com/PlanetMeron/items/3a41e14607a65bc9b60c
■データ取得
■ソート
MySQLでORDER BYをつけないときの並び順 - かみぽわーる
https://blog.kamipo.net/entry/2016/12/24/234944
「MySQLでORDER BYがついていないときに返す結果セットの並び順は定められていない。」とされている
並び順を指定しないと、稀に意図しない順でデータが取得されるので注意
【MySQL】昇順ソートで、NULLを最後に表示する - MySQL < 技術ブログ < 株式会社プロネット | WEBシステム開発20年以上の実績
https://www.p-nt.com/technicblog/archives/123
■グループ
MySQLでgroup byの結果を逆順（最大順、最新順）で取得する方法
https://xov.jp/e/176/
【MySQL】グループ毎に連番を振る - Qiita
https://qiita.com/toyottoyo/items/813338f4756dee41e49b
■その他
MySQLで全文検索 - FULLTEXTインデックスの基礎知識｜blog｜たたみラボ
http://www.tatamilab.jp/rnd/archives/000389.html
MySQL FULLTEXT + Ngram : LIKE検索より数十倍高速な、お手軽 日本語全文検索 について｜blog｜たたみラボ
http://www.tatamilab.jp/rnd/archives/000390.html
MySQLでの近似値順での値の取得について質問させてください。 【OKWAVE】
https://okwave.jp/qa/q6056886.html
SQL関数coalesceの使い方と読み方 | データベース | DoRuby
https://doruby.jp/users/akio0911_on_rails/entries/SQL_coalesce_
MYSQLの関数 COALESCE - Qiita
https://qiita.com/mikakane/items/1e45c2a798d0c7edffda
【MySQL】二つのカラムの組み合わせでユニーク設定するには. - My... - Yahoo!知恵袋
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1252536032
そろそろSQLのウィンドウ関数を理解したい - 連載1/3話 - Qiita
https://qiita.com/w-sato-ist/items/63600a3ab84aad38e879
SQL記述者全員が理解すべきSELECT文の実行順序のお話 - Qiita
https://qiita.com/k_0120/items/a27ea1fc3b9bddc77fa1
■データ登録
InnoDBの8KBの壁にぶち当たったら。 - sawara.me
http://sawara.me/mysql/2219/
ダミーデータを作成する。 insert_generate_series - イノベートな非日常
http://d.hatena.ne.jp/IT7C/20140520/1400522465
■「0000-00-00 00:00:00」のデータを登録する
データ登録時に以下のエラーになった場合、
ERROR 1292 (22007) at line 2419: Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 10
SQLの設定を変更することで登録できるようになる
（「NO_ZERO_IN_DATE」と「NO_ZERO_DATE」の項目を削除している）
とは言え、そもそも「0000-00-00 00:00:00」というのはおかしなデータなので、可能ならデータを「1970-01-01 00:00:00」やNULLにしておく方が好ましい
# mysql -u root -p
> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
> SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)
> SELECT @@GLOBAL.sql_mode;
+--------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode |
+--------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
> exit
Bye
Docker環境の場合、mysqlコンテナを停止＆起動すると設定がリセットされた
この場合、my.confで以下のように指定することで恒久的に対処できた
[mysqld]
character-set-server=utf8
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[client]
default-character-set=utf8
MySQLのsql_modeのせいで'0000-00-00'登録時にエラーが出た昔話。 - Qiita
https://qiita.com/tentatsu/items/fd2177777412ebc2f6b9
【MySQL】DATE/DATETIME型の'0000-00-00'について整理してみる - Qiita
https://qiita.com/rhap/items/26b3e796c68a2adaccf5
Data truncation: Incorrect datetime value: ‘0000-00-00 00:00:00’ | DriftwoodJP
https://www.d-wood.com/blog/2020/02/20_11802.html
■代理キー管理のテーブルにINSERTする
あくまでも一例だが、以下のような方法はある
INSERT INTO examinations(school_id, name, sort) VALUES(99999, 'AOエントリー', 1);
INSERT INTO examinations(school_id, name, sort) VALUES(99999, 'A入試', 2);
INSERT INTO examinations(school_id, name, sort) VALUES(99999, 'B入試', 3);
INSERT INTO examinations(school_id, name, sort) VALUES(99999, '二次入試', 4);
UPDATE examinations SET school_id = (SELECT id FROM schools WHERE code='test') WHERE school_id = 99999;
■データ削除
■TRUNCATEで削除
一例だが以下のようにすると、データをすべて削除できる
DELETEと違い、オートインクリメントもリセットされる
TRUNCATE TABLE users;
全データの削除(TRUNCATE文) - データの追加と削除 - MySQLの使い方
https://www.dbonline.jp/mysql/insert/index12.html
■一定期間経過したデータを削除
一例だが以下のようにすると、一定期間経過したデータをそれぞれ削除できる
# 30秒
DELETE FROM histories WHERE (created < DATE_SUB(CURDATE(), INTERVAL 30 SECOND));
# 30分
DELETE FROM histories WHERE (created < DATE_SUB(CURDATE(), INTERVAL 30 MINUTE));
# 12時間
DELETE FROM histories WHERE (created < DATE_SUB(CURDATE(), INTERVAL 12 HOUR));
# 1日
DELETE FROM histories WHERE (created < DATE_SUB(CURDATE(), INTERVAL 1 DAY));
# 1週間
DELETE FROM histories WHERE (created < DATE_SUB(CURDATE(), INTERVAL 1 WEEK));
# 10日
DELETE FROM histories WHERE (created < DATE_SUB(CURDATE(), INTERVAL 10 DAY));
# 1ヶ月
DELETE FROM histories WHERE (created < DATE_SUB(CURDATE(), INTERVAL 1 MONTH));
# 半年
DELETE FROM histories WHERE (created < DATE_SUB(CURDATE(), INTERVAL 6 MONTH));
以下のページで、Cronと合わせて自動削除する例が紹介されている
【MySQL】一定期間、特定の日付より以前のレコードを削除する cron設定有
https://web-creators-hub.com/%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9/mysql_date_del/
■データ操作
データベース作成
CREATE DATABASE test DEFAULT CHARACTER SET utf8mb4;
データベース削除
DROP DATABASE test;
データベース仕様確認
SHOW CREATE DATABASE test;
テーブル作成
CREATE TABLE address(
no INT,
name VARCHAR(80),
tel VARCHAR(80)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'アドレス';
テーブル名変更
ALTER TABLE address RENAME TO address2;
RENAME TABLE address TO address2;
MySQLの「ALTER TABLE RENAME」と「RENAME TABLE」 - なからなLife
https://atsuizo.hatenadiary.jp/entry/2016/06/16/100000
テーブル削除
DROP TABLE address;
テーブル仕様確認
SHOW COLUMNS FROM address;
SHOW FULL COLUMNS FROM address;
SHOW CREATE TABLE address;
登録
INSERT INTO address(no, name, tel) VALUES(1, '山田太郎', '090-1234-5678');
INSERT INTO address VALUES(1, '山田太郎', '090-1234-5678');
表示
SELECT name, tel FROM address;
SELECT token, COUNT(*) FROM devices GROUP BY token;
更新
UPDATE address SET name = '山田健二' WHERE name = '山田太郎';
UPDATE address SET name = '山田健二', tel = '090-2345-6789' WHERE name = '山田太郎';
削除
DELETE FROM address WHERE name = '山田太郎';
DELETE FROM products WHERE id >= 5001;
複製
INSERT INTO address_test SELECT * FROM address WHERE no >= 3;
オートインクリメントをリセット
ALTER TABLE products AUTO_INCREMENT = 0;
日付をフォーマットして表示
SELECT DATE_FORMAT(created, '%Y-%m-%d') AS created FROM histories GROUP BY created ORDER BY id DESC LIMIT 100;
日付ごとにランキング
SELECT
DATE_FORMAT(created, '%Y-%m-%d') AS created,
COUNT(*) AS count
FROM
histories
GROUP BY
DATE_FORMAT(created, '%Y-%m-%d')
ORDER BY
count DESC
LIMIT
100;
連結して表示
SELECT
histories.created_at,
histories.user_id,
histories.device_id,
devices.token
FROM
histories LEFT JOIN devices ON histories.device_id = devices.id
WHERE
histories.detail IS NULL
ORDER BY
histories.id
LIMIT
100
;
SELECT
devices.token,
COUNT(devices.token)
FROM
histories LEFT JOIN devices ON histories.device_id = devices.id
WHERE
histories.detail IS NULL AND histories.created_at = '2019-02-08 15:02:29'
GROUP BY
devices.token
;
データ件数を一括確認
SELECT
table_name, table_rows from information_schema.TABLES
WHERE
table_schema = '対象データベース名'
;
MySQLで全テーブルのレコード数を一覧表示する - Qiita
https://qiita.com/isobecky74/items/6efff247484f8e4fccae
インデックスを確認
SHOW INDEX FROM histories;
インデックスを一括確認
SELECT
TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX
FROM
information_schema.STATISTICS
WHERE
TABLE_SCHEMA = '対象データベース名'
ORDER BY
TABLE_SCHEMA, TABLE_NAME,INDEX_NAME, SEQ_IN_INDEX
;
MySQLでDB内の全てのインデックスの一覧をCSV出力する｜エンジニア初心者の備忘録
http://rigil.iku4.com/mysql_command/index
■ウインドウ関数
MySQL の Window 関数を完全に理解する - Qiita
https://qiita.com/ngyuki/items/c35dd314815939ffbeb6
以下、MariaDB 10.4.8 で検証
■簡単な例
CREATE TABLE users(
uid INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
gid INT NOT NULL,
value INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'ユーザ';
INSERT INTO users VALUES( 1, 100, 1);
INSERT INTO users VALUES( 2, 200, 2);
INSERT INTO users VALUES( 3, 100, 10);
INSERT INTO users VALUES( 4, 200, 20);
INSERT INTO users VALUES( 5, 100, 100);
INSERT INTO users VALUES( 6, 200, 200);
INSERT INTO users VALUES( 7, 100, 1000);
INSERT INTO users VALUES( 8, 200, 2000);
INSERT INTO users VALUES( 9, 100, 10000);
INSERT INTO users VALUES(10, 200, 20000);
GROUP BY で gid ごとの合計を表示した場合
SELECT
gid,
SUM(value) AS total
FROM
users
GROUP BY
gid
;
/*
+-----+-------+
| gid | total |
+-----+-------+
| 100 | 11111 |
| 200 | 22222 |
+-----+-------+
*/
以下はWindow関数を使用した例
「PARTITION BY gid」によって、行の範囲を指定している
SELECT
uid,
gid,
value,
SUM(value) OVER (PARTITION BY gid) AS total
FROM
users
;
/*
+-----+-----+-------+-------+
| uid | gid | value | total |
+-----+-----+-------+-------+
| 7 | 100 | 1000 | 11111 |
| 1 | 100 | 1 | 11111 |
| 9 | 100 | 10000 | 11111 |
| 3 | 100 | 10 | 11111 |
| 5 | 100 | 100 | 11111 |
| 8 | 200 | 2000 | 22222 |
| 2 | 200 | 2 | 22222 |
| 10 | 200 | 20000 | 22222 |
| 4 | 200 | 20 | 22222 |
| 6 | 200 | 200 | 22222 |
+-----+-----+-------+-------+
*/
行の範囲をしていなければ、全体を対象に処理される
SELECT
uid,
gid,
value,
SUM(value) OVER () AS total
FROM
users
;
/*
+-----+-----+-------+-------+
| uid | gid | value | total |
+-----+-----+-------+-------+
| 1 | 100 | 1 | 33333 |
| 2 | 200 | 2 | 33333 |
| 3 | 100 | 10 | 33333 |
| 4 | 200 | 20 | 33333 |
| 5 | 100 | 100 | 33333 |
| 6 | 200 | 200 | 33333 |
| 7 | 100 | 1000 | 33333 |
| 8 | 200 | 2000 | 33333 |
| 9 | 100 | 10000 | 33333 |
| 10 | 200 | 20000 | 33333 |
+-----+-----+-------+-------+
*/
以下はウインドウを明示的に定義して使う例（定義しなければ暗黙的に定義されたことになる）
Oracleでは明示的に定義することができないので、暗黙的に定義される方法の方が汎用性が高い
SELECT
uid,
gid,
value,
SUM(value) OVER w AS total
FROM
users
WINDOW
w AS (PARTITION BY gid)
;
/*
+-----+-----+-------+-------+
| uid | gid | value | total |
+-----+-----+-------+-------+
| 7 | 100 | 1000 | 11111 |
| 1 | 100 | 1 | 11111 |
| 9 | 100 | 10000 | 11111 |
| 3 | 100 | 10 | 11111 |
| 5 | 100 | 100 | 11111 |
| 8 | 200 | 2000 | 22222 |
| 2 | 200 | 2 | 22222 |
| 10 | 200 | 20000 | 22222 |
| 4 | 200 | 20 | 22222 |
| 6 | 200 | 200 | 22222 |
+-----+-----+-------+-------+
*/
OVER 句には ORDER BY も指定できる
ORDER BY は Window 関数がパーティションの中で行を処理する順番を指定できる
SELECT
uid,
gid,
value,
ROW_NUMBER() OVER (PARTITION BY gid ORDER BY value) AS num
FROM
users
;
/*
+-----+-----+-------+-----+
| uid | gid | value | num |
+-----+-----+-------+-----+
| 1 | 100 | 1 | 1 |
| 3 | 100 | 10 | 2 |
| 5 | 100 | 100 | 3 |
| 7 | 100 | 1000 | 4 |
| 9 | 100 | 10000 | 5 |
| 2 | 200 | 2 | 1 |
| 4 | 200 | 20 | 2 |
| 6 | 200 | 200 | 3 |
| 8 | 200 | 2000 | 4 |
| 10 | 200 | 20000 | 5 |
+-----+-----+-------+-----+
*/
■フレームの概念
前述の ORDER BY を SUM で使用すると次のような結果になる
gid で分割されたパーティションの中を value の昇順に並べ、パーティションの先頭から現在行までが順に集計される
SELECT
uid,
gid,
value,
SUM(value) OVER (PARTITION BY gid ORDER BY value) AS total
FROM
users
;
/*
+-----+-----+-------+-------+
| uid | gid | value | total |
+-----+-----+-------+-------+
| 1 | 100 | 1 | 1 |
| 3 | 100 | 10 | 11 |
| 5 | 100 | 100 | 111 |
| 7 | 100 | 1000 | 1111 |
| 9 | 100 | 10000 | 11111 |
| 2 | 200 | 2 | 2 |
| 4 | 200 | 20 | 22 |
| 6 | 200 | 200 | 222 |
| 8 | 200 | 2000 | 2222 |
| 10 | 200 | 20000 | 22222 |
+-----+-----+-------+-------+
*/
この「パーティションの先頭から現在行まで」のような範囲のことを「フレーム」と呼ぶ
フレームの範囲も OVER 句で指定できる。次の「ROWS 〜」の部分がフレームの指定
フレームの終了位置はデフォルトで現在行なので、ROWS CURRENT ROW だけ指定すると「現在行〜現在行」となる
SELECT
uid,
gid,
value,
SUM(value) OVER (PARTITION BY gid ORDER BY value) AS total,
SUM(value) OVER (PARTITION BY gid ORDER BY value ROWS CURRENT ROW) AS 'CURRENT ROW',
SUM(value) OVER (PARTITION BY gid ORDER BY value ROWS UNBOUNDED PRECEDING) AS 'UNBOUNDED PRECEDING',
SUM(value) OVER (PARTITION BY gid ORDER BY value ROWS 1 PRECEDING) AS '1 PRECEDING'
FROM
users
;
/*
+-----+-----+-------+-------+-------------+---------------------+-------------+
| uid | gid | value | total | CURRENT ROW | UNBOUNDED PRECEDING | 1 PRECEDING |
+-----+-----+-------+-------+-------------+---------------------+-------------+
| 1 | 100 | 1 | 1 | 1 | 1 | 1 |
| 3 | 100 | 10 | 11 | 10 | 11 | 11 |
| 5 | 100 | 100 | 111 | 100 | 111 | 110 |
| 7 | 100 | 1000 | 1111 | 1000 | 1111 | 1100 |
| 9 | 100 | 10000 | 11111 | 10000 | 11111 | 11000 |
| 2 | 200 | 2 | 2 | 2 | 2 | 2 |
| 4 | 200 | 20 | 22 | 20 | 22 | 22 |
| 6 | 200 | 200 | 222 | 200 | 222 | 220 |
| 8 | 200 | 2000 | 2222 | 2000 | 2222 | 2200 |
| 10 | 200 | 20000 | 22222 | 20000 | 22222 | 22000 |
+-----+-----+-------+-------+-------------+---------------------+-------------+
*/
■ウインドウ関数の利用例（移動平均）
ウインドウ関数の典型的な利用ケースである、移動平均を求めてみる
移動平均を使えば、長期的なデータの変化を可視化することができる
算術平均
・集合のすべての値を足して、その集合の要素数で割った値
・その集合が、どのようなものかを表すことができる（例：テストの平均点）
移動平均
・直近で取得したデータポイント群で平均を計算した値
・デコボコの多いグラフをなめらかにして、傾向を把握しやすくすることができる
MySQL の Window 関数を完全に理解する - Qiita
https://qiita.com/ngyuki/items/c35dd314815939ffbeb6
SQLのWindow関数で移動平均を求める - Qiita
https://qiita.com/omokawa_yasu/items/abb8624fc57a9f4c427c
【小ネタ】Window関数で移動平均を算出する【SQL】 - Qiita
https://qiita.com/tmiki/items/739457f5df27a306a36b
32-3. 時系列データと移動平均 | 統計学の時間 | 統計WEB
https://bellcurve.jp/statistics/course/12933.html
移動平均とは?活用するメリットやエクセルでの計算方法を紹介!｜いちばんやさしい、医療統計
https://best-biostatistics.com/summary/idou-heikinn.html
[SQL] 移動平均を算出 〜Window関数を使って変動傾向を掴む〜 | DevelopersIO
https://dev.classmethod.jp/articles/sql-moving-average/
CREATE TABLE profits(
day INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
value INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '日ごとの売上';
INSERT INTO profits VALUES( 1, 57);
INSERT INTO profits VALUES( 2, 47);
INSERT INTO profits VALUES( 3, 122);
INSERT INTO profits VALUES( 4, 104);
INSERT INTO profits VALUES( 5, 34);
INSERT INTO profits VALUES( 6, 65);
INSERT INTO profits VALUES( 7, 38);
INSERT INTO profits VALUES( 8, 39);
INSERT INTO profits VALUES( 9, 43);
INSERT INTO profits VALUES(10, 102);
INSERT INTO profits VALUES(11, 87);
INSERT INTO profits VALUES(12, 58);
INSERT INTO profits VALUES(13, 43);
INSERT INTO profits VALUES(14, 71);
INSERT INTO profits VALUES(15, 45);
INSERT INTO profits VALUES(16, 43);
INSERT INTO profits VALUES(17, 142);
INSERT INTO profits VALUES(18, 101);
INSERT INTO profits VALUES(19, 58);
INSERT INTO profits VALUES(20, 67);
INSERT INTO profits VALUES(21, 48);
INSERT INTO profits VALUES(22, 65);
INSERT INTO profits VALUES(23, 75);
INSERT INTO profits VALUES(24, 123);
INSERT INTO profits VALUES(25, 134);
INSERT INTO profits VALUES(26, 67);
INSERT INTO profits VALUES(27, 63);
INSERT INTO profits VALUES(28, 63);
INSERT INTO profits VALUES(29, 76);
INSERT INTO profits VALUES(30, 48);
SELECT
day,
value,
AVG(value) OVER (
ORDER BY
day
ROWS
BETWEEN 4 PRECEDING AND CURRENT ROW
) AS `Moving AVG`
FROM
profits
;
/*
+-----+-------+------------+
| day | value | Moving AVG |
+-----+-------+------------+
| 1 | 57 | 57.0000 |
| 2 | 47 | 52.0000 |
| 3 | 122 | 75.3333 |
| 4 | 104 | 82.5000 |
| 5 | 34 | 72.8000 |
| 6 | 65 | 74.4000 |
| 7 | 38 | 72.6000 |
| 8 | 39 | 56.0000 |
| 9 | 43 | 43.8000 |
| 10 | 102 | 57.4000 |
| 11 | 87 | 61.8000 |
| 12 | 58 | 65.8000 |
| 13 | 43 | 66.6000 |
| 14 | 71 | 72.2000 |
| 15 | 45 | 60.8000 |
| 16 | 43 | 52.0000 |
| 17 | 142 | 68.8000 |
| 18 | 101 | 80.4000 |
| 19 | 58 | 77.8000 |
| 20 | 67 | 82.2000 |
| 21 | 48 | 83.2000 |
| 22 | 65 | 67.8000 |
| 23 | 75 | 62.6000 |
| 24 | 123 | 75.6000 |
| 25 | 134 | 89.0000 |
| 26 | 67 | 92.8000 |
| 27 | 63 | 92.4000 |
| 28 | 63 | 90.0000 |
| 29 | 76 | 80.6000 |
| 30 | 48 | 63.4000 |
+-----+-------+------------+
*/
例えば7dayの移動平均である「72.6000」は、以下のように求められている
つまり、現在の行（CURRENT ROW）とその前の4行（4 PRECEDING）の計5行から平均化した値を算出し、傾向を把握しやすくしている
(122 + 104 + 34 + 65 + 38) / 5 = 72.6
■達人に学ぶSQL徹底指南書 第2版
達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ（ミック）｜翔泳社の本
https://www.shoeisha.co.jp/book/detail/9784798157825
■CASEでラベルを読み替え
単純CASE式、検索CASE式とも、結果は同じ
単純CASE式の方が簡潔に書くことができるが、できることも限られている
CREATE TABLE members(
name VARCHAR(32) PRIMARY KEY,
sex INTEGER NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '名簿';
INSERT INTO members VALUES('山田太郎', 1);
INSERT INTO members VALUES('山田花子', 2);
INSERT INTO members VALUES('鈴木一郎', 1);
INSERT INTO members VALUES('佐藤次郎', 1);
INSERT INTO members VALUES('田中三郎', 1);
INSERT INTO members VALUES('岡田智子', 2);
-- 単純CASE式で読み替え
SELECT
name,
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE 'その他'
END AS sex
FROM
members
;
-- 検索CASE式で読み替え
SELECT
name,
CASE
WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE 'その他'
END AS sex
FROM
members
;
■既存のコード体系を新しい体系に変換して集計
CREATE TABLE prefs(
pref_name VARCHAR(32) PRIMARY KEY,
population INTEGER NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '都道府県';
INSERT INTO prefs VALUES('徳島', 100);
INSERT INTO prefs VALUES('香川', 200);
INSERT INTO prefs VALUES('愛媛', 150);
INSERT INTO prefs VALUES('高知', 200);
INSERT INTO prefs VALUES('福岡', 300);
INSERT INTO prefs VALUES('佐賀', 100);
INSERT INTO prefs VALUES('長崎', 200);
INSERT INTO prefs VALUES('東京', 400);
INSERT INTO prefs VALUES('群馬', 50);
-- 県名を地方名に再分類する
SELECT
CASE pref_name
WHEN '徳島' THEN '四国'
WHEN '香川' THEN '四国'
WHEN '愛媛' THEN '四国'
WHEN '高知' THEN '四国'
WHEN '福岡' THEN '九州'
WHEN '佐賀' THEN '九州'
WHEN '長崎' THEN '九州'
ELSE 'その他'
END AS district,
SUM(population)
FROM
prefs
GROUP BY
district
;
-- 人口階級ごとに都道府県を分類する
SELECT
CASE
WHEN population < 100 THEN 'A'
WHEN population >= 100 AND population < 200 THEN 'B'
WHEN population >= 200 AND population < 300 THEN 'C'
WHEN population >= 300 THEN 'D'
ELSE NULL
END AS population_class,
COUNT(*) AS cnt
FROM
prefs
GROUP BY
population_class
;
■異なる条件の集計を1つのSQLで行う
CREATE TABLE prefs(
pref_name VARCHAR(32),
sex CHAR(1) NOT NULL,
population INTEGER NOT NULL,
PRIMARY KEY(pref_name, sex)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '都道府県';
INSERT INTO prefs VALUES('徳島', '1', 60);
INSERT INTO prefs VALUES('徳島', '2', 40);
INSERT INTO prefs VALUES('香川', '1', 100);
INSERT INTO prefs VALUES('香川', '2', 100);
INSERT INTO prefs VALUES('愛媛', '1', 100);
INSERT INTO prefs VALUES('愛媛', '2', 50);
INSERT INTO prefs VALUES('高知', '1', 100);
INSERT INTO prefs VALUES('高知', '2', 100);
INSERT INTO prefs VALUES('福岡', '1', 100);
INSERT INTO prefs VALUES('福岡', '2', 200);
INSERT INTO prefs VALUES('佐賀', '1', 20);
INSERT INTO prefs VALUES('佐賀', '2', 80);
INSERT INTO prefs VALUES('長崎', '1', 125);
INSERT INTO prefs VALUES('長崎', '2', 125);
INSERT INTO prefs VALUES('東京', '1', 250);
INSERT INTO prefs VALUES('東京', '2', 150);
-- 男性の人口
SELECT
pref_name,
population
FROM
prefs
WHERE
sex = '1'
;
-- 女性の人口
SELECT
pref_name,
population
FROM
prefs
WHERE
sex = '2'
;
以下のようにすれば、1つのSQLで処理できる
-- 男性の人口と女性の人口
SELECT
pref_name,
-- 男性の人口
SUM(CASE WHEN sex = '1' THEN population ELSE 0 END) AS cnt_male,
-- 女性の人口
SUM(CASE WHEN sex = '2' THEN population ELSE 0 END) AS cnt_female
FROM
prefs
GROUP BY
pref_name
;
■条件を分岐させたUPDATE
以下の条件で更新をかけるとする
1. 現在の給料が30万円以上の社員は、10%の減給とする
2. 現在の給料が25万円以上28万円未満の社員は、20%の昇給とする
UPDATE文を2回実行すると、1回目のSQLによって値が変わるため、意図した更新ができない
CASEを使って同時に実行することで、意図した結果を得られるうえに、1回の実行で済むのでパフォーマンスもいい
CREATE TABLE personnel(
name VARCHAR(32) PRIMARY KEY,
salary INTEGER NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '従業員';
INSERT INTO personnel VALUES('相田', 300000);
INSERT INTO personnel VALUES('神崎', 270000);
INSERT INTO personnel VALUES('木村', 220000);
INSERT INTO personnel VALUES('斎藤', 290000);
-- 条件1
UPDATE
personnel
SET
salary = salary * 0.9
WHERE
salary >= 300000
;
-- 条件2
UPDATE
personnel
SET
salary = salary * 1.2
WHERE
salary >= 250000 AND salary < 280000
;
以下のようにすれば、1つのSQLで処理できる
-- 条件1と条件2を同時に実行する
UPDATE
personnel
SET
salary =
CASE
WHEN salary >= 300000 THEN salary * 0.9
WHEN salary >= 250000 AND salary < 280000 THEN salary * 1.2
ELSE salary END
;
■テーブル同士のマッチング
CASE式の大きな利点は、式を評価できること
つまりCASE式の中でBETWEEN、LIKE、<、>といった便利な述語群を利用できる
中でもINとEXISTSはサブクエリを式に取れるため、非常に強力な表現力を持つ
CREATE TABLE course_master(
course_id INTEGER PRIMARY KEY,
course_name VARCHAR(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'コース内容';
INSERT INTO course_master VALUES(1, '経理入門');
INSERT INTO course_master VALUES(2, '財務知識');
INSERT INTO course_master VALUES(3, '簿記検定');
INSERT INTO course_master VALUES(4, '税理士');
CREATE TABLE course_schedule(
month INTEGER,
course_id INTEGER,
PRIMARY KEY(month, course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'コース予定';
INSERT INTO course_schedule VALUES(201806, 1);
INSERT INTO course_schedule VALUES(201806, 3);
INSERT INTO course_schedule VALUES(201806, 4);
INSERT INTO course_schedule VALUES(201807, 4);
INSERT INTO course_schedule VALUES(201808, 2);
INSERT INTO course_schedule VALUES(201808, 4);
以下のように処理できる
結果は同じだが、パフォーマンスはEXISTS方式の方がいい
-- INでテーブル同士のマッチング
SELECT
course_name,
CASE
WHEN course_id IN (
SELECT course_id FROM course_schedule WHERE month = 201806
) THEN '○'
ELSE '×' END AS "6月",
CASE
WHEN course_id IN (
SELECT course_id FROM course_schedule WHERE month = 201807
) THEN '○'
ELSE '×' END AS "7月",
CASE
WHEN course_id IN (
SELECT course_id FROM course_schedule WHERE month = 201808
) THEN '○'
ELSE '×' END AS "8月"
FROM
course_master
;
-- EXISTSでテーブル同士のマッチング
SELECT
cm.course_name,
CASE
WHEN EXISTS (
SELECT course_id FROM course_schedule cs WHERE month = 201806 AND cs.course_id = cm.course_id
) THEN '○'
ELSE '×' END AS "6月",
CASE
WHEN EXISTS (
SELECT course_id FROM course_schedule cs WHERE month = 201807 AND cs.course_id = cm.course_id
) THEN '○'
ELSE '×' END AS "7月",
CASE
WHEN EXISTS (
SELECT course_id FROM course_schedule cs WHERE month = 201808 AND cs.course_id = cm.course_id
) THEN '○'
ELSE '×' END AS "8月"
FROM
course_master cm
;
■CASE式の中で集約関数を使う
学生番号と、その学生の所属クラブを管理するテーブルがあるとする
学生は複数のクラブに所属している（100, 200）場合もあれば、1つのクラブにしか所属していない場合もある
複数のクラブに所属している学生については、主なクラブを表すフラグ列に「Y」が入り、それ以外には「N」が入る
1つのクラブに専念している学生の場合は「N」が入る
このテーブルから、以下の条件でデータを取得する
1. 1つだけのクラブに所属している学生については、そのクラブIDを取得する
2. 複数のクラブに所属している学生については、主なクラブのIDを取得する
CREATE TABLE student_club(
std_id INTEGER,
club_id INTEGER,
club_name VARCHAR(32),
main_club_flg CHAR(1),
PRIMARY KEY (std_id, club_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '学生の所属クラブ';
INSERT INTO student_club VALUES(100, 1, '野球', 'Y');
INSERT INTO student_club VALUES(100, 2, '吹奏楽', 'N');
INSERT INTO student_club VALUES(200, 2, '吹奏楽', 'N');
INSERT INTO student_club VALUES(200, 3, 'バドミントン','Y');
INSERT INTO student_club VALUES(200, 4, 'サッカー', 'N');
INSERT INTO student_club VALUES(300, 4, 'サッカー', 'N');
INSERT INTO student_club VALUES(400, 5, '水泳', 'N');
INSERT INTO student_club VALUES(500, 6, '囲碁', 'N');
-- 条件1
SELECT
std_id, MAX(club_id) AS main_club
FROM
student_club
GROUP BY
std_id
HAVING
COUNT(*) = 1
;
-- 条件2
SELECT
std_id, club_id AS main_club
FROM
student_club
WHERE
main_club_flg = 'Y'
;
以下のようにすれば、1つのSQLで処理できる
SELECT
std_id,
CASE
WHEN COUNT(*) = 1 -- 1つだけのクラブに所属している学生の場合
THEN MAX(club_id)
ELSE MAX( -- 複数のクラブに所属している学生の場合
CASE
WHEN main_club_flg = 'Y' THEN club_id
ELSE NULL
END
) END AS main_club
FROM
student_club
GROUP BY
std_id
;
■自己結合で検索
同じテーブルを結合して、高度な検索を行うことができる
CREATE TABLE address(
name VARCHAR(32),
family_id INTEGER,
address VARCHAR(32),
PRIMARY KEY(name, family_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '住所録';
INSERT INTO address VALUES('前田 義明', '100', '東京都港区虎ノ門3-2-29');
INSERT INTO address VALUES('前田 由美', '100', '東京都港区虎ノ門3-2-92');
INSERT INTO address VALUES('加藤 茶', '200', '東京都新宿区西新宿2-8-1');
INSERT INTO address VALUES('加藤 勝', '200', '東京都新宿区西新宿2-8-1');
INSERT INTO address VALUES('ホームズ', '300', 'ベーカー街221B');
INSERT INTO address VALUES('ワトソン', '400', 'ベーカー街221B');
-- 同じ家族で、住所が違うレコードを検索する（間違って登録されたデータを探す）
SELECT
DISTINCT A1.name, A1.address
FROM
address A1 INNER JOIN address A2
ON
A1.family_id = A2.family_id AND A1.address <> A2.address
;
CREATE TABLE products(
name VARCHAR(16) NOT NULL,
price INTEGER NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '商品';
INSERT INTO products VALUES('りんご', 50);
INSERT INTO products VALUES('みかん', 100);
INSERT INTO products VALUES('ぶどう', 50);
INSERT INTO products VALUES('スイカ', 80);
INSERT INTO products VALUES('レモン', 30);
INSERT INTO products VALUES('いちご', 100);
INSERT INTO products VALUES('バナナ', 100);
-- 同じ値段で、商品名が違うレコードを検索する
SELECT
DISTINCT P1.name, P1.price
FROM
products P1 INNER JOIN products P2
ON
P1.price = P2.price
AND
P1.name <> P2.name
ORDER BY
P1.price;
■データをJSONで扱う
※未検証
MySQLでJSONを扱う - Qiita
https://qiita.com/abcb2/items/8affae03caa3e94068b5
MySQLでJSON型を使う(JSON関数編) | スマートスタイル TECH BLOG｜データベース＆クラウドの最新技術情報を配信
https://www.s-style.co.jp/blog/2019/03/3614/
もう知ってた？ MySQL 5.7でNoSQLっぽくJSONデータを扱う方法 - WPJ
https://www.webprofessional.jp/use-json-data-fields-mysql-databases/
MySQLでJSON型を使う（パフォーマンス編） | スマートスタイル TECH BLOG｜データベース＆クラウドの最新技術情報を配信
https://www.s-style.co.jp/blog/2017/06/420/
■データの複製
別名で同じ仕様のテーブルを作成し、
そこに「INSERT INTO ○○ SELECT * FROM △△ WHERE no >= 3;」で必要な分だけデータを複製し、
その後「ALTER TABLE」もしくは「RENAME TABLE」でテーブル名を変更して新テーブルに入れ替えるのが良さそう
MySQLの「ALTER TABLE RENAME」と「RENAME TABLE」 - なからなLife
https://atsuizo.hatenadiary.jp/entry/2016/06/16/100000
MySQLで3億レコード物理削除した話 - Qiita
https://qiita.com/ningenMe/items/57bd66359d89b691ba0d
■データのマスキング
MySQLで個人情報をマスキング - Qiita
https://qiita.com/murak/items/7add80e9624b1abf103a
■権限の設定
MySQL5.7からは、ユーザを作成してからGRANTする必要がある
以下は webmaster ユーザの作成例
CREATE USER webmaster@localhost IDENTIFIED BY '1234';
testデータベースに対しての全権限を与える例
GRANT ALL PRIVILEGES ON test.* TO webmaster@localhost;
環境によっては「ALL PRIVILEGES」で一括指定するとエラーになるので、必要なものを個別に指定する
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, EVENT ON test.* TO webmaster@localhost;
■データベース名の変更
※未検証
MySQL5.1.23以降は RENAME DATABASE が廃止されたらしい
一応 RENAME TABLE を使えば擬似的に対応できるらしい
MySQLでdumpせずに "RENAME DATABASE" を実現する - Qiita
https://qiita.com/whisper0077/items/06add22b692a25bd19b5
【MySQL】データベースの名前変更（リネーム、RENAME）の方法のひとつ at softelメモ
https://www.softel.co.jp/blogs/tech/archives/5319
■外部キー制約
親テーブルの更新/削除の時の挙動(ON DELETE句,ON UPDATE句) - テーブルの作成 - MySQLの使い方
https://www.dbonline.jp/mysql/table/index12.html
MySQLの外部キー制約RESTRICT,CASCADE,SET NULL,NO ACTIONの違いは？ - Qiita
https://qiita.com/suin/items/21fe6c5a78c1505b19cb
外部キー制約を使うと、テーブル間のデータ整合性を厳密に保つことができる
制約は「FOREIGN KEY(外部キー名) REFERENCES 親テーブル名(カラム名) ON オプション」のように指定でき、オプションには以下のようなものがある
RESTRICT:
親テーブルに対して削除または更新を行うとエラーとなる
設定を省略した場合は RESTRICT を設定したのと同じ
NO ACTION:
親テーブルに対して削除または更新を行うとエラーとなる
RESTRICT を設定した場合と同じ
CASCADE:
親テーブルに対して削除または更新を行うと、子テーブルで同じ値を持つカラムのデータに対して削除または更新を行う
上記のように、MySQLでは「NO ACTION」と「RESTRICT」は同じ挙動となる
以下、実際にテーブルへの関連データを登録して、外部キー制約（FOREIGN KEY 制約）などの挙動を試してみる
CREATE TABLE goods(
id INT,
name VARCHAR(10),
INDEX(name)
) ENGINE=InnoDB;
INSERT INTO goods VALUES(1, '消しゴム');
INSERT INTO goods VALUES(2, '鉛筆');
INSERT INTO goods VALUES(3, 'ボールペン');
RESTRICT
CREATE TABLE sales1(
id INT,
name VARCHAR(10),
date DATE,
INDEX(name),
FOREIGN KEY(name) REFERENCES goods(name) ON UPDATE RESTRICT
) ENGINE=InnoDB;
INSERT INTO sales1 VALUES(1, '鉛筆', '2017-01-20');
INSERT INTO sales1 VALUES(2, '消しゴム', '2017-02-05');
UPDATE goods SET name = 'ケシゴム' WHERE id = 1; #1451 - Cannot delete or update a parent row: a foreign key constraint fails
NO ACTION
CREATE TABLE sales2(
id INT,
name VARCHAR(10),
date DATE,
INDEX(name),
FOREIGN KEY(name) REFERENCES goods(name) ON DELETE NO ACTION
) engine=InnoDB;
INSERT INTO sales2 VALUES(1, '鉛筆', '2017-01-20');
INSERT INTO sales2 VALUES(2, '消しゴム', '2017-02-05');
DELETE FROM goods WHERE id = 2; #1451 - Cannot delete or update a parent row: a foreign key constraint fails
CASCADE（MySQLにおいては、「NO ACTION」と「RESTRICT」は同じ挙動）
CREATE TABLE sales3(
id INT,
name VARCHAR(10),
date DATE,
INDEX(name),
FOREIGN KEY(name) REFERENCES goods(name) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;
INSERT INTO sales3 VALUES(1, '鉛筆', '2017-01-20');
INSERT INTO sales3 VALUES(2, '消しゴム', '2017-02-05');
INSERT INTO sales3 VALUES(3, '消しゴム', '2017-03-02');
INSERT INTO sales3 VALUES(4, '鉛筆', '2017-04-26');
UPDATE goods SET name = 'ケシゴム' WHERE id = 1; # sales3 テーブルの name も更新される
DELETE FROM goods WHERE id = 1; # sales3 テーブルの name が等しいデータも削除される
上記ではテーブル作成時に制約を追加している
後から追加する場合、以下のようにALTER TABLEで追加する
制約名は必須では無いようだが、削除のときに使用するので付けておくのが無難か
以下の制約名はLaravelが自動作成した制約名。制約名はランダムな文字列を付けることが多い？
ALTER TABLE sales1 ADD CONSTRAINT FK_23A0E6661220EA6 FOREIGN KEY(name) REFERENCES goods(name)
以下のようにすれば削除できる
ALTER TABLE sales1 DROP CONSTRAINT FK_23A0E6661220EA6;
■外部キー制約を無視して登録（インポート）・削除
データの移植時、外部キー制約があるとSQLを丸ごと登録（インポート）・削除できないことがある
SQLファイルの最初に以下を追加し、
SET FOREIGN_KEY_CHECKS = 0;
最後に以下を追加すれば、一時的に外部キー制約を無視してインポートできる
SET FOREIGN_KEY_CHECKS = 1;
以下は削除コードの一例
TRUNCATE TABLE dtb_mail_history;
TRUNCATE TABLE dtb_order_pdf;
TRUNCATE TABLE dtb_url_token;
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE dtb_order_item;
TRUNCATE TABLE dtb_shipping;
TRUNCATE TABLE dtb_order;
SET FOREIGN_KEY_CHECKS = 1;
■レプリケーション
レプリケーションの設定については、サーバメモの Etcetera.txt を参照
MySQL のレプリケーションから10年間逃げてきた我々が学んだこと8選 - Cybozu Inside Out | サイボウズエンジニアのブログ
https://blog.cybozu.io/entry/2020/10/26/173000
■チューニングテスト
■テーブル
以下のテーブルに、TwitterのAPIから取得したデータを記録し続けていく
ツイートテーブルとユーザテーブルを連結して一覧表示
キーワード検索やフォロワー数ソートなどを実装して、速度や負荷の様子を見る。スロークエリの記録も試したい
CREATE TABLE `tweets` (
`id` varchar(80) NOT NULL COMMENT 'ID',
`created` datetime NOT NULL COMMENT '登録日時',
`user_id` varchar(80) NOT NULL COMMENT 'ユーザID',
`text` text NOT NULL COMMENT 'ツイート内容',
`retweeted_id` varchar(80) DEFAULT NULL COMMENT 'リツイートID',
`retweeted_user_id` varchar(80) DEFAULT NULL COMMENT 'リツイートユーザID',
PRIMARY KEY (`id`),
KEY `id` (`id`),
KEY `created` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ツイート';
CREATE TABLE `users` (
`id` varchar(80) NOT NULL COMMENT 'ID',
`created` datetime NOT NULL COMMENT '登録日時',
`name` varchar(255) NOT NULL COMMENT '名前',
`screen_name` varchar(80) NOT NULL COMMENT 'スクリーンネーム',
`location` varchar(80) DEFAULT NULL COMMENT '位置情報',
`description` text COMMENT '説明',
`url` varchar(255) DEFAULT NULL COMMENT 'URL',
`image_url` varchar(255) NOT NULL COMMENT '画像URL',
`followers_count` int(11) NOT NULL COMMENT 'フォロワー数',
`friends_count` int(11) NOT NULL COMMENT 'フォロー数',
`listed_count` int(11) NOT NULL COMMENT 'リスト数',
`statuses_count` int(11) NOT NULL COMMENT 'リツイート数',
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ユーザ';
■環境
CentOS 7 + nginx 1.12.2 + MySQL 5.7.21
https://refirio.net/twitter/
■検証内容
30万件くらいまでは、何も考えずに作れる
30万件くらいになると、「データ比較」「データ推移」が少し重い。最初の一覧やキーワード検索もほんの少し重いがまだ問題ない
50万件くらいでも十分に早い。Apache→nginxで、PHPもMySQLも新しくしているからか
60万件くらいになると、CPU使用率でuserの使用が確認できるようになってきた。ただし0.6%程度なので大したものではない
80万件くらいになると、最初の一覧でもはっきり遅いと感じる。「データ比較」はかなり重い
120万件
トップページの表示が3秒ほどかかる
データ取得時に記事数をキャッシュするようにし、それをもとにページ数などを求めるように変更
トップページが2秒ほどで表示されるようになった
levis標準機能のエクスポートではエラーになるが、execコマンドでmysqldumpを実行すればダンプできる
200万件
トップページの表示が3〜4秒ほどかかる
キーワード検索すると8〜10秒ほどかかる
データ比較は20秒ほどかかる
データ推移は3秒ほどかかる
CPU使用率は2〜3%くらい
実用的ではない程度には重い
インデックスを張る（3つ目だけで良かったかもしれないが要検証）
ALTER TABLE users ADD INDEX (id);
ALTER TABLE tweets ADD INDEX (id);
ALTER TABLE tweets ADD INDEX (created);
トップページの表示が一瞬になった
キーワード検索も一瞬になった
データ比較とデータ推移は1〜2秒で表示されるようになった
CPU使用率も下がった（2〜3%あった使用率が0.7%くらいになった）
SWAPの発生も20〜40KB/sあったものが0.01KB/sくらいになった
全体的に明確な改善が見られる
300万件
トップページの表示は一瞬
キーワード検索も一瞬
データ比較とデータ推移は1〜2秒で表示される
ユーザごとのページは3秒くらいかかる
400万件
（300万件のときと大差なし）
600万件
（300万件のときと大差なし）
1200万件
（300万件のときと大差なし）
1900万件
トップページの表示は一瞬
キーワード検索とデータ比較とデータ推移は1〜2秒で表示される
ユーザごとのページは5秒くらいかかる
2700万件
トップページの表示は一瞬
キーワード検索とデータ比較とデータ推移は1〜2秒で表示される
ユーザごとのページも1〜2秒で表示される
何故か早くなっているが、さくら側でハードウェアのメンテナンスなどがあったのかもしれないが不明
■コーディングスタイル
分析SQLのコーディングスタイル - クックパッド開発者ブログ
http://techlife.cookpad.com/entry/2016/11/09/000033
「分析SQLスタイルガイド」をかなり真面目に考えた - Qiita
https://qiita.com/kai_data/items/6c119c43ad3626226dfc
あくまでも参考程度に
■セキュリティ
【SQLインジェクション対策】徳丸先生に怒られない、動的SQLの安全な組み立て方
https://www.slideshare.net/kwatch/sql-53624630
プレースホルダよりもSQLテンプレートの方が安全？要勉強
■トラブル
誰も教えてくれなかったMySQLの障害解析方法 - Qiita
https://qiita.com/muran001/items/14f19959d4723ffc29cc
MySQLがおかしい！あなたならどうしますか？ - MySQL Casual Advent Calendar 2011 - As a Futurist...
https://blog.riywo.com/2011/12/02/002204/
MySQLのレプリケーション遅延をローカルで再現させる手順 その1 - 株式会社CFlatの明後日スタイルのブログ
http://cflat-inc.hatenablog.com/entry/20130812/1376265269
RDBのトラブルの現場を追え！ / rdb-Troubleshooting - Speaker Deck
https://speakerdeck.com/soudai/rdb-troubleshooting
RDBのトラブルの現場を追え！ ~ 様々な現場を見る ~ / rdb-troubleshooting2 - Speaker Deck
https://speakerdeck.com/soudai/rdb-troubleshooting2
MySQLで3億レコード物理削除した話 - Qiita
https://qiita.com/ningenMe/items/57bd66359d89b691ba0d
■MySQL8へのバージョンアップ
MySQL 8.0 への移行が完了しました 〜さようなら全ての MySQL 5.7〜 - Cybozu Inside Out | サイボウズエンジニアのブログ
https://blog.cybozu.io/entry/2021/05/24/175000
■MariaDBについて
MariaDBはMySQLの作者によって、MySQLのソースコードをベースに作成されたもの
詳細はサーバメモの Web.txt を参照
■Adminer
※phpMyAdminよりも軽量なデータベース管理ツール
1つのPHPファイルを設置するだけで動作する
Adminer - Database management in a single PHP file
https://www.adminer.org/
Adminerを設置する - Qiita
https://qiita.com/nissuk/items/2b1aee7f81f351c7ab05
■A5:SQL Mk-2
※Windows用のデータベース管理ツール
テーブル定義をもとにER図の雛形を作成することもできる
A5:SQL Mk-2 - フリーの汎用SQL開発ツール/ER図ツール .. 松原正和
https://a5m2.mmatsubara.com/
■接続先の追加
初回起動時に接続先を確認されるので設定できる
その後はメニューの「データベース → データベースの追加と削除」から追加できる
■ER図を書く
以下でツールの基本操作が紹介されている
ERエディタ - ER図ツール
https://a5m2.mmatsubara.com/help/ER/
ER図を作成したいデータベースを選択し、メニューから「データベース → ER図 → ER図のりーバス生成」を実行
テーブル一覧が表示されるので、すべてのテーブルを選択して「リバースER生成」ボタンを押す
しばらく待つとER図が表示される。ただしテーブル間の関連は反映されていない
以下を参考にする限り、関連は手動での設定が必要みたい
描ける！ER図
https://www.slideshare.net/nekoruri/er-14127685
メニューの「ER図 → 表示レベル」は「属性」がデフォルトとなっているが、
これを例えば「属性とデータ型」にすると、列名も一緒に表示される
この設定は「属性（論理名と物理名）とデータ型（位置揃え）」にしておくのが判りやすいかもしれない
メニューの「ER図 → ER図プロパティ」から、プロジェクト名や著作者などを登録できる
何に使われているかは不明だが、単純にドキュメントの情報として持っているだけかもしれない
「RDBMS種類」は、適切なものを選択しておくと後々混乱しなくて良さそう
メニューの「設定 → オプション → ER図」から、ER図に関するオプションを調整できる
基本的にデフォルトのままで問題なさそうだが、「オブジェクトをスナップする」の間隔など、必要に応じて調整する
メニューの「設定 → オブジェクトの追加 → リレーションシップ」から、テーブル間の関連を設定できる
「親エンティティをクリック（主キーがある方） → 子エンティティをクリック（xxx_id がある方）」
とクリックすると線が引かれる
この線をダブルクリックするとリレーションの詳細を設定できる
基本的に
・「カーディナリティ」は「1」対「0以上」
・「依存」にチェックしない
・「リレーション」は「代理キー」と設定したい「xxx_id」の列
として「適用」をクリックする
…でいいはずだが、先の引かれる位置がおかしい？また調査する
メニューの「設定 → オブジェクトの追加 → エンティティ」から、テーブルを追加できる
追加されたエンティティをダブルクリックすると、列の追加ができる
作成済みのエンティティをダブルクリックすると、列の追加編集削除などができる
メニューの「ER図 → DDLを作成する」から、ER図をもとにSQLを作成できる
必要に応じて設定を変更してから「DDL生成」ボタンを押す
メニューの「ファイル → 名前をつけて保存」から、拡張子「a5er」のファイルとしてER図を保存できる
次回このファイルを開けば、ER図が再現される
その他、以下などを参考にすると良さそう
ER図とは？書き方や用語・テクニックを徹底解説
https://products.sint.co.jp/ober/blog/create-er-diagram
「ER図を見やすくするテクニック」部分などを参考にする
「親エンティティは左上から書く」は、常に意識しておくと良さそう
若手プログラマー必読！５分で理解できるER図の書き方５ステップ
https://it-koala.com/entity-relationship-diagram-1897
■HeidiSQL
※Windows用のデータベース管理ツール
Navicatは有料だし、MySQL Workbenchは日本語化に難があるし
…だが、HeidiSQLなら問題なく使えそう
「HeidiSQL」オープンソースのデータベース管理ソフト - 窓の杜
https://forest.watch.impress.co.jp/library/software/heidisql/
以下、SSHトンネルで接続するためのメモ
「plinkの取得」「puttygenの取得」「pemをppkに変換」
という作業が必要なので、それぞれ記載する
HeidiSQL SSH tunnel経由でMySQL(MariaDB)に接続する - Symfoware
https://symfoware.blog.fc2.com/blog-entry-1942.html
■plinkの取得
以下から plink.exe の64bit版をダウンロード
Download PuTTY: latest release (0.74)
https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html
手動で以下に配置した
C:\Program Files\plink\plink.exe
■puttygenの取得
以下から puttygen.exe の64bit版をダウンロード
Download PuTTY: latest release (0.74)
https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html
手動で以下に配置した
C:\Program Files\puttygen\puttygen.exe
■pemをppkに変換
PuTTYgenを使って変換できる
「Load」ボタンからpemファイルを読み込み、その後「SavePrivateKey」ボタンからppkファイルを出力する
（「SavePublicKey」では無いので注意）
pem ⇒ ppk 変換 - Qiita
https://qiita.com/naka46/items/3297242f3386b5f9e3dc
id_rsa⇒pem⇔ppk 相互変換 | Punio Kid Works
http://www.puniokid.com/tips/linux/69/
■HeidiSQL上での設定
※plink.exeなどを設定した後の汎用的な手順
※ユーザ名などは例
「設定」タブで以下を設定
ネットワーク種別: MySQL(SSH tunnel)
ホスト名: localhost
ユーザー: webmaster
パスワード: abcd1234
ポート: 3306
データベース: test
「SSHトンネル」タブで以下を設定
SSHホスト＋ポート: 203.0.113.1 + 10022
ユーザー名: ssh-user
パスワード: （空欄）
秘密鍵ファイル: Example.ppk
■トラブル（未解決）
新規に接続しようとしたとき、以下のエラーで接続できないことがあった
PLINKは、予期せず終了しました。コマンドライン：
C:\Program Files\plink\plink.exe -ssh ec2-user@203.0.113.1 -P 10022 -i "C:\Users\refirio\SSH\test\test.ppk" -N -L 3307:production.xxxxx.ap-northeast-1.rds.amazonaws.com:3306
コマンドプロンプトから直接実行してみると、以下のエラーが表示された
C:\Users\refirio>C:\"Program Files"\plink\plink.exe -ssh ec2-user@203.0.113.1 -P 10022 -i "C:\Users\refirio\SSH\test\test.ppk" -N -L 3307:production.xxxxx.ap-northeast-1.rds.amazonaws.com:3306
Using username "ec2-user".
FATAL ERROR: No supported authentication methods available (server sent: publickey,gssapi-keyex,gssapi-with-mic)
エラーメッセージをもとに調べてみると、authorized_keys のパーミッション問題の可能性はありそう
問題の解決方法 SSHの鍵認証接続を題材に - Qiita
https://qiita.com/szly/items/ef057727e8b6d227ed29
…だが、調べてみるとパーミッションに問題は無かった
またそもそも、このパーミッションが不正な場合はSSH接続自体できないはずだが、Poderosaで接続はできた
試しにHeidiSQLではなくMySQL Workbenchなら、すんなり接続できた
原因不明だが、HeidiSQLの問題で特定環境での接続に対応できないなどあるのかもしれない
■メモ
MySQLに初めてINSERTするとアクセスが発生するファイルは何かという質問をどう調べるのか - oranie's blog
https://oranie.hatenablog.com/entry/2021/08/26/233701
straceコマンドの使い方 - Qiita
https://qiita.com/hana_shin/items/a03150552e2f70994248