・設計 ・コーディングスタイル ・データベースの文字コード ・少数を管理する ・真偽値を管理する ・日時を扱う ・暗黙の型変換 ・トランザクション ・ロックによる排他制御 ・デッドロックの具体例 ・一斉アクセスを再現してテストする ・監視 ・チューニング ・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 ER図とは？書き方やテクニックをわかりやすく解説 https://products.sint.co.jp/ober/blog/create-er-diagram CRUD図 (クラッド図)とは｜「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典 https://wa3.i-3-i.info/word13565.html 【入門】データベース設計まとめ - Qiita https://qiita.com/KNR109/items/5d4a1954f3e8fd8eaae7 Postgres と MySQL における id, created_at, updated_at に関するベストプラクティス https://zenn.dev/mpyw/articles/rdb-ids-and-timestamps-best-practices 分析SQLのコーディングスタイル - クックパッド開発者ブログ http://techlife.cookpad.com/entry/2016/11/09/000033 「分析SQLスタイルガイド」をかなり真面目に考えた - Qiita https://qiita.com/kai_data/items/6c119c43ad3626226dfc ワンランク上のSQLを書くためのポイント3つ - Qiita https://qiita.com/ken1041/items/49417edd50536a397318 あくまでも参考程度に utf8_unicode_ciではなく、utf8_general_ciにしておくのが無難 なお今はutf8だけでなく、utf8mb4についても検討する 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に変更する場合の調査メモ ・基本的にはコマンドを叩くことで変更できそう ・767byte問題のために、データ型の変更が発生する可能性がありそう ・特定テーブルの文字コードのみ変更して事足りるなら、それが影響範囲が少なくていいか ・特定テーブルの特定列の文字コードのみ変更できるかは要調査。以下のページによると可能そうだが、照合順を変更するだけかどうか MySQLのテーブル作成後に、文字コードをutf8mb4に変更する - karakaram-blog https://www.karakaram.com/changing-the-character-set-to-utf8mb4-after-creating-mysql-table/ MySQLの文字コードをutf8mb4に変更 - Qiita https://qiita.com/decoch/items/bfa125ae45c16811536a MySQLのencodingをutf8からutf8mb4に変更して寿司ビール問題に対応する｜TechRacho by BPS株式会社 https://techracho.bpsinc.jp/hachi8833/2020_11_26/25044 MySQL utf8をutf8mb4に変更する - PukiWiki https://yassu.jp/pukiwiki/index.php?MySQL+utf8%A4%F2utf8mb4%A4%CB%CA%D1%B9%B9%A4%B9%A4%EB MySQL の文字エンコーディングを utf8mb4 にあとから変更する - ハトネコエ Web がくしゅうちょう https://nekonenene.hatenablog.com/entry/2016/08/02/125424 0.00〜100.00 までの値を管理する場合、合計5桁で小数部分が2桁なので、以下のように指定する -100.00〜100.00 までの値を管理する場合も、同様の定義で格納できる（マイナスがついていても格納できる） ※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 の型は無いので、以下のようにして対応する CURRENT_DATE() で日付を、CURRENT_TIME() で時間を、NOW() で日時をそれぞれ取得できる 時間の計算を行う際、CURRENT_DATE() を使用すると0時からの計算になるので注意 MySQL [recole]> SELECT CURRENT_DATE(); +----------------+ | CURRENT_DATE() | +----------------+ | 2022-11-08 | +----------------+ 1 row in set (0.00 sec) MySQL [recole]> SELECT CURRENT_TIME(); +----------------+ | CURRENT_TIME() | +----------------+ | 12:59:14 | +----------------+ 1 row in set (0.00 sec) MySQL [recole]> SELECT NOW(); +---------------------+ | NOW() | +---------------------+ | 2022-11-08 12:59:14 | +---------------------+ 1 row in set (0.00 sec) MySQL [recole]> SELECT CURRENT_DATE(), DATE_ADD(CURRENT_DATE(), INTERVAL 13 HOUR); +----------------+--------------------------------------------+ | CURRENT_DATE() | DATE_ADD(CURRENT_DATE(), INTERVAL 13 HOUR) | +----------------+--------------------------------------------+ | 2022-11-08 | 2022-11-08 13:00:00 | +----------------+--------------------------------------------+ 1 row in set (0.00 sec) MySQL [recole]> SELECT NOW(), DATE_ADD(NOW(), INTERVAL 13 HOUR); +---------------------+-----------------------------------+ | NOW() | DATE_ADD(NOW(), INTERVAL 13 HOUR) | +---------------------+-----------------------------------+ | 2022-11-08 12:59:14 | 2022-11-09 01:59:14 | +---------------------+-----------------------------------+ 1 row in set (0.00 sec) code が 528 と 528A のデータがあった場合、以下のように検索すると両方ヒットしてしまうので注意 以下のように型を明示的にして検索する必要がある PHP+PDOの場合、以下のようにして型を明示できる MySQLが文字列と数値の比較の際に自動的に変換してしまう件 | 創作メモ帳 https://sousaku-memo.net/php-system/123 SQLの暗黙の型変換はワナがいっぱい | 徳丸浩の日記 https://blog.tokumaru.org/2009/09/implicit-type-conversion-of-SQL-is-trap-full.html ■トランザクション ・BEGIN または START TRANSACTION でトランザクションを開始する（意味はどちらも同じ） ・COMMIT でトランザクションを終了し、変更は永続化される ・ROLLBACK でトランザクションを終了し、変更は取り消される ■トランザクション分離レベル MySQLのInnoDBのトランザクション分離レベルは、デフォルトで REPEATABLE READ となっている REPEATABLE READ はトランザクション開始後にテーブルの値を変更しても、SELECT で参照できるのは変更前の値 これにより、例えば予約システムなどで 1. トランザクションを開始する 2. 現在の予約数を取得する 3. 取得した予約数が一定件数以下なら予約データを登録する。一定件数以上ならエラーにする という処理を書いたとき、2で取得できるデータは1時点のもの つまり3に到達した時点で予約数はさらに増えている可能性がある つまり「定員が100人のイベントに105人予約されてしまった」という現象が起こりうる トランザクション分離レベルを「READ COMMITTED」にしておけば、操作もとでコミットされた時点で他端末からも変更後の値を参照できるようになり、Oracle、PostgreSQL、SQL Server などではこれがデフォルト設定となっている そちらの方が直感的なので、変更しておくと余計なトラブルを防ぐことができる（常に変更して良いものかは要検討） 以下のSQLを発行することで、その接続だけトランザクション分離レベルを変更できる 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で確認できる 第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 以下、トランザクション分離レベルの挙動を検証したときのメモ 2つの端末からアクセスするので、それぞれ「mysql1>」「mysql2>」と表記する ■REPEATABLE READ のまま（デフォルト） mysql1> BEGIN; mysql2> BEGIN; mysql1> SELECT * FROM test; mysql1> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2; mysql1> SELECT * FROM test; … 更新を確認できる mysql2> SELECT * FROM test; … 他端末からは更新を確認できない mysql1> COMMIT; mysql2> SELECT * FROM test; … 他端末からは更新を確認できない mysql2> COMMIT; mysql2> SELECT * FROM test; … 他端末からも更新を確認できる ■READ COMMITTED に変更 mysql1> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; mysql1> BEGIN; mysql2> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; mysql2> BEGIN; mysql1> SELECT * FROM test; mysql1> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2; mysql1> SELECT * FROM test; … 更新を確認できる mysql2> SELECT * FROM test; … 他端末からは更新を確認できない mysql1> COMMIT; mysql2> SELECT * FROM test; … 操作もとでコミットされた時点で、他端末からも更新を確認できる ■ロールバック 【忘備録】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のプロセス確認と強制終了（デッドロック解除）の方法は、サーバメモの Command.txt の「MySQLでプロセスを確認＆強制終了」を参照 ロックについては、このファイル内の「トランザクション」についても参照（トランザクション分離レベルについては注意が必要） 以下の記事は以前に試したときのメモ FOR UPDATE による行ロックの具体例がある （この内容以外にも、トランザクション分離レベルについては注意が必要。またボタンを連打されなように非活性化するなどの処理も入れておくべき） Webアプリケーションへの同時アクセス対策メモ | refirio.org http://refirio.org/view/367 CREATE TABLE や TRUNCATE TABLE を実行した場合は、暗黙的にコミットされてロックが外れる（テンポラリテーブルの場合は外れない） …など例外的な挙動もあるようなので注意する。要勉強 デッドロックが発生した場合、「SHOW ENGINE INNODB STATUS;」で詳細を確認できるみたい なぜあなたは SHOW ENGINE INNODB STATUS を読まないのか - そーだいなるらくがき帳 https://soudai.hatenablog.com/entry/2017/12/20/030013 以下、ロックの挙動を検証したときのメモ 主に以下のページを参考にしている DBのロックについてあまり意識したことがない人に向けた実は覚えておきたいロックについての知識 - CARTA TECH BLOG https://techblog.cartaholdings.co.jp/entry/2022/12/14/113000 デッドロックについては、後述の「デッドロックの具体例」も参照 ■前提 以下のテーブルを作成し、データを登録しているものとする （トランザクション分離レベルは、MySQLデフォルト設定の REPEATABLE READ としている） CREATE TABLE test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, text VARCHAR(80), PRIMARY KEY(id) ) ENGINE=InnoDB; INSERT INTO test VALUES(1, 'TEST1'); INSERT INTO test VALUES(2, 'TEST2'); INSERT INTO test VALUES(3, 'TEST3'); INSERT INTO test VALUES(4, 'TEST4'); INSERT INTO test VALUES(5, 'TEST5'); 以下のとおり、データが登録されていることを確認できる > SELECT * FROM test; +----+-------+ | id | text | +----+-------+ | 1 | TEST1 | | 2 | TEST2 | | 3 | TEST3 | | 4 | TEST4 | | 5 | TEST5 | +----+-------+ 以下で値の編集を確認できる 複数端末から実行しても編集できる > SELECT * FROM test; > UPDATE test SET text = 'TEST2 UPDATED!!' WHERE id = 2; > SELECT * FROM test; この前提で、トランザクション・共有ロック・排他ロックについて確認する 2つの端末からアクセスするので、それぞれ「mysql1>」「mysql2>」と表記する ■トランザクションの確認 mysql1> BEGIN; mysql2> BEGIN; mysql1> SELECT * FROM test; mysql1> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2; mysql1> SELECT * FROM test; … 更新を確認できる mysql2> SELECT * FROM test; … 他端末からは更新を確認できない mysql1> COMMIT; mysql2> SELECT * FROM test; … 他端末からは更新を確認できない mysql2> COMMIT; mysql2> SELECT * FROM test; … 他端末からも更新を確認できる ■共有ロックの確認（LOCK IN SHARE MODE） トランザクションが終了されるまで共有ロックを継続して取得し続ける 別のトランザクションからの読み取りを許可するが、書き込みは許可しない状態になる mysql1> BEGIN; mysql2> BEGIN; mysql1> SELECT * FROM test WHERE id = 2 LOCK IN SHARE MODE; mysql1> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2; mysql2> SELECT * FROM test WHERE id = 2; … 他端末からは更新を確認できない mysql2> SELECT * FROM test WHERE id = 2 LOCK IN SHARE MODE; … 他端末からは共有ロックがブロックされる mysql2> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2; … 他端末からは更新もブロックされる mysql1> COMMIT; mysql2> COMMIT; mysql2> SELECT * FROM test WHERE id = 2; … コミットされたので、他端末からも更新を確認できる ■排他ロックの確認（FOR UPDATE） トランザクションが終了されるまで排他ロックを継続して取得し続ける 別のトランザクションからの読み取り＆書き込みの両方を許可しない状態になる ※ただしInnoDBでトランザクション分離レベルが REPEATABLE READ の場合、操作もとでコミットされると読み取りできる REPEATABLE READ はトランザクション開始後にテーブルの値を変更しても、SELECT で参照できるのは変更前の値なので、実質変更途中のデータを取得されることが無いためだと思われる mysql1> BEGIN; mysql2> BEGIN; mysql1> SELECT * FROM test WHERE id = 2 FOR UPDATE; mysql1> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2; mysql2> SELECT * FROM test WHERE id = 2; … 他端末からは更新を確認できない（参照自体はできる） mysql2> SELECT * FROM test WHERE id = 2 FOR UPDATE; … 他端末からは排他ロックがブロックされる mysql2> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2; … 他端末からは更新できない mysql1> COMMIT; mysql2> COMMIT; mysql2> UPDATE test SET text = 'TEST2 UPDATED!!' WHERE id = 2; … コミットされたので、他端末からも更新できる ■排他ロックの確認（READ COMMITTED + FOR UPDATE） トランザクション分離レベルを READ COMMITTED にし、更新の前には FOR UPDATE で排他ロックをかける方法 予約処理の定員チェックなど、厳密性を求められる場面では基本的にこの処理が良さそう mysql1> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; mysql1> BEGIN; mysql2> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; mysql2> BEGIN; mysql1> SELECT * FROM test WHERE id = 2 FOR UPDATE; mysql1> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2; mysql2> SELECT * FROM test WHERE id = 2; … 他端末からは更新を確認できない（参照自体はできる） mysql2> SELECT * FROM test WHERE id = 2 FOR UPDATE; … 他端末からはロックできない mysql2> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2; … 他端末からは更新できない mysql1> COMMIT; mysql2> SELECT * FROM test WHERE id = 2; … 操作もとでコミットされた時点で、他端末からも更新を確認できる mysql2> UPDATE test SET text = 'TEST2 UPDATED!!' WHERE id = 2; … 操作もとでコミットされた時点で、他端末からも更新できる ■引き続きの勉強中メモ ・トランザクション内でロックしたら、コミットしてから次の処理が進む ロックは、定員オーバーチェックの最初だけ行うといい。同じ処理の中にfor updateが2回あると問題。1回だとデッドロックは理論的に起こらない。はず 予約者をINSERTしていくような行が増えるテーブルはロックできない ・ダミーテーブルをロック、という手法は無いか 「予約者をINSERTしていくような行が増えるテーブルはロックできない」があるので、適当なテーブルで行うべきでは無いか データが1行で「ロックしているか否か」の値を持つだけのテーブルをロックするのなら有効か ・「ダミーテーブルをロック」が有効なら、ダミーファイルを配置しておいてファイルロックをかけ、それによって排他制御を行うのは有効か サーバが複数台構成の場合は使えないので、かえってややこしくなるだけか ・ミドルウェアの設定で「デッドロックを検知した瞬間にエラーで落ちる」とかできないか 以下などでは「スレッドを特定して強制終了」となっているので、そのような設定は無さそうだが MySQLでロックを特定し、強制終了する - Qiita https://qiita.com/RyutaKojima/items/a76f4cd4c94d1989a4a5 【障害対応】MySQLでデットロックが発生した時の対応方法 - 気ままに https://unot13.hatenablog.com/entry/2018/08/07/174951 MySQLでロックを特定、強制終了してみた｜SHIFT Group 技術ブログ｜note https://note.com/shift_tech/n/n808984951f92 以下は参考になりそうなサイト 嵐のコンサートがあるとダブルブッキングしてしまうホテル予約システムを作ってみた | 徳丸浩の日記 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 MySQL(InnoDB)の行ロック - フリエン生活 https://free-engineer.life/mysql-innodb-record-locks/ MySQL １つのテーブルでデッドロックさせる│システムガーディアン株式会社 https://sys-guard.com/post-15568/ ■共有ロックでの例 DBのロックについてあまり意識したことがない人に向けた実は覚えておきたいロックについての知識 - CARTA TECH BLOG https://techblog.cartaholdings.co.jp/entry/2022/12/14/113000 mysql1> BEGIN; mysql2> BEGIN; mysql1> SELECT * FROM test WHERE id = 2 LOCK IN SHARE MODE; mysql2> SELECT * FROM test WHERE id = 2 LOCK IN SHARE MODE; mysql1> DELETE FROM test WHERE id = 2; … 処理待ちが発生するが完了はできる Query OK, 1 row affected (7.33 sec) mysql2> DELETE FROM test WHERE id = 2; … デッドロック発生 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction mysql2> DELETE FROM test WHERE id = 2; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql2> DELETE FROM test WHERE id = 2; ^CCtrl-C -- query killed. Continuing normally. ERROR 1317 (70100): Query execution was interrupted 何度か試していると、mysql1の方でデッドロックになることもあった ■排他ロックでの例 MySQL のデッドロックを調査した - エムティーアイ エンジニアリングブログ https://tech.mti.co.jp/entry/2017/12/27/190733 CREATE TABLE test2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED, value INT UNSIGNED, PRIMARY KEY(id, user_id) ) ENGINE=InnoDB; INSERT INTO test2 VALUES(1, 100, 50); INSERT INTO test2 VALUES(2, 100, 100); INSERT INTO test2 VALUES(3, 200, 10); INSERT INTO test2 VALUES(4, 100, 90); INSERT INTO test2 VALUES(5, 300, 70); INSERT INTO test2 VALUES(6, 200, 120); SELECT * FROM test2; +----+---------+-------+ | id | user_id | value | +----+---------+-------+ | 1 | 100 | 50 | | 2 | 100 | 100 | | 3 | 200 | 10 | | 4 | 100 | 90 | | 5 | 300 | 70 | | 6 | 200 | 120 | +----+---------+-------+ ここまで準備 引き続き以下を実行 mysql1> BEGIN; mysql2> BEGIN; mysql1> UPDATE test2 SET value = (value + 10) WHERE user_id = 500; … 条件に一致するものが無いので更新は発生しないが、トランザクション内で「一致するものが無い」ことを保証するために（他のトランザクションの影響を受けないように）ロックがかけられる mysql2> UPDATE test2 SET value = (value + 10) WHERE user_id = 600; … 処理待ちが発生。この時点でも条件に一致するものが無いのでロックがかけられる mysql1> INSERT INTO test2 (user_id, value) values (500, 50); mysql2> INSERT INTO test2 (user_id, value) values (600, 60); … デッドロックが発生する 以下のように強制終了させることで、改めてINSERTを実行することはできた # mysql -u root -p mysql> SHOW PROCESSLIST; +----+-----------+-----------+------+---------+------+--------+-----------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-----------+-----------+------+---------+------+--------+-----------------------------------------------------+----------+ | 51 | webmaster | localhost | test | Sleep | 196 | | NULL | 0.000 | | 52 | webmaster | localhost | test | Query | 2 | update | INSERT INTO test2 (user_id, value) values (600, 60) | 0.000 | | 53 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | 0.000 | +----+-----------+-----------+------+---------+------+--------+-----------------------------------------------------+----------+ mysql> KILL 51; mysql> KILL 52; 解説ページも参考に、引き続き勉強中 前述の「ロックによる排他制御 > 引き続きの勉強中メモ」も参照 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を徹底解説!! http://nippondanji.blogspot.com/2009/03/mysqlexplain.html MySQL EXPLAINの見方 - Qiita https://qiita.com/Stuffy86/items/809540b73cacde951997 例えば以下のようなSQLがあったとして、 SELECT id, created, name FROM students LIMIT 100; このSQLの直前に「EXPLAIN」を付けると実行計画を表示でき、この情報をもとにボトルネックを探してチューニングすることができる 一例だが、以下のように表示される > EXPLAIN SELECT id, created, name FROM students LIMIT 100; +------+-------------+----------+------+---------------+------+---------+------+-------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+------+---------------+------+---------+------+-------+-------+ | 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 70288 | | +------+-------------+----------+------+---------------+------+---------+------+-------+-------+ > EXPLAIN SELECT id, created, name FROM students WHERE name = '山田太郎' LIMIT 100; +------+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | students | ref | index_name_sei | index_name_sei | 767 | const | 398 | Using index condition | +------+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+ 結果の意味は以下のとおり ひとまずは「select_type」と「type」を確認するっと良さそう ■id 実行順序 select_typeとセットで確認する ■select_type SIMPLE … サブクエリやユニオンが含まれていない、単純なSELECT文 SUBQUERY … サブクエリに指定されているSELECT文 PRIMARY … ユニオンの1つ目のSELECT文 UNION … ユニオンの2つ目以降のSELECT文 UNION_RESULT … ユニオンの無名一時テーブルから結果を取得するSELECT文 ■table 対象のテーブル名 ■type テーブルへのアクセス方法 const … インデックスによる検索。最速 eq_ref … JOINで用いられる、インデックスによる検索 ref … ユニーク（PRIMARY or UNIQUE）ではないインデックスを使って等価検索（WHERE key = value）を行なった検索 range … インデックスを用いた範囲検索 index … フルインデックススキャン。インデックスがはられていること以外はALLと同じなので遅い ALL … フルテーブルスキャン。インデックスがまったく利用されていないので改善が必要。遅い ■possible_keys 検索に使用できるインデックス 「NULL」の場合は参照するインデックスが無い ■key 検索に使用されたインデックス ■key_len 検索に使用されたインデックスの長さ ■ref 検索条件でkeyと比較されている値やカラムの種類 定数が指定されている場合は「const」と表示される ■rows テーブルから取得される行数の見積もり 大まかな見積もりなので正確では無い ■Extra クエリを実行するために、どのような戦略を選択したかを示す 以下は一例 Using where … 頻繁に出力される追加情報。WHERE句に検索条件が指定されており、なおかつインデックスを見ただけではWHERE句の条件を全て適用することができない場合 Using index … クエリがインデックスだけを用いて解決できることを示す Using filesort … filesort（クイックソート）でソートを行っていることを示す Using temporary … JOINの結果をソートしたり、DISTINCTによる重複の排除を行う場合など、クエリの実行にテンポラリテーブルが必要なことを示す。 ■メモ 引き続き確認したい あなたの遅延はどこから？ SQLから！ 〜患部に止まってすぐ効くSQLレビューチェックリスト 年初め特大サービス号〜 - ANDPAD Tech Blog https://tech.andpad.co.jp/entry/2023/01/12/100000 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 SQLを速くするぞ―お手軽パフォーマンス・チューニング https://mickindex.sakura.ne.jp/database/db_optimize.html 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のselectの表示結果を\Gで縦に見やすく表示したい - Qiita https://qiita.com/HorikawaTokiya/items/80196e7e33695fc594ab > SELECT * FROM table_test; +----+---------------------------+ | id | text | +----+---------------------------+ | 1 | テストメッセージ1 | | 2 | テストメッセージ2 | | 3 | テストメッセージ3 | +----+---------------------------+ 3 rows in set (0.00 sec) > SELECT * FROM table_test \G *************************** 1. row *************************** id: 1 text: テストメッセージ1 *************************** 2. row *************************** id: 2 text: テストメッセージ2 *************************** 3. row *************************** id: 3 text: テストメッセージ3 3 rows in set (0.00 sec) ■ファイル出力 MySQLのSELECT結果をファイル出力する方法とよくあるエラー | pixelbeat sandbox http://pixelbeat.jp/mysql-select-into-outfile/ ■その他 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で以下のように指定することで恒久的に対処できた 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する codeが「test」のデータの代理キーをもとに挿入したい…という場合、あくまでも一例だが以下のような方法は使えるかもしれない （いったん仮の値で登録し、後から正しい代理キーに変更する） ■TRUNCATEで削除 一例だが以下のようにすると、データをすべて削除できる DELETEと違い、オートインクリメントもリセットされる 全データの削除(TRUNCATE文) - データの追加と削除 - MySQLの使い方 https://www.dbonline.jp/mysql/insert/index12.html ■一定期間経過したデータを削除 一例だが以下のようにすると、一定期間経過したデータをそれぞれ削除できる 以下のページで、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/ データベース作成 データベース削除 データベース仕様確認 テーブル作成 テーブル名変更 MySQLの「ALTER TABLE RENAME」と「RENAME TABLE」 - なからなLife https://atsuizo.hatenadiary.jp/entry/2016/06/16/100000 テーブル削除 テーブル仕様確認 登録 表示 更新 削除 複製 オートインクリメントをリセット 日付をフォーマットして表示 日付ごとにランキング 連結して表示 データ件数を一括確認 MySQLで全テーブルのレコード数を一覧表示する - Qiita https://qiita.com/isobecky74/items/6efff247484f8e4fccae インデックスを確認 インデックスを一括確認 MySQLでDB内の全てのインデックスの一覧をCSV出力する｜エンジニア初心者の備忘録 http://rigil.iku4.com/mysql_command/index カテゴリごとにAUTO_INCREMENTな値を登録するメモ ■InnoDBで対応する場合 「最大値+1」で連番を作成する（AUTO_INCREMENTを使わない） | PHPプログラミングの教科書 [php1st.com] https://php1st.com/806 MySQLメモ： INSERT時に最大値＋1を使いたいけど、auto_incrementは使いたくない - Qiita https://qiita.com/EmikoKishi/items/7f05ef6289a85b525c55 CREATE TABLE test ( category VARCHAR(5), id INT, text VARCHAR(10), PRIMARY KEY (category, id) ) ENGINE=InnoDB; INSERT INTO test SELECT 'A', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'A'; INSERT INTO test SELECT 'A', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'A'; INSERT INTO test SELECT 'B', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'B'; INSERT INTO test SELECT 'C', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'C'; INSERT INTO test SELECT 'B', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'B'; INSERT INTO test SELECT 'B', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'B'; SELECT * FROM test; +----------+----+------+ | category | id | text | +----------+----+------+ | A | 1 | TEST | | A | 2 | TEST | | B | 1 | TEST | | B | 2 | TEST | | B | 3 | TEST | | C | 1 | TEST | +----------+----+------+ ただし MAX(id) で値を取得した直後にデータが挿入された場合に正しく連番が作られない …のような問題が無いかどうかは要検証 ロック、トランザクション、トランザクション分離レベルなどを気にしながら処理する必要があるかもしれない なおデータの挿入は、以下のように記述してもいい INSERT INTO test (category, id, text) SELECT 'A', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'A'; 以下だとうまく行かなかった（0から開始されてしまう） INSERT INTO test (category, id, text) SELECT 'A', MAX(id) + 1, 'TEST' FROM test WHERE category = 'A'; ■MyISAMで対応する場合 [MySQL]親ごとに枝番をAUTO_INCREMENT @餅。 http://doshiroutonike.com/web/other-web/3740 以下のようにすると、categoryごとにidの連番を作ることができる ただしInnoDBではなくMyISAMにする必要があり、トランザクションが使えない問題があるので注意 CREATE TABLE test ( category VARCHAR(5), id INT AUTO_INCREMENT, text VARCHAR(10), PRIMARY KEY (category, id) ) ENGINE=MyISAM; INSERT INTO test VALUES('A', NULL, 'TEST'); INSERT INTO test VALUES('A', NULL, 'TEST'); INSERT INTO test VALUES('B', NULL, 'TEST'); INSERT INTO test VALUES('C', NULL, 'TEST'); INSERT INTO test VALUES('B', NULL, 'TEST'); INSERT INTO test VALUES('B', NULL, 'TEST'); SELECT * FROM test; +----------+----+------+ | category | id | text | +----------+----+------+ | A | 1 | TEST | | A | 2 | TEST | | B | 1 | TEST | | C | 1 | TEST | | B | 2 | TEST | | B | 3 | TEST | +----------+----+------+ ■MyISAMで対応する場合: 検証メモ 以下は実際に検証した内容 前提として、MySQL5.5移行はデフォルトでInnoDBとなっている よって「ENGINE=InnoDB」を省略してもInnoDB扱いになる 以下のコードでは、そもそもテーブルを作成できない CREATE TABLE `test1` ( category VARCHAR(5), id INT AUTO_INCREMENT, text VARCHAR(10), PRIMARY KEY (category, id) ) ENGINE=InnoDB; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key 以下のコードなら作成できるが、idが項目ごとの連番にならない CREATE TABLE `test2` ( category VARCHAR(5), id INT AUTO_INCREMENT, text VARCHAR(10), PRIMARY KEY (id, category) ) ENGINE=InnoDB; INSERT INTO test2 VALUES('A', NULL, 'TEST'); INSERT INTO test2 VALUES('A', NULL, 'TEST'); INSERT INTO test2 VALUES('B', NULL, 'TEST'); INSERT INTO test2 VALUES('C', NULL, 'TEST'); INSERT INTO test2 VALUES('B', NULL, 'TEST'); INSERT INTO test2 VALUES('B', NULL, 'TEST'); SELECT * FROM test2; +----------+----+------+ | category | id | text | +----------+----+------+ | A | 1 | TEST | | A | 2 | TEST | | B | 3 | TEST | | C | 4 | TEST | | B | 5 | TEST | | B | 6 | TEST | +----------+----+------+ PRIMARY KEYの指定順が違っているので、やはりidが項目ごとの連番にならない CREATE TABLE `test3` ( category VARCHAR(5), id INT AUTO_INCREMENT, text VARCHAR(10), PRIMARY KEY (id, category) ) ENGINE=MyISAM; INSERT INTO test3 VALUES('A', NULL, 'TEST'); INSERT INTO test3 VALUES('A', NULL, 'TEST'); INSERT INTO test3 VALUES('B', NULL, 'TEST'); INSERT INTO test3 VALUES('C', NULL, 'TEST'); INSERT INTO test3 VALUES('B', NULL, 'TEST'); INSERT INTO test3 VALUES('B', NULL, 'TEST'); SELECT * FROM test3; +----------+----+------+ | category | id | text | +----------+----+------+ | A | 1 | TEST | | A | 2 | TEST | | B | 3 | TEST | | C | 4 | TEST | | B | 5 | TEST | | B | 6 | TEST | +----------+----+------+ 以下ならOK CREATE TABLE `test4` ( category VARCHAR(5), id INT AUTO_INCREMENT, text VARCHAR(10), PRIMARY KEY (category, id) ) ENGINE=MyISAM; INSERT INTO test4 VALUES('A', NULL, 'TEST'); INSERT INTO test4 VALUES('A', NULL, 'TEST'); INSERT INTO test4 VALUES('B', NULL, 'TEST'); INSERT INTO test4 VALUES('C', NULL, 'TEST'); INSERT INTO test4 VALUES('B', NULL, 'TEST'); INSERT INTO test4 VALUES('B', NULL, 'TEST'); SELECT * FROM test4; +----------+----+------+ | category | id | text | +----------+----+------+ | A | 1 | TEST | | A | 2 | TEST | | B | 1 | TEST | | C | 1 | TEST | | B | 2 | TEST | | B | 3 | TEST | +----------+----+------+ ■表示での対応 このファイル内の「データ取得 > グループ」を参照 MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.7.6.1 変数代入の SET 構文 https://dev.mysql.com/doc/refman/8.0/ja/set-variable.html MySQL | SET文でシステム変数を変更(セッション変数, グロバール変数) - わくわくBank https://www.wakuwakubank.com/posts/413-mysql-set/ 以下のようにして、変数に値を代入＆使用できる 変数は接続を終了するまで有効なので、以下のように連続して実行する際にも使用できる 以下のようにして変数の内容を確認できる なおQUIT（EXIT）で接続を終了し、再度変数の内容を確認するとNULLに戻る MySQL の Window 関数を完全に理解する - Qiita https://qiita.com/ngyuki/items/c35dd314815939ffbeb6 以下、MariaDB 10.4.8 で検証 ■簡単な例 GROUP BY で gid ごとの合計を表示した場合 以下はWindow関数を使用した例 「PARTITION BY gid」によって、行の範囲を指定している 行の範囲をしていなければ、全体を対象に処理される 以下はウインドウを明示的に定義して使う例（定義しなければ暗黙的に定義されたことになる） Oracleでは明示的に定義することができないので、暗黙的に定義される方法の方が汎用性が高い OVER 句には ORDER BY も指定できる ORDER BY は Window 関数がパーティションの中で行を処理する順番を指定できる ■フレームの概念 前述の ORDER BY を SUM で使用すると次のような結果になる gid で分割されたパーティションの中を value の昇順に並べ、パーティションの先頭から現在行までが順に集計される この「パーティションの先頭から現在行まで」のような範囲のことを「フレーム」と呼ぶ フレームの範囲も OVER 句で指定できる。次の「ROWS 〜」の部分がフレームの指定 フレームの終了位置はデフォルトで現在行なので、ROWS CURRENT ROW だけ指定すると「現在行〜現在行」となる ■ウインドウ関数の利用例（移動平均） ウインドウ関数の典型的な利用ケースである、移動平均を求めてみる 移動平均を使えば、長期的なデータの変化を可視化することができる 算術平均 ・集合のすべての値を足して、その集合の要素数で割った値 ・その集合が、どのようなものかを表すことができる（例：テストの平均点） 移動平均 ・直近で取得したデータポイント群で平均を計算した値 ・デコボコの多いグラフをなめらかにして、傾向を把握しやすくすることができる 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/ 例えば7dayの移動平均である「72.6000」は、以下のように求められている つまり、現在の行（CURRENT ROW）とその前の4行（4 PRECEDING）の計5行から平均化した値を算出し、傾向を把握しやすくしている (122 + 104 + 34 + 65 + 38) / 5 = 72.6 達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ（ミック）｜翔泳社の本 https://www.shoeisha.co.jp/book/detail/9784798157825 ■CASEでラベルを読み替え 単純CASE式、検索CASE式とも、結果は同じ 単純CASE式の方が簡潔に書くことができるが、できることも限られている ■既存のコード体系を新しい体系に変換して集計 ■異なる条件の集計を1つのSQLで行う 以下のようにすれば、1つのSQLで処理できる ■条件を分岐させたUPDATE 以下の条件で更新をかけるとする 1. 現在の給料が30万円以上の社員は、10%の減給とする 2. 現在の給料が25万円以上28万円未満の社員は、20%の昇給とする UPDATE文を2回実行すると、1回目のSQLによって値が変わるため、意図した更新ができない CASEを使って同時に実行することで、意図した結果を得られるうえに、1回の実行で済むのでパフォーマンスもいい 以下のようにすれば、1つのSQLで処理できる ■テーブル同士のマッチング CASE式の大きな利点は、式を評価できること つまりCASE式の中でBETWEEN、LIKE、<、>といった便利な述語群を利用できる 中でもINとEXISTSはサブクエリを式に取れるため、非常に強力な表現力を持つ 以下のように処理できる 結果は同じだが、パフォーマンスはEXISTS方式の方がいい ■CASE式の中で集約関数を使う 学生番号と、その学生の所属クラブを管理するテーブルがあるとする 学生は複数のクラブに所属している（100, 200）場合もあれば、1つのクラブにしか所属していない場合もある 複数のクラブに所属している学生については、主なクラブを表すフラグ列に「Y」が入り、それ以外には「N」が入る 1つのクラブに専念している学生の場合は「N」が入る このテーブルから、以下の条件でデータを取得する 1. 1つだけのクラブに所属している学生については、そのクラブIDを取得する 2. 複数のクラブに所属している学生については、主なクラブのIDを取得する 以下のようにすれば、1つのSQLで処理できる ■自己結合で検索 同じテーブルを結合して、高度な検索を行うことができる MySQLでJSONカラムを利用する（備忘録) https://www.usagi1975.com/202101220843/ CREATE TABLE json_test( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID', text JSON NOT NULL COMMENT 'JSONデータ', PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'JSONデータ操作テスト'; INSERT INTO json_test VALUES(NULL, '{"number": 1,"code": "AAA","title": "テストメッセージ1","completed": true}'); INSERT INTO json_test VALUES(NULL, '{"number": 2,"code": "BBB","title": "テストメッセージ2","completed": false}'); SELECT * FROM json_test; このとき、以下のように JSON_EXTRACT を使用するとJSONの各値を参照できる 検索条件としても使用できる SELECT id, text, JSON_EXTRACT(text, '$.number') AS number, JSON_EXTRACT(text, '$.code') AS code, JSON_EXTRACT(text, '$.title') AS title, JSON_EXTRACT(text, '$.completed') AS completed FROM json_test ; ■「->」で指定する 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/ 「->」は JSON_EXTRACT のエイリアスとなっているらしいが、XAMPP環境（10.4.13-MariaDB）で試してもエラーになった バージョンに依存するのかも？要調査 SELECT id, text, text->"$.number" AS number, text->"$.code" AS code, text->"$.title" AS title, text->"$.completed" AS completed FROM json_test ; 別名で同じ仕様のテーブルを作成し、 そこに「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 ユーザの作成例 testデータベースに対しての全権限を与える例 環境によっては「ALL PRIVILEGES」で一括指定するとエラーになるので、必要なものを個別に指定する ※未検証 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 制約）などの挙動を試してみる RESTRICT NO ACTION CASCADE（MySQLにおいては、「NO ACTION」と「RESTRICT」は同じ挙動） 上記ではテーブル作成時に制約を追加している 後から追加する場合、以下のようにALTER TABLEで追加する 制約名は必須では無いようだが、削除のときに使用するので付けておくのが無難か 以下の制約名はLaravelが自動作成した制約名。制約名はランダムな文字列を付けることが多い？ 以下のようにすれば削除できる データの移植時、外部キー制約があるとSQLを丸ごと登録（インポート）・削除できないことがある SQLファイルの最初に以下を追加し、 最後に以下を追加すれば、一時的に外部キー制約を無視してインポートできる 以下は削除コードの一例 複数サーバでデータの同期を行なうことで、負荷分散や障害対策ができる レプリケーションの設定については、サーバメモの「Etcetera.txt」を参照 また、AWSで設定したときのメモは、サーバメモの「AWS.txt」を参照 MySQLレプリケーション再入門 - Qiita https://qiita.com/snoguchi/items/43247a433973d09f7c08 MySQL/MariaDBレプリケーション - とほほのWWW入門 https://www.tohoho-web.com/ex/mysql-replication.html MySQL のレプリケーションから10年間逃げてきた我々が学んだこと8選 - Cybozu Inside Out | サイボウズエンジニアのブログ https://blog.cybozu.io/entry/2020/10/26/173000 ログの肥大化に注意 | logw-ログウ個人的な記録用- https://www.logw.jp/website/site_make/2719.html MySQLのバイナリログが原因でディスクが枯渇した場合の対処方法 - Qiita https://qiita.com/Brutus/items/5244685a2b5e6b7d2a54 ■テーブル 以下のテーブルに、TwitterのAPIから取得したデータを記録し続けていく ツイートテーブルとユーザテーブルを連結して一覧表示 キーワード検索やフォロワー数ソートなどを実装して、速度や負荷の様子を見る。スロークエリの記録も試したい ■環境 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つ目だけで良かったかもしれないが要検証） トップページの表示が一瞬になった キーワード検索も一瞬になった データ比較とデータ推移は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インジェクション対策】徳丸先生に怒られない、動的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 MySQL 8.0 への移行が完了しました 〜さようなら全ての MySQL 5.7〜 - Cybozu Inside Out | サイボウズエンジニアのブログ https://blog.cybozu.io/entry/2021/05/24/175000 MariaDBはMySQLの作者によって、MySQLのソースコードをベースに作成されたもの 詳細はサーバメモの Web.txt を参照 ※phpMyAdminよりも軽量なデータベース管理ツール 1つのPHPファイルを設置するだけで動作する Adminer - Database management in a single PHP file https://www.adminer.org/ Adminerを設置する - Qiita https://qiita.com/nissuk/items/2b1aee7f81f351c7ab05 ※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 ※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 ■トラブル（未解決） 新規に接続しようとしたとき、以下のエラーで接続できないことがあった コマンドプロンプトから直接実行してみると、以下のエラーが表示された エラーメッセージをもとに調べてみると、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