MySQL中數據庫優化的常見sql語句有哪些

這篇文章主要介紹“MySQL中數據庫優化的常見sql語句有哪些”，在日常操作中，相信很多人在MySQL中數據庫優化的常見sql語句有哪些問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL中數據庫優化的常見sql語句有哪些”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

1.SHOW ENGINES

查看執行引擎以及默認引擎。

2.SHOW PROCESSLIST

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

屬性列以及含義：

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

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;后結果如下：

字段說明：

6.ALTER TABLE xx ENGINE = INNODB

重建表，包括索引結構。可以消除索引頁分裂以及刪除數據時留下的磁盤碎片。

7.ANALYZE TABLE

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

到此，關于“MySQL中數據庫優化的常見sql語句有哪些”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！