MySQL SQL語句優化

文章目錄

• 如何定位及優化SQL語句的性能問題？建立的索引有沒有被使用到?或者說怎麼才可以知道這條語句運作很慢的原因？
• 慢查詢日志？
• 主鍵使用自增ID還是UUID？
• 如何優化關聯查詢和子查詢？
• 如何優化where語句？
• 大表資料查詢，如何優化語句？

如何定位及優化SQL語句的性能問題？建立的索引有沒有被使用到?或者說怎麼才可以知道這條語句運作很慢的原因？

使用Explain關鍵字，mysql的explain 指令可以用來分析select 語句的運作效果。基本文法：

explain select ...
           

• id:

  執行計劃包含的資訊 id 有一組數字組成。表示一個查詢中各個子查詢的執行順序
  • id相同執行順序由上至下。
  • id不同，id值越大優先級越高，越先被執行。
  • id為null時表示一個結果集，不需要使用它查詢，常出現在包含union等查詢語句中。

• select_type:

  每個子查詢的查詢類型，一些常見的查詢類型

  id	select_type	description
  1	SIMPLE	不包含任何子查詢或union等查詢
  2	PRIMARY	包含子查詢最外層查詢就顯示為 PRIMARY
  3	SUBQUERY	在select或 where字句中包含的查詢
  4	DERIVED	from字句中包含的查詢
  5	UNION	出現在union後的查詢語句中
  6	UNION RESULT	從UNION中擷取結果集，例如上文的第三個例子

• table:

  查詢的資料表

• type:

  (非常重要，可以看到有沒有走索引) 通路類型:
  • ALL 掃描全表資料
  • index 周遊索引
  • range 索引範圍查找
  • index_subquery 在子查詢中使用 ref
  • unique_subquery 在子查詢中使用 eq_ref
  • ref_or_null 對Null進行索引的優化的 ref
  • fulltext 使用全文索引
  • ref 使用非唯一索引查找資料
  • eq_ref 在join查詢中使用PRIMARY KEYorUNIQUE NOT NULL索引關聯。

• possible_keys:

  可能使用的索引，注意不一定會使用。查詢涉及到的字段上若存在索引，則該索引将被列出來。當該列為 NULL時就要考慮目前的SQL是否需要優化了。

• key:

  顯示MySQL在查詢中實際使用的索引，若沒有使用索引，顯示為NULL。

• TIPS:

  查詢中若使用了覆寫索引(覆寫索引：索引的資料覆寫了需要查詢的所有資料)，則該索引僅出現在key清單中

• key_length:

  索引長度

• ref:

  表示上述表的連接配接比對條件，即哪些列或常量被用于查找索引列上的值

• rows:

  傳回估算的結果集數目，并不是一個準确的值。

• extra:

  的資訊非常豐富，常見的有：
  • Using index 使用覆寫索引
  • Using where 使用了用where子句來過濾結果集
  • Using filesort 使用檔案排序，使用非索引列進行排序時出現，非常消耗性能，盡量優化。
  • Using temporary 使用了臨時表 sql優化的目标可以參考阿裡開發手冊

慢查詢日志？

https://www.cnblogs.com/kerrycode/p/5593204.html

MySQL的慢查詢日志是MySQL提供的一種日志記錄，它用來記錄在MySQL中響應時間超過閥值的語句，具體指運作時間超過long_query_time值的SQL，則會被記錄到慢查詢日志中。long_query_time的預設值為10，意思是運作10S以上的語句。預設情況下，Mysql資料庫并不啟動慢查詢日志，需要我們手動來設定這個參數，當然，如果不是調優需要的話，一般不建議啟動該參數，因為開啟慢查詢日志會或多或少帶來一定的性能影響。慢查詢日志支援将日志記錄寫入檔案，也支援将日志記錄寫入資料庫表。

主鍵使用自增ID還是UUID？

自增長id：故名思意，id随着資料增長而增長。

UUID：含義是通用唯一識别碼 (Universally Unique Identifier)，是指在一台機器上生成的數字，它保證對在同一時空中的所有機器都是唯一的

關于UUID的兩篇文章：

唯一ID生成算法剖析，看看這篇就夠了

UUID是如何保證唯一性的？

使用自增長ID做主鍵的優點：

• 很小的資料存儲空間
• 性能最好,innodb的索引特性導緻了自增id做主鍵是效率最好的
• 容易記憶

使用自增長做主鍵的缺點：

• 如果存在大量的資料，可能會超出自增長的取值範圍
• 很難處理分布式存儲的資料表，尤其是需要合并表的情況下
• 安全性低，因為自增id是有規律的，容易被非法擷取資料

使用UUID做主鍵的優點：

• 它是獨一無二的，出現重複的幾率極低
• 适合大量資料中的插入和更新操作，尤其是在高并發和分布式環境下
• 跨伺服器資料合并非常友善
• 安全性較高

使用UUID做主鍵的缺點：

• 存儲空間大（16 byte），是以它将會占用更多的磁盤空間
• 會降低性能

如何選擇ID政策？

如何選擇政策取決于具體項目的應用場景，根據項目資料量級和是否分布式部署可以有以下選擇：

• 項目是單機版的，并且資料量比較大（百萬級）時，用自增長的，此時最好能考慮下安全性，做些安全措施。
• 項目是單機版的，并且資料量沒那麼大，對速度和存儲要求不高時，用UUID。
• 項目是分布式的，那麼首選UUID，分布式一般對速度和存儲要求不高。
• 項目是分布式的，并且資料量達到千萬級别可更高時，對速度和存儲有要求時，可以用自增長。

如何優化關聯查詢和子查詢？

• 一個複雜查詢還是多個簡單查詢
• 将一個大的查詢分為多個小的相同的查詢
• 一次性删除1000萬的資料要比一次删除1萬，暫停一會的方案更加損耗伺服器開銷。
• 分解關聯查詢，讓緩存的效率更高。
• 執行單個查詢可以減少鎖的競争。
• 在應用層做關聯更容易對資料庫進行拆分。
• 較少備援記錄的查詢。

優化關聯查詢

• 确定ON或者USING子句中是否有索引。
• 確定GROUP BY和ORDER BY隻有一個表中的列，這樣MySQL才有可能使用索引。

優化子查詢

• 用關聯查詢替代
• 優化GROUP BY和DISTINCT
• 這兩種查詢據可以使用索引來優化，是最有效的優化方法
• 關聯查詢中，使用辨別列分組的效率更高
• 如果不需要ORDER BY，進行GROUP BY時加ORDER BY NULL，MySQL不會再進行檔案排序。
• WITH ROLLUP超級聚合，可以挪到應用程式處理

如何優化where語句？

對于此類考題，先說明如何定位低效SQL語句，然後根據SQL語句可能低效的原因做排查，先從索引着手，如果索引沒有問題，考慮以上幾個方面，資料通路的問題，長難查詢句的問題還是一些特定類型優化的問題，逐一回答。

• 1.對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。
• 2.應盡量避免在 where 子句中對字段進行 null 值判斷，否則将導緻引擎放棄使用索引而進行全表掃描，如：

  select id from t where num is null
  -- 可以在num上設定預設值0，確定表中num列沒有null值，然後這樣查詢：
  select id from t where num=
             

• 3.應盡量避免在 where 子句中使用!=或<>操作符，否則引擎将放棄使用索引而進行全表掃描。
• 4.應盡量避免在 where 子句中使用or 來連接配接條件，否則将導緻引擎放棄使用索引而進行全表掃描，如：

  select id from t where num=10 or num=20
  -- 可以這樣查詢：
  select id from t where num=10 union all select id from t where num=20
             

• 5.in 和 not in 也要慎用，否則會導緻全表掃描，如：

  select id from t where num in(1,2,3) 
  -- 對于連續的數值，能用 between 就不要用 in 了：
  select id from t where num between 1 and 3
             

• 6.下面的查詢也将導緻全表掃描：select id from t where name like ‘%李%’若要提高效率，可以考慮全文檢索。
• 7.如果在 where 子句中使用參數，也會導緻全表掃描。因為SQL隻有在運作時才會解析局部變量，但優化程式不能将通路計劃的選擇推遲到運作時；它必須在編譯時進行選擇。然 而，如果在編譯時建立通路計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句将進行全表掃描：

  select id from t where [email protected]
  -- 可以改為強制查詢使用索引：
  select id from t with(index(索引名)) where [email protected]
             

• 8.應盡量避免在 where 子句中對字段進行表達式操作，這将導緻引擎放棄使用索引而進行全表掃描。如：

  select id from t where num/2=100
  -- 應改為:
  select id from t where num=100*2
             

• 9.應盡量避免在where子句中對字段進行函數操作，這将導緻引擎放棄使用索引而進行全表掃描。如：

  select id from t where substring(name,1,3)=’abc’
  -- name以abc開頭的id應改為:
  select id from t where name like ‘abc%’
             

• 10.不要在 where 子句中的“=”左邊進行函數、算術運算或其他表達式運算，否則系統将可能無法正确使用索引。

大表資料查詢，如何優化語句？

• 優化shema、sql語句+索引；
• 第二加緩存，memcached, redis；
• 主從複制，讀寫分離；
• 垂直拆分，根據你子產品的耦合度，将一個大的系統分為多個小的系統，也就是分布式系統；
• 水準切分，針對資料量大的表，這一步最麻煩，最能考驗技術水準，要選擇一個合理的sharding key, 為了有好的查詢效率，表結構也要改動，做一定的備援，應用也要改，sql中盡量帶sharding key，将資料定位到限定的表上去查，而不是掃描全部的表；