【XAMPP】【Database】進階資料庫使用設定:資料庫存取速率上限、取得資料庫狀態、安裝外掛等

第一次嘗試 把markdown檔案轉到Blogger

發現<code>區塊都會變成一整行block元素 有點亂糟糟的排版 0.0

以下箭頭可折疊起來,閱讀上比較方便

Heptabase好讀版


設置指令速率上限

在資料庫中,可以對帳號進行資源限制,限制該帳號每小時的執行次數

  • 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伺服器才會生效)

https://dev.mysql.com/doc/refman/8.0/en/flush.html

取得伺服器使用狀態

在這個體系中分成兩種狀態,分別為:

  • 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

https://dev.mysql.com/doc/refman/8.0/en/show-variables.html

Processlist

查看伺服器內執行緒

SHOW PROCESSLIST;
SHOW FULL PROCESSLIST; # 若未加上FULL則僅顯示前100個字元的info

https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html

https://mariadb.com/kb/en/show-processlist/

所有的Show語句

基礎語句

SHOW DATABASES;
SHOW TABLES;
SHOW PLUGINS;
SHOW ENGINES;
SHOW PRIVILEGES;

若想知道還有什麼其他SHOW能接的語法,可參考此處

https://dev.mysql.com/doc/refman/8.0/en/show.html

https://mariadb.com/kb/en/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

https://blog.csdn.net/java_w/article/details/103554447

sys schema (TODO 沒研究完)

在XAMPP上(目前mariadb10),需下載手動安裝sys schema

https://github.com/FromDual/mariadb-sys

區塊變數、使用者定義變數、全域變數

在這個體系中分成三種狀態,分別為:

變數分成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;


https://dev.mysql.com/doc/refman/8.0/en/user-variables.html

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

安裝插件的方式

沒有留言:

張貼留言