共計(jì) 3701 個(gè)字符，預(yù)計(jì)需要花費(fèi) 10 分鐘才能閱讀完成。

這篇文章主要介紹“MySQL 中數(shù)據(jù)庫(kù)優(yōu)化的常見(jiàn) sql 語(yǔ)句有哪些”，在日常操作中，相信很多人在 MySQL 中數(shù)據(jù)庫(kù)優(yōu)化的常見(jiàn) sql 語(yǔ)句有哪些問(wèn)題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”MySQL 中數(shù)據(jù)庫(kù)優(yōu)化的常見(jiàn) sql 語(yǔ)句有哪些”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著丸趣 TV 小編一起來(lái)學(xué)習(xí)吧！

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;

屬性列以及含義：

id 一個(gè)標(biāo)識(shí)，要 kill 一個(gè)語(yǔ)句的時(shí)候很有用。user 顯示當(dāng)前用戶(hù)，如果不是 root，這個(gè)命令就只顯示你權(quán)限范圍內(nèi)的 sql 語(yǔ)句。host 顯示這個(gè)語(yǔ)句是從哪個(gè) ip 的哪個(gè)端口上發(fā)出的。可用來(lái)追蹤出問(wèn)題語(yǔ)句的用戶(hù)。db 顯示這個(gè)進(jìn)程目前連接的是哪個(gè)數(shù)據(jù)庫(kù)。command 顯示當(dāng)前連接的執(zhí)行的命令，一般就是休眠（sleep），查詢(xún)（query），連接（connect）。

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

Checking table 正在檢查數(shù)據(jù)表（這是自動(dòng)的）。Closing tables 正在將表中修改的數(shù)據(jù)刷新到磁盤(pán)中，同時(shí)正在關(guān)閉已經(jīng)用完的表。這是一個(gè)很快的操作，如果不是這樣的話(huà)，就應(yīng)該確認(rèn)磁盤(pán)空間是否已經(jīng)滿(mǎn)了或者磁盤(pán)是否正處于重負(fù)中。Connect Out 復(fù)制從服務(wù)器正在連接主服務(wù)器。Copying to tmp table on disk 由于臨時(shí)結(jié)果集大于 tmp_table_size（默認(rèn) 16M），正在將臨時(shí)表從內(nèi)存存儲(chǔ)轉(zhuǎn)為磁盤(pán)存儲(chǔ)以此節(jié)省內(nèi)存。Creating tmp table 正在創(chuàng)建臨時(shí)表以存放部分查詢(xún)結(jié)果。deleting from main table 服務(wù)器正在執(zhí)行多表刪除中的第一部分，剛刪除第一個(gè)表。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
 MOD ( i, 100 ) = 0 THEN
 SET j = CONCAT(  first_name , i );
 END IF;
 MOD ( i, 200 ) = 0 THEN
 SET k = CONCAT(  last_name , i );
 END 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ō)明：

Table 表名 Non_unique 唯一索引為 0，其他索引為 1。主鍵索引也是唯一索引。Key_name 索引名。如果名字相同則表明是同一個(gè)索引，并且是聯(lián)合索引，每一行都表示聯(lián)合索引中的某一個(gè)列。Seq_in_index 索引中的列序列號(hào)，從 1 開(kāi)始。也可以表明該列在聯(lián)合索引中的順序。Column_name 索引列名，如果是聯(lián)合索引則是某一個(gè)列的名字 Collation 列以什么方式存儲(chǔ)在索引中，大概意思就是字符序。Cardinality 一個(gè)索引上不同的值的個(gè)數(shù)，我們稱(chēng)之為“基數(shù)”（cardinality），也稱(chēng)為區(qū)分度，這個(gè)基數(shù)越大，索引的區(qū)分度越好。該值的統(tǒng)計(jì)不一定是準(zhǔn)確的，可以使用 ANALYZE TABLE 修正。Sub_part 前綴索引。如果列只是被部分地編入索引，則為被編入索引的字符的數(shù)目。如果整列的值都被編入索引，則為 NULL。Packed 關(guān)鍵字如何被壓縮。如果沒(méi)有被壓縮，則為 NULL。壓縮一般包括壓縮傳輸協(xié)議、壓縮列解決方案和壓縮表解決方案。Null 如果列值可以包含 null，則為 YESIndex_type 索引結(jié)構(gòu)類(lèi)型，常見(jiàn)有 FULLTEXT，HASH，BTREE，RTREEComment、Index_comment 注釋 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ǔ)句有哪些”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注丸趣 TV 網(wǎng)站，丸趣 TV 小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！