MySQL-12.資料庫其他調優策略

长名06發表於2024-06-10
MySql資料庫

C-12.資料庫其他調優策略

1.資料庫調優的措施

1.1 調優的目標

  • 儘可能節省系統資源，以便系統可以提供更大負荷的服務。(吞吐量更大)
  • 合理的結構設計和引數調整，以提高使用者操作響應的速度。(響應速度更快)
  • 減少系統的瓶頸，提高MySQL資料庫整體的效能。

1.2 如何定位調優問題

不過隨著使用者量的不斷增加，以及應用程式複雜度的提升，我們很難用“更快”去定義資料庫調優的目標，因為使用者在不同時間段訪問伺服器遇到的瓶頸不同，比如雙十一促銷的時候會帶來大規模的併發訪問；還有使用者在進行不同業務操作的時候，資料庫的事務處理和SQL查詢都會有所不同。因此我們還需要更加精細的定位，去確定調優的目標。

如何確定呢?一般情況下，有如下幾種方式:

  • 使用者的反饋（主要）

使用者是我們的服務物件，因此他們的反饋是最直接的。雖然他們不會直接提出技術建議，但是有些問題往往是使用者第一時間發現的。我們要重視使用者的反饋，找到和資料相關的問題。

  • 日誌分析（主要）

我們可以透過檢視資料庫日誌和作業系統日誌等方式找出異常情況，透過它們來定位遇到的問題。

  • 伺服器資源使用監控

透過監控伺服器的CPU、記憶體、I/o等使用情況，可以實時瞭解伺服器的效能使用，與歷史情況進行對比。

  • 資料庫內部狀況監控

在資料庫的監控中，活動會話（Active Session）監控是一個重要的指標。透過它，你可以清楚地瞭解資料庫當前是否處於非常繁忙的狀態，是否存在SQL堆積等。

  • 其它

除了活動會話監控以外，我們也可以對事務、鎖等待等進行監控，這些都可以幫助我們對資料庫的執行狀態有更全面的認識。

1.3 調優的維度和步驟

我們需要調優的物件是整個資料庫管理系統，它不僅包括SQL查詢，還包括資料庫的部署配置、架構等。從這個角度來說，我們思考的維度就不僅僅侷限在SQL最佳化上了。透過如下的步驟我們進行梳理：

第一步：選擇合適的DBMS

如果對事務性處理以及安全性要求高的話，可以選擇商業的資料庫產品。這些資料庫在事務處理和查詢效能上都比較強，比如採用SQL Server、Oracle，那麼單表儲存上億條資料是沒有問題的。如果資料表設計得好，即使不採用分庫分表的方式，查詢效率也不差。|

除此以外，你也可以採用開源的MySQL進行儲存，它有很多儲存引擎可以選擇，如果進行事務處理的話可以選擇lnnoDB，非事務處理可以選擇MylSAM。

NoSQL陣營包括鍵值型資料庫、文件型資料庫、搜尋引擎、列式儲存和圖形資料庫。這些資料庫的優缺點和使用場景各有不同，比如列式儲存資料庫可以大幅度降低系統的I/O，適合於分散式檔案系統，但如果資料需要頻繁地增刪改，那麼列式儲存就不太適用了。

DBMS的選擇關係到了後面的整個設計過程，所以第一步就是要選擇適合的DBMS。如果已經確定好了DBMS，那麼這步可以跳過。

第二步：最佳化表設計

選擇了DBMS之後，我們就需要進行表設計了。而資料表的設計方式也直接影響了後續的SQL查詢語句。RDBMS中，每個物件都可以定義為一張表，表與表之間的關係代表了物件之間的關係。如果用的是MySQL我們還可以根據不同表的使用需求，選擇不同的儲存引擎。除此以外，還有一些最佳化的原則可以參考:

1．表結構要儘量遵循三正規化的原則。這樣可以讓資料結構更加清晰規範，減少冗餘欄位，同時也減少了在更新，插入和刪除資料時等異常情況的發生。

2如果查詢使用比較多，尤其是需要進行多表聯查的時候，可以採用反正規化進行最佳化。反正規化採用空間換時間的方式，透過增加冗餘欄位提高查詢的效率。

3．表欄位的資料型別選擇，關係到了查詢效率的高低以及儲存空間的大小。一般來說，如果欄位可以採用數值型別就不要採用字元型別;字元長度要儘可能設計得短一些。針對字元型別來說，當確定字元長度固定時，就可以採用CHAR型別;當長度不固定時，通常採用VARCHAR型別。

資料表的結構設計很基礎，也很關鍵。好的表結構可以在業務發展和使用者量增加的情況下依然發揮作用，不好的表結構設計會讓資料表變得非常臃腫，查詢效率也會降低。

第三步：最佳化邏輯查詢

當我們建立好資料表之後，就可以對資料表進行增刪改查的操作了。這時我們首先需要考慮的是邏輯查詢最佳化。

SQL查詢最佳化，可以分為邏輯查詢最佳化和物理查詢最佳化。邏輯查詢最佳化就是透過改變sQL語句的內容讓sQL執行效率更高效，採用的方式是對sQL語句進行等價變換，對查詢進行重寫。

SQL的查詢重寫包括了子查詢最佳化、等價謂詞重寫、檢視重寫、條件簡化、連線消除和巢狀連線消除等。

比如我們在講解EXISTS子查詢和IN子查詢的時候，會根據小表驅動大表的原則選擇適合的子查詢在WHERE子句中會盡量避免對欄位進行函式運算，它們會讓欄位的索引失效。

舉例：查詢評論內容開頭為abc的內容都有哪些，如果在WHERE子句中使用了函式，語句就會寫成下面這樣:

SELECT comment_id,comment_text,comment_time FROM product_comment 
WHERE SUBSTRING(comment_text,1,3)='abc';

採用查詢重寫的方式進行等價替換

SELECT comment_id,comment_text,comment_time FROM product_comment 
WHERE comment_text like 'abc%';
第四步：最佳化物理查詢

物理查詢最佳化是在確定了邏輯查詢最佳化之後，採用物理最佳化技術〈比如索引等)，透過計算代價模型對各種可能的訪問路徑進行估算，從而找到執行方式中代價最小的作為執行計劃。在這個部分中，我們需要掌握的重點是對索引的建立和使用。

但索引不是萬能的，我們需要根據實際情況來建立索引。那麼都有哪些情況需要考慮呢?我們在前面幾章中已經進行了細緻的剖析。

SQL查詢時需要對不同的資料表進行查詢，因此在物理查詢最佳化階段也需要確定這些查詢所採用的路徑，具體的情況包括:

  1. 單表掃描∶對於單表掃描來說，我們可以全表掃描所有的資料，也可以區域性掃描。
  2. 兩張表的連線：常用的連線方式包括了巢狀迴圈連線、HASH 連線和合並連線。
  3. 多張表的連線：多張資料表進行連線的時候，順序很重要，因為不同的連線路徑查詢的效率不同，搜尋空間也會不同。我們在進行多表連線的時候，搜尋空間可能會達到很高的資料量級，巨大的搜尋空間顯然會佔用更多的資源，因此我們需要透過調整連線順序，將搜尋空間調整在一個可接受的範圍內。
第五步：使用Redis或Memcached作為快取

除了可以對SQL本身進行最佳化以外，我們還可以請外援提升查詢的效率。

因為資料都是存放到資料庫中，我們需要從資料庫層中取出資料放到記憶體中進行業務邏輯的操作，當使用者量增大的時候，如果頻繁地進行資料查詢，會消耗資料庫的很多資源。如果我們將常用的資料直接放到記憶體中，就會大幅提升查詢的效率。

鍵值儲存資料庫可以幫我們解決這個問題。

常用的鍵值儲存資料庫有Redis和Memcached，它們都可以將資料存放到記憶體中。

從可靠性來說，Redis支援持久化，可以讓我們的資料儲存在硬碟上不過這樣一來效能消耗也會比較大。而Memcached僅僅是記憶體儲存，不支援持久化。

從支援的資料型別來說，Redis 比 Memcached 要多，它不僅支援 key-value型別的資料，還支援ListSet，Hash等資料結構。當我們有持久化需求或者是更高階的資料處理需求的時候，就可以使用Redis。如果是簡單的key-value儲存，則可以使用Memcached。

通常我們對於查詢響應要求高的場景（響應時間短，吞吐量大)，可以考慮記憶體資料庫，畢竟術業有專攻。傳統的RDBMS都是將資料儲存在硬碟上，而記憶體資料庫則存放在記憶體中，查詢起來要快得多。不過使用不同的工具，也增加了開發人員的使用成本。

第六步：庫級最佳化

庫級最佳化是站在資料庫的維度上進行的最佳化策略，比如控制一個庫中的資料表數量。另外，單一的資料庫總會遇到各種限制，不如取長補短，利用"外援""的方式。透過主從架構最佳化我們的讀寫策略，透過對資料庫進行垂直或者水平切分，突破單━資料庫或資料表的訪問限制，提升查詢的效能。

1、讀寫分離

如果讀和寫的業務量都很大，並且它們都在同一個資料庫伺服器中進行操作，那麼資料庫的效能就會出現瓶頸，這時為了提升系統的效能，最佳化使用者體驗，我們可以採用讀寫分離的方式降低主資料庫的負載，比如用主資料庫(master)完成寫操作，用從資料庫(slave)完成讀操作。


2、資料分片

對資料庫分庫分表。當資料量級達到千萬級以上時，有時候我們需要把一個資料庫切成多份，放到不同的資料庫伺服器上，減少對單一資料庫伺服器的訪問壓力。如果你使用的是MySQL，就可以使用MysQL自帶的分割槽表功能，當然你也可以考慮自己做垂直拆分（分庫)、水平拆分（分表)、垂直+水平拆分(分庫分表)。


但需要注意的是，分拆在提升資料庫效能的同時，也會增加維護和使用成本。

2.最佳化MySQL伺服器

最佳化MysQL伺服器主要從兩個方面來最佳化，一方面是對硬體進行最佳化;另一方面是對MySQL服務的引數進行最佳化。這部分的內容需要較全面的知識，一般只有專業的資料庫管理員才能進行這一類的最佳化。對於可以定製引數的作業系統，也可以針對MySQL進行作業系統最佳化。

2.1 最佳化伺服器硬體

伺服器的硬體效能直接決定著MySQL資料庫的效能。硬體的效能瓶頸直接決定MySQL資料庫的執行速度和效率。針對效能瓶頸提高硬體配置，可以提高MySQL資料庫查詢、更新的速度。
(1）配置較大的記憶體。足夠大的記憶體是提高MySQL資料庫效能的方法之一。記憶體的速度比磁碟I/O快得多，可以透過增加系統的緩衝區容量使資料在記憶體中停留的時間更長，以減少磁碟I/0。

(2）配置高速磁碟系統，以減少讀盤的等待時間，提高響應速度。磁碟的I/o能力，也就是它的尋道能力，目前的SCSI高速旋轉的是7200轉/分鐘，這樣的速度，一旦訪問的使用者量上去，磁碟的壓力就會過大,如果是每天的網站pv (page view)在150w，這樣的一般的配置就無法滿足這樣的需求了。現在SSD盛行，在SSD上隨機訪問和順序訪問效能幾乎差不多，使用SSD可以減少隨機IO帶來的效能損耗。

(3）合理分佈磁碟I/0，把磁碟I/O分散在多個裝置上，以減少資源競爭，提高並行操作能力。

(4）配置多處理器，MySQL是多執行緒的資料庫，多處理器可同時執行多個執行緒。

2.2 最佳化MySQL的引數

透過最佳化MySQL的引數可以提高資源利用率，從而達到提高MySQL伺服器效能的目的。

MySQL服務的配置引數都在my.cnf或者my.ini檔案的[mysqlq]組中。配置完引數以後，需要重新啟動MySQL服務才會生效。

下面對幾個對效能影響比較大的引數進行詳細介紹。

  • innodb_buffer_pool_size：這個引數是Mysql資料庫最重要的引數之一，表示InnoDB型別的表和索引的最大快取。它不僅僅快取索引資料，還會快取表的資料。這個值越大，查詢的速度就會越快。但是這個值太大會影響作業系統的效能。

  • key_buffer_size：表示索引緩衝區的大小。索引緩衝區是所有的執行緒共享。增加索引緩衝區可以得到更好處理的索引(對所有讀和多重寫)。當然，這個值不是越大越好，它的大小取決於記憶體的大小。如果這個值太大，就會導致作業系統頻繁換頁，也會降低系統效能。對於記憶體在4GB左右的伺服器該引數可設定為256M或384M。

  • table_cache：表示同時開啟的表的個數。這個值越大，能夠同時開啟的表的個數越多。實體記憶體越大，設定就越大。預設為2402，調到512-1024最佳。這個值不是越大越好，因為同時開啟的表太多會影響作業系統的效能。

  • query_cache_size：表示查詢緩衝區的大小。可以透過在MySQL控制檯觀察，如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩衝不夠的情況，就要增加Query_cache_size的值;如果Qcache_hits的值非常大，則表明查詢緩衝使用非常頻繁，如果該值較小反而會影響效率，那麼可以考慮不用查詢快取;
    Qcache_free_blocks，如果該值非常大，則表明緩衝區中碎片很多。MySQL8.0之後失效。該引數需要和query_cache_type配合使用。

  • query_cache_type的值是0時，所有的查詢都不使用查詢快取區。但是query_cache_type=0並不會導致MySQL釋放query_cache_size所配置的快取區記憶體。

    • 當query_cache_type=1時，所有的查詢都將使用查詢快取區，除非在查詢語句中指定SQL_NO_CACHE，如SELECT SQL_NO_CACHE * FROM tbl_name。

    • 當query_cache_type=2時，只有在查詢語句中使用SQL_CACHE關鍵字，查詢才會使用查詢快取區。使用查詢快取區可以提高查詢的速度，這種方式只適用於修改操作少且經常執行相同的查詢操作的情況。

  • sort_buffer_size：表示每個需要進行排序的執行緒分配的緩衝區的大小。增加這個引數的值可以提高ORDER BY或GROUP BY操作的速度。預設數值是2 097 144位元組(約2MB)。對於記憶體在4GB左右的伺服器推薦設定為6-8M，如果有100個連線，那麼實際分配的總共排序緩衝區大小為100 x6 = 600MB。

  • join_buffer_size = 8M：表示聯合查詢操作所能使用的緩衝區大小，和sort_buffer_size一樣，該引數對應的分配記憶體也是每個連線獨享。

  • read_buffer_size：表示每個執行緒連續掃描時為掃描的每個表分配的緩衝區的大小(位元組)。當執行緒從表中連續讀取記錄時需要用到這個緩衝區。SET SESSION read_buffer_size=n可以臨時設定該引數的值。預設為64K，可以設定為4M。

  • innodb_flush_log_at_trx_commit：表示何時將緩衝區的資料寫入日誌檔案，並且將日誌檔案寫入磁碟中。該引數對於innoDB引擎非常重要。該引數有3個值，分別為0、1和2。該引數的預設值為1。

    • 值為0時，表示每秒1次的頻率將資料寫入日誌檔案並將日誌檔案寫入磁碟。每個事務的commit並不會觸發前面的任何操作。該模式速度最快，但不太安全，mysqld程序的崩潰會導致上一秒鐘所有事務資料的丟失。

    • 值為1時，表示每次提交事務時將資料寫入日誌檔案並將日誌檔案寫入磁碟進行同步。該模式是最安全的，但也是最慢的一種方式。因為每次事務提交或事務外的指令都需要把日誌寫入(flush）硬碟。

    • 值為2時，表示每次提交事務時將資料寫入日誌檔案，每隔1秒將日誌檔案寫入磁碟。該模式速度較快，也比0安全，只有在作業系統崩潰或者系統斷電的情況下，上一秒鐘所有事務資料才可能丟失。

  • innodb_log_buffer_size：這是InnoDB儲存引擎的事務日誌所使用的緩衝區。為了提高效能也是先將資訊寫入Innodb Log Buffer中，當滿足innodb_flush_log_trx_commit引數所設定的相應條件（或者日誌緩衝區寫滿)之後，才會將日誌寫到檔案(或者同步到磁碟）中。

  • max_connections：表示允許連線到MySQL資料庫的最大數量，預設值是151。如果狀態變數connection_errors_max_connections 不為零，並且一直增長，則說明不斷有連線請求因資料庫連線數已達到允許最大值而失敗，這是可以考慮增大max_connections 的值。在Linux平臺下，效能好的伺服器，支援500-1000個連線不是難事，需要根據伺服器效能進行評估設定。這個連線數不是越大越好，因為這些連線會浪費記憶體的資源。過多的連線可能會導致MySQL伺服器僵死。

  • back_log：用於控制MySQL監聽TCP埠時設定的積壓請求棧大小。如果MySql的連線數達到max_connections時，新來的請求將會被存在堆疊中，以等待某一連線釋放資源，該堆疊的數量即back_log,如果等待連線的數量超過back_log，將不被授予連線資源，將會報錯。5.6.6版本之前預設值為50，之後的版本預設為50+ (max_connections / 5)，對於Linux系統推薦設定為小於512的整數，但最大不超過900。如果需要資料庫在較短的時間內處理大量連線請求，可以考慮適當增大back_log的值。

  • thread_cache_size：執行緒池快取執行緒數量的大小，當客戶端斷開連線後將當前執行緒快取起來當在接到新的連線請求時快速響應無需建立新的執行緒。這尤其對那些使用短連線的應用程式來說可以極大的提高建立連線的效率。那麼為了提高效能可以增大該引數的值。預設為60，可以設定為120。

​ 可以透過如下幾個MySQL狀態值來適當調整執行緒池的大小:

mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 2     |
| Threads_connected | 1     |
| Threads_created   | 3     |
| Threads_running   | 2     |
+-------------------+-------+
4 rows in set (0.01 sec)

​ 當Threads_cached越來越少，但Threads_connected 始終不降，且Threads_created持續升高，可適當增加thread_cache_size的大小。

  • wait_timeout：指定一個請求的最大連線時間，對於4GB左右記憶體的伺服器可以設定為5-10。
  • interactive_timeout：表示伺服器在關閉連線前等待行動的秒數。

這裡給出一份my.cnf的參考配置:

[mysqld]

port = 3306 

serverid = 1 

socket = /tmp/mysql.sock skip-locking #避免MySQL的外部鎖定，減少出錯機率增強穩定性。 

skip-name-resolve #禁止MySQL對外部連線進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間。但需要注意，如果開啟該選項，則所有遠端主機連線授權都要使用IP地址方式，否則MySQL將無法正常處理連線請求！ 

back_log = 384

key_buffer_size = 256M 

max_allowed_packet = 4M 

thread_stack = 256K

table_cache = 128K 

sort_buffer_size = 6M 

read_buffer_size = 4M

read_rnd_buffer_size=16M 

join_buffer_size = 8M 

myisam_sort_buffer_size =64M 

table_cache = 512 

thread_cache_size = 64 

query_cache_size = 64M

tmp_table_size = 256M 

max_connections = 768 

max_connect_errors = 10000000

wait_timeout = 10 

thread_concurrency = 8 

#該引數取值為伺服器邏輯CPU數量*2，在本例中，伺服器有2顆物理CPU，而每顆物理CPU又支援H.T超執行緒，所以實際取值為4*2=8 

skip-networking #開啟該選項可以徹底關閉MySQL的TCP/IP連線方式，如果WEB伺服器是以遠端連線的方式訪問MySQL資料庫伺服器則不要開啟該選項！否則將無法正常連線！

 table_cache=1024

innodb_additional_mem_pool_size=4M #預設為2M 

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=2M #預設為1M

 innodb_thread_concurrency=8 #你的伺服器CPU有幾個就設定為幾。建議用預設一般為8 

tmp_table_size=64M #預設為16M，調到64-256最掛

thread_cache_size=120 

query_cache_size=32M

舉例：
下面是一個電商平臺，類似京東或天貓這樣的平臺。商家購買服務，入住平臺，開通之後，商家可以在系統中上架各種商品，客戶透過手機App、微信小程式等渠道購買商品，商家接到訂單以後安排快遞送貨。

剛剛上線的時候，系統執行狀態良好。但是，隨著入住的商家不斷增多，使用系統的使用者量越來越多，每天的訂單資料達到了5萬條以上。這個時候，系統開始出現問題，CPU使用率不斷飆升。終於，雙十一或者618活動高峰的時候，CPU使用率達到99%，這實際上就意味著，系統的計算資源已經耗盡，再也無法處理任何新的訂單了。換句話說，系統已經崩潰了。

這個時候，我們想到了對系統引數進行調整，因為引數的值決定了資源配置的方式和投放的程度.為了解決這個問題，

一共調整3個系統引數，分別是

  • lnnoDB_flush_log_at_trx_commit
  • lnnoDB_buffer_pool_size
  • lnnoDB_buffer_pool_instances

下面我們就說—說調整這三個引數的原因是什麼。

（1）調整系統引數lnnoDB_flush_log_at_trx_commit

這個引數適用於InnoDB儲存引擎，電商平臺系統中的表用的儲存引擎都是InnoDB。預設的值是1意思是每次提交事務的時候，都把資料寫入日誌，並把日誌寫入磁碟。這樣做的好處是資料安全性最佳，不足之處在於每次提交事務，都要進行磁碟寫入的操作。在大併發的場景下，過於頻繁的磁碟讀寫會導致CPU資源浪費，系統效率變低。

這個引數的值還有2個可能的選項，分別是0和2。我們把這個引數的值改成了2。這樣就不用每次提交事務的時候都啟動磁碟讀寫了，在大併發的場景下，可以改善系統效率，降低CPU使用率。即便出現故障，損失的資料也比較小。

（2）調整系統引數lnnoDB_buffer_pool_size

這個引數的意思是，InnoDB儲存引擎使用快取來儲存索引和資料。這個值越大，可以載入到快取區的索引和資料量就越多，需要的磁碟讀寫就越少。

因為我們的MySQL伺服器是資料庫專屬伺服器，只用來執行MySQL資料庫服務，沒有其他應用了，而我們的計算機是64位機器，記憶體也有128G。於是我們把這個引數的值調整為64G。這樣一來，磁碟讀寫次數可以大幅降低，我們就可以充分利用記憶體，釋放出一些CPU的資源。

（3）調整系統引數lnnoDB_buffer_pool_instances

這個引數可以將InnoDB的快取區分成幾個部分，這樣可以提高系統的並行處理能力，因為可以允許多個程序同時處理不同部分的快取區。

我們把InnoDB_buffer_pool_instances的值修改為64，意思就是把InnoDB的快取區分成64個分割槽這樣就可以同時有多個執行緒進行資料操作，CPU的效率就高多了。修改好了系統引數的值，要重啟MySQL資料庫伺服器。

總結一下就是遇到CPU資源不足的問題，可以從下面2個思路去解決。

疏通擁堵路段，消除瓶頸，讓等待的時間更短;

開拓新的通道，增加並行處理能力。

3.最佳化資料庫結構

一個好的資料庫設計方案對於資料庫的效能常常會起到事半功倍的效果。合理的資料庫結構不僅可以使資料庫佔用更小的磁碟空間，而且能夠使查詢速度更快。資料庫結構的設計需要考慮資料冗餘、查詢和更新的速度、欄位的資料型別是否合理等多方面的內容。

3.1 拆分表：冷熱資料分離

拆分表的思路是，把1個包含很多欄位的表拆分成2個或者多個相對較小的表。這樣做的原因是這些表中某些欄位的操作頻率很高（熱資料），經常要進行查詢或者更新操作，而另外一些欄位的使用頻率卻很低（冷資料），冷熱資料分離，可以減小表的寬度。如果放在一個表裡面，每次查詢都要讀取大記錄，會消耗較多的資源。

MySQL限制每個表最多儲存4096列，並且每一行資料的大小不能超過65535位元組。表越寬，把表裝載進記憶體緩衝池時所佔用的記憶體也就越大，也會消耗更多的I0。冷熱資料分離的目的是:①減少磁碟IO，保證熱資料的記憶體快取命中率。②更有效的利用快取，避免讀入無用的冷資料。

舉例1:會員members表儲存會員登入認證資訊，該表中有很多欄位，如id、姓名、密碼、地址、電話、個人描述欄位。其中地址、電話、個人描述等欄位並不常用，可以將這些不常用的欄位分解出另一個表。將這個表耿名叫members_detail，表中有member_id、address、telephone、description等欄位。這樣就把會員表分成了兩個表，分別為members表和members_detail表。

CREATE TABLE members(
	id int(11) NOT NULL AUTO_INCREMENT，
    username varchar(50)
    DEFAULT NULL,
    password varchar(50) DEFAULTNULL，
    last_login_time datetime DEFAULT NULL,
    last_login_ip varchar(100)DEFAULT NULL,
    PRIMARY KEY(Id)
);

CREATE TABLE members_detail (
	Member_id int(11)NOTNULL DEFAULT 0,
    address varchar(255)DEFAULT NULL，
    telephone varchar(255)DEFAULT NULL,
    description text
) ;

如果需要查詢會員的基本資訊或詳細資訊，那麼可以用會員的id來查詢。如果需要將會員的基本資訊和詳細資訊同時顯示，那麼可以將members表和members_detail表進行聯合查詢，查詢語句如下:

SELECT * FROM members LEFT JOIN members_detail on members.id = members_detail.member_id ;

透過這種分解可以提高表的查詢效率。對於欄位很多且有些欄位使用不頻繁的表，可以透過這種分解的方式來最佳化資料庫的效能。

3.2 增加中間表

對於需要經常聯合查詢的表，可以建立中間表以提高查詢效率。透過建立中間表，把需要經常聯合查詢的資料插入中間表中，然後將原來的聯合查詢改為對中間表的查詢，以此來提高查詢效率。

首先，分析經常聯合查詢表中的欄位;然後，使用這些欄位建立一箇中間表，並將原來聯合查詢的表的資料插入中間表中;最後，使用中間表來進行查詢。

CREATE TABLE `class` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `className` VARCHAR(30) DEFAULT NULL,
    `address` VARCHAR(40) DEFAULT NULL,
    `monitor` INT NULL ,
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `stuno` INT NOT NULL ,
    `name` VARCHAR(20) DEFAULT NULL,
    `age` INT(3) DEFAULT NULL,
    `classId` INT(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

現在有一個模組需要經常查詢帶有學生名稱（name）、學生所在班級名稱（className）、學生班級班長（monitor）的學生資訊。根據這種情況可以建立一個 temp_student 表。temp_student表中儲存學生名稱（stu_name）、學生所在班級名稱（className）和學生班級班長（monitor）資訊。建立表的語句如下：

CREATE TABLE `temp_student` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `stu_name` INT NOT NULL ,
    `className` VARCHAR(20) DEFAULT NULL,
    `monitor` INT(3) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

接下來，從學生資訊表和班級表中查詢相關資訊儲存到臨時表中：

insert into temp_student(stu_name,className,monitor)
        select s.name,c.className,c.monitor
        from student as s,class as c
        where s.classId = c.id

以後，可以直接從temp_student表中查詢學生名稱、班級名稱和班級班長，而不用每次都進行聯合查詢。這樣可以提高資料庫的查詢速度。

如果使用者資訊修改了，是不是會導致temp_vip中的資料不一致的問題呢?如何同步資料呢?

方式1:清空資料->重新新增資料

方式2:使用檢視

3.3 增加冗餘欄位

設計資料庫表時應儘量遵循正規化理論的規約，儘可能減少冗餘欄位，讓資料庫設計看起來精緻、優雅。但是，合理地加入冗餘欄位可以提高查詢速度。

表的規範化程度越高，表與表之間的關係就越多，需要連線查詢的情況也就越多。尤其在資料量大，而且需要頻繁進行連線的時候，為了提升效率，我們也可以考慮增加冗餘欄位來減少連線。

這部分內容在，《第11章.資料庫的設計規範》 章節中反正規化化小節中具體展開講解了。這裡省略。

3.4 最佳化資料型別

改進表的設計時，可以考慮最佳化欄位的資料型別。這個問題在大家剛從事開發時基本不算是問題。但是，隨著你的經驗越來越豐富，參與的專案越來越大，資料量也越來越多的時候，你就不能只從系統穩定性的角度來思考問題了，還要考慮到系統整體的穩定性和效率。此時，優先選擇符合儲存需要的最小的資料型別。

列的欄位越大，建立索引時所需要的空間也就越大，這樣一頁中所能儲存的索引節點的數量也就越少，在遍歷時所需要的IO次數也就越多，索引的效能也就越差。

具體來說:

情況1：對整數型別資料進行最佳化

遇到整數型別的欄位可以用INT型。這樣做的理由是，INT 型資料有足夠大的取值範圍，不用擔心資料超出取值範圍的問題。剛開始做專案的時候，首先要保證系統的穩定性，這樣設計欄位型別是可以的。但在資料量很大的時候，資料型別的定義，在很大程度上會影響到系統整體的執行效率。

對於非負型的資料（(如自增ID、整型IP）來說，要優先使用無符號整型UNSIGNED來儲存。因為無符號相對於有符號，同樣的位元組數，儲存的數值範圍更大。如tinyint有符號為-128-127，無符號為0-255，多出一倍的儲存空間。

情況2：:既可以使用文字型別也可以使用整數型別的欄位，要選擇使用整數型別

跟文字型別資料相比，大整數往往佔用更少的儲存空間，因此，在存取和比對的時候，可以佔用更少的記憶體空間。所以，在二者皆可用的情況下，儘量使用整數型別，這樣可以提高查詢的效率。如:將IP地址轉換成整型資料。

情況3：避免使用TEXT、BLOB資料型別

MySQL記憶體臨時表不支援TEXT、BLOB這樣的大資料型別，如果查詢中包含這樣的資料，在排序等操作時，就不能使用記憶體臨時表，必須使用磁碟臨時表進行。並且對於這種資料，MySQL還是要進行二次查詢，會使sQL效能變得很差，但是不是說一定不能使用這樣的資料型別。

如果一定要使用，建議把BLOB或是TEXT列分離到單獨的擴充套件表中，查詢時一定不要使用select*，而只需要取出必要的列，不需要TEXT列的資料時不要對該列進行查詢。

情況4：避免使用ENUM型別

修改ENUM值需要使用ALTER語句。

ENUM型別的ORDER BY操作效率低，需要額外操作。使用TINYINT來代替ENUM型別。

情況5：使用TIMESTAMP儲存時間
TIMESTAMP儲存的時間範圍1970-01-0100:00:01~2038-01-19-03:14:07。TIMESTAMP使用4位元組，DATETIME使用8個位元組，同時TIMESTAMP具有自動賦值以及自動更新的特性。

情況6：用DEMAL代替FLOAT和DOUBLE儲存精確浮點數

1)非精準浮點: float,double

2)精準浮點: decimal

Decimal型別為精準浮點數，在計算時不會丟失精度，尤其是財務相關的金融類資料。佔用空間由定義的寬度決定，每4個位元組可以儲存9位數字，並且小數點要佔用一個位元組。可用於儲存比bigint更大的整型資料。

總之，遇到資料量大的專案時，一定要在充分了解業務需求的前提下，合理最佳化資料型別，這樣才能充分發揮資源的效率，使系統達到最優。

3.5 最佳化插入記錄的速度

插入記錄時，影響插入速度的主要是索引、唯一性校驗、一次插入記錄條數等。根據這些情況可以分別進行最佳化。這裡我們分為MylSAM引擎和InnoDB儲存引擎來講。

1.MylSAM引擎的表:

① 禁用索引

對於非空表，插入記錄時，MysQL會根據表的索引對插入的記錄建立索引。如果插入大量資料，建立索引就會降低插入記錄的速度。為了解決這種情況，可以在插入記錄之前禁用索引，資料插入完畢後再開啟索引。禁用索引的語句如下:

ALTER TABLE table_name DISABLE KEYS;

開啟

ALTER TABLE table_name ENABLE KEYS;

若對於空表批次匯入資料，則不需要進行此操作，因為MylISAM引擎的表是在匯入資料之後才建立索引的。

② 禁用唯一性檢查

插入資料時，MysQl會對插入的記錄進行唯一性校驗。這種唯一性校驗會降低插入記錄的速度。為了降低這種情況對查詢速度的影響，可以在插入記錄之前禁用唯一性檢查，等到記錄插入完畢後再開啟。

修改唯一性檢查的語句如下:

SET UNIQUE_CHECK = 0;//0是禁用，1是開啟

③使用批次插入

插入多條記錄時，可以使用一條INSERT語句插入一條記錄，也可以使用一條INSERT語句插入多條記錄。插入一條記錄的INSERT語句情形如下:

insert into student values (1,'zhangsan',18,1);
insert into student values (2,'lisi',17，1);
insert into student values (3,'wangwu',17，1);
insert into student values (4,'zhaoliu',19, 1);

使用一條INSERT語句插入多條記錄的情形如下:

insert into student values (1,'zhangsan',18,1),(2,'lisi',17，1),(3,'wangwu',17，1),(4,'zhaoliu',19, 1);

第2種情形的插入速度要比第1種情形快。

④使用LOAD DATA INFILE批次匯入

當需要批次匯入資料時，如果能用LOAD DATA INFILE語句，就儘量使用。因為LOAD DATA INFILE語句匯入資料的速度比INSERT語句快。

LOAD DATA [LOCAL] INFILE 'file_path'
INTO TABLE table_name
[FIELDS TERMINATED BY 'char' 
    [ENCLOSED BY 'char' 
    [ESCAPED BY 'char']]
    [LINES TERMINATED BY 'char']]
[IGNORE lines_number LINES]
[(col_name,...)]
[SET col_name={expr,...}]
[WHERE where_condition];
  • LOCAL：如果使用 LOCAL 關鍵字，資料檔案可以在客戶端上，而不是伺服器上。否則，檔案必須位於伺服器上。
  • file_path：檔案的路徑和名稱。
  • table_name：要匯入資料的表的名稱。
  • FIELDS TERMINATED BY, ENCLOSED BY, ESCAPED BY：這些子句定義了檔案中欄位如何分隔，欄位值是否被引號包圍，以及跳脫字元是什麼。
  • LINES TERMINATED BY：定義每行資料如何分隔。
  • IGNORE lines_number LINES：忽略檔案開頭的若干行，通常用於跳過標題行。
  • (col_name,...)：可以選擇性地列出要匯入的列名。
  • SET col_name={expr,...}：用於計算或轉換欄位值。
  • WHERE where_condition：可以新增條件過濾匯入的資料。

以上，關於LOAD DATA INFILE語句的語法和關鍵詞解析，來自通義千問大模型生成。

2.InnoDB引擎的表:

① 禁用唯一性檢查

插入資料之前執行set unique_checks=0來禁止對唯一索引的檢查，資料匯入完成之後冉執行setunique_checks=1。這個和MylSAM引擎的使用方法一樣。

② 禁用外來鍵檢查

插入資料之前執行禁止對外來鍵的檢查，資料插入完成之後再恢復對外來鍵的檢查。禁用外來鍵檢查的語句如下:

SET foreign_key_checks=0;

恢復對外來鍵的檢查語句如下:

SET foreign_key_checks=1;

③ 禁止自動提交

插入資料之前禁止事務的自動提交，資料匯入完成之後，執行恢復自動提交操作。禁止自動提交的語句如下:

set autocommit = 0;

恢復自動提交的語句如下:

set autocommit = 1;

3.6 使用非空約束

在設計欄位的時候，如果業務允許，建議儘量使用非空約束。這樣做的好處是:

①進行比較和計算時，省去要對NULL值的欄位判斷是否為空的SQL語句編寫開銷。

②非空欄位也容易建立索引。因為索引NULL列需要額外的空間來儲存，所以要佔用更多的空間。使用非空約束，就可以節省儲存空間(每個欄位1個bit)。

3.7 分析表，檢查表與最佳化表

MySQL提供了分析表、檢查表和最佳化表的語句。分析表主要是分析關鍵字的分佈，檢查表主要是檢查表是否存在錯誤，最佳化表主要是消除刪除或者更新造成的空間浪費。

1.分析表

MySQL中提供了ANALYZE TABLE語句分析表，ANALYZE TABLE語句的基本語法如下:

 ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [ , tbl_name ]...

預設的，MySQL服務會將ANALNZE TABLE語句寫到binlog中，以便在主從架構中，從服務能夠同步資料。可以新增引數LOCAL或者NO_WRITE_TO_BINLOG取消將語句寫到binlog中。

使用ANALYZE TABLE分析表的過程中，資料庫系統會自動對錶加一個只讀鎖。在分析期間，只能讀取表中的記錄，不能更新和插入記錄。ANALYZE TABLE語句能夠分析InnoDB和MyISAM型別的表，但是不能作用於檢視。

ANALYZE TABLE分析後的統計結果會反應到cardinality的值，該值統計了表中某一鍵所在的列不重複的值的個數。該值越接近表中的總行數，則在表連線查詢或者索引查詢時，就越優先被最佳化器選擇使用。也就是索引列的cardinality的值與表中資料的總條數差距越大，即使查詢的時候使用了該索引作為查詢條件，儲存引擎實際查詢的時候使用的機率就越小。下面透過例子來驗證下。cardinality可以透過SHOW INDEX FROM表名檢視。

舉例說明

08-資料庫的其他最佳化策略
CREATE TABLE `user1` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL,
  `age` INT DEFAULT NULL,
  `sex` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3;

#######
SET GLOBAL log_bin_trust_function_creators = 1;

DELIMITER //
CREATE FUNCTION  rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN   
DECLARE i INT DEFAULT 0;  
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1))   ;
RETURN i;  
END //
DELIMITER ;

###
DELIMITER //
CREATE PROCEDURE  insert_user( max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO `user1` ( NAME,age,sex ) 
 VALUES ("atguigu",rand_num(1,20),"male");  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT; 
END //
DELIMITER;

##
CALL insert_user(1000);

SHOW INDEX FROM user1;

SELECT * FROM user1;

UPDATE user1 SET NAME = 'atguigu03' WHERE id = 3;

mysql> analyze table user1;
+------------------+---------+----------+----------+
| Table            | Op      | Msg_type | Msg_text |
+------------------+---------+----------+----------+
| atguigudb3.user1 | analyze | status   | OK       |
+------------------+---------+----------+----------+
1 row in set (0.00 sec)

analyze語句的作用，就是，在修改了某個列的值時，這個列還存在索引的話，對於該列的區分度的值，不會立刻重新整理，執行了分析語句後，就會從新計算區分度的值。

2.檢查表

MysQL中可以使用CHECK TABLE語句來檢查表。CHECK TABLE語句能夠檢查InnoDB和MylSAM型別的表是否存在錯誤。CHECK TABLE語句在執行過程中也會給表加上只讀鎖。

對於MyISAM型別的表，CHECK TABLE語句還會更新關鍵字統計資料。而且，CHECK TABLE也可以檢查檢視是否有錯誤，比如在檢視定義中被引用的表已不存在。該語句的基本語法如下:

CHECK TABLE tbl_name [ , tbl_name ] ... [ option] ...
option = {QUICK | FAST |MEDIUM | EXTENDED | CHANGED}

其中，tbl_name是表名; option引數有5個取值，分別是QUICK、FAST、MEDIUM、EXTENDED和CHANGED。各個選項的意義分別是:

  • QUIcK: 不掃描行，不檢查錯誤的連線。
  • FAST: 只檢查沒有被正確關閉的表。
  • CHANGED: 只檢查上次檢查後被更改的表和沒有被正確關閉的表。
  • MEDIUM: 掃描行，以驗證被刪除的連線是有效的。也可以計算各行的關鍵字校驗和，並使用計算出的校驗和驗證這一點。
  • EXTENDED: 對每行的所有關鍵字進行一個全面的關鍵字查詢。這可以確保表是100%一致的，但是花的時間較長。

option只對MyISAM型別的表有效，對InnoDB型別的表無效。比如:

mysql> check table user1;
+------------------+-------+----------+----------+
| Table            | Op    | Msg_type | Msg_text |
+------------------+-------+----------+----------+
| atguigudb3.user1 | check | status   | OK       |
+------------------+-------+----------+----------+
1 row in set (0.00 sec)

該語句對於檢查的表可能會產生多行資訊。最後一行有一個狀態的Msg_type值，Msg_text通常為OK。如果得到的不是OK，通常要對其進行修復;是OK說明表已經是最新的了。表已經是最新的，意味著儲存引擎對這張表不必進行檢查。|

3.最佳化表

方式1：OPTIMIZE TABLE

MySQL中使用OPTIMIZE TABLE語句來最佳化表。但是，OPTILMIZE TABLE語句只能最佳化表中的VARCHAR 、BLOB或TEXT型別的欄位。一個表使用了這些欄位的資料型別，若已經刪除了表的一大部分資料，或者已經對含有可變長度行的表(含有VARCHAR、BLOB或TEXT列的表)進行了很多更新，則應使用OPTIMIZE TABLE來重新利用未使用的空間，並整理資料檔案的碎片。

OPTIMIZE TABLE語句對InnoDB和MylSAM型別的表都有效。該語句在執行過程中也會給表加上只讀鎖。

OPTILMIZE TABLE語句的基本語法如下:

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [ , tbl_name ] ...

LOCAL |NO_WRITE_TO_BINLOG關鍵字的意義和分析表相同，都是指定不寫入二進位制日誌。


執行完畢，Msg_text顯示

Table does not support optimize, doing recreate + analyze instead

到底最佳化了沒有呢？看官網！

OPTIMIZE語句官網解釋

在MyISAM中，是先分析這張表，然後會整理相關的MySQL datafile，之後回收未使用的空間；在InnoDB中，回收空間是簡單透過Alter table進行整理空間。在最佳化期間，MySQL會建立一個臨時表，最佳化完成之後會刪除原始表，然後會將臨時表rename成為原始表。

說明： 在多數的設定中，根本不需要執行OPTIMIZE TABLE。即使對可變長度的行進行了大量的更新，也不需要經常執行，每週一次或每月一次即可，並且只需要對特定的表執行。

方式2：使用mysqlcheck命令

mysqlcheck -o DatabaseName TableName -u root -p ******

mysqlcheck是Linux中的.sh檔案 -o 是Optimize。

3.8 小結

上述這些方法都是有利有弊的。比如：

  • 修改資料型別，節省儲存空間的同時，你要考慮到資料不能超過取值範圍；
  • 增加冗餘欄位的時候，不要忘了確保資料一致性；
  • 把大表拆分，也意味著你的查詢會增加新的連線，從而增加額外的開銷和運維的成本。

因此，你一定要結合實際的業務需求進行權衡。

4.大表最佳化

當MySQL單表記錄數過大時，資料庫的CIUD效能會明顯下降，一些常見的最佳化措施如下:

4.1 限定查詢的範圍

禁止不帶任何限制資料範圍條件的查詢語句。比如：我們當使用者在查詢訂單歷史的時候，我們可以控制在一個月的範圍內；

4.2 讀/寫分離

經典的資料庫拆分方案，主庫負責寫，從庫負責讀。

  • 一主一從模式：
  • 雙主雙從模式：

4.3 垂直拆分

當資料量級達到千萬級以上時，有時候我們需要把一個資料庫切成多份，放到不同的資料庫伺服器上，減少對單一資料庫伺服器的訪問壓力。

垂直分表，就是筆記3.1冷熱資料分離

垂直拆分的優點： 可以使得列資料變小，在查詢時減少讀取的Block數，減少I/O次數。此外，垂直分割槽可以簡化表的結構，易於維護。

垂直拆分的缺點： 主鍵會出現冗餘，需要管理冗餘列，並會引起 JOIN 操作。此外，垂直拆分會讓事務變得更加複雜。

4.4 水平拆分

  • 儘量控制單表資料量的力小，建議控制在1000萬以內。1000萬並不是MySQL資料庫的限制，過大會造成修改表結構、備份、恢復都會有很大的問題。此時可以用歷史資料歸檔（應用於日誌資料)，水平分表（應用於業務資料)等手段來控制資料量大小。
  • 這裡我們主要考慮業務資料的水平分表策略。將大的資料表按照某個屬性維度分拆成不同的小表每張小表保持相同的表結構。比如你可以按照年份來劃分，把不同年份的資料放到不同的資料表中。2017年、2018年和2019年的資料就可以分別放到三張資料表中。
  • 水平分表僅是解決了單一表資料過大的問題，但由於表的資料還是在同一臺機器上，其實對於提升MysQL併發能力沒有什麼意義，所以水平拆分最好分庫，從而達到分散式的目的。


水平拆分能夠支援非常大的資料量儲存，應用端改造也少，但分片事務難以解決，跨節點Joyin效能較差，邏輯複雜。《Java工程師修煉之道》的作者推薦儘量不要對資料進行分片，因為拆分會帶來邏輯、部署、運維的各種複雜度，一般的資料表在最佳化得當的情況下支撐千萬以下的資料量是沒有太大問題的。如果實在要分片，儘量選擇客戶端分片架構，這樣可以減少一次和中介軟體的網路I/O。

下面補充一下資料庫分片的兩種常見方案：

  • 客戶端代理： 分片邏輯在應用端，封裝在jar包中，透過修改或者封裝JDBC層來實現。 噹噹網的Sharding-JDBC 、阿里的TDDL是兩種比較常用的實現。
  • 中介軟體代理： 在應用和資料中間加了一個代理層。分片邏輯統一維護在中介軟體服務中。我們現在談的 Mycat 、360的Atlas、網易的DDB等等都是這種架構的實現。

5.其他調優策略

5.1 伺服器語句超時處理

在MySQL 8.0中可以設定伺服器語句超時的限制，單位可以達到毫秒級別。當中斷的執行語句超過設定的毫秒數後，伺服器將終止查詢影響不大的事務或連線，然後將錯誤報給客戶端。

設定伺服器語句超時的限制，可以透過設定系統變數MAX_EXECUTION_TIME來實現。預設情況下，MAX_EXECUTION_TIME的值為0，代表沒有時間限制。 例如：

SET GLOBAL MAX_EXECUTION_TIME=2000;

SET SESSION MAX_EXECUTION_TIME=2000; #指定該會話中SELECT語句的超時時間

5.2 建立全域性通用表空間

MySQL 8.0使用CREATE TABLESPACE語句來建立一個全域性通用表空間。全域性表空間可以被所有的資料庫的表共享，而且相比於獨享表空間，使用手動建立共享表空間可以節約後設資料方面的記憶體。可以在建立表的時候，指定屬於哪個表空間，也可以對已有表進行表空間修改等。

下面建立名為atguigu1的共享表空間，SQL語句如下:

mysql> CREATE TABLESPACE atguigu1 ADD DATAFILE 'atguigu1.ibd' FILE_BLOCK_SIZE=16k;

指定表空間，SQL語句如下：

CREATE TABLE test(id INT,NAME VARCHAR(10)) ENGINE=INNODB DEFAULT CHARSET utf8mb4 TABLESPACE atguigu1;

也可以透過alter語句，改變表空間

ALTER TABLE test TABLESPACE atguigu1;

如何刪除建立的共享表空間?因為是共享表空間，所以不能直接透過drop table tbname刪除，這樣操作並不能回收空間。當確定共享表空間的資料都沒用，並且依賴該表空間的表均已經刪除時，可以透過drop tablespace刪除共享表空間來釋放空間，如果依賴該共享表空間的表存在，就會刪除失敗。如下所示。

DROP TABLESPACE atguigu1;

DROP TABLE test;//先刪除表空間中的表

DROP TABLESPACE atguigu1;//再刪除表空間

5.3 MySQL 8.0新特性：隱藏索引對調優的幫助

不可見索引的特性對於效能除錯非常有用。在MySQL 8.0中，索引可以被"隱藏"和“顯示”。當一個索引被隱藏時，它不會被查詢最佳化器所使用。也就是說，管理員可以隱藏一個索引，然後觀察對資料庫的影響。如果資料庫效能有所下降，就說明這個索引是有用的，於是將其"恢復顯示"即可;如果資料庫效能看不出變化，就說明這個索引是多餘的，可以刪掉了。

需要注意的是當索引被隱藏時，它的內容仍然是和正常索引一樣實時更新的。如果一個索引需要長期被隱藏，那麼可以將其刪除，因為索引的存在會影響插入、更新和刪除的效能。

隱藏索引，就是在建立索引時，可以設定是否隱藏的關鍵字 invisible / visible(可見/不可見)。在MySQL8.0之後才可以。

資料表中的主鍵不能被設定為invisible。

只是為了記錄自己的學習歷程，且本人水平有限，不對之處，請指正。

相關文章