MySQL數(shù)據(jù)庫性能優(yōu)化

1、MySQL數(shù)據(jù)庫性能優(yōu)化數(shù)據(jù)庫性能優(yōu)化李計剛2015-08參考書籍和文檔參考書籍和文檔n MySQL管理之道管理之道,性能調(diào)優(yōu)、高可用與監(jiān)控性能調(diào)優(yōu)、高可用與監(jiān)控第第5章章“性能調(diào)優(yōu)性能調(diào)優(yōu)”n 深入淺出深入淺出 MySQL數(shù)據(jù)庫開發(fā)、優(yōu)化與管理維護(hù)數(shù)據(jù)庫開發(fā)、優(yōu)化與管理維護(hù)(第第2版版)第三部分第三部分“優(yōu)化篇優(yōu)化篇” n MySQL技術(shù)內(nèi)幕技術(shù)內(nèi)幕(第第4版版)第第5章章“查詢優(yōu)化查詢優(yōu)化”n DB2 SQL性能調(diào)優(yōu)秘笈性能調(diào)優(yōu)秘笈全書，重點(diǎn)第全書，重點(diǎn)第4章章“SQL程序走查程序走查”n 構(gòu)建高性能構(gòu)建高性能Web站點(diǎn)站點(diǎn)第第11章章“數(shù)據(jù)庫性能優(yōu)化數(shù)據(jù)庫性能優(yōu)化”n SQL反模式反模式

2、第第13章章 “反模式反模式:無規(guī)劃地使用索引無規(guī)劃地使用索引”n MySQL核心內(nèi)幕核心內(nèi)幕,InnoDB存儲引擎存儲引擎目錄目錄n表設(shè)計表設(shè)計n正確創(chuàng)建和使用索引正確創(chuàng)建和使用索引nMySQL配置參數(shù)配置參數(shù)nSQL程序走查程序走查n優(yōu)化案例介紹優(yōu)化案例介紹數(shù)據(jù)庫表需最大程度遵守三范式數(shù)據(jù)庫表需最大程度遵守三范式n第一范式：數(shù)據(jù)庫表中的字段都是單一屬第一范式：數(shù)據(jù)庫表中的字段都是單一屬性的，不可再分；性的，不可再分；n第二范式：在第一范式基礎(chǔ)上，除了主鍵第二范式：在第一范式基礎(chǔ)上，除了主鍵以外的其它列都依賴于該主鍵；以外的其它列都依賴于該主鍵；n第三范式：如果滿足第二范式，并且除了第三范式

3、：如果滿足第二范式，并且除了主鍵以外的其它列都不傳遞依賴于主鍵列。主鍵以外的其它列都不傳遞依賴于主鍵列。數(shù)據(jù)表和字段設(shè)計的原則數(shù)據(jù)表和字段設(shè)計的原則n字段選擇的一般原則是保小不保大，能占用字段選擇的一般原則是保小不保大，能占用字節(jié)少的字段就不用大字段；字節(jié)少的字段就不用大字段；n字段的命名要有意義字段的命名要有意義; ;n不用使用無法加索引的類型作為關(guān)鍵字段，不用使用無法加索引的類型作為關(guān)鍵字段，比如比如text；n表中組合主鍵的字段個數(shù)越少越好；表中組合主鍵的字段個數(shù)越少越好；n沒有冗余的數(shù)據(jù)庫未必是最好的數(shù)據(jù)庫，適沒有冗余的數(shù)據(jù)庫未必是最好的數(shù)據(jù)庫，適當(dāng)?shù)臅r候需降低范式標(biāo)準(zhǔn)；當(dāng)?shù)臅r候需降低

4、范式標(biāo)準(zhǔn)；n根據(jù)應(yīng)用場合選擇表的存儲引擎根據(jù)應(yīng)用場合選擇表的存儲引擎數(shù)據(jù)表選擇合適的引擎數(shù)據(jù)表選擇合適的引擎MyISAM 特點(diǎn)n 數(shù)據(jù)存儲方式簡單，使用數(shù)據(jù)存儲方式簡單，使用 B+ Tree B+ Tree 進(jìn)行索引使用三個文件進(jìn)行索引使用三個文件定義一個表：定義一個表：.MYI.MYI、.MYD.MYD、.frm.frm；少碎片、支持大文件、能夠進(jìn)行索引壓縮；少碎片、支持大文件、能夠進(jìn)行索引壓縮；訪問速度飛快，是所有訪問速度飛快，是所有MySQLMySQL文件引擎中速度最快的；文件引擎中速度最快的；不支持一些數(shù)據(jù)庫特性，比如 事務(wù)、外鍵約束等；n表級鎖，性能稍差，更適合讀取多的操作(查詢和更

5、新操作并行時，查詢操作需等待更新操作結(jié)束)；表數(shù)據(jù)容量有限，一般建議單表數(shù)據(jù)量介于表數(shù)據(jù)容量有限，一般建議單表數(shù)據(jù)量介于50w50w200w200w； 增刪查改以后要使用增刪查改以后要使用 myisamchk myisamchk 檢查優(yōu)化表檢查優(yōu)化表數(shù)據(jù)表選擇合適的引擎數(shù)據(jù)表選擇合適的引擎InnoDB 特點(diǎn)n使用使用 Table Space Table Space 的方式來進(jìn)行數(shù)據(jù)存儲的方式來進(jìn)行數(shù)據(jù)存儲 (ibdata1, (ibdata1, ib_logfile0)ib_logfile0)；n 支持事務(wù)、外鍵約束等數(shù)據(jù)庫特性；支持事務(wù)、外鍵約束等數(shù)據(jù)庫特性；n 行級鎖, 讀寫性能都非常優(yōu)秀

6、。(在默認(rèn)“可重復(fù)讀”事務(wù)隔離下，查詢和更新操作并行時，查詢操作不需等待) ；n 能夠承載大數(shù)據(jù)量的存儲和訪問；n 擁有自己獨(dú)立的緩沖池，能夠緩存數(shù)據(jù)和索引；擁有自己獨(dú)立的緩沖池，能夠緩存數(shù)據(jù)和索引；n 在關(guān)閉自動提交的情況下，與在關(guān)閉自動提交的情況下，與MyISAMMyISAM引擎速度差異不大引擎速度差異不大數(shù)據(jù)表的反范式化設(shè)計數(shù)據(jù)表的反范式化設(shè)計ped_target_detail 中字段中字段index_name、index_number完全可以通完全可以通過過index_id外鍵關(guān)聯(lián)外鍵關(guān)聯(lián) ped_index表來獲取到相應(yīng)信息。表來獲取到相應(yīng)信息。這么設(shè)計：主要目標(biāo)是減少這么設(shè)計：主要

7、目標(biāo)是減少join鏈接鏈接。降低范式標(biāo)準(zhǔn)至第二范式降低范式標(biāo)準(zhǔn)至第二范式目錄目錄n表設(shè)計表設(shè)計n正確創(chuàng)建和使用索引正確創(chuàng)建和使用索引nMySQL配置參數(shù)配置參數(shù)nSQL程序走查程序走查n優(yōu)化案例介紹優(yōu)化案例介紹什么是索引？什么是索引？索引的好處與壞處索引的好處與壞處創(chuàng)建索引的好處 n幫助用戶提高查詢速度；幫助用戶提高查詢速度； n利用索引的唯一性來控制記錄的唯一性；利用索引的唯一性來控制記錄的唯一性； n可以加速表與表之間的連接可以加速表與表之間的連接 ；n降低查詢中分組和排序的時間降低查詢中分組和排序的時間創(chuàng)建索引的壞處n存儲索引占用磁盤空間；存儲索引占用磁盤空間；n執(zhí)行數(shù)據(jù)修改操作執(zhí)行數(shù)據(jù)

8、修改操作(INSERT、UPDATE、DELETE)產(chǎn)生索引維護(hù)產(chǎn)生索引維護(hù)索引創(chuàng)建的總體原則索引創(chuàng)建的總體原則n在了解表的具體應(yīng)用場景基礎(chǔ)上建立索引；在了解表的具體應(yīng)用場景基礎(chǔ)上建立索引；n為所有主鍵和外鍵列建立索引；為所有主鍵和外鍵列建立索引；n對出現(xiàn)在對出現(xiàn)在WHERE子句、子句、JOIN子句、子句、ORDER BY或或GROUP BY子句中的列考慮建立索引子句中的列考慮建立索引；n對需要確保唯一性的列考慮建立索引；對需要確保唯一性的列考慮建立索引；n對于對于WHEREWHERE子句中用子句中用ANDAND連接并頻繁使用的列使用連接并頻繁使用的列使用組合索引，組合索引，最頻繁的列放在最左

9、邊最頻繁的列放在最左邊；n數(shù)據(jù)更新頻繁的列不宜建立索引；數(shù)據(jù)更新頻繁的列不宜建立索引；n數(shù)據(jù)量較小的表也不宜建立索引數(shù)據(jù)量較小的表也不宜建立索引索引的正確使用索引的正確使用(一一)去除查詢條件左端的任何標(biāo)量函數(shù)去除查詢條件左端的任何標(biāo)量函數(shù)索引的正確使用索引的正確使用(二二)去除查詢條件左端的任何數(shù)學(xué)運(yùn)算去除查詢條件左端的任何數(shù)學(xué)運(yùn)算索引的正確使用索引的正確使用(三三)確保宿主變量定義與列數(shù)據(jù)類型匹配確保宿主變量定義與列數(shù)據(jù)類型匹配索引的正確使用索引的正確使用(四四)查詢條件中避免使用查詢條件中避免使用in索引的正確使用索引的正確使用(五五)盡可能用盡可能用UNION ALL取代取代UNION

10、索引的正確使用索引的正確使用(六六)查詢條件中使用查詢條件中使用like時避免宿主變量以時避免宿主變量以%開頭開頭索引的正確使用索引的正確使用(七七)避免使用避免使用“or” ，采用其它方式重寫，采用其它方式重寫索引的正確使用索引的正確使用(八八)避免使用避免使用，用其它方式改寫，用其它方式改寫索引的正確使用索引的正確使用(八八)使用組合索引時，注意使用組合索引時，注意“最左前綴最左前綴”這個基本原這個基本原則則n最左前綴：就是最左優(yōu)先，我們創(chuàng)建了最左前綴：就是最左優(yōu)先，我們創(chuàng)建了lname、fname和和age的多列索引的多列索引,相當(dāng)于創(chuàng)相當(dāng)于創(chuàng)建了建了lname單列索引，單列索引，(ln

11、ame,fname)的組的組合索引以及合索引以及(lname,fname,age)組合索引組合索引；nSELECT uid FROM people WHERE fname=Zhiqun AND age=26上述查詢語句上述查詢語句因違法“最左前綴”原則，系，系統(tǒng)統(tǒng)通常會掃描整表以匹配數(shù)據(jù)！以匹配數(shù)據(jù)！目錄目錄n表設(shè)計表設(shè)計n正確創(chuàng)建和使用索引正確創(chuàng)建和使用索引nMySQL配置參數(shù)配置參數(shù)nSQL程序走查程序走查n優(yōu)化案例介紹優(yōu)化案例介紹MySQL配置參數(shù)配置參數(shù)(一一)調(diào)整線程緩存的大小調(diào)整線程緩存的大小per_thread_buffers=(read_buffer_size+read_rnd

12、_buffer_size+sort_buffer_size+thread_stack+join_buffer_size+binlog_cache_size)*max_connections參數(shù)名稱參數(shù)名稱參數(shù)說明參數(shù)說明設(shè)置大小設(shè)置大小read_buffer_sizeMySQL讀入緩沖區(qū)大小讀入緩沖區(qū)大小默認(rèn)默認(rèn)128K,一般一般128K256Ksort_buffer_sizeMySQL執(zhí)行排序使用的緩沖大小執(zhí)行排序使用的緩沖大小默認(rèn)默認(rèn)2M，一般，一般128K256Kread_rnd_buffer_sizeMySQL的隨機(jī)讀緩沖區(qū)大小的隨機(jī)讀緩沖區(qū)大小默認(rèn)默認(rèn)256K,一般一般 128K25

13、6Kthread_stack每個線程的堆棧大小每個線程的堆棧大小默認(rèn)默認(rèn)192KBjoin_buffer_sizeJoin連接操作時，如果關(guān)聯(lián)的字段連接操作時，如果關(guān)聯(lián)的字段沒有索引，會出現(xiàn)此參數(shù)。沒有索引，會出現(xiàn)此參數(shù)。默認(rèn)默認(rèn)128K, 128K256Ktmp_table_sizeMySQL的臨時表緩沖大小的臨時表緩沖大小binlog_cache_size在事務(wù)過程中容納二進(jìn)制日志在事務(wù)過程中容納二進(jìn)制日志SQL 語句的緩存大小語句的緩存大小128K256Kmax_connections最大連接數(shù)最大連接數(shù)默認(rèn)值默認(rèn)值100，一般，一般5121000MySQL配置參數(shù)配置參數(shù)(二二)調(diào)整全

14、局緩存的大小調(diào)整全局緩存的大小global_buffers=innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+key_buffer_size+query_cache_size參數(shù)名稱參數(shù)名稱參數(shù)說明參數(shù)說明設(shè)置大小設(shè)置大小innodb_buffer_pool_sizeInnoDB使用該參數(shù)指定大小的內(nèi)存來緩沖數(shù)據(jù)使用該參數(shù)指定大小的內(nèi)存來緩沖數(shù)據(jù)和索引和索引默認(rèn)默認(rèn)128M,要設(shè)置為物要設(shè)置為物理內(nèi)存的理內(nèi)存的60%70%innodb_additional_mem_pool_size指定

15、指定InnoDB用來存儲數(shù)據(jù)字典和其他內(nèi)部數(shù)據(jù)用來存儲數(shù)據(jù)字典和其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池大小結(jié)構(gòu)的內(nèi)存池大小默認(rèn)默認(rèn)8M，一般設(shè)置為，一般設(shè)置為16M即可。即可。innodb_log_buffer_size指定指定InnoDB用來存儲日志數(shù)據(jù)的緩存大小，如用來存儲日志數(shù)據(jù)的緩存大小，如果您的表操作中包含大量并發(fā)事務(wù)（或大規(guī)模事果您的表操作中包含大量并發(fā)事務(wù)（或大規(guī)模事務(wù)），并且在事務(wù)提交前要求記錄日志文件，請務(wù)），并且在事務(wù)提交前要求記錄日志文件，請盡量調(diào)高此項值，以提高日志效率盡量調(diào)高此項值，以提高日志效率默認(rèn)默認(rèn)8M，一般設(shè)置為，一般設(shè)置為16M64M即可。即可。key_buffer_si

16、ze決定索引處理的速度。決定索引處理的速度。key_buffer_size只對只對MyISAM表起作用。即使你不使用表起作用。即使你不使用MyISAM表，表，但是內(nèi)部的臨時磁盤表是但是內(nèi)部的臨時磁盤表是MyISAM表，也要使用表，也要使用該值。該值。64M即可。即可。query_cache_size緩存緩存select語句和結(jié)果集大小的參數(shù)語句和結(jié)果集大小的參數(shù)MySQL配置參數(shù)配置參數(shù)(三三)使用查詢緩存使用查詢緩存MySQL配置參數(shù)配置參數(shù)(四四)定位慢查詢和不使用索引的查詢定位慢查詢和不使用索引的查詢MySQL配置參數(shù)配置參數(shù)(五五)使用持久連接使用持久連接目錄目錄n表設(shè)計表設(shè)計n正確創(chuàng)

17、建和使用索引正確創(chuàng)建和使用索引nMySQL配置參數(shù)配置參數(shù)nSQL程序走查程序走查n優(yōu)化案例介紹優(yōu)化案例介紹SQL程序走查程序走查(一一)走查時，要提供的內(nèi)容：走查時，要提供的內(nèi)容：n排列清晰明了的排列清晰明了的SQL代碼；代碼；nSQL所涉及表結(jié)構(gòu)的詳細(xì)定義、所建立索引情況等；所涉及表結(jié)構(gòu)的詳細(xì)定義、所建立索引情況等；n此此SQL解析的解析的EXPLAIN截圖；截圖；n此此SQL執(zhí)行后的相關(guān)信息，包括獲取的行數(shù)、執(zhí)行時間等等。執(zhí)行后的相關(guān)信息，包括獲取的行數(shù)、執(zhí)行時間等等。SQL程序走查程序走查(二二)分析分析SQL的的EXPLAIN：n檢查所有表空間掃描；檢查所有表空間掃描；n檢查發(fā)生的所

18、有排序，是否需要排序檢查發(fā)生的所有排序，是否需要排序?；n檢查發(fā)生的所有排序，是否有其它辦法編寫查詢來消除排序？檢查發(fā)生的所有排序，是否有其它辦法編寫查詢來消除排序？SQL程序走查程序走查(三三)分析具體的分析具體的SQL：nSQL語句中選擇的列是否都需要，不需要的移除掉；語句中選擇的列是否都需要，不需要的移除掉；n檢查各個查詢中的所有列。其中有沒有可為檢查各個查詢中的所有列。其中有沒有可為null的列？確保的列？確保編寫相應(yīng)的編寫相應(yīng)的SQL來處理來處理null值；值；n對于對于AVG、MIN、MAX或或SUM語句，確保編寫相應(yīng)的語句，確保編寫相應(yīng)的SQL來來處理處理null值；值；n內(nèi)聯(lián)接

19、和外聯(lián)接的使用是否正確？內(nèi)聯(lián)接和外聯(lián)接的使用是否正確？n檢查所有的檢查所有的Union SQL語句。是否需要寫為語句。是否需要寫為Union，或者，或者SQL語句是否可以寫為語句是否可以寫為Union All?n檢查所有的檢查所有的Order By語句。從業(yè)務(wù)上考量是否需要這些語句。從業(yè)務(wù)上考量是否需要這些Order By語句么？有沒有一個索引支持排序？如果與一個索引語句么？有沒有一個索引支持排序？如果與一個索引匹配，匹配，Order By可能非常高效?？赡芊浅８咝АQL程序走查程序走查(三三)續(xù)續(xù)分析具體的分析具體的SQL：n檢查所有檢查所有Distinct語句。是否可能出現(xiàn)重復(fù)？如果有重

20、復(fù)，考語句。是否可能出現(xiàn)重復(fù)？如果有重復(fù)，考慮改寫該語句以避免排序；慮改寫該語句以避免排序；n檢查所有謂詞，確保所有數(shù)學(xué)計算都在操作符的另一邊完成，檢查所有謂詞，確保所有數(shù)學(xué)計算都在操作符的另一邊完成，而不是應(yīng)用在列本身；而不是應(yīng)用在列本身；n檢查所有謂詞，確保左端無任何標(biāo)量函數(shù)；檢查所有謂詞，確保左端無任何標(biāo)量函數(shù)；n檢查所有的檢查所有的“非非”邏輯，按正面的方式來改寫此非邏輯，這邏輯，按正面的方式來改寫此非邏輯，這樣會更高效。樣會更高效。n檢查所有的檢查所有的“”,對其進(jìn)行改寫，這樣會更高效；對其進(jìn)行改寫，這樣會更高效；SQL程序走查程序走查(三三)再續(xù)再續(xù)分析具體的分析具體的SQL：n檢

21、查所有檢查所有OR語句語句,對其進(jìn)行改寫，這樣更高效；對其進(jìn)行改寫，這樣更高效；n檢查所有的檢查所有的LIKE語句，確保其宿主變量不以語句，確保其宿主變量不以“%”開頭；開頭；n檢查所有的組合索引，確保符合檢查所有的組合索引，確保符合“最左前綴最左前綴”原則；原則；n檢查所有的檢查所有的“In”語句，對其進(jìn)行改寫，這樣會更高效；語句，對其進(jìn)行改寫，這樣會更高效；n確保宿主變量定義與列數(shù)據(jù)類型匹配確保宿主變量定義與列數(shù)據(jù)類型匹配目錄目錄n表設(shè)計表設(shè)計n正確創(chuàng)建和使用索引正確創(chuàng)建和使用索引nMySQL配置參數(shù)配置參數(shù)nSQL程序走查程序走查n優(yōu)化案例介紹優(yōu)化案例介紹優(yōu)化案例優(yōu)化案例(一一)優(yōu)化案例優(yōu)化案例(二二)優(yōu)化案例優(yōu)化案例(三三)優(yōu)化案例優(yōu)化案例(四四)n考勤表考勤表(att_attendance_record)分區(qū)處理；分區(qū)處理；n考勤調(diào)整表考勤調(diào)整表(att_attendance_adjust)對字段對字段 attendance_id 建立索引，避免全表建立索引，避免全表掃描；掃描；n考勤表考勤表(att_attendance_re