MySql介紹及最佳化（1）

標籤：統計   關係   協助   分析   變更   外掛程式   group   primary   資料   

mysql 資料庫1、是關係型資料庫；2、體積小、支援多處理器、開源、免費3、在 Internet 中小型網站中的使用率尤其高mysql 資料庫開發常見問題及最佳化一、庫表設計1.1 引擎選擇在 mysql 5.1 中，引入了新的外掛程式式儲存引擎體繫結構，允許將儲存引擎載入到正在運新的 mysql 伺服器中。使用 mysql 外掛程式式儲存引擎體繫結構，允許資料庫專業人員或者設計庫表的軟體開發人員為特定的應用需求選擇專門的儲存引擎，完全不需要管理任何特殊的應用編碼要求，也無需考慮所有的底層實施細節。因此，儘管不同的儲存引擎具有不同的能力，應用程式是與之分離的。此外，使用者可以在伺服器、資料庫和表格三個層級中儲存引擎，提供了極大的靈活性。mysql 常用的儲存引擎包括 MYISAM、Innodb 和 Memory，其中各自的特點如下：MYISAM : 全表鎖，擁有較高的執行速度，一個寫請求請阻塞另外相同表格的所有讀寫請求，並發效能差，佔用空間相對較小，mysql 5.5 及以下僅 MYISAM 支援全文索引，不支援事務。Innodb：行級鎖（SQL 都走索引查詢），並發能力相對強，佔用空間是 MYISAM 的 2.5 倍，不支援全文索引（5.6 開始支援），支援事務。Memory : 全表鎖，儲存在記憶體當中，速度快，但會佔用和資料量成正比的記憶體空間且資料在 mysql 重啟時會丟失。基於以上特性，建議絕大部份都設定為 innodb 引擎，特殊的業務再考慮選用 MYISAM 或 Memory ，如全文索引支援或極高的執行效率等。1.2 分表方法在資料庫表使用過程中，為了減小資料庫伺服器的負擔、縮短查詢時間，常常會考慮做分表設計。分表分兩種，一種是縱向分表（將本來可以在同一個表的內容，人為劃分儲存在為多個不同結構的表）和橫向分表（把大的表結構，橫向切割為同樣結構的不同表）。其中，縱向分表常見的方式有根據活躍度分表、根據重要性分表等。其主要解決問題如下：表與表之間資源爭用問題；鎖爭用機率小；實現核心與非核心的分級儲存，如UDB登陸庫拆分成一級二級三級庫；解決了資料庫同步壓力問題。橫向分表是指根據某些特定的規則來劃分大資料量表，如根據時間分表。其主要解決問題如下：單表過大造成的效能問題；單表過大造成的單伺服器空間問題。1.3 索引問題索引是對資料庫表中一個或多個列的值進行排序的結構，建立索引有助於更快地擷取資訊。 mysql 有四種不同的索引類型：主鍵索此 ( PRIMARY )唯一索引 ( UNIQUE )普通索引 ( INDEX )全文索引（FULLTEXT , MYISAM 及 mysql 5.6 以上的 Innodb ）建立索引的目的是加快對錶中記錄的尋找或排序，索引也並非越多越好，因為建立索引是要付出代價的：一是增加了資料庫的儲存空間，二是在插入和修改資料時要花費較多的時間維護索引。在設計表或索引時，常出現以下幾個問題：少建索引或不建索引。這個問題最突出，建議建表時 DBA 可以一起協助把關。索引濫用。濫用索引將導致寫請求變慢，拖慢整體資料庫的響應速度（5.5 以下的 mysql 只能用到一個索引)。從不考慮聯合索引。實際上聯合索引的效率往往要比單列索引的效率更高。非最優列選擇。低選擇性的欄位不適合建單列索引，如 status 類型的欄位。二、慢 SQL 問題2.1 導致慢 SQL 的原因在遇到慢 SQL 情況時，不能簡單的把原因歸結為 SQL 編寫問題(雖然這是最常見的因素)，實際上導致慢 SQL 有很多因素，甚至包括硬體和 mysql 本身的 bug。根據出現的機率從大到小，羅列如下：SQL編寫問題鎖業務執行個體相互幹繞對 IO/CPU 資源爭用伺服器硬體MYSQL BUG2.2 由 SQL 編寫導致的慢 SQL 最佳化針對SQL編寫導致的慢 SQL，最佳化起來還是相對比較方便的。正如上一節提到的正確的使用索引能加快查詢速度，那麼我們在編寫 SQL 時就需要注意與索引相關的規則：欄位類型轉換導致不用索引，如字串類型的不用引號，數字類型的用引號等，這有可能會用不到索引導致全表掃描；mysql 不支援函數轉換，所以欄位前面不能加函數，否則這將用不到索引；不要在欄位前面加減運算；字串比較長的可以考慮索引一部份減少索引檔案大小，提高寫入效率；like % 在前面用不到索引；根據聯合索引的第二個及以後的欄位單獨查詢用不到索引；不要使用 select *；排序請盡量使用升序 ;or 的查詢盡量用 union 代替 （Innodb）；複合索引高選擇性的欄位排在前面；order by / group by 欄位包括在索引當中減少排序，效率會更高。除了上述索引使用規則外，SQL 編寫時還需要特別注意一下幾點：盡量規避大事務的 SQL，大事務的 SQL 會影響資料庫的並發效能及主從同步；分頁語句 limit 的問題；刪除表所有記錄請用 truncate，不要用 delete；不讓 mysql 幹多餘的事情，如計算；輸寫 SQL 帶欄位，以防止後面表變更帶來的問題，效能也是比較優的 ( 涉及到資料字典解析，請自行查詢資料)；在 Innodb上用 select count(*)，因為 Innodb 會儲存統計資訊；慎用 Oder by rand()。三、分析診斷工具在日常開發工作中，我們可以做一些工作達到預防慢 SQL 問題，比如在上線前預先用診斷工具對 SQL 進行分析。常用的工具有：mysqldumpslowmysql profilemysql explain四、誤操作、程式 bug 時怎麼辦一個成熟的企業往往會有完善的資料管理規範和較豐富的資料恢複方案（初創公司除外），會進行資料備份和資料容災。當你發現誤操作或程式 bug 導致線上資料被誤刪或誤改動時，一定不能慌亂，應及時與 DBA 聯絡，第一時間進行資料恢複（嚴重時直接停止服務），儘可能減少影響和損失。對於重要資料（如資金）的操作，在開發時一定要反覆進行測試，確保沒有問題後再上線。

　　

MySql介紹及最佳化（1）