$30 off During Our Annual Pro Sale. View Details »

Better Rails by Knowing Better Database

Dingding Ye
September 27, 2014

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.

Dingding Ye

September 27, 2014
Tweet

More Decks by Dingding Ye

Other Decks in Technology

Transcript

  1. Database with Rails #FDPNJOHBTFOJPSEFWFMPQFS

  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

  7. None
  8. *OUFHSJUZ$POTUSBJOU

  9. None
  10. Primary-Key Constraint

  11. None
  12. Not Null Constraint

  13. None
  14. Unique Constraint

  15. None
  16. Referential Constraint *

  17. None
  18. 5IF"DUJWF3FDPSE8BZ

  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@NBOZVTFST EFQFOEFOUYY FOE

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

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

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

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

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

  27. DMBTT"DDPVOU IBT@NBOZVTFST EFQFOEFOUYY 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

  29. None
  30. gem ‘foreigner’

  31. 7JFX

  32. None
  33. None
  34. None
  35. .BUFSJBMJ[FE7JFX

  36. .BUFSJBMJ[FE7JFX CREATE MATERIALIZED VIEW table_name

  37. .BUFSJBMJ[FE7JFX

  38. .BUFSJBMJ[FE7JFX 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. *OEFY5ZQF • 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)PXUPQJDLVQ • Size of table • Data distributions • Query

    vs update load
  66. 5SBOTBDUJPO

  67. None
  68. None
  69. None
  70. None
  71. None
  72. None
  73. None
  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

  79. None
  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. "$*%

  88. None
  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. 3FQFBUBCMF3FBE

  101. 3FQFBUBCMF3FBE 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