檢視mysql伺服器靜態引數：

show variables;

檢視mysql動態執行資訊：

show status;

一、查詢使用者的連線數

--show processlist;只列出前100條，如果想全列出請使用show full processlist;

命令： show processlist;

如果是root帳號，你能看到所有使用者的當前連線。如果是其它普通帳號，只能看到自己佔用的連線。用 mysql -uroot -p123456 來登入（注意：使用者名稱和密碼不用包含“”）

二、檢視資料庫執行狀態

命令： show global status;

命令：show status;

命令：show status like '%下面變數%';

Aborted_clients 由於客戶沒有正確關閉連線已經死掉，已經放棄的連線數量。

Aborted_connects 嘗試已經失敗的MySQL伺服器的連線的次數。

Connections 試圖連線MySQL伺服器的次數。

Created_tmp_tables 當執行語句時，已經被創造了的隱含臨時表的數量。

Delayed_insert_threads 正在使用的延遲插入處理器執行緒的數量。

Delayed_writes 用INSERT DELAYED寫入的行數。

Delayed_errors 用INSERT DELAYED寫入的發生某些錯誤(可能重複鍵值)的行數。

Flush_commands 執行FLUSH命令的次數。

Handler_delete 請求從一張表中刪除行的次數。

Handler_read_first 請求讀入表中第一行的次數。

Handler_read_key 請求數字基於鍵讀行。

Handler_read_next 請求讀入基於一個鍵的一行的次數。

Handler_read_rnd 請求讀入基於一個固定位置的一行的次數。

Handler_update 請求更新表中一行的次數。

Handler_write 請求向表中插入一行的次數。

Key_blocks_used 用於關鍵字快取的塊的數量。

Key_read_requests 請求從快取讀入一個鍵值的次數。

Key_reads 從磁碟物理讀入一個鍵值的次數。

Key_write_requests 請求將一個關鍵字塊寫入快取次數。

Key_writes 將一個鍵值塊物理寫入磁碟的次數。

Max_used_connections 同時使用的連線的最大數目。

Not_flushed_key_blocks 在鍵快取中已經改變但是還沒被清空到磁碟上的鍵塊。

Not_flushed_delayed_rows 在INSERT DELAY佇列中等待寫入的行的數量。

Open_tables 開啟表的數量。

Open_files 開啟檔案的數量。

Open_streams 開啟流的數量(主要用於日誌記載）

Opened_tables 已經開啟的表的數量。

Questions 發往伺服器的查詢的數量。

Slow_queries 要花超過long_query_time時間的查詢數量。

Threads_connected 當前開啟的連線的數量。

Threads_running 不在睡眠的執行緒數量。

Uptime 伺服器工作了多少秒。

註釋：

如果Opened_tables太大，那麼你的table_open_cache變數可能太小。

如果key_reads太大，那麼你的key_cache可能太小。快取命中率可以用key_reads/key_read_requests計算。

如果Handler_read_rnd太大，那麼你很可能有大量的查詢需要MySQL掃描整個表或你有沒正確使用鍵值的聯結(join)。

可以根據“show status”命令返回的狀態進行微調。主要注意以下變數的數值，越小越好，最好為零：

Created_tmp_disk_tables

Created_tmp_tables

Created_tmp_files

Slow_queries

三、資料庫效能等查詢

1. Show status命令瞭解各種SQL的執行頻率

--檢視當前會話執行的各項命令統計 com_XXX

show session status like 'Com_%' 其中session可省

show status like 'Com_%'

--顯示全域性統計使用

SHOW GLOBAL STATUS LIKE 'COM_%';

2. 針對InnoDB儲存引擎狀態的統計

SHOW GLOBAL STATUS LIKE 'Innodb_%'

3. 檢視試圖連線mySQL伺服器的次數

show global status like 'connections';

4. 檢視伺服器工作時間

show global status like 'uptime';

5.檢視所在的資料庫名

（1）用select database()語句;

select database();

（2）用show tables語句，查詢出來的結果中，第一行為Tables_in_***，這裡***就是當前所在的資料庫名稱。

show tables;

（3）用status語句，查詢出來的結果中有一行是currrent database:***。這裡***就是當前所在的資料庫名稱。

status;

6.檢視錶的儲存格式資訊

show table status like '%test%'\G;

--顯示當前使用或者指定的database中的每個表的資訊。資訊包括表型別和表的最新更新時間

show table status;

--顯示當前資料庫中所有表的名稱

show tables或show tables from database_name;

7.檢視告警資訊

show warnings\G;

8.查詢表結構資訊

show create table test\G;

9.查詢當前架構(schema)下的基表

select * from information_schema.tables where table_type='BASE TABLE' and table_schema=database()\G;

10. 查詢當前架構(schema)下的檢視資訊

select * from information_schema.views where table_schema=database()\G';

11.查詢資料庫是否啟用了分割槽功能

show variables like '%partition%'\G;

show plugins\G;

12.顯示索引資訊

show index from TABLE_NAME\G

show keys from TABLE_NAME ;

13. 查詢當前架構(schema)下的分割槽資訊

select * from information_schema.PARTITIONS where table_schema=database() and table_name='t3'\G;

14. 查詢當前架構(schema)下的表

select * from information_schema.tables where table_name='t' and table_schema=database()\G;

15.顯示mysql如何使用索引來處理select語句以及連線表

https://www.cnblogs.com/yycc/p/7338894.html

explain sql\G;

explain partitions sql\G;

17.根據已知表建立新表的結構

create table e2 like e;

18.將已知分割槽表的結構變為普通表

alter table e2 remove partitioning;

19.交換分割槽

alter table e exchange partition p0 with table e2;

20.檢視innodb儲存引擎中的latch

show engine innodb mutex;

21.檢視innodb儲存引擎中的當前鎖引擎資訊

show engine innodb status\G;

show full processlist;

select * from information_schema.innodb_trx\G;

select * from information_schema.innodb_locks\G;

select * from information_schema.innodb_lock_waits\G;

--鎖聯合查詢，如下：

select r.trx_id waiting_trx_id,

r.trx_requested_lock_id waiting_requested_lock_id,

r.trx_mysql_thread_id waiting_thread,

r.trx_query waiting_query,

r.trx_state waiting_status,

b.trx_id blocking_trx_id,

b.trx_mysql_thread_id blocking_thread,

b.trx_query blocking_query,

b.trx_state blocking_status

from information_schema.innodb_lock_waits w,

information_schema.innodb_trx b,

information_schema.innodb_trx r

where b.trx_id = w.blocking_trx_id

and r.trx_id = w.requesting_trx_id\G;

22.mysql 顯示開啟事物與關閉事物

開啟事物： start transaction 或者 begin

關閉事物：commit---成功與 rollback--回滾；

23. mysql修改事務隔離級別

使用者可以用SET TRANSACTION語句改變單個會話或者所有新進連線的隔離級別。它的語法如下：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED |

REPEATABLE READ | SERIALIZABLE}

注意：預設的行為（不帶session和global）是為下一個（未開始）事務設定隔離級別。如果你使用GLOBAL關鍵字，

語句在全域性對從那點開始建立的所有新連線（除了不存在的連線）設定預設事務級別。你需要SUPER許可權來做這個。

使用SESSION 關鍵字為將來在當前連線上執行的事務設定預設事務級別。任何客戶端都能自由改變會話隔離級別（甚至

在事務的中間），或者為下一個事務設定隔離級別。

你可以用下列語句查詢全域性和會話事務隔離級別：

SELECT @@global.tx_isolation;

SELECT @@session.tx_isolation;

SELECT @@tx_isolation;

24. SHOW COLUMNS顯示有關給定表中列的資訊。它也適用於檢視。 SHOW COLUMNS僅顯示您具有某些許可權的列的資訊。

-- 顯示錶中列名稱

show columns from table_name from database_name; 或show columns from database_name.table_name;

SHOW [FULL] {COLUMNS | FIELDS}

{FROM | IN} tbl_name

[{FROM | IN} db_name]

[LIKE 'pattern' | WHERE expr]

25. QPS(每秒Query量)

QPS = Questions(or Queries) / seconds

mysql > show global status like 'Question%';

26. TPS(每秒事務量)

TPS = (Com_commit + Com_rollback) / seconds

mysql > show global status like 'Com_commit';

mysql > show global status like 'Com_rollback';

27. key Buffer 命中率

mysql>show global status like 'key%';

key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%

key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%

28. InnoDB Buffer命中率

mysql> show status like 'innodb_buffer_pool_read%';

innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%

29. Query Cache命中率

mysql> show status like 'Qcache%';

Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%;

30. Table Cache狀態量

mysql> show global status like 'open%';

比較 open_tables 與 opend_tables 值

31. Thread Cache 命中率

mysql> show global status like 'Thread%';

mysql> show global status like 'Connections';

Thread_cache_hits = (1 - Threads_created / connections ) * 100%

32. 鎖定狀態

mysql> show global status like '%lock%';

Table_locks_waited/Table_locks_immediate=0.3% 如果這個比值比較大的話，說明表鎖造成的阻塞比較嚴重

Innodb_row_lock_waits innodb行鎖，太大可能是間隙鎖造成的

33. 複製延時量

mysql > show slave status

檢視延時時間

34. Tmp Table 狀況(臨時表狀況)

mysql > show status like 'Create_tmp%';

Created_tmp_disk_tables/Created_tmp_tables比值最好不要超過10%，如果Created_tmp_tables值比較大，

可能是排序句子過多或者是連線句子不夠最佳化

35. Binlog Cache 使用狀況

mysql > show status like 'Binlog_cache%';

如果Binlog_cache_disk_use值不為0 ，可能需要調大 binlog_cache_size大小

36. Innodb_log_waits 量

mysql > show status like 'innodb_log_waits';

Innodb_log_waits值不等於0的話，表明 innodb log buffer 因為空間不足而等待

比如命令：

>#show global status;

雖然可以使用：

>#show global status like %...%;

來過濾，但是對應長長的list，每一項都代表什麼意思，還是有必要弄清楚。

37.檢視是否啟動了分散式事物XA(預設是ON):

show variables like '%innodb_support_xa%';

38.MySQL XA事務基本語法

XA {START|BEGIN} xid [JOIN|RESUME] 啟動xid事務 (xid 必須是一個唯一值; 不支援[JOIN|RESUME]子句)

XA END xid [SUSPEND [FOR MIGRATE]] 結束xid事務 ( 不支援[SUSPEND [FOR MIGRATE]] 子句)

XA PREPARE xid 準備、預提交xid事務

XA COMMIT xid [ONE PHASE] 提交xid事務

XA ROLLBACK xid 回滾xid事務

XA RECOVER 檢視處於PREPARE 階段的所有事務

39.判斷資料庫記憶體是否已經達到瓶頸

show global status like '%innodb%read%'\G;

40.檢視索引使用情況

show status like '%Handler_read%';

41.檢視innodb儲存引擎上行鎖的爭用情況：

show status like '%innodb_row_lock%';

如果發現鎖爭用比較嚴重，則 Innodb_row_lock_time_avg和Innodb_row_lock_waits 比較高。

另外就是設定innodbDB Monitors進一步發生鎖衝突的表、資料行等，並分析鎖爭用的原因！

注意：innodb行鎖是透過給索引上的索引項加鎖實現的。這意味著：只有透過索引條件檢索資料，innodb才使用行級鎖，

否則，innodb將使用表鎖！

42.檢視mysql伺服器引數的詳細定義

mysqld --verbose --help|more

比如想要知道當前資料庫字符集的設定，如下：

mysqld --verbose --help|grep character-set-server

43.二進位制日誌

檢視二進位制日誌狀態

show variables like '%log_bin%';

檢視當前伺服器所有的二進位制日誌檔案

show binary logs;

show master logs;

檢視當前二進位制日誌檔案狀態

show master status;

切換二進位制日誌

flush logs;

44.查詢mysql資料庫中的當前時間

select now();

45.在 mysql資料庫中查詢作業系統當前的時間

system date;

46.檢視資料庫字符集檢視MYSQL資料庫伺服器和資料庫字符集

show variables like '%character%';

檢視MYSQL所支援的字符集

show charset;

檢視庫的字符集

show create database test72\G;

檢視錶的字符集

show table status from test72 like 'zs'\G;

檢視錶中所有列的字符集

show full columns from 表名;

show full columns from zs\G;

47.顯示mysql中所有資料庫的名稱

show databases;

48.顯示一個使用者的許可權，顯示結果類似於grant 命令

show grants for user_name;

49.顯示伺服器所支援的不同許可權

show privileges;

50.顯示create database 語句是否能夠建立指定的資料庫

show create database database_name;

51.顯示安裝以後可用的儲存引擎和預設引擎

show engines;

52.顯示innoDB儲存引擎的狀態

show engine innodb status\G;

53.統計每個庫大小

select TABLE_SCHEMA,SUM(DATA_LENGTH)/1024/1024/1024 as data_length,SUM(INDEX_LENGTH)/1024/1024/10

24 as index_length,SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 as sum_data_index from informatio

n_schema.tables where TABLE_SCHEMA!='information_schema' and TABLE_SCHEMA!='mysql' group by TABL

E_SCHEMA;

54.統計庫中每個表大小，此以test庫為例

select TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024/102

4 as TOTAL_SIZE from information_schema.tables where TABLE_SCHEMA='test' group by TABLE_SCHEMA;

55.統計所有資料庫大小

select SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 as sum_data_index from information_schema.tab

les;

mysql效能監控相關

相關文章