08 June 2007

隨服務的資料越來越多,有些 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


回響

可以用 Tag <I>、<B>,程式碼請用 <PRE>