🚀 ニフティ’s Notion

2024-09-25

【DB2024 #5】チューニング

目次
クエリチューニング クエリチューニングいつやる？ スロークエリ スロークエリの確認方法 スロークエリの原因 インデックス インデックスは万能か インデックスを作成した方が良いケース インデックスを作成しない方が良いケース EXPLAIN 例 実行結果 出力される項目と種類 LIMIT どんな時に使うか DBチューニング
クエリチューニング
  • クエリ ：データベースに送る命令文
  • クエリチューニング ：クエリのパフォーマンスを改善してDBのパフォーマンスを上げること

クエリチューニングいつやる？

データベースのパフォーマンスを上げたいときに行う

  • 命令や説明が回りくどいとパフォーマンスが落ちる
  • Webアプリの応答が遅くて、利用者が迷惑を被る
    • データベースから検索する 等……
  • データ処理で遅くなっているが原因が分からない
  • サービス開始直後（データが少ない間）は大丈夫だったが、データが増えてきたら処理が遅くなってきた

スロークエリ
スロークエリの確認方法
  • スロークエリはログとして出力することができる
スロークエリの原因
  • クエリに何かしらの問題がある
    • 不必要なデータを参照している 等……
    • 対策：期待された結果から変わらないようにクエリを書き換える
  • 巨大なテーブルデータ全体を読み取っている（ フルスキャン）
    • 対策：カラムに インデックス を貼ってフルスキャンを回避する
インデックス
  • インデックス（index; 索引）：データベースの検索性能を向上させる方法の一種
    • 本の索引のようなもの

インデックスは万能か
  • インデックスを作成した方がいいケースとそうでないケースがある

インデックスを作成した方が良いケース
  • 項目数、データ数が多い大規模な表の1%～15%程の行を頻繁にアクセスする
  • WHERE句やORDER BY句、GROUP BY句等で頻繁に使用されている列である
  • 列の値が比較的一意である（検索で絞り込みやすい）
    • カーディナリティ（データの種類の数）が高い
      • ○ 都道府県 → カーディナリティ=47
      • × 性別 → カーディナリティ=2
  • 参照整合性制約(外部キー制約)で使用されている外部キーになっている列である
    • 外部キー制約：他のテーブルの列を参照し、その列に無いデータを禁止
インデックスを作成しない方が良いケース
  • データ登録件数が少ない表である
    • データ件数が少ない場合、フルスキャンのほうが速いことがある
  • NULL値が多く、NULL以外の値を検索しない

EXPLAIN
  • データベース管理システム（DBMS）で提供されているクエリチューニング用のツール。クエリのパフォーマンス向上ができる

  • 使い方は確認したいSELECT文の先頭に EXPLAIN をつけるだけ
例

EXPLAIN SELECT * FROM employees WHERE first_name like 'A%';

実行結果 
mysql> EXPLAIN SELECT * FROM employees WHERE first_name like 'A%';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

出力される項目と種類
項目名 説明
id クエリにおけるテーブルのID
select_type このテーブルがどのような役割を果たすか
table MySQLがレコードを読み出すテーブル名
partitions 使用したパーティションテーブル
type レコードへのアクセス種別
possible_keys 選択可能なインデックス
key 実際に選択されたインデックス
key_len 選択されたkeyの長さ
ref 検索条件でkeyと比較されている値やカラム
rows 調査される行の見積もり
filtered テーブル条件によってフィルタ処理される行の推定の割合
Extra どのような戦略をたてて検索しようとしたか
LIMIT
  • SQLクエリの結果から 特定の数 の行だけを返すことを指定する条件
    • DBサーバーに余分なデータの処理や、データ転送の負荷をかけずに必要なデータだけを取得することができる
どんな時に使うか

例：重いポケモンを上位5体表示する 

SELECT * FROM pokemon
ORDER BY heavy DESC
LIMIT 5;

DBチューニング

  • データベースのパフォーマンスを上げる方法はクエリチューニングだけではない
  • データベースのパフォーマンスを上げる方法は以下の2つに分けられる
    • クエリチューニング
      • レスポンスタイム（クエリの実行時間）を短縮させる
      • テーブルの構成やクエリの最適化を行う
      • 秒〜分レベル
    • DBチューニング
      • スループット（時間当たりの処理量）を向上させる
      • 並列処理が出来る件数が増えるほどこのスループットの値が向上する
      • ミリ秒レベル

次: 【DB2024 #6】NoSQLについて

エンジニア定例 DB回 2024