x. インデックス演習2024

インデックスについて

インデックスはテーブルのフィールドに管理用の見出し（目次）を作成してデータの検索効率などを改善する仕組みのこと。

テーブルのフィールドに対してインデックスを設定するとそのフィールドに対する検索時間を短縮することができる。

インデックスはDBMSがテーブルのレコードにアクセスする際に内部的に利用される。SQLでインデックスの内部的な値を操作することはできない。

データベースには大量のデータを効率よく処理するためにデータ検索を高速で行う仕組みが必要である。

インデックスによるデータ検索は高速化されたデータ探索アルゴリズムで実行される。

その一方でインデックスを設定したフィールドの値の修正やレコードの追加や削除においてインデックスを内部的に修正するコストが発生する。

インデックスはデータベースの利用状況に応じて適切に設定し、不要なインデックスは設定しないように注意する。

インデックス設定の方針

• データを追記するだけのテーブルにはインデックスは不要

• データの検索や並べ替えに利用しないフィールドについてインデックスは不要

• 主キーはインデックスであるのでインデックスの設定は不要

• 多くのレコードを持つテーブルで主に参照するフィールドにインデックスを設定する

データ探索アルゴリズムについて

解説ページを用意した。確認しておくこと。

SQLを速くする！インデックス入門

データベース用の検索アルゴリズムの丁寧な解説記事

インデックスとは何？MySQL(InnoDB)とPostgreSQLのインデックスの違いとは？調べてみました

演習

ここの演習は「x. ビュー演習2022」を完了してから取り組む必要がある。

準備：

• • 1. スタート メニュー → xampp → xampp control

    2. Apache Webサーバと MySQL データベースサーバ を start ボタンで起動する。

    3. SQL タブからコマンド入力フォームを表示して以下のSQLを入力して実行ボタンを押す。
       番号は自分の学籍番号に変更すること。

       1. use db3120888

※phpMyAdminでは無効なコマンドなので以下の画像のようにマウスでクリックしてデータベースを選択

手順：

インデックスの追加

• • 1. create index STUDENT_INDEX on STUDENT ( ID )

STUDENT表のIDフィールドに index を設定することでIDを利用したデータベースの処理効率（検索、データ追加、削除、修正）が改善される。

※効果の確認： 数万件のデータ登録後にテーブルを操作するとパフォーマンス向上を体感できるかもしれない。

48万件の単語データによるインデックスの効果検証実験を行う。

準備

レポートフォルダから単語ファイルをコピーする

レポートフォルダ　→　小橋一秀　→　データベース

ファイル naist-jdic.sql

を以下のフォルダ

T:ドライブ \xampp\mysql\bin

 にコピーする

（参考）ダウンロード元　NAIST Japanese Dictionary https://ja.osdn.net/projects/naist-jdic/releases/53500

naist-jdic.csv には以下の様に日本語の単語と品詞の分類と読み方が記載されています。
これを演習用に加工してnaist-jdic.sqlを作成しました。（文字コードEUC→UTF8変換。フィールドの削除。SQL化。フィールド名の設定）

XAMPP ControlのSHELLからMySQLサーバに登録したユーザIDで接続してコマンドを実行して演習を進める。

1. SHELLボタンを押す

2. 次のコマンドを入力してEnter キーを押す。以下、コマンドはEnterキーで実行する

3. cd mysql\bin

4. 次のコマンドでコマンドプロンプトの文字コードをShift_JISからUTF8に切替える

5. chcp 65001

6. 次のコマンドは使用するデータベース名の番号を自分の学籍番号に修正して実行する
   　※-proot の pとrootの間に空白を入れてはいけない
   　※パスワードがかかっていない場合は、赤文字-prootの部分は入力しない

7. mysql -uroot -proot db3120888 < naist_jdic.sql

8. mysql -uroot -proot

9. use db3120888

10. select count(*) from naist_jdic;

ここまで実行すると、データベースに48万件のレコードを持つnaist_jdicテーブルを準備できる。

インデックス有りと無しの状態でSQLの実行時間を比較する。

1. select count(*) from naist_jdic;

2. select * from naist_jdic where tango like '名古屋文%';

3. select * from naist_jdic where char_length(tango) = 5 order by tango limit 10;

4. ここでインデックスをtangoフィールドに設定する

5. ALTER TABLE naist_jdic ADD INDEX(tango);

6. select count(*) from naist_jdic;

7. select * from naist_jdic where tango like '名古屋文%';

8. select * from naist_jdic where char_length(tango) = 5 order by tango limit 10;

課題提出

コマンドプロンプトのスクリーンショットを撮る。

SQLコマンドの実行時間の改善が分かる部分を記録することが条件。上掲の画像を参照。

提出先はWebclass参照。

1. データベースサーバからログアウトする

2. exit