1、count(1)和count(primary_key) 優(yōu)于 count(*)
很多人為了統(tǒng)計記錄條數(shù)，就使用 count(1) 和 count(primary_key) 而不是 count(*) ，他們認(rèn)為這樣性能更好，其實(shí)這是一個誤區(qū)。對于有些場景，這樣做可能性能會更差，應(yīng)為數(shù)據(jù)庫對 count(*) 計數(shù)操作做了一些特別的優(yōu)化。
2、count(column) 和 count(*) 是一樣的
這個誤區(qū)甚至在很多的資深工程師或者是 DBA 中都普遍存在，很多人都會認(rèn)為這是理所當(dāng)然的。實(shí)際上，count(column) 和 count(*) 是一個完全不一樣的操作，所代表的意義也完全不一樣。
count(column) 是表示結(jié)果集中有多少個column字段不為空的記錄
這個誤區(qū)主要存在于大量的開發(fā)人員中，主要原因是對數(shù)據(jù)庫的存儲原理不是太了解。
實(shí)際上，大多數(shù)關(guān)系型數(shù)據(jù)庫都是按照行(row)的方式存儲，而數(shù)據(jù)存取操作都是以一個固定大小的IO單元(被稱作 block 或者 page)為單位，一般為4KB，8KB… 大多數(shù)時候，每個IO單元中存儲了多行，每行都是存儲了該行的所有字段(lob等特殊類型字段除外)。
所以，我們是取一個字段還是多個字段，實(shí)際上數(shù)據(jù)庫在表中需要訪問的數(shù)據(jù)量其實(shí)是一樣的。
1、盡量少 join
MySQL 的優(yōu)勢在于簡單，但這在某些方面其實(shí)也是其劣勢。MySQL 優(yōu)化器效率高，但是由于其統(tǒng)計信息的量有限，優(yōu)化器工作過程出現(xiàn)偏差的可能性也就更多。對于復(fù)雜的多表 Join，一方面由于其優(yōu)化器受限，再者在 Join 這方面所下的功夫還不夠，所以性能表現(xiàn)離 Oracle 等關(guān)系型數(shù)據(jù)庫前輩還是有一定距離。但如果是簡單的單表查詢，這一差距就會極小甚至在有些場景下要優(yōu)于這些數(shù)據(jù)庫前輩。
2、盡量少排序
排序操作會消耗較多的 CPU 資源，所以減少排序可以在緩存命中率高等 IO 能力足夠的場景下會較大影響 SQL 的響應(yīng)時間。
對于MySQL來說，減少排序有多種辦法，比如：
上面誤區(qū)中提到的通過利用索引來排序的方式進(jìn)行優(yōu)化
減少參與排序的記錄條數(shù)
非必要不對數(shù)據(jù)進(jìn)行排序
3、盡量避免 select *
很多人看到這一點(diǎn)后覺得比較難理解，上面不是在誤區(qū)中剛剛說 select 子句中字段的多少并不會影響到讀取的數(shù)據(jù)嗎?
是的，大多數(shù)時候并不會影響到 IO 量，但是當(dāng)我們還存在 order by 操作的時候，select 子句中的字段多少會在很大程度上影響到我們的排序效率，這一點(diǎn)可以通過我之前一篇介紹 MySQL ORDER BY 的實(shí)現(xiàn)分析 的文章中有較為詳細(xì)的介紹。
此外，上面誤區(qū)中不是也說了，只是大多數(shù)時候是不會影響到 IO 量，當(dāng)我們的查詢結(jié)果僅僅只需要在索引中就能找到的時候，還是會極大減少 IO 量的。
4、盡量用 join 代替子查詢
雖然 Join 性能并不佳，但是和 MySQL 的子查詢比起來還是有非常大的性能優(yōu)勢。MySQL 的子查詢執(zhí)行計劃一直存在較大的問題，雖然這個問題已經(jīng)存在多年，但是到目前已經(jīng)發(fā)布的所有穩(wěn)定版本中都普遍存在，一直沒有太大改善。雖然官方也在很早就承認(rèn)這一問題，并且承諾盡快解決，但是至少到目前為止我們還沒有看到哪一個版本較好的解決了這一問題。
5、盡量少 or
當(dāng) where 子句中存在多個條件以“或”并存的時候，MySQL 的優(yōu)化器并沒有很好的解決其執(zhí)行計劃優(yōu)化問題，再加上 MySQL 特有的 SQL 與 Storage 分層架構(gòu)方式，造成了其性能比較低下，很多時候使用 union all 或者是union(必要的時候)的方式來代替“or”會得到更好的效果。
6、盡量用 union all 代替 union
union 和 union all 的差異主要是前者需要將兩個(或者多個)結(jié)果集合并后再進(jìn)行唯一性過濾操作，這就會涉及到排序，增加大量的 CPU 運(yùn)算，加大資源消耗及延遲。所以當(dāng)我們可以確認(rèn)不可能出現(xiàn)重復(fù)結(jié)果集或者不在乎重復(fù)結(jié)果集的時候，盡量使用 union all 而不是 union。
7、盡量早過濾
這一優(yōu)化策略其實(shí)最常見于索引的優(yōu)化設(shè)計中(將過濾性更好的字段放得更靠前)。
在 SQL 編寫中同樣可以使用這一原則來優(yōu)化一些 Join 的 SQL。比如我們在多個表進(jìn)行分頁數(shù)據(jù)查詢的時候，我們最好是能夠在一個表上先過濾好數(shù)據(jù)分好頁，然后再用分好頁的結(jié)果集與另外的表 Join，這樣可以盡可能多的減少不必要的 IO 操作，大大節(jié)省 IO 操作所消耗的時間。
8、避免類型轉(zhuǎn)換
這里所說的“類型轉(zhuǎn)換”是指 where 子句中出現(xiàn) column 字段的類型和傳入的參數(shù)類型不一致的時候發(fā)生的類型轉(zhuǎn)換：
人為在column_name 上通過轉(zhuǎn)換函數(shù)進(jìn)行轉(zhuǎn)換
直接導(dǎo)致 MySQL(實(shí)際上其他數(shù)據(jù)庫也會有同樣的問題)無法使用索引，如果非要轉(zhuǎn)換，應(yīng)該在傳入的參數(shù)上進(jìn)行轉(zhuǎn)換
由數(shù)據(jù)庫自己進(jìn)行轉(zhuǎn)換
如果我們傳入的數(shù)據(jù)類型和字段類型不一致，同時我們又沒有做任何類型轉(zhuǎn)換處理，MySQL 可能會自己對我們的數(shù)據(jù)進(jìn)行類型轉(zhuǎn)換操作，也可能不進(jìn)行處理而交由存儲引擎去處理，這樣一來，就會出現(xiàn)索引無法使用的情況而造成執(zhí)行計劃問題。
9、優(yōu)先優(yōu)化高并發(fā)的 SQL，而不是執(zhí)行頻率低某些“大”SQL
對于破壞性來說，高并發(fā)的 SQL 總是會比低頻率的來得大，因?yàn)楦卟l(fā)的 SQL 一旦出現(xiàn)問題，甚至不會給我們?nèi)魏未⒌臋C(jī)會就會將系統(tǒng)壓跨。而對于一些雖然需要消耗大量 IO 而且響應(yīng)很慢的 SQL，由于頻率低，即使遇到，最多就是讓整個系統(tǒng)響應(yīng)慢一點(diǎn)，但至少可能撐一會兒，讓我們有緩沖的機(jī)會。
10、從全局出發(fā)優(yōu)化，而不是片面調(diào)整
SQL 優(yōu)化不能是單獨(dú)針對某一個進(jìn)行，而應(yīng)充分考慮系統(tǒng)中所有的 SQL，尤其是在通過調(diào)整索引優(yōu)化 SQL 的執(zhí)行計劃的時候，千萬不能顧此失彼，因小失大。
11、盡可能對每一條運(yùn)行在數(shù)據(jù)庫中的SQL進(jìn)行 explain
優(yōu)化 SQL，需要做到心中有數(shù)，知道 SQL 的執(zhí)行計劃才能判斷是否有優(yōu)化余地，才能判斷是否存在執(zhí)行計劃問題。在對數(shù)據(jù)庫中運(yùn)行的 SQL 進(jìn)行了一段時間的優(yōu)化之后，很明顯的問題 SQL 可能已經(jīng)很少了，大多都需要去發(fā)掘，這時候就需要進(jìn)行大量的 explain 操作收集執(zhí)行計劃，并判斷是否需要進(jìn)行優(yōu)化。
