Q15. [個人技能題組][Backend] 功能：商品訂購，生意太好。

[wildwindjen]

生意太好，訂單來到 50 萬筆。

a. 請想辦法做資料到 50 萬筆以上。
b. 效能如何？如果你的效能好，你是哪邊做得好？如果你的效能不好，原因在哪？你怎麼解決？
c. 請提供驗收項目清單。

答題時間: 16 hr

[dustfantasy]

「於2017/04/16 開始答題」

「2017/04/17，因為一些工作延誤時間，預計 2017/04/17 答題完成」
a.
寫程式迴圈輸入資料

b.
資料庫的效能對於有沒有建立索引有很大的差異，使用索引的查詢比起全表查找快上許多，在mysql中使用auto_increment會自動建立索引，而我的查找條件通常以這欄位為主，對於有做unique,primary key的欄位也會建立索引。
另外也要注意其他會造成全表查找的因素，像是欄位有null的值存在的話也會造成全表查找。

建立索引跟維護需要時間，數據量一大時間也會增多。
對於資料的新增、修改、刪除的處理成本增加，因為這些動作也需要動態更新索引。另外索引需要佔據物理空間，因此索引並不是越多越好。

c.
A.確認經常被用來查找的欄位是否建立索引
show index from tablename

B.確認用來查找的欄位預設為NOT NULL

在有建立索引的情況下檢索
SELECT * FROM orders where id = 2 0.00041 sec
SELECT * FROM orders where id = 50000 0.00036 sec
SELECT * FROM orders where id = 90000 0.00035 sec

去除索引後
SELECT * FROM orders where id = 2 0.047 sec
SELECT * FROM orders where id = 50000 0.040 sec
SELECT * FROM orders where id = 90000 0.056 sec

這是有null值的情況
SELECT * FROM orders where id = 2 0.00031 sec
SELECT * FROM orders where id = 50000 0.00035 sec
SELECT * FROM orders where id = 90000 0.00047 sec

有索引　　　無索引
0.00041：　0.047　　比率為百分之一

結果有索引查詢的用時明顯較少，速度與沒有建立時相比有100倍的差距，用於改善查詢效率是有效的。
然而在索引中有null值的結果跟索引差不多，與網路上說的不符合，此情形應該不會引起full table scan才對，這應該是建議盡量避免使用null所造成的誤導。

「於 2017/04/17 答題結束」

[wildwindjen]

@dustfantasy
全表查找 => full table scan
既然建索引這麼好用，那是不是所有的欄位都乾脆建索引好了？有沒有什麼成本？

關於 c 的回答
想辦法用數據量化的方式，來驗證你的改善是有效的。

[dustfantasy]

@wildwindjen 已修改

[YenChunchen]

「於2017/04/26 開始答題」
a. 請想辦法做資料到 50 萬筆以上。

• 使用迴圈批次(batch insert&insert delayed into...)插入3萬筆資料*20次,單次約97s

b. 效能如何？如果你的效能好，你是哪邊做得好？如果你的效能不好，原因在哪？你怎麼解決？
SQL有建索引跟沒建索引在查詢效能上差異很大,縮小查詢範圍,省去了全表查詢的時間,但因對每筆資料作變更後索引本身會自動更新,會讓資料變更操作變慢;索引也會佔用額外硬體空間,故並不是欄位索引越多越好,如該欄位包含了許多重覆資料,則該欄位建索引效果不大
因當初建立程式本身id欄位設為主鍵(自動增長),故SQL自動新建主/唯一索引;外鍵也新建了外鍵索引,故相較未建索引省去了全表查詢的時間,
c. 請提供驗收項目清單。

• 驗證索引對查詢效能影響

id欄位主索引
select * from orderlist where id =186820 66ms
select * from orderlist where id =48682 73ms
select * from orderlist where id =500000 87ms

未加索引
select * from orderlist where id =186820 308ms
select * from orderlist where id =48682 291ms
select * from orderlist where id =500000 296ms

*以上相較有添加索引在查詢效率上較好

[PenguinRun]

「於2017/04/27 開始答題」
a. 請想辦法做資料到 50 萬筆以上。
參考資料：How do I do a bulk insert in mySQL using node.js
一次生成10萬筆，耗費344.291ms。
若五次50萬筆則總耗費：1721.455ms，換算成秒數則為：1.721455秒。約2秒不到生成完畢。

b. 效能如何？如果你的效能好，你是哪邊做得好？如果你的效能不好，原因在哪？你怎麼解決?

目前orderList的資料欄位有：

1. OrderID
2. CustomerID
3. ProductID
4. OrderQuantity
5. OrderPrice
6. OrderEmail
7. OrderDate
8. IsComplete
9. UpdateDate

目前索引設置為：

• 叢集索引：OrderID, CustomerID, ProductID，為唯一值辨識。
• 非叢集索引：OrderPrice, OrderDate。

效能如何？
若單純針對OrderID, CustomerID, ProductID來進行查詢，其效能並無太大差異，因為本身就已經是索引。
至於若是將OrderDate及OrderDate增加索引，是有增加一點點點點效能。（可看C部份的測試）

小結
所以目前推測其實有無加索引目前還看不太出來效能有很顯著的增加，推估與用到的query語法有關，還沒複雜到有無使用索引就有提昇效能的影響。

===

設立索引準則：
假設重用性高(常使用它來當搜尋條件)、資料重複率低及需要透過它來縮短範圍查詢。都可以考慮建立索引。

索引優缺點：
適量的索引可會在執行CRUD更有效率，但過量的索引會在執行CRUD更有負擔。
這是因為當有CRUD的情況發生時，SQL server會同步處理相關索引中的數據，所以過多的索引會影響到處理效率。

c. 請提供驗收項目清單。

驗收「測試效能」
未增加OrderData索引：

• 使用query = 「select * from orderList where DATE(OrderDate) = '2017-04-20';」
  • 獲得結果：3 rows in set (0.18 sec)
• 使用query = 「select * from orderList where DATE(OrderDate) = '2017-04-21';」
  • 獲得結果：6 rows in set (0.20 sec)
• 使用query = 「select * from orderList where DATE(OrderDate) = '2017-04-27';」
  • 獲得結果：492611 rows in set (0.55 sec)

增加OrderData索引：

• 使用query = 「select * from orderList where DATE(OrderDate) = '2017-04-20';」
  • 獲得結果： 3 rows in set (0.16 sec)
• 使用query = 「select * from orderList where DATE(OrderDate) = '2017-04-21';」
  • 獲得結果： 6 rows in set (0.18 sec)
• 使用query = 「select * from orderList where DATE(OrderDate) = '2017-04-27';」
  • 獲得結果：492611 rows in set (0.55 sec)

未增加OrderPrice索引：

• 使用query = 「 select * from orderList where OrderPrice >= 50 and OrderPrice <=1000;」
  • 獲得結果：492617 rows in set (0.54 sec)
• 使用query = 「 select * from orderList where OrderPrice >= 50 and OrderPrice <=90;」
  • 獲得結果：3 rows in set (0.21 sec)

增加OrderPrice索引：

• 使用query = 「 select * from orderList where OrderPrice >= 50 and OrderPrice <=1000;」
  • 獲得結果：492617 rows in set (0.52 sec)
• 使用query = 「 select * from orderList where OrderPrice >= 50 and OrderPrice <=90;」
  • 獲得結果：3 rows in set (0.00 sec)

小結

• 並無顯著影響，但的確比未設來的好。
• 但唯一效能最有感的還是在A部份，有沒有遵從這規則來產生假資料，效能影響特別大。
  • 前10萬筆是用直接將整個值insert進去的方式存進去，約莫快20分鐘才跑完。但若是遵從，如同A部份所提到50萬筆就能在2秒內生成完成。

「於2017/04/27 答題結束」

[wildwindjen]

@PenguinRun 也許可以跟另外兩個討論看看為什麼會有結論上的差異。是測試手法哪邊不一樣？哪一方的結論才是對的？

[wildwindjen]

對了，改善前，改善後數據都出來的話，多做一個動作：改善的比率。對於只關心結論的人(ex: 業主，老闆)，比較有說服力。

[PenguinRun]

@wildwindjen
討論結果：
他們是針對「叢集索引」也就是primary key欄位來做有索引及沒有索引後來做測試。
而我是針對每個特定欄位來做「非叢集索引」來做測試。

還有測試的單位不同
@dustfantasy 是sec有到小數點第六位。
@YenChunchen 是用ms作為單位。
我是sec小數點只到第二位。

若是說以「索引是否能有效提昇效能」這研究目的來進行測試，他們的實驗結果會比較有顯著差異。
但若以該議題來看測試結果，我們的結論都是：「有+索引，對於資料庫的搜尋效率來說，是有幫助的。」

[wildwindjen]

@PenguinRun 討論歸討論啊！你們怎麼證明你們的「討論」是對的？是真的因為「叢集 vs 非叢集」還是因為其他變因，請設計實驗方法來驗證。

[HoHow]

「於2017/05/24 開始答題」
a. 請想辦法做資料到 50 萬筆以上。
利用迴圈來插入資料
b. 效能如何？如果你的效能好，你是哪邊做得好？如果你的效能不好，原因在哪？你怎麼解決？
一開始就是有使用索引的查詢，很明顯地比沒索引的效能差蠻多的。
索引的數量也不是越多越好，而是建立合理的索引數量。
資料表的結構也會影響效能
在多個索引中，索引的順序也是很重要的，索引是按照最左邊開始進行排序的，所以順序亂排也是會影響查詢的結果的。
c. 請提供驗收項目清單。
有索引的搜尋
select * from members where id = '1000';
結果： rows in set (20.361 ms)
select * from members where id = '100000';
結果： rows in set (1147.359 ms)
無索引搜尋
select * from members where id = '1000';
結果： rows in set (45.012 ms)
select * from members where id = '100000';
結果： rows in set (2981.898 ms)

「於2017/05/24 答題結束」