Better Rails by Knowing Better Database

It's my presentation at Rails Pacific. Knowing the database concept and implementation of Integrity, Locking, Transaction, Index, etc will help developers much better on data modeling design and build solid production applications.

Transcript

1. Database with Rails #FDPNJOH
   B
   TFOJPS
   EFWFMPQFS
2. @yedingding RubyConf China
3. Apache/Nginx App App App App Client Database
4. DB Server 44% Network 10% Web Server 14% App Server 32% Source Bottleneck
5. 1FSGPSNBODF
   $BVTFT • SQL Statement • Data Model Design • DB Configurations • Indexes • Query Cache • Concurrency model • Data cache • Deadlocks
6. %BUB
   .PEFMJOH
8. *OUFHSJUZ
   $POTUSBJOU
10. Primary-Key Constraint
12. Not Null Constraint
14. Unique Constraint
16. Referential Constraint *
18. 5IF
    "DUJWF3FDPSE
    8BZ
19. /PU
    /VMM
    $POTUSBJOU
    6OJRVF
    $POTUSBJOU
    3FGFSFOUJBM
    $POTUSBJOU WBMJEBUFT@QSFTFODF@PG
    WBMJEBUFT@VOJRVFTTOFTT@PG
    WBMJEBUFT@BTTPDJBUFE
20. 3FGFSFOUJBM
    $POTUSBJOU id Accounts . . . id Users account_id . .
21. DMBTT
    "DDPVOU
    
    
    IBT@NBOZ
    VTFST
    EFQFOEFOU
    YY
    FOE
22. DMBTT
    "DDPVOU
    
    
    IBT@NBOZ
    VTFST
    EFQFOEFOU
    YY
    FOE :destroy :delete_all :nullify :restrict_with_error :restrict_with_exception
23. DMBTT
    "DDPVOU
    
    
    IBT@NBOZ
    VTFST
    EFQFOEFOU
    YY
    FOE :destroy :delete_all :nullify :restrict_with_error :restrict_with_exception ON DELETE CASCADE
24. DMBTT
    "DDPVOU
    
    
    IBT@NBOZ
    VTFST
    EFQFOEFOU
    YY
    FOE :destroy :delete_all :nullify :restrict_with_error :restrict_with_exception
25. DMBTT
    "DDPVOU
    
    
    IBT@NBOZ
    VTFST
    EFQFOEFOU
    YY
    FOE :destroy :delete_all :nullify :restrict_with_error :restrict_with_exception ON DELETE SET NULL
26. DMBTT
    "DDPVOU
    
    
    IBT@NBOZ
    VTFST
    EFQFOEFOU
    YY
    FOE :destroy :delete_all :nullify :restrict_with_error :restrict_with_exception
27. DMBTT
    "DDPVOU
    
    
    IBT@NBOZ
    VTFST
    EFQFOEFOU
    YY
    FOE :destroy :delete_all :nullify :restrict_with_error :restrict_with_exception ON DELETE RESTRICT
28. Account.delete(1) SQL (3.6ms) DELETE FROM `accounts` WHERE `accounts`.`id` = 1
30. gem ‘foreigner’
31. 7JFX
35. .BUFSJBMJ[FE
    7JFX
36. .BUFSJBMJ[FE
    7JFX CREATE MATERIALIZED VIEW table_name
37. .BUFSJBMJ[FE
    7JFX
38. .BUFSJBMJ[FE
    7JFX REFRESH MATERIALIZED VIEW table_name
39. *OEFYJOH
40. id name email tasks_count 1 Dingding ding@fengche.co 28 2 Ben ben@fengche.co 12 3 Roy roy@fengche.co 8 4 Terry terry@fengche.co 2 5 JiaQi jiaqi@fengche.co 4
41. id name email tasks_count 1 Dingding ding@fengche.co 28 2 Ben ben@fengche.co 12 3 Roy roy@fengche.co 8 4 Terry terry@fengche.co 2 5 JiaQi jiaqi@fengche.co 4 pry(main) > User.find(4) User Load (0.2ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 4 LIMIT 1
42. id name email tasks_count 1 Dingding ding@fengche.co 28 2 Ben ben@fengche.co 12 3 Roy roy@fengche.co 8 4 Terry terry@fengche.co 2 5 JiaQi jiaqi@fengche.co 4 pry(main) > User.find(4) User Load (0.2ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 4 LIMIT 1 Is ID equal to 4?
43. id name email tasks_count 1 Dingding ding@fengche.co 28 2 Ben ben@fengche.co 12 3 Roy roy@fengche.co 8 4 Terry terry@fengche.co 2 5 JiaQi jiaqi@fengche.co 4 pry(main) > User.find(4) User Load (0.2ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 4 LIMIT 1
44. id name email tasks_count 1 Dingding ding@fengche.co 28 2 Ben ben@fengche.co 12 3 Roy roy@fengche.co 8 4 Terry terry@fengche.co 2 5 JiaQi jiaqi@fengche.co 4 pry(main) > User.find(4) User Load (0.2ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 4 LIMIT 1 Is ID equal to 4?
45. id name email tasks_count 1 Dingding ding@fengche.co 28 2 Ben ben@fengche.co 12 3 Roy roy@fengche.co 8 4 Terry terry@fengche.co 2 5 JiaQi jiaqi@fengche.co 4 pry(main) > User.find(4) User Load (0.2ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 4 LIMIT 1
46. id name email tasks_count 1 Dingding ding@fengche.co 28 2 Ben ben@fengche.co 12 3 Roy roy@fengche.co 8 4 Terry terry@fengche.co 2 5 JiaQi jiaqi@fengche.co 4 pry(main) > User.find(4) User Load (0.2ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 4 LIMIT 1 Is ID equal to 4?
47. id name email tasks_count 1 Dingding ding@fengche.co 28 2 Ben ben@fengche.co 12 3 Roy roy@fengche.co 8 4 Terry terry@fengche.co 2 5 JiaQi jiaqi@fengche.co 4 pry(main) > User.find(4) User Load (0.2ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 4 LIMIT 1
48. id name email tasks_count 1 Dingding ding@fengche.co 28 2 Ben ben@fengche.co 12 3 Roy roy@fengche.co 8 4 Terry terry@fengche.co 2 5 JiaQi jiaqi@fengche.co 4 pry(main) > User.find(4) User Load (0.2ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 4 LIMIT 1 Is ID equal to 4?
49. id name email tasks_count 1 Dingding ding@fengche.co 28 2 Ben ben@fengche.co 12 3 Roy roy@fengche.co 8 4 Terry terry@fengche.co 2 5 JiaQi jiaqi@fengche.co 4 pry(main) > User.find(4) User Load (0.2ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 4 LIMIT 1
50. id name email tasks_count 1 Dingding ding@fengche.co 28 2 Ben ben@fengche.co 12 3 Roy roy@fengche.co 8 4 Terry terry@fengche.co 2 5 JiaQi jiaqi@fengche.co 4 pry(main) > User.find(4) User Load (0.2ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 4 LIMIT 1 1 2 3 4 5
51. id name email tasks_count 1 Dingding ding@fengche.co 28 2 Ben ben@fengche.co 12 3 Roy roy@fengche.co 8 4 Terry terry@fengche.co 2 5 JiaQi jiaqi@fengche.co 4 pry(main) > User.find(4) User Load (0.2ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 4 LIMIT 1 1 2 3 4 5
52. *OEFY
    5ZQF • B-Tree • Hash • GiST • SP-GiST • GIN
53. #
    5SFF 5 8 9 2 2 3 4 1 5 9 10 8
54. #
    5SFF 5 8 9 2 2 3 4 1 5 9 10 8 User.find(4)
55. #
    5SFF 5 8 9 2 2 3 4 1 5 9 10 8 User.find(4) < 5
56. #
    5SFF 5 8 9 2 2 3 4 1 5 9 10 8 User.find(4) < 5 > 2
57. #
    5SFF 5 8 9 2 2 3 4 1 5 9 10 8 User.find(4) < 5 > 2
58. #
    5SFF 5 8 9 2 2 3 4 1 5 9 10 8 User.find(4) < 5 > 2
59. #
    5SFF 5 8 9 2 2 3 4 1 5 9 10 8 User.find(4) < 5 > 2 Users
60. #
    5SFF 5 8 9 2 2 3 4 1 5 9 10 8 User.find(4) < 5 > 2 Users A = V
61. #
    5SFF 5 8 9 2 2 3 4 1 5 9 10 8 User.find(4) < 5 > 2 Users A = V A < V
62. #
    5SFF 5 8 9 2 2 3 4 1 5 9 10 8 User.find(4) < 5 > 2 Users A = V A < V V1 <= A <= V2
63. #
    5SFF 5 8 9 2 2 3 4 1 5 9 10 8 User.find(4) < 5 > 2 Users A = V A < V V1 <= A <= V2 A > V
64. *OEFYFT
    %PXOTJEF • Cost extra space • Take time to create • Take time to maintain
65. *OEFYFT
    )PX
    UP
    QJDLVQ • Size of table • Data distributions • Query vs update load
66. 5SBOTBDUJPO
74. Begin Statement
75. Begin Statement Statement 1
76. Begin Statement Statement 1 Statement 2
77. Begin Statement Statement 1 Statement 2 Commit Statement
78. $BMMCBDLT
80. user.save
81. BEGIN user.save
82. BEGIN before_save user.save
83. BEGIN before_save save user.save
84. BEGIN before_save save after_save user.save
85. BEGIN before_save save after_save COMMIT! user.save
86. BEGIN before_save save after_save COMMIT! after_commit user.save
87. "$*%
89. • Atomicity: Either all operations of the transaction should be reflected properly in the database, or none are.
90. • Atomicity: Either all operations of the transaction should be reflected properly in the database, or none are. • Consistency. Execution of the individual transaction should preserve the consistency of the database.
91. • Atomicity: Either all operations of the transaction should be reflected 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.
92. • Atomicity: Either all operations of the transaction should be reflected 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. • Durability. After a transaction commited, the changes it has made to the database should persist, even if there are system failures.
93. $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
94. $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
95. 3FBE
    $PNNJUUFE
96. 3FBE
    $PNNJUUFE SET TRANSACTION ISOLATION LEVEL READ COMMITTED
97. 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
98. 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
99. 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 NO
100. 3FQFBUBCMF
     3FBE
101. 3FQFBUBCMF
     3FBE SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
102. 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
103. 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
104. 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 YES
105. 4&3*"-*;"#-&
106. 4&3*"-*;"#-& SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
107. 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
108. 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
109. 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 YES
110. 3&"%
     6/$0..*55&%
111. 3&"%
     6/$0..*55&% SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED
112. 5IBOL
     :PV