-- 查看當(dāng)前配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 動(dòng)態(tài)開啟（無(wú)需重啟，但重啟后失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;  -- 超過(guò) 1 秒視為慢查詢

-- 記錄未使用索引的查詢（強(qiáng)烈建議開啟）
SET GLOBAL log_queries_not_using_indexes = 'ON';

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_output = FILE  -- 或 TABLE，記錄到 mysql.slow_log 表

-- 開啟 Statement 監(jiān)控
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' WHERE NAME LIKE '%statements%';

-- 查看耗時(shí) TOP 10 的 SQL
SELECT 
    DIGEST_TEXT AS query,
    COUNT_STAR AS exec_count,
    ROUND(SUM_TIMER_WAIT/1000000000000, 2) AS total_latency_sec,
    ROUND(AVG_TIMER_WAIT/1000000000000, 4) AS avg_latency_sec,
    ROUND(MAX_TIMER_WAIT/1000000000000, 4) AS max_latency_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查看全表掃描次數(shù)最多的 SQL
SELECT * FROM sys.statements_with_full_table_scans 
ORDER BY rows_examined DESC LIMIT 10;

-- 查看執(zhí)行次數(shù)最多且平均耗時(shí)高的 SQL
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

-- 查看使用臨時(shí)表和文件排序的 SQL
SELECT * FROM sys.statements_with_sorting 
ORDER BY rows_sorted DESC LIMIT 10;

# Ubuntu/Debian
apt-get install percona-toolkit
# CentOS/RHEL
yum install percona-toolkit
# 或下載源碼包
wget https://www.percona.com/downloads/percona-toolkit/3.5.5/binary/tarball/percona-toolkit-3.5.5_x86_64.tar.gz

# 分析慢查詢?nèi)罩?，輸出到文?
pt-query-digest /var/log/mysql/slow.log > slow_report.txt
# 只顯示前 20 條（默認(rèn)按總耗時(shí)排序）
pt-query-digest --limit 20 /var/log/mysql/slow.log
# 過(guò)濾只分析最近 1 小時(shí)的日志
pt-query-digest --since "1h" /var/log/mysql/slow.log
# 分析特定數(shù)據(jù)庫(kù)的慢查詢
pt-query-digest --filter '$event->{db} eq "mydb"' /var/log/mysql/slow.log
# 直接分析 Processlist（實(shí)時(shí)分析）
pt-query-digest --processlist h=localhost,u=root,p=password

-- 案例：檢查一個(gè)慢查詢的執(zhí)行計(jì)劃
EXPLAIN ANALYZE
SELECT o.*, u.name 
FROM orders o 
JOIN user u ON o.user_id = u.id 
WHERE o.status = 'pending' 
  AND o.create_time > '2024-01-01'
ORDER BY o.create_time DESC 
LIMIT 100;

-- 原始慢查詢 (平均 2.3s)
SELECT * FROM orders 
WHERE user_id = 12345 
  AND status = 'completed' 
ORDER BY create_time DESC 
LIMIT 10;

EXPLAIN SELECT * FROM orders WHERE user_id = 12345 AND status = 'completed' ...
-- 結(jié)果: type=ref, key=idx_user_id, rows=15000, Extra=Using where; Using filesort

-- 創(chuàng)建覆蓋索引，包含 WHERE、ORDER BY、SELECT 需要的所有列
CREATE INDEX idx_user_status_time 
ON orders(user_id, status, create_time, order_no, amount, pay_time);

-- 改寫查詢，只取必要字段（利用覆蓋索引）
SELECT order_no, amount, status, create_time, pay_time 
FROM orders 
WHERE user_id = 12345 
  AND status = 'completed' 
ORDER BY create_time DESC 
LIMIT 10;

type=ref, key=idx_user_status_time, rows=120, Extra=Using index

-- 原始慢查詢 (平均 4.8s)
SELECT * FROM orders 
WHERE status = 'completed' 
ORDER BY create_time DESC 
LIMIT 100000, 10;

type=index, key=idx_status_time, rows=100010, Extra=Using where

-- 先查主鍵，再回表取數(shù)據(jù)
SELECT o.* 
FROM orders o
JOIN (
    SELECT id 
    FROM orders 
    WHERE status = 'completed' 
    ORDER BY create_time DESC 
    LIMIT 100000, 10
) tmp ON o.id = tmp.id;

-- 上一頁(yè)最后一條記錄的 create_time 為 '2024-03-15 14:30:00'
SELECT * FROM orders 
WHERE status = 'completed' 
  AND create_time < '2024-03-15 14:30:00'
ORDER BY create_time DESC 
LIMIT 10;

-- 表結(jié)構(gòu)
CREATE TABLE user (
    id INT PRIMARY KEY,
    phone VARCHAR(20),  -- 注意是 VARCHAR
    INDEX idx_phone (phone)
);

-- 原始查詢（Java 代碼傳入 long 類型）
SELECT * FROM user WHERE phone = 13800138000;

EXPLAIN SELECT * FROM user WHERE phone = 13800138000;
-- 結(jié)果: type=ALL, key=NULL, rows=5000000

// 修改 Java 代碼，確保傳入字符串
String phone = "13800138000";  // 原來(lái)是 Long phone = 13800138000L;
jdbcTemplate.query("SELECT * FROM user WHERE phone = ?", phone);

-- 原始慢查詢 (平均 1.5s)
SELECT * FROM orders 
WHERE order_no = 'ORD2024001' 
   OR user_phone = '13800138000';

type=ALL, key=NULL, rows=10000000

-- 優(yōu)化后查詢 (平均 15ms)
SELECT * FROM orders WHERE order_no = 'ORD2024001'
UNION ALL
SELECT * FROM orders WHERE user_phone = '13800138000' 
  AND order_no <> 'ORD2024001';  -- 避免重復(fù)

-- 原始慢查詢 (平均 800ms，并發(fā)高時(shí)更慢)
SELECT 
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount
FROM orders 
WHERE create_time >= CURDATE();

type=range, key=idx_create_time, rows=50000, Extra=Using index condition

-- 創(chuàng)建覆蓋索引（只包含查詢需要的列）
CREATE INDEX idx_create_time_amount 
ON orders(create_time, amount);

-- 查詢變?yōu)楦采w索引掃描
SELECT COUNT(*), SUM(amount), AVG(amount)
FROM orders 
WHERE create_time >= CURDATE();

-- 創(chuàng)建統(tǒng)計(jì)表
CREATE TABLE order_daily_stats (
    stat_date DATE PRIMARY KEY,
    order_count INT,
    total_amount DECIMAL(18,2),
    avg_amount DECIMAL(18,2),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 通過(guò)定時(shí)任務(wù)或觸發(fā)器更新（每 5 分鐘）
INSERT INTO order_daily_stats (stat_date, order_count, total_amount, avg_amount)
SELECT CURDATE(), COUNT(*), SUM(amount), AVG(amount)
FROM orders 
WHERE create_time >= CURDATE()
ON DUPLICATE KEY UPDATE
    order_count = VALUES(order_count),
    total_amount = VALUES(total_amount),
    avg_amount = VALUES(avg_amount);

-- 查詢變?yōu)楹撩爰?jí)
SELECT * FROM order_daily_stats WHERE stat_date = CURDATE();

-- 優(yōu)化前保存執(zhí)行計(jì)劃
EXPLAIN SELECT ... \G  -- 保存輸出

-- 優(yōu)化后對(duì)比
EXPLAIN SELECT ... \G  -- 確認(rèn) type、rows、Extra 改善

# 使用 mysqlslap 壓測(cè)
mysqlslap --concurrency=50 --iterations=10 \
  --query="SELECT order_no, amount FROM orders WHERE user_id = 12345 AND status = 'completed' ORDER BY create_time DESC LIMIT 10" \
  --create-schema=mydb --delimiter=";" \
  --engine=innodb --number-of-queries=1000

# 或使用 sysbench
sysbench oltp_read_only --mysql-host=localhost --mysql-user=root \
  --mysql-password=xxx --mysql-db=mydb --tables=10 --table-size=1000000 \
  --threads=64 --time=60 --report-interval=10 run

-- 使用 MySQL 8.0 的 Query Rewrite 插件，先對(duì)部分流量生效
INSTALL PLUGIN query_rewrite SONAME 'rewriter.so';

-- 添加重寫規(guī)則（先對(duì) 10% 流量生效，驗(yàn)證無(wú)誤后再全量）
INSERT INTO query_rewrite.rewrite_rules (pattern, replacement, enabled)
VALUES (
    'SELECT * FROM orders WHERE user_id = ? AND status = ? ORDER BY create_time DESC LIMIT ?',
    'SELECT order_no, amount, status, create_time, pay_time FROM orders WHERE user_id = ? AND status = ? ORDER BY create_time DESC LIMIT ?',
    'YES'
);
CALL query_rewrite.flush_rewrite_rules();

# docker-compose.yml
version: '3'
services:
  prometheus:
    image: prom/prometheus
    ports:
      - "9090:9090"
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml
  mysqld_exporter:
    image: prom/mysqld-exporter
    environment:
      - DATA_SOURCE_NAME=root:password@(mysql:3306)/
    ports:
      - "9104:9104"
  grafana:
    image: grafana/grafana
    ports:
      - "3000:3000"

#!/bin/bash
# slow_query_check.sh - 每日慢查詢巡檢
LOG_FILE="/var/log/mysql/slow.log"
REPORT_FILE="/tmp/slow_report_$(date +%Y%m%d).txt"
THRESHOLD=10  # 慢查詢數(shù)量閾值
# 生成報(bào)告
pt-query-digest --limit 20 $LOG_FILE > $REPORT_FILE
# 提取 TOP 1 的耗時(shí)占比
TOP_PCT=$(grep -A 1 "Rank Query ID" $REPORT_FILE | head -3 | tail -1 | awk '{print $3}')
# 發(fā)送告警
if [ $(echo "$TOP_PCT > $THRESHOLD" | bc) -eq 1 ]; then
    curl -X POST "https://oapi.dingtalk.com/robot/send?access_token=xxx" \
      -H "Content-Type: application/json" \
      -d "{"msgtype": "markdown", "markdown": {"title": "MySQL慢查詢告警", "text": "### MySQL 慢查詢告警\nTOP 1 慢查詢占比: ${TOP_PCT}%\n請(qǐng)查看報(bào)告: ${REPORT_FILE}"}}"
fi
# 歸檔日志（可選）
mv $LOG_FILE /var/log/mysql/slow_$(date +%Y%m%d).log
mysqladmin -uroot -p flush-logs slow