第一次嘗試 把markdown檔案轉到Blogger
發現<code>區塊都會變成一整行block元素 有點亂糟糟的排版 0.0
以下箭頭可折疊起來,閱讀上比較方便
設置指令速率上限
在資料庫中,可以對帳號進行資源限制,限制該帳號每小時的執行次數
MAX QUERIES PER HOUR
:限制使用者每小時可發送到伺服器的查詢數。
MAX UPDATES PER HOUR
:限制使用者每小時可執行修改資料表或資料庫的指令數。
MAX CONNECTIONS PER HOUR
:限制使用者每小時可開啟的新連線數。
MAX USER_CONNECTIONS
:限制使用者同一時間的連線數。
https://dev.mysql.com/doc/refman/8.0/en/user-resources.html
使用以下語法可以查看每個帳號資源的限制
SELECT CONCAT('\'', user, '\'', '@', '\'', host, '\'') account,
max_questions,
max_updates,
max_connections,
max_user_connections
FROM mysql.user;
重置使用次數
使用SQL語法讓該帳戶的最近一小時使用次數歸零(只有清除最近一小時,而非整體計數歸零)
FLUSH USER_RESOURCES;
但該帳號必須擁有RELOAD
的權限
否則會出現Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
的錯誤訊息
(更新帳號權限時,儲存後要重開SQL伺服器才會生效)
取得伺服器使用狀態
在這個體系中分成兩種狀態,分別為:
GLOBAL
系統全域變數
SESSION
(在此與LOCAL
同義詞)當前連線狀態- 若同時存在global與session,優先調用session
- 若session變數中不存在,則會返回global的變數
- 所以查看到的session變數數量會比global還多,因為global有的session都有
Status
SHOW SESSION STATUS; # 當前連線的狀態值。如果沒有區域值則顯示全域。
SHOW GLOBAL STATUS; # 自mysqld啟動以來全局的狀態
SHOW STATUS; # 沒有global參數的話,效果等同於session
https://dev.mysql.com/doc/refman/8.0/en/server-status-variable-reference.html
https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html
https://mariadb.com/kb/en/show-status/
取得自上一次伺服器啟動以來,該連線或者全域的”查看、新增、更新、刪除”次數
(Com代表Command的意思)
SHOW SESSION STATUS LIKE "Com_select";
SHOW SESSION STATUS LIKE 'Com_insert';
SHOW SESSION STATUS LIKE "Com_update";
SHOW SESSION STATUS LIKE "Com_delete";
SHOW GLOBAL STATUS LIKE "Com_select";
SHOW GLOBAL STATUS LIKE 'Com_insert';
SHOW GLOBAL STATUS LIKE "Com_update";
SHOW GLOBAL STATUS LIKE "Com_delete";
SHOW GLOBAL STATUS LIKE "Com_%"; # 或可執行這行模糊對比
有人創立了這個語法,可以供觀看每小時的增刪改查狀態
USE YOUR_DATABASE; CREATE OR REPLACE VIEW _dba_query_stats ASSELECT SUBSTRING(VARIABLE_NAME, 5) AS query_type, VARIABLE_VALUE AS total_count, round(VARIABLE_VALUE / ( SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Uptime_since_flush_status'), 2) AS per_second, round(VARIABLE_VALUE / ((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Uptime_since_flush_status') / (60))) AS per_minute, round(VARIABLE_VALUE / ((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Uptime_since_flush_status') / (60*60))) AS per_hour, round(VARIABLE_VALUE / ((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Uptime_since_flush_status') / (60*60*24))) AS per_day, FROM_UNIXTIME(round(UNIX_TIMESTAMP(sysdate()) - (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Uptime_since_flush_status'))) report_period_start, sysdate() AS report_period_end, TIME_FORMAT(SEC_TO_TIME((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Uptime_since_flush_status')),'%Hh %im') AS report_period_duration FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME IN ('Com_select', 'Com_delete', 'Com_update', 'Com_insert'); SELECT * FROM _dba_query_stats; # 觀看
Variables
SHOW GLOBAL STATUS;
SHOW SESSION STATUS;
SHOW VARIABLES; # 沒有global參數的話,效果等同於session
Processlist
查看伺服器內執行緒
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST; # 若未加上FULL則僅顯示前100個字元的info
https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html
所有的Show語句
基礎語句
SHOW DATABASES;
SHOW TABLES;
SHOW PLUGINS;
SHOW ENGINES;
SHOW PRIVILEGES;
若想知道還有什麼其他SHOW能接的語法,可參考此處
information_schema
可以獲取到如上述的status、各種variables、processlist等
資料都是存在這個表之中
SELECT * FROM information_schema.global_status;
SELECT * FROM information_schema.session_status;
SELECT * FROM information_schema.global_variables;
SELECT * FROM information_schema.session_variables;
SELECT * FROM information_schema.user_variables;
SELECT * FROM information_schema.processlist;
比較特殊的是一些需要開啟plugins才能取得到的參數,例如USER_STATISTICS
啟用方式有兩種:
- 動態更改數值:用
SET
指令
- 變更伺服器預設值:更改設定檔
my.ini
,則參考下面啟用插件的方式
章節。
SET GLOBAL userstat = ON; # 啟用userstat plugin。連線關閉後效果仍會持續,除非重啟MySQL伺服器
SELECT * FROM information_schema.USER_STATISTICS; # 執行這行前先確保啟用 userstat plugin
不論區域變數、全域變數,一旦重啟伺服器就會讓這些變數重置設定回預設值。
如果沒啟用到話,會撈到一片空白的表
https://dev.mysql.com/doc/refman/8.0/en/information-schema-introduction.html
performance_schema
也需要啟用設定,但這個更特別一點,沒辦法透過SET
來動態更改設定,會出現Variable 'performance_schema' is a read only variable
的錯誤訊息
啟用套件的唯一方法只能更改my.ini
[mysqld]
performance_schema = 1
取得伺服器的一些連線狀況
SELECT * FROM performance_schema.accounts;
SELECT * FROM performance_schema.users;
SELECT * FROM performance_schema.hosts;
SELECT * FROM performance_schema.events_statements_summary_by_digest;
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-startup-configuration.html
profiling
剖析執行時間
也需要啟用設定,先注意,要把PROFILING_HISTORY_SIZE
大小調整大一點
不然只有1
的話會永遠只出現SELECT DATABASE()
或者SELECT @@tx_isolation
的結果,會以為profiling沒有生效。
SHOW VARIABLES LIKE '%profiling%'; # 查看profiling相關設定
可以動態設定
SET profiling = 1;
SET PROFILING_HISTORY_SIZE = 50;
SELECT SLEEP(2);
SHOW PROFILES; # 查詢多筆
(如果關閉自動提交autocommit的話,可以用TRANSACTION
包起來)
START TRANSACTION;
SET profiling = 1;
SET profiling_history_size = 50;
SELECT SLEEP(2);
SHOW PROFILES;
COMMIT;
查詢特定Query_ID
SHOW PROFILE FOR QUERY 7;
顯示所有花費訊息
SHOW PROFILE ALL;
SHOW PROFILE ALL FOR QUERY 7;
此方法將被棄用,未來會透過Performance Schema
來進行替代
https://dev.mysql.com/doc/refman/8.0/en/show-profile.html
general_log、slow_query_log、error_log
日誌輸出格式
log_output
分為兩種:FILE
:將輸出存成log檔案,存到general_log_file
指定的位置
TABLE
:設置為table的話,log輸出會到mysql.general_log
表、mysql.slow_log
表,就不會輸出到檔案了
可以進行動態設定
SET GLOBAL log_output = 'FILE'; # 或 TABLE
也可在
my.ini
中進行設定[mariadb] log_output = FILE
general_log
每一次執行的SQL語句,不論增刪改查全部都會被詳細地記錄下來
因為會造成效能影響,在上線產品production環境強烈建議關閉。
查看general_log設定狀態
SHOW VARIABLES LIKE 'general_log%'; SHOW VARIABLES LIKE 'log%';
可以進行動態設定
SET GLOBAL general_log = 'ON'; SET GLOBAL general_log_file = 'general.log'; # 輸出log檔案位置
也可在
my.ini
中進行設定[mariadb] general_log = 1general_log_file = "general.log"
slow_query_log
用於記錄執行時間較長的 SQL
查詢,使用方式與general_log
雷同可以進行動態設定
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL slow_query_log_file = 'slow.log'; SET GLOBAL long_query_time = 0.5; # 超過0.5秒的查詢會被記錄到慢查詢日誌中
也可在
my.ini
中進行設定[mariadb] slow_query_log = 1slow_query_log_file = "slow.log"long_query_time = 0.5
error_log
log_error
無法被設定,log名稱只能是mysql_error.log
自動提交 autocommit
是否開啟自動提交模式,預設為開啟
可以進行動態設定,但更新後當前的會話不會生效,需要開啟新的連線。
SET autocommit = 0;
SELECT @@autocommit;
也可在my.ini
中進行設定
[mariadb]
autocommit = 0
關閉自動提交功能後,所有的SQL語句都需要手動加上COMMIT
才會生效
DELETE FROM ... WHERE ...;
COMMIT;
也可以加上TRANSACTION區塊
START TRANSACTION;
DELETE FROM ...
COMMIT;
BEGIN;
交易隔離 tx_isolation (TODO 沒研究完)
事務隔離級別,分為四種
REPEATABLE-READ
預設
READ-COMMITTED
READ-UNCOMMITTED
SERIALIZABLE
查看目前交易隔離級別
SHOW VARIABLES LIKE 'tx_isolation';
動態更改交易隔離級別
SET tx_isolation = 'READ-COMMITTED';
SET tx_isolation = 'REPEATABLE-READ';
SET tx_isolation = 'SERIALIZABLE';
SET tx_isolation = 'READ-UNCOMMITTED';
也可在my.ini
中進行設定
[mariadb]
transaction_isolation = 'REPEATABLE-READ'
https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html
sys schema (TODO 沒研究完)
在XAMPP上(目前mariadb10),需下載手動安裝sys schema
區塊變數、使用者定義變數、全域變數
在這個體系中分成三種狀態,分別為:
變數分成GLOBAL
系統整體、SESSION
當前連線、LOCAL
區域變數
三種區塊
- 區域變數
local
:。區域變數作用只存在於該SQL語句、當前事務(transaction)區塊。
- 使用者定義變數
session
:會話變數只在當前的會話中有效,當連線結束後變數會被清除。
- 使用系統變數
global
:在不同連線中也享有共同的值,在整個伺服器中是全域的。
這三種變數體系都是互相獨立存在的,不會有誰覆蓋誰的問題。
Local (Stored Procedure) 區塊變數
要使用區域變數只能放在BEGIN、END裡面,也就是需要創造個預存程序(有點類似於Function,但實際上則不同),變數只存活於這個區塊裡面。
先定義好Procedure後執行,這段程式碼將儲存在資料庫裡
注意,DECLARE聲明區塊與一定要放在區塊的頭部,需放在任何操作之前
CREATE PROCEDURE MyProcedure()
BEGIN # 聲明區塊
DECLARE MyVariable1 INT DEFAULT 123;
DECLARE MyVariable2 INT;
DECLARE MyVariable3 VARCHAR(255) DEFAULT 'Hello, World!';
# 設定變數區塊
SET MyVariable2 = 456;
SELECT MyVariable1, MyVariable2, MyVariable3;
END;
需先定義好型別,如果沒有DEFAULT則會是NULL,也可以後來再用SET賦值
這段程式碼會存在於DB的”程序”底下
欲使用時,直接呼叫該程序
每次呼叫都會執行這整個區塊
CALL MyProcedure();
如果想要刪除變數
無法用DELETE或DROP的方式刪除變數,只能在區塊中將該變數設置為NULL
SET MyVariable1 = NULL;
如果要刪除Procedure
DROP PROCEDURE MyProcedure;
https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html
https://dev.mysql.com/doc/refman/8.0/en/declare-local-variable.html
https://dev.mysql.com/doc/refman/8.0/en/local-variable-scope.html
Session (User-Defined Variables) 使用者定義變數@
使用者自訂變數(User-defined
variables),需要加上@
前綴符號
注意在SQL中,變數是沒有大小寫之分的
SET @MyVariable = 123;
SET @MyVariable := 123; # 使用 := 也可以
SELECT @MyVariable; # 查看變數
SELECT * ... WHERE VALUE = @MyVariable; # 使用變數來做查詢
一次宣告多個變數
SET @MyVariable1 = 'aaa', @MyVariable2 = 'bbb';
變數會在執行完畢(連線結束)後自動刪除
斷開連線後,可以再執行一次看看
SELECT @MyVariable;
這時候就會呈現Null
這些自訂變數是儲存在information_scheme table中
SELECT * FROM information_schema.USER_VARIABLES;
Global (Using System Variables) 系統全域變數@@
取得系統全域變數
SELECT @@userstat; # 或者 SELECT @@global.userstat;
設定全域變數
# SET @@userstat = 0; # 此行無法設定,會出現 Variable is a GLOBAL variable and should be set with SET GLOBAL 的錯誤
SET @@global.userstat = 1; # 一定得用 global 的方式來指定
SET GLOBAL userstat = 0;
\G
用在什麼時候
\G
符號無法在Query Console中使用
\G
符號只能在Cli命令列中使用
目的是讓輸出變成垂直顯示
SHOW PROCESSLIST \G
有\G
句尾就不用接分號
對比沒有/G
以表格方式顯示
SHOW PROCESSLIST;
如何在XAMPP開啟mysql CLI
開啟控制面板,點擊Shell
接著輸入mysql.exe -u 帳戶名稱 -p
等下一行出現再輸入密碼
預設是
mysql -u root
如果要檢查資料庫版本
開啟XAMPP Shell
mysql --version
或者登入進到mysql cli之後
SHOW VARIABLES LIKE "%version";
資料庫設定檔位置
在Shell中輸入mysql --help
其中這一段就是讀取設定檔案的順序
C:\WINDOWS\my.ini
C:\WINDOWS\my.cnf
C:\my.ini
C:\my.cnf
C:\xampp\mysql\my.ini
C:\xampp\mysql\my.cnf
C:\xampp\mysql\data\my.ini
C:\xampp\mysql\data\my.cnf
C:\xampp\mysql\bin\my.ini
C:\xampp\mysql\bin\my.cnf
https://dev.mysql.com/doc/refman/8.0/en/option-files.html
XAMPP預設my.ini
預設路徑會在C:\xampp\mysql\bin\my.ini
可以直接在這份中做設定
不過如果要更好控管全域區域,不想更動到原始文件的話
也可以按照以下說明,複製整份文件到其他地方
啟用插件的方式
以安裝用戶統計userstat
為例
直接在my.ini
中添加
[mariadb]
userstat = 1 # 1 或 ON都行
或者使用mysqld也行
[mysqld]
userstat = 1
然後重啟伺服器,接著執行SQL語句
SELECT * FROM INFORMATION_SCHEMA.USER_STATISTICS;
就可以看到統計數據了(要記得以有權限的帳號執行,否則會撈到空白的表)
https://mariadb.com/kb/en/user-statistics/#enabling-the-plugin
沒有留言:
張貼留言