欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

數(shù)據(jù)庫SQL調(diào)優(yōu)的幾種方式匯總

 更新時間：2022年10月26日 11:48:33   作者：帶你去吃小豆花  
在項目中,SQL的調(diào)優(yōu)對項目的性能來講至關(guān)重要,所有掌握常見的SQL調(diào)優(yōu)方式是必不可少的,下面這篇文章主要給大家介紹了關(guān)于數(shù)據(jù)庫SQL調(diào)優(yōu)的幾種方式,需要的朋友可以參考下

最近在復(fù)習(xí)SQL調(diào)優(yōu)，總結(jié)了下主要有以下幾種方式：

char  vs varchar

1、如果文本字段始終是固定長度的（例如，US 郵編，其始終具有“XXXXX-XXXX”形式的規(guī)范表示），那么推薦使用char。varchar 類型的長度是可變的，而 char 類型是一個定長的字段，以 char(10) 為例，不管真實(shí)的存儲內(nèi)容多大或者是占了多少空間，都會消耗掉 10 個字符的空間，通俗來講，當(dāng)定義為 char(10) 時，即使插入的內(nèi)容是 'abc' 3 個字符，它依然會占用 10 個字節(jié)，其中包含了 7 個空字節(jié)。

2、CHAR 在快速、隨機(jī)訪問時效率很高。使用 VARCHAR，如果你想讀取下一個字符串，不得不先讀取到當(dāng)前字符串的末尾，查找效率比較低。

3、char 長度最大為 255 個字符，varchar 長度最大為 65535 個字符

為什么varchar(255)而不是varchar(258),varchar(257)呢？

使用255是因為它是8位數(shù)字可以計算的最大字符數(shù)。它最大限度地利用了8位計數(shù)，而不需要另一個整字節(jié)來計算255以上的字符。

開啟慢查詢?nèi)罩緛矶ㄎ徊樵兟恼Z句

啟動慢查詢?nèi)罩?，看哪些語句慢。默認(rèn)是禁用慢查詢?nèi)罩镜模?-slow_query_log[={0|1}] 不指定或者為1，將啟用日志。如果參數(shù)為0，此選項將禁用日志。--slow_query_log_file=file_name 來改變慢查詢?nèi)罩久Q。

先找到慢查詢的原因：

1、打開數(shù)據(jù)庫慢查詢?nèi)罩?，定義超時時間，比如超過2S就是慢查詢

2、定位執(zhí)行效率低的慢查詢

              show processlist

3、也可以通過explain來分析執(zhí)行計劃，explain得到的信息要主要關(guān)注：type字段，

       possible_keys字段，key字段，key_len字段，rows，extra字段等

一般情況下慢查詢的原因有以下這些：

       沒用索引或者索引失效

       用了索引但是走了全表掃描

慢查詢?nèi)绾蝺?yōu)化

索引+sql語句+架構(gòu)優(yōu)化+數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化

索引：避免索引失效

sql語句：優(yōu)化insert語句：多條插入寫一條，數(shù)據(jù)有序的插入；分頁優(yōu)化：通過子查詢優(yōu)化，比如查詢2w起后面10條數(shù)據(jù)，先通過子查詢拿到20000的id,然后通過主鍵索引，通過B+樹定位到拿到的id對應(yīng)的行數(shù)據(jù)，然后再向后取10條數(shù)據(jù)；

架構(gòu)優(yōu)化：數(shù)據(jù)庫讀寫分離，主庫寫，從庫讀；

數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化：將字段比較多的表分解成多個表，將字段使用頻率高的和字段使用頻率低的分開，對于一些要進(jìn)行聯(lián)合查詢的表，可以考慮建立中間表

合理使用關(guān)鍵字

比如MySQL還有其他更危險的關(guān)鍵字，應(yīng)該謹(jǐn)慎使用。其中包括INSERT DELAYED，它告訴MySQL立即插入數(shù)據(jù)并不重要（例如，在日志記錄情況下）。問題是，在高負(fù)載情況下，插入可能會無限期延遲，導(dǎo)致插入隊列停滯。

優(yōu)化查詢緩存

查詢緩存相關(guān)的服務(wù)器變量：

query_cache_min_res_unit：查詢緩存中內(nèi)存塊的最小分配單位，默認(rèn)4k，較小值會減少浪費(fèi)，但會導(dǎo)致更頻繁的內(nèi)存分配操作，較大值會帶來浪費(fèi)，會導(dǎo)致碎片過多，內(nèi)存不足
query_cache_limit：單個查詢結(jié)果能緩存的最大值，單位字節(jié)，默認(rèn)為1M，對于查詢結(jié)果過大而無法緩存的語句，建議使用SQL_NO_CACHE
query_cache_size：查詢緩存總共可用的內(nèi)存空間；單位字節(jié)，必須是1024的整數(shù)倍，最小值40KB，低于此值有警報
query_cache_wlock_invalidate：如果某表被其它的會話鎖定，是否仍然可以從查詢緩存中返回結(jié)果，默認(rèn)值為OFF，表示可以在表被其它會話鎖定的場景中繼續(xù)從緩存返回數(shù)據(jù)；ON則表示不允許
query_cache_type：是否開啟緩存功能，取值為ON, OFF, DEMAND

查詢緩存相關(guān)的狀態(tài)變量：
show gloable status like 'Qcache%' ;

innodb_buffer_pool_size：這是任何使用innodb的安裝都要考慮的#1設(shè)置。緩沖池是緩存數(shù)據(jù)和索引的地方：使其盡可能大將確保大多數(shù)讀取操作使用內(nèi)存而不是磁盤。典型值為5-6GB（8GB RAM）、20-25GB（32GB RAM）、100-120GB（128GB RAM）。
innodb_log_file_size:這是重做日志的大小。重做日志用于確保寫入速度和持久性，以及在崩潰恢復(fù)期間。在MySQL 5.1之前，很難進(jìn)行調(diào)整，因為您既需要大的重做日志來獲得良好的性能，也需要小的重做日志來實(shí)現(xiàn)快速的崩潰恢復(fù)。幸運(yùn)的是，自MySQL 5.5以來，崩潰恢復(fù)性能有了很大提高，因此您現(xiàn)在可以擁有良好的寫性能和快速的崩潰恢復(fù)。在MySQL 5.5之前，重做日志的總大小限制為4GB（默認(rèn)為有2個日志文件）。這在MySQL 5.6中得到了提升。

max_connections:如果經(jīng)常遇到“連接數(shù)過多”錯誤，則最大連接數(shù)太低。由于應(yīng)用程序無法正確關(guān)閉與數(shù)據(jù)庫的連接，因此經(jīng)常需要比默認(rèn)的151個連接多得多的連接。

查詢緩存的優(yōu)化路線

 在早期版本mysql均支持緩存，但是隨著redis等內(nèi)存型高性能的緩存技術(shù)興起，mysql已經(jīng)拋棄自己的緩存功能，mysql8.0以后不再支持緩存功能。

適當(dāng)使用索引  

每個索引都需要與表中的行數(shù)成比例的空間，因此太多的索引最終會占用更多內(nèi)存。由于每次寫入都需要更新相應(yīng)的索引，因此寫入操作的性能也會受到影響。通過分析代碼，可以發(fā)現(xiàn)一個平衡點(diǎn)。這因系統(tǒng)和實(shí)施而異。

  • 查詢（SELECT、GROUP BY、ORDER BY、JOIN）的列如果用了索引會更快
  • 索引通常表示為自平衡的 B 樹，可以保持?jǐn)?shù)據(jù)有序，并允許在對數(shù)時間內(nèi)進(jìn)行搜索，順序訪問，插入，刪除操作
  • 設(shè)置索引，會將數(shù)據(jù)存在內(nèi)存中，占用了更多內(nèi)存空間
  • 寫入操作會變慢，因為索引需要被更新
  • 加載大量數(shù)據(jù)時，禁用索引再加載數(shù)據(jù)，然后重建索引，這樣也許會更快

索引何時會失效？

索引 (Index) 是幫助 MySQL 高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。我們可以簡單理解為：快速查找排好序的一種數(shù)據(jù)結(jié)構(gòu)。

當(dāng)索引不起作用時，會引起全表查詢，索引就會失效，引起慢查詢，有以下幾種情況：

  • 模糊查詢，比如以%開頭的like查詢。
  • 在索引列上操作or, not in , !=,<>,等操作
  • 如果查詢條件有or,并且or的前后條件中有一個列沒有索引，則涉及的索引都不會用到

1、避免索引失效，減少%like這種索引失效語句

2、合理創(chuàng)建索引

3、分頁查詢優(yōu)化，可以通過子查詢，關(guān)聯(lián)查詢優(yōu)化，比如要查詢從10000行開始的10行數(shù)據(jù)，看似只返回了10條數(shù)據(jù)，但是數(shù)據(jù)庫引擎需要查詢10010數(shù)據(jù)，然后將前面的10000條數(shù)據(jù)丟棄，性能可想而知，針對這種情況，我們可以先定位到上次分頁的id,然后對id做條件索引查詢；或者將原有的sql拆成2步，首先查詢出一頁數(shù)據(jù)中的最小id,然后通過索引樹，定位到最小id索引樹節(jié)點(diǎn)位置，通過偏移量來讀取后面的10條數(shù)據(jù)

4、避免使用select *

select *的查詢過程：先在字段的二級索引B+樹上，查出對應(yīng)的主鍵id列表

然后進(jìn)行回表操作，在主鍵索引中 查詢id對應(yīng)的行數(shù)據(jù)

5.通過explain來分析SQL執(zhí)行計劃，看是否用到了索引

explain得到的字段key（使用到的索引）,rows（MYSQL估計為了查找目標(biāo)行而需要讀取的行數(shù)：）,possible_keys（查詢可能會使用的索引）等主要關(guān)注type

Type:以怎樣的方式查找表中的行的方式（性能逐漸變好）

All:   全表掃描

Index:  根據(jù)索引的次序進(jìn)行全表掃描，若在extra出現(xiàn)using index表示使用覆蓋索引，而非全表掃描

Range:  根據(jù)索引實(shí)現(xiàn)的范圍掃描

Ref:  根據(jù)索引返回表中匹配某單個值的所有行

Eq_ref:  僅返回一個行，但需要和某個參考值作比較

Const,system:  根據(jù)具有唯一性的索引（比如主鍵）查找時，返回的是一行

NULL：類似于覆蓋查詢

Id:當(dāng)前查詢中，每個Select語句的編號

復(fù)雜類型的查詢?nèi)N：

  • 簡單子查詢：
  • 用于from中的子查詢
  • 聯(lián)合查詢，union

注意：union查詢的分析結(jié)果中會出現(xiàn)一個額外的匿名臨時表

Select_type:

簡單查詢?yōu)閟imple

復(fù)雜查詢：

Subquery:簡單子查詢

Derived:用于from中的子查詢

Union:用于union第一個之后的select語句

 

possible_keys:  查詢可能會使用的索引

Key:  使用到的索引

Key_len:  索引中使用的字節(jié)數(shù)，比如索引有70個字節(jié)數(shù)，只是用了20個

ref:  在利用key所表示的索引完成查詢時，所用的列或某常量值

rows:  MYSQL估計為了查找目標(biāo)行而需要讀取的行數(shù)：

Extra:額外信息

Using index:會使用覆蓋索引，以避免訪問表

Using where:服務(wù)器將在存儲引擎檢索后，再進(jìn)行一次過濾

Using temporary:對結(jié)果排序時會使用臨時表

Using filesort:對結(jié)果使用一個外部索引排序

例如：

MariaDB [testdb]> explain select sname,age from stu union select tname,age from teacher\G;

*************************** 1. row ***************************

           id: 1
  select_type: PRIMARY
        table: stu
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
        Extra:

*************************** 2. row ***************************

           id: 2
  select_type: UNION
        table: teacher
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra:

*************************** 3. row ***************************

           id: NULL   聯(lián)合前兩個表   匿名臨時表
  select_type: UNION RESULT
        table: <union1,2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:
3 rows in set (0.00 sec)
ERROR: No query specified

分割數(shù)據(jù)表

將熱點(diǎn)數(shù)據(jù)拆分到單獨(dú)的數(shù)據(jù)表中，可以有助于緩存

例如，在博客上，可能會在許多地方顯示條目標(biāo)題（例如，最近發(fā)布的文章列表）. 經(jīng)常訪問的數(shù)據(jù)保存在一個表中，而不經(jīng)常訪問的數(shù)據(jù)保存在另一個表中。由于數(shù)據(jù)現(xiàn)在已分區(qū)，因此不經(jīng)常訪問的數(shù)據(jù)占用的內(nèi)存更少。

非規(guī)范化的方式

非規(guī)范化: 非規(guī)范化是一種用于先前規(guī)范化數(shù)據(jù)庫以提高性能的策略。在計算中，非規(guī)范化是指通過添加數(shù)據(jù)的冗余副本或?qū)?shù)據(jù)進(jìn)行分組，以犧牲某些寫入性能為代價，嘗試提高數(shù)據(jù)庫的讀取性能的過程。

這通常是由需要執(zhí)行大量讀取操作的關(guān)系數(shù)據(jù)庫軟件的性能或可擴(kuò)展性引起的。一般情況下，只需要在性能需要的地方進(jìn)行非規(guī)范化。

參考：

總結(jié) 

到此這篇關(guān)于數(shù)據(jù)庫SQL調(diào)優(yōu)的幾種方式的文章就介紹到這了,更多相關(guān)SQL調(diào)優(yōu)方式內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

最新評論