close
プログラミング クラウド Microsoft Azure 情報処理資格 基本情報技術者 IT基礎 応用情報技術者 開発・設計方法 オブジェクト指向 内定者・新人研修 プログラミング基礎 アルゴリズム コンピュータ数学 内定者研修 新人研修 ヒューマンスキル プロジェクトマネジメント プレゼンテーション リーダーシップ 組織マネジメント ネゴシエーション ロジカルシンキング Java UI/UX HTTP JavaScript基礎 情報処理資格 ネットワークスペシャリスト ネットワーク インターネットルーティング応用 IPアドレス データベース応用 SQL応用 パフォーマンスチューニング データベース設計 ER図 概念設計(データベース) 論理設計(データベース) IT資格 Linux基礎 OS・システム基盤 セキュリティ TCP/IP OSI参照モデル データベースセキュリティ ファイアウォール 標的型攻撃 SQLインジェクション ネットワーク基本設計 CCNA Cisco プロジェクトマネジメント資格 情報処理資格プロジェクトマネージャ 情報処理安全確保支援士 人事給与 財務会計 管理会計 簿記 生産管理 在庫管理 ERP バランススコアカード 情報処理資格 ITアーキテクト 情報処理資格 ITストラテジスト 情報処理資格 ITサービスマネジメント 情報処理資格 システム監査 PMBOK® PMP® プロジェクト計画 WBS リスクコントロール ITIL ITサービスマネジメント 要求定義 要件定義 見積手法 ビジネスインダストリ 業種・業界知識 業務知識 提案力 ソフトウェアテスト基礎 情報処理資格 データベーススペシャリスト ハードウェア基礎 外部設計(基本設計) 内部設計(詳細設計) データベース基礎 SQL基礎 RDBMS 物理設計(データベース) C++ Ruby MVC基礎 Webアプリケーション開発 JavaEE Javaプログラミング応用 フレームワーク MVC応用 Spring フレームワーク ソフトウェアテスト応用 テスト手法 Junit スマートフォンアプリ開発 Androidアプリ開発 C# 基礎 C# 応用 負荷テスト Javaプログラミング基礎 ソフトウェアテスト コーチング メンタリング HTML/CSS サーバー構築 仮想化技術 KVS (NoSQL) アジャイル スクラム ファシリテーション C言語 ITパスポート JSTQB データサイエンス 単体テスト ユニットテスト キャリアアップ インターネットルーティング基礎 パケット解析 LAN構築 データベース データサイエンティスト トレンド 障害対応 インフラ監視 HTTP/2.0 コンピュータサイエンス VPN ネットワーク物理設計 データベース障害 JavaScript モダンJS (Modern JavaScript) 応用 MVS応用 バックアップ/リカバリ 分散処理 Hadoop Hive Python AI 深層学習(DeepLearning) CentOS Linux応用 Zabbix シェルスクリプト Infrastructure as Code Windowsサーバー基礎 内部設計 Docker DevOps Windowsサーバー応用 NginX chef Ainsible ロジカルライティング R テスト自動化 Jenkins Git 継続的インテグレーション (CI) バージョン管理 Vagrant 要求分析 Redmine 継続的インテグレーション(CI) 継続的デリバリー (CD) ヒューマンリソース管理 Web API マイクロサービス コミュニケーション 業務知識/業界知識 マーケティング 語学 AWS 法務 IoT ビジネスマナー OJT 業務効率化 表計算ソフト オフィスソフト コンプライアンス フロントエンド Subversion PHP 関数型プログラミング Laravel モダンJS (Modern JavaScript) 基礎 Android Studio 機械学習 iOSアプリ開発 ぷプログラミング React 次世代高度IT人材 共創 IPA Raspberry Pi Xamarin スクリプト言語 GoF CUI VBA 資格 ビジネス文書 jQuery 研修参加レポート マネジメント OSPF テーブル設計 アンガーマネジメント クリティカル・シンキング PDU 経営改善 Pマーク 問題解決技法 サイバー攻撃 エンジニア 参加してみた エンゲージメントマネジメント 労働関連法 新人育成 ネットワーク構築 情報セキュリティマネジメント デザインパターン リファクタリング マルチスレッドプログラミング ベンダーコントロール Modern JavaScript 冗長化 VLAN インフラエンジニア チームビルディング テストケース リーダブルコード セキュリティ入門 ネットワーク入門 Node.js npm gulp ビルドツール Python入門 冗長化入門 インフラ実機演習 プロジェクト管理 Active Directory ネットワーク管理 コンテナ 正規化理論 Haskell 品質管理 OpenStack シンギュラリティ DBA中級 プロトコル UX 基本設計 FinTech トラブルシューティング 並列処理 見える化 PMO ロジカルコミュニケーション Deep Learning インデックス設計 超上流工程 BGP Excel C-CENT Selenide プライベートクラウド アセンブラ コンピュータ基礎 工数見積 CCENT 法律知識 失敗から学ぶ プロジェクト失敗事例 PDCA プログラミング入門 非エンジニア向け 4Biz DNS セルフマネジメント 片付け術 サーバーダウン サーバー タイムマネジメント GO言語 プロダクトマネジメント プロダクトマネージャ LVS ロードバランサー 負荷分散 仮想通過 犯罪心理学 情報漏えい SEカレッジ導入事例 IT研修を聞いてみた 会社規模 (100~300名) IT研修制度を聞いてみた CentOS7 開発環境構築 数字力 財務 IT人材 UI Machine Learning Go言語 (golang) データマイニング 統計学 新人教育 やり直し数学 RDB つながる工場 モチベーション WebSocket WebWorker HTML5 CSS3 Bootstrap 微分・積分 システム設計 決断力 LAMP環境 トレ担 教育研修担当者向け ルーティング Linux入門 図解術 目標設定 試験対策 インタビュー技法 Vue.js ブロックチェーン 会社規模 (~50名) DHCP 仕掛け学 BSC 財務諸表 自己分析 RIP スタティックルート バッファオーバーフロー DoS攻撃 システム開発 会社規模 (~99名) Wireshark パケットキャプチャ 管理職研修 部下育成 ワークあり 文章力 情報システム部門向け プロジェクトリーダー プロジェクトマネージャ 塗り絵 リスク管理 法改定 会社の仕組み Chainer AI人材 会話術 会社規模 (~25名) テスト技法 組織規模 51名~99名 組織規模:~199名 [組織規模]199名まで 組織規模 199名まで 組織規模199名まで 会社規模199名まで 会社規模49名まで 会社規模99名まで アプリ開発 サーバサイドJava 営業知識 Cloud 栄養学 基本コマンド ウォーターフォールモデル ヘルスケア 論理設計 ニューラルネットワーク ハンズオン UML 顧客ヒアリング マウスで学ぶ Apache EC2 Lightsail M5Stack DevSecOps プロジェクト成果 画像認識 チャットポット コマンド レビュー 基本用語 自動構築 LPIC-1 サーバーサイドJavascript キャリア形成 ワークライフバランス インバスケット テック用語 GitHub Windows エディタ 教養 令和時代 RESTful API

インデックス設計を見直してみよう 研修コースに参加してみた

今回参加した研修コースは インデックス設計を見直してみよう です。

インターネットで “インデックス設計” でググると、「やたらインデックスだらけになってしまう」という記事が多く見受けられます。

このコースでは、インデックス設計をおさらいしながら、色々なインデックスの種類を解説いただき、最後に実際 EXPLAIN しながらパフォーマンスを計測し、インデックスが効くのはどういうときか体験しました。

なかなかインデックス設計だけを集中して学べる研修コースは無いので、あやふやなので深掘りしたい、という方にはオススメのコースです!

では、どんな内容だったのかレポートします!

 

想定している受講者

  • データベース管理に関する用語が理解できている

受講目標

  • 効果的なインデックスの設計および追加作成ができるようになる

講師紹介

データベース関連ではおなじみ 林 優子さん が登壇されました。いつものように、受講者の方が使っているRDBMSを調査してスタートです。

 

林 優子

インデックス設計のおさらい

まずはインデックスをサラサラと復習します。

 

 

  • SELECT * FROM orders WHERE ORD_ID=2 というとスグに見つかりますよね
  • SELECT * FROM orders WHERE CUST_ID=2 では、どうでしょう?
  • これが全件検索
  • インデックスというオブジェクトを追加して行アドレスを指定する
    • でも CUST_ID=20 と言われると結局、全件検索しますよね
    • それを避けるために BTree というアルゴリズムが使われている

 

BTreeのイメージ

  • テーブルのあるカラムに 1~100 のユニークな値が格納されているとする
  • 47 という値を探し出すのに全件検索せず、47は51より下、47は26より上となり、 26~50 を探せばよい

 

 

 

RDBMSによる違い

 

  • Oracleの場合
    • リーフではなく直接テーブルになっている
  • それ以外のRDBMSの場合
    • クラスタ索引と呼ばれる
    • INSERT するときが違う
    • クラスタ索引はデフォルトはプライマリーキーに紐づく
      • プライマリーキーではない値でもOK
    • クラスタ索引はテーブルあたり1つ
    • クラスタ索引はテーブルの値を小さい順に並べるので、INSERT時に並び替えの一手間が入る
    • ランダムな値が格納されている場合は、クラスタ索引を構成し直すので、時間がかかる
      • 夜間バッチで一括INSERTするときには、索引を削除してから処理することが多い

 

いろいろなインデックス

RDBMSによって採用されている索引の種類が違うのですが、主な索引の種類を紹介いただきました。

なお、このあたりのインデックスは create index しないとアプライされないので、注意が必要です。

 

ビットマップインデックス

 

  • AND で続く限り、索引でテーブルを作らない
  • 遅い。。ので索引検索は苦手
  • こういうときにビットマップインデックスが使える

 

  • リーフブロックごとに値があるかどうかを出している
    • レコードごとに 上場 / 非上場 で 0 or 1 で値を入れる
  • 複雑な値でも論理積 (AND) を取れば良いので速い
    • ORも早い
  • 値の種類が少ないときに有効

 

ハッシュインデックス

 

 

  • ハッシュは均等分配する箱とイメージすると良い
    • 3で割って 0 余る
    • 3で割って 1 余る
    • 3で割って 2 余る
  • WHERE ID=10 だと 1 余る箱を探せば良い

 

逆キー

  • 1013 1014 1015 1016 とインクリメンタルに値が格納されているテーブル
  • これを 3101 4101 5101 6101 と逆にして INSERT の待ちを発生させない

 

ナント。すごい発想です。。

演習する

ここからは実際にどういう検索ならインデックスが使われるのか、実際SQLを実行して試してみます。

 

準備

  • カラム構成

  • インデックスを作成


なお set autot traceonly とすると実行計画しか出ないので実行しましょう

 

色々実験してみる

林さんからSQL文を紹介してもらい、予想してから、実際に実行してみます。

 

case1


インデックスが実行されました。

 

case2


演算子 <> だと索引が使われない

101じゃないものというと全件検索しないとわからない

 

case3

  • LIKE '%S' 後方一致だと索引は使われない
  • 末尾s では全件検索しないとわからない

 

case4


IS_NULL は索引を使わないが、 IS NOT NULL だと索引を使うことがある

 

やってみると不思議なもので、どういう値の持ち方をしているとインデックスが効くのか、頭の理解と感覚が一致するようになってきます。

インデックス作成のポイント

 

  • ユニークな値が多い
    • ただし、テキスト検索は別
  • 大きな表とは
    • 基準が難しいので悩むよりつけましょう
    • ただし、 人の問題 で、作成すると取りにくくなる (後述)

演習

 

Q. この2つのSQLを索引を付けて早くしたい。どうするとよいか?

 

問題のSQL

試行錯誤してみる

  • 索引が使われない
    • WHERE last_name だと前方一致になるけど、 WHERE first_name だと後方一致になる
  • 複合索引を貼るときは値の種類が多い方を第一引数にしたほうがよい
  • なので WHERE first_name を早くしたければ、索引を新しく作る

 

試行錯誤2

  • とやっていくと、SQL毎にインデックスを作ってしまいがち。結果、バックアップに時間がかかる
    • バックアップのときに、インデックス毎にテーブルを作ってしまうため、容量が増える
  • でいっぱい作ったインデックスを削除しようとすると、すでに何らかで使われてしまっているため、そのSQLが遅くなってしまう
  • なのでインデックスを一度作ってしまうと削除するのが難しくなってしまう

 

実際にインデックスが増えてしまうことを体験して、このコースは終了しました。

まとめ

あやふやなまま使ってしまうことの多いインデックスについて、色々なインデックスの種類を知った上で、実際にSQLで実験し、どんなときに使えるのか、体感しました。

頭では分かっているつもりでも、実際予想しながらやることで、あ、これはアカンやつ、というのがわかるようになりました。

一方で、 SQLアンチパターン でも「インデックスショットガン」とも呼ばれる、インデックスが増えてしまう問題をやってみて、これまた判断が難しいことも分かりました。なかなか銀の弾丸は無いものなんですね。

インデックス設計を学びながら、悩ましいところも含め体験できるので、深掘りしたい方はもちろん、テーブル設計にも携わる方にもオススメのコースです!!

 

 
label SE カレッジの無料見学、資料請求などお問い合わせはこちらから!!


SEカレッジについて

label SEカレッジを詳しく知りたいという方はこちらから !!

特集・キャンペーン

よくある質問集

タグ一覧

参加してみた研修参加レポートJavaLinux基礎セキュリティプロジェクトマネジメント基本情報技術者プログラミングオブジェクト指向Pythonネットワークプログラミング基礎SQL基礎AI資格コミュニケーションヒューマンスキルIoTデータベースJavaScriptマネジメントテーブル設計エンジニアアルゴリズムソフトウェアテストSQL応用C# 基礎Linux入門プログラミング入門仮想化技術ファイアウォールパケット解析ビジネスマナービジネス文書jQueryLinux応用PMBOK®デザインパターンテック用語Dockerシェルスクリプトプレゼンテーション教育研修担当者向けコンテナデータサイエンスRDBMS障害対応LAN構築問題解決技法工数見積IT研修制度を聞いてみたRaspberry Pi単体テスト簿記トレンドJavaプログラミング基礎情報処理資格AWSER図SEカレッジ導入事例データベース基礎要求定義DNSサーバサイドJavaロジカルライティングWindowsサーバー基礎ネットワーク入門テスト自動化C# 応用プロジェクトマネージャロジカルシンキングデータベース設計アジャイルGoFコーチングPマークVLANExcelインフラエンジニアJavaプログラミング応用アンガーマネジメントデータサイエンティストベンダーコントロールインフラ実機演習プロトコルテストケースModern JavaScriptプロジェクト管理品質管理Active DirectoryCCNA試験対策ロジカルコミュニケーション財務リファクタリングヘルスケアWBSマルチスレッドプログラミング新人研修パフォーマンスチューニングデータベースセキュリティ新人育成Cloudフレームワークインデックス設計ZabbixITIL非エンジニア向け見える化法律知識LVS機械学習SQLインジェクションRDBスクラムOSPFIT基礎CentOS7ITパスポートPython入門HTML/CSS応用情報技術者JavaScript基礎営業知識VPNサーバー構築Vue.js片付け術サイバー攻撃犯罪心理学正規化理論UIデータベース障害物理設計(データベース)ネットワーク構築図解術管理職研修情報漏えい決断力HTML5ソフトウェアテスト基礎DHCPUI/UX会社規模99名まで経営改善Webアプリケーション開発CSS3TCP/IPエンゲージメントマネジメントセキュリティ入門プロジェクト失敗事例JavaEE統計学Deep LearningCentOSクラウド業務効率化DBA中級リスク管理マーケティングクリティカル・シンキングプロダクトマネージャLPIC-1ブロックチェーンOS・システム基盤UX情報セキュリティマネジメントWindowsサーバー応用会社の仕組み法改定基本コマンドLAMP環境冗長化ロードバランサー次世代高度IT人材データベース応用ウォーターフォールモデルBGPJunitCisco内定者研修関数型プログラミング要件定義セルフマネジメントルーティングPDCAトラブルシューティング微分・積分論理設計CCENTビジネスインダストリ表計算ソフトインターネットルーティング応用塗り絵開発環境構築栄養学インターネットルーティング基礎会社規模199名までMachine LearningMVC基礎サーバーサイドJavascriptサーバーIPアドレスITサービスマネジメントコマンドテスト手法PMO失敗から学ぶWeb APIWindowsUML財務諸表PHPHTTPプロジェクト計画リーダーシップシステム開発ネットワーク管理論理設計(データベース)HTTP/2.0自己分析GO言語OSI参照モデルインタビュー技法OJT会社規模49名までDevSecOps仮想通過IPAIT人材プロダクトマネジメントWebSocketC-CENT内部設計見積手法自動構築Node.js新人教育リスクコントロールMicrosoft Azure画像認識チームビルディング令和時代パケットキャプチャテスト技法並列処理Apacheシステム設計継続的インテグレーション (CI)スタティックルートモダンJS (Modern JavaScript) 基礎ニューラルネットワークWebWorkerSelenideサーバーダウンnpmやり直し数学チャットポットソフトウェアテスト応用RESTful APIHaskellスクリプト言語PDUEC2継続的インテグレーション(CI)GitHubバッファオーバーフローハンズオンプライベートクラウドgulpフロントエンド超上流工程目標設定4Bizリーダブルコードアプリ開発Go言語 (golang)PMP®LightsailDoS攻撃概念設計(データベース)基本設計インフラ監視アセンブラchefコンピュータ数学キャリア形成タイムマネジメントビルドツールChainer数字力つながる工場Infrastructure as Codeレビュー情報システム部門向けOpenStackデータマイニングM5Stackエディタ負荷分散共創FinTech顧客ヒアリングBootstrapコンピュータ基礎ワークライフバランスAI人材モチベーション労働関連法基本用語プロジェクトリーダーシンギュラリティ業務知識/業界知識ネゴシエーション教養WiresharkIT資格会話術財務会計マウスで学ぶインバスケットRIP冗長化入門Laravel

過去の記事