第一次嘗試 把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參數的話，效果等同於sessionhttps://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參數的話，效果等同於sessionProcesslist
查看伺服器內執行緒
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST; # 若未加上FULL則僅顯示前100個字元的infohttps://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.cnfhttps://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
沒有留言:
張貼留言