TOP＞書籍連動＞ インデックスを使った方がよいとは限らない

PostgreSQLチューニング実践テクニック 第４回：インデックスの活用によるチューニング 著者：石井達夫（ISHII, Tatsuo）   2005/6/8

前のページ  1  2

インデックスを使った方がよいとは限らない

この例では、目的のノードに辿り着くまでに「ルートノード→インデックスノード→リーフノード→テーブル本体」の計4回のアクセスが必要でした。実際にはノード中にもっと大量のノードが詰め込まれており、整数型のデータであればたとえ100万件以上のデータであっても2、3回のアクセスで到達できます。インデックスがない場合はテーブル本体を頭から順に探索するほかないので、平均50万行を読み出すことになり、比較にならないほど時間がかかります。    ただし、インデックスを使ったアクセスのほうが常に速いというわけではありません。テーブルの大半のデータを検索結果として利用する場合は、データ1件あたり最低2回以上アクセスが必要なインデックスは不利になります。また、データ件数が少ない場合はテーブル本体に1回か2回アクセスすればすべてのデータが読み取れるので、インデックスを使わない方が効率的です。

文字列データとインデックス

基本的にB-treeインデックスは次のように、値が完全に一致するものを探す目的で設計されています。

SELECT * FROM t1 WHERE textcol = 'abc';

しかし、文字列の検索においてはそれだけでは不便なので、前方一致検索といって、最初の何文字かが一致するものを検索できるようになっています。たとえば、次の検索は検索パターンを用いた前方一致検索を行っています。

SELECT * FROM t1 WHERE textcol LIKE 'abc%'; SELECT * FROM t1 WHERE textcol~'^abc';

こうした検索が多いケースではインデックスが有効に利用できます。

ORDER BY、GROUP BYとインデックス

B-treeインデックスは検索だけではなく、ORDER BYを使って検索結果を並び替える際にも利用できます。これは、前述のようにB-treeインデックスのリーフノードには昇順にデータが並んでおり、その結果を直接利用できるからです。したがって、仮に以下の問い合わせでintcolにB-treeインデックスが貼ってあれば、インデックスが使われる可能性があります。この場合、ソートが不要なので非常に高速に処理されます。

SELECT * FROM t1 WHERE textcol LIKE 'abc%' ORDER BY intcol;

また、B-treeインデックスのリーフノードには降順のリンクも設定されているので、次のように降順に並び替える場合もインデックスが使われます。

SELECT * FROM t1 WHERE textcol LIKE 'abc%' ORDER BY intcol DESC;

なお、GROUP BYでも内部的にソートが必要になるので、場合によってはB-treeインデックスが使われることもあります。

インデックスの適用基準

以上をまとめると、ある列に対してインデックスの作成を検討すべきケースは次のようになります。

WHERE句において検索条件になっている。ただし、文字列型において、前方一致検索でない場合を除く ORDER BYの対象になっている GROUP BYの対象になっている 重複が比較的少ない 抽出結果が比較的少ない

なお、主キーやユニーク制約のある列には自動的にインデックスが貼られるので、あらためてインデックスを作成する必要はありません。

前のページ  1  2

著者プロフィール 石井達夫（ISHII, Tatsuo） PostgreSQLの開発者、エバンジェリスト。本業でもPostgreSQLによるビジネスに関わっている。著書に「PostgreSQL完全攻略ガイド」「PHPxPostgreSQLで作る最強Webシステム」(技術評論社)などがある。

INDEX 第4回：インデックスの活用によるチューニング   インデックスの活用 インデックスを使った方がよいとは限らない

PostgreSQLチューニング実践テクニック 第1回 postgresql.confによるチューニング（1） 第2回 postgresql.confによるチューニング（2） 第3回 VACUUMの活用によるチューニング 第4回 インデックスの活用によるチューニング 第5回 高度なインデックスの活用 第6回 統計情報の最適化 第7回 オプティマイザに関するチューニング 第8回 GROUP BYを使用したチューニング