1盒件、在Model使用sql語(yǔ)句
before_destroy :clean_data
def clean_data
teacher_id = self.id
sql = "delete * from train_teacher where nurse_info_id = #{teacher_id}"
#這個(gè)方法是私有方法褪迟，必須在Model中使用
ActiveRecord::Base.connection.exec(sql)
end
2厕妖、數(shù)據(jù)庫(kù)的優(yōu)化
一. N+1 queries 問(wèn)題
# model
class User < ActieRecord::Base
has_one :car
end
class Car < ActiveRecord::Base
belongs_to :user
end
# your controller
def index
@users = User.page(params[:page])
end
# view
<% @users.each do |user| %>
<%= user.car.name %>
<% end %>
當(dāng)我們?cè)赩iew中使用user.car.name的時(shí)候塘偎，會(huì)導(dǎo)致N+1 queries(多次查詢(xún)數(shù)據(jù)庫(kù))剧腻，假設(shè)User有10筆，這程式會(huì)產(chǎn)生出11筆Queries隔嫡，一筆是查
User甸怕，另外10筆是一筆一筆去查Car甘穿，嚴(yán)重拖慢效能腮恩。
SELECT * FROM `users` LIMIT 10 OFFSET 0
SELECT * FROM `cars` WHERE (`cars`.`user_id` = 1)
SELECT * FROM `cars` WHERE (`cars`.`user_id` = 2)
SELECT * FROM `cars` WHERE (`cars`.`user_id` = 3)
...
...
...
SELECT * FROM `cars` WHERE (`cars`.`user_id` = 10)
解決方法，加上includes：
# your controller
def index
@users = User.includes(:car).page(params[:page])
end
如此SQL query就只有兩個(gè)温兼，只用一個(gè)就查出所有Cars資料秸滴。
SELECT * FROM `users` LIMIT 10 OFFSET 0
SELECT * FROM `cars` WHERE (`cars`.`user_id` IN('1','2','3','4','5','6','7','8',' 9','10'))
二. 索引
需要加索引的列：
- 外鍵(Foreign Key)
- 需要排序的列
- 被查詢(xún)的列
- 會(huì)被group的列
三. 使用select
ActiveRecord會(huì)將數(shù)據(jù)庫(kù)中的所有列取出來(lái)，浪費(fèi)內(nèi)存募判。
#只取出id,name,description這些字段
Event.select(:id, :name, :description).limit(10)
四. 使用計(jì)數(shù)器
在ActiveRecord中經(jīng)常要計(jì)算has_many的Model有多少關(guān)聯(lián)的數(shù)據(jù)荡含。
<% @topics.each do |topic| %>
主題：<%= topic.subject %>
回復(fù)數(shù)：<%= topic.posts.size %>
<% end %>
這個(gè)時(shí)候會(huì)產(chǎn)生很多SQL查詢(xún)。
SELECT * FROM `posts` LIMIT 5 OFFSET 0
SELECT count(*) AS count_all FROM `posts` WHERE (`posts`.topic_id = 1 )
SELECT count(*) AS count_all FROM `posts` WHERE (`posts`.topic_id = 2 )
SELECT count(*) AS count_all FROM `posts` WHERE (`posts`.topic_id = 3 )
SELECT count(*) AS count_all FROM `posts` WHERE (`posts`.topic_id = 4 )
SELECT count(*) AS count_all FROM `posts` WHERE (`posts`.topic_id = 5 )
使用counter cache會(huì)把這個(gè)數(shù)字存進(jìn)數(shù)據(jù)庫(kù)届垫。必須在Topic這個(gè)Model增加一個(gè)字段释液。
rails g migration add_posts_count_to_topic
編輯Migration文件
class AddPostsCountToTopic < ActiveRecord::Migration
def change
add_column :topics, :posts_count, :integer, :default => 0
Topic.pluck(:id).each do |i|
Topic.reset_counters(i, :posts) # 全部重算一次
end
end
end
編輯Model文件，加入counter_cache => true
class Topic < ActiveRecord::Base
has_many :posts
end
class Posts < ActiveRecord::Base
belongs_to :topic, :counter_cache => true
end
這樣topic.posts.size會(huì)自動(dòng)轉(zhuǎn)化為topic.posts_count
五. 查詢(xún)優(yōu)化
如果要查出所有的數(shù)據(jù)装处，不要用all误债，all會(huì)一次把所有的數(shù)據(jù)取到內(nèi)存中去。
六. 逆正規(guī)化
