MySQL中數(shù)據(jù)庫優(yōu)化的常見sql語句總結(jié)
1.SHOW ENGINES
查看執(zhí)行引擎以及默認引擎。
2.SHOW PROCESSLIST
SHOW PROCESSLIST查看當(dāng)前數(shù)據(jù)庫連接的使用情況,以及各種狀態(tài)信息,非常有用。SHOW PROCESSLIST; 只列出前100條,如果想全列出請使用SHOW FULL PROCESSLIST;
屬性列以及含義:
id | 一個標識,要kill 一個語句的時候很有用。 |
---|---|
user | 顯示當(dāng)前用戶,如果不是root,這個命令就只顯示你權(quán)限范圍內(nèi)的sql語句。 |
host | 顯示這個語句是從哪個ip 的哪個端口上發(fā)出的。可用來追蹤出問題語句的用戶。 |
db | 顯示這個進程目前連接的是哪個數(shù)據(jù)庫。 |
command | 顯示當(dāng)前連接的執(zhí)行的命令,一般就是休眠(sleep),查詢(query),連接(connect)。 |
state列以及含義,mysql列出的狀態(tài):
Checking table | 正在檢查數(shù)據(jù)表(這是自動的)。 |
---|---|
Closing tables | 正在將表中修改的數(shù)據(jù)刷新到磁盤中,同時正在關(guān)閉已經(jīng)用完的表。這是一個很快的操作,如果不是這樣的話,就應(yīng)該確認磁盤空間是否已經(jīng)滿了或者磁盤是否正處于重負中。 |
Connect Out | 復(fù)制從服務(wù)器正在連接主服務(wù)器。 |
Copying to tmp table on disk | 由于臨時結(jié)果集大于tmp_table_size(默認16M),正在將臨時表從內(nèi)存存儲轉(zhuǎn)為磁盤存儲以此節(jié)省內(nèi)存。 |
Creating tmp table | 正在創(chuàng)建臨時表以存放部分查詢結(jié)果。 |
deleting from main table | 服務(wù)器正在執(zhí)行多表刪除中的第一部分,剛刪除第一個表。 |
3.SHOW STATUS LIKE 'InnoDB_row_lock%'
InnoDB 的行級鎖定狀態(tài)變量。
InnoDB 的行級鎖定狀態(tài)變量不僅記錄了鎖定等待次數(shù),還記錄了鎖定總時長,每次平均時長,以及最大時長,此外還有一個非累積狀態(tài)量顯示了當(dāng)前正在等待鎖定的等待數(shù)量。對各個狀態(tài)量的說明如下:
- InnoDB_row_lock_current_waits:當(dāng)前正在等待鎖定的數(shù)量;
- InnoDB_row_lock_time:從系統(tǒng)啟動到現(xiàn)在鎖定總時間長度;
- InnoDB_row_lock_time_avg:每次等待所花平均時間;
- InnoDB_row_lock_time_max:從系統(tǒng)啟動到現(xiàn)在等待最常的一次所花的時間;
- InnoDB_row_lock_waits:系統(tǒng)啟動后到現(xiàn)在總共等待的次數(shù);
對于這5個狀態(tài)變量,比較重要的主要是InnoDB_row_lock_time_avg(等待平均時長),InnoDB_row_lock_waits(等待總次數(shù))以及InnoDB_row_lock_time(等待總時長)這三項。尤其是當(dāng)?shù)却螖?shù)很高,而且每次等待時長也不小的時候,我們就需要分析系統(tǒng)中為什么會有如此多的等待,然后根據(jù)分析結(jié)果著手指定優(yōu)化計劃。
如果發(fā)現(xiàn)鎖爭用比較嚴重,如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比較高,還可以通過設(shè)置InnoDB Monitors 來進一步觀察發(fā)生鎖沖突的表、數(shù)據(jù)行等,并分析鎖爭用的原因。
4.SHOW ENGINE INNODB STATUS
SHOW ENGINE INNODB STATUS命令會輸出當(dāng)前InnoDB監(jiān)視器監(jiān)視到的很多信息,它輸出就是一個單獨的字符串,沒有行和列,內(nèi)容分為很多小段,每一段對應(yīng)innodb存儲引擎不同部分的信息,其中有一些信息對于innodb開發(fā)者來說非常有用。
有一節(jié)LATEST DETECTED DEADLOCK,就是記錄的最后一次死鎖信息, 如下案例:
- “(1) TRANSACTION”顯示第一個事務(wù)的信息;
- “(1) WAITING FOR THIS LOCK TO BE GRANTED”顯示第一個事務(wù)等待的鎖信息
- “(2) TRANSACTION”顯示第二個事務(wù)的信息;
- “(2) HOLDS THE LOCK(S)” 顯示的是第二個事務(wù)持有的鎖信息;
- “(2) WAITING FOR THIS LOCK TO BE GRANTED” 顯示第二個事務(wù)等待的鎖信息
- 最后一行表示處理結(jié)果,比如“WE ROLL BACK TRANSACTION (2),表示回滾了第二個事務(wù)。
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' );
存儲過程插入五萬條數(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é)果如下:
字段說明:
Table | 表名 |
---|---|
Non_unique | 唯一索引為0,其他索引為1。主鍵索引也是唯一索引。 |
Key_name | 索引名。如果名字相同則表明是同一個索引,并且是聯(lián)合索引,每一行都表示聯(lián)合索引中的某一個列。 |
Seq_in_index | 索引中的列序列號,從1開始。也可以表明該列在聯(lián)合索引中的順序。 |
Column_name | 索引列名,如果是聯(lián)合索引則是某一個列的名字 |
Collation | 列以什么方式存儲在索引中,大概意思就是字符序。 |
Cardinality | 一個索引上不同的值的個數(shù),我們稱之為“基數(shù)”(cardinality),也稱為區(qū)分度,這個基數(shù)越大,索引的區(qū)分度越好。該值的統(tǒng)計不一定是準確的,可以使用ANALYZE TABLE修正。 |
Sub_part | 前綴索引。如果列只是被部分地編入索引,則為被編入索引的字符的數(shù)目。如果整列的值都被編入索引,則為NULL。 |
Packed | 關(guān)鍵字如何被壓縮。如果沒有被壓縮,則為NULL。壓縮一般包括壓縮傳輸協(xié)議、壓縮列解決方案和壓縮表解決方案。 |
Null | 如果列值可以包含null,則為YES |
Index_type | 索引結(jié)構(gòu)類型,常見有FULLTEXT,HASH,BTREE,RTREE |
Comment、Index_comment | 注釋 |
6.ALTER TABLE xx ENGINE = INNODB
重建表,包括索引結(jié)構(gòu)??梢韵饕摲至岩约皠h除數(shù)據(jù)時留下的磁盤碎片。
7.ANALYZE TABLE
不是重建表,只是對表的索引信息做重新統(tǒng)計,沒有修改數(shù)據(jù),這個過程中加了MDL讀鎖??梢杂脕硇拚齭how index from tablename;中統(tǒng)計索引的Cardinality是數(shù)據(jù)異常的情況。
到此這篇關(guān)于MySQL中數(shù)據(jù)庫優(yōu)化的常見sql語句總結(jié)的文章就介紹到這了,更多相關(guān)MySQL數(shù)據(jù)庫優(yōu)化語句內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Mysql數(shù)據(jù)庫delete操作沒報錯卻刪除不了數(shù)據(jù)的解決
本文主要介紹了Mysql數(shù)據(jù)庫delete操作沒報錯卻刪除不了數(shù)據(jù)的解決,文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-01-01MySQL查看用戶權(quán)限及權(quán)限管理的方法詳解
在MySQL中,查看用戶權(quán)限可以通過多種方式實現(xiàn),主要取決于我們想要查看的權(quán)限類型和詳細程度,本文給大家介紹了MySQL查看用戶權(quán)限及權(quán)限管理的方法,并通過代碼示例介紹的非常詳細,需要的朋友可以參考下2024-03-03mysql中如何將時間戳轉(zhuǎn)換為年月日格式進行查詢
這篇文章主要介紹了mysql中如何將時間戳轉(zhuǎn)換為年月日格式進行查詢問題,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教2023-07-07詳解DBeaver連接MySQL8以上版本以及解決可能遇到的問題
這篇文章主要介紹了DBeaver連接MySQL8以上版本以及解決可能遇到的問題,文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11MySQL使用命令創(chuàng)建、刪除、查詢索引的介紹
今天小編就為大家分享一篇關(guān)于MySQL使用命令創(chuàng)建、刪除、查詢索引的介紹,小編覺得內(nèi)容挺不錯的,現(xiàn)在分享給大家,具有很好的參考價值,需要的朋友一起跟隨小編來看看吧2019-03-03mysql 5.7.20常用下載、安裝和配置方法及簡單操作技巧(解壓版免安裝)
這篇文章主要介紹了mysql 5.7.20常用下載、安裝和配置方法及簡單操作技巧(解壓版免安裝)的相關(guān)資料,需要的朋友可以參考下2017-11-11