MySQL.txt

■目次

・設計・データベースの文字コード・少数を管理する・真偽値を管理する・暗黙の型変換・ロック関連・トランザクション・監視・チューニング・mysqldump ・データ取得・データ登録・データ削除・データ操作・データをJSONで扱う・データの複製・データのマスキング・権限の設定・データベース名の変更・外部キー制約・外部キー制約を無視して登録（インポート）・削除・レプリケーション・チューニングテスト・コーディングスタイル・セキュリティ・トラブル・MySQL8へのバージョンアップ・MariaDBについて・Adminer ・A5:SQL Mk-2 ・HeidiSQL

■設計

SQLアンチパターン勉強会 - Qiita https://qiita.com/iwata@github/items/9a47ddababd8e0a82662 ユーザ情報を保存する時のテーブル設計 - そーだいなるらくがき帳 https://soudai.hatenablog.com/entry/2018/05/01/204442 ソーシャルゲームのためのMySQL入門 - Technology of DeNA https://engineer.dena.jp/2010/11/mysql-for-socialgame.html MySQLテーブル設計入門 https://www.slideshare.net/yoku0825/mysql-47591576

■データベースの文字コード

utf8_unicode_ciではなく、utf8_general_ciにしておくのが無難 MySQLのutf8_general_ciとutf8_unicode_ciの違い http://program.sagasite.info/wiki/index.php?MySQL%E3%81%AEutf8_general_ci%E3%81%A8utf8_unicode_ci%E3... utf8_unicode_ci に対する日本の開発者の見解 http://blog.kamipo.net/entry/2015/03/08/145045 MySQLの照合順序 http://qiita.com/Vit-Symty/items/159c27d7d62c78ee9ce7 ※今はutf8だけでなく、utf8mb4についても検討する

■少数を管理する

0.00〜100.00 までの値を管理する場合、合計5桁で小数部分が2桁なので、以下のように指定する -100.00〜100.00 までの値を管理する場合も、同様の定義で格納できる（マイナスがついていても格納できる）

rate DECIMAL(5, 2) NOT NULL COMMENT '受注率',

※MySQL バージョン 3.23 より前のバージョンでは引数に、符号と小数点に必要なスペースを含める必要があったらしい【MySQL】データ型一覧 - ysklog http://ysklog.net/mysql/1797.html 数値型(データ型)のまとめ - [MySQL/SQL] ぺんたん info http://pentan.info/sql/mysql/mysql_type_num.html

■真偽値を管理する

MySQLに boolean の型は無いので、以下のようにして対応する

verified TINYINT(1) UNSIGNED NOT NULL COMMENT 'データ確認',

■暗黙の型変換

code が 528 と 528A のデータがあった場合、以下のように検索すると両方ヒットしてしまうので注意

SELECT * FROM products WHERE code = 528;

以下のように型を明示的にして検索する必要がある

SELECT * FROM products WHERE code = '528';

PDOの場合、以下のようにして型を明示できる

PDO::PARAM_STR PDO::PARAM_INT PDO::PARAM_BOOL

MySQLが文字列と数値の比較の際に自動的に変換してしまう件 | 創作メモ帳 https://sousaku-memo.net/php-system/123 SQLの暗黙の型変換はワナがいっぱい | 徳丸浩の日記 https://blog.tokumaru.org/2009/09/implicit-type-conversion-of-SQL-is-trap-full.html

■ロック関連

MySQLのプロセス確認と強制終了の方法は、サーバメモの command.txt の「MySQLでプロセスを確認＆強制終了」を参照 Webアプリケーションへの同時アクセス対策メモ | refirio.org http://refirio.org/view/367 嵐のコンサートがあるとダブルブッキングしてしまうホテル予約システムを作ってみた | 徳丸浩の日記 https://blog.tokumaru.org/2015/05/blog-post.html MySQLのINSERT/UPDATE時におこる不整合対策 - Slow Dance http://d.hatena.ne.jp/LukeSilvia/20110123/p1 MySQL - InnoDBのロック関連まとめ - Qiita https://qiita.com/mizzwithliam/items/31fb68217899bd0559e8 MySQL テーブルのロック - とみぞーノート http://wiki.bit-hive.com/tomizoo/pg/MySQL%20%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%AE%E3%83%AD%E... デッドロックを回避するために - 技術ブログ | 株式会社クラウディア https://cloudear.jp/blog/?p=1335 MySQLでINSERTのデッドロックに嵌る人を1人でも減らすために - ichirin2501's diary https://ichirin2501.hatenablog.com/entry/2015/12/24/164916 doc/innodb.md at master - ichirin2501/doc https://github.com/ichirin2501/doc/blob/master/innodb.md MySQLでSELECT FOR UPDATEと行ロックの挙動を検証してみた - JUST FOR FUN http://taiga.hatenadiary.com/entry/2018/02/12/170109 MySQLのトランザクション処理中にALTERするとコミットされる - ペチパーノート http://butterbull.hatenablog.com/entry/2014/06/12/150756 ソシャゲエンジニアの自分が開発に必須だなと思った知識（MySQL編） - Qiita https://qiita.com/west-hiroaki/items/ea6ee53765282a9c86cb 第75回　MySQLのさまざまなタイムアウトオプションについて：MySQL道普請便り｜gihyo.jp … 技術評論社 https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0075

■トランザクション

■ロールバック【忘備録】InnoDBでもロールバックが効かない文 http://mementomori.info/%E3%80%90%E5%BF%98%E5%82%99%E9%8C%B2%E3%80%91innodb%E3%81%A7%E3%82%82%E3%83%... MySQLでTruncateはRollback出来るのか？ | SRIA BLOG - 宮城県仙台市のWEBシステム開発・スマホアプリ開発 https://www.sria.co.jp/blog/2014/08/mysql-can-do-rollback-truncate/ MySQLの「暗黙のトランザクションコミット」対策：トランザクション中でも安全にCREATE TABLEなどをする方法 - Qiita https://qiita.com/suin/items/3527297a22632f3db31d MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.3.3 暗黙的なコミットを発生させるステートメント https://dev.mysql.com/doc/refman/5.6/ja/implicit-commit.html ■分離レベル MySQLのデフォルトのトランザクション分離レベルは SELECT がスナップショットを参照する - ngyukiの日記 http://ngyuki.hatenablog.com/entry/2013/02/02/202558 MySQL自体の設定を変えるのがリスクになるなら

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

でその接続だけ変更できるみたい現在のトランザクション分離レベルは、以下のSQLで確認できる

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

■監視

MySQLの監視 ~ mackerel-plugin-mysqlを読み解く - そーだいなるらくがき帳 https://soudai.hatenablog.com/entry/mackerel-plugin-mysql

■チューニング

MySQLのExplainを確認する - woshidan's blog https://woshidan.hatenablog.com/entry/2015/06/20/165817 MySQLのexplainとかについてしらべたときのメモ - Qiita https://qiita.com/lastcat_/items/de7b530a94fbcf9ba646 漢(オトコ)のコンピュータ道: MySQLのEXPLAINを徹底解説!! http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html 漢(オトコ)のコンピュータ道: MySQLを高速化する10の方法 http://nippondanji.blogspot.jp/2009/02/mysql10.html ヤフー社内でやってるMySQLチューニングセミナー大公開 https://www.slideshare.net/techblogyahoo/mysql-58540246 Cygamesを支えるPHPと、その高速化の取り組み // Speaker Deck https://speakerdeck.com/cygames/cygameswozhi-eruphpto-sofalsegao-su-hua-falsequ-rizu-mi 秒間100万クエリを受け付ける大規模ソーシャルゲームのバックエンドDBシステムの設計・運用ノウハウ // Speaker Deck https://speakerdeck.com/cygames/miao-jian-100mo-kueriwoshou-kefu-keruda-gui-mo-sosiyarugemufalsebatu... MySQL with InnoDB のインデックスの基礎知識とありがちな間違い - クックパッド開発者ブログ http://techlife.cookpad.com/entry/2017/04/18/092524 これだけ見れば大丈夫！ーMySQLパフォーマンス監視のツボ（クエリ編） | 株式会社インフィニットループ技術ブログ 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でORDER BYをつけないときの並び順 - かみぽわーる https://blog.kamipo.net/entry/2016/12/24/234944 「MySQLでORDER BYがついていないときに返す結果セットの並び順は定められていない。」とされている並び順を指定しないと、稀に意図しない順でデータが取得されるので注意【MySQL】昇順ソートで、NULLを最後に表示する - MySQL < 技術ブログ < 株式会社プロネット | WEBシステム開発20年以上の実績 https://www.p-nt.com/technicblog/archives/123 ■グループ MySQLでgroup byの結果を逆順（最大順、最新順）で取得する方法 https://xov.jp/e/176/ 【MySQL】グループ毎に連番を振る - Qiita https://qiita.com/toyottoyo/items/813338f4756dee41e49b ■その他 MySQLで全文検索 - FULLTEXTインデックスの基礎知識｜blog｜たたみラボ http://www.tatamilab.jp/rnd/archives/000389.html MySQL FULLTEXT + Ngram : LIKE検索より数十倍高速な、お手軽日本語全文検索について｜blog｜たたみラボ http://www.tatamilab.jp/rnd/archives/000390.html MySQLでの近似値順での値の取得について質問させてください。【OKWAVE】 https://okwave.jp/qa/q6056886.html SQL関数coalesceの使い方と読み方 | データベース | DoRuby https://doruby.jp/users/akio0911_on_rails/entries/SQL_coalesce_ MYSQLの関数 COALESCE - Qiita https://qiita.com/mikakane/items/1e45c2a798d0c7edffda 【MySQL】二つのカラムの組み合わせでユニーク設定するには. - My... - Yahoo!知恵袋 https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1252536032 そろそろSQLのウィンドウ関数を理解したい - 連載1/3話 - Qiita https://qiita.com/w-sato-ist/items/63600a3ab84aad38e879 SQL記述者全員が理解すべきSELECT文の実行順序のお話 - Qiita https://qiita.com/k_0120/items/a27ea1fc3b9bddc77fa1

■データ登録

InnoDBの8KBの壁にぶち当たったら。 - sawara.me http://sawara.me/mysql/2219/ ダミーデータを作成する。 insert_generate_series - イノベートな非日常 http://d.hatena.ne.jp/IT7C/20140520/1400522465 代理キー管理のテーブルにINSERTするためのSQLファイルを作成する例

INSERT INTO examinations(school_id, name, sort) VALUES(99999, 'AOエントリー', 1); INSERT INTO examinations(school_id, name, sort) VALUES(99999, 'A入試', 2); INSERT INTO examinations(school_id, name, sort) VALUES(99999, 'B入試', 3); INSERT INTO examinations(school_id, name, sort) VALUES(99999, '二次入試', 4); UPDATE examinations SET school_id = (SELECT id FROM schools WHERE code='test') WHERE school_id = 99999;

■データ削除

全データの削除(TRUNCATE文) - データの追加と削除 - MySQLの使い方 https://www.dbonline.jp/mysql/insert/index12.html

■データ操作

データベース作成

CREATE DATABASE test DEFAULT CHARACTER SET utf8mb4;

データベース削除

DROP 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;

登録

INSERT INTO address(no, name, tel) VALUES(1, '山田太郎', '090-1234-5678'); INSERT INTO address VALUES(1, '山田太郎', '090-1234-5678');

表示

SELECT name, tel FROM address; SELECT token, COUNT(*) FROM devices GROUP BY token;

更新

UPDATE address SET name = '山田健二' WHERE name = '山田太郎'; UPDATE address SET name = '山田健二', tel = '090-2345-6789' WHERE name = '山田太郎';

削除

DELETE FROM address WHERE name = '山田太郎'; DELETE FROM products WHERE id >= 5001;

複製

INSERT INTO address_test SELECT * FROM address WHERE no >= 3;

オートインクリメントをリセット

ALTER TABLE products AUTO_INCREMENT = 0;

日付をフォーマットして表示

SELECT DATE_FORMAT(created, '%Y-%m-%d') AS created FROM histories GROUP BY created ORDER BY id DESC LIMIT 100;

日付ごとにランキング

SELECT DATE_FORMAT(created, '%Y-%m-%d') AS created, COUNT(*) AS count FROM histories GROUP BY DATE_FORMAT(created, '%Y-%m-%d') ORDER BY count DESC LIMIT 100;

連結して表示

SELECT histories.created_at, histories.user_id, histories.device_id, devices.token FROM histories LEFT JOIN devices ON histories.device_id = devices.id WHERE histories.detail IS NULL ORDER BY histories.id LIMIT 100 ; SELECT devices.token, COUNT(devices.token) FROM histories LEFT JOIN devices ON histories.device_id = devices.id WHERE histories.detail IS NULL AND histories.created_at = '2019-02-08 15:02:29' GROUP BY devices.token ;

■データをJSONで扱う

※未検証 MySQLでJSONを扱う - Qiita https://qiita.com/abcb2/items/8affae03caa3e94068b5 MySQLでJSON型を使う(JSON関数編) | スマートスタイル TECH BLOG｜データベース＆クラウドの最新技術情報を配信 https://www.s-style.co.jp/blog/2019/03/3614/ もう知ってた？ MySQL 5.7でNoSQLっぽくJSONデータを扱う方法 - WPJ https://www.webprofessional.jp/use-json-data-fields-mysql-databases/ MySQLでJSON型を使う（パフォーマンス編） | スマートスタイル TECH BLOG｜データベース＆クラウドの最新技術情報を配信 https://www.s-style.co.jp/blog/2017/06/420/

■データの複製

別名で同じ仕様のテーブルを作成し、そこに「INSERT INTO ○○ SELECT * FROM △△ WHERE no >= 3;」で必要な分だけデータを複製し、その後「ALTER TABLE」もしくは「RENAME TABLE」でテーブル名を変更して新テーブルに入れ替えるのが良さそう MySQLの「ALTER TABLE RENAME」と「RENAME TABLE」 - なからなLife https://atsuizo.hatenadiary.jp/entry/2016/06/16/100000 MySQLで3億レコード物理削除した話 - Qiita https://qiita.com/ningenMe/items/57bd66359d89b691ba0d

■データのマスキング

MySQLで個人情報をマスキング - Qiita https://qiita.com/murak/items/7add80e9624b1abf103a

■権限の設定

MySQL5.7からは、ユーザを作成してからGRANTする必要がある以下は webmaster ユーザの作成例

CREATE USER webmaster@localhost IDENTIFIED BY '1234';

testデータベースに対しての全権限を与える例

GRANT ALL PRIVILEGES ON test.* TO webmaster@localhost;

環境によっては「ALL PRIVILEGES」で一括指定するとエラーになるので、必要なものを個別に指定する

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, EVENT ON test.* TO webmaster@localhost;

■データベース名の変更

※未検証 MySQL5.1.23以降は RENAME DATABASE が廃止されたらしい一応 RENAME TABLE を使えば擬似的に対応できるらしい MySQLでdumpせずに "RENAME DATABASE" を実現する - Qiita https://qiita.com/whisper0077/items/06add22b692a25bd19b5 【MySQL】データベースの名前変更（リネーム、RENAME）の方法のひとつ at softelメモ https://www.softel.co.jp/blogs/tech/archives/5319

■外部キー制約

親テーブルの更新/削除の時の挙動(ON DELETE句,ON UPDATE句) - テーブルの作成 - MySQLの使い方 https://www.dbonline.jp/mysql/table/index12.html MySQLの外部キー制約RESTRICT,CASCADE,SET NULL,NO ACTIONの違いは？ - Qiita https://qiita.com/suin/items/21fe6c5a78c1505b19cb 外部キー制約を使うと、テーブル間のデータ整合性を厳密に保つことができる制約は「FOREIGN KEY(外部キー名) REFERENCES 親テーブル名(カラム名) ON オプション」のように指定でき、オプションには以下のようなものがある RESTRICT: 親テーブルに対して削除または更新を行うとエラーとなる設定を省略した場合は RESTRICT を設定したのと同じ NO ACTION: 親テーブルに対して削除または更新を行うとエラーとなる RESTRICT を設定した場合と同じ CASCADE: 親テーブルに対して削除または更新を行うと、子テーブルで同じ値を持つカラムのデータに対して削除または更新を行う上記のように、MySQLでは「NO ACTION」と「RESTRICT」は同じ挙動となる以下、実際にテーブルへの関連データを登録して、外部キー制約（FOREIGN KEY 制約）などの挙動を試してみる

CREATE TABLE goods( id INT, name VARCHAR(10), INDEX(name) ) ENGINE=InnoDB; INSERT INTO goods VALUES(1, '消しゴム'); INSERT INTO goods VALUES(2, '鉛筆'); INSERT INTO goods VALUES(3, 'ボールペン');

RESTRICT

CREATE TABLE sales1( id INT, name VARCHAR(10), date DATE, INDEX(name), FOREIGN KEY(name) REFERENCES goods(name) ON UPDATE RESTRICT ) ENGINE=InnoDB; INSERT INTO sales1 VALUES(1, '鉛筆', '2017-01-20'); INSERT INTO sales1 VALUES(2, '消しゴム', '2017-02-05'); UPDATE goods SET name = 'ケシゴム' WHERE id = 1; #1451 - Cannot delete or update a parent row: a foreign key constraint fails

NO ACTION

CREATE TABLE sales2( id INT, name VARCHAR(10), date DATE, INDEX(name), FOREIGN KEY(name) REFERENCES goods(name) ON DELETE NO ACTION ) engine=InnoDB; INSERT INTO sales2 VALUES(1, '鉛筆', '2017-01-20'); INSERT INTO sales2 VALUES(2, '消しゴム', '2017-02-05'); DELETE FROM goods WHERE id = 2; #1451 - Cannot delete or update a parent row: a foreign key constraint fails

CASCADE（MySQLにおいては、「NO ACTION」と「RESTRICT」は同じ挙動）

CREATE TABLE sales3( id INT, name VARCHAR(10), date DATE, INDEX(name), FOREIGN KEY(name) REFERENCES goods(name) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB; INSERT INTO sales3 VALUES(1, '鉛筆', '2017-01-20'); INSERT INTO sales3 VALUES(2, '消しゴム', '2017-02-05'); INSERT INTO sales3 VALUES(3, '消しゴム', '2017-03-02'); INSERT INTO sales3 VALUES(4, '鉛筆', '2017-04-26'); UPDATE goods SET name = 'ケシゴム' WHERE id = 1; # sales3 テーブルの name も更新される DELETE FROM goods WHERE id = 1; # sales3 テーブルの name が等しいデータも削除される

上記ではテーブル作成時に制約を追加している後から追加する場合、以下のようにALTER TABLEで追加する制約名は必須では無いようだが、削除のときに使用するので付けておくのが無難か以下の制約名はLaravelが自動作成した制約名。制約名はランダムな文字列を付けることが多い？

ALTER TABLE sales1 ADD CONSTRAINT FK_23A0E6661220EA6 FOREIGN KEY(name) REFERENCES goods(name)

以下のようにすれば削除できる

ALTER TABLE sales1 DROP CONSTRAINT FK_23A0E6661220EA6;

■外部キー制約を無視して登録（インポート）・削除

データの移植時、外部キー制約があるとSQLを丸ごと登録（インポート）・削除できないことがある SQLファイルの最初に以下を追加し、

SET FOREIGN_KEY_CHECKS = 0;

最後に以下を追加すれば、一時的に外部キー制約を無視してインポートできる

SET FOREIGN_KEY_CHECKS = 1;

以下は削除コードの一例

TRUNCATE TABLE dtb_mail_history; TRUNCATE TABLE dtb_order_pdf; TRUNCATE TABLE dtb_url_token; SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE dtb_order_item; TRUNCATE TABLE dtb_shipping; TRUNCATE TABLE dtb_order; SET FOREIGN_KEY_CHECKS = 1;

■レプリケーション

レプリケーションの設定については、サーバメモの etcetera.txt を参照 MySQL のレプリケーションから10年間逃げてきた我々が学んだこと8選 - Cybozu Inside Out | サイボウズエンジニアのブログ https://blog.cybozu.io/entry/2020/10/26/173000

■チューニングテスト

■テーブル以下のテーブルに、TwitterのAPIから取得したデータを記録し続けていくツイートテーブルとユーザテーブルを連結して一覧表示キーワード検索やフォロワー数ソートなどを実装して、速度や負荷の様子を見る。スロークエリの記録も試したい

CREATE TABLE `tweets` ( `id` varchar(80) NOT NULL COMMENT 'ID', `created` datetime NOT NULL COMMENT '登録日時', `user_id` varchar(80) NOT NULL COMMENT 'ユーザID', `text` text NOT NULL COMMENT 'ツイート内容', `retweeted_id` varchar(80) DEFAULT NULL COMMENT 'リツイートID', `retweeted_user_id` varchar(80) DEFAULT NULL COMMENT 'リツイートユーザID', PRIMARY KEY (`id`), KEY `id` (`id`), KEY `created` (`created`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ツイート'; CREATE TABLE `users` ( `id` varchar(80) NOT NULL COMMENT 'ID', `created` datetime NOT NULL COMMENT '登録日時', `name` varchar(255) NOT NULL COMMENT '名前', `screen_name` varchar(80) NOT NULL COMMENT 'スクリーンネーム', `location` varchar(80) DEFAULT NULL COMMENT '位置情報', `description` text COMMENT '説明', `url` varchar(255) DEFAULT NULL COMMENT 'URL', `image_url` varchar(255) NOT NULL COMMENT '画像URL', `followers_count` int(11) NOT NULL COMMENT 'フォロワー数', `friends_count` int(11) NOT NULL COMMENT 'フォロー数', `listed_count` int(11) NOT NULL COMMENT 'リスト数', `statuses_count` int(11) NOT NULL COMMENT 'リツイート数', PRIMARY KEY (`id`), KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ユーザ';

■環境 CentOS 7 + nginx 1.12.2 + MySQL 5.7.21 https://refirio.net/twitter/ ■検証内容 30万件くらいまでは、何も考えずに作れる 30万件くらいになると、「データ比較」「データ推移」が少し重い。最初の一覧やキーワード検索もほんの少し重いがまだ問題ない 50万件くらいでも十分に早い。Apache→nginxで、PHPもMySQLも新しくしているからか 60万件くらいになると、CPU使用率でuserの使用が確認できるようになってきた。ただし0.6%程度なので大したものではない 80万件くらいになると、最初の一覧でもはっきり遅いと感じる。「データ比較」はかなり重い 120万件トップページの表示が3秒ほどかかるデータ取得時に記事数をキャッシュするようにし、それをもとにページ数などを求めるように変更トップページが2秒ほどで表示されるようになった levis標準機能のエクスポートではエラーになるが、execコマンドでmysqldumpを実行すればダンプできる 200万件トップページの表示が3〜4秒ほどかかるキーワード検索すると8〜10秒ほどかかるデータ比較は20秒ほどかかるデータ推移は3秒ほどかかる CPU使用率は2〜3%くらい実用的ではない程度には重いインデックスを張る（3つ目だけで良かったかもしれないが要検証）

ALTER TABLE users ADD INDEX (id); ALTER TABLE tweets ADD INDEX (id); ALTER TABLE tweets ADD INDEX (created);

トップページの表示が一瞬になったキーワード検索も一瞬になったデータ比較とデータ推移は1〜2秒で表示されるようになった CPU使用率も下がった（2〜3%あった使用率が0.7%くらいになった） SWAPの発生も20〜40KB/sあったものが0.01KB/sくらいになった全体的に明確な改善が見られる 300万件トップページの表示は一瞬キーワード検索も一瞬データ比較とデータ推移は1〜2秒で表示されるユーザごとのページは3秒くらいかかる 400万件（300万件のときと大差なし） 600万件（300万件のときと大差なし） 1200万件（300万件のときと大差なし） 1900万件トップページの表示は一瞬キーワード検索とデータ比較とデータ推移は1〜2秒で表示されるユーザごとのページは5秒くらいかかる 2700万件トップページの表示は一瞬キーワード検索とデータ比較とデータ推移は1〜2秒で表示されるユーザごとのページも1〜2秒で表示される何故か早くなっているが、さくら側でハードウェアのメンテナンスなどがあったのかもしれないが不明

■コーディングスタイル

分析SQLのコーディングスタイル - クックパッド開発者ブログ http://techlife.cookpad.com/entry/2016/11/09/000033 「分析SQLスタイルガイド」をかなり真面目に考えた - Qiita https://qiita.com/kai_data/items/6c119c43ad3626226dfc あくまでも参考程度に

■セキュリティ

【SQLインジェクション対策】徳丸先生に怒られない、動的SQLの安全な組み立て方 https://www.slideshare.net/kwatch/sql-53624630 プレースホルダよりもSQLテンプレートの方が安全？要勉強

■トラブル

誰も教えてくれなかったMySQLの障害解析方法 - Qiita https://qiita.com/muran001/items/14f19959d4723ffc29cc MySQLがおかしい！あなたならどうしますか？ - MySQL Casual Advent Calendar 2011 - As a Futurist... https://blog.riywo.com/2011/12/02/002204/ MySQLのレプリケーション遅延をローカルで再現させる手順その1 - 株式会社CFlatの明後日スタイルのブログ http://cflat-inc.hatenablog.com/entry/20130812/1376265269 RDBのトラブルの現場を追え！ / rdb-Troubleshooting - Speaker Deck https://speakerdeck.com/soudai/rdb-troubleshooting RDBのトラブルの現場を追え！ ~ 様々な現場を見る ~ / rdb-troubleshooting2 - Speaker Deck https://speakerdeck.com/soudai/rdb-troubleshooting2 MySQLで3億レコード物理削除した話 - Qiita https://qiita.com/ningenMe/items/57bd66359d89b691ba0d

■MySQL8へのバージョンアップ

MySQL 8.0 への移行が完了しました〜さようなら全ての MySQL 5.7〜 - Cybozu Inside Out | サイボウズエンジニアのブログ https://blog.cybozu.io/entry/2021/05/24/175000

■MariaDBについて

MariaDBはMySQLの作者によって、MySQLのソースコードをベースに作成されたもの詳細はサーバメモの web.txt を参照

■Adminer

※phpMyAdminよりも軽量なデータベース管理ツール 1つのPHPファイルを設置するだけで動作する Adminer - Database management in a single PHP file https://www.adminer.org/ Adminerを設置する - Qiita https://qiita.com/nissuk/items/2b1aee7f81f351c7ab05

■A5:SQL Mk-2

※Windows用のデータベース管理ツールテーブル定義をもとにER図の雛形を作成することもできる A5:SQL Mk-2 - フリーの汎用SQL開発ツール/ER図ツール .. 松原正和 https://a5m2.mmatsubara.com/ ■接続先の追加初回起動時に接続先を確認されるので設定できるその後はメニューの「データベース → データベースの追加と削除」から追加できる ■ER図を書く以下でツールの基本操作が紹介されている ERエディタ - ER図ツール https://a5m2.mmatsubara.com/help/ER/ ER図を作成したいデータベースを選択し、メニューから「データベース → ER図 → ER図のりーバス生成」を実行テーブル一覧が表示されるので、すべてのテーブルを選択して「リバースER生成」ボタンを押すしばらく待つとER図が表示される。ただしテーブル間の関連は反映されていない以下を参考にする限り、関連は手動での設定が必要みたい描ける！ER図 https://www.slideshare.net/nekoruri/er-14127685 メニューの「ER図 → 表示レベル」は「属性」がデフォルトとなっているが、これを例えば「属性とデータ型」にすると、列名も一緒に表示されるこの設定は「属性（論理名と物理名）とデータ型（位置揃え）」にしておくのが判りやすいかもしれないメニューの「ER図 → ER図プロパティ」から、プロジェクト名や著作者などを登録できる何に使われているかは不明だが、単純にドキュメントの情報として持っているだけかもしれない「RDBMS種類」は、適切なものを選択しておくと後々混乱しなくて良さそうメニューの「設定 → オプション → ER図」から、ER図に関するオプションを調整できる基本的にデフォルトのままで問題なさそうだが、「オブジェクトをスナップする」の間隔など、必要に応じて調整するメニューの「設定 → オブジェクトの追加 → リレーションシップ」から、テーブル間の関連を設定できる「親エンティティをクリック（主キーがある方） → 子エンティティをクリック（xxx_id がある方）」とクリックすると線が引かれるこの線をダブルクリックするとリレーションの詳細を設定できる基本的に・「カーディナリティ」は「1」対「0以上」・「依存」にチェックしない・「リレーション」は「代理キー」と設定したい「xxx_id」の列として「適用」をクリックする …でいいはずだが、先の引かれる位置がおかしい？また調査するメニューの「設定 → オブジェクトの追加 → エンティティ」から、テーブルを追加できる追加されたエンティティをダブルクリックすると、列の追加ができる作成済みのエンティティをダブルクリックすると、列の追加編集削除などができるメニューの「ER図 → DDLを作成する」から、ER図をもとにSQLを作成できる必要に応じて設定を変更してから「DDL生成」ボタンを押すメニューの「ファイル → 名前をつけて保存」から、拡張子「a5er」のファイルとしてER図を保存できる次回このファイルを開けば、ER図が再現されるその他、以下などを参考にすると良さそう ER図とは？書き方や用語・テクニックを徹底解説 https://products.sint.co.jp/ober/blog/create-er-diagram 「ER図を見やすくするテクニック」部分などを参考にする「親エンティティは左上から書く」は、常に意識しておくと良さそう若手プログラマー必読！５分で理解できるER図の書き方５ステップ https://it-koala.com/entity-relationship-diagram-1897

■HeidiSQL

※Windows用のデータベース管理ツール Navicatは有料だし、MySQL Workbenchは日本語化に難があるし …だが、HeidiSQLなら問題なく使えそう「HeidiSQL」オープンソースのデータベース管理ソフト - 窓の杜 https://forest.watch.impress.co.jp/library/software/heidisql/ 以下、SSHトンネルで接続するためのメモ「plinkの取得」「puttygenの取得」「pemをppkに変換」という作業が必要なので、それぞれ記載する HeidiSQL SSH tunnel経由でMySQL(MariaDB)に接続する - Symfoware https://symfoware.blog.fc2.com/blog-entry-1942.html ■plinkの取得以下から plink.exe の64bit版をダウンロード Download PuTTY: latest release (0.74) https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html 手動で以下に配置した C:\Program Files\plink\plink.exe ■puttygenの取得以下から puttygen.exe の64bit版をダウンロード Download PuTTY: latest release (0.74) https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html 手動で以下に配置した C:\Program Files\puttygen\puttygen.exe ■pemをppkに変換 PuTTYgenを使って変換できる「Load」ボタンからpemファイルを読み込み、その後「SavePrivateKey」ボタンからppkファイルを出力する（「SavePublicKey」では無いので注意） pem ⇒ ppk 変換 - Qiita https://qiita.com/naka46/items/3297242f3386b5f9e3dc id_rsa⇒pem⇔ppk 相互変換 | Punio Kid Works http://www.puniokid.com/tips/linux/69/ ■HeidiSQL上での設定 ※plink.exeなどを設定した後の汎用的な手順 ※ユーザ名などは例「設定」タブで以下を設定ネットワーク種別: MySQL(SSH tunnel) ホスト名: localhost ユーザー: webmaster パスワード: abcd1234 ポート: 3306 データベース: test 「SSHトンネル」タブで以下を設定 SSHホスト＋ポート: 203.0.113.1 + 10022 ユーザー名: ssh-user パスワード: （空欄）秘密鍵ファイル: Example.ppk ■トラブル（未解決）新規に接続しようとしたとき、以下のエラーで接続できないことがあった

PLINKは、予期せず終了しました。コマンドライン： C:\Program Files\plink\plink.exe -ssh ec2-user@203.0.113.1 -P 10022 -i "C:\Users\refirio\SSH\test\test.ppk" -N -L 3307:production.xxxxx.ap-northeast-1.rds.amazonaws.com:3306

コマンドプロンプトから直接実行してみると、以下のエラーが表示された

C:\Users\refirio>C:\"Program Files"\plink\plink.exe -ssh ec2-user@203.0.113.1 -P 10022 -i "C:\Users\refirio\SSH\test\test.ppk" -N -L 3307:production.xxxxx.ap-northeast-1.rds.amazonaws.com:3306 Using username "ec2-user". FATAL ERROR: No supported authentication methods available (server sent: publickey,gssapi-keyex,gssapi-with-mic)

エラーメッセージをもとに調べてみると、authorized_keys のパーミッション問題の可能性はありそう問題の解決方法 SSHの鍵認証接続を題材に - Qiita https://qiita.com/szly/items/ef057727e8b6d227ed29 …だが、調べてみるとパーミッションに問題は無かったまたそもそも、このパーミッションが不正な場合はSSH接続自体できないはずだが、Poderosaで接続はできた試しにHeidiSQLではなくMySQL Workbenchなら、すんなり接続できた原因不明だが、HeidiSQLの問題で特定環境での接続に対応できないなどあるのかもしれない