mysql效能調優與架構設計筆記

標籤：

1、mysql基本介紹    mysql支援多線程高並發的關係型資料庫;    資料庫儲存引擎InnoDB、MyISAM;    mysql快速崛起的原因就是他是開源的;    效能一直是mysql自豪的一大特點;2、mysql架構組成    麻雀雖小五髒俱全,mysql雖然簡單但其內部結構並不簡單;    mysql物理檔案組成之記錄檔：    錯誤記錄檔error log這裡記錄mysql運行時嚴重的警告和錯誤,以及mysql啟動和關閉的日誌資訊    二進位日誌 binary log 記錄mysql運行時所有的query和query執行的時間儲存為二進位資訊    查詢日誌 query log 記錄所有query 包括select語句 體積較大開啟後對效能有所影響    慢查詢日誌 slow query log 記錄慢查詢的日誌資訊    mysql物理檔案組成之資料檔案：    每一個資料庫都會在定義好的資料目錄下存在一個以資料庫名字命名的檔案夾,用來存放某個資料庫各個表的資料資訊;    不同的資料存放區引擎有著不同的資料檔案;    .frm拓展名的檔案：不論是儲存引擎,每個表都會有一個以表名.frm的檔案,存放的是表結構的定義    .MYD拓展名的檔案：MyISAM儲存引擎專用,存放表資料,每一個表都會存在一個以表名.MYD的檔案    .MYI拓展名的檔案：MyISAM儲存引擎專用,存放表索引資料,每個表都會存在一個以表名.MYI的檔案    .ibd、.ibdata檔案：InnoDB儲存引擎專用,存放表資料和表索引,區別在於.ibd是隔離儲存區 (Isolated Storage)每個表資訊,每個表都有一個表名.idb檔案這點和MyISAM一樣,而ibdata檔案是共用資料表空間儲存資料資訊;    mysql server系統架構之邏輯模組組成：    mysql可以看成是二層架構,第一層是sql layer，在mysql資料庫系統處理底層資料之前的所有工作都在這一層完成,包括許可權判斷、sql解析、執行計畫最佳化、query cache等工作,第二層是儲存引擎層也就是資料存放區實現的部份，有多種儲存引擎組成;    雖然只有兩個層但是每個層都有很多模組組成,也是相當的複雜;    sql layer層包含：(optimizer 最佳化程式)    初始化、核心Api、網路互動、client & server互動協議、使用者模組、存取控制模組、    串連管理連接線程管理、query解析和轉化、query cache、query最佳化器、表變更管理、    表維護、系統狀態管理、表管理、日誌記錄、複製、儲存引擎介面管理    mysql內建工具使用介紹：    mysql提供大量的用戶端工具程式mysql、mysqladmin、mysqldump...3、mysql儲存引擎    mysql儲存引擎概述：    MyISAM儲存引擎是mysql預設的儲存引擎;    Innodb儲存引擎是第三方外掛程式式儲存引擎,是innobase公司開發,其最大特點是提供事務控制等功能;    MyISMA儲存引擎簡介：    MyISMA儲存引擎的表在資料庫中每一個表都被存放到三個以表名命名的物理檔案中,分別是存放表結構定義的表名.frm檔案、表資料表名.MYD檔案、表索引表名.MYI檔案;    MyISAM索引類型有三種:B-Tree、R-Tree、full-text,常用B-Tree類型索引;    MyISAM儲存引擎有靜態固定長度儲存FIXED、動態可變長度儲存DYNAMIC、壓縮儲存COMPRESS;    MyISAM儲存引擎中某個表資料檔案出錯後不會影響到其他表或其他資料資訊;    Innodb儲存引擎簡介：    mysql中除了MyISAM儲存引擎之外適用作廣泛就是Innodb儲存引擎,他和MyISAM一樣遵循開源license協議;    Innodb的優點就是提供了事務控制功能;    Innodb提升了MyISAM鎖的機制,實現了行鎖功能;    Innodb的資料存放區和MyISAM也不一樣,雖然也有表結構定義資訊表名.frm檔案,表資料和表索引是在一個檔案裡面儲存.ibd單獨的儲存、.ibdata共用儲存;    Innodb的物理結構可以分成兩大類：    資料檔案(存放表資料和表索引資料).ibd單獨的儲存、.ibdata共用儲存兩種類型;    記錄檔:請不要全部刪除Innodb的記錄檔這樣會讓資料庫crash(崩潰),或提示資料丟失;    Memory儲存引擎：    Memory儲存引擎就是把資料存放區在記憶體裡面的一種引擎;    Memory儲存引擎不會把資料存放區在磁碟,存入磁碟的只是表名.frm結構,因此如果資料庫crash或者宕機都會導致資料丟失;    小結：多儲存引擎是mysql有別於其他資料庫的一大特性;後續對常用的儲存引擎會進行詳情介紹;4、mysql安全管理    對企業來說資料庫儲存的資料的安全性是非常重要的;    資料失去了安全性就等於失去了一切;        資料庫系統安全相關因素：    周邊網路安全:mysql是基於網路環境的,而網路本身就存在一種入侵的威脅;要從最外層預防;                盡量讓mysql存在有保護的區域網路環境中;    主機防禦:周邊網路預防得到了保護,那麼還是會存在入侵的可能,那就是區域網路入侵;        主要是阻止沒有授權的裝置串連mysql;        入侵資料庫危害盜取資料、刪除資料、製造漏洞;    資料庫防禦:通過第二道防線我們可以預防一部分威脅,可是允許使用主機登入的裝置是否                完全擁有許可權呢?是否是可信任的對象呢?                資料庫防線是mysql自身系統的存取控制授權模組,這道防線是mysql入侵的最後一道防線了;                設定登入使用者名稱和密碼和連接埠號碼並並設定許可權;    代碼防禦:sql語句相關安全因素、sql注入攻擊、程式碼相關安全因素    DDL、DML、DCL    資料庫定義語言(CREATE ALTER DROP TRUNCATE)    資料庫操作語言(SELECT INSERT UPDATE DELETE EXPLAN)    資料庫控制語言(COMMIT ROLLBACK)    mysql存取控制實現原理：    mysql存取控制其實有2部分組成:一是使用者模組管理、一是存取控制模組管理（許可權）;        使用者模組決定是否能進入,存取控制模組（許可權）決定能有哪些操作;        例如：用戶端請求(提供host,使用者名稱,密碼)-->使用者模組驗證(通過mysql.user表驗證)-->        用戶端請求query(DML、DDL)-->解析query執行許可權-->許可權匹配尋找(grant tables中尋找)        -->發往後端繼續執行    mysql訪問授權策略：    不是每個使用者的許可權都一樣無限大;    每個使用者的許可權做到越小越好,滿足使用就好;    首先瞭解來訪主機、瞭解使用者需求、最後為工作分類,這樣確保絕對必要擁有者擁有grant(准許)許可權;    備忘：安全無小事,資料是一個企業的財富;5、mysql資料的備份與恢複    Database Backup使用的情境：    資料丟失應用情境:人為誤操作、軟體bug、硬體故障、安全性漏洞    非資料丟失情境:特殊情境下資料恢複、開發測試資料庫搭建、資料庫或資料移轉    備忘：資料庫資料備份解決問題不是萬能的;6、影響mysql server效能的相關因素    大多都認為資料庫應用系統的效能瓶頸是資料庫管理軟體和資料庫主機自身造成的,其實不然;    下面以mysql資料庫web情境為例來分析影響效能的瓶頸;    商業需求對效能的影響：        不是所有功能都能實現,有些不合理的功能反而最後成了累贅,消耗資源;        不合理的需求造成資源投入產出比過低;        無用功能堆積使系統過度複雜影響整體效能;(無用的功能大多不會下線,因為考慮風險,所以系統越來越龐大複雜,不僅維護困難,系統效能也越來越差)    系統架構及實現對效能的影響：        一個web應用自然離不開應用程式(web application)和web應用程式伺服器(web server),web server我們控制調優的不多都是很成熟的產品,web application我們可以最佳化很多方面;        以下幾類資料不適合存放到資料庫中：            二進位多媒體資料(消耗記憶體、消耗cpu);            流水隊列資料(不斷的進行insert update delete因為每次操作都會寫入記錄檔影響效能);            超大文本資料(佔用空間浪費資源)        是否合理利用應用程式層cache機制：mysql memory儲存引擎            通過cache機製成功的案例很多很多,可是失敗的案例也很多;            下面整理哪些可以使用cache機制:                系統各種配置和規則的資料;                活躍使用者的基本資料(緩衝使用者的基本資料可以大大提升效能);                時間段的統計資料;                訪問平凡更新較少的資料;        過度依賴資料庫sql語句的功能造成資料庫操作效率低下：            盡量不要在迴圈中多次執行sql，有時可以使用2個sql,這樣不佔用IO和解析資源;            如果一條sql查詢的列不是全部使用時請拆分成2個sql,減少不適用列的查詢;            避免重複執行相同的sql浪費資源(這裡可能和上面兩條像違背,分不同邏輯考慮取捨)        架構設計不當帶來效能問題和資源浪費問題：            cache命中率低,增加資料庫的訪問壓力,浪費cache資源使用率;            過度依賴物件導向,對可拓展性的過度追求;            對資料庫的過度依賴,一些不符合存入資料庫的應該存入檔案系統中;            過度的在乎使用者體驗,比如不用即時更新的資料即時更新了浪費資源;        query語句對效能的影響：            當mysql的連接線程接收到client請求的sql時,會經過解析和分析,然後通過執行計畫調用預存程序介面,最後把資料返回給client顯示;            執行sql主要是IO消耗和cpu消耗(這裡可以通過explain進行測試);            備忘：有時間測試下兩個表先串連查詢和先查詢一個表資訊在和另一個表串連分析哪個好？        schema(方案)的設計對系統效能的影響：            資料庫設計對效能的提升;            適當的使用好範式是對設計最大的調優;        硬體環境對效能的影響：            考慮並發訪問比較頻繁的時候要考慮伺服器IO和CPU處理能力;7、mysql資料庫鎖定機制    為了保證資料的完整性任何一種資料庫都有鎖定的機制;    一個資料庫鎖定技術的優劣直接影響資料庫高並發處理和效能;    mysql常用儲存引擎Innodb、MyISAM    mysql鎖定機制簡介：        行級鎖定：            行級鎖定最大的特點就是對象的顆粒度很小,是最常用的一種形式;            由於鎖定顆粒下取鎖和鎖定處理的事情比較多,耗記憶體,也最容易發生死結;        表級鎖定：            表級鎖定與行級鎖定的特點正好相反,是鎖定mysql儲存引擎中最大的顆粒;            表鎖定邏輯簡單、處理快、耗能小、不容易死結;        頁級鎖定：            頁級鎖定是mysql一個比較獨特的鎖定機制,鎖定介於行鎖和表鎖之間;            頁級鎖定和行級鎖定一樣,很容易被死結;        備忘：行級鎖定不是mysql自己的鎖定機制,而是第三方Innodb儲存引擎的鎖定機制;            Innodb如果產生死結時會通過檢測死結機制來判斷要復原那個事務sql,這裡會根據影響資料的大小來判斷,讓影響資料大的事務sql執行成功,復原影響小的事務sql;或者通過死結機制到期時間來復原事務sql;        Innodb行級鎖的優點：            在很多線程請求不同記錄時減少衝突;            交易回復時減少改變的資料;            使長時間對單獨的一行記錄加鎖成為可能;        Innodb行鎖的缺點：            比表層級鎖和頁層級鎖消耗更多的記憶體;    合理的利用鎖機制最佳化mysql：        MyISAM的表鎖最佳化建議：            MyISAM的表鎖比行鎖和頁鎖減小了資源,但是一定程度上影響了並發的效能，            因此最佳化表鎖的建議就是如何提高並發的效能;                        縮短鎖定時間;                唯一的辦法讓sql執行時間儘可能的短;                龐大複雜的sql建議分成多個小sql分布式執行;                儘可能的建立高效的索引和欄位類型限制;            分離能並行的操作;            合理利用讀寫優先順序;        Innodb行鎖的最佳化建議：            Innodb的行鎖機制雖然比MyISAM的表鎖機制消耗很大的資源但是高並發卻遠遠超於後者;            Innodb的行鎖也有瓶頸的一面：                查詢盡量使用索引提高查詢速度;                合理的設計索引;                查詢的範圍不應過大;        系統鎖定情況查詢：            表鎖定情況查詢：                SHOW STATUS LIKE ‘%table%‘;                Table_locks_immediate 表鎖定的次數                Table_locks_waited    表鎖定等待的次數                 Table_locks_waited如果數值變大了說明表爭用比較嚴重,需要最佳化;            Innodb行鎖的情況查詢：                SHOW STATUS LIKE ‘%innodb_row_lock%‘;                Innodb_row_lock_current_waits   //當前正在等待鎖定的數量                Innodb_row_lock_time    //從系統啟動到現在鎖定總時間長度                Innodb_row_lock_time_avg    //每次等待所花費的平均時間                Innodb_row_lock_time_max    //某次等待最長的時間                Innodb_row_lock_waits   //從系統啟動到現在請求的次數                上述分析：重要的是1 3 5這幾個值    Innodb儲存引擎的整體效能要高於MyISAM儲存引擎8、mysql資料庫中query的最佳化    mysql query optimizer：        mysql query optimizer 是query查詢最佳化工具模組,提供最優的執行計畫;    query語句最佳化的思路和原則：        最佳化更需要最佳化的query;        定位最佳化對象的效能瓶頸;//是IO還是CPU還是記憶體        明確的最佳化目標;        多使用show profiles;        從explain sql入手; //因為它可以展示執行計畫詳細資料        最可能的在索引中排序;        只取出自己需要的columns;        使用最有效過濾條件;        儘可能避免複雜的join和子查詢;    最佳化更需要最佳化的query：        兩個query每小時執行的IO數是一樣的,一個是每小時執行10000次每次消耗20個IO,        一個是每小時執行10次每次消耗20000個IO那麼試問該最佳化哪個query呢？        解答：第一個query把IO從20降到18就減少了2個,那麼就2*10000 = 20000個IO            第二個query如果能減少20000個IO那麼20000/10 = 2000個IO那麼每次需減少2000個IO            因此我們覺得那個更好最佳化呢,哪個能減少更少的IO呢,第一個query;        執行高並發的query比執行低並發的危險性要高的多,高並發的query很容易讓系統crash掉,        等我們重新啟動後系統負荷就會直線飆升接近crash,讓我們都不能查詢問題出現在哪裡,        而低並發的query雖然也產生負荷至少還在可控範圍內;    join時的原則就是‘小結果集驅動大結果集的’：    A表1000資料,B表10萬條資料 SELECT A.*, B.name FROM A LEFT JOIN B ON A.id=B.a_id    //這裡就是以A表作為驅動表迴圈串連B表資訊;減少了迴圈次數;B表示被驅動表;    explain sql語句詳細分析：        注意key_len的值,越小越好;        //也就是說往往一個where條件可以查詢到的就不要再用第二個沒有意義的條件了哦,因為消耗記憶體;    mysql中索引的限制：        MyISAM儲存引擎的索引索引值長度總和不能超過1000位元組;        mysql目前不支援函數索引;        mysql查詢條件!= 、<>的時候不能使用索引;        使用like查詢 like‘%abc‘這樣無法使用索引;    join的原理和最佳化思路：        SELECT A.*         FROM USER_GROUP A LEFT JOIN GROUP_MESSAGE B ON A.group_id = B.group_id        LEFT JOIN GROUP_MESSAGE_CONTENT C ON B.id = C.message_id        WHERE A.user_id = 1;        //這裡是把USER_GROUP表作為驅動表, 先A表通過索引查詢出group_id集合作為驅動表,        //對GROUP_MESSAGE表進行迴圈查詢出id,最後在通過索引message_id查詢出最終結果集合;                儘可能的減少join語句的迴圈次數;        串連的欄位必須建索引;        GROUP BY/ ORDER BY盡量使用索引;9、mysql資料庫schema(表徵圖)的設計和調優      高效的模型設計：        首先考慮符合第一第二第三範式;        適當沉餘讓query盡量減少join;            舉例:user表,message表中可以添加個author_name欄位;        大欄位垂直分隔;            blog表中有個content欄位text類型可以分隔成blog_detail表;        統計表准即時更新;            統計的資料不建議即時更新,這裡就是商業需求影響效能,可以使用定時即時更新;    合適的資料類型：        盡量使用小的資料類型來減少磁碟的空間;        通過合適的資料類型進行數值比較;    規範的對象命名：        資料庫和表命名應儘可能的和所屬產品描述相符合;        欄位名也應和該列資訊描述相符合;        索引名稱盡量包含欄位名稱或欄位縮寫;    備忘：資料庫效能的提升不是最佳化出來的而是設計出來的;10、mysql效能最佳化    mysql安裝最佳化：        安裝適合的資料庫版本;    mysql日誌效能最佳化：        錯誤記錄檔(error log);        更新日誌(update log);        二進位日誌(binlog);        查詢日誌(query log);        慢查詢日誌(slow query sql);    mysql query cache的最佳化：        mysql query cache產生可以讓mysql效能產生質的飛越;    mysql server的其他最佳化：        網路允許的最大串連數;max_connections處理並發能力        使用者允許的組大串連數;max_user_connextions針對於單個使用者的串連限制;        網路包傳輸中,傳輸資訊之前net_buffer的初始化大小;net_buffer_length;        網路傳輸中一次傳輸資訊的最大值;max_allowed_packet;        mysql串連等待中的最大數量;back_log;11、常用儲存引擎最佳化    mysql中MyISAM儲存引擎最佳化;    mysql中Innodb儲存引擎最佳化;12、mysql可拓展設計的基本原則13、可拓展性設計之mysql replication(複製)14、可拓展性設計之資料切分    何為資料切割：        按照不同的表來切分到不同的資料庫上這個是垂直(縱向)切割模式;        把同一個表按照某種邏輯關係分別拆分存放到不同的資料庫上這個是水平切割模式;        備忘：垂直分隔的特點就是簡單,低耦合的表可以進行垂直分隔;        如果我們做了垂直分隔後還任然不能提高效能時我們還的進行水平分隔;    資料的垂直分隔：        資料庫中的表都是有多個功能模組組成,每個功能模組之前的耦合度越小越容易進行垂直分隔;    垂直分隔優點：        資料庫拆分簡單明了,拆分規則明確;        應用程式模組清晰明確,整合容易;        資料維護方便容易定位;    垂直分隔缺點：        部分表關聯無法在資料庫中完成;        切分成一定程度之後拓展性降低;    資料水平分隔：        資料的水平分隔是高並發查詢的表通過某個欄位的規則吧資料分別存放不同的表中進行查詢,        這樣每張表的資料集合就沒有之前一張表大,從而來提示查詢速度,常見的方案是通過userid        對5模數然後分別存放到5個表中,顯示查詢則通過userid對5模數,餘數就知道此userid存在哪裡;    備忘：通過資料分隔技術將一個大的資料mysql server分隔成多個小資料的mysql server,這樣提高了        查詢和寫入效能, 最佳方案是先進行垂直分隔再進行水平分隔;15、可拓展性設定cache和search的利用    分布式緩衝cache解決方案memcached;    利用search實現高效的全文索引;    備忘：資料庫只是儲存資料的工具,他的特點就是持久化,除了資料庫我們還有很多其他方式的資料存放區;16、mysql cluster(叢集)    mysql cluster是一個基於NDB cluster儲存引擎的完整的分散式資料庫系統;

　　

mysql效能調優與架構設計筆記