一、性能瓶頸產(chǎn)生的原因
1，關(guān)系型數(shù)據(jù)庫的先天缺陷：為了保證ACID，也就是原子性，隔離性，一致性，持久性，所以事務(wù)必須串行執(zhí)行，也就是任何一個事務(wù)在處理期間，其他事務(wù)必須在等待，等待，等待，如果是大事務(wù)的話，需要鎖定太多的數(shù)據(jù)，造成大量的阻塞和鎖超時，回滾所需時間較長，執(zhí)行時間長，容易造成主從延遲。
2、學(xué)院派的誤導(dǎo)：要為了范式而范式，上學(xué)的時候經(jīng)典教程里的什么范式是在企業(yè)內(nèi)部管理系統(tǒng)開發(fā)為主和存儲系統(tǒng)昂貴的時代產(chǎn)物。雖然有減少數(shù)據(jù)冗余、更新操作快，數(shù)據(jù)表比較小等優(yōu)點，但是在互聯(lián)網(wǎng)時代相對于多表快速查詢，小事務(wù)快速讀寫這些特性，完全按照范式進(jìn)行數(shù)據(jù)庫設(shè)計，難以進(jìn)行索引優(yōu)化，從而降低數(shù)據(jù)庫性能，實際上適度的反范式化，是提高數(shù)據(jù)庫性能的有效手段。當(dāng)然不能數(shù)據(jù)庫冗余的太離譜了，太多的列當(dāng)然也會造成更坑爹的性能問題。
3，運(yùn)維人員的失誤：不當(dāng)?shù)呐渲?，不?dāng)?shù)挠布?，不?dāng)?shù)墓芾矶紩绊憯?shù)據(jù)庫的性能，這些雖然都是入門問題，但是老實說，非常多的公司做不到，對運(yùn)維人員配置不足，不夠重視，或者說是運(yùn)維人員意識不強(qiáng)是主要原因，好在都是簡單問題，不管問題如何嚴(yán)重，基本萬能的百度都可以幫你找到辦法，也就是個工作量的問題?；蛘呤清X的問題，不行把硬盤都SSD化試試？
4，數(shù)據(jù)庫設(shè)計缺陷：數(shù)據(jù)庫表結(jié)構(gòu)，索引設(shè)計不當(dāng)和閑的蛋疼的查詢語句，這個是性能瓶頸最主要的問題，優(yōu)化這方面的問題，也是最體現(xiàn)技術(shù)人員功力的地方，相對事半功倍。
本文也主要圍繞這數(shù)據(jù)庫設(shè)計的關(guān)鍵點展開，什么參數(shù)配置，基準(zhǔn)測試這類，要不就是需要較多的人力參與，通常小公司是沒這么多人的，道理都懂，誰也做不到而已，要不就是百度下可以找到方法的，就不做贅述了。
二、性能優(yōu)化的思路，數(shù)據(jù)庫性能優(yōu)化幾步走：
2.1選擇合適的引擎:
2.2、選擇合適的字段:
當(dāng)一列可以有多種數(shù)據(jù)類型選擇的時候，要優(yōu)先數(shù)字類，優(yōu)先數(shù)字類，優(yōu)先選數(shù)字類，重要的話說三遍！其次是選擇日期類型和二進(jìn)制類型，萬不得已再用字符串，計算機(jī)這個東西，實在太不擅長處理字符串了，具體原因，是計算機(jī)的先天缺陷，自行百度去吧。另外考慮清楚VARCHAR和CHAR各自不同的場景，字段不要太長，但是也有足夠使用，還有最重要的是，主鍵盡可能小。
2.3、 選擇合適的索引：
B-tree索引能夠加快數(shù)據(jù)查詢速度，更適合進(jìn)行范圍查找，適合全值匹配的查詢、匹配最左前綴的查詢、匹配列前綴查詢、范圍值的查詢、精確匹配左前列并范圍匹配另外一列、只訪問索引的查詢。但是如果不是按最左列開始查找，則無法使用，也不能跳過索引中的列，Not in和<>操作無法使用索引，如果查詢有某個列的范圍查詢，則有右邊所有列都無法使用索引。
Hash索引是基于Hash表實現(xiàn)的，只有查詢條件精確匹配Hash索引的所有列時，才能使用到hash索引，對于Hash索引中所有的列，存儲引擎會為每一行計算一個Hash碼，Hash索引中存儲的就是Hash碼。使用Hash查詢時，必須兩次查找，大部分因為存在內(nèi)存中，所以不會明顯影響性能，但是要小心特殊情況出現(xiàn)，Hash無法使用排序，不支持范圍和部分索引查找，另外hash沖突無法避免，所以不適合用于很多重復(fù)數(shù)據(jù)的列查找。
最后最重要的是，索引會增加寫操作成本，太多的索引也增加查詢優(yōu)化器的選擇時間，所以沒事兒干就清理下沒有被使用的索引，更新索引統(tǒng)計信息并減少索引碎片，是一件很有價值的事情。
2.4 索引優(yōu)化策略
索引列上不能使用表達(dá)式和函數(shù)，這個我不想說了，真有人這么干的，如果你們公司有，那就換掉吧。
索引的選擇性是不重復(fù)的索引值和表記錄的記錄數(shù)的比值，據(jù)我個人以前的工作經(jīng)驗，在查詢結(jié)果占數(shù)據(jù)總量20%以上是全表掃描更快，0.1%以下是索引掃描更快，20%到0.1%之間的范圍， 因為CPU.內(nèi)存，磁盤性能、服務(wù)器整體性能甚至空氣，電壓，心情等詭異的原因，只有測試過才知道那個更快那個更慢，要根據(jù)測試結(jié)果來指導(dǎo)索引優(yōu)化更為妥當(dāng)，實在沒有人能總結(jié)什么經(jīng)驗教訓(xùn) ，所以有句萬能的正確的廢話，叫看情況而定！
建立聯(lián)合索引的時候，要制定合適的索引列順序，經(jīng)常會被使用到的列優(yōu)先，選擇性高的列優(yōu)先，寬度小的列優(yōu)先。
2.5 優(yōu)化SQL性能
首先來看；SQL語句的執(zhí)行有五步，具體如下
1，客戶端發(fā)送SQL請求給服務(wù)器
2、服務(wù)器檢查是否可以在查詢緩存中命中該SQL
3、服務(wù)器端進(jìn)行SQL解析，預(yù)處理，再由優(yōu)化生成的執(zhí)行計劃
4、根據(jù)執(zhí)行計劃，調(diào)用存儲引擎API來查詢數(shù)據(jù)
5、將結(jié)果返回給客戶端
通常認(rèn)為其中第二步和第三步是性能優(yōu)化的關(guān)鍵，但也不排除其他幾步成為瓶頸的可能性。
所以，查詢緩存對SQL性能影響較大，有幾個參數(shù)必須要關(guān)注
query_cache_type? 設(shè)置查詢緩存是否可用
query_cache_size? 設(shè)置查詢緩存的內(nèi)存大小
query_cache_limit? 設(shè)置查詢緩存可用存儲的最大值
query_cache_wlock_invalidate? 設(shè)置數(shù)據(jù)表被鎖后是否返回緩存中的數(shù)據(jù)
query_cache_min_res_unit? 設(shè)置查詢緩存分配的內(nèi)存塊最小單位。
對于SQL執(zhí)行下，又包含了幾步子過程
先檢查查詢中所涉及的表和數(shù)據(jù)列是否存在名字或者別名是否存在歧義等；
等語法檢查全部通過了，查詢優(yōu)化器，就可以生成查詢計劃了！
這里要注意統(tǒng)計信息不準(zhǔn)確會導(dǎo)致生成錯誤的執(zhí)行計劃，執(zhí)行過程中的成本估算不等于實際的執(zhí)行計劃的成本，MYSQL優(yōu)化所認(rèn)為的最優(yōu)可能與你認(rèn)為的不同，MYSQL從不考慮其他并發(fā)查詢，可能會影響當(dāng)前查詢速度，MYSQL有時候會基于一些固定的規(guī)則來生成執(zhí)行計劃，MYSQL不會考慮不受器其控制的成本等，所以雖然MYSQL很強(qiáng)大，但是也很靠不住，多看執(zhí)行計劃，多做性能測試，木有太好的辦法解決。
還有，查詢優(yōu)化器優(yōu)化偷偷的會干這些活兒，比如重新定義表的關(guān)聯(lián)順序，將外連接轉(zhuǎn)化成內(nèi)連接，使用等價變換規(guī)則，將一個表達(dá)式轉(zhuǎn)化為一個常數(shù)，將子查詢改成關(guān)聯(lián)查詢等等。
在優(yōu)化過程中，需要習(xí)慣使用Profile度量SQL執(zhí)行的消耗時間，準(zhǔn)確的選擇優(yōu)化的階段，對其進(jìn)行優(yōu)化，或者根據(jù)性能測試結(jié)果來最判斷，最好不要主觀臆斷。
具體做法有，對大表的結(jié)構(gòu)進(jìn)行修改，一般來說先建立新表，在老表做觸發(fā)器和排它鎖，數(shù)據(jù)不斷同步到新表，等新表完全啟用后，再停用老表‘；把NOT IN過濾改成LEFT JOIN；使用匯總表優(yōu)化查詢等
老實說，這里么有太多的技巧去閃展騰挪，根據(jù)實際運(yùn)行的效果，去調(diào)整表結(jié)構(gòu)，索引，和執(zhí)行方式是一個長期的過程，沒有什么方法是在所有場景中都可以適用的。
如果表級優(yōu)化不能滿足需求，需要對數(shù)據(jù)庫進(jìn)行整體改造最常見的有
1、加錢：不要笑，擴(kuò)充硬件，增加內(nèi)存，網(wǎng)卡更換，這個是最省事兒的，當(dāng)然也是有局限的。
2、分庫：一個實例中的多個數(shù)據(jù)庫拆分到不同的實例，比如訂單、用戶、促銷本來放在一個節(jié)點，可以分開三個節(jié)點部署，這種模式對讀效率很很大優(yōu)化，對寫負(fù)載，基本沒毛啥用。
3、分表：一個庫中的表分離到不同數(shù)據(jù)庫，比如表的水平拆分，水平分片是數(shù)據(jù)庫的優(yōu)化終極大招，其難度最大，維護(hù)成本高，比如對訂單表，按年，月，甚至日來分片，也有按地區(qū)，按商品類型來分的，花樣很多，原則上要避免跨分片查詢，不得以用匯總表來做處理，分區(qū)鍵要盡可能使各分片中的數(shù)據(jù)分區(qū)。對于無需分片的表，可以在每個分片中存儲一份相同的數(shù)據(jù)，或者使用額外的節(jié)點統(tǒng)一存儲。
總結(jié)：
MYSQL數(shù)據(jù)庫優(yōu)化過程，沒有什么不傳之秘，也沒有什么方法是一勞永逸的，還是那句萬能的廢話，一切看情況而定！另外重要的話說三遍，性能優(yōu)化的分表工作是MYSQL終極大招，也是工程浩大的事情，如果不打算花個三五個月去做，乘早洗洗睡了，當(dāng)熱，如果是阿貓阿狗類的公司，自己關(guān)起門來自嗨，另當(dāng)別論！
