MySQL中資料庫最佳化的常見sql語句（總結分享）

這篇文章為大家帶來了關於mysql的相關知識，其中主要為大家總結了一些MySQL中資料庫優化的常見sql語句，文中的範例程式碼講解詳細，下面一起來看一下，希望對大家有幫助。

推薦學習：mysql影片教學

#1.SHOW ENGINES

##查看執行引擎以及預設引擎。

2.SHOW PROCESSLIST

SHOW PROCESSLIST查看當前資料庫連接的使用情況，以及各種狀態信息，非常有用。 SHOW PROCESSLIST; 只列出前100條，如果想全列出請使用SHOW FULL PROCESSLIST;

##屬性列以及意義：

#iduserhostdbcommandstate欄位以及意義，mysql列出的狀態：

一個標識，要kill 一個語句的時候很有用。
顯示目前用戶，如果不是root，這個指令就只顯示你權限範圍內的sql語句。
顯示這個語句是從哪個ip 的哪個連接埠上發出的。可用於追蹤出問題語句的使用者。
顯示這個程序目前連接的是哪個資料庫。
顯示目前連線的執行的指令，一般就是休眠（sleep），查詢（query），連線（connect）。

Checking tableClosing tablesConnect OutCopying to tmp table on diskCreating tmp tabledeleting from main table

3.SHOW STATUS LIKE 'InnoDB_row_lock%'

#InnoDB 的行級鎖定狀態變數。

InnoDB 的行級鎖定狀態變數不僅記錄了鎖定等待次數，還記錄了鎖定總時長，每次平均時長，以及最大時長，此外還有一個非累積狀態量顯示了目前正在等待鎖定的等待數量。各個狀態量的說明如下：

• InnoDB_row_lock_current_waits：目前正在等待鎖定的數量；
• InnoDB_row_lock_time：從系統啟動到現在鎖定總時間長度；
• InnoDB_row_lock_time_avg：每次等待所花平均時間；
• InnoDB_row_lock_time_max：從系統啟動到現在等待最常的一次所花的時間；
• InnoDB_row_lock_waits：系統啟動後到現在總共等待的次數；

對於這5個狀態變量，比較重要的主要是InnoDB_row_lock_time_avg（等待平均時長），InnoDB_row_lock_waits（等待總次數）以及InnoDB_row_lock_time（等待總時長）這三項。尤其是當等待次數很高，而且每次等待時長也不小的時候，我們就需要分析系統中為什麼會有如此多的等待，然後根據分析結果著手指定優化計劃。

如果發現鎖定爭用比較嚴重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比較高，還可以透過設定InnoDB Monitors 來進一步觀察發生鎖定衝突的資料表、資料行等，並分析鎖定爭用的原因。

4.SHOW ENGINE INNODB STATUS

SHOW ENGINE INNODB STATUS指令會輸出目前InnoDB監視器監視到的很多訊息，它輸出就是一個單獨的字串，沒有行和列，內容分成很多小段，每一段對應innodb儲存引擎不同部分的信息，其中有一些資訊對於innodb開發者來說非常有用。

有一節LATEST DETECTED DEADLOCK，就是記錄的最後一次死鎖信息， 如下案例：

• “(1) TRANSACTION”顯示第一個交易的資訊；
• 「(1) WAITING FOR THIS LOCK TO BE GRANTED」顯示第一個交易等待的鎖定資訊
• 「(2) TRANSACTION」顯示第二個事務的資訊；
• “(2) HOLDS THE LOCK(S)” 顯示的是第二個事務所持有的鎖資訊；
• “(2) WAITING FOR THIS LOCK TO BE GRANTED」顯示第二個交易等待的鎖定資訊
• 最後一行表示處理結果，例如「WE ROLL BACK TRANSACTION (2)，表示回滾了第二個交易。

#5.SHOW INDEXS

SHOW INDEXS查詢一個表中的索引資訊：SHOW INDEXES FROM table_name;

建表的sql如下：

CREATE TABLE contacts(
    contact_id INT AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL comment 'first name',
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    PRIMARY KEY(contact_id),
    UNIQUE(email),
    INDEX phone(phone) ,
    INDEX names(first_name, last_name) comment 'By first name and/or last name'
);

預存程序插入五萬筆資料：

CREATE PROCEDURE zqtest ( ) BEGIN
	DECLARE
		i INT DEFAULT 0;
	DECLARE
		j VARCHAR ( 100 ) DEFAULT 'first_name';
	DECLARE
		k VARCHAR ( 100 ) DEFAULT 'last_name';
	DECLARE
		l VARCHAR ( 100 ) DEFAULT 'email';
	DECLARE
		m VARCHAR ( 20 ) DEFAULT '11111111111';
	
	SET i = 0;
	START TRANSACTION;
	WHILE
			i < 50000 DO
		IF
			MOD ( i, 100 ) = 0 THEN
				
				SET j = CONCAT( &#39;first_name&#39;, i );
			
		END IF;
		IF
			MOD ( i, 200 ) = 0 THEN
				
				SET k = CONCAT( &#39;last_name&#39;, i );
			
		END IF;
		IF
			MOD ( i, 50 ) = 0 THEN
				
				SET m = CONCAT( &#39;&#39;, CAST( m as UNSIGNED) + i );
			
		END IF;
		INSERT INTO contacts ( first_name, last_name, email, phone )
		VALUES
			( j, k, CONCAT(l,i), m );
		
		SET i = i + 1;
		
	END WHILE;
	COMMIT;
	
END;

使用show index from contacts;後結果如下：

#欄位說明：

正在檢查資料表（這是自動的）。
正在將表中修改的資料刷新到磁碟中，同時正在關閉已經用完的表。這是一個很快的操作，如果不是這樣的話，就應該確認磁碟空間是否已經滿了或者磁碟是否正處於重負中。
複製從伺服器正在連接主伺服器。
由於臨時結果集大於tmp_table_size（預設16M），正在將臨時表從記憶體儲存轉為磁碟儲存以此節省內存。
正在建立暫存表以存放部分查詢結果。
伺服器正在執行多表刪除中的第一部分，剛刪除第一個表。

##Index_type索引結構類型，常見有FULLTEXT，HASH，BTREE，RTREEComment、Index_comment註解

6.ALTER TABLE xx ENGINE = INNODB

重建表，包括索引結構。可以消除索引頁分割以及刪除資料時留下的磁碟碎片。

7.ANALYZE TABLE

不是重建表，只是對錶的索引資訊做重新統計，沒有修改數據，這個過程中加了MDL讀鎖。可以用來修正show index from tablename;中統計索引的Cardinality是資料異常的情況。

推薦學習：mysql影片教學

#

Table	表名
Non_unique	唯一索引為0，其他索引為1。主鍵索引也是唯一索引。
Key_name	索引名。如果名字相同則表示是同一個索引，並且是聯合索引，每一行都表示聯合索引中的某一個欄位。
Seq_in_index	索引中的列序號，從1開始。也可以表示該列在聯合索引中的順序。
Column_name	索引列名，如果是聯合索引則是某一列的名字
#Collat​​ion	列以什麼方式儲存在索引中，大概意思就是字元序。
Cardinality	一個索引上不同的值的個數，我們稱之為「基數」（cardinality），也稱為區分度，這個基數越大，索引的區分度越好。該值的統計不一定是準確的，可以使用ANALYZE TABLE修正。
Sub_part	前綴索引。如果列只是部分地編入索引，則為被編入索引的字元的數目。如果整列的值都被編入索引，則為NULL。
Packed	關鍵字如何被壓縮。如果沒有被壓縮，則為NULL。壓縮一般包括壓縮傳輸協定、壓縮列解決方案和壓縮表解決方案。
Null	如果列值可以包含null，則為YES

以上是MySQL中資料庫最佳化的常見sql語句（總結分享）的詳細內容。更多資訊請關注PHP中文網其他相關文章！