integration test / Export Database Data to SQL insert statement

寫 integration test 的時候，有時候會需要透過 SQL query 去抓取部分的 table ，然後再塞入 testing 資料庫裡頭。這件事，透過 MySQL Workbench 來做的話，它可以很快地 export result set as CSV file 。但是，變成了 CSV file 雖然可以透過 LOAD 指令來匯入，更一般的指令，應該還是 mysql insert 。

那要如何將 data CSV file 做成 mysql insert statement 呢？

(1) CodeBeautify 圖形化介面
(2) csvsql command line 指令

database and system design

最近要換工作了。換工作前，依然還沒有學完/學通公司的資深工程師 Mike 的 skills。只好先把他以前寫在 slack 的 comments 拷貝出來。

==================================================
1. 一張 table 的資料，一定只有一個順序存在「硬碟」中

2. Clustered Index，是一個用資料規則來決定資料在「硬碟中」的順序的方法

3. 當需要進行範圍查詢時，Clustered Index 可以利用到「硬碟」的循序存取效能

4. 不是有索引，資料庫的查詢最佳化就會使用，當資料量不多時，資料庫會直接使用 Full table scan

5. 就點查詢而言，WHERE 的條件在 Clustered Index 比 Secondary Index 還快，因為 Leaf Node 就是 Data Block

6. 資料庫效能是在「新增、修改、刪除」與「查詢」之間取捨 --- 註：前同事 Mike 對 relational database 的理解沒有錯。然而，在 Martin Kleppmann 的 Turning the database inside out with Apache Samza 這個 talk 裡，他探討了一種 system architecture，透過 event sourcing 與 CQRS ，可以達成不需要為了 Read 或是 Write 的效能而做出 trade-off。

7. Secondary Index Leaf 是 Clustered Index Key 是 MySql InnoDB 特有的實作方式

8. MySql 選擇了讓 Secondary Index 「新增、修改、刪除」容易，但查詢付出代價

9. MySql 是查詢最佳化很蠢的資料庫，意思要用很多索引，所以 InnoDB 實作 Secondary Index Leaf 到 Value of PK，在多個索引的情況下，PK 的順序不影響 2nd Index 的 Leaf Node 的值

10. 在那一篇引起戰文的為何用 MySql 取代 PostgreSql 的文章(UBer)中，關於索引的部份，他們在不斷更新資料的前提下，再加上有一堆索引，所以使用 MySql，更新的成本比 PostgreSql 低

11. 在 MVCC 的比較上，當 Transaction Log 與 Data Block 放同一個磁碟子系統時，
 假設 Read(50%) 與 Write(50%)，PostgreSql 可能比 MySql 好太多，因為減少了大量的 Random Access

=============
資料庫先求正確，再求效能，不正確的資料會拖累效能，甚至無法 Tuning，因為隨著時間，相依的程式碼太多。

資料庫資料的修正與程式的 Patch 不是同樣成本的事，讓資料嚴謹至少佔了系統品質的 60%。程式出錯，還有資料庫把關。

資料庫嚴謹的代價是效能，但它是可調效的，資料出錯，通常是前端先發現，然後要一層層檢查是哪裡出錯，是查詢有錯、還是新增修改有錯、還是前端有錯。甚至可能只有正式機才會有資料範例，此時就可能要在正式機上除錯。

==============
資料庫有兩本書就用了
The Art of SQL
Database: Principles, Programming, and Performance

SQL insert after join table

最近寫 SQL 的時候，遇到了有趣的「寫入」問題。問題如下：

有三張 table
table host 有 id, hostname
table grp   有 id, grp_name
table grp_host 有 grp_id, host_id => 這張表用來記錄 grp 和 host 之間的 relation

需求是：要寫入 grp_host 這張表，但是，原始資料的 grp 與 host 的 relation ，是用字串來記錄的，也就是 (string, string) 這樣子的 tuple。

總之，這個寫入的合理作法，要用一點小技巧：
建立 temporary table，然後 insert into ... select ，還有，要包在一個 transaction 裡頭！

H2 Database

最近在用 clojure luminus framework 來開發，因為 framework 範例的 default database 是 H2 Database ，所以我就來用看看。 畢竟資料庫很多，我也該沒事多試看看非 Sqlite, MySQL 之外的 RDBMS 選項。

開始用了之後，就發現 java 的東西還真的有它很不錯的一些地方。比方說，H2 資料庫的 jar 檔並不大，約 2 MB ，也不用什麼安裝，下載下來就可以用了。使用五分鐘之後，立刻就可以發現的優點就是： 儘管 jar 不大，卻還是同時附上了 console 與 web 介面。這樣子算是很有親和力的資料庫了。

(*) 下載
       http://repo2.maven.org/maven2/com/h2database/h2/

(*) 啟動 h2 server 的指令
      java -cp h2*.jar org.h2.tools.Server
       或是
      java -cp h2*.jar org.h2.tools.Server -webAllowOthers
   
(*) 觀察所有可以用的指令
      java -cp h2*.jar org.h2.tools.Server -?
     
(*) 啟動 h2 shell 環境的指令
      java -cp h2*.jar org.h2.tools.Shell

(*) SQL 指令範例，讀入 TAB delimited 文字檔

db patch

公司的系統一直在增加功能，資料庫也會不斷地更改 schema 。然而，已經存在生產環境 (production environment) 中的資料庫，卻不能直接套用新的 schema ，而是要用 patch 的方式，將舊的 schema 改成新的 schema 。於是開發的工作就會有一項，是要比較新舊的 schema 來寫出 transformation script 。

很幸運的是，這個似乎已經是前人研究過的問題了。有現成的工具可以使用。

(1) 安裝 mysqldiff

      $ sudo apt-get install mysql-utilities

   
(2) 如果因為遇到一些奇奇怪怪 python library 的問題導致裝不起來時，也可以用考慮使用 docker 來迴避安裝的困難。

      $ docker pull samfulton/mysql-utilities

      $ docker run -ti samfulton/mysql-utilities

(3) 使用的實例1：比較兩張資料表 

   $ mysqldiff --server1=root:password@10.20.30.40 \

   boss.contacts:coss.contacts \

   --difftype=sql -v

(4)使用的實例2：比較兩個完整的資料庫，且遇到錯誤不停止，繼續比較。

   $ mysqldiff --server1=root:password@10.20.30.40 \

   boss:coss \  

   --difftype=sql -v --force

[SQL] 對一張 table 的 multiple rows 做更新

公司的源碼裡，有一段程式碼，被公司的資深工程師挑出來說需要重構。本來的程式碼做的事情是： 「對一張 table 的 multiple rows 做更新的動作」。

原始的寫法如下：
1 用 ORM 將整張 table 讀入記憶體，每一 row 恰好對應一個物件。
2 跑迴圈，對物件做檢查，如果合乎條件，則做更新。

上述的寫法在資料量少的時候沒有影響，然而，在資料量大的時候，效能就會極差。因為多做了將整張 table 讀入記憶體的動作。比較好的重構版如下：

1    將要寫入 table 的資料，先寫入一張 temporary table。
例如：
CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT * FROM table1)

2.1 開啟 transaction
2.2 基於 temporary table 的值，用 join 操作來更新目的地的 table
例如：

UPDATE TABLE1
       JOIN TABLE2
       ON TABLE1.SUBST_ID = TABLE2.SERIAL_ID
SET    TABLE2.BRANCH_ID = TABLE1.CREATED_ID;

2.3 關閉 transaction

3  丟棄 temporary table      

新的寫法，是將要用來寫入 table 的值先寫入資料庫裡，再透過 SQL 的指令去做資料的更新。如此，大量減少了記憶體與資料庫之間的資料搬移。對於數據量大的情況，就會有效能的大幅改進。

註：新的寫法中，其實可以不用加上 transaction ，因為只有一個 update 的操作。然而考慮實務上的程式，常常會有超過一個 update 的操作。當兩個 update 操作有必要緊接著完成，不可以在中間被其它的 session 插入讀取的動作，就會需要 transaction 。

物件關係阻抗不匹配(object-relation impedance) vs 快速開發的選項 ORM 或是 mongodb

物件關系阻抗不匹配是指：記憶體中的資料結構，往往是多維度的、巢狀的，和關聯式資料庫中的二維表格，其實是不同的。程式設計人員總是要花費不少時間，才能將記憶體中的資料結構轉換成資料庫中的資料結構。

現代許多流行的程式語言( ruby, python 等)都有提供「物件關系對應」 ORM( object-relational mapping )，主要是用來簡化程式開發人員處理「物件關系阻抗不匹配」問題。然而， ORM 麻煩的地方在於，批評者指出，它是一個 反面模式( anti-pattern )。是反面模式的理由主要是因為 ORM 違反了物件導向程式設計的重要原則：「封裝」。它沒有沒有完整地將 SQL 的細節隱藏在物件中，導致了程式變得難以測試。 批評者則是認為，應該要用 SQL-speaking object 來取代 ORM。

這個用 SQL-speaking object 來取代 ORM 的作法，固然是相當成熟、穩健的作法，因為有完整地封裝 SQL ，妥善地處理 object-relation impedance 問題。問題是，其實勢必還是有許多使用者之所以想用 ORM 最根本的理由，是懶得寫這麼多程式碼！ 太麻煩了，因為要快速開發的話，根本一開始連需求都還沒有完全想好。

既然 ORM 有難以測試的問題的話，那還是使用 mongodb 吧。當然，等程式發展到一段時間之後，還是有可能會需要對資料庫做重新設計，但想要快速開發、想要跳過處理 object-relation impedance 的苦工，也只有 ORM 或是乾脆不要使用 RDBMS 這兩大類的解法了。

Use the index, Luke

use-the-index-luke 是 SQL performance 的教學網站。 內容滿深入淺出的。我一開始是為了要理解 clustered index 和 primary key 有什麼關系，而查到這個網站。想不到立刻就看到這個網站上的一篇文章，談論「 MySQL 的預設值將 primary key 設定為 clustered index 這是不盡理想的設計」，文章的大意是：

1. 要使用 primary key 的話，建議要使用 non-clustered primary key。不要用 MySQL 的預設設置的 clustered primary key 。否則會得到 clustered index penalty 。
2. 效能的重點在 index-only scan 

由於網站上的文章也相當多，我讀了兩三篇之後，改變心意，用速成的方式來學好了。於是我做了網站上的習題。結果，五題裡頭，我還真的只會兩題，就是有讀過網站上文章所以才會寫兩題。 題目很有啟發性，下方就是其中的一題，題目是不好的 SQL 語句，要能夠看出效能的瓶頸才算通過。

SQL schema 設定 primary key 的 constraint

最近做的工作，我在產品的資料庫，新增了一張 table 。在 code review 時，被同事建議「要設定 primary key」。於是，我就順便研究了設定 primary key 的重要性及理由。

主要的原因如下：有設定 primary key 的 column ，其值必定是唯一的。目前設計的這個 table，它的 business logic 裡，有一個 column 它的值也是唯一存在的，不會有重複的值。既然 business logic 就隱含了 unique value 的概念，加上 Primary key 的 constraint 自然可以「讓錯誤看得出來是錯誤」。

而好的 Primary Key 該如何設定呢？
Good primary keys are essential to good database design. They let you query and modify each table row individually without changing other rows in the same table. When you evaluate candidates for a table's primary key, follow these rules:

• The primary key should consist of one column whenever possible.
• The name should mean the same 5 years from now as it does today.
• The data value should be non-null and remain constant over time.
• The data type should be either an integer or a short, fixed-width character.
• If you're using a character data type, the primary key should exclude differential capitalization, spaces, and special characters, which might be difficult to remember.