refirio.org

Memo

メモ > 技術 > データベース: MySQL

設計
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
データベースのサイズ
サーバメモ「Command.txt」内の「MySQLのデータ容量を確認」を参照。
データ操作
データベース作成。
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;
ASによる別名の設定。
SELECT c.id AS id, c.name_sei AS sei, c.name_mei AS mei FROM customers AS c;
ASの省略による別名の設定。
SELECT c.id id, c.name_sei sei, c.name_mei mei FROM customers c;
オートインクリメントをリセット。
ALTER TABLE products AUTO_INCREMENT = 0;
日時をフォーマットして表示。
SELECT DATE_FORMAT(created, '%Y-%m-%d %H:%i:%s') AS created FROM histories GROUP BY created ORDER BY id DESC LIMIT 100;
日付をフォーマットして表示。
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で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入門】exists句の使い方を理解しよう！データが存在するかをチェック | ポテパンスタイル https://style.potepan.com/articles/19151.html MySQL の EXISTS / NOT EXISTS - MySQL の基礎 - MySQL 入門 https://mysql.sql55.com/sql/mysql-exists.php オイ、そこのSELECT COUNT。余計な数え上げに意味なんかねえ - inSmartBank https://blog.smartbank.co.jp/entry/2024/10/11/mysql-count-query-performance ■ファイル出力 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 医薬品検索でMySQLの全文検索機能を使った話 - KAKEHASHI Tech Blog https://kakehashi-dev.hatenablog.com/entry/2024/09/11/110000
データ登録
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する codeが「test」のデータの代理キーをもとに挿入したい…という場合、あくまでも一例だが以下のような方法は使えるかもしれない。 （いったん仮の値で登録し、後から正しい代理キーに変更する。）
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;
グループごとに連番を作成
カテゴリごとに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 | +----------+----+------+
■表示での対応 このファイル内の「データ取得 > グループ」を参照。
データ削除
■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/
変数を扱う
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/ 以下のようにして、変数に値を代入＆使用できる。
SET @name = 'yamada'; SELECT id FROM users WHERE name = @name;
変数は接続を終了するまで有効なので、以下のように連続して実行する際にも使用できる。
SET @target = 5; UPDATE logs SET message = 'テスト' WHERE id = @target; UPDATE logs SET message = 'テスト' WHERE id = (@target + 1); UPDATE logs SET message = 'テスト' WHERE id = (@target + 2);
以下のようにして変数の内容を確認できる。 なおQUIT（EXIT）で接続を終了し、再度変数の内容を確認するとNULLに戻る。
mysql> SELECT @target; +---------+ | @target | +---------+ | NULL | +---------+ 1 row in set (0.00 sec) mysql> SET @target = 5; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @target; +---------+ | @target | +---------+ | 5 | +---------+ 1 row in set (0.00 sec)
少数を管理する
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 'データ確認',
日時を扱う
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 のデータがあった場合、以下のように検索すると両方ヒットしてしまうので注意。
SELECT * FROM products WHERE code = 528;
以下のように型を明示的にして検索する必要がある。
SELECT * FROM products WHERE code = '528';
PHP+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 の 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
トランザクション
■トランザクション ・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を発行することで、その接続だけトランザクション分離レベルを変更できる。
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 ５分で覚えるトランザクション分離レベル https://zenn.dev/levtech/articles/67bedc33a4a87b 現在のトランザクション分離レベルは、以下の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 以下、トランザクション分離レベルの挙動を検証したときのメモ。 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\G;」とすると結果を読みやすくなる。） なぜあなたは 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/ データベースのロックの基礎からデッドロックまで https://zenn.dev/gibjapan/articles/1d8dfb7520dabc MySQL/Aurora/TiDBロック入門 - 第１回トランザクション分離レベル｜技術ブログ｜北海道札幌市・宮城県仙台市のVR・ゲーム・システム開発 インフィニットループ https://www.infiniteloop.co.jp/tech-blog/2024/05/mysql-lntro-locking-1-transaction-isolation/ MySQL/Aurora/TiDBロック入門 - 第２回ロックモニターの読み方【動画解説付】｜技術ブログ｜北海道札幌市・宮城県仙台市のVR・ゲーム・システム開発 インフィニットループ https://www.infiniteloop.co.jp/tech-blog/2024/06/mysql-intro-locking-2-lock-monitor/ MySQL/Aurora/TiDBロック入門 - 第３回ロック読取りも SELECT は止められない【解説動画付】｜技術ブログ｜北海道札幌市・宮城県仙台市のVR・ゲーム・システム開発 インフィニットループ https://www.infiniteloop.co.jp/tech-blog/2024/06/mysql-intro-locking-3-locking-read/ ■本番環境で実際にログを確認したときの内容 Laravelのログに以下が現れた。
production.ERROR: PropertyController: detail {"message":"SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction (SQL: insert into `user_property_views` (`user_accounts_id`, `properties_property_cd`, `updated_at`, `created_at`) values (?, 52-56350, 2024-08-26 16:56:50, 2024-08-26 16:56:50))"}
本番環境で以下の内容を確認できる。 - - - - - - - - - - - - - - - - - - - - > SHOW ENGINE INNODB STATUS\G; *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2024-08-26 08:27:54 0x1532ed702700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 18 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 573526 srv_idle srv_master_thread log flush and writes: 573462 ---------- SEMAPHORES ---------- ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2024-08-26 08:00:27 0x153300128700 *** (1) TRANSACTION: TRANSACTION 134818679, ACTIVE 193 sec fetching rows mysql tables in use 3, locked 3 LOCK WAIT 7551 lock struct(s), heap size 1122424, 3044103 row lock(s) MariaDB thread id 453694, OS thread handle 23308788729600, query id 17183524 10.0.0.176 webmaster Sending data select count(*) as aggregate from `user_property_views` where `user_accounts_id` is null and `user_identification_id` is null and `properties_property_cd` = '52-56599' and `user_property_views`.`deleted_at` is null for update *** WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 570 page no 16942 n bits 112 index PRIMARY of table `test`.`user_property_views` trx id 134818679 lock_mode X locks rec but not gap waiting Record lock, heap no 39 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 0: len 4; hex 00176363; asc cc;; 1: len 6; hex 000000000000; asc ;; 2: len 7; hex 80000000000000; asc ;; 3: SQL NULL; 4: SQL NULL; 5: len 8; hex 35322d3536333530; asc 52-56350;; 6: SQL NULL; 7: len 4; hex 64f800b1; asc d ;; 8: len 4; hex 64f803b7; asc d ;; 9: len 4; hex 64f803b7; asc d ;; *** CONFLICTING WITH: RECORD LOCKS space id 570 page no 16942 n bits 112 index PRIMARY of table `test`.`user_property_views` trx id 134818634 lock_mode X locks rec but not gap Record lock, heap no 39 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 0: len 4; hex 00176363; asc cc;; 1: len 6; hex 000000000000; asc ;; 2: len 7; hex 80000000000000; asc ;; 3: SQL NULL; 4: SQL NULL; 5: len 8; hex 35322d3536333530; asc 52-56350;; 6: SQL NULL; 7: len 4; hex 64f800b1; asc d ;; 8: len 4; hex 64f803b7; asc d ;; 9: len 4; hex 64f803b7; asc d ;; Record lock, heap no 53 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 0: len 4; hex 00176372; asc cr;; 1: len 6; hex 000000000000; asc ;; 2: len 7; hex 80000000000000; asc ;; 3: SQL NULL; 4: SQL NULL; 5: len 8; hex 35322d3536333530; asc 52-56350;; 6: SQL NULL; 7: len 4; hex 64f803b7; asc d ;; 8: len 4; hex 64f81e1f; asc d ;; 9: len 4; hex 64f81e1f; asc d ;; *** (2) TRANSACTION: TRANSACTION 134818634, ACTIVE 189 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 681 lock struct(s), heap size 90232, 2045 row lock(s), undo log entries 2 MariaDB thread id 453707, OS thread handle 23308626749184, query id 17197392 10.0.1.223 webmaster Update insert into `user_property_views` (`user_accounts_id`, `properties_property_cd`, `updated_at`, `created_at`) values (NULL, '52-56350', '2024-08-26 17:00:23', '2024-08-26 17:00:23') *** WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 570 page no 24944 n bits 16 index properties_property_cd of table `test`.`user_property_views` trx id 134818634 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 35322d3536353939; asc 52-56599;; 1: len 4; hex 00188da5; asc ;; *** CONFLICTING WITH: RECORD LOCKS space id 570 page no 24944 n bits 16 index properties_property_cd of table `test`.`user_property_views` trx id 134818679 lock_mode X Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 35322d3536353939; asc 52-56599;; 1: len 4; hex 00188da5; asc ;; *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 134823210 Purge done for trx's n:o < 134823210 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION (0x15344fd648c0), not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION (0x15344fd63dc0), not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 42708 OS file reads, 920068 OS file writes, 812525 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.33 writes/s, 0.33 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 774097754010 Log flushed up to 774097754010 Pages flushed up to 773558503655 Last checkpoint at 773558503655 0 pending log flushes, 0 pending chkp writes 807780 log i/o's done, 0.33 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 5385486336 Dictionary memory allocated 32056824 Buffer pool size 324480 Free buffers 158700 Database pages 165780 Old database pages 61176 Modified db pages 30299 Percent of dirty pages(LRU & free pages): 9.338 Max dirty pages percent: 90.000 Pending reads 0 Pending writes: LRU 0, flush list 0 Pages made young 113153, not young 2015954 0.00 youngs/s, 0.00 non-youngs/s Pages read 42603, created 355298, written 112134 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 165780, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 read views open inside InnoDB Process ID=0, Main thread ID=0, state: sleeping Number of rows inserted 9312843, updated 305539, deleted 100852, read 5982990655 0.11 inserts/s, 0.00 updates/s, 0.00 deletes/s, 85.55 reads/s Number of system rows inserted 7, updated 1863, deleted 7, read 304252 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.06 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec) ERROR: No query specified - - - - - - - - - - - - - - - - - - - - 以下、ChatGPTによる回答。 このSHOW ENGINE INNODB STATUSコマンドの出力から、現在のデッドロックの状況と、その原因についていくつかのことが分かります。 1. デッドロックの概要 ・LATEST DETECTED DEADLOCKセクションに、最近検出されたデッドロックの詳細が記録されています。 ・具体的には、2つのトランザクション (TRANSACTION 134818679 と TRANSACTION 134818634) が競合して、デッドロックが発生しています。 2. トランザクションの内容 ・トランザクション1 (134818679) は、user_property_viewsテーブルで SELECT クエリを実行しており、これによりレコードに X ロックがかかっています。 これに対して、もう一つのトランザクションが挿入操作を行おうとして、ロックがかかっているために待機している状態です。 ・トランザクション2 (134818634) は、同じテーブルに対して INSERT 操作を行おうとしていますが、同じレコードに対して他のトランザクションがロックを持っているため、ロック待ちが発生しています。 3. デッドロックの解消 ・この場合、InnoDBはトランザクション1をロールバックすることでデッドロックを解消しました (WE ROLL BACK TRANSACTION (1)と記載されています)。 4. 継続的な問題かどうかの判断 ・現在のところ、エラーログにデッドロックが頻発しているか、またはこのSHOW ENGINE INNODB STATUSでデッドロックが再度報告されているかを確認することで、問題が継続的か一時的かを判断できます。 ・LATEST DETECTED DEADLOCKの時間が最近のものであり、頻繁に発生している場合は、デッドロックが継続的な問題となっている可能性があります。 5. 対策 デッドロックを防ぐためには、可能であれば、以下のような改善を検討することが推奨されます： ・クエリの順序を統一して、異なるトランザクションが同じ順序でロックを取得するようにする。 ・大量のレコードに対してロックを取得するクエリを、可能な限り短いトランザクション内で実行する。 ・LOCK IN SHARE MODE や FOR UPDATE といったクエリを見直し、必要最小限のレコードに対してのみロックをかけるようにする。 エラーログとInnoDBステータスを引き続き監視し、問題が継続するかどうかを確認することをお勧めします。 また、デッドロックが頻発する場合は、クエリの最適化やデータベース設計の見直しが必要かもしれません。
デッドロックの具体例
■共有ロックでの例 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;
なお、以下のようにすると「60秒以上実行されているプロセス」を表示することができる。
mysql> SELECT * FROM information_schema.PROCESSLIST WHERE TIME > 60;
解説ページも参考に、引き続き勉強中。 前述の「ロックによる排他制御 > 引き続きの勉強中メモ」も参照。
一斉アクセスを再現してテストする
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 データベースの仕組み(アーキテクチャ)をざっくり理解する https://zenn.dev/revenuehack/articles/dc5a901fbe90e6 MySQLで全文検索機能を使う際のパフォーマンスについて調査してみた - iimon tech blog https://tech.iimon.co.jp/entry/2023/12/01/113000
チューニング（スロークエリ）
スロークエリの記録については、サーバメモの「Command.txt」にある「MySQLでスロークエリログを記録」も参照。 スロークエリログをどう使えばいいのかって疑問、全て解決 https://zenn.dev/levtech/articles/6607ee10b57656
チューニング（インデックス）
インデックスによる高速化を実際に行った時の計測メモ。 ■テーブル 以下のテーブルに、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秒で表示される。 何故か早くなっている。さくら側でハードウェアのメンテナンスなどがあったのかもしれないが不明。
mysqldump
達人に学ぶ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カラムを利用する（備忘録) 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
データのマスキング
開発環境のデータベースでも本番環境相当のデータを使う - クックパッド開発者ブログ https://techlife.cookpad.com/entry/2024/10/01/105503 開発環境のデータをできるだけ本番に近づける - クックパッド開発者ブログ https://techlife.cookpad.com/entry/2014/10/03/110806 開発/Stg環境のための本番DBマスキングと継続的リストアの仕組みを作りました | ランサーズ（Lancers）エンジニアブログ https://engineer.blog.lancers.jp/sre/auto-masking/ 本番DBを開発用にマスキング - 技術情報のメモ書 https://gijutsu.com/2022/07/02/data-masking/ 本番環境のデータをマスクしてステージング環境に同期する - 食べチョク開発者ブログ https://tech.tabechoku.com/entry/2018/11/29/113244 本番環境のデータをマスキング加工して安全にアクセスできる調査環境を作った - HRBrain Blog https://times.hrbrain.co.jp/entry/go-aws-masking-production-data 本番環境のデータをマスクしてステージング環境に自動同期する仕組みを作りました【AWS CDK/SDK】1 ~CDK編~ https://zenn.dev/chycara344/articles/7529bd5539de03 データマスキングとは何か? - 静的データマスキングと動的データマスキングの説明 - AWS https://aws.amazon.com/jp/what-is/data-masking/ やり方は色々考えられるが、マスキング処理自体は「UPDATE分を作成してデータを置換する」という作業を行うことになりそう。 以下、マスキングの検証を行ったもの。
CREATE TABLE customers( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID', name_sei VARCHAR(80) NOT NULL COMMENT '姓', name_mei VARCHAR(80) NOT NULL COMMENT '名', email VARCHAR(255) COMMENT 'メールアドレス', tel VARCHAR(80) COMMENT '電話番号', PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '顧客情報'; INSERT INTO customers VALUES(NULL, '山田', '太郎', 'taro@refirio.net', '090-1234-5678'); INSERT INTO customers VALUES(NULL, '鈴木', '健次郎', 'kenjirou@refirio.org', '090-2345-6789'); INSERT INTO customers VALUES(NULL, '佐藤', '花子', 'hanako@refirio.net', '06-3456-7890');
上記データに対してマスキングを行う。 具体的には ・名前は、最後の文字を残して「〇」にする。例えば「山田」は「〇田」にする。 ・メールアドレスは、ドメインはそのままに、アカウント名を「固定文字 + ID」にする。例えば「taro@refirio.net」は「test1@refirio.net」にする。 ・電話番号は、市外局番はそのままに、それ以降をIDにする。例えば「090-1234-5678」は「090-0000-0001」にする。 という処理を行っている。
UPDATE customers SET name_sei = CONCAT( REPEAT('〇', CHAR_LENGTH(name_sei) - 1), SUBSTRING(name_sei, -1, 1) ) ; UPDATE customers SET name_mei = CONCAT( REPEAT('〇', CHAR_LENGTH(name_mei) - 1), SUBSTRING(name_mei, -1, 1) ) ; UPDATE customers SET email = CONCAT( 'test', id, '@', SUBSTRING_INDEX(email, '@', -1) ) ; UPDATE customers SET tel = CONCAT( SUBSTRING_INDEX(tel, '-', 1), '-', LPAD(FLOOR(id / 10000), 4, '0'), '-', LPAD(MOD(id, 10000), 4, '0') ) ;
ユーザと権限
■ユーザの作成と権限の設定 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;
■権限の確認 ユーザを確認。
SELECT host, user FROM mysql.user;
権限を確認。
SHOW GRANTS FOR webmaster;
データベース名の変更
※未検証。 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」を参照。 また、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
セキュリティ
【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 ■データベースに接続できない場合 PHPからMySQL8を使用する際、「The server requested authentication method unknown to the client」というエラーになって接続できないことがある。 以下はChatGPTによる解説。
ご認識のとおり、エラーの原因は plugin が caching_sha2_password になっているためです。 MySQL 8.0 では、新しい認証方式 caching_sha2_password がデフォルトになりましたが、PHP7 の mysqli や PDO_MySQL 拡張モジュールがこれに未対応な場合があります。 そのため、PHP7 から接続するには、mysql_native_password に変更する必要があります。 ■解決方法：ユーザーの認証プラグインを mysql_native_password に変更する ALTER USER 'user'@'%' IDENTIFIED WITH mysql_native_password BY 'パスワード'; FLUSH PRIVILEGES; user と パスワード の部分は、実際に使っているユーザー名・パスワードに読み替えてください。
これをもとに、以下のように確認する。
> USE mysql; > SELECT user, host, plugin FROM user; +----------------------------+-----------+-----------------------+ | user | host | plugin | +----------------------------+-----------+-----------------------+ | root | % | caching_sha2_password | | user | % | caching_sha2_password | | mysql.infoschema | localhost | caching_sha2_password | | mysql.session | localhost | caching_sha2_password | | mysql.sys | localhost | caching_sha2_password | | root | localhost | caching_sha2_password | +----------------------------+-----------+-----------------------+ 6 rows in set (0.00 sec)
上記のようにpluginが「caching_sha2_password」となっている場合、PHPから接続したいユーザで「mysql_native_password」に変更する。 具体的には、以下のように実行する。
> USE mysql; > SELECT user, host, plugin FROM user; > ALTER USER 'user'@'%' IDENTIFIED WITH mysql_native_password BY 'userpass'; > FLUSH PRIVILEGES; > SELECT user, host, plugin FROM user;
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/ A5:SQL Mk-2に回帰した話 | フューチャー技術ブログ https://future-architect.github.io/articles/20220225a/ ■接続先の追加 初回起動時に接続先を確認されるので設定できる。 その後はメニューの「データベース → データベースの追加と削除」から追加できる。 ■接続先の追加（トンネル接続の場合） 接続設定画面の「SSH2トンネル」タブで、サーバの接続情報を入力する。 「基本」タブでは、そのサーバ内からデータベースへ接続する際の情報を入力する。 ■接続先の追加（Docker環境の場合） ・XAMPPをインストールしている。 ・WSL2に手動でDockerをインストールしている。 ・DockerのMySQLコンテナを起動させている。 という環境で確認。 通常どおりA5:SQLのメニューから。 データベース → データベースの追加と削除 → 追加 → MySQL/MariaDB直接接続 として接続設定を行う。 「基本」タブで以下のとおり設定。 ホスト名: localhost ポート番号: 3306 ユーザ名: root パスワード: （docker-compose.yml の MYSQL_ROOT_PASSWORD で設定した値。） データベース: （docker-compose.yml の MYSQL_DATABASE で設定した値。） これで接続ができた。 なおXAMPPとコンテナの両方を起動させている場合、 「コンテナが起動されていると、コンテナのMySQLに接続できるがXAMPPのMySQLには接続できない」 「コンテナが起動されていなければ、コンテナのMySQLに接続できないがXAMPPのMySQLには接続できる」 となった。 【Docker】DockerコンテナのDBに外部のA5:SQL Mk-2から接続する方法 | とんプログラミング https://programming.sincoston.com/docker-db-connect-pg-external/ 環境によっては接続先は localhost（127.0.0.1）で無いことがあるらしい。 その場合、以下などが参考になるかもしれない。（未検証。） Windows 10 Home（WSL2）のDockerでMySQL立ち上げてWindows側からA5:SQL Mk2で接続してみる | Snow System https://snowsystem.net/container/docker/docker-mysql-wsl-a5sql/ ■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図に関するオプションを調整できる。 基本的にデフォルトのままで問題なさそうだが、「オブジェクトをスナップする」の間隔など、必要に応じて調整する。 メニューの「ER図 → オブジェクトの追加 → リレーションシップ」から、テーブル間の関連を設定できる。 「親エンティティをクリック（主キーがある方） → 子エンティティをクリック（xxx_id がある方）」 とクリックすると線が引かれる。 この線をダブルクリックするとリレーションの詳細を設定できる。 基本的に ・「カーディナリティ」は「1」対「0以上」。 ・「依存」にチェックしない。 ・「リレーション」は「代理キー」と設定したい「xxx_id」の列。 として「適用」をクリックする。 …でいいはずだが、先の引かれる位置がおかしい？また調査する。 メニューの「設定 → オブジェクトの追加 → エンティティ」から、テーブルを追加できる。 追加されたエンティティをダブルクリックすると、列の追加ができる。 作成済みのエンティティをダブルクリックすると、列の追加編集削除などができる。 メニューの「ER図 → DDLを作成する」から、ER図をもとにSQLを作成できる。 必要に応じて設定を変更してから「DDL生成」ボタンを押す。 ER図に修正を加える場合、基本的にはデータベース定義を見ながらA5:SQL上で作業することになる…はず。 ただし新規にテーブルを追加する場合、いったん対象テーブルをリバース生成し、それをコピー＆ペーストで持ってくると早い…はず。 メニューの「ファイル → 名前をつけて保存」から、拡張子「a5er」のファイルとしてER図を保存できる。 次回このファイルを開けば、ER図が再現される。 その他、以下などを参考にすると良さそう。 若手プログラマー必読！５分で理解できるER図の書き方５ステップ https://it-koala.com/entity-relationship-diagram-1897 ひととおり基本が紹介されている。 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

Advertisement