MySQL中數(shù)據(jù)庫(kù)優(yōu)化的常見(jiàn)sql語(yǔ)句總結(jié)
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è)很快的操作,如果不是這樣的話,就應(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 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ō)明:
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,則為YES |
Index_type | 索引結(jié)構(gòu)類(lèi)型,常見(jiàn)有FULLTEXT,HASH,BTREE,RTREE |
Comment、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ǔ)句總結(jié)的文章就介紹到這了,更多相關(guān)MySQL數(shù)據(jù)庫(kù)優(yōu)化語(yǔ)句內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Mysql數(shù)據(jù)庫(kù)delete操作沒(méi)報(bào)錯(cuò)卻刪除不了數(shù)據(jù)的解決
本文主要介紹了Mysql數(shù)據(jù)庫(kù)delete操作沒(méi)報(bào)錯(cuò)卻刪除不了數(shù)據(jù)的解決,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-01-01MySQL查看用戶(hù)權(quán)限及權(quán)限管理的方法詳解
在MySQL中,查看用戶(hù)權(quán)限可以通過(guò)多種方式實(shí)現(xiàn),主要取決于我們想要查看的權(quán)限類(lèi)型和詳細(xì)程度,本文給大家介紹了MySQL查看用戶(hù)權(quán)限及權(quán)限管理的方法,并通過(guò)代碼示例介紹的非常詳細(xì),需要的朋友可以參考下2024-03-03MySQL一對(duì)多查詢(xún)的實(shí)現(xiàn)示例
一對(duì)多連接查詢(xún)就是其中一種常見(jiàn)的查詢(xún)方式,它可以將一張表中的一行記錄與多張表中的多行記錄關(guān)聯(lián)起來(lái),并將其結(jié)果輸出,本文就來(lái)介紹一下如何使用,感興趣的可以了解一下2023-10-10mysql中如何將時(shí)間戳轉(zhuǎn)換為年月日格式進(jìn)行查詢(xún)
這篇文章主要介紹了mysql中如何將時(shí)間戳轉(zhuǎn)換為年月日格式進(jìn)行查詢(xún)問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-07-07詳解DBeaver連接MySQL8以上版本以及解決可能遇到的問(wèn)題
這篇文章主要介紹了DBeaver連接MySQL8以上版本以及解決可能遇到的問(wèn)題,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11MySQL使用命令創(chuàng)建、刪除、查詢(xún)索引的介紹
今天小編就為大家分享一篇關(guān)于MySQL使用命令創(chuàng)建、刪除、查詢(xún)索引的介紹,小編覺(jué)得內(nèi)容挺不錯(cuò)的,現(xiàn)在分享給大家,具有很好的參考價(jià)值,需要的朋友一起跟隨小編來(lái)看看吧2019-03-03mysql 5.7.20常用下載、安裝和配置方法及簡(jiǎn)單操作技巧(解壓版免安裝)
這篇文章主要介紹了mysql 5.7.20常用下載、安裝和配置方法及簡(jiǎn)單操作技巧(解壓版免安裝)的相關(guān)資料,需要的朋友可以參考下2017-11-11