Upgrade to Pro — share decks privately, control downloads, hide ads and more …

改善 Programmer 生活的 SQL 技巧

改善 Programmer 生活的 SQL 技巧

改善 Programmer 生活的SQL 技巧

REF:
http://sqlfiddle.com/#!2/fbf31/5/0

http://wiki.postgresql.org/wiki/Foreign_data_wrappers

DEMO DB
Host ec2-107-20-224-218.compute-1.amazonaws.com
Database dbkrqa4hb2ckgl
User qzacwszmuccqrc
Port 5432
Password SByaWWQCEiCYqwNAfl8iwCmqPa

Rack Lin (阿土伯)

July 01, 2013
Tweet

More Decks by Rack Lin (阿土伯)

Other Decks in Technology

Transcript

  1. about:me   •  ViViPOS  Co.,  Ltd    技術總監   – 

    利用 JavaScript  寫 〞收銀機〞(傳統產業,全新感 受)   •  PHP  /  JavaScript  LiveScript  /  Java  (目前移情於 Scala  )     •  [email protected]   •  hKps://twiKer.com/racklin   •  hKp://www.plurk.com/racklin   •  hKps://www.facebook.com/racklin1002  
  2. 但是   • Programmer  如何與            DBA

     真心相愛   • 苦命的 PG  通常兼 DBA  
  3. 你了解 ORM  做了什麼嗎?   •  Rails   •  Java  

    – Hibernate   – Grails   •  PHP   – Lavara   – Phalcon   – CakePHP  
  4. 你的 DB  只做 CRUD ?   •  CREATE  TABLE  

    INSERT  INTO   SELECT   DELETE  FROM      
  5. ORM  迷思   •  ORM  開發者企圖簡化了使用者直接操作 SQL  ,  但不會產出 “較美觀”SQL

     .   – hasAndBelongsToMany  (CAKEPHP  SourceCode)   •  SELECT  a  from  A  where  a  IN(  …………..  )  產出超長 SQL  。   •  上線一陣子後就死的不明不白。   •  ORM  開發者企圖讓您在各個資料庫平台中 移植你的程式,所以不會產出 “最佳 化”SQL.      
  6. 關於 MySQL   •  曾經我也是用 MySQL  ,直到我膝蓋中了一 箭。    

    – SELECT  *  FROM  users  WHERE  username=‘rack’   AND  paswd  =  0;     – hKp://sqlfiddle.com/#!2/pf31/5/0  
  7. Type  handling   •  了解 Framework  如何處理變數   – DB  Column

     Type  or  Input  Data  Type  ?     – bindParam  with  custom  query   •  SELECT  a.user  as  aa  ,  a.passwd  as  bb  WHERE  aa  =  ?  AND     bb  =  ?   •  Resqul  Services   •  XML   •  JSON  
  8. Schema   •  Linux  CHROOT  ,  FreeBSD  jail  .  

      •  Per  User  Schema   –  永遠不要信任:   •  使用者   •  新手開發者   •  Per  App  Schema   –  正式環境中的 Staging   •  Schema  And  search_path  .  
  9. Schema  in  VIVIPOS   •  SELECT  COUNT(schema_name)  as  K  FROM

      informaton_schema.schemata;   –   372  schema   – 為難搞的客戶和新手鎖在建立獨立的 Schema    
  10. Big  Data   •  你的  Big  Data   –  百萬筆級

      •  別人的 Small  Data     –  VIVIPOS  單一客戶      -­‐  (ec2  large  +  500G  EBS)   •  1000        transactons/day   •  1500        clients     •  4  *365                    years   =  2,190,000,000        (21億筆)   •  我的 Big  Data,  金融業的 Small  Data   –  一天幾百萬上下  
  11. PostgreSQL  特殊功能   •  Partton  Table   •  Partal  Index

      •  DBLink   – Connect  Remote  DB  Server  as  View   •  Data  Wrappers   – MySQL  /  Oracle  /  Redis  /  Mongo hKp://wiki.postgresql.org/wiki/Foreign_data_wrappers  
  12. Database  View   •  Fine-­‐grained  Views   – Users  Table  

    •  Username,password,email,cellphones…..   – Views   •  user_auths    (username,  password)   •  User_profiles  (不含 password)     •  Mapping  Model  to  VIEW   •  降低 Mass  Assignment  帶來的風險.    
  13. Updatable  View   •  View  是唯讀的   – Model  Mapping  to

     View 也是唯讀的.     •  MySQL  5.5  後支援   •  PostgreSQL   – USE  RULE   CREATE  RULE  user_login_update  AS    ON  UPDATE  TO  user_login      DO  INSTEAD            UPDATE  users  SET  password  =  new.password              WHERE  users.account  =  new.account;    
  14. Database  View   •  Simplify  Model  associatons   –  User

      •  belongsTo  Compony   –  Compony   •  hasMany  User   •  belongsTo  Region   –  Region   •  hasMany  Machine   •  Get  User  Machines  Lists  ?   –  N+1  Query  ?     –  Prepare  in  Controller  ?  
  15. Database  View   •  建立 user_machines  VIEW      

      •  Mapping  UserMachine  to  ‘user_machines’   VIEW   CREATE  View  user_machines  AS  (   SELECT  u.id  as  user_id,  m.*  FROM  users  u   INNER  JOIN  user_componies  ….   INNER  JOIN  compony_regions  ….   LEFT  JOIN  region_machines  ……   )    
  16. Complex  SQL  and  Joins   •  需要多個 Sub-­‐Query    

    •  需要 Temporary  Table  儲存運算過程中的資 料,並重覆引用   •  SQL  複雜到爸媽都認不得      
  17. Common  Table  Expression   •  在單⼀一  SELECT、INSERT、UPDATE、DELETE   或  CREATE

     VIEW  陳述式之執⾏行範圍內定義 的暫存結果集。CTE  與衍⽣生資料表類似的地 ⽅方在於,它不會儲存為物件,⽽而且其⽣生命 週期僅限於查詢期間.   – 建⽴立遞迴查詢   – 您不需要在中繼資料中儲存定義   – 在相同陳述式中多次參考結果資料表  
  18. 天不從人願   •  貨幣匯率表   Create  Table  exgRate  (  

         exgRateId  serial  primary  key,  -­‐-­‐  主鍵        factTime  tmestamp,                       -­‐-­‐  匯入時間        currency  char(3),                            -­‐-­‐  貨幣別        purpose  varchar(2),                       -­‐-­‐  匯率種類        currType  varchar(10),                   -­‐-­‐  貨幣種類        rate  numeric(18,6)                         -­‐-­‐  匯率   );  
  19. Data  Mining  ?   •  RD  找 PM  抱怨  

    •  PM  找客戶抱怨   •  客戶向天抱怨   – 為什麼 Data  Mining  變成 Data  Money  $$$  
  20. PIVOT  /  Crosstab   •  一般稱為「樞紐分析表」,PostgreSQL  為 「交叉表」   • 

    SELECT  *  FROM  crosstab('...')  AS  ct(row_name   text,  category_1  text,  category_2  text);  
  21. Trigger   •  觸發程序是⼀一種在資料庫伺服器發⽣生事件 時,會⾃自動執⾏行的特殊預存程序   – Logging     • 

    Logging  in  controller  !?   – Backup  data   – Calling  Stored  Procedure  from  Triggers