1.SHOW ENGINES

查看執(zhí)行引擎以及默認(rèn)引擎。

2.SHOW PROCESSLIST

SHOW PROCESSLIST查看當(dāng)前數(shù)據(jù)庫(kù)連接的使用情況，以及各種狀態(tài)信息，非常有用。SHOW PROCESSLIST; 只列出前100條，如果想全列出請(qǐng)使用SHOW FULL PROCESSLIST;

屬性列以及含義：

state列以及含義，mysql列出的狀態(tài)：

3.SHOW STATUS LIKE 'InnoDB_row_lock%'

InnoDB 的行級(jí)鎖定狀態(tài)變量。

InnoDB 的行級(jí)鎖定狀態(tài)變量不僅記錄了鎖定等待次數(shù)，還記錄了鎖定總時(shí)長(zhǎng)，每次平均時(shí)長(zhǎng)，以及最大時(shí)長(zhǎng)，此外還有一個(gè)非累積狀態(tài)量顯示了當(dāng)前正在等待鎖定的等待數(shù)量。對(duì)各個(gè)狀態(tài)量的說(shuō)明如下：

• InnoDB_row_lock_current_waits：當(dāng)前正在等待鎖定的數(shù)量；
• InnoDB_row_lock_time：從系統(tǒng)啟動(dòng)到現(xiàn)在鎖定總時(shí)間長(zhǎng)度；
• InnoDB_row_lock_time_avg：每次等待所花平均時(shí)間；
• InnoDB_row_lock_time_max：從系統(tǒng)啟動(dòng)到現(xiàn)在等待最常的一次所花的時(shí)間；
• InnoDB_row_lock_waits：系統(tǒng)啟動(dòng)后到現(xiàn)在總共等待的次數(shù)；

對(duì)于這5個(gè)狀態(tài)變量，比較重要的主要是InnoDB_row_lock_time_avg（等待平均時(shí)長(zhǎng)），InnoDB_row_lock_waits（等待總次數(shù)）以及InnoDB_row_lock_time（等待總時(shí)長(zhǎng)）這三項(xiàng)。尤其是當(dāng)?shù)却螖?shù)很高，而且每次等待時(shí)長(zhǎng)也不小的時(shí)候，我們就需要分析系統(tǒng)中為什么會(huì)有如此多的等待，然后根據(jù)分析結(jié)果著手指定優(yōu)化計(jì)劃。

如果發(fā)現(xiàn)鎖爭(zhēng)用比較嚴(yán)重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比較高，還可以通過(guò)設(shè)置InnoDB Monitors 來(lái)進(jìn)一步觀察發(fā)生鎖沖突的表、數(shù)據(jù)行等，并分析鎖爭(zhēng)用的原因。

4.SHOW ENGINE INNODB STATUS

SHOW ENGINE INNODB STATUS命令會(huì)輸出當(dāng)前InnoDB監(jiān)視器監(jiān)視到的很多信息，它輸出就是一個(gè)單獨(dú)的字符串，沒(méi)有行和列，內(nèi)容分為很多小段，每一段對(duì)應(yīng)innodb存儲(chǔ)引擎不同部分的信息，其中有一些信息對(duì)于innodb開(kāi)發(fā)者來(lái)說(shuō)非常有用。

有一節(jié)LATEST DETECTED DEADLOCK，就是記錄的最后一次死鎖信息， 如下案例：

• “(1) TRANSACTION”顯示第一個(gè)事務(wù)的信息；
• “(1) WAITING FOR THIS LOCK TO BE GRANTED”顯示第一個(gè)事務(wù)等待的鎖信息
• “(2) TRANSACTION”顯示第二個(gè)事務(wù)的信息；
• “(2) HOLDS THE LOCK(S)” 顯示的是第二個(gè)事務(wù)持有的鎖信息；
• “(2) WAITING FOR THIS LOCK TO BE GRANTED” 顯示第二個(gè)事務(wù)等待的鎖信息
• 最后一行表示處理結(jié)果，比如“WE ROLL BACK TRANSACTION (2)，表示回滾了第二個(gè)事務(wù)。

5.SHOW INDEXS

SHOW INDEXS查詢(xún)一個(gè)表中的索引信息：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'
);

存儲(chǔ)過(guò)程插入五萬(wàn)條數(shù)據(jù)：

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;后結(jié)果如下：

字段說(shuō)明：

6.ALTER TABLE xx ENGINE = INNODB

重建表，包括索引結(jié)構(gòu)?？梢韵饕?yè)分裂以及刪除數(shù)據(jù)時(shí)留下的磁盤(pán)碎片。

7.ANALYZE TABLE

不是重建表，只是對(duì)表的索引信息做重新統(tǒng)計(jì)，沒(méi)有修改數(shù)據(jù)，這個(gè)過(guò)程中加了MDL讀鎖?？梢杂脕?lái)修正show index from tablename;中統(tǒng)計(jì)索引的Cardinality是數(shù)據(jù)異常的情況。

到此這篇關(guān)于MySQL中數(shù)據(jù)庫(kù)優(yōu)化的常見(jiàn)sql語(yǔ)句總結(jié)的文章就介紹到這了,更多相關(guān)MySQL數(shù)據(jù)庫(kù)優(yōu)化語(yǔ)句內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論