第16章

性能優(yōu)化本章概述MySQL性能優(yōu)化就是通過(guò)合理安排資源、調(diào)整系統(tǒng)參數(shù)使MySQL運(yùn)行得更快、更節(jié)省資源。MySQL性能優(yōu)化包括查詢速度優(yōu)化、數(shù)據(jù)庫(kù)結(jié)構(gòu)優(yōu)化、MySQL服務(wù)器優(yōu)化等。本章學(xué)習(xí)目標(biāo)了解什么是數(shù)據(jù)庫(kù)性能優(yōu)化；掌握常用的優(yōu)化查詢的方法；掌握常用的優(yōu)化數(shù)據(jù)庫(kù)結(jié)構(gòu)的方法；掌握常用的優(yōu)化MySQL服務(wù)器的方法；熟練掌握綜合案例中性能優(yōu)化的方法和技巧。目錄01020304優(yōu)化MySQL服務(wù)器優(yōu)化數(shù)據(jù)庫(kù)結(jié)構(gòu)臨時(shí)表性能優(yōu)化優(yōu)化查詢05服務(wù)器語(yǔ)句超時(shí)處理06創(chuàng)建全局通用表空間07本章小結(jié)優(yōu)化查詢MySQL數(shù)據(jù)庫(kù)優(yōu)化是多方面的，原則是減少系統(tǒng)的瓶頸，減少資源的占用，增加系統(tǒng)的反應(yīng)速度。在MySQL中，可以使用SHOWSTATUS語(yǔ)句查詢一些MySQL數(shù)據(jù)庫(kù)的性能參數(shù)。SHOWSTATUS語(yǔ)句語(yǔ)法如下：SHOWSTATUSLIKE'value';優(yōu)化簡(jiǎn)介優(yōu)化查詢MySQL中提供了EXPLAIN語(yǔ)句和DESCRIBE語(yǔ)句，用來(lái)分析查詢語(yǔ)句。語(yǔ)法如下：EXPLAIN[EXTENDED]SELECTselect_options分析查詢語(yǔ)句優(yōu)化查詢分析未使用索引時(shí)的查詢情況，EXPLAIN語(yǔ)句如下：EXPLAINSELECT*FROMtb_bookWHEREauthor="明日科技";在tb_book表的author字段上加上索引，再分析：索引對(duì)查詢速度的影響優(yōu)化查詢使用LIKE關(guān)鍵字的查詢語(yǔ)句使用多列索引的查詢語(yǔ)句使用OR關(guān)鍵字的查詢語(yǔ)句使用索引查詢優(yōu)化查詢?cè)贛ySQL中，可以使用連接（JOIN）查詢來(lái)替代子查詢。連接查詢不需要建立臨時(shí)表，其速度比子查詢要快，如果查詢中使用索引的話，性能就會(huì)更好。連接之所以更有效率，是因?yàn)镸ySQL不需要在內(nèi)存中創(chuàng)建臨時(shí)表來(lái)完成查詢工作。優(yōu)化子查詢目錄優(yōu)化MySQL服務(wù)器優(yōu)化數(shù)據(jù)庫(kù)結(jié)構(gòu)臨時(shí)表性能優(yōu)化優(yōu)化查詢服務(wù)器語(yǔ)句超時(shí)處理創(chuàng)建全局通用表空間本章小結(jié)01020304050607優(yōu)化數(shù)據(jù)庫(kù)結(jié)構(gòu)將字段很多的表分解成多個(gè)表：對(duì)于字段較多的表，如果有些字段的使用頻率很低，那么可以將這些字段分離出來(lái)形成新表。當(dāng)一個(gè)表的數(shù)據(jù)量很大時(shí)，會(huì)由于使用頻率低的字段的存在而變慢。增加中間表：通過(guò)建立中間表，把需要經(jīng)常聯(lián)合查詢的數(shù)據(jù)插入中間表中，然后將原來(lái)的聯(lián)合查詢改為對(duì)中間表的查詢，以此來(lái)提高查詢效率。操作方法為：首先，分析經(jīng)常聯(lián)合查詢表中的字段；然后，使用這些字段建立一個(gè)中間表，并將原來(lái)聯(lián)合查詢的表的數(shù)據(jù)插入中間表中；最后，使用中間表來(lái)進(jìn)行查詢。增加冗余字段：設(shè)計(jì)數(shù)據(jù)庫(kù)表時(shí)應(yīng)盡量遵循范式理論的規(guī)約，盡可能減少冗余字段，讓數(shù)據(jù)庫(kù)設(shè)計(jì)看起來(lái)精致、優(yōu)雅。但是，合理地加入冗余字段可以提高查詢速度。目錄優(yōu)化MySQL服務(wù)器優(yōu)化數(shù)據(jù)庫(kù)結(jié)構(gòu)臨時(shí)表性能優(yōu)化優(yōu)化查詢服務(wù)器語(yǔ)句超時(shí)處理創(chuàng)建全局通用表空間本章小結(jié)01040506070302優(yōu)化MySQL服務(wù)器優(yōu)化MySQL服務(wù)器主要從兩個(gè)方面來(lái)優(yōu)化，一方面是對(duì)硬件進(jìn)行優(yōu)化；另一方面是對(duì)MySQL服務(wù)的參數(shù)進(jìn)行優(yōu)化。（1）配置較大的內(nèi)存。足夠大的內(nèi)存是提高M(jìn)ySQL數(shù)據(jù)庫(kù)性能的方法之一。內(nèi)存的速度比磁盤I/O快得多，可以通過(guò)增加系統(tǒng)的緩沖區(qū)容量使數(shù)據(jù)在內(nèi)存中停留的時(shí)間更長(zhǎng)，以減少磁盤I/O。（2）配置高速磁盤系統(tǒng)，以減少讀盤的等待時(shí)間，提高響應(yīng)速度。（3）合理分布磁盤I/O，把磁盤I/O分散在多個(gè)設(shè)備上，以減少資源競(jìng)爭(zhēng)，提高并行操作能力。（4）配置多處理器，MySQL是多線程的數(shù)據(jù)庫(kù)，多處理器可同時(shí)執(zhí)行多個(gè)線程。優(yōu)化服務(wù)器硬件優(yōu)化MySQL服務(wù)器通過(guò)優(yōu)化MySQL的參數(shù)可以提高資源利用率，從而達(dá)到提高M(jìn)ySQL服務(wù)器性能的目的。MySQL服務(wù)的配置參數(shù)都在f或者my.ini文件的[MySQLd]組中。key_buffer_size：表示索引緩沖區(qū)的大小。table_cache：表示同時(shí)打開的表的個(gè)數(shù)。query_cache_size：表示查詢緩沖區(qū)的大小。sort_buffer_size：表示排序緩存區(qū)的大小。read_buffer_size：表示每個(gè)線程連續(xù)掃描時(shí)為掃描的每個(gè)表分配的緩沖區(qū)的大?。ㄗ止?jié)）。read_rnd_buffer_size：表示為每個(gè)線程保留的緩沖區(qū)的大小。innodb_buffer_pool_size：表示InnoDB類型的表和索引的最大緩存。max_connections：表示數(shù)據(jù)庫(kù)的最大連接數(shù)。innodb_flush_log_at_trx_commit：表示何時(shí)將緩沖區(qū)的數(shù)據(jù)寫入日志文件，并且將日志文件寫入磁盤中。back_log：表示在MySQL暫時(shí)停止回答新請(qǐng)求之前的短時(shí)間內(nèi)，多少個(gè)請(qǐng)求可以被存儲(chǔ)在堆棧中。interactive_timeout：表示服務(wù)器在關(guān)閉連接前等待行動(dòng)的秒數(shù)。優(yōu)化MySQL的參數(shù)目錄優(yōu)化MySQL服務(wù)器優(yōu)化數(shù)據(jù)庫(kù)結(jié)構(gòu)臨時(shí)表性能優(yōu)化優(yōu)化查詢服務(wù)器語(yǔ)句超時(shí)處理創(chuàng)建全局通用表空間本章小結(jié)01050607020403臨時(shí)表性能優(yōu)化MySQL8.0使用CREATETABLESPACE語(yǔ)句來(lái)創(chuàng)建一個(gè)通用表空間。這個(gè)功能可以讓用戶自由地選擇表和表空間之間的映射。優(yōu)化普通SQL臨時(shí)表的性能是MySQL8.0的目標(biāo)之一。首先，通過(guò)優(yōu)化臨時(shí)表在磁盤中的不必要步驟，使得臨時(shí)表的創(chuàng)建和移除成為一個(gè)輕量級(jí)的操作。將臨時(shí)表移動(dòng)到一個(gè)單獨(dú)的表空間中，恢復(fù)臨時(shí)表的過(guò)程就變得非常簡(jiǎn)單，就是在啟動(dòng)時(shí)重新創(chuàng)建臨時(shí)表的單一過(guò)程。MySQL8.0去掉了臨時(shí)表中不必要的持久化。臨時(shí)表僅僅在連接和會(huì)話內(nèi)被創(chuàng)建，然后通過(guò)服務(wù)的生命周期綁定它們。通過(guò)移除不必要的UNDO和REDO日志，改變緩沖和鎖，從而為臨時(shí)表做優(yōu)化操作。目錄優(yōu)化MySQL服務(wù)器優(yōu)化數(shù)據(jù)庫(kù)結(jié)構(gòu)臨時(shí)表性能優(yōu)化優(yōu)化查詢服務(wù)器語(yǔ)句超時(shí)處理創(chuàng)建全局通用表空間本章小結(jié)01060702030504服務(wù)器語(yǔ)句超時(shí)處理在MySQL8.0中可以設(shè)置服務(wù)器語(yǔ)句超時(shí)的限制，單位可以達(dá)到毫秒級(jí)別。當(dāng)中斷的執(zhí)行語(yǔ)句超過(guò)設(shè)置的毫秒數(shù)后，服務(wù)器將終止查詢影響不大的事務(wù)或連接，然后將錯(cuò)誤報(bào)給客戶端。設(shè)置服務(wù)器語(yǔ)句超時(shí)的限制，可以通過(guò)設(shè)置系統(tǒng)變量max_execution_time來(lái)實(shí)現(xiàn)。例如：SETGLOBALMAX_EXECUTION_TIME=2000;默認(rèn)情況下，MAX_EXECUTION_TIME的值為0，代表沒有時(shí)間限制。通過(guò)上述設(shè)置后，如果SELECT語(yǔ)句執(zhí)行超過(guò)2000毫秒，語(yǔ)句就會(huì)被終止。服務(wù)器語(yǔ)句超時(shí)處理目錄優(yōu)化MySQL服務(wù)器優(yōu)化數(shù)據(jù)庫(kù)結(jié)構(gòu)臨時(shí)表性能優(yōu)化優(yōu)化查詢服務(wù)器語(yǔ)句超時(shí)處理創(chuàng)建全局通用表空間本章小結(jié)01070203040605創(chuàng)建全局通用表空間MySQL8.0支持創(chuàng)建全局通用表空間，全局表空間可以被所有的數(shù)據(jù)庫(kù)的表共享，而且相比于獨(dú)享表空間，使用手動(dòng)創(chuàng)建共享表空間可以節(jié)約元數(shù)據(jù)方面的內(nèi)存?？梢栽趧?chuàng)建表的時(shí)候，指定屬于哪個(gè)表空間，也可以對(duì)已有表進(jìn)行表空間修改，具體的信息可以查看官方文檔。下面創(chuàng)建名為dxy的共享表空間，SQL語(yǔ)句如下：CREATETABLESPACEabcADDdatafile‘a(chǎn)bc.ibd’file_block_size=16k;指定表空間，SQL語(yǔ)句如下：CREATETABLEt1(idint,namevarchar(20))engine=innodbdefaultcharsetutf8mb4tablespaceabc;首先刪除依賴該表空間的數(shù)據(jù)表，SQL語(yǔ)句如下：DROPTABLEt1;最后即可刪除表空間，SQL語(yǔ)句如下：DROPTABLESPACEabc;目錄優(yōu)化MySQL服務(wù)器優(yōu)化數(shù)據(jù)庫(kù)結(jié)構(gòu)臨時(shí)表性能優(yōu)化優(yōu)化查詢服務(wù)器語(yǔ)句超時(shí)處理創(chuàng)建全局通用表空間本章小結(jié)020304050706本章小結(jié)MySQL性能優(yōu)化就是通過(guò)合理安排資源、調(diào)整系統(tǒng)參數(shù)使MySQL運(yùn)行得更快、更節(jié)省資源?？梢詮牟樵兯俣取?shù)據(jù)庫(kù)結(jié)構(gòu)、MySQL服務(wù)器等方面來(lái)進(jìn)行優(yōu)化，以提高系統(tǒng)運(yùn)行性能。本章首先詳細(xì)介紹了查詢的優(yōu)