# 工作一年半我踩過 Active Record 的雷 五倍紅寶石 **廢的 Fred** --- ### 觀念篇 - scope 注意事項 - subquery - n + 1 的問題 - connection pool --- ### scope --- 把常用的 query 放進 scope 讓他變漂亮 ```ruby class User < ApplicationRecord scope :adult, ->{ where( 'age >= 18') } end adult_users = User.adult ``` --- ### scope 新手誤區 - 每次查詢都要回傳 relation - 每次回傳都要是自己 --- ### scope 新手誤區 ```ruby class User < ApplicationRecord scope :names, ->{ map(&:name) } # 誰准你把 scope 當 method 用？ scope :posts, ->(user_id){ Post.where(user_id: user_id)} # 這到底是.... scope :oldest, ->{order(:age).last} # 這個不是 relation end ``` --- ### scope 寫好可以讓 code 變美 ```ruby class Address < ApplicationRecord belongs_to :location end class User < ApplicationRecord scope :lives_in_taipei, -> do joins(address: :location) .where( address: {location: 'taipei'} ) end end class Post < ApplicationRecord belongs_to :author, class_name: "User" scope :sports, ->{ where(category: 'sports')} scope :sports_in_taipei, -> do joins(:author).merge(User.lives_in_taipei) end end some_posts = Post.sports_in_taipei ``` --- ### Subquery --- 1. `where.... IN...` 2. `INNER JOIN table ON ..` 3. `FROM table1 , (SUBQUERY TABLE) as table2` --- ### 要注意 subquery 效能問題 ```sql SELECT * FROM TABLE WHERE COLUMN IN (SELECT COLUMN FROM TABLE) ``` --- - 這樣做是很耗資源的(要看 db 有沒有優化)，因為sql會先處理內部查詢，查完才做外面的查詢 - 通常 JOIN 效能會比 subquery 好 - 有些 DBMS 會優化 query 所以要看實際執行效率 --- [15個優化你的SQL QUERY的方式](http://blog.davidou.org/archives/609) [rewriting subqueries](https://dev.mysql.com/doc/refman/5.7/en/rewriting-subqueries.html) [MySQL 子查詢及其優化](https://codertw.com/ios/59303/) [where in 效能](https://ithelp.ithome.com.tw/questions/10155255) [pg where in 效能](https://dba.stackexchange.com/questions/91247/optimizing-a-postgres-query-with-a-large-in) [卡米 where in 效能](http://etrex.blogspot.com/2017/08/sql-where-in.html) [MySQL EXPLAIN 獨立子查詢dependent subquery 優化示例](https://www.itread01.com/content/1549843400.html) --- ### N + 1 問題 --- 如果要用到，先用 includes 讀進來 ```ruby adults = User.adults.includes(posts: :comments) adults.each do |adult| adult.posts.each do |post| puts "#{adult.name}, post: #{post.title}, lastest comment: #{post.comments.last}" end end ``` --- ### N + 1 or 效能檢查工具 - [active record query trace](https://github.com/brunofacca/active-record-query-trace) (查哪邊噴出 query 來，自己用過) - [Bullet](https://github.com/flyerhzm/bullet) - [Mini Profiler](https://github.com/MiniProfiler/rack-mini-profiler) --- ### connection pool --- 每次建立連線很貴  ##### credit: http://peggg327.blogspot.com/2014/11/connection-pool.html --- - 先連好，要用到再去領 - pg 預設是 100 個  ##### credit: http://peggg327.blogspot.com/2014/11/connection-pool.html --- ### Rails 的 connection pool 以下是每個 process 的 pool 數 ```yml # config/database.yml development: pool: 5 ``` --- ### Rails 的 connection pool sidekiq 要注意 https://dev.to/appaloosastore/active-record-sidekiq-pools-and-threads-18d5 --- 私心推薦 - [Upcase - Advanced ActiveRecord Querying](https://thoughtbot.com/upcase/advanced-activerecord-querying?utm_source=giantrobots&utm_medium=blog&utm_campaign=aarq-announcement) - [Upcase - Advanced Query Optimization](https://thoughtbot.com/upcase/videos/advanced-query-optimization) --- ### 進階篇 - Transaction - Lock - Mulitple DB in Rails --- ### Transaction --- ### 交易的四個特性 ACID - Atomicity (原子性、不可分割)：全執行、全部不執行。 - Consistency (一致性)：所有資料必須符合預設的驗證規則。例如銀行轉帳 - Isolation (隔離性)：多個交易可以獨立、同時執行，不會互相干擾。 - Durability (持久性)：交易完成後，異動結果須完整的保留。 [參考資料](https://openhome.cc/Gossip/EJB3Gossip/TransactionABC.html) --- ### Transaction: COMMIT or ROLLBACK --- #### Transaction in MySql ```sql -- 記得要先把 autocommit 關掉 -- 1. start a new transaction START TRANSACTION; -- 2. Get the latest order number SELECT @orderNumber:=MAX(orderNUmber)+1 FROM orders; -- 3. insert a new order for customer 145 INSERT INTO orders(orderNumber, orderDate, requiredDate, shippedDate, status, customerNumber) VALUES(@orderNumber, '2005-05-31', '2005-06-10', '2005-06-11', 'In Process', 145); -- 4. Insert order line items INSERT INTO orderdetails(orderNumber, productCode, quantityOrdered, priceEach, orderLineNumber) VALUES(@orderNumber,'S18_1749', 30, '136', 1), (@orderNumber,'S18_2248', 50, '55.09', 2); -- 5. commit changes COMMIT; ``` --- #### Transaction in Rails ```ruby ActiveRecord::Base.transaction do david.withdrawal(100) mary.deposit(100) end ``` [參考資料](https://api.rubyonrails.org/classes/ActiveRecord/Transactions/ClassMethods.html) --- #### 注意 commit 之後才會寫入資料庫 ```ruby ActiveRecord::Base.transaction do book = Book.find(1) book.sold! unless book.sold? book2 = Book.find(1) book.sold! unless book.sold? end ``` --- ### Lock --- [鎖定模式 Lock Mode](https://docs.microsoft.com/zh-tw/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15#lock_modes) 太複雜了自己看 --- ### Race Condition  --- ### Active Record 的鎖 - [樂觀鎖 Optimistic Locking](https://api.rubyonrails.org/classes/ActiveRecord/Locking/Optimistic.html) - [悲觀鎖 Pessimistic Locking](https://api.rubyonrails.org/classes/ActiveRecord/Locking/Pessimistic.html) --- ### 樂觀鎖 - 要有 `lock_version` 的欄位 - 可以有多個 thread SELECT, 但是會依據 lock_version 鎖定 update - 在 web form 的話要塞一個 `lock_version` hidding field 給它 --- 範例 ```ruby p1 = Person.find(1) p2 = Person.find(1) p1.first_name = "Michael" p1.save p2.first_name = "should fail" p2.save # Raises an ActiveRecord::StaleObjectError p1 = Person.find(1) p2 = Person.find(1) p1.first_name = "Michael" p1.save p2.destroy # Raises an ActiveRecord::StaleObjectError ``` [更多範例](http://railscasts.com/episodes/59-optimistic-locking-revised) --- --- ### 悲觀鎖 - 利用 `SELECT ... FOR UPDATE` 鎖定 - 其他的 thread 會被 block 不能 select - https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html - 要搭配 transaction 服用 - commit 後才會解鎖 --- ### 悲觀鎖 ```ruby Account.transaction do # select * from accounts where name = 'shugo' limit 1 for update nowait shugo = Account.lock("FOR UPDATE NOWAIT").find_by(name: "shugo") yuko = Account.lock("FOR UPDATE NOWAIT").find_by(name: "yuko") shugo.balance -= 100 shugo.save! yuko.balance += 100 yuko.save! end ``` --- whit_lock ```ruby Account.transaction do account = Account.first account.with_lock do # This block is called within a transaction, # account is already locked. account.balance -= 100 account.save! end end ``` --- 參考資料 - https://devfun.tw/t/db-lock/529 - https://ruby-china.org/topics/28963 - https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html --- ### Multi DB in Rails --- ### Rails 6 很簡單 --- _database.yml_ ```yml production: primary: database: my_primary_database user: root adapter: mysql primary_replica: database: my_primary_database user: root_readonly adapter: mysql replica: true animals: database: my_animals_database user: animals_root adapter: mysql migrations_paths: db/animals_migrate animals_replica: database: my_animals_database user: animals_readonly adapter: mysql replica: true ``` --- model ```ruby class AnimalsBase < ApplicationRecord self.abstract_class = true connects_to database: { writing: :animals, reading: :animals_replica } end ``` --- ### before Rails 6 --- ### establish_connection ```ruby ActiveRecord::Base.establish_connection( :adapter => "mysql", :host => "localhost", :username => "myuser", :password => "mypass", :database => "somedatabase" ) ``` https://apidock.com/rails/ActiveRecord/Base/establish_connection/class --- 不要這樣幹 ```ruby class SecondBaseUser < ActiveRecord::Base establish_connection DB_SECOND end ``` 每次 new 的時候會建一次 connection --- 在上層繼承 ```ruby module Second class Base < ActiveRecord::Base self.abstract_class = true establish_connection DB_SECOND end class User < Second::Base end end ``` --- 隨機附贈 second db migration rake https://medium.com/@primazp/multiple-databases-in-rails-part-1-setup-48f6d207685a --- ### Doorkeeper 隱藏版設定 _config/initialize/doorkeeper.rb_ ```ruby Doorkeeper.configure do orm :active_record # ... active_record_options(establish_connection: "portal_#{Rails.env}".to_sym) end ``` --- ### 另外最近發生的事 --- ### index 問題 - index 怎麼建 - 怎麼下 index 比較好 --- ### NULL KEY --- ## 以上