■目次
設計データベースの文字コード外部キー制約を無視してインポートロック関連トランザクションチューニングmysqldumpデータ取得データ登録データ削除その他のデータ操作ER図セキュリティトラブルコーディングスタイル
■設計
SQLアンチパターン勉強会 - Qiita https://qiita.com/iwata@github/items/9a47ddababd8e0a82662 ユーザ情報を保存する時のテーブル設計 - そーだいなるらくがき帳 https://soudai.hatenablog.com/entry/2018/05/01/204442 ソーシャルゲームのためのMySQL入門 - Technology of DeNA https://engineer.dena.jp/2010/11/mysql-for-socialgame.html MySQLテーブル設計入門 https://www.slideshare.net/yoku0825/mysql-47591576
■データベースの文字コード
utf8_unicode_ciではなく、utf8_general_ciにしておくのが無難 MySQLのutf8_general_ciとutf8_unicode_ciの違い http://program.sagasite.info/wiki/index.php?MySQL%E3%81%AEutf8_general_ci%E3%81%A8utf8_unicode_ci%E3... utf8_unicode_ci に対する日本の開発者の見解 http://blog.kamipo.net/entry/2015/03/08/145045 MySQLの照合順序 http://qiita.com/Vit-Symty/items/159c27d7d62c78ee9ce7 ※今はutf8だけでなく、utf8mb4についても検討する
■外部キー制約を無視してインポート
データの移植時、外部キー制約があるとSQLを丸ごとインポートできないことがある SQLファイルの最初に以下を追加し、
SET FOREIGN_KEY_CHECKS=0;
最後に以下を追加すればインポートできる
SET FOREIGN_KEY_CHECKS=1;
■ロック関連
Webアプリケーションへの同時アクセス対策メモ | refirio.org http://refirio.org/view/367 嵐のコンサートがあるとダブルブッキングしてしまうホテル予約システムを作ってみた | 徳丸浩の日記 https://blog.tokumaru.org/2015/05/blog-post.html MySQLのINSERT/UPDATE時におこる不整合対策 - Slow Dance http://d.hatena.ne.jp/LukeSilvia/20110123/p1 MySQL - InnoDBのロック関連まとめ - Qiita https://qiita.com/mizzwithliam/items/31fb68217899bd0559e8 MySQL テーブルのロック - とみぞーノート http://wiki.bit-hive.com/tomizoo/pg/MySQL%20%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%AE%E3%83%AD%E... デッドロックを回避するために - 技術ブログ | 株式会社クラウディア https://cloudear.jp/blog/?p=1335 MySQLでINSERTのデッドロックに嵌る人を1人でも減らすために - ichirin2501's diary https://ichirin2501.hatenablog.com/entry/2015/12/24/164916
■トランザクション
■ロールバック 【忘備録】InnoDBでもロールバックが効かない文 http://mementomori.info/%E3%80%90%E5%BF%98%E5%82%99%E9%8C%B2%E3%80%91innodb%E3%81%A7%E3%82%82%E3%83%... MySQLでTruncateはRollback出来るのか? | SRIA BLOG - 宮城県仙台市のWEBシステム開発・スマホアプリ開発 https://www.sria.co.jp/blog/2014/08/mysql-can-do-rollback-truncate/ MySQLの「暗黙のトランザクションコミット」対策:トランザクション中でも安全にCREATE TABLEなどをする方法 - Qiita https://qiita.com/suin/items/3527297a22632f3db31d MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.3.3 暗黙的なコミットを発生させるステートメント https://dev.mysql.com/doc/refman/5.6/ja/implicit-commit.html ■分離レベル MySQLのデフォルトのトランザクション分離レベルは SELECT がスナップショットを参照する - ngyukiの日記 http://ngyuki.hatenablog.com/entry/2013/02/02/202558 MySQL自体の設定を変えるのがリスクになるなら
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
でその接続だけ変更できるみたい
■チューニング
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パフォーマンス監視のツボ(クエリ編) | 株式会社インフィニットループ技術ブログ 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
■mysqldump
mysqldumpまとめ - Qiita https://qiita.com/PlanetMeron/items/3a41e14607a65bc9b60c
■データ取得
【MySQL】昇順ソートで、NULLを最後に表示する - MySQL < 技術ブログ < 株式会社プロネット | WEBシステム開発20年以上の実績 https://www.p-nt.com/technicblog/archives/123 MySQLでgroup byの結果を逆順(最大順、最新順)で取得する方法 https://xov.jp/e/176/ 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】グループ毎に連番を振る - Qiita https://qiita.com/toyottoyo/items/813338f4756dee41e49b 【MySQL】二つのカラムの組み合わせでユニーク設定するには. - My... - Yahoo!知恵袋 https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1252536032
■データ登録
InnoDBの8KBの壁にぶち当たったら。 - sawara.me http://sawara.me/mysql/2219/ ダミーデータを作成する。 insert_generate_series - イノベートな非日常 http://d.hatena.ne.jp/IT7C/20140520/1400522465 代理キー管理のテーブルにINSERTするためのSQLファイルを作成する例 INSERT INTO examinations(school_id, name, sort) VALUES(99999, 'AOエントリー', 1); INSERT INTO examinations(school_id, name, sort) VALUES(99999, 'A入試', 2); INSERT INTO examinations(school_id, name, sort) VALUES(99999, 'B入試', 3); INSERT INTO examinations(school_id, name, sort) VALUES(99999, '二次入試', 4); UPDATE examinations SET school_id = (SELECT id FROM schools WHERE code='test') WHERE school_id = 99999;
■データ削除
全データの削除(TRUNCATE文) - データの追加と削除 - MySQLの使い方 https://www.dbonline.jp/mysql/insert/index12.html
■その他のデータ操作
親テーブルの更新/削除の時の挙動(ON DELETE句,ON UPDATE句) - テーブルの作成 - MySQLの使い方 https://www.dbonline.jp/mysql/table/index12.html 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 ※MySQLにおいては、「NO ACTION」と「RESTRICT」は同じ挙動 ■CASCADE 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 が等しいデータも削除される
■ER図
A5:SQL Mk-2 - フリーの汎用SQL開発ツール/ER図ツール .. 松原正和 https://a5m2.mmatsubara.com/ テーブルを選択して「データベース → ER図 → ER図のりーバス生成」を実行 少し待つ すべてのテーブルを選択して「リバースER生成」を実行 しばらく待つとER図が表示される。ただしテーブル間の関連は反映されていない 以下を参考にする限り、関連は手動での設定が必要みたい 描ける!ER図 https://www.slideshare.net/nekoruri/er-14127685
■セキュリティ
【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
■コーディングスタイル
分析SQLのコーディングスタイル - クックパッド開発者ブログ http://techlife.cookpad.com/entry/2016/11/09/000033 あくまでも参考程度に