Mysql優(yōu)化
Sql性能下降的原因
在程序的運行過程中，我們會發(fā)現(xiàn)這樣的一個現(xiàn)象，隨著程序運行時間的不斷推移以及數(shù)據(jù)量越來越大，程序響應的時間逐漸變慢， 程序變得卡頓，但最開始的時候并不是這樣的，那是什么原因?qū)е碌哪兀?/span>
性能下降的原因
外部原因
計算機磁盤空間占用較大，服務器cpu性能及內(nèi)存不足
內(nèi)部原因
程序員編寫的sql語句有問題索引失效
表關聯(lián)太多
服務器參數(shù)設置不合理
Sql讀取順序
我們在編寫sql代碼時的順序和Mysql內(nèi)部讀取sql時的順序是不一樣的，因此有必要搞清楚Mysql讀取sql語句的順序。
代碼編寫的順序
Mysql讀取順序
整體過程
先對多表進行關聯(lián)，根據(jù)條件找出符合的記錄
在符合的記錄基礎上進行where條件過濾
對篩選出的記錄進行分組操作
分組完成后再進行having操作，過濾出滿足條件的數(shù)據(jù)
對取出的記錄進行排序
再按照分頁條件取出要顯示的數(shù)據(jù)
explain執(zhí)行計劃
Mysql?使用 explain?關鍵字可以模擬優(yōu)化器執(zhí)行 sql?語句，我們就能夠知道 Mysql?會如何處理sql，可以根據(jù) explain?的分析結(jié)果和Mysql?底層數(shù)據(jù)結(jié)構(gòu)優(yōu)化 sql。不同Mysql?版本可能有差別，但差別不會很大。
執(zhí)行示例：
id：select查詢的序號，表示執(zhí)行select操作時表的順序。
?id相同按順序走
? ? ?id不同，序號大的先執(zhí)行
? ? ?id相同，不同同時存在
select_type
查詢類型，主要用于區(qū)別普通查詢、聯(lián)合查詢、子查詢
simple：簡單select查詢，查詢中不包含子查詢或union；
? ? ? PRIMARY：主鍵查詢
? ? ?SUBQUERY：where條件包含了子查詢
? ? ?DERIVED：from的表中包含子查詢，被標記為derived（衍 生），把子查詢的結(jié)果放在臨時表中
table
顯示這一行的數(shù)據(jù)是來自于哪張表的
partitions
如果查詢是基于分區(qū)的話，會顯示查詢訪問的分區(qū)
type
訪問類型，按性能從低到高依次排列為
ALL：全表掃描，一定要優(yōu)化
index：它和All都是掃描全表，但index是從索引中讀取表，All是從硬盤中讀取
range：只檢索給定范圍的行，key列顯示使用了哪個索引， between and 或 in 等 查 詢ref：非唯一性索引掃描，本質(zhì)上也是一種索引訪問
eq-ref：唯一性索引掃描，對于每個索引鍵只有一條記錄與之匹配
const：通過索引一次就找到了，常見于primary或unique索引查找
system：表中只有一行記錄（系統(tǒng)表），很少出現(xiàn)
NULL：不需要訪問表
possible_keys：顯示可能應用在這張表中的索引，一個或多個，查詢涉及的字段上若建立了索引則會列出來，但不一定被使用
key：它和possible_keys的關系，理論上應該用到哪些索引，實 ?際上用到了哪些索引
key_len：索引使用的字節(jié)數(shù)，key_len顯示的值為索引字段的最大可能長度
ref：索引是否被引用到，用到了哪幾個索引??
?rows：根據(jù)表統(tǒng)計信息及索引使用情況，估算所需讀取的記錄行數(shù)
filtered：滿足查詢條件記錄數(shù)量的比例，是百分比，不是具體 ?的記錄數(shù)，這個值越大越好，它依賴于統(tǒng)計信息，并不是很準確
Extra：Using?filesort（文件排序，表示mysql無法利用索引完成排序操作）
Using temporary（使用了臨時表保存中間結(jié)果，常見于order by和group by）
Using?index（如果同時出現(xiàn)Using?where，表明索引用來執(zhí)行索引鍵值的查 ??找，如果沒有出現(xiàn)Using?where，表明索引用來讀取數(shù)據(jù)而非執(zhí)行查找）
Using?where（使用了where過濾）
Using?join?buffer（使用了連接緩存）
impossible?where（where子句的值總是false，不能用來獲取記錄）
優(yōu)化技巧
建表規(guī)約
各大公司都有自己的MySQL開發(fā)規(guī)約，我們以阿里為例，阿里的
MySQL開發(fā)規(guī)約如下：
表達是與否概念的字段，必須使用 is_xxx?的方式命名，數(shù)據(jù)類型是 unsigned tinyint（1 表示是，0 表示否）。
說明：任何字段如果為非負數(shù)，必須是 unsigned。
注意：POJO 類中的任何布爾類型的變量，都不要加 is 前綴。數(shù)據(jù)庫表示是與否的值，使用 tinyint 類型，以is_xxx 的命名方式是為了明確其取值含義與取值范圍。例：表達邏輯刪除的字段名is_deleted，1 表示刪除，0 表示未刪除
表名、字段名必須使用小寫字母或數(shù)字，禁止出現(xiàn)數(shù)字開頭，禁止兩個下劃線中間只出現(xiàn)數(shù)字。數(shù)據(jù)庫字段名的修改代價很大，因為無法進行預發(fā) 布，所以字段名稱需要慎重考慮。說明：MySQL?在 Windows 下不區(qū)分大小寫，但在 Linux?下默認是區(qū)分大小寫。因此，數(shù)據(jù)庫名、表名、字段名，都不允許出現(xiàn)任何大寫字母，避免節(jié)外生枝。
正 例 ：aliyun_admin，rdc_config，level3_name
反例：AliyunAdmin，rdcConfig，level_3_name
表名不使用復數(shù)名詞。
說明：表名應該僅僅表示表里面的實體內(nèi)容，不應該表示實體數(shù)量，對應于 DO 類名也是單數(shù)形式，符合表達習慣。
禁用保留字，如 desc、range、match、delayed 等，請參考MySQL 官方保留字。
主鍵索引名為 pk字段名；唯一索引名為 uk字段名；普通索引名則為 idx字段名。
說明：pk?即 primary?key；uk_?即 unique?key；idx_?即 index的簡稱。
小數(shù)類型為 decimal，禁止使用 float 和 double。
說明：float 和 double 在存儲的時候，存在精度損失的問題，很可能在值的比較時，得到不正確的結(jié)果。如果存儲的數(shù)據(jù)范圍超過 decimal 的范圍，建議將數(shù)據(jù)拆成整數(shù)和小數(shù)分開存儲。
如果存儲的字符串長度幾乎相等，使用 char?定長字符串類型。
字段允許適當冗余，以提高查詢性能，但必須考慮數(shù)據(jù)一致。冗余字段應遵循：
不是頻繁修改的字段；
不是 varchar 超長字段，更不能是 text 字段
例如，商品單位使用的頻率高，字段長度短，名稱基本一成不變，可在相關聯(lián)的表中冗余存儲單位名稱，避免關聯(lián)查詢。
合適的字符存儲長度，不但節(jié)約數(shù)據(jù)庫表空間、節(jié)約索引存儲， 更重要的是提升檢索速度。
正例：如下表，其中無符號值可以避免誤存負數(shù)，且擴大了表示范圍。
Sql及索引優(yōu)化
排序字段加索引
使用explain命令分析sql時得到type的值為index，表示該查詢sql使用了索引Z
如果order by的字段沒有索引，type的值變?yōu)?/span>ALL，即全表掃描
結(jié)論：對查詢進行優(yōu)化，要盡量避免全表掃描，首先應考慮在where?及?order?by?涉 及的列上建立索引
where條件中or兩邊的字段沒有索引時盡量少用or
or兩邊的字段中，如果有一個不是索引字段會造成查詢不走索引的情況
區(qū)分in與exists
什么時候用in，什么時候用exists？當in里面子查詢的數(shù)據(jù)少可以用in，例如：
如果外層查詢的數(shù)據(jù)少，而內(nèi)層查詢的數(shù)據(jù)量大，則用exists。
注意exists返回的是true或false
不建議使用%前綴模糊查詢
"%name"或者LIKE"%name%"，這種查詢會導致索引失效而進行全表掃描，如下圖
但是可以使用LIKE "name%"，type的值為range
避免在where子句中對字段進行表達式操作
where條件中對all_amount字段進行計算，造成索引失效
注意范圍查詢語句.
對于聯(lián)合索引來說，如果存在范圍查詢，比如between、>、<等條件時，會造成后面的索引字段失效
使用JOIN優(yōu)化
A left join B，A表為驅(qū)動表，A left join B，B表為驅(qū)動表，而INNERJOIN，MySQL會自動找出數(shù)據(jù)少的表作用驅(qū)動表。
合理利用索引
被驅(qū)動表的索引字段作為on的限制字段；
利用小表去驅(qū)動大表
減少嵌套循環(huán)中的循環(huán)次數(shù)，以減少 IO總量及CPU運算的次數(shù)
