一、MySQL 數(shù)據庫性能優(yōu)化之SQL優(yōu)化（載錄于：http://lib.csdn.net/article/mysql/5028）

注：這篇文章是以MySQL為背景，很多內容同時適用于其他關系型數(shù)據庫，需要有一些索引知識為基礎

優(yōu)化目標

減少 IO 次數(shù)

IO永遠是數(shù)據庫最容易瓶頸的地方，這是由數(shù)據庫的職責所決定的，大部分數(shù)據庫操作中超過90%的時間都是 IO 操作所占用的，減少 IO 次數(shù)是 SQL 優(yōu)化中需要第一優(yōu)先考慮，當然，也是收效最明顯的優(yōu)化手段。

降低 CPU 計算

除了 IO 瓶頸之外，SQL優(yōu)化中需要考慮的就是 CPU 運算量的優(yōu)化了。order by, group by,distinct … 都是消耗 CPU 的大戶（這些操作基本上都是 CPU 處理內存中的數(shù)據比較運算）。當我們的 IO 優(yōu)化做到一定階段之后，降低 CPU 計算也就成為了我們 SQL 優(yōu)化的重要目標

優(yōu)化方法

改變 SQL 執(zhí)行計劃

明確了優(yōu)化目標之后，我們需要確定達到我們目標的方法。對于 SQL 語句來說，達到上述2個目標的方法其實只有一個，那就是改變 SQL 的執(zhí)行計劃，讓他盡量“少走彎路”，盡量通過各種“捷徑”來找到我們需要的數(shù)據，以達到 “減少 IO 次數(shù)” 和 “降低 CPU 計算” 的目標

常見誤區(qū)

count(1)和count(primary_key) 優(yōu)于 count(*)

很多人為了統(tǒng)計記錄條數(shù)，就使用 count(1) 和 count(primary_key) 而不是 count(*) ，他們認為這樣性能更好，其實這是一個誤區(qū)。對于有些場景，這樣做可能性能會更差，應為數(shù)據庫對 count(*) 計數(shù)操作做了一些特別的優(yōu)化。

count(column) 和 count(*) 是一樣的

這個誤區(qū)甚至在很多的資深工程師或者是 DBA 中都普遍存在，很多人都會認為這是理所當然的。實際上，count(column) 和 count(*) 是一個完全不一樣的操作，所代表的意義也完全不一樣。

count(column) 是表示結果集中有多少個column字段不為空的記錄

count(*) 是表示整個結果集有多少條記錄

select a,b from … 比 select a,b,c from … 可以讓數(shù)據庫訪問更少的數(shù)據量

這個誤區(qū)主要存在于大量的開發(fā)人員中，主要原因是對數(shù)據庫的存儲原理不是太了解。

實際上，大多數(shù)關系型數(shù)據庫都是按照行（row）的方式存儲，而數(shù)據存取操作都是以一個固定大小的IO單元（被稱作 block 或者 page）為單位，一般為4KB，8KB… 大多數(shù)時候，每個IO單元中存儲了多行，每行都是存儲了該行的所有字段（lob等特殊類型字段除外）。

所以，我們是取一個字段還是多個字段，實際上數(shù)據庫在表中需要訪問的數(shù)據量其實是一樣的。

當然，也有例外情況，那就是我們的這個查詢在索引中就可以完成，也就是說當只取 a,b兩個字段的時候，不需要回表，而c這個字段不在使用的索引中，需要回表取得其數(shù)據。在這樣的情況下，二者的IO量會有較大差異。

order by 一定需要排序操作

我們知道索引數(shù)據實際上是有序的，如果我們的需要的數(shù)據和某個索引的順序一致，而且我們的查詢又通過這個索引來執(zhí)行，那么數(shù)據庫一般會省略排序操作，而直接將數(shù)據返回，因為數(shù)據庫知道數(shù)據已經滿足我們的排序需求了。

實際上，利用索引來優(yōu)化有排序需求的 SQL，是一個非常重要的優(yōu)化手段

延伸閱讀：MySQL ORDER BY 的實現(xiàn)分析，MySQL

中 GROUP BY 基本實現(xiàn)原理以及MySQL DISTINCT 的基本實現(xiàn)原理這3篇文章中有更為深入的分析，尤其是第一篇

執(zhí)行計劃中有 filesort 就會進行磁盤文件排序

有這個誤區(qū)其實并不能怪我們，而是因為 MySQL 開發(fā)者在用詞方面的問題。filesort 是我們在使用 explain 命令查看一條 SQL 的執(zhí)行計劃的時候可能會看到在 “Extra” 一列顯示的信息。

實際上，只要一條 SQL 語句需要進行排序操作，都會顯示“Using filesort”，這并不表示就會有文件排序操作。

延伸閱讀：理解MySQL Explain 命令輸出中的filesort，我在這里有更為詳細的介紹

基本原則

盡量少 join

MySQL 的優(yōu)勢在于簡單，但這在某些方面其實也是其劣勢。MySQL 優(yōu)化器效率高，但是由于其統(tǒng)計信息的量有限，優(yōu)化器工作過程出現(xiàn)偏差的可能性也就更多。對于復雜的多表 Join，一方面由于其優(yōu)化器受限，再者在 Join 這方面所下的功夫還不夠，所以性能表現(xiàn)離 Oracle 等關系型數(shù)據庫前輩還是有一定距離。但如果是簡單的單表查詢，這一差距就會極小甚至在有些場景下要優(yōu)于這些數(shù)據庫前輩。

盡量少排序

排序操作會消耗較多的 CPU 資源，所以減少排序可以在緩存命中率高等 IO 能力足夠的場景下會較大影響 SQL 的響應時間。

對于MySQL來說，減少排序有多種辦法，比如：

上面誤區(qū)中提到的通過利用索引來排序的方式進行優(yōu)化

減少參與排序的記錄條數(shù)

非必要不對數(shù)據進行排序

避免使用耗費資源的操作，帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎 執(zhí)行，耗費資源的排序(SORT)功能.

DISTINCT需要一次排序操作, 而其他的至少需要執(zhí)行兩次排序

…

盡量避免 select *

很多人看到這一點后覺得比較難理解，上面不是在誤區(qū)中剛剛說 select 子句中字段的多少并不會影響到讀取的數(shù)據嗎？

是的，大多數(shù)時候并不會影響到 IO 量，但是當我們還存在 order by 操作的時候，select 子句中的字段多少會在很大程度上影響到我們的排序效率，這一點可以通過我之前一篇介紹MySQL

ORDER BY 的實現(xiàn)分析的文章中有較為詳細的介紹。

此外，上面誤區(qū)中不是也說了，只是大多數(shù)時候是不會影響到 IO 量，當我們的查詢結果僅僅只需要在索引中就能找到的時候，還是會極大減少 IO 量的。

盡量用 join 代替子查詢

雖然 Join 性能并不佳，但是和 MySQL 的子查詢比起來還是有非常大的性能優(yōu)勢。MySQL 的子查詢執(zhí)行計劃一直存在較大的問題，雖然這個問題已經存在多年，但是到目前已經發(fā)布的所有穩(wěn)定版本中都普遍存在，一直沒有太大改善。雖然官方也在很早就承認這一問題，并且承諾盡快解決，但是至少到目前為止我們還沒有看到哪一個版本較好的解決了這一問題。

盡量少 or

當 where 子句中存在多個條件以“或”并存的時候，MySQL 的優(yōu)化器并沒有很好的解決其執(zhí)行計劃優(yōu)化問題，再加上 MySQL 特有的 SQL 與 Storage 分層架構方式，造成了其性能比較低下，很多時候使用 union all 或者是union（必要的時候）的方式來代替“or”會得到更好的效果。

盡量用 union all 代替 union

union 和 union all 的差異主要是前者需要將兩個（或者多個）結果集合并后再進行唯一性過濾操作，這就會涉及到排序，增加大量的 CPU 運算，加大資源消耗及延遲。所以當我們可以確認不可能出現(xiàn)重復結果集或者不在乎重復結果集的時候，盡量使用 union all 而不是 union。

盡量早過濾

這一優(yōu)化策略其實最常見于索引的優(yōu)化設計中（將過濾性更好的字段放得更靠前）。

在 SQL 編寫中同樣可以使用這一原則來優(yōu)化一些 Join 的 SQL。比如我們在多個表進行分頁數(shù)據查詢的時候，我們最好是能夠在一個表上先過濾好數(shù)據分好頁，然后再用分好頁的結果集與另外的表 Join，這樣可以盡可能多的減少不必要的 IO 操作，大大節(jié)省 IO 操作所消耗的時間。

避免類型轉換

這里所說的“類型轉換”是指 where 子句中出現(xiàn) column 字段的類型和傳入的參數(shù)類型不一致的時候發(fā)生的類型轉換：

人為在column_name 上通過轉換函數(shù)進行轉換

直接導致 MySQL（實際上其他數(shù)據庫也會有同樣的問題）無法使用索引，如果非要轉換，應該在傳入的參數(shù)上進行轉換

SELECT emp.ename, emp.job FROM emp WHERE emp.empno = 7369;

不要使用：SELECT emp.ename, emp.job FROM emp WHERE emp.empno = ‘7369

由數(shù)據庫自己進行轉換

如果我們傳入的數(shù)據類型和字段類型不一致，同時我們又沒有做任何類型轉換處理，MySQL 可能會自己對我們的數(shù)據進行類型轉換操作，也可能不進行處理而交由存儲引擎去處理，這樣一來，就會出現(xiàn)索引無法使用的情況而造成執(zhí)行計劃問題。

優(yōu)先優(yōu)化高并發(fā)的 SQL，而不是執(zhí)行頻率低某些“大”SQL

對于破壞性來說，高并發(fā)的 SQL 總是會比低頻率的來得大，因為高并發(fā)的 SQL 一旦出現(xiàn)問題，甚至不會給我們任何喘息的機會就會將系統(tǒng)壓跨。而對于一些雖然需要消耗大量 IO 而且響應很慢的 SQL，由于頻率低，即使遇到，最多就是讓整個系統(tǒng)響應慢一點，但至少可能撐一會兒，讓我們有緩沖的機會。

從全局出發(fā)優(yōu)化，而不是片面調整

SQL 優(yōu)化不能是單獨針對某一個進行，而應充分考慮系統(tǒng)中所有的 SQL，尤其是在通過調整索引優(yōu)化 SQL 的執(zhí)行計劃的時候，千萬不能顧此失彼，因小失大。

盡可能對每一條運行在數(shù)據庫中的SQL進行 explain

優(yōu)化 SQL，需要做到心中有數(shù)，知道 SQL 的執(zhí)行計劃才能判斷是否有優(yōu)化余地，才能判斷是否存在執(zhí)行計劃問題。在對數(shù)據庫中運行的 SQL 進行了一段時間的優(yōu)化之后，很明顯的問題 SQL 可能已經很少了，大多都需要去發(fā)掘，這時候就需要進行大量的 explain 操作收集執(zhí)行計劃，并判斷是否需要進行優(yōu)化。

二、MySQL

數(shù)據庫性能優(yōu)化之表結構

很多人都將數(shù)據庫設計范式作為數(shù)據庫表結構設計“圣經”，認為只要按照這個范式需求設計，就能讓設計出來的表結構足夠優(yōu)化，既能保證性能優(yōu)異同時還能滿足擴展性要求。殊不知，在N年前被奉為“圣經”的數(shù)據庫設計3范式早就已經不完全適用了。這里我整理了一些比較常見的數(shù)據庫表結構設計方面的優(yōu)化技巧，希望對大家有用。

由于MySQL數(shù)據庫是基于行（Row）存儲的數(shù)據庫，而數(shù)據庫操作 IO 的時候是以 page（block）的方式，也就是說，如果我們每條記錄所占用的空間量減小，就會使每個page中可存放的數(shù)據行數(shù)增大，那么每次 IO 可訪問的行數(shù)也就增多了。反過來說，處理相同行數(shù)的數(shù)據，需要訪問的 page 就會減少，也就是 IO 操作次數(shù)降低，直接提升性能。此外，由于我們的內存是有限的，增加每個page中存放的數(shù)據行數(shù)，就等于增加每個內存塊的緩存數(shù)據量，同時還會提升內存換中數(shù)據命中的幾率，也就是緩存命中率。

數(shù)據類型選擇

數(shù)據庫操作中最為耗時的操作就是 IO 處理，大部分數(shù)據庫操作 90% 以上的時間都花在了 IO 讀寫上面。所以盡可能減少 IO 讀寫量，可以在很大程度上提高數(shù)據庫操作的性能。

我們無法改變數(shù)據庫中需要存儲的數(shù)據，但是我們可以在這些數(shù)據的存儲方式方面花一些心思。下面的這些關于字段類型的優(yōu)化建議主要適用于記錄條數(shù)較多，數(shù)據量較大的場景，因為精細化的數(shù)據類型設置可能帶來維護成本的提高，過度優(yōu)化也可能會帶來其他的問題：

數(shù)字類型：非萬不得已不要使用DOUBLE，不僅僅只是存儲長度的問題，同時還會存在精確性的問題。同樣，固定精度的小數(shù)，也不建議使用DECIMAL，建議乘以固定倍數(shù)轉換成整數(shù)存儲，可以大大節(jié)省存儲空間，且不會帶來任何附加維護成本。對于整數(shù)的存儲，在數(shù)據量較大的情況下，建議區(qū)分開 TINYINT / INT / BIGINT 的選擇，因為三者所占用的存儲空間也有很大的差別，能確定不會使用負數(shù)的字段，建議添加unsigned定義。當然，如果數(shù)據量較小的數(shù)據庫，也可以不用嚴格區(qū)分三個整數(shù)類型。

字符類型：非萬不得已不要使用 TEXT 數(shù)據類型，其處理方式決定了他的性能要低于char或者是varchar類型的處理。定長字段，建議使用 CHAR 類型，不定長字段盡量使用 VARCHAR，且僅僅設定適當?shù)淖畲箝L度，而不是非常隨意的給一個很大的最大長度限定，因為不同的長度范圍，MySQL也會有不一樣的存儲處理。

時間類型：盡量使用TIMESTAMP類型，因為其存儲空間只需要 DATETIME 類型的一半。對于只需要精確到某一天的數(shù)據類型，建議使用DATE類型，因為他的存儲空間只需要3個字節(jié)，比TIMESTAMP還少。不建議通過INT類型類存儲一個unix timestamp 的值，因為這太不直觀，會給維護帶來不必要的麻煩，同時還不會帶來任何好處。

ENUM & SET：對于狀態(tài)字段，可以嘗試使用 ENUM 來存放，因為可以極大的降低存儲空間，而且即使需要增加新的類型，只要增加于末尾，修改結構也不需要重建表數(shù)據。如果是存放可預先定義的屬性數(shù)據呢？可以嘗試使用SET類型，即使存在多種屬性，同樣可以游刃有余，同時還可以節(jié)省不小的存儲空間。

LOB類型：強烈反對在數(shù)據庫中存放 LOB 類型數(shù)據，雖然數(shù)據庫提供了這樣的功能，但這不是他所擅長的，我們更應該讓合適的工具做他擅長的事情，才能將其發(fā)揮到極致。在數(shù)據庫中存儲 LOB 數(shù)據就像讓一個多年前在學校學過一點Java的營銷專業(yè)人員來寫 Java 代碼一樣。

字符編碼

字符集直接決定了數(shù)據在MySQL中的存儲編碼方式，由于同樣的內容使用不同字符集表示所占用的空間大小會有較大的差異，所以通過使用合適的字符集，可以幫助我們盡可能減少數(shù)據量，進而減少IO操作次數(shù)。

純拉丁字符能表示的內容，沒必要選擇 latin1 之外的其他字符編碼，因為這會節(jié)省大量的存儲空間

如果我們可以確定不需要存放多種語言，就沒必要非得使用UTF8或者其他UNICODE字符類型，這回造成大量的存儲空間浪費

MySQL的數(shù)據類型可以精確到字段，所以當我們需要大型數(shù)據庫中存放多字節(jié)數(shù)據的時候，可以通過對不同表不同字段使用不同的數(shù)據類型來較大程度減小數(shù)據存儲量，進而降低 IO 操作次數(shù)并提高緩存命中率

適當拆分

有些時候，我們可能會希望將一個完整的對象對應于一張數(shù)據庫表，這對于應用程序開發(fā)來說是很有好的，但是有些時候可能會在性能上帶來較大的問題。

當我們的表中存在類似于 TEXT 或者是很大的 VARCHAR類型的大字段的時候，如果我們大部分訪問這張表的時候都不需要這個字段，我們就該義無反顧的將其拆分到另外的獨立表中，以減少常用數(shù)據所占用的存儲空間。這樣做的一個明顯好處就是每個數(shù)據塊中可以存儲的數(shù)據條數(shù)可以大大增加，既減少物理 IO 次數(shù)，也能大大提高內存中的緩存命中率。

上面幾點的優(yōu)化都是為了減少每條記錄的存儲空間大小，讓每個數(shù)據庫中能夠存儲更多的記錄條數(shù)，以達到減少 IO 操作次數(shù)，提高緩存命中率。下面這個優(yōu)化建議可能很多開發(fā)人員都會覺得不太理解，因為這是典型的反范式設計，而且也和上面的幾點優(yōu)化建議的目標相違背。

適度冗余

為什么我們要冗余？這不是增加了每條數(shù)據的大小，減少了每個數(shù)據塊可存放記錄條數(shù)嗎？

確實，這樣做是會增大每條記錄的大小，降低每條記錄中可存放數(shù)據的條數(shù)，但是在有些場景下我們仍然還是不得不這樣做：

被頻繁引用且只能通過 Join 2張（或者更多）大表的方式才能得到的獨立小字段

這樣的場景由于每次Join僅僅只是為了取得某個小字段的值，Join到的記錄又大，會造成大量不必要的 IO，完全可以通過空間換取時間的方式來優(yōu)化。不過，冗余的同時需要確保數(shù)據的一致性不會遭到破壞，確保更新的同時冗余字段也被更新

盡量使用 NOT NULL

NULL 類型比較特殊，SQL 難優(yōu)化。雖然 MySQL NULL類型和 Oracle 的NULL 有差異，會進入索引中，但如果是一個組合索引，那么這個NULL 類型的字段會極大影響整個索引的效率。此外，NULL 在索引中的處理也是特殊的，也會占用額外的存放空間。

很多人覺得 NULL 會節(jié)省一些空間，所以盡量讓NULL來達到節(jié)省IO的目的，但是大部分時候這會適得其反，雖然空間上可能確實有一定節(jié)省，倒是帶來了很多其他的優(yōu)化問題，不但沒有將IO量省下來，反而加大了SQL的IO量。所以盡量確保 DEFAULT 值不是 NULL，也是一個很好的表結構設計優(yōu)化習慣。

三、MySQL

數(shù)據庫性能優(yōu)化之索引優(yōu)化

大家都知道索引對于數(shù)據訪問的性能有非常關鍵的作用，都知道索引可以提高數(shù)據訪問效率。

為什么索引能提高數(shù)據訪問性能？他會不會有“副作用”？是不是索引創(chuàng)建越多，性能就越好？到底該如何設計索引，才能最大限度的發(fā)揮其效能？

這篇文章主要是帶著上面這幾個問題來做一個簡要的分析，同時排除了業(yè)務場景所帶來的特殊性，請不要糾結業(yè)務場景的影響。

索引為什么能提高數(shù)據訪問性能？

很多人只知道索引能夠提高數(shù)據庫的性能，但并不是特別了解其原理，其實我們可以用一個生活中的示例來理解。

我們讓一位不太懂計算機的朋友去圖書館確認一本叫做《MySQL性能調優(yōu)與架構設計》的書是否在藏，這樣對他說：“請幫我借一本計算機類的數(shù)據庫書籍，是屬于 MySQL 數(shù)據庫范疇的，叫做《MySQL性能調優(yōu)與架構設計》”。朋友會根據所屬類別，前往存放“計算機”書籍區(qū)域的書架，然后再尋找“數(shù)據庫”類存放位置，再找到一堆講述“MySQL”的書籍，最后可能發(fā)現(xiàn)目標在藏（也可能已經借出不在書架上）。

在這個過程中： “計算機”->“數(shù)據庫”->“MySQL”->“在藏”->《MySQL性能調優(yōu)與架構設計》其實就是一個“根據索引查找數(shù)據”的典型案例，“計算機”->“數(shù)據庫”->“MySQL”->“在藏” 就是朋友查找書籍的索引。

假設沒有這個索引，那查找這本書的過程會變成怎樣呢？朋友只能從圖書館入口一個書架一個書架的“遍歷”，直到找到《MySQL性能調優(yōu)與架構設計》這本書為止。如果幸運，可能在第一個書架就找到。但如果不幸呢，那就慘了，可能要將整個圖書館所有的書架都找一遍才能找到我們想要的這本書。

注：這個例子中的“索引”是記錄在朋友大腦中的，實際上，每個圖書館都會有一個非常全的實際存在的索引系統(tǒng)（大多位于入口顯眼處），由很多個貼上了明顯標簽的小抽屜構成。這個索引系統(tǒng)中存放這非常齊全詳盡的索引數(shù)據，標識出我們需要查找的“目標”在某個區(qū)域的某個書架上。而且每當有新的書籍入庫，舊的書籍銷毀以及書記信息修改，都需要對索引系統(tǒng)進行及時的修正。

下面我們通過上面這個生活中的小示例，來分析一下索引，看看能的出哪些結論？

索引有哪些“副作用”？

圖書的變更（增，刪，改）都需要修訂索引，索引存在額外的維護成本

查找翻閱索引系統(tǒng)需要消耗時間，索引存在額外的訪問成本

這個索引系統(tǒng)需要一個地方來存放，索引存在額外的空間成本

索引是不是越多越好？

如果我們的這個圖書館只是一個進出中轉站，里面的新書進來后很快就會轉發(fā)去其他圖書館而從這個館藏中“清除”，那我們的索引就只會不斷的修改，而很少會被用來查找圖書

所以，對于類似于這樣的存在非常大更新量的數(shù)據，索引的維護成本會非常高，如果其檢索需求很少，而且對檢索效率并沒有非常高的要求的時候，我們并不建議創(chuàng)建索引，或者是盡量減少索引。

如果我們的書籍量少到只有幾本或者就只有一個書架，索引并不會帶來什么作用，甚至可能還會浪費一些查找索引所花費的時間。

所以，對于數(shù)據量極小到通過索引檢索還不如直接遍歷來得快的數(shù)據，也并不適合使用索引。

如果我們的圖書館只有一個10平方的面積，現(xiàn)在連放書架都已經非常擁擠，而且館藏還在不斷增加，我們還能考慮創(chuàng)建索引嗎？

所以，當我們連存儲基礎數(shù)據的空間都捉襟見肘的時候，我們也應該盡量減少低效或者是去除索引。

索引該如何設計才高效？

如果我們僅僅只是這樣告訴對方的：“幫我確認一本數(shù)據庫類別的講述 MySQL 的叫做《MySQL性能調優(yōu)與架構設計》的書是否在藏”，結果又會如何呢？朋友只能一個大類區(qū)域一個大類區(qū)域的去尋找“數(shù)據庫”類別，然后再找到 “MySQL”范疇，再看到我們所需是否在藏。由于我們少說了一個“計算機類”，朋友就必須到每一個大類去尋找。

所以，我們應該盡量讓查找條件盡可能多的在索引中，盡可能通過索引完成所有過濾，回表只是取出額外的數(shù)據字段。

如果我們是這樣說的：“幫我確認一本講述 MySQL 的數(shù)據庫范疇的計算機叢書，叫做《MySQL性能調優(yōu)與架構設計》，看是否在藏”。如果這位朋友并不知道計算機是一個大類，也不知道數(shù)據庫屬于計算機大類，那這位朋友就悲劇了。首先他得遍歷每個類別確認“MySQL”存在于哪些類別中，然后從包含 “MySQL” 書籍中再看有哪些是“數(shù)據庫”范疇的（有可能部分是講述PHP或者其他開發(fā)語言的），然后再排除非計算機類的（雖然可能并沒有必要），然后才能確認。

所以，字段的順序對組合索引效率有至關重要的作用，過濾效果越好的字段需要更靠前。

如果我們還有這樣一個需求（雖然基本不可能）：“幫我將圖書館中所有的計算機圖書借來”。朋友如果通過索引來找，每次都到索引柜找到計算機書籍所在的區(qū)域，然后從書架上搬下一格（假設只能以一格為單位從書架上取下，類比數(shù)據庫中以block/page為單位讀取），取出第一本，然后再從索引柜找到計算機圖書所在區(qū)域，再搬下一格，取出一本… 如此往復直至取完所有的書。如果他不通過索引來找又會怎樣呢？他需要從地一個書架一直往后找，當找到計算機的書，搬下一格，取出所有計算機的書，再往后，直至所有書架全部看一遍。在這個過程中，如果計算機類書籍較多，通過索引來取所花費的時間很可能要大于直接遍歷，因為不斷往復的索引翻閱所消耗的時間會非常長。（延伸閱讀：這里有一篇以前寫的關于Oracle的文章，索引掃描還是全表掃描（Index

Scan Or Full Table Scan））

所以，當我們需要讀取的數(shù)據量占整個數(shù)據量的比例較大抑或者說索引的過濾效果并不是太好的時候，使用索引并不一定優(yōu)于全表掃描。

如果我們的朋友不知道“數(shù)據庫”這個類別可以屬于“計算機”這個大類，抑或者圖書館的索引系統(tǒng)中這兩個類別屬性并沒有關聯(lián)關系，又會怎樣呢？也就是說，朋友得到的是2個獨立的索引，一個是告知“計算機”這個大類所在的區(qū)域，一個是“數(shù)據庫”這個小類所在的區(qū)域（很可能是多個區(qū)域），那么他只能二者選其一來搜索我的需求。即使朋友可以分別通過2個索引檢索然后自己在腦中取交集再找，那這樣的效率實際過程中也會比較低下。

所以，在實際使用過程中，一次數(shù)據訪問一般只能利用到1個索引，這一點在索引創(chuàng)建過程中一定要注意，不是說一條SQL語句中Where子句里面每個條件都有索引能對應上就可以了。

最后總結一下法則：不要在建立的索引的數(shù)據列上進行下列操作:

◆避免對索引字段進行計算操作

◆避免在索引字段上使用not，<>，!=

◆避免在索引列上使用IS NULL和IS NOT NULL

◆避免在索引列上出現(xiàn)數(shù)據類型轉換

◆避免在索引字段上使用函數(shù)

◆避免建立索引的列中使用空值。

四、MySQL

數(shù)據庫性能優(yōu)化之緩存參數(shù)優(yōu)化

數(shù)據庫屬于 IO 密集型的應用程序，其主要職責就是數(shù)據的管理及存儲工作。而我們知道，從內存中讀取一個數(shù)據庫的時間是微秒級別，而從一塊普通硬盤上讀取一個IO是在毫秒級別，二者相差3個數(shù)量級。所以，要優(yōu)化數(shù)據庫，首先第一步需要優(yōu)化的就是

IO，盡可能將磁盤IO轉化為內存IO。本文先從 MySQL

數(shù)據庫IO相關參數(shù)（緩存參數(shù)）的角度來看看可以通過哪些參數(shù)進行IO優(yōu)化：

query_cache_size/query_cache_type （global）

Query cache 作用于整個 MySQL Instance，主要用來緩存 MySQL 中的 ResultSet，也就是一條SQL語句執(zhí)行的結果集，所以僅僅只能針對select語句。當我們打開了 Query Cache 功能，MySQL在接受到一條select語句的請求后，如果該語句滿足Query Cache的要求（未顯式說明不允許使用Query Cache，或者已經顯式申明需要使用Query Cache），MySQL 會直接根據預先設定好的HASH算法將接受到的select語句以字符串方式進行hash，然后到Query Cache 中直接查找是否已經緩存。也就是說，如果已經在緩存中，該select請求就會直接將數(shù)據返回，從而省略了后面所有的步驟（如 SQL語句的解析，優(yōu)化器優(yōu)化以及向存儲引擎請求數(shù)據等），極大的提高性能。

當然，Query Cache 也有一個致命的缺陷，那就是當某個表的數(shù)據有任何任何變化，都會導致所有引用了該表的select語句在Query Cache 中的緩存數(shù)據失效。所以，當我們的數(shù)據變化非常頻繁的情況下，使用Query Cache 可能會得不償失。

Query Cache的使用需要多個參數(shù)配合，其中最為關鍵的是 query_cache_size 和 query_cache_type ，前者設置用于緩存 ResultSet 的內存大小，后者設置在何場景下使用 Query Cache。在以往的經驗來看，如果不是用來緩存基本不變的數(shù)據的MySQL數(shù)據庫，query_cache_size 一般 256MB 是一個比較合適的大小。當然，這可以通過計算Query Cache的命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）來進行調整。query_cache_type可以設置為0(OFF)，1(ON)或者2(DEMOND)，分別表示完全不使用query

cache，除顯式要求不使用query cache（使用sql_no_cache）之外的所有的select都使用query cache，只有顯示要求才使用query cache（使用sql_cache）。

binlog_cache_size （global）

Binlog Cache 用于在打開了二進制日志（binlog）記錄功能的環(huán)境，是 MySQL 用來提高binlog的記錄效率而設計的一個用于短時間內臨時緩存binlog數(shù)據的內存區(qū)域。

一般來說，如果我們的數(shù)據庫中沒有什么大事務，寫入也不是特別頻繁，2MB～4MB是一個合適的選擇。但是如果我們的數(shù)據庫大事務較多，寫入量比較大，可與適當調高binlog_cache_size。同時，我們可以通過binlog_cache_use 以及 binlog_cache_disk_use來分析設置的binlog_cache_size是否足夠，是否有大量的binlog_cache由于內存大小不夠而使用臨時文件（binlog_cache_disk_use）來緩存了。

key_buffer_size （global）

Key Buffer 可能是大家最為熟悉的一個 MySQL 緩存參數(shù)了，尤其是在 MySQL 沒有更換默認存儲引擎的時候，很多朋友可能會發(fā)現(xiàn)，默認的 MySQL 配置文件中設置最大的一個內存參數(shù)就是這個參數(shù)了。key_buffer_size 參數(shù)用來設置用于緩存 MyISAM存儲引擎中索引文件的內存區(qū)域大小。如果我們有足夠的內存，這個緩存區(qū)域最好是能夠存放下我們所有的 MyISAM 引擎表的所有索引，以盡可能提高性能。

此外，當我們在使用MyISAM 存儲的時候有一個及其重要的點需要注意，由于 MyISAM 引擎的特性限制了他僅僅只會緩存索引塊到內存中，而不會緩存表數(shù)據庫塊。所以，我們的 SQL 一定要盡可能讓過濾條件都在索引中，以便讓緩存幫助我們提高查詢效率。

bulk_insert_buffer_size （thread）

和key_buffer_size一樣，這個參數(shù)同樣也僅作用于使用 MyISAM存儲引擎，用來緩存批量插入數(shù)據的時候臨時緩存寫入數(shù)據。當我們使用如下幾種數(shù)據寫入語句的時候，會使用這個內存區(qū)域來緩存批量結構的數(shù)據以幫助批量寫入數(shù)據文件：

insert … select …

insert … values (…) ,(…),(…)…

load data infile… into… (非空表)

innodb_buffer_pool_size（global）

當我們使用InnoDB存儲引擎的時候，innodb_buffer_pool_size 參數(shù)可能是影響我們性能的最為關鍵的一個參數(shù)了，他用來設置用于緩存 InnoDB 索引及數(shù)據塊的內存區(qū)域大小，類似于 MyISAM 存儲引擎的 key_buffer_size 參數(shù)，當然，可能更像是 Oracle 的 db_cache_size。簡單來說，當我們操作一個 InnoDB 表的時候，返回的所有數(shù)據或者去數(shù)據過程中用到的任何一個索引塊，都會在這個內存區(qū)域中走一遭。

和key_buffer_size 對于 MyISAM 引擎一樣，innodb_buffer_pool_size 設置了 InnoDB 存儲引擎需求最大的一塊內存區(qū)域的大小，直接關系到 InnoDB存儲引擎的性能，所以如果我們有足夠的內存，盡可將該參數(shù)設置到足夠打，將盡可能多的 InnoDB 的索引及數(shù)據都放入到該緩存區(qū)域中，直至全部。

我們可以通過 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 計算緩存命中率，并根據命中率來調整 innodb_buffer_pool_size 參數(shù)大小進行優(yōu)化。

innodb_additional_mem_pool_size（global）

這個參數(shù)我們平時調整的可能不是太多，很多人都使用了默認值，可能很多人都不是太熟悉這個參數(shù)的作用。innodb_additional_mem_pool_size 設置了InnoDB存儲引擎用來存放數(shù)據字典信息以及一些內部數(shù)據結構的內存空間大小，所以當我們一個MySQL Instance中的數(shù)據庫對象非常多的時候，是需要適當調整該參數(shù)的大小以確保所有數(shù)據都能存放在內存中提高訪問效率的。

這個參數(shù)大小是否足夠還是比較容易知道的，因為當過小的時候，MySQL 會記錄 Warning 信息到數(shù)據庫的 error log 中，這時候你就知道該調整這個參數(shù)大小了。

innodb_log_buffer_size （global）

這是 InnoDB 存儲引擎的事務日志所使用的緩沖區(qū)。類似于 Binlog Buffer，InnoDB 在寫事務日志的時候，為了提高性能，也是先將信息寫入 Innofb Log Buffer 中，當滿足 innodb_flush_log_trx_commit 參數(shù)所設置的相應條件（或者日志緩沖區(qū)寫滿）之后，才會將日志寫到文件（或者同步到磁盤）中?？梢酝ㄟ^ innodb_log_buffer_size 參數(shù)設置其可以使用的最大內存空間。

注：innodb_flush_log_trx_commit 參數(shù)對 InnoDB Log 的寫入性能有非常關鍵的影響。該參數(shù)可以設置為0，1，2，解釋如下：

0：log buffer中的數(shù)據將以每秒一次的頻率寫入到log file中，且同時會進行文件系統(tǒng)到磁盤的同步操作，但是每個事務的commit并不會觸發(fā)任何log buffer 到log file的刷新或者文件系統(tǒng)到磁盤的刷新操作；

1：在每次事務提交的時候將log buffer 中的數(shù)據都會寫入到log file，同時也會觸發(fā)文件系統(tǒng)到磁盤的同步；

2：事務提交會觸發(fā)log buffer 到log file的刷新，但并不會觸發(fā)磁盤文件系統(tǒng)到磁盤的同步。此外，每秒會有一次文件系統(tǒng)到磁盤同步操作。

此外，MySQL文檔中還提到，這幾種設置中的每秒同步一次的機制，可能并不會完全確保非常準確的每秒就一定會發(fā)生同步，還取決于進程調度的問題。實際上，InnoDB 能否真正滿足此參數(shù)所設置值代表的意義正常 Recovery 還是受到了不同 OS 下文件系統(tǒng)以及磁盤本身的限制，可能有些時候在并沒有真正完成磁盤同步的情況下也會告訴 mysqld 已經完成了磁盤同步。

innodb_max_dirty_pages_pct （global）

這個參數(shù)和上面的各個參數(shù)不同，他不是用來設置用于緩存某種數(shù)據的內存大小的一個參數(shù)，而是用來控制在 InnoDB Buffer Pool 中可以不用寫入數(shù)據文件中的Dirty Page 的比例（已經被修但還沒有從內存中寫入到數(shù)據文件的臟數(shù)據）。這個比例值越大，從內存到磁盤的寫入操作就會相對減少，所以能夠一定程度下減少寫入操作的磁盤IO。

但是，如果這個比例值過大，當數(shù)據庫 Crash 之后重啟的時間可能就會很長，因為會有大量的事務數(shù)據需要從日志文件恢復出來寫入數(shù)據文件中。同時，過大的比例值同時可能也會造成在達到比例設定上限后的 flush 操作“過猛”而導致性能波動很大。

上面這幾個參數(shù)是 MySQL 中為了減少磁盤物理IO而設計的主要參數(shù)，對 MySQL 的性能起到了至關重要的作用。

—EOF—

按照mcsrainbow朋友的要求，這里列一下根據以往經驗得到的相關參數(shù)的建議值：

query_cache_type : 如果全部使用innodb存儲引擎，建議為0，如果使用MyISAM 存儲引擎，建議為2，同時在SQL語句中顯式控制是否是喲你gquery cache

query_cache_size: 根據 命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）進行調整，一般不建議太大，256MB可能已經差不多了，大型的配置型靜態(tài)數(shù)據可適當調大

binlog_cache_size: 一般環(huán)境2MB～4MB是一個合適的選擇，事務較大且寫入頻繁的數(shù)據庫環(huán)境可以適當調大，但不建議超過32MB

key_buffer_size: 如果不使用MyISAM存儲引擎，16MB足以，用來緩存一些系統(tǒng)表信息等。如果使用 MyISAM存儲引擎，在內存允許的情況下，盡可能將所有索引放入內存，簡單來說就是“越大越好”

bulk_insert_buffer_size: 如果經常性的需要使用批量插入的特殊語句（上面有說明）來插入數(shù)據，可以適當調大該參數(shù)至16MB～32MB，不建議繼續(xù)增大，某人8MB

innodb_buffer_pool_size: 如果不使用InnoDB存儲引擎，可以不用調整這個參數(shù)，如果需要使用，在內存允許的情況下，盡可能將所有的InnoDB數(shù)據文件存放如內存中，同樣將但來說也是“越大越好”

innodb_additional_mem_pool_size: 一般的數(shù)據庫建議調整到8MB～16MB，如果表特別多，可以調整到32MB，可以根據error log中的信息判斷是否需要增大

innodb_log_buffer_size: 默認是1MB，系的如頻繁的系統(tǒng)可適當增大至4MB～8MB。當然如上面介紹所說，這個參數(shù)實際上還和另外的flush參數(shù)相關。一般來說不建議超過32MB

innodb_max_dirty_pages_pct: 根據以往的經驗，重啟恢復的數(shù)據如果要超過1GB的話，啟動速度會比較慢，幾乎難以接受，所以建議不大于 1GB/innodb_buffer_pool_size(GB)*100 這個值。當然，如果你能夠忍受啟動時間比較長，而且希望盡量減少內存至磁盤的flush，可以將這個值調整到90，但不建議超過90