MySQLでの検索を高速化できるindexについて紹介

TOP
コラム
MySQLでの検索を高速化できるin

はじめに

データベースは顧客データや商品データ等を始め、AIやIoTに利用する膨大なビッグデータを、あらかじめ設計した方法に沿って整理した上で格納しておき、必要な時に必要なデータをすぐに取得できる便利な仕組みです。データベースは大きく階層型、ネットワーク型、リレーショナル型、NoSQLの4つに分類されますが、それぞれにはさらに様々な種類のデータベースが存在します。例えば、今回取り上げるMySQLや、PostgreSQL、Oracle Database、Microsoft SQL Server等、これらは全てリレーショナル型に属するデータベースです。

MySQLはオープンソースであり、無償での小規模〜大規模様々なシステムで利用できる程の機能を備えているため、特にコスト削減を意識しながら素早く開発を進めたいベンチャー企業やスタートアップ企業で利用されることが多いですが、日本だけではなく世界中でも幅広く利用されています。無償であるため、データベースの学習でも良く利用される傾向にあります。なお、MySQLは1995年に初めてリリースされましたが、その後の管理はサン・マイクロシステムズを経由してOracle社となり、2022年現在で30年弱の歴史を持つことになります。近年は、ネットワークやセキュリティ技術の向上により企業でも多く利用されるようになったクラウドサービスでもMySQLが採用されることが多く、MySQLの派生系となる「MariaDB」というデータベースも開発される等、今後も進化を続けながら活用の幅は広がっていく可能性が高いと言えます。

今回はそんなMySQLの代表的な機能である「index」に焦点を当てて、その概要や作成・確認・削除方法について紹介していきます。データベースやMySQLの学習が初めてという方に向けた内容となっていますが、MySQLを普段使っているもののiindexの活用方法がいまいちわからない、しばらくMySQLから遠ざかっていて使い方を忘れてしまったという方もぜひご覧ください。

MySQLでindexを利用する理由

indexを利用する理由は、データベースのテーブルに存在するカラムに含まれているレコードを検索しやすくするためです。もっと簡単に言うと、探したいデータを効率良く探すためです。漫画でも小説でも本の最初の方には、大抵の場合、目次ページが用意されています。漫画や小説であれば初めから順に読み進めるのでさほど目次の重要性は感じないかもしれませんが、参考書等では役立つことが多いです。例えば、苦手な項目を繰り返し勉強し直すために、目次を使って何度も参考書の特定のページを開きたいということがあることでしょう。何度も開いていれば感覚で目的のページに行き着けますが、それでもページ数が多い参考書であればある程、行き着くまでに多少なりとも無関係なページを経由し、ようやくたどり着くことがほとんどです。勘で目的のページを開くよりは、目次を活用した方が効率的と言えます。仕組みこそ異なりますが、MySQL等のデータベースで利用するindexも、まさにこの目次の役目を持っています。

indexがないデータベースは、該当のレコードにたどり着くまでに先頭の行から全て検索しようとします。レコードの少ないデータベースであればまだしも、ビッグデータのように大量のデータが格納されていたら、いくらコンピュータといえど時間を要します。先頭行から全て検索することをフルテーブルスキャンと呼びますが、100,000行あるテーブルだとすると100,000行をスキャンしなければなりません。これを例えば5,000行のスキャンに減らすことができるのがindexのメリットです。ただし、数ページの冊子に目次を付けると、ほとんど使われないであろう目次ページが増えるだけで効果がないのと同様、indexを利用しない方が検索がスムーズにいく場合もあるので注意が必要です。

上記で「目次ページが増える」という喩えを出しましたが、ページが増えると言うことはそれだけコストがかかります。同じくindexを作成すると、テーブルのデータとは別にindexのデータが保存されるため、それだけ容量が必要となります。そのため無駄なindexが少しずつ積もれば、MySQLが無償であるにもかかわらず、ディスク容量にプラスでコストが発生するという点がindexのデメリットとなります。また、検索関連の処理が早くなる一方で、元のデータが更新されるとindexの内容も更新されるため、レコードの変更や追加といった処理に時間がかかるようになるという面もあります。

早くなる秘訣は「B-tree(B+tree)」方式

indexを使うとデータを効率的に探すことができるようになるのは、MySQLが採用している「B-tree(B+tree)」に理由があります。なお、B+treeはB-treeを変形したもので、MySQLで使われているのは厳密に言うとB+treeの方になります。この方式はテーブルが膨大であればある程に有効となります。

このB-treeを細かく説明すると非常に長くなり、初心者の方は混乱しやすい内容となるのでこの記事では簡潔に述べますが、一つ一つのデータを直列でスキャンするのではなく、データベースをあらかじめ枝分れ構造にして、スキャン回数を減らす方法のことを指します。また、B+treeがB-treeと異なる点は、リーフノード同士がポインタで繋がっているところですが、恐らくリーフノードやポインタが何であるかがわからないことでしょう。今回はこの違いがもたらすそれぞれのメリットだけ紹介しますが、B-treeは途中で検索にヒットするものがあればそこで処理が終了するため、レスポンスが早くなる場合があります。対するB+treeは、範囲検索に強みを持っています。

indexの作成・確認・削除方法について

あらかじめ多用するSQL文の予測を立てた上でindexを作成する、実行計画を取得(クエリがどのように実行されるかを確認)するEXPLAINというコマンドがある、CREATE INDEX文、ALTER TABLE文(元々は既存テーブルの内容を変更する文)、

作成方法

indexを作成する際の基本的なコマンドは「ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名);」となります。「ALTER TABLE」文は、既存の内容を変更する際に利用しますが、index作成時にも利用可能ということを覚えておいて下さい。なお「CREATE INDEX」文でもindexの作成は可能で、その場合は「CREATE INDEX インデックス名 ON テーブル名 (カラム名)」と記述します。複数のカラムを指定したい場合は、「,(カンマ)」でカラム名を続けることができ、これを「複合インデックス」と呼びます。例えば「ALTER TABLE」文で「user」というテーブルの「id」というカラムに対して「id_index」というインデックスを作成したい場合は「ALTER TABLE user ALL INDEX id_index (id);」を実行します。「CREATE INDEX」文の場合は「CREATE INDEX id_index ON user(id);」となります。

以上のようにindexを作成した場合はカラムに重複した値が持てますが、重複した値を許可したくない場合はUNIQUEインデックスを作成する必要があります。UNIQUE インデックスを作成したい時は「ALTER TABLE テーブル名 ADD UNIQUE INDEX [インデックス名] (カラム名)」「CREATE UNIQUE INDEX インデックス名 ON テーブル名(カラム名);」を実行しましょう。これらを実行後に、実際に重複したデータを格納しようとすると「Duplicate entry '重複データ' for key 'インデックス名' 」というエラーが発生します。

確認方法

作成されたindexを確認したい場合は「SHOW INDEX FROM テーブル名;」を実行します。そのため「user」テーブルのindex情報を確認したい時は「SHOW INDEX FROM user¥G」となります。さらに、index作成後の効果測定を行いたい場合には「EXPLAIN」文も利用します。「EXPLAIN SELECT カラム名 FROM テーブル名 WHERE 条件;」を実行するとその結果が表示されます。ここでもカラム名を複数指定する場合は「,(カンマ)」で区切りますが、カラム全部について表示させたい場合は「*(ワイルドカード)」も有効となります。

実行結果には様々な項目が表示されますが、効果測定をするには「type」「possible_keys」「key」「rows」といった項目を確認することをおすすめします。「type」が「ALL」となっている場合は「フルテーブルスキャン」を表しますが、例えば「ref」のように「ALL」以外が表示されていれば何らかのindexが効いていると判断できます。「possible_keys」には、まだ実際には作られていないものの、作成される可能性のあるindex名が一つあるいは複数表示されます。「key」には実際に作成されているinexが表示されます。そして効果測定の際に特に注目しておきたいのが「rows」です。「rows」には、テーブルから参照されるおおよその行数が表示されるので、indexを作成する前より数が減っていれば効果があることを判断できるためです。

削除方法

削除する時は「DROP INDEX」を利用します。「ALTER TABLE」で削除する場合は「ALTER TABLE テーブル名 DROP INDEX インデックス名」を実行します。「ALTER TABLE」を使わなくても「DROP INDEX インデックス名 ON テーブル名」での削除も可能です。実際に削除されているか確認したい場合は「SHOW INDEX FROM テーブル名;」を実行してみましょう。

MySQLでindexを作成する際の注意点

indexを有効なものとするために注意点を4つ紹介しておきます。一つ目は、インデックス名にはインデックスとわかる名前を付けるという点です。MySQL含めデータベースにはたくさんの追加することになるので、混同しないように「idx」や「index」という文字列を含めた名前をつけましょう。

二つ目は、データの並び替えを行う可能性がある場合にもindexを利用するという点です。indexは検索だけではなく「ORDER BY」等でソートする場合にも有効であるためです。

三つ目はすでに紹介済みではありますが、何でもかんでもindexを作成しないという点です。例えば、主キーやユニーク制約がされているカラムは、主キー、ユニークインデックスで検索した方が早くなります。また、行数が少ないテーブルや、テーブル内のレコードの大部分を取得したい場合はindexがかえって逆効果となります。データの更新が発生するとindexの更新も同時に実施されて、処理速度が遅くなる可能性があることから、頻繁に表の更新・追加・削除が発生する場合も避けた方が良いでしょう。

そして最後の一つは、複合インデックスの場合はカーディナリティの高いカラムに対してindexを作成するべきという点です。「カーディナリティ」は聴き慣れない言葉かと思いますので、以下で解説します。

複合Indexでのカーディナリティについて

「カーディナリティ」とは選択肢の多さを表します。例えば学校のクラスで「A組」「B組」という2つのカラムしかない場合に検索する対象は2つです。対して生徒名をカラムにする場合は数十個以上のカラムが必要になります。この場合、後者の方がカーディナリティが高いということになります。低くても効果的と判断された時はindexが作成されるという例外もありますが、基本的にindexはカーディナリティがより高い方に対して作成されます。なお、一時的な選択肢の多さではなく、今後の運用も見越して判断することが求められます。

まとめ

MySQLのindexは、その仕組みを深く理解しようとするとそれだけで1冊の参考書ができてしまうのではというくらい内容の濃いものですが、index自体の作成・確認・削除操作は特に複雑なことをするわけではないため、今回紹介した基本的な内容を頭に入れただけでも、ある程度使いこなすことはできるでしょう。記事内でも説明していますが、せっかくindexを利用してシステムの処理速度を早くしようとしても、indexを作成し過ぎてかえって遅くなってしまっては元も子もありません。そのため、index作成時の注意点を頭に入れた上で、データベース全体の設計と同様、indexについてもあらかじめ十分な設計を行っておくことをおすすめします。今回はMySQLにおけるindexについてのみ紹介してきましたが、その他データベースにも同様の仕組みはあるため、様々なプロジェクトで活躍できるデータベースエンジニアを目指したい方は、頃合いを見計ってその他データベースのindexについても合わせて学習を進めてみましょう。