總結一下常見查詢語句的優化方式
1. COUNT的作用
· COUNT(table.filed)統計的該字段非空值的記錄行數
· COUNT(*)或者是COUNT(not nullable field) 統計的是全表的行數
如果要是統計全表記錄數，COUNT(*)效率會比COUNT(not nullable field)要高一點
2. MYISAM的COUNT
一般執行COUNT操作時需要掃描大量的記錄，但是在MyISAM引擎的數據庫中，數據庫把表的記錄數保存起來，所以COUN(*)會非常的快（前提是不包含where條件）
3. 當需要頻繁的使用COUNT時，可以考慮使用匯總表的策略
4. 優化小例子
在MYISAM中進行范圍查詢時，可以減少檢索行數的小技巧
原始的：select count(*) from dictionary where id>5.
優化後：select (select count(*) fromdictionary)-count(*) from dictionary where id<=5
減少查詢次數
優化前：需要兩條語句
Select count(*)from student where area=’SH’
Select count(*)from student where area=’BJ’
優化後：合並成一條
select count(area='SH') as shcount, count(area='BJ') as bjcount from student;
1. 確保ON或USING的字句上有索引
2. 一般情況下只需要在第二個表上創建索引
3. 盡量使 Group by/Order by的表達式中只包含一個表的字段
盡量用關聯代替子查詢
1. 當對關聯查詢執行group by操作時，使用查詢表的標識列作為分組條件效率會比較高
2. 當需要查詢的非group by指定的字段時，正常情況下是無法執行的，可以通過inner join 的形式來彌補
select firstname, lastname from actor inner join(select actor_id, count(*) as cnt from actor group by(actor_id)) using (actor_id)
3. group by默認會對查詢的結果進行排序，數據量很大的時候可能會比較耗資源，如果你不關心查詢結果的順序，可以通過order by null 避免這種不必要的浪費
在進行分頁查詢的時候往往是采用select * from table1 limit 100,20 的方式來提取數據，在處理的過程中會讀取120條數據，然後扔掉100條的offset記錄,最後返回20條記錄給客戶端。如果offset的值非常大，效率上可能會有影響，可以嘗試
1. 可以通過覆蓋索引+inner join的方式來重寫sql
select field1,field2,field3
from table1
inner join
(select id from table1 limit 100, 20) as temp
using(id)
2. 如果可以計算出明確的開始點和結束點，可以轉換成 between and 的方式,這種方式只會掃描指定的行數，效率比較高
Select * from table1 between 100 and 120.
3. 可以通過位置標簽的方式，來減少需要檢索的記錄數
例如 從某個位置開始。 Select * from table1 whereid>100 limit 20
下圖列出了三種方式的效率對比
在進行分頁處理的時候往往需要知道記錄的總數，然後用這些總數生成頁碼。獲取總數往往是使用count或是伴隨一次全表查詢得到的，這個過程也是檢索所有的記錄，然後再丟掉。為了避免這種浪費可以采取兩種策略
· 把頁碼換成“下一頁”的方式，這樣就只需要去取固定的條數
· 一次性讀取1000條，當一千條使用完後，采用“獲取更多記錄”的方式再獲取1000條
· 使用的時候要把每個優化手段下推到每個子集中（http://blog.csdn.net/eric_sunah/article/details/17290641）
· Union操作會對處理後的結果執行distinct操作，這在很多時候是沒有必要的。可以采用union all來避免這個問題
合理靈活的使用自定義變量往往會給程序的性能帶來意想不到的效果，但往往也會帶來與其他數據庫系統的兼容性問題。
下面列出幾個自定義變量使用的小例子
· 行號
mysql> set @rownumber:=0;
mysql> select mean, @rownumber:=@rownumber+1 from dictionary limit10;
· 避免重復查詢剛剛更新的數據
在更新完一條記錄後，往往需要再次執行select查詢剛剛更新過的記錄
通過變量可以避免這種問題
Mysql>set @updaterow:=null;
mysql> update dictionary set mean='update get variable' where id=100and @updaterow:=now();
· 統計更新和插入的數量
mysql> set @x:=0; //define avariable
mysql> insert into dictionary (id,mean) values(3,'duplicate') onduplicate key update mean=values(mean)+(0*(@x:=@x+1)); //insert a duplicaterecord
mysql> select @x; //get x value, it’s indicator duplicate times
有時候可以借助專門的查詢分析工具來發現自己的問題，比如pt-query-advisor(http://www.percona.com/doc/percona-toolkit/2.1/pt-query-advisor.html)
