MySQL中資料庫優化的常見sql語句總結

2022-08-23 14:03:25

1.SHOW ENGINES

檢視執行引擎以及預設引擎。

2.SHOW PROCESSLIST

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

屬性列以及含義：

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

state列以及含義，mysql列出的狀態：

Checking table	正在檢查資料表（這是自動的）。
Closing tables	正在將表中修改的資料重新整理到磁碟中，同時正在關閉已經用完的表。這是一個很快的操作，如果不是這樣的話，就應該確認磁碟空間是否已經滿了或者磁碟是否正處於重負中。
Connect Out	複製從伺服器正在連線主伺服器。
Copying to tmp table on disk	由於臨時結果集大於tmp_table_size（預設16M），正在將臨時表從記憶體儲存轉為磁碟儲存以此節省記憶體。
Creating tmp table	正在建立臨時表以存放部分查詢結果。
deleting 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( 'first_name', i );
			
		END IF;
		IF
			MOD ( i, 200 ) = 0 THEN
				
				SET k = CONCAT( 'last_name', i );
			
		END IF;
		IF
			MOD ( i, 50 ) = 0 THEN
				
				SET m = CONCAT( '', 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;後結果如下：

欄位說明：

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