MySQL InnoDBのインデックスについて調べたことをまとめます。
インデックスの内部構造と分類
InnoDBのインデックスは、B＋Treeで実装されています。主に以下の2種類のインデックスがあります。
- 主キーインデックス
- 主キーカラムで構成されるインデックス、クラスタインデックス(Cluster Index)とも呼ばれます。インデックスノートには、レコードのデータそのものが保存されています。
- 非主キーインデックス
- 主キー以外のカラムで構成されるインデックス、セカンダリインデックス(Secondary Index)とも呼ばれます。インデックスノートには主キーカラムの値が保存されています。
以下のテーブルを例として作成して、インデックスの構造を説明します。
mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index(k)) engine=InnoDB;
insert into T values(100,1,'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff');
テーブルTにR1-R5の5つのレコードがあって、(id,k)の値が(100,1)、(200,2)、(300,3)、(500,5)、(600,6)である場合、インデックスの構造は、下図の通りになる。
SQL文[select * from T where id = 500]を実行する場合、idのインデックスTreeを一回検索したらいいですが、
SQL文[select * from T where k = 5]を実行する場合、まず、kのインデックスTreeを検索して、idの値500を取得します。次にidのインデックスTreeを検索します。
上記のように、非主キーインデックスを利用して検索する場合、主キーインデックスを利用することより、1回多くインデックスツリーを検索するため、業務開発では、できるだけ主キーインデックスによる検索を利用すべきです。
インデックスのメンテナンス
B+Treeの順序性を保つために、新しいレコードが挿入された場合、B+Treeを更新しなければなりません。
上図の例では、新規レコードのidが700であれば、R5レコードの後に新しいレコードを追加することで済みますが、新規レコードのidの値が400であれば、R5以後のレコードを移動しなけばなりません。さらに運が悪ければ、R5レコードが保存されるデータ頁がいっぱいになった場合、新らしいデータ頁を割り当て、一部のレコードを新しいデータ頁に移動する必要があります。これは、頁分裂といい、性能に影響します。
性能を影響すること以外、頁分裂が発生した場合、データを保存するスペースも大きくなります。もともと1データ頁で保存されるレコードは2頁で保存することになり、スペース利用率が50％下がります。
上記の理由より、MySQLでは、テーブルにAUTO_INCREMENTキーを指定することを推奨します。AUTO_INCREMENTキーを指定した場合、新しいレコードの挿入は、追加のみとなり、他のレコードの移動や、頁分裂を起こしません。また、AUTO_INCREMENTキーの保存に、intの場合、4バイト、bigintの場合、8バイトのスペースが必要で、一般的な業務カラムより利用するスペースが少ない、インデックスTreeを保存するスペースも少なくなるメリットがあります。
インデックスのスペース利用率が悪くなると、インデックスを再作成する必要があります。
非主キーインデックスの再作成には、以下のSQL文を利用できます。
alter table T drop index k;
alter table T add index(k);
主キーインデックスの再作成は、上記SQL文を利用してはいけません。主キーインデックスをdropすると、非主キーインデックスが参照する主キーは全部無効になり、内部でRowIDを利用してテーブルとすべてのインデックスを再構築することになり、非効率的です。
主キーインデックスの再作成は以下のSQL文で行います。
alter table T engine=InnoDB;
カバリングインデックス
テーブルTにSQL文（select id from T wher k between 3 and 5）を実行すると、kのインデックスTreeを検索することのみで該当するidの値を取得できます。この場合、kのインデックスは、カバリングインデックス(Covering Index)といいます。カバリングインデックスを活用することで、主キーのインデックスTreeを検索することが避けられ、性能チューニングでよく使われます。
例として次のテーブルを作成します。
mysql> create table `tuser`(
`id` int(11) not null,
`id_card` varchar(32) default null,
`name` varchar(32) default null,
`age` int(11) default null,
`ismale` tinyint(1) default null,
primary key(`id`),
key `id_card` (`id_card`),
key `name_age` (`name`, `age`)
)ENGINE=InnoDB;
id_cardをもとにtuserの情報を取得するために、id_cardのインデックスがあれば十分ですが、id_cardからnameを取得する業務要件があって、かつ、実行回数が非常に多い場合、(id_card,name)のカバリングインデックスを作成することで、レコード全体を取得することはなくなり、SQL文実行時間が短縮できます。もちろん、インデックスを追加することで、メンテナンスのコストがかかります。業務要件を含めて設計すべきです。
インデックスの左プリフィクス
複数カラムで構成されるインデックスのノートは、インデックスを定義するときに指定したカラムの並び順でソートされます。
例として、tuserテーブルの(name,age)インデックスを下図で説明します。
上図で示したように、氏名が'田中二'であるレコードを検索する場合、(name,age)のインデックスTreeを検索すれば、すぐにID2のレコードを特定できます。その後のインデックスノートをスキャンすることで、条件に満たすレコードをすべて特定できます。また、SQL文の条件が"where name like '田中%'"であっても、(name,age)のインデックスを利用できます。
つまり、検索条件にあるカラムがインデックスの全カラムではなくても、インデックスの左プリフィクスに一致すれば、該当インデックスを利用できます。インデックスの左のN個のカラムのみならず、文字列カラムの先頭のＮ文字でも検索条件に指定すれば該当インデックスを利用できます。
インデックスの左プリフィクスを利用できるため、テーブルに(a,b)のインデックスがあれば、(a)のインデックスを作成する必要がありません。ただ、検索条件にbのみがある場合、(a,b)のインデックスを利用できない、(b)のインデックスを作成する必要がります。
業務要件として、カラムaとカラムbを両方利用する検索条件と個別にa、bを検索する条件がある場合、作成するインデックスの候補は、以下の２つがあります。
- (a,b)のインデックスと(b)のインデックス
- (b,a)のインデックスと(a)のインデックス
この場合、サイズの小さいカラムに個別のインデックスを作成します。上記tuserの例では、nameとageに個別に検索する要件がある場合、nameより、ageにインデックスを追加作成すべきです。
ユニックインデックスとChange Buffer
主キー以外のインデックスを定義するとき、キーワード「UNIQUE」をつけると、ユニックインデックスになります。ユニックインデックスであれば、当該カラムにおいて重複した値を設定できません（NULLは除く）。ユニックインデックスの更新性能が良くないため、業務要件が許す場合、カラムの値が重複しなくても、ユニックインデックスを作成しないほうがいいです。理由は、以下の通りです。
MySQLでは、メモリのBuffer PoolにChange Bufferという領域を割りあっています。更新系のSQL文を実行するときに、該当データ頁がメモリに存在しなければ、Change Bufferのみを更新しで、SQL文の実行が完了とします。バックグランドのプロセスがchange Bufferとディスクの同期を行います。ただし、更新後すぐに当該レコードの検索処理を実行する場合、データ頁をメモリに読み込んで、Change Bufferの内容で更新します。
カラムにユニックインデックスを指定すると、カラムを更新、あるいは、新規作成するときに、該当のデータ頁をディスクから読み込まないと、ユニックか判定できませんので、Change Bufferが利用できません。ディスクからデータ頁の読込みは、もちろん性能に影響します。
上記説明より、業務処理がカラムのユニックを保証でき、かつ、更新後すぐに当該レコードを検索する要件がなければ、カラムにユニックインデックスを作成しないことを推奨します。