MySQL數(shù)據(jù)庫原理與應(yīng)用(微課版)ch16性能優(yōu)化

第16章

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