What's New Qiita

20200725のMySQLに関する記事は5件です。

  • 投稿日：2020-07-25T20:26:41+09:00

『失敗から学ぶRDBの正しい歩き方』を読んでいく

決意表明

これから読む。永遠の未完にならないようにする。

概要

1. データベースの迷宮

事前知識

  • アンチパターンの事前知識としての、RDBにおける制約
    • PRIMARY KEY制約
      • 重複とNULLがなく、そのテーブルで一意な行であることを確定させる
    • NOT NULL制約
      • NULLがないことを確定させる
    • UNIQUE制約
      • その値がテーブルで一意であることを確定させる（NULLは許容される）
    • CHECK制約
      • 指定した条件の値のみが保存されていることを確定させる
    • DEFAULT制約
      • 値が指定されない時に保存される値を決める。それにより初期値を確定させる。
    • FOREIGN KEY制約（外部キー制約）
      • 別テーブルの主キーと参照整合性が保たれていることを確定させる

アンチパターン

  • わかりづらい設計や名前はデータベースの破綻の始まり

まとめ

  • テーブルやレコードの中身がわかる適切な名前をつける
  • 外部キー制約やCHECK制約を利用してデータを適切に防ぐ
    • MySQLにおいては8.0.16以降からCHECK制約に対応。
  • リレーショナルモデルに基づいた設計を心がける
  • 何らかの理由で課題の残る設計をした場合、早めに改修する
    • カラム名の変更などでは、RDBのINSERTやUPDATEのactionに対するトリガーなどを利用して少しずつ切り替えていく。

2. 失われた事実

アンチパターン

  • 今ある事実のみを保存してしまうと過去の事実を失ってしまうので、例外処理を行うときやトラブル時に状況把握する場合に、情報が不足する。
    • 消費税率がからむEC系、管理画面の作業ログなど

まとめ

  • 「打ち消しのINSERT」として保存する。
  • 払い戻しなどの取り消し処理に対応できるかに気を付ける。
  • 配送状況などステータス変化を終えるかに気を付ける。
  • トラブル対応時、欲しい情報が失われていないかに気を付ける。 別手段として、
  • 遅延レプリケーションを使う。
  • アプリケーションログとしてElasticsearchなどの分析ツールに保存する。

RDBへの履歴の保存のデメリット

  • レコードの保存量が増えるためテーブルサイズが増える
  • 集計が単純な主キー検索ではなくなるため、テーブルサイズが肥大化した際に検索速度が劣化する

遅延レプリケーションについて

  • 指定した時間分、スレーブDBに対して、マスタDBからのレプリケーションを遅延させることができる。
    • 1日遅れや2時間遅れのスレーブDBを作るなど。
  • マスタDB上で行われた誤った作業から保護する
  • システムのデバッグ時の再現手法として使う ※DBの複製なので物理的なコストは高い。 ※バックアップは別に必ず取るようにする。

3. やり過ぎたJOIN

アンチパターン

  • パフォーマンスに直結するJOINに対する不理解
    • 多段JOINと不要なJOIN
    • JOINの内部表にINDEXがない

JOINについて

JOINの特性

  • 集合の和の結果なので、ベン図で理解する。

    • INNER JOIN
    • LEFT OUTER JOIN
    • RIGHT OUTER JOIN
    • FULL OUTER JOIN

  • MySQLは、FULL OUTER JOINをサポートしていないため、RIGHT JOINの結果をLEFT JOINの結果とUNIONすることで表現する。

SELECT
  *
FROM
  会員
  LEFT OUTER JOIN 都道府県
    ON 会員.出身県id = 都道府県.県id
UNION
SELECT
  *
FROM
  会員
  RIGHT OUTER JOIN 都道府県
    ON 会員.出身県id = 都道府県.県id

JOINの問題点

  • 「JOINは掛け算」であり、JOINの回数が増えると急激に重くなる
    • SQLの処理の中で最も重い処理の1つ。

JOINアルゴリズムの種類

  • Nested Loop Join（NLJ）
    • JOIN元のテーブルが先のテーブルに対し1行ずつループして処理する
  • Hash Join
    • 一度に全件を読み込んで処理する
      1. 小さい票を全件読み取ってHash表を作成
      2. 大きい表の結合列をHash表の値と比較して結合
      3. 両テーブルを1回ずつ全件読み取り

  • Sort Merge Join

    • 全件を（2つの表の結合キーで）ソートして上から順に比較する

  • PostgreSQLは3種類のJOINをサポートしているが、MySQLはNJLしかサポートしていない。

    • PostgreSQLは、大きな2つの表のJOINや不等号を使ったJOINが得意。
    • MySQLも、「内部表に適切なINDEX」があり、「小さな外部表をもとに等号で結合」する場合は、非常に高速に処理できる。

まとめ

  • JOINは必要最低限
  • JOINするテーブルは小さくしてからJOINする。
  • 複雑なクエリになった場合はViewを活用する。
    • > ビューとはテーブルから取得したいデータの条件を定義してあたかも独立したテーブルのように扱えるようにしたものです。 （参照: ビューの作成）
  • INDEXを適切に活用する。

マテリアライズド・ビュー

  • SQLの結果を実体のあるViewにする機能
    • クエリの結果のテーブルを作ること。
    • 再作成の時にテーブルの作り直しが不要で、共有ロックのリフレッシュのみで良い。
    • 例）単価表の更新が1日1回で良い場合などは、集計結果を別テーブルとして保存することで、高速に参照できる。

4. 効かないINDEX

INDEXの種類

  • 最も一般的なのが、BTree INDEX

INDEXを利用するために必要な条件

  • 検索結果がテーブル全体の20%未満（実務レベルでは10%未満）
  • 検索対象のテーブルが十分に大きい（数万〜数十万行）
    • 1,000行程度のテーブルではINDEXを参照するよりもテーブルスキャンが効率的なケースが多い。 - 例）47行しかない都道府県マスタ。

アンチパターン

設定したINDEXが効かない（使われない）ケース

1. 条件にその列を使っていない
  • 検索対象の列がWHERE句やJOINの際のON句などで利用されていない場合、利用できない。
# bad
# 比較する検索の対象は age * 10 の計算結果となるため、
# 全ての行に対して計算・比較してしまう
SELECT * FROM users WHERE age * 10 > 100;
# good
SELECT * FROM users WHERE age > 100/10;
2. カーディナリティの低い列に対する検索
  • カーディナリティとは？
    • 「列に格納されるデータの値にどのくらいの種類があるのか」
      • 種類が多い：カーディナリティが高い（シーケンシャルなidなど）
      • 種類が少ない；カーディナリティが低い（性別など）
  • カーディナリティが低い列に対して絞り込む場合は検索結果が多くなりやすいため、INDEXをうまく利用することが難しい
3. あいまいな検索
  • 前方一致
    • 標準でINDEXを利用。
  • 後方一致
    • INDEXを利用するには、reverse()などの関数で対象の列をひっくり返して別の列に保存したり、PostgreSQLの式INDEXを利用したりする必要がある。
  • 部分一致
    • INDEXを利用するには、全文検索インデックスなどを利用する必要がある。
      • 例） MySQL：Mroonga, PostgreSQL：PGroonga
4. 統計情報と実際のテーブルで乖離がある場合
  • INDEXを利用するかどうかは、クエリの実行時にオプティマイザが判断して決めている。
  • この時の判断材料となるのが統計情報。
    • 統計情報は定期的にテーブルから一定数のサンプリングを行い、それをもとに作られる。
  • 次のような場合、「実際のデータ分布から乖離した統計情報」が作られることがある
    1. サンプリングの前に大量のデータ更新が行われた
    2. サンプリングで偏ったデータを収集した
  • これらのような場合は、オプティマイザによる統計情報の更新を行う。
  • また、利用するINDEXや統計情報を固定する手法もある。

まとめ

  • INDEX（とくにBTree INDEX）の特性をしっかりと把握して適切なINDEXを設定する
  • INDEXを利用できるクエリを実行する
  • INDEXを活用できるテーブル設計をする
  • スロークエリログやデータの状態などをしっかりとモニタリングする

インデックスショットガン

  • 「SQLアンチパターン」に出てくる、闇雲にINDEXを設定しまくるというアンチパターン。
  • INDEXを設定することで INSERT/UPDATE/DELETE が遅くなる。
  • 複雑な複合INDEX（複数の列に対するINDEX）を設定し過ぎると、オプティマイザが不適切なINDEXを選ぶことがある。

MENTORの原則

  • Measure（測定）
    • スロークエリログやDBのパフォーマンスなどをモニタリング
  • Explain（解析）
    • 実行計画を見てクエリが遅くなっている原因を追求
  • Nominate（指名）
    • ボトルネックの原因（インデックス未定義など）を特定
  • Test（試験）
    • ボトルネック改善（インデックス追加など）を実施し、処理時間を測定。改善後の全体的なパフォーマンスを確認
  • Optimize（最適化）
    • DBパラメータの最適化を定期的に実施し、インデックスがキャッシュメモリに載るように最適化

  • Rebuild（再構築）

    • 統計情報やインデックスを定期的に再構築

  • INDEXを設計する際の3つの問い

    1. このテーブルは1年後、3年後、5年後、何行くらいになるだろうか
    2. このINDEXは複合INDEXでまとめる、または単一のINDEXで十分絞り込めるのではないだろうか
    3. 今このINDEXを張るべきか

5. フラグの闇

アンチパターン

  • テーブルに削除という「状態」を持たせてしまっている。
    • 削除フラグ以外にも、課金状態やユーザー状態などがある

『とりあえず削除フラグ』

  • 『とりあえず削除フラグ』は以下の問題を含む。
1. クエリの複雑化
  • 仕様変更時などに影響範囲が広がってしまう。
2. UNIQUE制約が使えない
  • UNIQUE制約が使えないデメリット
    • データの重複を防げない
    • 該当列に対して外部キー制約を利用できない
    • 外部キー制約を利用できないことでデータの関連性を担保できない
3. カーディナリティが低くなる
  • 重複が少ない（カーディナリティが高い）データであればINDEXをうまく利用できる。
  • 削除フラグのようなカーディナリティが低いデータは、検索時に必ず削除フラグを含めなければならず、ボトルネックの理由になる。

まとめ

事実のみを保存する

  • トリガーを使う

    • トリガーとは、テーブルに対するある操作に反応して、別の操作を実行する機能。
    • 「削除済み」のためのテーブルを作り、データを移す。

  • Viewを使う

    • 有効なデータだけの表を作る。
    • 高速化にはつながらない。
      • 高速化を意識するなら、PostgreSQLならばマテリアライズド・ビュー、MySQLならサマリーテーブルを生成する。

「状態」を持たせるのは絶対にだめか

  • 持たせる場合は以下に注意

    • 対象のテーブルが小さく、INDEXが不要
    • そのテーブルに関連するテーブルの親になることがなく、データを取得する際に頻繁にJOINの対象になることがない
    • UNIQUE制約が不要で、外部キーでデータの整合性を担保する必要がない

削除フラグを利用したくなるケース

  • エンドユーザーから見えなくしたいが、データは消したくない
  • 削除したデータを検索したい
  • データを消さずにログに残したい
  • 操作を誤ってもなかったことにしたい

  • 削除してもすぐに元に戻したい

  • 以上は削除フラグ以外の設計でも対応できるため、安易にテーブルに状態を持たせることなく設計することを心がける。

削除フラグ以外のフラグの闇のケース

  • statusカラム
    • 取り出す際にWHERE句を利用したり、View側で表示のバグを防ぐためにif文でcheckを入れる必要が発生する。
  • 送信ステータス
    • メルマガの送信ステータスには、2つの問題がある。
      1. テーブルが肥大化していき、将来的にパフォーマンスがボトルネックになる。
      2. トランザクションの問題。配信が重複しないように、メールの送信リストに対して排他的な行ロックを取って管理しなければならない。
        • テーブルに状態を持たせている場合に大量のメルマガを送信すると、長時間ロックを取ることになる。
        • データが小さい場合や並列処理が少ない場合には、問題が顕在化しにくいため、初期の段階で早めに対策することが大切。

6. ソートの依存

アンチパターン

  • ソートはパフォーマンス面で考えると、RDBが苦手とする分野。
    • ページが昔のものになればなるほど、表示に時間がかかる。

リレーショナルモデルとソートのしくみ

リレーショナルモデル

  • リレーショナルモデルは集合を扱うデータモデル
    • 次のような性質がある。
      • 重複がない
      • 実在する要素しかない（NULLがない）
      • 要素に順序がない
    • ソートはリレーショナルモデルの外の世界の話なので、パフォーマンスのボトルネックになりやすいのは必然。

ORDER BYのしくみ

RDBMSのクエリ実行部分『エグゼキュータ』
  • 次の順にSQLを評価する。
  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. HAVING
  7. SELECT
  8. DISTINCT
  9. ORDER BY
  10. LIMIT
  • 全てのデータを取り出してからORDER BYで並び替え、最後にLIMITで必要なデータを切り分ける。
SELECT ← 3番目
  *
FROM ← 1番目
  users
WHERE ← 2番目
  id < 10000
ORDER BY name ← 4番目
LIMIT 100 ← 5番目

WHERE句狙いのINDEX

  • ORDER BYは、データ大きくなればなるほど重い処理になるが、事前にWHERE句を使うことで、対象を絞り込むことができる。

    • 1億件のレコードを並び替えるのと、それを1,000件に絞り込んでから並び替えるのとでは、同じORDER BY後に100件を取り出すとしても、処理時間が雲泥の差になる。

  • WHERE句がINDEXを活用でき、データを充分に小さくできるのであれば、パフォーマンスは劇的に向上する。

SELECT * FROM users WHERE 性別 = '男性' ORDER BY 出身県 id LIMIT 5
  1. 性別のINDEXを利用して検索
  2. 該当の結果を取り出し、それをソート
  3. ソート中に5件が確定したら結果を返す

ORDER BY句狙いのINDEX

  • WHERE句が必ずしも最適解ではない。

    • WHERE句でINDEXを利用する列は、カーディナリティが少なく、データの値が偏っているとINDEXが有効に活用されない。

  • PostgreSQL、MySQLの標準的なINDEXの実装であるBtree INDEXは、データを「ソート済み」の状態で保存している。

    • 対象のソート結果とINDEXが同じならば、INDEXから取り出せば良い。
      • これを『ORDER BY句狙いのINDEx』と言う。
SELECT * FROM users WHERE 性別 = '男性' ORDER BY 出身県id LIMIT 5
  1. 出身県idのINDEXを利用して検索
  2. ソートされた結果を取り出しながら性別を評価
  3. 5件が確定したら結果を返す
  • ORDER BYでINDEXを利用した場合の強み。
    1. ソートの処理が不要になる
    2. 評価数がLIMITの件数に達した時点で結果を返せる

MySQLでのORDER BY句狙い

まとめ

  • ORDER BYを速くするには

    • データを小さくする 　- INDEXを活用する

  • 実際に業務でうまく活用するためには「しっかりと実行計画を見る」

    • ORDER BY句狙いが良いか、WHERE句狙いが良いか

idを指定してソートを高速化

  • WHERE句で絞る値を追加する
    • ページャでは、「次のページ」として渡すのは「最後に表示された行のid」にする
      • データ量が増えてもINDEXを活用できるため高速
      • ページ数が深くなってもOFFSETを利用しないため取得行が肥大化しない
  • SELECTでINDEXに含まれているデータのみが必要な場合は、実際のテーブルデータにアクセせずに結果を返せる
    • MySQLの場合は「カバリングインデックス」、PostgreSQLの場合は「インデックスオンリースキャン」が期待できる。

idを指定できないケース

  • ORDER BYやINDEXの仕組みを知り、SQLを工夫することでパフォーマンスを何十倍にもすることができる。

1.ORDER BYの結果がidの順番と関係なく、またUNIQUEな値でない

ORDER BY name, id DESC

のようにidでもソートすることで改善する場合もある。

2.UNION/GROUP BY/HAVINGが使いたい

  • UNION/GROUP BY/HAVINGを利用した場合、結果にidが使えないケースがある。
  • UNION/GROUP BY/HAVINGとORDER BYは相性が良くない。
    • GROUP BYとORDER BYが組み合わさったケースでは、集計結果のサマリーテーブルを作り、そこにINDEXを貼ることを検討する。

大きなデータをソートしたいとき

  1. アプリ側でソート

    • フロントエンド側からデータの一覧をREST APIで取得し、それをJavaScript側でソートさせて表示させる方法。
      • データ一覧をアプリに全て渡す必要があり、データサイズが大き過ぎると通信がボトルネックになることもある。

  2. ソート済みの結果をキャッシュして利用

  • ソートの処理が決まっており、結果が変更されにくいデータなどはキャッシュが有効。
    • 郵便番号や市町村の住所など。
    • 更新頻度が高いデータでは使い物にならない。
  1. NoSQLなどを利用してソート
  • 近年では一般的な方法。

RDBを補う存在、Redis

  • RedisとRDBMSは非常に相性が良い。
    • Sorted Set（ソート済みセット型）を使うと、高速にソート済みデータを検索したり、取得したりすることができる。
      • トランザクションが必要な課金系のデータはRDBMSとの住み分けになっており、処理の結果はRedisに保存して参照はRedisから行うといった構成はよく取られる。

7. 隠された状態

コラム EAVの代替案になり得るJSONデータ型

7-3. 隠された状態が生む問題

トリガー

8. JSONの甘い罠

8-2. 「なんでもJSON」の危険性

コラム JSONデータ型のほかの使い道

9. 強過ぎる制約

9-2. 似たようなアンチパターン

コラム PostgreSQLの遅延制約

10. 転んだ後のバックアップ

10-2. 3つのバックアップ

10-3. バックアップ戦略

11. 見られないエラーログ

11-2. エラーログの種類

ログを見やすくする工夫

12. 監視されないデータベース

12-2. ミドルウェアの監視の種類

コラム 可視化と改善は両輪

13. 知らないロック

13.2 ロックの基本

14. ロックの功罪

トランザクション分離レベル

15. 簡単過ぎる不整合

15.2 非正規化の誘惑

コラム 非正規化と履歴データの違い

16. キャッシュ中毒

キャッシュについて知る

17. 複雑なクエリ

17-2. 複雑なクエリの発端

18. ノーチェンジ・コンフィグ

18-2. コンフィグを知る

19. 塩漬けのバージョン

19-2. なぜバージョンアップは重要なのか

20. フレームワーク依存症

20-2. フレームワークが生むメリットとデメリット

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-07-25T19:27:06+09:00

Laravelのmigrationで詰まった話

Laravelで、todoアプリを作成中です。
migrationのファイルを作成し、
ターミナルにて

＄php artisan migrate

いざ！実行！

エラーが出ました。
エラー内容は以下の通りです。

  Illuminate\Database\QueryException  : SQLSTATE[42000]: 
Syntax error or access violation: 1071 Specified key was too long; 
max key length is 767 bytes (SQL: alter table `users` add unique 
`users_email_unique`(`email`))

指定されたキーが長すぎます。キーの最大長は767バイトです。と

参考にさせていただいた記事
https://qiita.com/beer_geek/items/6e4264db142745ea666f

mysqlをアップデートしました。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-07-25T14:45:13+09:00

チャットアプリ制作

個人の学習の知見を広げるため、チャットアプリを制作しました。

アプリの概要

・ユーザーの登録ができる
・ユーザーがグループを作成できる
・グループを指定して、メッセージを送ることができる

使用技術

・Ruby
・Ruby on Rails
・JavaScript
・MySQL
・AWS
・nginx
・unicorn
・Capistrano

本番環境のリンク

18.178.232.222

githubのリンク

https://github.com/mitsugu3/ChatSpace

感想

アプリ制作の流れを掴むことができた。
AWSで、本番環境にあげるのが
シークレットキーなどの知識が必要だったので難しく感じたが、
検索記事を参考に自走することができた。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-07-25T13:11:55+09:00

ローカルのDockerにLAMP環境を構築

はじめに

ローカルのDockerにLAMP環境を構築する方法です。
以下の環境を構築します。
Windows１０Proに構築します。

  • CentOS7.4.1708
  • PHP7.2
  • Laravel5.5
  • Apache
  • MySQL5.7
  • phpMyAdmin]

※予め出来ているDockerファイルを利用するため、Dockerファイルの作成方法などを記載するわけではありません。

Docker Desktopのインストール

以下のサイトからDocker Desktopをインストールします。
https://www.docker.com/get-started

Dockerファイル群の配置

以下から、ApaLara.zipをDLし、任意の場所に配置します。
https://github.com/shinjinakatamari/LAMP

配置フォルダの追加

DockerDeskTopを開き、のsetting＞Resources>FILE SHARINGで設定フォルダを追加し、Apply&Restartをクリックする

コマンドの実施

コマンドプロンプトを起動し、以下のコマンドで、配置フォルダに移動する。

cd c:¥・・・

以下のコマンドを実行し、Dockerの作成と立ち上げを行う。

docker-compose build
docker-compose up -d

起動確認

http://localhost/
上記にアクセスすると、Laravelのページが表示される。

http://localhost:8080/
上記にアクセスすると、PHPMYAdminのページが表示される。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-07-25T10:45:51+09:00

【Laravel】Authを使ったログインとユーザー登録

Authでログイン機能とユーザー登録機能を用意する

composerでlaravel/uiをインストールし、artisanコマンドでスカフォールドを生成する。

composer require laravel/ui

php artisan ui bootstrap --auth

※uiではbootstrapの他に、vueやreactを用いることもできる。
uiの生成
これでログイン機能とユーザー登録機能は用意できたが、このままではデザインがないのでnpmをインストールし、run devでコンパイルしてデザインを付ける。

npm install
npm run dev

データベースの作成

ユーザー情報を保存するためにデータベースを作成する。
今回はmysqlを使う。
mampのスタート画面のtoolsからphpmyadminを開く。
新規作成からデータベース名を埋めてデータベースを作成する。
laravelの.envファイルの内容を書きえる。

DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel
DB_USERNAME=root
DB_PASSWORD=

↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

B_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=【mampで決めたデータベースの名前】
DB_USERNAME=root
DB_PASSWORD=root

database/migrationsにusers_tableのmigrationが作成されているので、あとはartisanコマンドでmigrationとすればusers_tableが作られてログインとユーザーの登録ができるようになる。

Authを使ってアクセスを制限

Authのログイン機能を使い、ログインしていればページが表示され、ログインしていなければログインページを表示する。

Route::get('sample', SampleContoroller@index})->middleware('auth')->name('sample.index');

#groupを使う場合
Route::group(['prefix' => 'sample', 'middleware' => 'auth'], function () {
    Route::get('/', 'SampleController@index')->name('sample.index');
    Route::get('edit', 'SampleController@edit')->name('sample.edit');
    Route::post('update', 'SampleController@update')->name('sample.update');
});

middlewareをつけることでログインしないとそのページにアクセスできないようにできる。
認証について

バージョン

php:7.3.11
Laravel:7.21.0

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

日付別

タグ一覧