第一方面：30種mysql優化sql語句查詢的方法
1.對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。
2.應盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。
3.應盡量避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：
select id from t where num is null
可以在num上設置默認值0，確保表中num列沒有null值，然后這樣查詢：
select id from t where num=0
4.應盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如：
select id from t where num=10 or num=20
可以這樣查詢：
select id from t where num=10
union all
select id from t where num=20
5.下面的查詢也將導致全表掃描：
select id from t where name like '%abc%'
若要提高效率，可以考慮全文檢索。
6.in 和 not in 也要慎用，否則會導致全表掃描，如：
select id from t where num in(1,2,3)
對于連續的數值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
7.如果在 where 子句中使用參數，也會導致全表掃描。因為SQL只有在運行時才會解析局部變量，但優化程序不能將訪問計劃的選擇推遲到運行時;它必須在編譯時進行選擇。然而，如果在編譯時建立訪問計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描：
select id from t where num=@num
可以改為強制查詢使用索引：
select id from t with(index(索引名)) where num=@num
8.應盡量避免在 where 子句中對字段進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描。如：
select id from t where num/2=100
應改為:
select id from t where num=100*2
9.應盡量避免在where子句中對字段進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。如：
select id from t where substring(name,1,3)='abc'--name以abc開頭的id
select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id
應改為:
select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'
10.不要在 where 子句中的“=”左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。
11.在使用索引字段作為條件時，如果該索引是復合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引，否則該索引將不會被使用，并且應盡可能的讓字段順序與索引順序相一致。
12.不要寫一些沒有意義的查詢，如需要生成一個空表結構：
select col1,col2 into #t from t where 1=0
這類代碼不會返回任何結果集，但是會消耗系統資源的，應改成這樣：
create table #t(...)
13.很多時候用 exists 代替 in 是一個好的選擇：
select num from a where num in(select num from b)
用下面的語句替換：
select num from a where exists(select 1 from b where num=a.num)
14.并不是所有索引對查詢都有效，SQL是根據表中數據來進行查詢優化的，當索引列有大量數據重復時，SQL查詢可能不會去利用索引，如一表中有字段sex，male、female幾乎各一半，那么即使在sex上建了索引也對查詢效率起不了作用。
15.索引并不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。
16.應盡可能的避免更新 clustered 索引數據列，因為 clustered 索引數據列的順序就是表記錄的物理存儲順序，一旦該列值改變將導致整個表記錄的順序的調整，會耗費相當大的資源。若應用系統需要頻繁更新 clustered 索引數據列，那么需要考慮是否應將該索引建為 clustered 索引。
17.盡量使用數字型字段，若只含數值信息的字段盡量不要設計為字符型，這會降低查詢和連接的性能，并會增加存儲開銷。這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對于數字型而言只需要比較一次就夠了。
18.盡可能的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長字段存儲空間小，可以節省存儲空間，其次對于查詢來說，在一個相對較小的字段內搜索效率顯然要高些。
19.任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。
20.盡量使用表變量來代替臨時表。如果表變量包含大量數據，請注意索引非常有限(只有主鍵索引)。
21.避免頻繁創建和刪除臨時表，以減少系統表資源的消耗。
22.臨時表并不是不可使用，適當地使用它們可以使某些例程更有效，例如，當需要重復引用大型表或常用表中的某個數據集時。但是，對于一次性事件，最好使用導出表。
23.在新建臨時表時，如果一次性插入數據量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度;如果數據量不大，為了緩和系統表的資源，應先create table，然后insert。
24.如果使用到了臨時表，在存儲過程的最后務必將所有的臨時表顯式刪除，先 truncate table ，然后 drop table ，這樣可以避免系統表的較長時間鎖定。
25.盡量避免使用游標，因為游標的效率較差，如果游標操作的數據超過1萬行，那么就應該考慮改寫。
26.使用基于游標的方法或臨時表方法之前，應先尋找基于集的解決方案來解決問題，基于集的方法通常更有效。
27.與臨時表一樣，游標并不是不可使用。對小型數據集使用 FAST_FORWARD 游標通常要優于其他逐行處理方法，尤其是在必須引用幾個表才能獲得所需的數據時。在結果集中包括“合計”的例程通常要比使用游標執行的速度快。如果開發時間允許，基于游標的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好。
28.在所有的存儲過程和觸發器的開始處設置 SET NOCOUNT ON ，在結束時設置 SET NOCOUNT OFF 。無需在執行存儲過程和觸發器的每個語句后向客戶端發送 DONE_IN_PROC 消息。
29.盡量避免向客戶端返回大數據量，若數據量過大，應該考慮相應需求是否合理。
30.盡量避免大事務操作，提高系統并發能力。
上面有幾句寫的有問題。
第二方面：
select Count (*)和Select Count(1)以及Select Count(column)區別
一般情況下，Select Count (*)和Select Count(1)兩著返回結果是一樣的
假如表沒有主鍵(Primary key), 那么count(1)比count(*)快，
如果有主鍵的話，那主鍵作為count的條件時候count(主鍵)最快
如果你的表只有一個字段的話那count(*)就是最快的
count(*) 跟 count(1) 的結果一樣，都包括對NULL的統計，而count(column) 是不包括NULL的統計
第三方面：
索引列上計算引起的索引失效及優化措施以及注意事項
創建索引、優化查詢以便達到更好的查詢優化效果。但實際上，MySQL有時并不按我們設計的那樣執行查詢。MySQL是根據統計信息來生成執行計劃的，這就涉及索引及索引的刷選率，表數據量，還有一些額外的因素。
Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.
簡而言之，當MYSQL認為符合條件的記錄在30%以上，它就不會再使用索引，因為mysql認為走索引的代價比不用索引代價大，所以優化器選擇了自己認為代價最小的方式。事實也的確如此
是MYSQL認為記錄是30%以上，而不是實際MYSQL去查完再決定的。都查完了，還用什么索引啊？！
MYSQL會先估算，然后決定是否使用索引。
第四方面：
一，如何判斷SQL的執行效率？
通過explain 關鍵字分析效率低的SQL執行計劃。
比如： explain select sum(moneys) from sales a, company b where a.company_id = b.company_id and a.year = 2006;
id : 1
select_type: SIMPLE
table:
type:
1.row:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a ALL NULL NULL NULL NULL 1000 Using where
2.row
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE b ref ind_company_id ind_company_id 5 sakila.a.company_id 1 Using where;Using index
select_type: SIMPLE, 簡單表，不使用表連接或子查詢；PRIMARY，主查詢，即外層的查詢；UNION，UNION中的第二個查詢或后面的查詢；SUMQUERY，子查詢中的第一個SELECT。
table: 輸出結果集的表。
type: 表示表的連接類型，性能由好到壞，依次為，
system，表中只有一行，常量表。
const，單表中最多有一行匹配，如pramary key 或者 unique index
eq_ref，對于前面的每一行，此表中只查詢一條記錄，比如多表連接中，使用primary key 或 unique index
ref，與eq_ref 類似，區別在于不是primary key 或qunique index, 而是普通索引。
ref_or_null，與ref 類似，區別在于查詢中包含對null的查詢。
index_merge，索引合并優化
unique_subquery，in的后面是一個查詢主鍵字段的子查詢。
index_subquery，與unique_subquery類似，區別在于是查詢非唯一索引字段的子查詢。
range，單表中的范圍查詢。
index，對于前面的每一行，都通過查詢索引來得到數據。
all，全表掃描。
possible_keys: 表示查詢時，可能使用的索引。
key:表示實際使用的索引。
key_len:索引字段的長度。
rows:掃描行的數量。
Extra:執行情況的說明和描述。
二，如何通過查詢數據庫各操作的比例及索引使用次數來判斷數據庫索引及使用是否合理？
1， 命令： >show status like 'Com_%';
結果：Com_xxx 表示每個xxx語句的執行次數。如：
Com_select, Com_insert,Com_update,Com_delete。
特別的，針對InnoDB：
Innodb_rows_read，select查詢返回的行數
Innodb_rows_inserted，執行insert操作插入的行數 等等。
通過以上可以查看該數據庫的讀寫比例，以便優化。
2， 命令：>show status like 'Handler_read%'
查看索引使用次數。
三，何時匹配到索引？
明確第一索引，第二索引的含義。回表，覆蓋索引等優化方法。這里不再贅述。特別的，組合索引只能前綴匹配。同樣，like 關鍵字也只能前綴匹配索引，通配符不能放在第一個字符。
四，何時不走索引？
1，如果mysql 估計索引使用比全表掃描更慢，則不使用索引。例如幾乎獲取全表數據的范圍查詢等等。
2，or 分開的條件，OR前的條件列有索引，后面的沒有索引，那么涉及的索引都不會用到。
3，條件不是組合索引的第一部分，即不滿足前綴左匹配的條件。
4，like 條件以%開始，則不走索引。
5，where 條件后如果是字符串，則一定要用引號括起來，不然自動轉換其他類型后，不會走索引。
五，常用SQL優化
1，大批量插入數據，使用多值語句插入。
insert into test values (1,2),(2,3),(2,4)......
2， 優化group by, 默認情況下，mysql 會對所有group by C1,C2,C3 ... 的字段排序，與order by C1,C2,C3 類似，所以在group by 中增加相同列的order by 性能沒什么影響。
如果用戶想避免排序帶來的影響，可以顯式指定不排序，后面加上order by NULL。
3，order by 后面的順序與索引順序相同，且與where 中使用的條件相同，且是索引，則才會走真正索引。
4，in + 子查詢的 SQL 盡量用join 連接來代替。
5，OR 之間的每個條件列都必須用到索引。
六，深層一些的優化
考慮每次查詢時的IO消耗，回表次數；考慮表設計時，數據結構的不同，比如varchar ,char 區別；考慮表設計時每行數據的大小，盡量保持在128K以內，讓其在一頁內，避免跨頁，大數據行。