隨服務的資料越來越多，有些 sql 會開始變慢，除了重新調整 sql 語法之外，漸漸地一些欄位要開始加 index 以維持一定的效能。這裡收集一些跟 index 相關的筆記
建立 Index 的語法
create index index_mytable_mycol on mytable(mycol)
上述語法替 mytable 上的 mycol 欄位建立 index (沒有設 type 一般都是 BTree)。雖然 PostgreSQL 也提供 Hash 的 index，但效率很差，建議不要使用。而index_mytable_mycol 則是 index 的名稱。postgresql 不像 oracle 的名稱有 30 個字的限制，設定起來比較自由。
刪除 Index 的語法
drop index index_mytable_mycol
查詢系統已經建立的 index
基本上就是查 db 的 meta table。每次建立 index 之前先 check 一下，不然建立重複的 index 很傷的。
Oracle的做法: (取代 'MySchema' 為欲查詢的 schema)
select * from sys.all_ind_columns where table_owner = 'MySchema' order by index_name
PostgreSQL 的做法:
select a.schemaname, a.tablename, a.indexname, b.column_name
from pg_catalog.pg_indexes a,
information_schema.columns b
where a.tablename = b.table_name
and a.schemaname = b.table_schema
and a.schemaname = 'public'
and ( a.indexdef like '%(' || b.column_name || '%'
or a.indexdef like '%, ' || b.column_name || '%'
or a.indexdef like '%("' || b.column_name || '%'
or a.indexdef like '%, "' || b.column_name || '%' )
order by a.tablename, length(b.column_name)
上述指令列出該資料庫中 public schema 的所有 index。Postgresql 我在 where 處做了一點 hack，有可能無法查出所有的 index 相關的欄位。你可以用下面的指令對照：
select * from pg_catalog.pg_indexes where schemaname = 'public'
查詢還沒做 index 的 foreign key
foreign key 沒做 index 時，join 會變慢 (尤其是主 table 資料很大時)。下面的指令可以查出還有哪些 foreign key 還沒加 index:
oracle: (取代 'MySchema' 為欲查詢的 schema)
select a.table_name,
a.constraint_name,
a.column_name,
c.index_name
from all_cons_columns a,
all_constraints b,
all_ind_columns c
where a.owner = 'MySchema'
and b.constraint_type = 'R'
and a.owner = b.owner
and a.table_name = b.table_name
and a.constraint_name = b.constraint_name
and a.owner = c.table_owner (+)
and a.table_name = c.table_name (+)
and a.column_name = c.column_name (+)
--如果只想看未加上 index 的 FK，移除掉下面的 comment
--and c.index_name is null
Postgresql: (先連線到欲查詢的 database)
select fkeys.table_name,
fkeys.constraint_name,
fkeys.column_name,
ind_cols.indexname
from (
select a.constraint_schema,
a.table_name,
a.constraint_name,
a.column_name
from information_schema.key_column_usage a,
information_schema.referential_constraints b
where a.constraint_name = b.constraint_name
and a.constraint_schema = b.constraint_schema
and a.constraint_schema = 'public'
) fkeys
left join
( select a.schemaname, a.tablename, a.indexname, b.column_name
from pg_catalog.pg_indexes a,
information_schema.columns b
where a.tablename = b.table_name
and a.schemaname = b.table_schema and a.schemaname = 'public'
and ( a.indexdef like '%(' || b.column_name || '%'
or a.indexdef like '%, ' || b.column_name || '%'
or a.indexdef like '%("' || b.column_name || '%'
or a.indexdef like '%, "' || b.column_name || '%' )
) ind_cols on ( fkeys.constraint_schema = ind_cols.schemaname
and fkeys.table_name = ind_cols.tablename
and fkeys.column_name = ind_cols.column_name )
-- 如果只想看未加上 index 的 FK，移除掉下面的 comment
-- where ind_cols.indexname is null
下面是 PostgreSQL 執行的結果範例：
table_name | constraint_name | column_name | indexname -----------------+-------------------------+-----------------+------------------------ order_item | fk_orderitem_shareorder | share_order_id | index_item_order post_it | fk_postit_group | group_id | uk_post_it_group product | fk_product_shop | shop_id | index_product_shop shop_revision | fk_revision_shop | shop_id | share_order | fk_shareorder_account | account_id | share_order | fk_shareorder_formula | formula_id | share_order | fk_shareorder_group | group_id | index_shareorder_group
並沒有什麼規定說 FK 一定要加 index，一切還是依實際的狀況調整。我在 Postgresql 上做一些實驗，大概筆數到 5000 以上，index 的有無對 join FK 有明顯的改善。另外，在 postgresql 裡，delete 資料時如果含有 FK 時，index 也會有巨大的影響，如下例：
Person Address
------------ --------------
id id
name desc
person_id (FK)
delete from Address; -- 如果 Person 很大的話，這一行會變的爆慢。
在 postgresql 裡，每 delete 一筆 Address，內部都會再對 Person table 做一次 select 以確認 integrity。所以即使 delete 的 sql 沒有任何的 where，還是會受到 FK 的影響。因此如果發現 delete 忽然變得很慢，先試試看加 FK index 有沒有幫助。
hibernate hbm 的設定
hbm 上面可以直接寫 index="foo"。例如：
<many-to-one name="project" column="project_id" index="index_user_project"/>
用了上述寫法後，若使用 hbm2ddl 之類的工具建立資料庫時，便會產生對應的 create index 指令。但是！這只有在第一次建立全新的資料庫時有效，後續使用 hbm2ddl 工具進行 update 時，hibernate 不會幫你產生 index，(unique key 也是這樣)。
自動建立的 index
primary key 和 unique constraints 都會自動建立 index (unique index)所以不用額外再建立了，例如在 hibernate hbm 裡：
//pk 會自動建立
<id name="id">
<generator class="sequence">
....
</generator>
</id>
//unique="true" 會自動建立 unique constraint (db 會順便建立 unique index)
<many-to-one name="project" column="project_id" unique="true"/>
參考資料：SQL Cookbook