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

Avatar for Rack Lin (阿土伯)

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