資料庫索引數據結構及主鍵設計(b+tree)(part 1)

Slide 1

Slide 1 text

資料庫索引數據結構及主鍵設計 (B+Tree) Database index data structure and primary key design (part1) 2016 Ant yftzeng@gmail.com

Slide 2

Slide 2 text

2016 2/129 自我介紹 ♪ Muzik Online (www.muzikair.com) 總工程師。 ♪ Paganini+ (http://www.paganiniplus.com/) 技術長。 ♪ BELP (fb.com/belp.general) 共同創辦人。 ♪ CHROOT (www.chroot.org) 成員。 ♪ 講師經驗 PHPConf, Modern Web Taiwan, MOPCON, WebConf, COSCUP, JSDC Taiwan, DrupalCamp Taiwan, OSDC, HITCON, ... ♪ 略懂智慧財產權、資訊安全及軟體程式設計。 ♪ 『略懂諸葛』。 ♪ 沉浸於『人性』、『法律』與『科技』的三角關係中。

Slide 3

Slide 3 text

2016 3/129 問題 1: UUID ? Q: 資料庫 Primary key 使用 UUID 比較快？ Serial/Auto-Increment 1 2 3 4 5 ... UUID 3a5e439b-eeb4-4074-8b23-51c2cdd35421 1bb19e6b-9c0c-475e-a7b3-f01a6b4cd496 af4c8b80-4bf5-4bf3-a4e3-a71bed920f10 e62acb5b-a393-4707-9aa5-90f79352eb4b 2e199d72-4e49-4ace-a504-419da63878f5 ...

Slide 4

Slide 4 text

4/129 2016 問題 1: UUID ? Q: 資料庫 Primary key 使用 UUID 比較快？偽命題

Slide 5

Slide 5 text

2016 5/129 問題 1 ( 修正 ): MySQL UUID ? Q: MySQL 的 Primary key 使用 UUID 比較快？ Serial/Auto-Increment 1 2 3 4 5 ... UUID 3a5e439b-eeb4-4074-8b23-51c2cdd35421 1bb19e6b-9c0c-475e-a7b3-f01a6b4cd496 af4c8b80-4bf5-4bf3-a4e3-a71bed920f10 e62acb5b-a393-4707-9aa5-90f79352eb4b 2e199d72-4e49-4ace-a504-419da63878f5 ...

Slide 6

Slide 6 text

6/129 2016 問題 1 ( 修正 ): MySQL UUID ? Q: MySQL 的 Primary key 使用 UUID 比較快？偽命題

Slide 7

Slide 7 text

7/129 2016 問題 1 ( 修正 ): MySQL UUID ? UUID 目前公定有五種版本： ➀ Version 1 (MAC address & date-time) ➁ Version 2 (DCE Security) ➂ Version 3 (MD5 hash & namespace) ➃ Version 4 (Random) ➄ Version 5 (SHA-1 hash & namespace) Ref: https://en.wikipedia.org/wiki/Universally_unique_identifier

Slide 8

Slide 8 text

8/129 2016 問題 1 ( 修正 ): MySQL UUID ? UUID 目前公定有五種版本： ➀ Version 1 (MAC address & date-time) ➁ Version 2 (DCE Security) ➂ Version 3 (MD5 hash & namespace) ➃ Version 4 (Random) ➄ Version 5 (SHA-1 hash & namespace) Ref: https://en.wikipedia.org/wiki/Universally_unique_identifier Version 2 未在 UUID 規格中定義，少見

Slide 9

Slide 9 text

9/129 2016 問題 1 ( 修正 ): MySQL UUID ? UUID 目前公定有五種版本： ➀ Version 1 (MAC address & date-time) ➁ Version 2 (DCE Security) ➂ Version 3 (MD5 hash & namespace) ➃ Version 4 (Random) ➄ Version 5 (SHA-1 hash & namespace) Ref: https://en.wikipedia.org/wiki/Universally_unique_identifier

Slide 10

Slide 10 text

10/129 2016 問題 1 ( 修正 ): MySQL UUID ? UUID 類循序式 ( 通常後序產生的值 > 先前產生的值 ) ： ➀ Version 1 (MAC address & date-time) UUID 亂序式 ( 值的大小沒有前後關係 ) ： ➂ Version 3 (MD5 hash & namespace) ➃ Version 4 (Random) ➄ Version 5 (SHA-1 hash & namespace)

Slide 11

Slide 11 text

11/129 2016 問題 1 ( 修正 ): MySQL UUID ? UUIDv1 的範例： 2190718a-c7a3-61e2-aa34-024281ed9db5 2191f33e-c7a3-61e2-9b92-024281ed9db5 2192cc0a-c7a3-61e2-be4c-024281ed9db5 21939950-c7a3-61e2-9aad-024281ed9db5 21945ff2-c7a3-61e2-90fc-024281ed9db5 219525f4-c7a3-61e2-99df-024281ed9db5 Time

Slide 12

Slide 12 text

12/129 2016 問題 1 ( 修正 ): MySQL UUID ? UUIDv4 的範例： 3a5e439b-eeb4-4074-8b23-51c2cdd35421 1bb19e6b-9c0c-475e-a7b3-f01a6b4cd496 af4c8b80-4bf5-4bf3-a4e3-a71bed920f10 e62acb5b-a393-4707-9aa5-90f79352eb4b 2e199d72-4e49-4ace-a504-419da63878f5 8cf2e49f-8ae9-43f2-a84d-78cb323114a3 Time

Slide 13

Slide 13 text

13/129 2016 問題 1 ( 修正 ): MySQL UUID ? 結論： UUID 可以是類循序式，也可以是亂序式。但《循序式》與《亂序式》的重要性為何？

Slide 14

Slide 14 text

14/129 2016 問題 1 ( 修正 ): MySQL UUID ? Ref: https://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/

Slide 15

Slide 15 text

15/129 2016 問題 1 ( 修正 ): MySQL UUID ? 延伸 : GUID 與 UUID 的差異？

Slide 16

Slide 16 text

16/129 2016 問題 1 ( 修正 ): MySQL UUID ? GUID 是 Microsoft 用詞，指的其實類似於 UUID ，但實作方式不一樣。 Microsoft 對於 GUID 的實作至少有兩種： ➀ 基於 UuidCreate 的 NEWID(SQL) ； ➁ 基於 UuidCreateSequential 的 NEWSEQUENTIALID(SQL) 。其中 NEWID 是亂序式，而 NEWSEQUENTIALID 是循序式。

Slide 17

Slide 17 text

2016 17/129 索引數據結構

Slide 18

Slide 18 text

18/129 2016 索引數據結構 : 無索引目的：尋找【欄位一為 5 的資料】操作： 5 ， O(n) 缺點：總是需要全表掃描資料表： 1 user1 pass1 2 user2 pass2 3 user3 pass3 4 user4 pass4 5 user5 pass5

Slide 19

Slide 19 text

19/129 2016 索引數據結構 : B-tree 索引 B-tree = Balanced tree (not Binary tree)

Slide 20

Slide 20 text

20/129 2016 索引數據結構 : B-tree 索引目的：尋找【欄位一為 5 的資料】操作： 4 ， O(logb n) 缺點：全表掃描需要遍歷樹資料表： 1 user1 pass1 2 user2 pass2 3 user3 pass3 4 user4 pass4 5 user5 pass5 8 3 6 1 2 4 5 7

Slide 21

Slide 21 text

21/129 2016 索引數據結構 : B+tree 索引目的：尋找【欄位一為 5 的資料】優點：全表掃描不需遍歷樹資料表： 1 user1 pass1 2 user2 pass2 3 user3 pass3 4 user4 pass4 5 user5 pass5 13 4 7 1 2 3 4 5 6 7

Slide 22

Slide 22 text

22/129 2016 索引數據結構 : B+tree 索引目的：尋找【欄位一為 5 的資料】操作： 4 ， O(logb n) 優點：全表掃描不需遍歷樹資料表： 1 user1 pass1 2 user2 pass2 3 user3 pass3 4 user4 pass4 5 user5 pass5 13 4 7 1 2 3 4 5 6 7 13 b

Slide 23

Slide 23 text

2016 23/129 問題 2: B+tree Q: 哪些資料庫索引數據結構支援 B+tree ？ MySQL Oracle Microsoft SQL Server PostgreSQL MongoDB CouchDB LevelDB RocksDB HBase Cassandra Riak NessDB BoltDB TokuDB Aerospike Couchbase

Slide 24

Slide 24 text

24/129 2016 問題 2: B+tree Q: 哪些資料庫索引數據結構支援 B+tree ？ MySQL Oracle Microsoft SQL Server PostgreSQL MongoDB CouchDB LevelDB RocksDB HBase Cassandra Riak NessDB BoltDB TokuDB Aerospike Couchbase

Slide 25

Slide 25 text

2016 25/129 索引頁分裂

Slide 26

Slide 26 text

26/129 2016 索引頁分裂觸發：頁剩餘空間 - 保留空間 < 新增資料問題：頁分裂時會 Latch( 小鎖 ) page page page latch page

Slide 27

Slide 27 text

27/129 2016 索引頁分裂 : 循序式新增資料操作：新增 25( 假設頁只能存三筆資料 ) 9 18 21 24 19 20 21 22 23 24 25 p1 p10 p12 p11 9 18 21 24 25 19 20 21 22 23 24 p1 p10 p12 p11 25 p13 latch latch hotspot

Slide 28

Slide 28 text

28/129 2016 索引頁分裂 : 亂序式新增資料操作：新增 5( 假設頁只能存三筆資料 ) 20 6 12 2 4 6 8 10 12 5 20 4 6 12 2 4 5 6 8 10 12 p1 p1 p2 p2 p4 p3 p3 p4 p5 latch fragmentation fragmentation

Slide 29

Slide 29 text

29/129 2016 索引頁分裂 : 亂序式新增資料 Ref: http://blogs.catapultsystems.com/rnewkirk/archive/2013/05/14/sql-fragmentation-explained/

Slide 30

Slide 30 text

30/129 2016 索引頁分裂 : I/O 與 Page Size 資料庫 I/O 以 Page 為單位。儲存引擎的 Page Size ： ➊ PostgreSQL 8KB ➋ MySQL/InnoDB 16KB(default) ➌ MongoDB/MMAPv1 dynamic (powered by 2) ➍ MongoDB/WiredTiger 32KB(default) ➎ Percona/TokuDB 64KB(default)

Slide 31

Slide 31 text

31/129 2016 索引頁分裂 : 循序式新增 I/O 9 18 21 24 25 19 20 21 22 23 24 25 p1 p10 p12 p11 25 p13 latch latch Block N+1 Block N+2 Block N+3 p11 p12 p13 p10 I/O hotspot? hotspot

Slide 32

Slide 32 text

32/129 2016 索引頁分裂 : 循序式新增 I/O 如果 Page Size 比較大？ Block N+1 Block N+2 Block N+3 p11 p12 p13 p10 I/O Block N+1 Block N+2 p13 p12 p11 p10 I/O I/O

Slide 33

Slide 33 text

33/129 2016 索引頁分裂 : 亂序式新增 I/O Block N+3 Block N+4 I/O p2 p3 p4 p1 # # # p5 20 6 12 2 4 6 8 10 12 20 4 6 12 2 4 5 6 8 10 12 p1 p1 p2 p2 p4 p3 p3 p4 p5 latch I/O 5

Slide 34

Slide 34 text

34/129 2016 索引頁分裂 : 亂序式新增 I/O 如果 Page Size 比較大？ Block N+4 p4 p3 p2 p1 I/O I/O Block N+3 Block N+4 I/O p2 p3 p4 p1 # # # p5 I/O # p5 I/O

Slide 35

Slide 35 text

2016 35/129 索引子頁結構

Slide 36

Slide 36 text

36/129 2016 索引子頁結構 : Non-clustered index 以 B+tree 為例特色：子頁不包括資料本身 ( 指針指向資料 ) 補充：對應的資料表稱 Heap table 資料表：循序式 1 user1 pass1 2 user2 pass2 3 user3 pass3 4 user4 pass4 5 user5 pass5 13 4 7 1 2 3 4 5 6 7

Slide 37

Slide 37 text

37/129 2016 索引子頁結構 : Non-clustered index 如果不是《循序式》而是《亂序式》。資料表：亂序式 1 user1 pass1 4 user4 pass4 5 user5 pass5 2 user2 pass2 3 user3 pass3 13 4 7 1 2 3 4 5 6 7

Slide 38

Slide 38 text

38/129 2016 索引子頁結構 : Clustered index 以 B+tree 為例特色：子頁包括資料本身 ( 不需指針指向資料 ) 補充：對應的資料表稱 Index Organized Table(IOT) 資料表：循序式與亂序式都一樣 13 4 7 1 2 3 4 5 6 7 1 user1 pass1 2 user2 pass2 3 user3 pass3 4 user4 pass4 5 user5 pass5

Slide 39

Slide 39 text

39/129 2016 Non-clustered index(Heap table) 循序式 ➀ 有索引指針指向資料的額外開銷。 ➁ Hotspot 發生在樹最右下的頁，但循序寫及合併寫機率高。 ➂ Range scan 讀取時開銷大。亂序式 ➀ 有索引指針指向資料的額外開銷。 ➁ Range scan 讀取時開銷可能比循序式更大。 ➂ 大量新增資料，幾乎是隨機寫，也難以合併寫。索引子頁結構 : Non-clustered index

Slide 40

Slide 40 text

40/129 2016 索引子頁結構 : Clustered index Clustered index(IOT) 循序式 ➀ 資料佔頁空間，頁存放的資料量少，頁分裂頻繁。 ➁ 搜尋至頁索引時，因包括資料本身，無額外指針開銷。 ➂ Hotspot 發生在樹最右下的頁，但循序寫及合併寫機率高。亂序式 ➀ 資料佔頁空間，頁存放的資料量少，頁分裂頻繁。 ➁ 搜尋至頁索引時，因包括資料本身，無額外指針開銷。 ➂ 為了維持循序，發生大量的頁分裂。

Slide 41

Slide 41 text

41/129 2016 索引子頁結構 : Clustered index Q: 循序式 / 亂序式 vs. I/O Scheduler vs. Filesystem vs. HDD / Flash / Fusion IO ？循序式亂序式 CFQ Deadline NOOP HDD Flash Fusion IO Ext4 XFS ZFS BtrFS

Slide 42

Slide 42 text

2016 42/129 Range scan

Slide 43

Slide 43 text

43/129 2016 Range scan >, <, >=, <= BETWEEN, IN GROUP BY with max, min, count

Slide 44

Slide 44 text

44/129 2016 Range scan: Heap table w/ 循序式 SELECT * FROM x WHERE y BETWEEN 1 AND 7; 1 user1 pass1 2 user2 pass2 3 user3 pass3 4 user4 pass4 5 user5 pass5 6 user6 pass6 7 user7 pass7 13 4 7 1 2 3 4 5 6 7 Block N+3 ... p1 p3 I/O p2 p1 p2 p3 p4 I/O Block N+M

Slide 45

Slide 45 text

45/129 2016 Range scan: Heap table w/ 亂序式 SELECT * FROM x WHERE y BETWEEN 1 AND 7; 1 user1 pass1 4 user4 pass4 5 user5 pass5 2 user2 pass2 3 user3 pass3 7 user7 pass7 6 user6 pass6 13 4 7 1 2 3 4 5 6 7 I/O p5 p7 p3 p6 I/O I/O I/O p1 # p2 p4 ... p4 p7 p5 p1 p3 p2 p6

Slide 46

Slide 46 text

46/129 2016 Range scan: Heap table w/ 亂序式 I/O p5 p7 p3 p6 I/O I/O I/O p1 # p2 p4 ... Ref: http://etutorials.org/SQL/Postgresql/Part+I+General+PostgreSQL+Use/Chapter+4.+Performance/Gathering+Performance+Information/

Slide 47

Slide 47 text

47/129 2016 Range scan: IOT w/ 循序式 & 亂序式 SELECT * FROM x WHERE y BETWEEN 1 AND 7; 13 4 7 1 2 3 4 5 6 7 1 user1 pass1 2 user2 pass2 3 user3 pass3 4 user4 pass4 5 user5 pass5 6 user6 pass6 7 user7 pass7 p1 p3 p2 Block N+3 ... I/O p1 p2 p3 p4 I/O Block N+M

Slide 48

Slide 48 text

2016 48/129 問題 3 Q: 哪些資料庫支援 Heap table ？哪些支援 IOT ？ MySQL Oracle Microsoft SQL Server PostgreSQL MongoDB CouchDB

Slide 49

Slide 49 text

49/129 2016 問題 3 Heap table IOT MySQL/MyISAM O X MySQL/InnoDB X O Oracle O O Microsoft SQL Server O O PostgreSQL O X MongoDB/MMAPv1 O X MongoDB/WiredTiger O X CouchDB X O

Slide 50

Slide 50 text

50/129 2016 問題 3 Q: 選什麼資料庫比較好？是個偽命題，需視你的場景而定。

Slide 51

Slide 51 text

2016 51/129 特別篇 1: MySQL 生態系 Q: MySQL 生態系， MySQL / Percona / MariaDB / WebScaleSQL 該怎麼選擇？

Slide 52

Slide 52 text

52/129 2016 特別篇 1: MySQL 生態系 MySQL ➀ 官方，有 Oracle 技術支持及專利保護。 ➁ 最新的 MySQL 5.7 擁有大量新特性。 ➂ 商業因素預設不會接受其他分支的特性。 Percona ➀ drop-in compatible with (official) MySQL 。 ➁ 針對 MySQL 額外新增許多特性。 ➂ 額外支援 XtraDB / TokuDB 等引擎。 ➃ 商業特性可接受其他所有分支的特性。

Slide 53

Slide 53 text

53/129 2016 特別篇 1: MySQL 生態系 MariaDB ➀ drop-in replacement for (official) MySQL 。 ➁ MariaDB 10 基於 MySQL 5.6 ，但不相容 5.7 。 ➂ 額外支援 XtraDB / TokuDB / Cassandra / CONNECT 等引擎。 ➃ 至今仍沒有穩定的自我開發儲存引擎。 XtraDB / TokuDB 都屬 Percona 公司。 WebScaleSQL ➀ 基於 5.6 ，但可收納 5.7 特性。 ➁ 專針對特大型 Web 公司需求而生，小心駕馭。

Slide 54

Slide 54 text

54/129 2016 特別篇 1: MySQL 生態系 MySQL 5.7 解決了 index→lock contention 問題。

Slide 55

Slide 55 text

2016 55/129 特別篇 2: PostgreSQL PostgreSQL 是 Object-relational database

Slide 56

Slide 56 text

56/129 2016 特別篇 2: PostgreSQL (Fusion IO) Fusion IO 可大幅排除 IO Bottlenect 的可能性。

Slide 57

Slide 57 text

57/129 2016 特別篇 2: PostgreSQL (Fusion IO) Fusion IO 只特別支援 Oracle / MySQL / Percona / MariaDB ？ Ref: https://www.sandisk.com/business/datacenter/resources/overviews/accelerate-mysql-open-source-databases

Slide 58

Slide 58 text

58/129 2016 特別篇 2: PostgreSQL (Fusion IO) Fusion IO 可讓 MySQL 免除 double write 。 Ref: https://www.sandisk.com/business/datacenter/resources/overviews/accelerate-mysql-open-source-databases

Slide 59

Slide 59 text

59/129 2016 特別篇 2: PostgreSQL (Fragmentation) PostgreSQL(HOT) 天性無法避免 fragmentation 。 Ref: http://etutorials.org/SQL/Postgresql/Part+I+General+PostgreSQL+Use/Chapter+4.+Performance/Gathering+Performance+Information/

Slide 60

Slide 60 text

60/129 2016 特別篇 2: PostgreSQL (Index Bloat) PostgreSQL(non HOT updates) 天性無法避免 index bloat 。 Ref: PostgreSQL 9.0 High Performance [PACKT] (2010) (p171)

Slide 61

Slide 61 text

61/129 2016 特別篇 2: PostgreSQL (Index Bloat)

Slide 62

Slide 62 text

62/129 2016 特別篇 2: PostgreSQL (Index Bloat)

Slide 63

Slide 63 text

63/129 2016 特別篇 2: PostgreSQL (Merge IO) PostgreSQL 沒有類似 MySQL 的 Change Buffer 。 Ref: http://www.slideshare.net/morgo/inno-db-presentation (p9)

Slide 64

Slide 64 text

64/129 2016 特別篇 2: PostgreSQL (Merge IO) MySQL Insert Buffering( 現在改名 Change Buffer) ： 1. Reducing the number of disk i/o operations by merging i/o requests to the same block. 2. Some random i/o operations can be sequential. Ref: http://www.percona.com/files/presentations/percona-live/london-2011/PLUK2011-linux-and-hw-optimizations-for-mysql.pdf (p17)

Slide 65

Slide 65 text

65/129 2016 特別篇 2: PostgreSQL (Merge IO)

Slide 66

Slide 66 text

66/129 2016 特別篇 2: PostgreSQL ( 儲存引擎 ) Microsoft SQL Server / Oracle 支援 Heap table 及 IOT ，可依各資料表的特性分別採用。 MySQL 及 MongoDB 支援可抽換儲存引擎，也可依資料表的特性分別採用。但 PostgreSQL 只有一個儲存引擎，也只支援 Heap table 。

Slide 67

Slide 67 text

2016 67/129 特別篇 3: MongoDB

Slide 68

Slide 68 text

68/129 2016 特別篇 3: MongoDB Ref: http://itindex.net/detail/43573

Slide 69

Slide 69 text

69/129 2016 特別篇 3: MongoDB Ref: http://sql-vs-nosql.blogspot.tw/2013/11/indexes-comparison-mongodb-vs-mssqlserver.html

Slide 70

Slide 70 text

70/129 2016 特別篇 3: MongoDB Ref: http://learnmongodbthehardway.com/schema/chapter4/ B+Tree / Heap Table

Slide 71

Slide 71 text

71/129 2016 特別篇 3: MongoDB Ref: Indexing and Performance Tuning (2015-11-03).pdf (p39)

Slide 72

Slide 72 text

72/129 2016 特別篇 3: MongoDB Index ➀ ” 統一命名為 _id” ➁ ” 新增若未指定 _id” 值，會使用 ObjectId ➂ ObjectId 為 12-byte BSON 型態 ➊ 4-byte 為 UNIX 紀元時間 ➋ 3-byte 為機器識別碼 ➌ 2-byte 為 Process ID ➍ 3-byte 為隨機值 Ref: https://docs.mongodb.org/manual/reference/object-id/ “_id” : ObjectId(“563479cc8a8a4246bd27d784”) “_id” : ObjectId(“563479d48a8a4246bd27d785”) “_id” : ObjectId(“563479df8a8a4246bd27d786”) 循序式 UUID

Slide 73

Slide 73 text

73/129 2016 特別篇 3: MongoDB MMAPv1 儲存引擎 ➀ Memory-mapped files Ref: http://learnmongodbthehardway.com/schema/chapter3/

Slide 74

Slide 74 text

74/129 2016 特別篇 3: MongoDB MMAPv1 儲存引擎 ➀ Memory-mapped files ➁ Padding Ref: http://learnmongodbthehardway.com/schema/chapter3/

Slide 75

Slide 75 text

75/129 2016 特別篇 3: MongoDB MMAPv1 儲存引擎 ➀ Memory-mapped files ➁ Padding ➂ Fragmentation Ref: http://learnmongodbthehardway.com/schema/chapter3/

Slide 76

Slide 76 text

76/129 2016 特別篇 3: MongoDB MMAPv1 儲存引擎 ➀ Memory-mapped files ➁ Padding ➂ Fragmentation ➃ Collection-level locking (WiredTiger 為 Document-level) Ref: http://sql-vs-nosql.blogspot.tw/2013/11/indexes-comparison-mongodb-vs-mssqlserver.html

Slide 77

Slide 77 text

77/129 2016 特別篇 3: MongoDB MMAPv1 儲存引擎 ➀ Memory-mapped files ➁ Padding ➂ Fragmentation ➃ Collection-level locking (WiredTiger 為 Document-level) ➄ MongoDB 3.2 以前為預設引擎， 3.2 後換 WiredTiger Ref: https://docs.mongodb.org/manual/core/wiredtiger/

Slide 78

Slide 78 text

78/129 2016 特別篇 3: MongoDB MMAPv1 的 Fragmentation 問題 Ref: http://www.slideshare.net/mongodb/7-managing-a-maturing-mongo-db-ecosystem-charity-majors (p35) 左邊是大量 Fragmentation ；右邊是修復 Fragmentation 後

Slide 79

Slide 79 text

79/129 2016 特別篇 3: MongoDB MMAPv1 的 Fragmentation 問題動作：連續新增 5 個 Document Collection ： {Document 1} {Document 2} {Document 3} {Document 4} {Document 5}

Slide 80

Slide 80 text

80/129 2016 特別篇 3: MongoDB MMAPv1 的 Fragmentation 問題動作：更新 Document 3 情況一：新的 Document 3 大小 <= 原先大小 Collection ： {Document 1} {Document 2} {Document 3} {Document 4} {Document 5} {Document 1} {Document 2} {Document 3‘} {Document 4} {Document 5}

Slide 81

Slide 81 text

81/129 2016 特別篇 3: MongoDB MMAPv1 的 Fragmentation 問題動作：更新 Document 3 情況二：新的 Document 3 大小 > 原先大小 Collection ： {Document 1} {Document 2} {Document 3} {Document 4} {Document 5} {Document 1} {Document 2} Free {Document 4} {Document 5} {Document 3‘}

Slide 82

Slide 82 text

82/129 2016 特別篇 3: MongoDB MMAPv1 的 Fragmentation 問題動作：新增 Document 6 Collection ： {Document 1} {Document 2} Free {Document 4} {Document 5} {Document 3‘}

Slide 83

Slide 83 text

83/129 2016 特別篇 3: MongoDB MMAPv1 的 Fragmentation 問題動作：新增 Document 6 情況一： Document 6 大小 <= 原先 Document 3 (Free) 大小 Collection ： {Document 1} {Document 2} {Document 6} {Document 4} {Document 5} {Document 3‘} {Document 1} {Document 2} Free {Document 4} {Document 5} {Document 3‘}

Slide 84

Slide 84 text

84/129 2016 特別篇 3: MongoDB MMAPv1 的 Fragmentation 問題動作：新增 Document 6 情況二： Document 6 大小 > 原先 Document 3 (Free) 大小 Collection ： {Document 1} {Document 2} Free {Document 4} {Document 5} {Document 3‘} {Document 6} {Document 1} {Document 2} Free {Document 4} {Document 5} {Document 3‘}

Slide 85

Slide 85 text

85/129 2016 特別篇 3: MongoDB 特色與改善 ➀ Sharding/Replica 較容易 ( 但 Sharding/Replica 只有 Primary 可寫 ) ➁ Dynamic schema ( 不是 Schemaless) ➂ 儲存引擎可抽換 ➊ MMAPv1 ➋ WiredTiger ➌ inMemory ➍ Devnull ➎ (RocksDB)

Slide 86

Slide 86 text

86/129 2016 特別篇 3: MongoDB 特色與改善 ➃ 寫入的 Locking level 改善 2.2 版前， Process-level lock ，一個 Mongod 實例一個鎖 2.8 版前， Database-level lock ，一個 DB 一個鎖 2.8 版之後， WiredTiger 提供 Document-level lock 3.0 版之後， MMAPv1 改為 Collection-level lock Ref: http://sql-vs-nosql.blogspot.tw/2013/11/indexes-comparison-mongodb-vs-mssqlserver.html

Slide 87

Slide 87 text

87/129 2016 特別篇 3: MongoDB 特色與改善 ➄ 支援 MapReduce 2.4 版前，使用 SpiderMonkey ，是 single threaded 2.4 版之後，改用 V8 engine ，改善問題 ➅ Single thread model ，大多時候只用到一顆 CPU (WiredTiger 已支援 Multiple CPUs)

Slide 88

Slide 88 text

2016 88/129 特別篇 4: RDMBS or NoSQL ？ Q: 使用 RDBMS 還是 NoSQL ？

Slide 89

Slide 89 text

89/129 2016 特別篇 4: RDMBS or NoSQL ？ Q: 使用 RDBMS 還是 NoSQL ？是個偽命題，需視你的場景而定。

Slide 90

Slide 90 text

90/129 2016 特別篇 4: RDMBS or NoSQL ？不過 RDBMS 及 NoSQL 的分界會愈來愈小。過去的一些 RDBMS 已開始支援 NoSQL 特性， Microsoft SQL Server / Oracle / MySQL / PostgreSQL 都確定支援 JSON(B) 操作。 Percona / MariaDB 可抽換為 TokuDB 引擎， MariaDB 更可抽換為 Cassandra 引擎。 NoSQL 等也開始支援強一致性。

Slide 91

Slide 91 text

91/129 2016 特別篇 4: RDMBS or NoSQL ？不過 RDBMS 及 NoSQL 的分界會愈來愈小。過去的一些 RDBMS 已開始支援 NoSQL 特性， Microsoft SQL Server / Oracle / MySQL / PostgreSQL 都確定支援 JSON(B) 操作。 Percona / MariaDB 可抽換為 TokuDB 引擎， MariaDB 更可抽換為 Cassandra 引擎。 NoSQL 等也開始支援強一致性。 NewSQL

Slide 92

Slide 92 text

2016 92/129 特別篇 5: 黑暗執行緒案例 Q: 試著分析黑暗執行緒的文章。【 GUID Primary Key 資料庫避雷守則】 Ref: http://blog.darkthread.net/post-2016-01-29-guid-as-pk-on-db.aspx

Slide 93

Slide 93 text

93/129 2016 特別篇 5: 黑暗執行緒案例以架構設計而言，我更傾向讓應用伺服器決定 GUID / UUID 的值，理由有幾個。 ➊ 未來若需要異質資料庫彼此交換資料， GUID / UUID 的衝突會更小。例如若 SQL Server 要與 MySQL 交換， MySQL 沒有 NEWID / NEWSEQUENTIALID 函式，所以勢必要用另一種算法來計算 UUID ，而算法不同就可能破壞原本 GUID / UUID 不會重複的機率。 ➋ 資料庫在整體架構中，是最難以擴展的，所以我傾向讓資料庫做愈少事情愈好。 GUID / UUID 是需要計算的，即使開銷很低，但若量大還是很可觀，所以我還是會把計算的成本移至應用伺服器處理。

Slide 94

Slide 94 text

94/129 2016 特別篇 5: 黑暗執行緒案例 ➌ 應用伺服器脫離 SQL Server 後，就無法使用 NEWID 及 NEWSEQUENTIALID ，所以需要找 UUID 產生器。 UUID 產生器有幾種版本， UUIDv1 是類循序性， UUIDv4 是亂序性，可以依業務場景選擇所需。而且 UUIDv1 的類循序性不像 NEWSEQUENTIALID 這麼容易猜測。 ➍ 大規模架構中，全循序性 Insert 不一定是好事。想像一下，順序性 Insert 時，所有資料會集中在樹最右下角的 Leaf Page Insert ，當 Page 滿後，就必須 Page Split ，此時就一定有 Latch 。 Latch 會減緩 Insert 的速度。再加上實際在硬碟寫入的 Block 也會變成是 Hotspot 。所以有時候，適當非循序性所造成的 Fragmentation 有時反而會更快。最後有可能會變成全順序性 Insert 不一定最快，全隨機性 Insert 也不一定最好，反而中間找平衡才是最佳的。而這時把 UUID 的計算移到應用伺服器，剛好可以滿足這點。

Slide 95

Slide 95 text

95/129 2016 特別篇 5: 黑暗執行緒案例黑暗執行緒提供的表設計建議

Slide 96

Slide 96 text

96/129 2016 特別篇 5: 黑暗執行緒案例黑暗執行緒提供的表設計建議 4c.. cb.. ee.. 07.. 3b.. 4c.. 5b.. 9e.. cb.. ee.. Clustered Index ( 循序式 ) Nonclustered Index ( 亂序式 ) 3 6 7 1 2 3 4 5 6 7

Slide 97

Slide 97 text

97/129 2016

Slide 98

Slide 98 text

98/129 2016 特別篇 5: 黑暗執行緒案例假設已有五筆資料 5b.. cb.. 07.. 3b.. 5b.. 9e.. cb.. 3 5 1 2 3 4 5 [SeqNo] [FlowId] ... 1 07.. ... 2 9e.. ... 3 3b.. ... 4 cb.. ... 5 5b.. ... 1 3 5 2 4 Nonclustered Index ( 亂序式 ) Clustered Index ( 循序式 )

Slide 99

Slide 99 text

99/129 2016 特別篇 5: 黑暗執行緒案例尋找【 WHERE FlowId = “5b..” 】 IO 次數： 0 5b.. cb.. 07.. 3b.. 5b.. 9e.. cb.. 3 5 1 2 3 4 5 [SeqNo] [FlowId] ... 1 07.. ... 2 9e.. ... 3 3b.. ... 4 cb.. ... 5 5b.. ... 1 3 5 2 4 Nonclustered Index ( 亂序式 ) Clustered Index ( 循序式 )

Slide 100

Slide 100 text

100/129 2016 特別篇 5: 黑暗執行緒案例尋找【 WHERE FlowId = “5b..” 】 IO 次數： 1 5b.. cb.. 07.. 3b.. 5b.. 9e.. cb.. 3 5 1 2 3 4 5 [SeqNo] [FlowId] ... 1 07.. ... 2 9e.. ... 3 3b.. ... 4 cb.. ... 5 5b.. ... 1 3 5 2 4 Nonclustered Index ( 亂序式 ) Clustered Index ( 循序式 )

Slide 101

Slide 101 text

101/129 2016 特別篇 5: 黑暗執行緒案例尋找【 WHERE FlowId = “5b..” 】 IO 次數： 2 5b.. cb.. 07.. 3b.. 5b.. 9e.. cb.. 3 5 1 2 3 4 5 [SeqNo] [FlowId] ... 1 07.. ... 2 9e.. ... 3 3b.. ... 4 cb.. ... 5 5b.. ... 1 3 5 2 4 Nonclustered Index ( 亂序式 ) Clustered Index ( 循序式 )

Slide 102

Slide 102 text

102/129 2016 特別篇 5: 黑暗執行緒案例尋找【 WHERE FlowId = “5b..” 】 IO 次數： 3 5b.. cb.. 07.. 3b.. 5b.. 9e.. cb.. 3 5 1 2 3 4 5 [SeqNo] [FlowId] ... 1 07.. ... 2 9e.. ... 3 3b.. ... 4 cb.. ... 5 5b.. ... 1 3 5 2 4 Nonclustered Index ( 亂序式 ) Clustered Index ( 循序式 )

Slide 103

Slide 103 text

103/129 2016 特別篇 5: 黑暗執行緒案例尋找【 WHERE FlowId = “5b..” 】 IO 次數： 4 ( 完成 ) 5b.. cb.. 07.. 3b.. 5b.. 9e.. cb.. 3 5 1 2 3 4 5 [SeqNo] [FlowId] ... 1 07.. ... 2 9e.. ... 3 3b.. ... 4 cb.. ... 5 5b.. ... 1 3 5 2 4 Nonclustered Index ( 亂序式 ) Clustered Index ( 循序式 )

Slide 104

Slide 104 text

104/129 2016 特別篇 5: 黑暗執行緒案例新增【 SeqNo: 6, FlowID: 4c.. 】 IO 次數： 0 5b.. cb.. 07.. 3b.. 5b.. 9e.. cb.. 3 5 1 2 3 4 5 [SeqNo] [FlowId] ... 1 07.. ... 2 9e.. ... 3 3b.. ... 4 cb.. ... 5 5b.. ... 6 4c.. ... 1 3 5 2 4 Nonclustered Index ( 亂序式 ) Clustered Index ( 循序式 )

Slide 105

Slide 105 text

105/129 2016 特別篇 5: 黑暗執行緒案例新增【 SeqNo: 6, FlowID: 4c.. 】 IO 次數： 1 5b.. cb.. 07.. 3b.. 5b.. 9e.. cb.. 3 5 1 2 3 4 5 [SeqNo] [FlowId] ... 1 07.. ... 2 9e.. ... 3 3b.. ... 4 cb.. ... 5 5b.. ... 6 4c.. ... 1 3 5 2 4 Nonclustered Index ( 亂序式 ) Clustered Index ( 循序式 )

Slide 106

Slide 106 text

106/129 2016 特別篇 5: 黑暗執行緒案例新增【 SeqNo: 6, FlowID: 4c.. 】 IO 次數： 2 5b.. cb.. 07.. 3b.. 5b.. 9e.. cb.. 3 5 1 2 3 4 5 [SeqNo] [FlowId] ... 1 07.. ... 2 9e.. ... 3 3b.. ... 4 cb.. ... 5 5b.. ... 6 4c.. ... 1 3 5 2 4 Nonclustered Index ( 亂序式 ) Clustered Index ( 循序式 )

Slide 107

Slide 107 text

107/129 2016 特別篇 5: 黑暗執行緒案例新增【 SeqNo: 6, FlowID: 4c.. 】 IO 次數： 3+3 Page Split 次數： 1 3 5 1 2 3 4 5 [SeqNo] [FlowId] ... 1 07.. ... 2 9e.. ... 3 3b.. ... 4 cb.. ... 5 5b.. ... 6 4c.. ... Nonclustered Index ( 亂序式 ) Clustered Index ( 循序式 ) 07.. 3b.. 4c.. 5b.. 1 3 6 5 4 2 3b.. 5b.. cb.. 9e.. cb..

Slide 108

Slide 108 text

108/129 2016 特別篇 5: 黑暗執行緒案例新增【 SeqNo: 6, FlowID: 4c.. 】 IO 次數： 3+3+1 Page Split 次數： 1 3 5 1 2 3 4 5 [SeqNo] [FlowId] ... 1 07.. ... 2 9e.. ... 3 3b.. ... 4 cb.. ... 5 5b.. ... 6 4c.. ... Nonclustered Index ( 亂序式 ) Clustered Index ( 循序式 ) 07.. 3b.. 4c.. 5b.. 1 3 6 5 4 2 3b.. 5b.. cb.. 9e.. cb..

Slide 109

Slide 109 text

109/129 2016 特別篇 5: 黑暗執行緒案例新增【 SeqNo: 6, FlowID: 4c.. 】 IO 次數： 3+3+2 Page Split 次數： 1 3 5 1 2 3 4 5 [SeqNo] [FlowId] ... 1 07.. ... 2 9e.. ... 3 3b.. ... 4 cb.. ... 5 5b.. ... 6 4c.. ... Nonclustered Index ( 亂序式 ) Clustered Index ( 循序式 ) 07.. 3b.. 4c.. 5b.. 1 3 6 5 4 2 3b.. 5b.. cb.. 9e.. cb..

Slide 110

Slide 110 text

110/129 2016 特別篇 5: 黑暗執行緒案例新增【 SeqNo: 6, FlowID: 4c.. 】 IO 次數： 3+3+3 Page Split 次數： 1 3 5 1 2 3 4 5 6 [SeqNo] [FlowId] ... 1 07.. ... 2 9e.. ... 3 3b.. ... 4 cb.. ... 5 5b.. ... 6 4c.. ... Nonclustered Index ( 亂序式 ) Clustered Index ( 循序式 ) 07.. 3b.. 4c.. 5b.. 1 3 6 5 4 2 3b.. 5b.. cb.. 9e.. cb..

Slide 111

Slide 111 text

111/129 2016 特別篇 5: 黑暗執行緒案例新增【 SeqNo: 6, FlowID: 4c.. 】 IO 次數： 3+3+4=10 ( 完成 ) Page Split 次數： 1 07.. 3b.. 4c.. 5b.. 3 6 1 2 3 4 5 6 [SeqNo] [FlowId] ... 1 07.. ... 2 9e.. ... 3 3b.. ... 4 cb.. ... 5 5b.. ... 6 4c.. ... 1 3 6 5 4 2 Nonclustered Index ( 亂序式 ) Clustered Index ( 循序式 ) 3b.. 5b.. cb.. 9e.. cb..

Slide 112

Slide 112 text

112/129 2016 特別篇 5: 黑暗執行緒案例如果用一般亂序式 UUID [FlowId] ... ... 07.. ... ... 9e.. ... ... 3b.. ... ... cb.. ... ... 5b.. ... ... 5b.. cb.. 07.. 3b.. 5b.. 9e.. cb.. Clustered Index ( 亂序式 )

Slide 113

Slide 113 text

113/129 2016 特別篇 5: 黑暗執行緒案例尋找【 WHERE FlowId = “5b..” 】 IO 次數： 0 [FlowId] ... ... 07.. ... ... 9e.. ... ... 3b.. ... ... cb.. ... ... 5b.. ... ... 5b.. cb.. 07.. 3b.. 5b.. 9e.. cb.. Clustered Index ( 亂序式 )

Slide 114

Slide 114 text

114/129 2016 特別篇 5: 黑暗執行緒案例尋找【 WHERE FlowId = “5b..” 】 IO 次數： 1 [FlowId] ... ... 07.. ... ... 9e.. ... ... 3b.. ... ... cb.. ... ... 5b.. ... ... 5b.. cb.. 07.. 3b.. 5b.. 9e.. cb.. Clustered Index ( 亂序式 )

Slide 115

Slide 115 text

115/129 2016 特別篇 5: 黑暗執行緒案例尋找【 WHERE FlowId = “5b..” 】 IO 次數： 2 ( 完成 ) [FlowId] ... ... 07.. ... ... 9e.. ... ... 3b.. ... ... cb.. ... ... 5b.. ... ... 5b.. cb.. 07.. 3b.. 5b.. 9e.. cb.. Clustered Index ( 亂序式 )

Slide 116

Slide 116 text

116/129 2016 特別篇 5: 黑暗執行緒案例新增【 FlowID: 4c.. 】 IO 次數： 0 [FlowId] ... ... 07.. ... ... 9e.. ... ... 3b.. ... ... cb.. ... ... 5b.. ... ... 4c.. ... ... 5b.. cb.. 07.. 3b.. 5b.. 9e.. cb.. Clustered Index ( 亂序式 )

Slide 117

Slide 117 text

117/129 2016 特別篇 5: 黑暗執行緒案例新增【 FlowID: 4c.. 】 IO 次數： 1 [FlowId] ... ... 07.. ... ... 9e.. ... ... 3b.. ... ... cb.. ... ... 5b.. ... ... 4c.. ... ... 5b.. cb.. 07.. 3b.. 5b.. 9e.. cb.. Clustered Index ( 亂序式 )

Slide 118

Slide 118 text

118/129 2016 特別篇 5: 黑暗執行緒案例新增【 FlowID: 4c.. 】 IO 次數： 2 [FlowId] ... ... 07.. ... ... 9e.. ... ... 3b.. ... ... cb.. ... ... 5b.. ... ... 4c.. ... ... 5b.. cb.. 07.. 3b.. 5b.. 9e.. cb.. Clustered Index ( 亂序式 )

Slide 119

Slide 119 text

119/129 2016 特別篇 5: 黑暗執行緒案例新增【 FlowID: 4c.. 】 IO 次數： 3+3 ( 完成 ) Page Split 次數： 1 [FlowId] ... ... 07.. ... ... 9e.. ... ... 3b.. ... ... cb.. ... ... 5b.. ... ... 4c.. ... ... 3b.. 5b.. cb.. 07.. 3b.. 4c.. 5b.. Clustered Index ( 亂序式 ) 9e.. cb..

Slide 120

Slide 120 text

120/129 2016 特別篇 5: 黑暗執行緒案例如果用一般循序式 UUID [FlowId] ... ... 1 ... ... 2 ... ... 3 ... ... 4 ... ... 5 ... ... 3 5 1 2 3 4 5 Clustered Index ( 循序式 )

Slide 121

Slide 121 text

121/129 2016 特別篇 5: 黑暗執行緒案例尋找【 WHERE FlowId = 5 】 IO 次數： 0 [FlowId] ... ... 1 ... ... 2 ... ... 3 ... ... 4 ... ... 5 ... ... 3 5 1 2 3 4 5 Clustered Index ( 循序式 )

Slide 122

Slide 122 text

122/129 2016 特別篇 5: 黑暗執行緒案例尋找【 WHERE FlowId = 5 】 IO 次數： 1 [FlowId] ... ... 1 ... ... 2 ... ... 3 ... ... 4 ... ... 5 ... ... 3 5 1 2 3 4 5 Clustered Index ( 循序式 )

Slide 123

Slide 123 text

123/129 2016 特別篇 5: 黑暗執行緒案例尋找【 WHERE FlowId = 5 】 IO 次數： 2 ( 完成 ) [FlowId] ... ... 1 ... ... 2 ... ... 3 ... ... 4 ... ... 5 ... ... 3 5 1 2 3 4 5 Clustered Index ( 循序式 )

Slide 124

Slide 124 text

124/129 2016 特別篇 5: 黑暗執行緒案例新增【 FlowID: 6 】 IO 次數： 0 [FlowId] ... ... 1 ... ... 2 ... ... 3 ... ... 4 ... ... 5 ... ... 6 ... ... 3 5 1 2 3 4 5 Clustered Index ( 循序式 )

Slide 125

Slide 125 text

125/129 2016 特別篇 5: 黑暗執行緒案例新增【 FlowID: 6 】 IO 次數： 1 [FlowId] ... ... 1 ... ... 2 ... ... 3 ... ... 4 ... ... 5 ... ... 6 ... ... 3 5 1 2 3 4 5 Clustered Index ( 循序式 )

Slide 126

Slide 126 text

126/129 2016 特別篇 5: 黑暗執行緒案例新增【 FlowID: 6 】 IO 次數： 2 [FlowId] ... ... 1 ... ... 2 ... ... 3 ... ... 4 ... ... 5 ... ... 6 ... ... 3 5 1 2 3 4 5 Clustered Index ( 循序式 )

Slide 127

Slide 127 text

127/129 2016 特別篇 5: 黑暗執行緒案例新增【 FlowID: 6 】 IO 次數： 3+1 ( 完成 ) [FlowId] ... ... 1 ... ... 2 ... ... 3 ... ... 4 ... ... 5 ... ... 6 ... ... 3 6 1 2 3 4 5 6 Clustered Index ( 循序式 )

Slide 128

Slide 128 text

128/129 2016 特別篇 5: 黑暗執行緒案例小結：本場景為例黑暗執行緒亂序式 UUID 循序式 UUID Point-of-query IO 4 2 2 Insert IO 10 6 4 Insert (Page Split) 1 1 0 Space size ++ + + Latch(%) ++ + +

Slide 129

Slide 129 text

129/129 2016 特別篇 6: MultiColumn B-Tree Index [Col#1] [Col#2] 1 A 3 B 3 C 4 D 3 C CREATE INDEX ON table (Col#1, Col#2); 1 A 3 B 3 C 4 D