MySQL_のお話.pdf

By.shiro16

Transcript

1. MySQL のお話 minne 事業部 CTL shiro16
2. @_shiro16 shiro16
3. 目次 • MySQL #とは • MySQL のパラメータの紹介 • クエリの最適化 • レプリケーションについて • まとめ + α
4. MySQL って知ってますか？
5. MySQL 使ってますか？
6. そもそも MySQL とは • データベース管理システム • データの検索・追加・更新・削除が行える • トランザクション機能を備えている • アクセス権の管理 • PostgreSQL との違いは？
7. データベース管理システムとはどんなものか？
8. データベース管理システムとは • スプレッドシートを開いてみましょう • このスプレッドシートが 1 database • 1 シート = table • 1 列 = column • 1 行 = record • リレーションも簡単に出来ちゃう
9. そもそも MySQL とは • データベース管理システム • データの検索・追加・更新・削除が行える • トランザクション機能を備えている • アクセス権の管理 • PostgreSQL との違いは？
10. データの操作とは？
11. データの操作とは • SELECT (検索) ◦ 条件は基本的な WHERE ちょっと難しい HAVING など様々 • INSERT (追加) ◦ 1 クエリで 1 件の追加から n 件の追加まで • UPDATE (更新) ◦ 1 クエリで 1 件の更新から n 件の更新まで • DELETE (削除) ◦ 1 クエリで 1 件の削除から n 件の削除まで
12. 他にも • Table の検索・追加・更新・削除が行えたり • プロセスの検索・削除(KILL)が行えたり
13. そもそも MySQL とは • データベース管理システム • データの検索・追加・更新・削除が行える • トランザクション機能を備えている • アクセス権の管理 • PostgreSQL との違いは？
14. トランザクションって知ってます？
15. トランザクションとは • n 個の処理をまとめたもの • n 個全ての処理が成功した際に全ての処理が成功したとみなす • n 個の処理が 1 つでも失敗した場合はそれまでの処理をなかったものとみなす
16. トランザクションとは • トランザクション開始時に save(BEGIN) する • n 個全ての処理が成功した際は上書き save(COMMIT) する • 1 つでも失敗した場合開始からの変更を無かったことにするため最初の save を load(ROLLBACK) する • ※あくまで変更を行なったデータのみ load するので他の処理で変更されたデー タに影響はない
17. そもそも MySQL とは • データベース管理システム • データの検索・追加・更新・削除が行える • トランザクション機能を備えている • アクセス権の管理 • PostgreSQL との違いは？
18. 誰でもどんなデータにアクセスできるのやばくない？
19. そんな時にアクセス権を管理する
20. アクセス権の管理 • GRANT 構文を使って行う • 権限が付与できる範囲は様々 ◦ SELECT/UPDATE/INSERT/DELETE/CREATE などの単位で細かく設定 可能 ◦ どんなユーザが存在するか？は “SELECT * FROM mysql.user\G” • GRANT 構文を実行したら FLUSH PRIVILEGES を忘れずに
21. そもそも MySQL とは • データベース管理システム • データの検索・追加・更新・削除が行える • トランザクション機能を備えている • アクセス権の管理 • PostgreSQL との違いは？
22. PostgreSQL との違い https://employment.en-japan.com/engineerhub/entry/2017/09/05/110000
23. MySQL のパラメータについて
24. 現在の設定されているパラーメータの確認
25. SHOW VARIABLES 構文を使う
26. SHOW VARIABLES 構文について • MySQL の設定には GLOBAL/SESSION が存在する • “SHOW GLOBAL VARIABLES;” で GLOBAL の設定 • “SHOW VARIABLES;” で SESSION の設定 • GLOBAL/SESSION の違いは全体の設定か、現在接続しているセッ ションでのみ有効な設定か • ちょっと変更して試したい場合は SESSION を使うのが良い
27. SHOW VARIABLES 構文について • “SHOW VARIABLES” を実行すると大量に設定項目が出てくる • “SHOW VARIABLES LIKE "%connection%";” などで絞り込みが可能 • max_connections は接続できる最大数 ◦ Rails の server を並べるなら max_connections をあげないと死 • 意味を調べておくと後々役に立つかも
28. と言っても多いのでとりあえず見るやつ
29. オススメの項目 • 先で述べた max_connections はとりあえず上げておいても良い ◦ Rails なら unicorn が立ち上がらないのでエラーにすぐに気づけるので最 悪その時でも可 • 文字コード関連 ◦ 最低でも utf8 絵文字や特殊な文字が入ることがわかっているなら utf8mb4 ◦ 今時、新規で Shift_JIS/EUC-JP とか正気か？となる • InnoDB 周りの設定は一通り把握しておくのが良い ◦ “SHOW GLOBAL VARIABLES LIKE "%innodb%";” ◦ 今時、新規で MyISAM とか（ry
30. クエリの最適化について
31. 最適化って何か知ってます？
32. なぜ最適化が必要なのか？
33. 最適化が必要な理由 • クエリはレコード数などによって実行時間が変わってくる • 最初は 0.00 sec で取得できていたものがある時から 1.00 sec とか普通 ◦ (ユーザがアクセスするページでは) 1 sec は遅い • どうにかして早くしないといけない
34. どうやって最適化するか？
35. 最適化の方法 • INDEX というものを使う • 基本的には WHERE 句や ORDER BY 句で指定されている column に INDEX を貼る ◦ 実際にはどのような INDEX が効果的か調べる必要がある ◦ 単独/複数の column にどのように INDEX を貼ると効果的か？ • EXPLAIN を使って今の実行計画を調べることが可能 ◦ “EXPLAIN SELECT * FROM ….” と EXPLAIN の後に実行計画を確認し たいクエリを続ける • INDEX について語り始めると長くなるので詳細は割愛 ◦ INDEX に関してはこちらの記事がわかりやすい
36. 最適化についてちょっとデモ
37. レプリケーションについて
38. レプリケーションを知ってる人？
39. レプリケーションの概要 • MySQL server が 1 台のみだといずれ限界がくる ◦ 限界は server の cpu/memory による • server を n 台にすればいい(雑) • slave server を用意する ◦ 検索系のクエリは slave へ ◦ 更新系のクエリは master へ • リアルタイムでのデータの同期は binlog を使って行われる • 負荷分散の他にも master server がお亡くなりになった際に slave を master に昇格させることが可能(自動でも可)
43. レプリケーションの概要 • master 1 台で稼働させることも可能 • 可用性という面では master-slave 構成が良い • slave = backup ではない ◦ 別でしっかり backup を取るの推奨 ◦ slave は 2 台以上が望ましい
44. まとめ
45. MySQL まとめ • MySQL 覚えることいっぱい • アクセス権を適切に設定してセキュアに • 各種パラメータはいざという時に役立つ • クエリチューニングが出来ると強みになる ◦ 意外にちゃんと出来る人は少ない ◦ shiro16 は今も 10 年前もクエリチューニングしてる • レプリケーションも仕組みを知っていると役立つはず • マネージドなもの(RDS等)は仕組みを知らなくてもポチポチで使えるが 仕組みを知っていると困った時に役に立つよ
46. 最後にオススメの本
47. MySQL 本 • 実践ハイパフォーマンスMySQL ◦ MySQL の全てが書いてあるといっても過言ではない(かも) ◦ 少し古いが... ◦ 最初の会社の上司に server 3 台と本を渡されて「レプリケーション組んで みな」と言われたのはいい思い出 • SQLアンチパターン ◦ MySQL というよりはリレーショナルデータベースを使う際にシステムが気を つけることが書いてある
48. 予備スライドです
49. 予備スライドです