1 數(shù)據(jù)庫調(diào)優(yōu)維度

• 業(yè)務(wù)需求：勇敢地對不合理的需求說不
• 系統(tǒng)架構(gòu)：做架構(gòu)設(shè)計(jì)的時候，應(yīng)充分考慮業(yè)務(wù)的實(shí)際情況，考慮好數(shù)據(jù)庫的各種選擇(讀寫分離?高可用?實(shí)例個數(shù)?分庫分表?用什么數(shù)據(jù)庫?)
• SQL及索引：根據(jù)需求編寫良好的SQL，并去創(chuàng)建足夠高效的索引
• 表結(jié)構(gòu)：設(shè)計(jì)良好的表結(jié)構(gòu)
• 數(shù)據(jù)庫參數(shù)設(shè)置：設(shè)置合理的數(shù)據(jù)庫性能參數(shù)(join buffer、sort buffer…)
• 系統(tǒng)配置：操作系統(tǒng)提供了各種資源使用策略，設(shè)置合理的配置，以便于數(shù)據(jù)庫充分利用資源(swap應(yīng)盡可能小 -> swappiness)
• 硬件：SSD or 機(jī)械硬盤

2 查詢?nèi)罩?/h2>

2.1 所有SQL執(zhí)行日志

-- 開啟查看所有查詢?nèi)罩?#xff0c;使用后立即關(guān)閉
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log = 'OFF';
-- 查看慢查詢?nèi)罩韭窂?/span>
show variables like '%general_log%';

2.2 慢查詢?nèi)罩?/h3>

2.2.1 開啟日志

• 方式一：修改配置文件my.cnf，在[mysqld]段落中加入如上參數(shù)開啟，需要重啟MySQL

# 開啟慢查詢?nèi)罩?/span>
[mysqld]
slow_query_log = ON
log_output = 'FILE,TABLE'
long_query_time = 2
# 重啟MySQL
service mysqld restart

• 方式二：通過全局變量設(shè)置，這種方式無需重啟即可生效，但一旦重啟，配置又會丟失

# 開啟慢查詢?nèi)罩?/span>
set global slow_query_log = 'ON';
# 修改多慢算慢查詢的定義long_query_time，需要切換session才能生效
set global long_query_time = 2;
# 將慢查詢?nèi)罩就瑫r記錄到文件以及mysql.slow_log表中
set global log_output = 'FILE,TABLE';

2.2.1 查看與分析慢查詢?nèi)罩?/h4>

-- 查看TABLE中的記錄
select * from mysql.slow_log;
-- 查看slow FILE文件，查看slow file路徑，然后查看文本文件
show variables like '%slow_query_log_file%';

# 分析慢查詢?nèi)罩疚募ぞ?#xff1a;mysqldumpslow
mysqldumpslow -s r -t 10 -a /var/lib/mysql/node3-26-slow.log
# 分析慢查詢?nèi)罩疚募ぞ?#xff1a;pt-query-digest
pt-query-digest mysql-slow-2022-01-07.log > 0107.report

pt-query-digest工具官網(wǎng)

3 SQL性能分析

• EXPLAIN：id越大越先執(zhí)行，相同的id則上面的先執(zhí)行，可視化分析可以使用：IDEA：Explain plan，MysqlWorkBench，show warnings; 用于展示分析結(jié)果
• SHOW PROFILE: 簡單、方便，已廢棄
• INFORMATION_SCHEMA.PROFILING: 和SHOW PROFILE本質(zhì)一樣
• PERFORMANCE_SCHEMA: 未來之光，但目前來說使用不夠方便
• OPTIMIZER_TRACE：跟蹤優(yōu)化器做出的各種決策、了解優(yōu)化器的執(zhí)行細(xì)節(jié)、理解SQL的執(zhí)行過程，進(jìn)而優(yōu)化SQL

4 數(shù)據(jù)庫診斷

-- 查看當(dāng)前正在運(yùn)行的進(jìn)程列表
SHOW FULL PROCESSLIST;
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;-- 按照客戶端IP分組,看哪個客戶端的連接數(shù)最多
select client_ip, count(client_ip) as client_num from (select substring_index ( host, ':', 1 ) as client_ip from information_schema.processlist) as connect_info group by client_ip order by client_num desc;
-- 查看正在執(zhí)行的線程，并按time倒排序，看看有沒有執(zhí)行時間特別長的線程
select * from information_schema.processlist where command != 'sleep' order by Time desc limit 10\G
-- 找出所有執(zhí)行時間超過5分鐘的線程，拼湊出kill語句，方便后面查殺
select concat ('kill', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;-- 查看狀態(tài)
SHOW STATUS;show global status like '%slow%';
SHOW VARIABLES;SHOW VARIABLES like '%%';
SHOW TABLE STATUS;
SHOW INDEX FROM EMPLOYEES;
SHOW ENGINE INNODB STATUS\G
SHOW MASTER STATUS;
SHOW SLAVE STATUS;