第一方面：30種mysql優(yōu)化sql語句查詢的方法
1.對查詢進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。
2.應(yīng)盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描。
3.應(yīng)盡量避免在 where 子句中對字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：
select id from t where num is null
可以在num上設(shè)置默認(rèn)值0，確保表中num列沒有null值，然后這樣查詢：
select id from t where num=0
4.應(yīng)盡量避免在 where 子句中使用 or 來連接條件，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：
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.下面的查詢也將導(dǎo)致全表掃描：
select id from t where name like '%abc%'
若要提高效率，可以考慮全文檢索。
6.in 和 not in 也要慎用，否則會導(dǎo)致全表掃描，如：
select id from t where num in(1,2,3)
對于連續(xù)的數(shù)值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
7.如果在 where 子句中使用參數(shù)，也會導(dǎo)致全表掃描。因為SQL只有在運(yùn)行時才會解析局部變量，但優(yōu)化程序不能將訪問計劃的選擇推遲到運(yùn)行時;它必須在編譯時進(jìn)行選擇。然而，如果在編譯時建立訪問計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進(jìn)行全表掃描：
select id from t where num=@num
可以改為強(qiáng)制查詢使用索引：
select id from t with(index(索引名)) where num=@num
8.應(yīng)盡量避免在 where 子句中對字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：
select id from t where num/2=100
應(yīng)改為:
select id from t where num=100*2
9.應(yīng)盡量避免在where子句中對字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：
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
應(yīng)改為:
select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'
10.不要在 where 子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無法正確使用索引。
11.在使用索引字段作為條件時，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統(tǒng)使用該索引，否則該索引將不會被使用，并且應(yīng)盡可能的讓字段順序與索引順序相一致。
12.不要寫一些沒有意義的查詢，如需要生成一個空表結(jié)構(gòu)：
select col1,col2 into #t from t where 1=0
這類代碼不會返回任何結(jié)果集，但是會消耗系統(tǒng)資源的，應(yīng)改成這樣：
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是根據(jù)表中數(shù)據(jù)來進(jìn)行查詢優(yōu)化的，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時，SQL查詢可能不會去利用索引，如一表中有字段sex，male、female幾乎各一半，那么即使在sex上建了索引也對查詢效率起不了作用。
15.索引并不是越多越好，索引固然可以提高相應(yīng)的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數(shù)最好不要超過6個，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要。
16.應(yīng)盡可能的避免更新 clustered 索引數(shù)據(jù)列，因為 clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲順序，一旦該列值改變將導(dǎo)致整個表記錄的順序的調(diào)整，會耗費(fèi)相當(dāng)大的資源。若應(yīng)用系統(tǒng)需要頻繁更新 clustered 索引數(shù)據(jù)列，那么需要考慮是否應(yīng)將該索引建為 clustered 索引。
17.盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計為字符型，這會降低查詢和連接的性能，并會增加存儲開銷。這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對于數(shù)字型而言只需要比較一次就夠了。
18.盡可能的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長字段存儲空間小，可以節(jié)省存儲空間，其次對于查詢來說，在一個相對較小的字段內(nèi)搜索效率顯然要高些。
19.任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。
20.盡量使用表變量來代替臨時表。如果表變量包含大量數(shù)據(jù)，請注意索引非常有限(只有主鍵索引)。
21.避免頻繁創(chuàng)建和刪除臨時表，以減少系統(tǒng)表資源的消耗。
22.臨時表并不是不可使用，適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行В纾?dāng)需要重復(fù)引用大型表或常用表中的某個數(shù)據(jù)集時。但是，對于一次性事件，最好使用導(dǎo)出表。
23.在新建臨時表時，如果一次性插入數(shù)據(jù)量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度;如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，應(yīng)先create table，然后insert。
24.如果使用到了臨時表，在存儲過程的最后務(wù)必將所有的臨時表顯式刪除，先 truncate table ，然后 drop table ，這樣可以避免系統(tǒng)表的較長時間鎖定。
25.盡量避免使用游標(biāo)，因為游標(biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過1萬行，那么就應(yīng)該考慮改寫。
26.使用基于游標(biāo)的方法或臨時表方法之前，應(yīng)先尋找基于集的解決方案來解決問題，基于集的方法通常更有效。
27.與臨時表一樣，游標(biāo)并不是不可使用。對小型數(shù)據(jù)集使用 FAST_FORWARD 游標(biāo)通常要優(yōu)于其他逐行處理方法，尤其是在必須引用幾個表才能獲得所需的數(shù)據(jù)時。在結(jié)果集中包括“合計”的例程通常要比使用游標(biāo)執(zhí)行的速度快。如果開發(fā)時間允許，基于游標(biāo)的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好。
28.在所有的存儲過程和觸發(fā)器的開始處設(shè)置 SET NOCOUNT ON ，在結(jié)束時設(shè)置 SET NOCOUNT OFF 。無需在執(zhí)行存儲過程和觸發(fā)器的每個語句后向客戶端發(fā)送 DONE_IN_PROC 消息。
29.盡量避免向客戶端返回大數(shù)據(jù)量，若數(shù)據(jù)量過大，應(yīng)該考慮相應(yīng)需求是否合理。
30.盡量避免大事務(wù)操作，提高系統(tǒng)并發(fā)能力。
上面有幾句寫的有問題。
第二方面：
select Count (*)和Select Count(1)以及Select Count(column)區(qū)別
一般情況下，Select Count (*)和Select Count(1)兩著返回結(jié)果是一樣的
假如表沒有主鍵(Primary key), 那么count(1)比count(*)快，
如果有主鍵的話，那主鍵作為count的條件時候count(主鍵)最快
如果你的表只有一個字段的話那count(*)就是最快的
count(*) 跟 count(1) 的結(jié)果一樣，都包括對NULL的統(tǒng)計，而count(column) 是不包括NULL的統(tǒng)計
第三方面：
索引列上計算引起的索引失效及優(yōu)化措施以及注意事項
創(chuàng)建索引、優(yōu)化查詢以便達(dá)到更好的查詢優(yōu)化效果。但實際上，MySQL有時并不按我們設(shè)計的那樣執(zhí)行查詢。MySQL是根據(jù)統(tǒng)計信息來生成執(zhí)行計劃的，這就涉及索引及索引的刷選率，表數(shù)據(jù)量，還有一些額外的因素。
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.
簡而言之，當(dāng)MYSQL認(rèn)為符合條件的記錄在30%以上，它就不會再使用索引，因為mysql認(rèn)為走索引的代價比不用索引代價大，所以優(yōu)化器選擇了自己認(rèn)為代價最小的方式。事實也的確如此
是MYSQL認(rèn)為記錄是30%以上，而不是實際MYSQL去查完再決定的。都查完了，還用什么索引啊？！
MYSQL會先估算，然后決定是否使用索引。
第四方面：
一，如何判斷SQL的執(zhí)行效率？
通過explain 關(guān)鍵字分析效率低的SQL執(zhí)行計劃。
比如： 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: 輸出結(jié)果集的表。
type: 表示表的連接類型，性能由好到壞，依次為，
system，表中只有一行，常量表。
const，單表中最多有一行匹配，如pramary key 或者 unique index
eq_ref，對于前面的每一行，此表中只查詢一條記錄，比如多表連接中，使用primary key 或 unique index
ref，與eq_ref 類似，區(qū)別在于不是primary key 或qunique index, 而是普通索引。
ref_or_null，與ref 類似，區(qū)別在于查詢中包含對null的查詢。
index_merge，索引合并優(yōu)化
unique_subquery，in的后面是一個查詢主鍵字段的子查詢。
index_subquery，與unique_subquery類似，區(qū)別在于是查詢非唯一索引字段的子查詢。
range，單表中的范圍查詢。
index，對于前面的每一行，都通過查詢索引來得到數(shù)據(jù)。
all，全表掃描。
possible_keys: 表示查詢時，可能使用的索引。
key:表示實際使用的索引。
key_len:索引字段的長度。
rows:掃描行的數(shù)量。
Extra:執(zhí)行情況的說明和描述。
二，如何通過查詢數(shù)據(jù)庫各操作的比例及索引使用次數(shù)來判斷數(shù)據(jù)庫索引及使用是否合理？
1， 命令： >show status like 'Com_%';
結(jié)果：Com_xxx 表示每個xxx語句的執(zhí)行次數(shù)。如：
Com_select, Com_insert,Com_update,Com_delete。
特別的，針對InnoDB：
Innodb_rows_read，select查詢返回的行數(shù)
Innodb_rows_inserted，執(zhí)行insert操作插入的行數(shù) 等等。
通過以上可以查看該數(shù)據(jù)庫的讀寫比例，以便優(yōu)化。
2， 命令：>show status like 'Handler_read%'
查看索引使用次數(shù)。
三，何時匹配到索引？
明確第一索引，第二索引的含義。回表，覆蓋索引等優(yōu)化方法。這里不再贅述。特別的，組合索引只能前綴匹配。同樣，like 關(guān)鍵字也只能前綴匹配索引，通配符不能放在第一個字符。
四，何時不走索引？
1，如果mysql 估計索引使用比全表掃描更慢，則不使用索引。例如幾乎獲取全表數(shù)據(jù)的范圍查詢等等。
2，or 分開的條件，OR前的條件列有索引，后面的沒有索引，那么涉及的索引都不會用到。
3，條件不是組合索引的第一部分，即不滿足前綴左匹配的條件。
4，like 條件以%開始，則不走索引。
5，where 條件后如果是字符串，則一定要用引號括起來，不然自動轉(zhuǎn)換其他類型后，不會走索引。
五，常用SQL優(yōu)化
1，大批量插入數(shù)據(jù)，使用多值語句插入。
insert into test values (1,2),(2,3),(2,4)......
2， 優(yōu)化group by, 默認(rèn)情況下，mysql 會對所有g(shù)roup 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 之間的每個條件列都必須用到索引。
六，深層一些的優(yōu)化
考慮每次查詢時的IO消耗，回表次數(shù)；考慮表設(shè)計時，數(shù)據(jù)結(jié)構(gòu)的不同，比如varchar ,char 區(qū)別；考慮表設(shè)計時每行數(shù)據(jù)的大小，盡量保持在128K以內(nèi)，讓其在一頁內(nèi)，避免跨頁，大數(shù)據(jù)行。