Better Rails by Knowing Better Database

Slide 1

Slide 1 text

Database with Rails #FDPNJOHBTFOJPSEFWFMPQFS

Slide 2

Slide 2 text

@yedingding RubyConf China

Slide 3

Slide 3 text

Apache/Nginx App App App App Client Database

Slide 4

Slide 4 text

DB Server 44% Network 10% Web Server 14% App Server 32% Source Bottleneck

Slide 5

Slide 5 text

1FSGPSNBODF$BVTFT • SQL Statement • Data Model Design • DB Conﬁgurations • Indexes • Query Cache • Concurrency model • Data cache • Deadlocks

Slide 6

Slide 6 text

%BUB.PEFMJOH

Slide 7

Slide 7 text

No content

Slide 8

Slide 8 text

*OUFHSJUZ$POTUSBJOU

Slide 9

Slide 9 text

No content

Slide 10

Slide 10 text

Primary-Key Constraint

Slide 11

Slide 11 text

No content

Slide 12

Slide 12 text

Not Null Constraint

Slide 13

Slide 13 text

No content

Slide 14

Slide 14 text

Unique Constraint

Slide 15

Slide 15 text

No content

Slide 16

Slide 16 text

Referential Constraint *

Slide 17

Slide 17 text

No content

Slide 18

Slide 18 text

5IF"DUJWF3FDPSE8BZ

Slide 19

Slide 19 text

/PU/VMM$POTUSBJOU 6OJRVF$POTUSBJOU 3FGFSFOUJBM$POTUSBJOU WBMJEBUFT@QSFTFODF@PG WBMJEBUFT@VOJRVFTTOFTT@PG WBMJEBUFT@BTTPDJBUFE

Slide 20

Slide 20 text

3FGFSFOUJBM$POTUSBJOU id Accounts . . . id Users account_id . .

Slide 21

Slide 21 text

DMBTT"DDPVOU IBT@NBOZVTFST EFQFOEFOUYY FOE

Slide 22

Slide 22 text

DMBTT"DDPVOU IBT@NBOZVTFST EFQFOEFOUYY FOE :destroy :delete_all :nullify :restrict_with_error :restrict_with_exception

Slide 23

Slide 23 text

DMBTT"DDPVOU IBT@NBOZVTFST EFQFOEFOUYY FOE :destroy :delete_all :nullify :restrict_with_error :restrict_with_exception ON DELETE CASCADE

Slide 24

Slide 24 text

DMBTT"DDPVOU IBT@NBOZVTFST EFQFOEFOUYY FOE :destroy :delete_all :nullify :restrict_with_error :restrict_with_exception

Slide 25

Slide 25 text

DMBTT"DDPVOU IBT@NBOZVTFST EFQFOEFOUYY FOE :destroy :delete_all :nullify :restrict_with_error :restrict_with_exception ON DELETE SET NULL

Slide 26

Slide 26 text

DMBTT"DDPVOU IBT@NBOZVTFST EFQFOEFOUYY FOE :destroy :delete_all :nullify :restrict_with_error :restrict_with_exception

Slide 27

Slide 27 text

DMBTT"DDPVOU IBT@NBOZVTFST EFQFOEFOUYY FOE :destroy :delete_all :nullify :restrict_with_error :restrict_with_exception ON DELETE RESTRICT

Slide 28

Slide 28 text

Account.delete(1) SQL (3.6ms) DELETE FROM àccounts` WHERE àccounts`.ìd` = 1

Slide 29

Slide 29 text

No content

Slide 30

Slide 30 text

gem ‘foreigner’

Slide 31

Slide 31 text

7JFX

Slide 32

Slide 32 text

No content

Slide 33

Slide 33 text

No content

Slide 34

Slide 34 text

No content

Slide 35

Slide 35 text

.BUFSJBMJ[FE7JFX

Slide 36

Slide 36 text

.BUFSJBMJ[FE7JFX CREATE MATERIALIZED VIEW table_name

Slide 37

Slide 37 text

.BUFSJBMJ[FE7JFX

Slide 38

Slide 38 text

.BUFSJBMJ[FE7JFX REFRESH MATERIALIZED VIEW table_name

Slide 39

Slide 39 text

*OEFYJOH

Slide 40

Slide 40 text

id name email tasks_count 1 Dingding [email protected] 28 2 Ben [email protected] 12 3 Roy [email protected] 8 4 Terry [email protected] 2 5 JiaQi [email protected] 4

Slide 41

Slide 41 text

id name email tasks_count 1 Dingding [email protected] 28 2 Ben [email protected] 12 3 Roy [email protected] 8 4 Terry [email protected] 2 5 JiaQi [email protected] 4 pry(main) > User.find(4) User Load (0.2ms) SELECT ùsers`.* FROM ùsers` WHERE ùsers`.ìd` = 4 LIMIT 1

Slide 42

Slide 42 text

Slide 43

Slide 43 text

Slide 44

Slide 44 text

Slide 45

Slide 45 text

Slide 46

Slide 46 text

Slide 47

Slide 47 text

Slide 48

Slide 48 text

Slide 49

Slide 49 text

Slide 50

Slide 50 text

Slide 51

Slide 51 text

Slide 52

Slide 52 text

*OEFY5ZQF • B-Tree • Hash • GiST • SP-GiST • GIN

Slide 53

Slide 53 text

#5SFF 5 8 9 2 2 3 4 1 5 9 10 8

Slide 54

Slide 54 text

#5SFF 5 8 9 2 2 3 4 1 5 9 10 8 User.ﬁnd(4)

Slide 55

Slide 55 text

#5SFF 5 8 9 2 2 3 4 1 5 9 10 8 User.ﬁnd(4) < 5

Slide 56

Slide 56 text

#5SFF 5 8 9 2 2 3 4 1 5 9 10 8 User.ﬁnd(4) < 5 > 2

Slide 57

Slide 57 text

#5SFF 5 8 9 2 2 3 4 1 5 9 10 8 User.ﬁnd(4) < 5 > 2

Slide 58

Slide 58 text

#5SFF 5 8 9 2 2 3 4 1 5 9 10 8 User.ﬁnd(4) < 5 > 2

Slide 59

Slide 59 text

#5SFF 5 8 9 2 2 3 4 1 5 9 10 8 User.ﬁnd(4) < 5 > 2 Users

Slide 60

Slide 60 text

#5SFF 5 8 9 2 2 3 4 1 5 9 10 8 User.ﬁnd(4) < 5 > 2 Users A = V

Slide 61

Slide 61 text

#5SFF 5 8 9 2 2 3 4 1 5 9 10 8 User.ﬁnd(4) < 5 > 2 Users A = V A < V

Slide 62

Slide 62 text

#5SFF 5 8 9 2 2 3 4 1 5 9 10 8 User.ﬁnd(4) < 5 > 2 Users A = V A < V V1 <= A <= V2

Slide 63

Slide 63 text

#5SFF 5 8 9 2 2 3 4 1 5 9 10 8 User.ﬁnd(4) < 5 > 2 Users A = V A < V V1 <= A <= V2 A > V

Slide 64

Slide 64 text

*OEFYFT%PXOTJEF • Cost extra space • Take time to create • Take time to maintain

Slide 65

Slide 65 text

*OEFYFT)PXUPQJDLVQ • Size of table • Data distributions • Query vs update load

Slide 66

Slide 66 text

5SBOTBDUJPO

Slide 67

Slide 67 text

No content

Slide 68

Slide 68 text

No content

Slide 69

Slide 69 text

No content

Slide 70

Slide 70 text

No content

Slide 71

Slide 71 text

No content

Slide 72

Slide 72 text

No content

Slide 73

Slide 73 text

No content

Slide 74

Slide 74 text

Begin Statement

Slide 75

Slide 75 text

Begin Statement Statement 1

Slide 76

Slide 76 text

Begin Statement Statement 1 Statement 2

Slide 77

Slide 77 text

Begin Statement Statement 1 Statement 2 Commit Statement

Slide 78

Slide 78 text

$BMMCBDLT

Slide 79

Slide 79 text

No content

Slide 80

Slide 80 text

user.save

Slide 81

Slide 81 text

BEGIN user.save

Slide 82

Slide 82 text

BEGIN before_save user.save

Slide 83

Slide 83 text

BEGIN before_save save user.save

Slide 84

Slide 84 text

BEGIN before_save save after_save user.save

Slide 85

Slide 85 text

BEGIN before_save save after_save COMMIT! user.save

Slide 86

Slide 86 text

BEGIN before_save save after_save COMMIT! after_commit user.save

Slide 87

Slide 87 text

"$*%

Slide 88

Slide 88 text

No content

Slide 89

Slide 89 text

• Atomicity: Either all operations of the transaction should be reﬂected properly in the database, or none are.

Slide 90

Slide 90 text

Slide 91

Slide 91 text

• Atomicity: Either all operations of the transaction should be reﬂected properly in the database, or none are. • Consistency. Execution of the individual transaction should preserve the consistency of the database. • Isolation. Concurrently executing transactions should be isolated from one another, so that each has the impression that no other transaction is executing concurrently with it.

Slide 92

Slide 92 text

Slide 93

Slide 93 text

$PODVSSFOU.PEFM Transaction 1 Transaction 2 User.transaction do sleep 3 user = User.find(3) user.name = "dingding- #{Time.now.to_i}" user.save end User.transaction do user = User.find(3) puts “1”+user.name sleep 5 user2 = User.find(3) puts “1”+user.name end

Slide 94

Slide 94 text

Slide 95

Slide 95 text

3FBE$PNNJUUFE

Slide 96

Slide 96 text

3FBE$PNNJUUFE SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Slide 97

Slide 97 text

Transaction 1 Transaction 2 User.transaction(:read_ committed) do sleep 3 user = User.find(3) user.name = "dingding- #{Time.now.to_i}" user.save end User.transaction(:rea d_committed) do user = User.find(3) puts “1”+user.name sleep 5 user2 = User.find(3) puts “1”+user.name end

Slide 98

Slide 98 text

Slide 99

Slide 99 text

Slide 100

Slide 100 text

3FQFBUBCMF3FBE

Slide 101

Slide 101 text

3FQFBUBCMF3FBE SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Slide 102

Slide 102 text

Transaction 1 Transaction 2 User.transaction(:repeat able_read) do sleep 3 user = User.find(3) user.name = "dingding- #{Time.now.to_i}" user.save end User.transaction(:rep eatable_read) do user = User.find(3) puts “1”+user.name sleep 5 user2 = User.find(3) puts “1”+user.name end

Slide 103

Slide 103 text

Slide 104

Slide 104 text

Slide 105

Slide 105 text

4&3*"-*;"#-&

Slide 106

Slide 106 text

4&3*"-*;"#-& SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Slide 107

Slide 107 text

Transaction 1 Transaction 2 User.transaction(:seriali zable) do sleep 3 user = User.find(3) user.name = "dingding- #{Time.now.to_i}" user.save end User.transaction(:seri alizable) do user = User.find(3) puts “1”+user.name sleep 5 user2 = User.find(3) puts “1”+user.name end

Slide 108

Slide 108 text

Slide 109

Slide 109 text

Slide 110

Slide 110 text

3&"%6/$0..*55&%

Slide 111

Slide 111 text

3&"%6/$0..*55&% SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED

Slide 112

Slide 112 text

5IBOL:PV