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

1、MySQL數(shù)據(jù)庫(kù)性能優(yōu)化數(shù)據(jù)庫(kù)性能優(yōu)化李計(jì)剛2015-08參考書(shū)籍和文檔參考書(shū)籍和文檔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ù)庫(kù)開(kāi)發(fā)、優(yōu)化與管理維護(hù)數(shù)據(jù)庫(kù)開(kāi)發(fā)、優(yōu)化與管理維護(hù)(第第2版版)第三部分第三部分“優(yōu)化篇優(yōu)化篇” n MySQL技術(shù)內(nèi)幕技術(shù)內(nèi)幕(第第4版版)第第5章章“查詢(xún)優(yōu)化查詢(xún)優(yōu)化”n DB2 SQL性能調(diào)優(yōu)秘笈性能調(diào)優(yōu)秘笈全書(shū)，重點(diǎn)第全書(shū)，重點(diǎn)第4章章“SQL程序走查程序走查”n 構(gòu)建高性能構(gòu)建高性能Web站點(diǎn)站點(diǎn)第第11章章“數(shù)據(jù)庫(kù)性能優(yōu)化數(shù)據(jù)庫(kù)性能優(yōu)化”n SQL反模式反模式

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

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

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

5、新操作并行時(shí)，查詢(xún)操作需等待更新操作結(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 的方式來(lái)進(jìn)行數(shù)據(jù)存儲(chǔ)的方式來(lái)進(jìn)行數(shù)據(jù)存儲(chǔ) (ibdata1, (ibdata1, ib_logfile0)ib_logfile0)；n 支持事務(wù)、外鍵約束等數(shù)據(jù)庫(kù)特性；支持事務(wù)、外鍵約束等數(shù)據(jù)庫(kù)特性；n 行級(jí)鎖, 讀寫(xiě)性能都非常優(yōu)秀

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

7、目標(biāo)是減少join鏈接鏈接。降低范式標(biāo)準(zhǔn)至第二范式降低范式標(biāo)準(zhǔn)至第二范式目錄目錄n表設(shè)計(jì)表設(shè)計(jì)n正確創(chuàng)建和使用索引正確創(chuàng)建和使用索引nMySQL配置參數(shù)配置參數(shù)nSQL程序走查程序走查n優(yōu)化案例介紹優(yōu)化案例介紹什么是索引？什么是索引？索引的好處與壞處索引的好處與壞處創(chuàng)建索引的好處 n幫助用戶(hù)提高查詢(xún)速度；幫助用戶(hù)提高查詢(xún)速度； n利用索引的唯一性來(lái)控制記錄的唯一性；利用索引的唯一性來(lái)控制記錄的唯一性； n可以加速表與表之間的連接可以加速表與表之間的連接 ；n降低查詢(xún)中分組和排序的時(shí)間降低查詢(xún)中分組和排序的時(shí)間創(chuàng)建索引的壞處n存儲(chǔ)索引占用磁盤(pán)空間；存儲(chǔ)索引占用磁盤(pá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ǎng)景基礎(chǔ)上建立索引；在了解表的具體應(yīng)用場(chǎng)景基礎(chǔ)上建立索引；n為所有主鍵和外鍵列建立索引；為所有主鍵和外鍵列建立索引；n對(duì)出現(xiàn)在對(duì)出現(xiàn)在WHERE子句、子句、JOIN子句、子句、ORDER BY或或GROUP BY子句中的列考慮建立索引子句中的列考慮建立索引；n對(duì)需要確保唯一性的列考慮建立索引；對(duì)需要確保唯一性的列考慮建立索引；n對(duì)于對(duì)于WHEREWHERE子句中用子句中用ANDAND連接并頻繁使用的列使用連接并頻繁使用的列使用組合索引，組合索引，最頻繁的列放在最左

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

10、索引的正確使用索引的正確使用(六六)查詢(xún)條件中使用查詢(xún)條件中使用like時(shí)避免宿主變量以時(shí)避免宿主變量以%開(kāi)頭開(kāi)頭索引的正確使用索引的正確使用(七七)避免使用避免使用“or” ，采用其它方式重寫(xiě)，采用其它方式重寫(xiě)索引的正確使用索引的正確使用(八八)避免使用避免使用，用其它方式改寫(xiě)，用其它方式改寫(xiě)索引的正確使用索引的正確使用(八八)使用組合索引時(shí)，注意使用組合索引時(shí)，注意“最左前綴最左前綴”這個(gè)基本原這個(gè)基本原則則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上述查詢(xún)語(yǔ)句上述查詢(xún)語(yǔ)句因違法“最左前綴”原則，系，系統(tǒng)統(tǒng)通常會(huì)掃描整表以匹配數(shù)據(jù)！以匹配數(shù)據(jù)！目錄目錄n表設(shè)計(jì)表設(shè)計(jì)n正確創(chuàng)建和使用索引正確創(chuàng)建和使用索引nMySQL配置參數(shù)配置參數(shù)nSQL程序走查程序走查n優(yōu)化案例介紹優(yōu)化案例介紹MySQL配置參數(shù)配置參數(shù)(一一)調(diào)整線(xiàn)程緩存的大小調(diào)整線(xiàn)程緩存的大小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ù)名稱(chēng)參數(shù)名稱(chēng)參數(shù)說(shuō)明參數(shù)說(shuō)明設(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每個(gè)線(xiàn)程的堆棧大小每個(gè)線(xiàn)程的堆棧大小默認(rèn)默認(rèn)192KBjoin_buffer_sizeJoin連接操作時(shí)，如果關(guān)聯(lián)的字段連接操作時(shí)，如果關(guān)聯(lián)的字段沒(méi)有索引，會(huì)出現(xiàn)此參數(shù)。沒(méi)有索引，會(huì)出現(xiàn)此參數(shù)。默認(rèn)默認(rèn)128K, 128K256Ktmp_table_sizeMySQL的臨時(shí)表緩沖大小的臨時(shí)表緩沖大小binlog_cache_size在事務(wù)過(guò)程中容納二進(jìn)制日志在事務(wù)過(guò)程中容納二進(jìn)制日志SQL 語(yǔ)句的緩存大小語(yǔ)句的緩存大小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ù)名稱(chēng)參數(shù)名稱(chēng)參數(shù)說(shuō)明參數(shù)說(shuō)明設(shè)置大小設(shè)置大小innodb_buffer_pool_sizeInnoDB使用該參數(shù)指定大小的內(nèi)存來(lái)緩沖數(shù)據(jù)使用該參數(shù)指定大小的內(nèi)存來(lái)緩沖數(shù)據(jù)和索引和索引默認(rèn)默認(rèn)128M,要設(shè)置為物要設(shè)置為物理內(nèi)存的理內(nèi)存的60%70%innodb_additional_mem_pool_size指定

15、指定InnoDB用來(lái)存儲(chǔ)數(shù)據(jù)字典和其他內(nèi)部數(shù)據(jù)用來(lái)存儲(chǔ)數(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用來(lái)存儲(chǔ)日志數(shù)據(jù)的緩存大小，如用來(lái)存儲(chǔ)日志數(shù)據(jù)的緩存大小，如果您的表操作中包含大量并發(fā)事務(wù)（或大規(guī)模事果您的表操作中包含大量并發(fā)事務(wù)（或大規(guī)模事務(wù)），并且在事務(wù)提交前要求記錄日志文件，請(qǐng)務(wù)），并且在事務(wù)提交前要求記錄日志文件，請(qǐng)盡量調(diào)高此項(xiàng)值，以提高日志效率盡量調(diào)高此項(xiàng)值，以提高日志效率默認(rèn)默認(rèn)8M，一般設(shè)置為，一般設(shè)置為16M64M即可。即可。key_buffer_si

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

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

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

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

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

21、查所有檢查所有OR語(yǔ)句語(yǔ)句,對(duì)其進(jìn)行改寫(xiě)，這樣更高效；對(duì)其進(jìn)行改寫(xiě)，這樣更高效；n檢查所有的檢查所有的LIKE語(yǔ)句，確保其宿主變量不以語(yǔ)句，確保其宿主變量不以“%”開(kāi)頭；開(kāi)頭；n檢查所有的組合索引，確保符合檢查所有的組合索引，確保符合“最左前綴最左前綴”原則；原則；n檢查所有的檢查所有的“In”語(yǔ)句，對(duì)其進(jìn)行改寫(xiě)，這樣會(huì)更高效；語(yǔ)句，對(duì)其進(jìn)行改寫(xiě)，這樣會(huì)更高效；n確保宿主變量定義與列數(shù)據(jù)類(lèi)型匹配確保宿主變量定義與列數(shù)據(jù)類(lèi)型匹配目錄目錄n表設(shè)計(jì)表設(shè)計(jì)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)對(duì)字段對(duì)字段 attendance_id 建立索引，避免全表建立索引，避免全表掃描；掃描；n考勤表考勤表(att_attendance_re