■目次
・設計 ・データベースの文字コード ・少数を管理する ・真偽値を管理する ・暗黙の型変換 ・ロック関連 ・トランザクション ・監視 ・チューニング ・mysqldump ・データ取得 ・データ登録 ・データ削除 ・データ操作 ・ウインドウ関数 ・達人に学ぶSQL徹底指南書 第2版 ・データをJSONで扱う ・データの複製 ・データのマスキング ・権限の設定 ・データベース名の変更 ・外部キー制約 ・外部キー制約を無視して登録（インポート）・削除 ・レプリケーション ・チューニングテスト ・コーディングスタイル ・セキュリティ ・トラブル ・MySQL8へのバージョンアップ ・MariaDBについて ・Adminer ・A5:SQL Mk-2 ・HeidiSQL ・メモ
■設計
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でプロセスを確認＆強制終了」を参照 以下は以前に試したときのメモ Webアプリケーションへの同時アクセス対策メモ | refirio.org http://refirio.org/view/367 以下は参考になりそうなサイト 嵐のコンサートがあるとダブルブッキングしてしまうホテル予約システムを作ってみた | 徳丸浩の日記 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のデフォルトのトランザクション分離レベルは SELECT がスナップショットを参照する - ngyukiの日記 http://ngyuki.hatenablog.com/entry/2013/02/02/202558 MySQL自体の設定を変えるのがリスクになるなら
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
でその接続だけ変更できるみたい 現在のトランザクション分離レベルは、以下のSQLで確認できる
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
■監視
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 代理キー管理のテーブルにINSERTするためのSQLファイルを作成する例
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;
■データ削除
以下のようにすれば、データをすべて削除できる DELETEと違い、オートインクリメントもリセットされる TRUNCATE TABLE users; 全データの削除(TRUNCATE文) - データの追加と削除 - MySQLの使い方 https://www.dbonline.jp/mysql/insert/index12.html
■データ操作
データベース作成
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;
登録
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 インデックスを一括確認
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