本文目錄一覽：
MySQL常用優化方案
語句執行後，會顯示三個欄位： Query_ID（執行ID） | Duration（持續時間）| Query（查詢語句） ；
拿到後Query_ID後，可執行 show profile for query Query_ID ，查看詳細的準備時間，執行時間、執行結束（ preparing、executing、end ）等。
顯示用戶正在運行的線程，需要注意的是，除了 root 用戶能看到所有正在運行的線程外，其他用戶都只能看到自己正在運行的線程，看不到其它用戶正在運行的線程。除非單獨個這個用戶賦予了PROCESS 許可權。
顯示欄位包含： User| Host| db | Command | Time| State| Info 等。
解析語句，查詢是否命中索引，及，命中何種索引，用以判斷是否符合我們的預期。
返回欄位包含： select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra 等。
select_type 常見類型：
(1) SIMPLE(簡單SELECT，不使用UNION或子查詢等)
(2) PRIMARY(子查詢中最外層查詢，查詢中若包含任何複雜的子部分，最外層的select被標記為PRIMARY)
(3) UNION(UNION中的第二個或後面的SELECT語句)
(4) SUBQUERY(子查詢中的第一個SELECT，結果不依賴於外部查詢)
table 常見類型：
顯示這一行的數據是關於哪張表的.
有時不是真實的表名字,看到的是derivedx(x是個數字,我的理解是第幾步執行的結果)
type 常見類型：
對錶訪問方式，表示MySQL在表中找到所需行的方式，又稱「訪問類型」。
常用的類型有： ALL、index、range、 ref、eq_ref、const、system、NULL （從左到右，性能從差到好）
possible_keys
指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的欄位上若存在索引，則該索引將被列出，但不一定被查詢使用（該查詢可以利用的索引，如果沒有任何索引顯示 null）
該列完全獨立於EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用。
如果該列是NULL，則沒有相關的索引。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能。如果是這樣，創造一個適當的索引並且再次用EXPLAIN檢查查詢
key
key列顯示MySQL實際決定使用的鍵（索引），必然包含在possible_keys中
如果沒有選擇索引，鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
key_len
表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度，非實際長度，為最大可能長度。
註：不損失精確性的情況下，長度越短越好。
ref
列與索引的比較，表示上述表的連接匹配條件，即哪些列或常量被用於查找索引列上的值。
rows
估算出結果集行數，表示MySQL根據表統計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數；
extra
該列包含MySQL解決查詢的詳細信息,有以下幾種情況：
(1).Distinct
一旦MYSQL找到了與行相聯合匹配的行，就不再搜索了
(2).Not exists
MYSQL優化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標準的行，就不再搜索了
(3).Range checked for each
Record（index map:#）
沒有找到理想的索引，因此對於從前面表中來的每一個行組合，MYSQL檢查使用哪個索引，並用它來從表中返回行。這是使用索引的最慢的連接之一
(4).Using filesort
看到這個的時候，查詢就需要優化了。MYSQL需要進行額外的步驟來發現如何對返回的行排序。它根據連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行；
(5).Using temporary
看到這個的時候，查詢需要優化了。這裡，MYSQL需要創建一個臨時表來存儲結果，這通常發生在對不同的列集進行ORDER BY上，而不是GROUP BY上；
(6).Using index
列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發生在對錶的全部的請求列都是同一個索引的部分的時候。
(7).Using where
使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，並且連接類型ALL或index，這就會發生，或者是查詢有問題。
mysql優化的幾種方法
1、選取最適用的欄位屬性
MySQL 可以很好的支持大數據量的存取，但是一般說來，資料庫中的表越小，在它上面執行的查詢也就會越快。因此，在創建表的時候，為了獲得更好的性能，我們可以將表中欄位的寬度設得儘可能小。例如，在定義郵政編碼這個欄位時，如果將其設置為CHAR(255),顯然給資料庫增加了不必要的空間，甚至使用VARCHAR這種類型也是多餘的，因為CHAR(6)就可以很好的完成任務了。同樣的，如果可以的話，我們應該使用MEDIUMINT而不是BIGIN來定義整型欄位。
另外一個提高效率的方法是在可能的情況下，應該盡量把欄位設置為NOT NULL，這樣在將來執行查詢的時候，資料庫不用去比較NULL值。
對於某些文本欄位，例如「省份」或者「性別」，我們可以將它們定義為ENUM類型。因為在MySQL中，ENUM類型被當作數值型數據來處理，而數值型數據被處理起來的速度要比文本類型快得多。這樣，我們又可以提高資料庫的性能。
2、使用連接（JOIN）來代替子查詢(Sub-Queries)
MySQL 從4.1開始支持SQL的子查詢。這個技術可以使用SELECT語句來創建一個單列的查詢結果，然後把這個結果作為過濾條件用在另一個查詢中。例如，我們要將客戶基本信息表中沒有任何訂單的客戶刪除掉，就可以利用子查詢先從銷售信息表中將所有發出訂單的客戶ID取出來，然後將結果傳遞給主查詢，如下所示：
DELETE FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表鎖死，並且寫起來也很容易。但是，有些情況下，子查詢可以被更有效率的連接（JOIN）.. 替代。例如，假設我們要將所有沒有訂單記錄的用戶取出來，可以用下面這個查詢完成：
SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
如果使用連接（JOIN）.. 來完成這個查詢工作，速度將會快很多。尤其是當salesinfo表中對CustomerID建有索引的話，性能將會更好，查詢如下：
SELECT * FROM customerinfo
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.
CustomerID
WHERE salesinfo.CustomerID IS NULL
連接（JOIN）.. 之所以更有效率一些，是因為 MySQL不需要在內存中創建臨時表來完成這個邏輯上的需要兩個步驟的查詢工作。
3、使用聯合(UNION)來代替手動創建的臨時表
MySQL 從 4.0 的版本開始支持 UNION 查詢，它可以把需要使用臨時表的兩條或更多的 SELECT 查詢合併的一個查詢中。在客戶端的查詢會話結束的時候，臨時表會被自動刪除，從而保證資料庫整齊、高效。使用 UNION 來創建查詢的時候，我們只需要用 UNION作為關鍵字把多個 SELECT 語句連接起來就可以了，要注意的是所有 SELECT 語句中的欄位數目要想同。下面的例子就演示了一個使用 UNION的查詢。
SELECT Name, Phone FROM client
UNION
SELECT Name, BirthDate FROM author
UNION
SELECT Name, Supplier FROM product
4、事務
儘管我們可以使用子查詢（Sub-Queries）、連接（JOIN）和聯合（UNION）來創建各種各樣的查詢，但不是所有的資料庫操作都可以只用一條或少數幾條SQL語句就可以完成的。更多的時候是需要用到一系列的語句來完成某種工作。但是在這種情況下，當這個語句塊中的某一條語句運行出錯的時候，整個語句塊的操作就會變得不確定起來。設想一下，要把某個數據同時插入兩個相關聯的表中，可能會出現這樣的情況：第一個表中成功更新後，資料庫突然出現意外狀況，造成第二個表中的操作沒有完成，這樣，就會造成數據的不完整，甚至會破壞資料庫中的數據。要避免這種情況，就應該使用事務，它的作用是：要麼語句塊中每條語句都操作成功，要麼都失敗。換句話說，就是可以保持資料庫中數據的一致性和完整性。事物以BEGIN 關鍵字開始，COMMIT關鍵字結束。在這之間的一條SQL操作失敗，那麼，ROLLBACK命令就可以把資料庫恢復到BEGIN開始之前的狀態。
BEGIN;
INSERT INTO salesinfo SET CustomerID=14;
UPDATE inventory SET Quantity=11
WHERE item=’book’;
COMMIT;
事務的另一個重要作用是當多個用戶同時使用相同的數據源時，它可以利用鎖定資料庫的方法來為用戶提供一種安全的訪問方式，這樣可以保證用戶的操作不被其它的用戶所干擾。
5、鎖定表
儘管事務是維護資料庫完整性的一個非常好的方法，但卻因為它的獨佔性，有時會影響資料庫的性能，尤其是在很大的應用系統中。由於在事務執行的過程中，資料庫將會被鎖定，因此其它的用戶請求只能暫時等待直到該事務結束。如果一個資料庫系統只有少數幾個用戶
來使用，事務造成的影響不會成為一個太大的問題；但假設有成千上萬的用戶同時訪問一個資料庫系統，例如訪問一個電子商務網站，就會產生比較嚴重的響應延遲。
其實，有些情況下我們可以通過鎖定表的方法來獲得更好的性能。下面的例子就用鎖定表的方法來完成前面一個例子中事務的功能。
LOCK TABLE inventory WRITE
SELECT Quantity FROM inventory
WHEREItem=’book’;
…
UPDATE inventory SET Quantity=11
WHEREItem=’book’;
UNLOCK TABLES
這裡，我們用一個 SELECT 語句取出初始數據，通過一些計算，用 UPDATE 語句將新值更新到表中。包含有 WRITE 關鍵字的 LOCK TABLE 語句可以保證在 UNLOCK TABLES 命令被執行之前，不會有其它的訪問來對 inventory 進行插入、更新或者刪除的操作。
6、使用外鍵
鎖定表的方法可以維護數據的完整性，但是它卻不能保證數據的關聯性。這個時候我們就可以使用外鍵。例如，外鍵可以保證每一條銷售記錄都指向某一個存在的客戶。在這裡，外鍵可以把customerinfo 表中的CustomerID映射到salesinfo表中CustomerID，任何一條沒有合法CustomerID的記錄都不會被更新或插入到 salesinfo中。
CREATE TABLE customerinfo
(
CustomerID INT NOT NULL ,
PRIMARY KEY ( CustomerID )
) TYPE = INNODB;
CREATE TABLE salesinfo
(
SalesID INT NOT NULL,
CustomerID INT NOT NULL,
PRIMARY KEY(CustomerID, SalesID),
FOREIGN KEY (CustomerID) REFERENCES customerinfo
(CustomerID) ON DELETECASCADE
) TYPE = INNODB;
注意例子中的參數「ON DELETE CASCADE」。該參數保證當 customerinfo 表中的一條客戶記錄被刪除的時候，salesinfo 表中所有與該客戶相關的記錄也會被自動刪除。如果要在 MySQL 中使用外鍵，一定要記住在創建表的時候將表的類型定義為事務安全表 InnoDB類型。該類型不是 MySQL 表的默認類型。定義的方法是在 CREATE TABLE 語句中加上 TYPE=INNODB。如例中所示。
7、使用索引
索引是提高資料庫性能的常用方法，它可以令資料庫伺服器以比沒有索引快得多的速度檢索特定的行，尤其是在查詢語句當中包含有MAX(), MIN()和ORDERBY這些命令的時候，性能提高更為明顯。那該對哪些欄位建立索引呢？一般說來，索引應建立在那些將用於JOIN, WHERE判斷和ORDER BY排序的欄位上。盡量不要對資料庫中某個含有大量重複的值的欄位建立索引。對於一個ENUM類型的欄位來說，出現大量重複值是很有可能的情況，例如 customerinfo中的「province」.. 欄位，在這樣的欄位上建立索引將不會有什麼幫助；相反，還有可能降低資料庫的性能。我們在創建表的時候可以同時創建合適的索引，也可以使用ALTER TABLE或CREATE INDEX在以後創建索引。此外，MySQL
從版本3.23.23開始支持全文索引和搜索。全文索引在 MySQL 中是一個FULLTEXT類型索引，但僅能用於MyISAM 類型的表。對於一個大的資料庫，將數據裝載到一個沒有FULLTEXT索引的表中，然後再使用ALTER TABLE或CREATE INDEX創建索引，將是非常快的。但如果將數據裝載到一個已經有FULLTEXT索引的表中，執行過程將會非常慢。
8、優化的查詢語句
絕大多數情況下，使用索引可以提高查詢的速度，但如果SQL語句使用不恰當的話，索引將無法發揮它應有的作用。下面是應該注意的幾個方面。首先，最好是在相同類型的欄位間進行比較的操作。在MySQL 3.23版之前，這甚至是一個必須的條件。例如不能將一個建有索引的INT欄位和BIGINT欄位進行比較；但是作為特殊的情況，在CHAR類型的欄位和 VARCHAR類型欄位的欄位大小相同的時候，可以將它們進行比較。其次，在建有索引的欄位上盡量不要使用函數進行操作。
例如，在一個DATE類型的欄位上使用YEAE()函數時，將會使索引不能發揮應有的作用。所以，下面的兩個查詢雖然返回的結果一樣，但後者要比前者快得多。
SELECT * FROM order WHERE YEAR(OrderDate)2001;
SELECT * FROM order WHERE OrderDate”2001-01-01″;
同樣的情形也會發生在對數值型欄位進行計算的時候：
SELECT * FROM inventory WHERE Amount/724;
SELECT * FROM inventory WHERE Amount24*7;
上面的兩個查詢也是返回相同的結果，但後面的查詢將比前面的一個快很多。第三，在搜索字元型欄位時，我們有時會使用 LIKE 關鍵字和通配符，這種做法雖然簡單，但卻也是以犧牲系統性能為代價的。例如下面的查詢將會比較表中的每一條記錄。
SELECT * FROM books
WHERE name like “MySQL%”
但是如果換用下面的查詢，返回的結果一樣，但速度就要快上很多：
SELECT * FROM books
WHERE name=”MySQL”and name”MySQM”
最後，應該注意避免在查詢中讓MySQL進行自動類型轉換，因為轉換過程也會使索引變得不起作用。
技術分享 | 淺談 MySQL 的臨時表和臨時文件
本文內容來源於對客戶的三個問題的思考：
以下測試都是在 MySQL 8.0.21 版本中完成，不同版本可能存在差異，可自行測試；
臨時表和臨時文件都是用於臨時存放數據集的地方；
一般情況下，需要臨時存放在臨時表或臨時文件中的數據集應該符合以下特點：
從臨時表|臨時文件產生的主觀性來看，分為2類：
用戶創建臨時表：
– 用戶創建臨時表(只有創建臨時表的會話才能查看其創建的臨時表的內容)
注意：
可以創建和普通表同名臨時表，其他會話可以看到普通表(因為看不到其他會話創建的臨時表)；
創建臨時表的會話會優先看到臨時表；
– 同名表的創建的語句如下
當存在同名的臨時表時，會話都是優先處理臨時表(而不是普通表)，包括：select、update、delete、drop、alter 等操作；
查看用戶創建的臨時表：
任何 session 都可以執行下面的語句；
查看用戶創建的當前 active 的臨時表(不提供 optimizer 使用的內部 InnoDB 臨時表信息)
注意
用戶創建的臨時表，表名為t1，
但是通過 INNODB_TEMP_TABLE_INFO 查看到的臨時表的 NAME 是#sql開頭的名字，例如：#sql45aa_7c69_2 ；
另外 information_schema.tables 表中是不會記錄臨時表的信息的。
用戶創建的臨時表的回收：
用戶創建的臨時表的其他信息參數：
會話臨時表空間存儲 用戶創建的臨時表和優化器 optimizer 創建的內部臨時表(當磁碟內部臨時表的存儲引擎為 InnoDB 時)；
innodb_temp_tablespaces_dir 變數定義了創建 會話臨時表空間的位置，默認是數據目錄下的#innodb_temp 目錄；
文件類似temp_[1-20].ibt ；
查看會話臨時表空間的元數據：
用戶創建的臨時表刪除後，其佔用的空間會被釋放(temp_[1-20].ibt文件會變小)。
在 MySQL 8.0.16 之前，internal_tmp_disk_storage_engine 變數定義了用戶創建的臨時表和 optimizer 創建的內部臨時表的引擎，可選 INNODB 和 MYISAM ；
從 MySQL 8.0.16 開始，internal_tmp_disk_storage_engine參數被移除，默認使用InnoDB存儲引擎；
innodb_temp_data_file_path 定義了用戶創建的臨時表使用的回滾段的存儲文件的相對路徑、名字、大小和屬性，該文件是全局臨時表空間(ibtmp1)；
可以使用語句查詢全局臨時表空間的數據文件大小：
SQL 什麼時候產生臨時表|臨時文件呢？
需要用到臨時表或臨時文件的時候，optimizer 自然會創建使用(感覺是廢話，但是又覺得有道理=.=!)；
(想像能力強的，可以牢記上面這句話；想像能力弱的，只能死記下面的 SQL 了。我也弱，此處有個疲憊的微笑?)
下面列舉一些 server 在處理 SQL 時，可能會創建內部臨時表的 SQL ：
SQL 包含 union | union distinct 關鍵字
SQL 中存在派生表
SQL 中包含 with 關鍵字
SQL 中的order by 和 group by 的欄位不同
SQL 為多表 update
SQL 中包含 distinct 和 order by 兩個關鍵字
我們可以通過下面兩種方式判斷 SQL 語句是否使用了臨時表空間：
# 如果 explain 的 Extra 列包含 Using temporary ，那麼說明會使用臨時空間，如果包含 Using filesort ，那麼說明會使用文件排序(臨時文件)；
如果執行 SQL 後，表的 ID 列變為了show processlist 中的 id 列的值，那麼說明 SQL 語句使用了臨時表空間
SQL創建的內部臨時表的存儲信息：
SQL 創建內部臨時表時，優先選擇在內存中，默認使用 TempTable 存儲引擎(由參數 internal_tmp_mem_storage_engine 確定)，
當 temptable 使用的內存量超過 temptable_max_ram 定義的大小時，
由 temptable_use_mmap 確定是使用內存映射文件的方式還是 InnoDB 磁碟內部臨時表的方式存儲數據
(temptable_use_mmap 參數在 MySQL 8.0.16 引入，MySQL 8.0.26 版本不推薦，後續應該會移除)；
temptable_use_mmap 的功能將由MySQL 8.0.23 版本引入的 temptable_max_mmap 代替，
當 temptable_max_mmap=0 時，說明不使用內存映射文件，等價於 temptable_use_mmap=OFF ；
當 temptable_max_mmap=N 時，N為正整數，包含了 temptable_use_mmap=ON 以及聲明了允許為內存映射文件分配的最大內存量。
該參數的定義解決了這些文件使用過多空間的風險。
內存映射文件產生的臨時文件會存放於 tmpdir 定義的目錄中，在 TempTable 存儲引擎關閉或 mysqld 進程關閉時，回收空間；
當 SQL 創建的內部臨時表，選擇 MEMORY 存儲引擎時，如果內存中的臨時表變的太大，MySQL 將自動將其轉為磁碟臨時表；
其能使用的內存上限為 min(tmp_table_size,max_heap_table_size)；
監控 TempTable 從內存和磁碟上分配的空間：
具體的欄位含義見：Section 27.12.20.10, 「Memory Summary Tables」.
監控內部臨時表的創建：
當在內存或磁碟上創建內部臨時表，伺服器會增加 Created_tmp_tables 的值；
當在磁碟上創建內部臨時表時，伺服器會增加 Created_tmp_disk_tables 的值，
如果在磁碟上創建了太多的內部臨時表，請考慮增加 tmp_table_size 和 max_heap_table_size 的值；
created_tmp_disk_tables 不計算在內存映射文件中創建的磁碟臨時表；
例外項：
臨時表/臨時文件一般較小，但是也存在需要大量空間的臨時表/臨時文件的需求：
因為這些例外項一般需要較大的空間，所以需要考慮是否要將其存放在獨立的掛載點上。
其他：
列出由失敗的 alter table 創建的隱藏臨時表，這些臨時表以#sql開頭，可以使用 drop table 刪除；
通過 lsof +L1 可以查看標識為 delete ，但還未釋放空間的文件。
如果想釋放這些 delete 狀態的文件，可以嘗試下面的方法(不推薦，後果自負)：
普通的磁碟臨時表|臨時文件(一般需要較小的空間)：
臨時表|臨時文件的一般所需的空間較小，會優先存放於內存中，若超過一定的大小，則會轉換為磁碟臨時表|臨時文件；
磁碟臨時表默認為 InnoDB 引擎，其存放在臨時表空間中，由 innodb_temp_tablespaces_dir 定義表空間的存放目錄，表空間文件類似：temp_[1-20].ibt ；MySQL 未定義 InnoDB 臨時表空間的最大使用上限；
當臨時表|臨時文件使用完畢後，會自動回收臨時表空間文件的大小；
innodb_temp_data_file_path 定義了用戶創建的臨時表使用的回滾段的存儲文件的相對路徑、名字、大小和屬性，該文件是全局臨時表空間(ibtmp1)，該文件可以設置文件最大使用大小；
例外項(一般需要較大的空間)：
load data local 語句，客戶端讀取文件並將其內容發送到伺服器，伺服器將其存儲在 tmpdir 參數指定的路徑中；
在 replica 中，回放 load data 語句時，需要將從 relay log 中解析出來的數據存儲在 slave_load_tmpdir(replica_load_tmpdir) 指定的目錄中，該參數默認和 tmpdir 參數指定的路徑相同；
需要 rebuild table 的在線 alter table 需要使用 innodb_tmpdir 存放排序磁碟排序文件，如果 innodb_tmpdir 未指定，則使用 tmpdir 的值；
若用戶判斷產生的臨時表|臨時文件一定會轉換為磁碟臨時表|臨時文件，那麼可以設置 set session big_tables=1;讓產生的臨時表|臨時文件直接存放在磁碟上；
對於需要較小空間的臨時表|臨時文件，MySQL 要麼將其存儲於內存，要麼放在統一的磁碟臨時表空間中，用完即釋放；
對於需要較大空間的臨時表|臨時文件，可以通過設置參數，將其存儲於單獨的目錄|掛載點；例如：load local data 語句或需要重建表的在線 alter table 語句，都有對應的參數設置其存放臨時表|臨時文件的路徑；
當前只有 innodb_temp_data_file_path 參數可以限制 用戶創建的臨時表使用的回滾段的存儲文件的大小，無其他參數可以限制臨時表|臨時文件可使用的磁碟空間；
MySQL 排序優化
2.1 排序方式
數據量小則在內存排序, 數據量大則使用磁碟排序
內存排序 : 直接使用”快速排序”
磁碟排序 : 先將數據分塊, 對每個獨立的塊使用”快速排序”, 並將各個塊的排序結果存在磁碟上, 然後將各個排好序的塊進行合併(merge), 最後返回排序結果
2.2 排序演算法
3. 注意點 :
原創文章，作者：BPDOL，如若轉載，請註明出處：https://www.506064.com/zh-tw/n/330491.html
微信掃一掃 
支付寶掃一掃 