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

char  vs varchar

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

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

3、char 長(zhǎng)度最大為 255 個(gè)字符，varchar 長(zhǎng)度最大為 65535 個(gè)字符

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

使用255是因?yàn)樗?位數(shù)字可以計(jì)算的最大字符數(shù)。它最大限度地利用了8位計(jì)數(shù)，而不需要另一個(gè)整字節(jié)來(lái)計(jì)算255以上的字符。

開(kāi)啟慢查詢?nèi)罩緛?lái)定位查詢慢的語(yǔ)句

啟動(dòng)慢查詢?nèi)罩荆茨男┱Z(yǔ)句慢。默認(rèn)是禁用慢查詢?nèi)罩镜模?-slow_query_log[={0|1}] 不指定或者為1，將啟用日志。如果參數(shù)為0，此選項(xiàng)將禁用日志。--slow_query_log_file=file_name 來(lái)改變慢查詢?nèi)罩久Q。

先找到慢查詢的原因：

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

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

              show processlist

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

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

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

       沒(méi)用索引或者索引失效

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

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

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

索引：避免索引失效

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

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

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

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

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

優(yōu)化查詢緩存

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

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

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

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

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

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

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

適當(dāng)使用索引  

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

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

索引何時(shí)會(huì)失效？

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

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

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

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

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

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

4、避免使用select *

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

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

5.通過(guò)explain來(lái)分析SQL執(zhí)行計(jì)劃，看是否用到了索引

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

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

All:   全表掃描

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

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

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

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

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

NULL：類似于覆蓋查詢

Id:當(dāng)前查詢中，每個(gè)Select語(yǔ)句的編號(hào)

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

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

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

Select_type:

簡(jiǎn)單查詢?yōu)閟imple

復(fù)雜查詢：

Subquery:簡(jiǎn)單子查詢

Derived:用于from中的子查詢

Union:用于union第一個(gè)之后的select語(yǔ)句

 

possible_keys:  查詢可能會(huì)使用的索引

Key:  使用到的索引

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

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

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

Extra:額外信息

Using index:會(huì)使用覆蓋索引，以避免訪問(wèn)表

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

Using temporary:對(duì)結(jié)果排序時(shí)會(huì)使用臨時(shí)表

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

例如：

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)合前兩個(gè)表   匿名臨時(shí)表
  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ù)表中，可以有助于緩存

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

非規(guī)范化的方式

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

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

參考：

• system-design-primer/README-zh-Hans.md at master · donnemartin/system-design-primer · GitHub
• MySQL查詢緩存優(yōu)化_Tomorrow Is Forever的技術(shù)博客

總結(jié) 

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

最新評(píng)論