應用層 → 主 MySQL（可寫）
         ↓
         binlog ────→ 從 MySQL 1（只讀）
         │            ↓
         │        從 MySQL 2（只讀）
         │
         └────→ 讀寫分離中間件（Mycat/Sharding-JDBC）
                 ↓
                 分片庫 1（用户 ID 0-50w）
                 分片庫 2（用户 ID 50w-100w）
                 分片庫 3（用户 ID 100w-150w）

# 1. 登錄 MySQL
mysql -u root -p -h localhost

# 2. 啓用慢查詢日誌（動態開啓，重啓不保留）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  # 1 秒以上查詢記錄
SET GLOBAL log_queries_not_using_indexes = 'ON';  # 不走索引的查詢也記錄

# 3. 查看慢查詢日誌位置
SHOW VARIABLES LIKE 'slow_query_log_file';

# 4. 從命令行查看慢查詢（最近 50 條）
tail -50 /var/log/mysql/slow.log

# 5. 使用 pt-query-digest 分析慢查詢（需安裝 percona-toolkit）
yum install -y percona-toolkit
pt-query-digest /var/log/mysql/slow.log | head -100

# 同上 MySQL 命令相同，日誌路徑可能不同
apt update && apt install -y percona-toolkit
tail -50 /var/log/mysql/mysql-slow.log
pt-query-digest /var/log/mysql/mysql-slow.log | head -100

# 檢查慢查詢日誌是否啓用
mysql -u root -p -e "SHOW VARIABLES LIKE 'slow_query_log';"
# 預期輸出：slow_query_log | OFF（默認關閉）

# 確認慢查詢日誌已啓用
mysql -u root -p -e "SHOW VARIABLES LIKE 'slow_query_log';"
# 預期輸出：slow_query_log | ON

# 生成示例慢查詢（等待 2 秒）
mysql -u root -p -e "SELECT SLEEP(2);"

# 檢查是否記錄到日誌
tail -5 /var/log/mysql/slow.log | grep "Query_time"
# 預期輸出：# Query_time: 2.000123  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0

原因：沒有 SUPER 權限，無法設置全局變量
修復：登錄時用 root 用户，或授予權限
grant SUPER on *.* to 'mysql_user'@'localhost';

原因：日誌目錄權限不足
修復：chown mysql:mysql /var/log/mysql && chmod 755 /var/log/mysql

# 關閉慢查詢日誌
SET GLOBAL slow_query_log = 'OFF';

mysql -u root -p << 'EOF'
# 查看錶的所有索引
SHOW INDEXES FROM users;
# 預期輸出列：Table, Non_unique, Key_name, Seq_in_index, Column_name, Index_type

# 查看錶統計信息（MySQL 8.0+）
SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='prod_db' AND TABLE_NAME='users';
EOF

mysql -u root -p << 'EOF'
# 添加聯合索引：(user_id, status, created_at)
# 順序很關鍵：WHERE 字段 → ORDER BY 字段 → 覆蓋字段
ALTER TABLE orders ADD INDEX idx_user_status_date (user_id, status, created_at);

# 驗證索引是否被使用
EXPLAIN FORMAT=JSON SELECT * FROM orders
WHERE user_id=100 AND status='paid'
ORDER BY created_at DESC LIMIT 10\G
EOF

mysql -u root -p << 'EOF'
# 檢查執行計劃
EXPLAIN SELECT * FROM orders
WHERE user_id=100 AND status='paid'
ORDER BY created_at DESC LIMIT 10;

# 關鍵字段解釋：
# - type: range/ref/eq_ref（越靠前越好，ALL 最差）
# - key: 實際使用的索引名
# - rows: 掃描行數（越少越好）
# - filtered: 使用索引後過濾率（>70% 較好）
EOF

# 安裝 percona-toolkit
yum install -y percona-toolkit

# 檢測重複索引
pt-duplicate-key-checker -h localhost -u root -p'password' | grep -A 5 "Duplicate"

原因：索引順序不對，WHERE 條件順序不匹配索引
修復：調整索引順序，或在 SQL 中重新排列 WHERE 條件

原因：長時間運行的查詢持有表鎖，新索引添加被阻塞
修復：
1. 查看長查詢：SHOW PROCESSLIST;
2. Kill 長查詢：KILL QUERY/CONNECTION <thread_id>;
3. 或在從庫執行 ALTER TABLE，再主從切換

# 使用條件索引，重複執行不會報錯
ALTER TABLE orders ADD INDEX IF NOT EXISTS idx_user_status_date (user_id, status, created_at);

ALTER TABLE orders DROP INDEX idx_user_status_date;

SELECT*FROM users u
WHERE user_id IN (
SELECTDISTINCT user_id FROM orders WHERE created_at > DATE_SUB(NOW(), INTERVAL7DAY)
);

SELECTDISTINCT u.*FROM users u
INNERJOIN orders o ON u.user_id = o.user_id
WHERE o.created_at > DATE_SUB(NOW(), INTERVAL7DAY);

mysql -u root -p << 'EOF'
# 慢查詢執行計劃（相關子查詢）
EXPLAIN FORMAT=JSON SELECT * FROM users u WHERE user_id IN (...)\G

# 快速查詢執行計劃（JOIN）
EXPLAIN FORMAT=JSON SELECT DISTINCT u.* FROM users u INNER JOIN orders o...\G
EOF

-- 獲取第 10000 頁（每頁 20 條）
SELECT*FROM users LIMIT 199980, 20;  -- 掃描 199980 行後取 20 行，極浪費

-- 假設上次查詢的最後一條記錄 user_id = 500
SELECT*FROM users WHERE user_id >500ORDERBY user_id LIMIT 20;

SELECT*FROM users;  -- 獲取 50 個字段，實際只需要 3 個

SELECT user_id, name, email FROM users;  -- 僅查詢需要的字段

# 查看錶的字段數
mysql -u root -p -e "SHOW COLUMNS FROM users;" | wc -l
# 預期輸出：實際字段數 +1（表頭）

mysql -u root -p << 'EOF'
# 對比兩個查詢的 rows 和 bytes 返回
EXPLAIN FORMAT=JSON SELECT * FROM users LIMIT 100\G
EXPLAIN FORMAT=JSON SELECT user_id, name, email FROM users LIMIT 100\G
EOF

mysql -u root -p << 'EOF'
# 查看緩衝池大小與使用率
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW STATUS LIKE 'Innodb_buffer_pool%'\G

# 關鍵指標：
# Innodb_buffer_pool_pages_data：數據頁數量
# Innodb_buffer_pool_pages_free：空閒頁數量
# 使用率 = (pages_data) / (pages_data + pages_free)，應該 > 95%
EOF

# 1. 編輯 MySQL 配置文件
vi /etc/my.cnf

# 2. 在 [mysqld] 段添加（假設服務器有 32GB 內存）
[mysqld]
innodb_buffer_pool_size = 24G  # 物理內存的 75%
innodb_buffer_pool_instances = 8  # 分 8 個實例，減少競爭

# 3. 重啓 MySQL 生效
systemctl restart mysqld

# 4. 驗證配置
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool%';"

# 配置文件路徑不同
vi /etc/mysql/mysql.conf.d/mysqld.cnf

# 或
vi /etc/mysql/percona-server.conf.d/mysqld.cnf

# 其他步驟相同

mysql -u root -p << 'EOF'
# 確認配置生效
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
# 預期輸出：24G（或相應值）

# 等待 5 分鐘，讓緩衝池預熱，再查看使用率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%'\G
# 如果 read_requests >> read_ahead_rnd，説明緩存命中率高
EOF

原因：緩衝池大小超過物理內存
修復：設置為物理內存的 70-80%，不要 100%（需要給 OS 留餘量）

原因：未啓用 innodb_buffer_pool_dump_at_shutdown
修復：SET GLOBAL innodb_buffer_pool_dump_at_shutdown = ON;

應用層
  ├─ 寫操作（INSERT/UPDATE/DELETE）→ 主庫 M（192.168.1.10）
  └─ 讀操作（SELECT）→ 從庫 S1（192.168.1.11）或 S2（192.168.1.12）

主庫 M ─binlog→ 從庫 S1 ─binlog→ 從庫 S2

# 1. 編輯主庫配置
vi /etc/my.cnf

# 2. 添加配置
[mysqld]
server-id = 10  # 唯一標識，主從不能相同
log_bin = mysql-bin  # 啓用 binlog
binlog_format = ROW  # 行級日誌（最安全）
sync_binlog = 1  # 每次提交都刷盤（性能損耗 10-20%，但可靠性最高）
innodb_flush_log_at_trx_commit = 1  # 事務立即刷盤

# 3. 重啓 MySQL
systemctl restart mysqld

# 4. 查看 binlog 狀態
mysql -u root -p -e "SHOW MASTER STATUS\G"

vi /etc/my.cnf

[mysqld]
server-id = 11  # 與主庫不同
relay-log = mysql-relay-bin  # 啓用 relay log
relay-log-index = mysql-relay-bin.index

systemctl restart mysqld

# 在主庫上創建複製賬户
mysql -u root -p << 'EOF'
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

# 查看當前 binlog 位置（從庫需要）
SHOW MASTER STATUS\G
# 記錄 File（如 mysql-bin.000003）和 Position（如 154）
EOF

# 在從庫上配置複製
mysql -u root -p << 'EOF'
CHANGE MASTER TO
  MASTER_HOST='192.168.1.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl_password',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=154;

# 啓動從庫複製
START SLAVE;

# 檢查複製狀態
SHOW SLAVE STATUS\G
# 關鍵字段：
# - Slave_IO_Running: Yes（IO 線程運行中）
# - Slave_SQL_Running: Yes（SQL 執行線程運行中）
# - Seconds_Behind_Master: 0（從庫延遲，0 表示同步）
EOF

# 在主庫寫入數據
mysql -u root -p << 'EOF'
USE test_db;
CREATE TABLE test_repl (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100));
INSERT INTO test_repl(name) VALUES('test-from-master');
EOF

# 在從庫查詢，驗證數據已複製
mysql -u root -p << 'EOF'
USE test_db;
SELECT * FROM test_repl;
# 預期輸出：看到主庫插入的數據
EOF

原因：主庫連接失敗，可能網絡、密碼、主機 IP 錯誤
修復：
1. 檢查網絡連通性：ping 主庫 IP
2. 驗證賬户密碼：mysql -u repl -p -h 192.168.1.10
3. 檢查防火牆：firewall-cmd --add-port=3306/tcp
4. 重新配置：CHANGE MASTER TO ...

原因：從庫 SQL 執行出錯，通常是數據不一致或 SQL 語句錯誤
修復：
1. 查看具體錯誤：SHOW SLAVE STATUS\G（Last_Error 字段）
2. 跳過錯誤：SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;
3. 或完全重新同步：RESET SLAVE; CHANGE MASTER TO ...

應用層
  ↓（分片鍵：user_id）
分片路由層（Mycat/Sharding-JDBC）
  ├─ 分片庫 db0（user_id % 4 = 0）→ 實例 1
  ├─ 分片庫 db1（user_id % 4 = 1）→ 實例 2
  ├─ 分片庫 db2（user_id % 4 = 2）→ 實例 3
  └─ 分片庫 db3（user_id % 4 = 3）→ 實例 4

# 在每個 MySQL 實例上執行

mysql -u root -p << 'EOF'
# 創建 4 個分片庫
CREATE DATABASE db0 CHARACTER SET utf8mb4;
CREATE DATABASE db1 CHARACTER SET utf8mb4;
CREATE DATABASE db2 CHARACTER SET utf8mb4;
CREATE DATABASE db3 CHARACTER SET utf8mb4;

# 在每個庫中創建分片表（以 db0 為例）
USE db0;
CREATE TABLE users_0 (
  user_id BIGINT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_email(email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 類似創建 users_1, users_2, users_3 表
EOF

# Python 示例
import mysql.connector

defget_shard_db(user_id, num_shards=4):
    shard_idx = user_id % num_shards
returnf"db{shard_idx}"

definsert_user(user_id, name, email):
    shard_db = get_shard_db(user_id)
    conn = mysql.connector.connect(
        host="192.168.1.10",  # 分片庫 IP
        user="root",
        password="password",
        database=shard_db
    )
    cursor = conn.cursor()
    cursor.execute(
f"INSERT INTO users_{user_id % 4} (user_id, name, email) VALUES (%s, %s, %s)",
        (user_id, name, email)
    )
    conn.commit()
    cursor.close()
    conn.close()

# 使用
insert_user(user_id=100123, name="張三", email="zhangsan@example.com")
# 自動路由到：db0（100123 % 4 = 3）

# 場景：查詢所有用户統計（跨所有分片）
# 需要在應用層或中間件合併結果

mysql -u root -p << 'EOF'
-- 分別查詢各分片庫
SELECT COUNT(*) as total FROM db0.users_0;  -- 結果 250w
SELECT COUNT(*) as total FROM db1.users_1;  -- 結果 250w
SELECT COUNT(*) as total FROM db2.users_2;  -- 結果 250w
SELECT COUNT(*) as total FROM db3.users_3;  -- 結果 250w
-- 應用層合計：1000w

-- 應用層偽代碼：
def count_all_users():
    total = 0
for shard_idx in range(4):
        result = query(f"SELECT COUNT(*) FROM db{shard_idx}.users_{shard_idx}")
        total += result[0]
return total
EOF

# 1. 查看 MySQL 進程與資源佔用
ps aux | grep mysqld | grep -v grep

# 2. 實時監控 MySQL 性能
mysql -u root -p << 'EOF'
-- 每 5 秒刷新一次
SHOW PROCESSLIST;  -- 查看運行中的查詢

-- 查看關鍵性能指標
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Questions';
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 計算 QPS（每秒查詢數）
-- 兩次執行間隔 10 秒，Questions 增量 / 10 = QPS
EOF

# 3. 查看 MySQL 日誌
tail -100 /var/log/mysql/error.log  # 錯誤日誌
tail -100 /var/log/mysql/slow.log   # 慢查詢日誌

groups:
-name:mysql_alerts
interval:10s
rules:
# 告警 1：慢查詢增多
-alert:MySQLSlowQueryHigh
expr:rate(mysql_global_status_slow_queries[5m])>1
for:5m
annotations:
summary:"MySQL 慢查詢速率過高（實例：{{ $labels.instance }}）"
description:"過去 5 分鐘平均每秒 {{ $value }} 條慢查詢"

# 告警 2：緩衝池使用率
-alert:MySQLBufferPoolLow
expr:|
          (mysql_innodb_buffer_pool_pages_data /
           (mysql_innodb_buffer_pool_pages_data + mysql_innodb_buffer_pool_pages_free)) < 0.5
for:10m
annotations:
summary:"MySQL InnoDB 緩衝池使用率 < 50%"

# 告警 3：主從複製延遲
-alert:MySQLReplicationLag
expr:mysql_slave_status_seconds_behind_master>10
for:5m
annotations:
summary:"MySQL 從庫複製延遲超過 10 秒"

# 告警 4：連接數接近上限
-alert:MySQLConnectionsHigh
expr:|
          (mysql_global_status_threads_connected /
           mysql_global_variables_max_connections) > 0.8
for:5m
annotations:
summary:"MySQL 連接數接近上限（{{ $value | humanizePercentage }}）"

# 安裝 sysbench（MySQL 壓力測試工具）
yum install -y sysbench

# 1. 準備測試數據（創建 1000w 行測試表）
sysbench /usr/share/sysbench/oltp_prepare.lua \
  --mysql-host=192.168.1.10 \
  --mysql-user=root \
  --mysql-password=password \
  --mysql-db=test \
  --table-size=10000000 \
  prepare

# 2. 執行讀寫混合測試（16 併發，持續 60 秒）
sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=192.168.1.10 \
  --mysql-user=root \
  --mysql-password=password \
  --mysql-db=test \
  --table-size=10000000 \
  --threads=16 \
  --time=60 \
  run

# 預期輸出（參考值）：
# transactions: 60000 (1000.00 per sec)
# read/write requests: 1200000 (20000.00 per sec)
# Latency (ms):
#          min:                                  0.48
#          avg:                                 16.00
#          max:                               200.00

第1步：問題現象
   ↓ 檢查 QPS/響應時間是否異常？
第2步：查看 PROCESSLIST
   ├─ 是否有 LOCK WAIT 或長時間運行的查詢？
   │  ├─ 是 → Kill 長查詢，查看索引
   │  └─ 否 → 下一步
   │
第3步：檢查磁盤與 I/O
   ├─ 磁盤是否滿？（df -h）
   ├─ I/O 是否飽和？（iostat -x 1）
   │  ├─ 是 → 優化查詢或升級硬件
   │  └─ 否 → 下一步
   │
第4步：檢查慢查詢日誌
   ├─ 是否存在全表掃描查詢？
   │  ├─ 是 → 添加索引
   │  └─ 否 → 檢查索引是否失效
   │
第5步：檢查主從複製
   ├─ 從庫是否延遲？
   │  ├─ 是 → 優化從庫慢查詢或增加資源
   │  └─ 否 → 檢查應用連接池配置

# 1. 在開發環境驗證索引效果（5 分鐘）
# 2. 在預發佈環境全量驗證（30 分鐘）
# 3. 在生產從庫執行 ALTER TABLE（可能需要 1-24 小時，取決於表大小）
# 4. 主從切換：從庫升為主庫
# 5. 在新從庫（原主庫）執行相同操作

# 以添加索引為例：

# 在從庫 S1 上執行（不影響主庫服務）
mysql -u root -p << 'EOF'
-- 停止從庫複製
STOP SLAVE;

-- 執行 ALTER TABLE（在從庫上可能需要數分鐘到數小時）
ALTER TABLE orders ADD INDEX idx_user_status_date (user_id, status, created_at);

-- 等待 ALTER 完成，驗證索引效果
EXPLAIN SELECT * FROM orders WHERE user_id=100 AND status='paid' ORDER BY created_at DESC;

-- 啓動從庫複製
START SLAVE;

-- 驗證從庫恢復同步
SHOW SLAVE STATUS\G;
EOF

# 等待從庫 Seconds_Behind_Master = 0，確保主從完全同步

# 執行主從切換（應用寫入切到 S1）
# Keepalived 自動切換 VIP，或應用手動切換連接字符串

# 在新從庫（原主庫）上執行相同操作
# 完成後，系統恢復全量服務

# 回滾觸發條件：
# 1. 索引添加後，新索引導致查詢變慢（可能優化器選錯索引）
# 2. 執行 ALTER TABLE 中斷，主從不同步
# 3. 索引佔用過多磁盤空間（>表大小的 50%）

# 回滾步驟：
mysql -u root -p << 'EOF'
-- 刪除不適合的索引
ALTER TABLE orders DROP INDEX idx_user_status_date;

-- 驗證查詢是否回到正常
EXPLAIN SELECT * FROM orders WHERE user_id=100;
EOF

-- 如果涉及主從切換，需要手動切回：
-- 應用連接字符串改回原主庫
-- 在原從庫（現主庫）執行 CHANGE MASTER TO，指向新主庫

-- 1. 等值條件 → 2. 範圍條件 → 3. 排序字段
ALTER TABLE orders ADD INDEX idx_opt (user_id, status, created_at);
-- user_id = ? AND status IN (...) ORDER BY created_at

-- 不走索引示例（SELECT 包含非索引字段）
SELECT user_id, name, amount FROM orders WHERE user_id =100;

-- 改為覆蓋索引
ALTER TABLE orders ADD INDEX idx_cover (user_id, amount, name);

-- 字符串字段超過 20 字符，使用前綴索引
ALTER TABLE users ADD INDEX idx_email (email(10));  -- 僅索引前 10 個字符

-- 錯誤：函數導致索引失效
SELECT*FROM users WHEREYEAR(created_at) =2024;

-- 正確：直接範圍比較
SELECT*FROM users WHERE created_at >='2024-01-01'AND created_at <'2025-01-01';

# 每月檢查一次重複索引
yum install -y percona-toolkit
pt-duplicate-key-checker -h localhost -u root -p'password'

-- 避免創建過多單列索引，優先使用聯合索引
-- 錯誤：3 個單列索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_amount ON orders(amount);

-- 正確：1 個聯合索引（3 個字段都能用上）
CREATE INDEX idx_combo ON orders(user_id, status, amount);

-- NOT NULL 默認，避免 NULL 值導致索引失效
ALTER TABLE users MODIFY COLUMN status VARCHAR(50) NOT NULLDEFAULT'active';

# 上線前必須檢查 type、rows、filtered
mysql -u root -p << 'EOF'
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 100;
EOF

-- 錯誤：user_id 是 BIGINT，但傳入字符串
SELECT*FROM users WHERE user_id ='100';  -- MySQL 自動轉換，索引失效

-- 正確：類型匹配
SELECT*FROM users WHERE user_id =100;

# 對於頻繁更新的大表，每季度重建一次
mysql -u root -p << 'EOF'
-- MySQL 5.7+：原地重建，不阻塞查詢
ALTER TABLE orders ENGINE=InnoDB;
EOF

-- 減少網絡傳輸，降低緩衝池壓力
SELECT user_id, name, email FROM users;

-- 錯誤：相關子查詢（N+1 問題）
SELECT*FROM users WHERE user_id IN (SELECTDISTINCT user_id FROM orders);

-- 正確：JOIN
SELECTDISTINCT u.*FROM users u INNERJOIN orders o ON u.user_id = o.user_id;

-- 錯誤：大偏移量掃描所有行
SELECT*FROM users LIMIT 100000, 20;

-- 正確：記錄上次 ID，快速定位
SELECT*FROM users WHERE user_id > :last_id ORDERBY user_id LIMIT 20;

# 錯誤：一次插入 100w 條，造成內存溢出、主從延遲
INSERT INTO users (...) VALUES (...), (...), ...;  -- 100w 條

# 正確：分批 1000 條一次
for batch in batches:
    INSERT INTO users (...) VALUES (...), ...;  -- 1000 條

-- 錯誤：多個 OR 導致全表掃描
SELECT*FROM users WHERE id =1OR id =2OR id =3;

-- 正確：用 IN
SELECT*FROM users WHERE id IN (1, 2, 3);

-- 錯誤：返回 100w 行，網絡傳輸 100MB
SELECT*FROM users;

-- 正確：前端分頁，一次僅返回 20 行
SELECT*FROM users LIMIT 0, 20;

-- 錯誤：前綴通配符，無法使用索引
SELECT*FROM users WHERE name LIKE'%zhang%';

-- 正確：使用後綴通配符，或用全文搜索
SELECT*FROM users WHERE name LIKE'zhang%';
-- 或：SELECT * FROM users WHERE MATCH(name) AGAINST('zhang');

-- 錯誤：UNION 去重需要排序，性能差
SELECT user_id FROM users UNIONSELECT user_id FROM orders;

-- 正確：確認無重複數據，用 UNION ALL
SELECT user_id FROM users UNIONALLSELECT user_id FROM orders;

# MySQL 8.0+ 自動更新，5.7 需要手動
mysql -u root -p << 'EOF'
ANALYZE TABLE users;
ANALYZE TABLE orders;
EOF

-- 低效：GROUP BY 全表 → HAVING 再過濾
SELECT user_id, COUNT(*) FROM orders GROUPBY user_id HAVINGCOUNT(*) >100;

-- 優化：先用 WHERE 過濾，再 GROUP BY
SELECT user_id, COUNT(*) FROM orders
WHERE created_at >'2024-01-01'
GROUPBY user_id
HAVINGCOUNT(*) >100;

# 主庫：處理寫（INSERT/UPDATE/DELETE）
# 從庫：處理讀（SELECT），可有多個
# 讀寫比例通常 1:9（1 主 9 從）

# Cache-Aside 模式
defget_user(user_id):
# 先查緩存
    user = redis.get(f"user:{user_id}")
if user:
return user

# 緩存未命中，查數據庫
    user = mysql.query(f"SELECT * FROM users WHERE user_id={user_id}")

# 寫入緩存，過期時間 1 小時
    redis.setex(f"user:{user_id}", 3600, user)
return user

-- MySQL 5.7 支持查詢緩存（8.0 已刪除）
SETGLOBAL query_cache_type =1;
SETGLOBAL query_cache_size =1GB;

單庫容量上限：100GB（B+ 樹高度限制）
分庫分表：4 庫 × 4 表 = 16 個分片，容量 = 100GB × 16 = 1.6TB
水平擴展：增加分片數（如改為 8 庫 × 8 表 = 64 分片），容量再提升

# 每日定時檢查
- InnoDB 緩衝池使用率 > 95%
- 慢查詢 QPS > 10 條/分鐘
- 主從複製延遲 > 1 秒
- 磁盤使用率 > 85%

# 每日全量備份，每小時增量備份
mysqldump -u root -p --single-transaction --master-data=2 \
  --all-databases > backup_$(date +%Y%m%d_%H%M%S).sql

# 模擬恢復（每週一次），確保備份可用
mysql -u root -p < backup_20240115_120000.sql

-- 默認 utf8（MySQL 中僅支持 3 字節），改用 utf8mb4（4 字節）
CREATE TABLE users (
    user_id BIGINTPRIMARY KEY,
    name VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 錯誤：一個事務更新 1w 條記錄，主從延遲嚴重
BEGIN;
UPDATE users SET status='active'WHERE created_at <'2024-01-01';  -- 100w 行
COMMIT;

-- 正確：分批更新
FOR i IN0..99:
UPDATE users SET status='active'
WHERE created_at <'2024-01-01'
    LIMIT i*10000, 10000;

# 設置 1 秒閾值，每週分析一次
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

# 每週五定時任務
0 2 * * 5 pt-query-digest /var/log/mysql/slow.log | mail admin@example.com

DBA 變更檢查清單：
☐ EXPLAIN 確認查詢是否走索引
☐ 在從庫灰度驗證（1 小時）
☐ 在預發佈環境全量驗證（30 分鐘）
☐ 在主庫執行前備份（mysqldump）
☐ 準備回滾腳本
☐ 設置時間窗口（業務低谷）
☐ 監控變更後的性能指標（30 分鐘）

示例：32GB 服務器
- 系統 OS：2GB
- MySQL 其他開銷（連接、排序、臨時表）：2GB
- InnoDB 緩衝池：28GB

-- 主庫
sync_binlog =1                        # 每次提交都刷盤
innodb_flush_log_at_trx_commit =1    # 事務立即刷盤

-- 從庫
slave-parallel-workers =4             # 並行複製，加速應用 binlog

#!/bin/bash
# 文件名：mysql_health_check.sh
# 用途：一次性檢查 MySQL 性能、配置、主從複製等關鍵指標
# 執行：bash mysql_health_check.sh

set -e

# 配置變量（需要修改）
MYSQL_HOST="192.168.1.10"
MYSQL_USER="root"
MYSQL_PASSWORD="password"
MYSQL_PORT="3306"

# 顏色輸出
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m'# No Color

echo -e "${YELLOW}=== MySQL 性能診斷報告 ===${NC}"
echo"執行時間：$(date '+%Y-%m-%d %H:%M:%S')"
echo""

# 1. 檢查 MySQL 是否運行
echo -e "${GREEN}[1/10] 檢查 MySQL 進程狀態${NC}"
if mysqladmin -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD ping 2>/dev/null | grep -q "mysqld is alive"; then
echo -e "${GREEN}✓ MySQL 服務運行正常${NC}"
else
echo -e "${RED}✗ MySQL 服務未運行${NC}"
exit 1
fi

# 2. 獲取版本信息
echo -e "\n${GREEN}[2/10] MySQL 版本${NC}"
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SELECT VERSION() as 'MySQL Version';"

# 3. 檢查緩衝池使用率
echo -e "\n${GREEN}[3/10] InnoDB 緩衝池使用率${NC}"
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
SELECT
    ROUND((pages_data * 100) / (pages_data + pages_free), 2) as 'Buffer Pool Usage %'
FROM (
    SELECT
        (VARIABLE_VALUE * 1024 * 1024 / 16384) as pages_total,
        VARIABLE_VALUE as size_mb
    FROM information_schema.GLOBAL_VARIABLES
    WHERE VARIABLE_NAME = 'innodb_buffer_pool_size'
) AS pool_size,
(
    SELECT
        VARIABLE_VALUE as pages_data
    FROM information_schema.GLOBAL_STATUS
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'
) AS data_pages,
(
    SELECT
        VARIABLE_VALUE as pages_free
    FROM information_schema.GLOBAL_STATUS
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_free'
) AS free_pages;
"

# 4. 檢查慢查詢日誌
echo -e "\n${GREEN}[4/10] 慢查詢設置${NC}"
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
SELECT
    @@slow_query_log as 'Slow Query Log',
    @@long_query_time as 'Query Time Threshold (s)';"

# 5. 檢查 QPS
echo -e "\n${GREEN}[5/10] 當前 QPS（近似值）${NC}"
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
SHOW STATUS WHERE variable_name IN ('Questions', 'Innodb_rows_read', 'Innodb_rows_inserted');"

# 6. 檢查連接數
echo -e "\n${GREEN}[6/10] 連接數狀態${NC}"
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
SELECT
    @@max_connections as 'Max Connections',
    (SELECT COUNT(*) FROM information_schema.PROCESSLIST) as 'Current Connections',
    ROUND(((SELECT COUNT(*) FROM information_schema.PROCESSLIST) * 100) / @@max_connections, 2) as 'Usage %';"

# 7. 檢查主從複製
echo -e "\n${GREEN}[7/10] 主從複製狀態${NC}"
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
SHOW SLAVE STATUS\G" 2>/dev/null || echo"此實例為獨立庫（無從庫配置）"

# 8. 檢查表碎片
echo -e "\n${GREEN}[8/10] 數據庫表統計（Top 5 大表）${NC}"
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024), 2) as 'Size(GB)',
    TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
ORDER BY DATA_LENGTH + INDEX_LENGTH DESC
LIMIT 5;"

# 9. 檢查冗餘索引
echo -e "\n${GREEN}[9/10] 索引統計${NC}"
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    COUNT(*) as 'Index Count'
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
GROUP BY TABLE_SCHEMA, TABLE_NAME
ORDER BY 'Index Count' DESC
LIMIT 10;"

# 10. 安全建議
echo -e "\n${GREEN}[10/10] 安全建議${NC}"
echo"1. ✓ 定期備份數據庫（建議每日全量 + 每小時增量）"
echo"2. ✓ 啓用 binlog，配置主從複製（高可用）"
echo"3. ✓ 定期分析慢查詢日誌，優化關鍵 SQL"
echo"4. ✓ 監控關鍵指標（緩衝池、連接數、磁盤）"
echo"5. ✓ 定期清理冗餘索引，減少寫入成本"

echo -e "\n${YELLOW}=== 診斷完成 ===${NC}"

#!/bin/bash
# 文件名：setup_replication.sh
# 用途：自動配置 MySQL 主從複製（僅適用於全新實例）
# 執行：bash setup_replication.sh master|slave

set -e

MASTER_IP="192.168.1.10"
SLAVE_IP="192.168.1.11"
REPL_USER="repl"
REPL_PASSWORD="repl_password"
MASTER_PORT="3306"
SLAVE_PORT="3306"

if [ "$1" = "master" ]; then
echo"配置主庫..."

# 修改 /etc/my.cnf
    sed -i '/\[mysqld\]/a\
server-id = 10\
log_bin = mysql-bin\
binlog_format = ROW\
sync_binlog = 1' /etc/my.cnf

# 重啓 MySQL
    systemctl restart mysqld

# 創建複製賬户
    mysql -u root -p$MYSQL_PASSWORD << EOF
CREATE USER '$REPL_USER'@'$SLAVE_IP' IDENTIFIED BY '$REPL_PASSWORD';
GRANT REPLICATION SLAVE ON *.* TO '$REPL_USER'@'$SLAVE_IP';
FLUSH PRIVILEGES;
EOF

echo"✓ 主庫配置完成"

elif [ "$1" = "slave" ]; then
echo"配置從庫..."

# 修改 /etc/my.cnf
    sed -i '/\[mysqld\]/a\
server-id = 11\
relay-log = mysql-relay-bin' /etc/my.cnf

# 重啓 MySQL
    systemctl restart mysqld

# 獲取主庫 binlog 位置
    MASTER_STATUS=$(mysql -h $MASTER_IP -u root -p$MYSQL_PASSWORD -e "SHOW MASTER STATUS\G")
    LOG_FILE=$(echo"$MASTER_STATUS" | grep "File:" | awk '{print $NF}')
    LOG_POS=$(echo"$MASTER_STATUS" | grep "Position:" | awk '{print $NF}')

# 配置從庫複製
    mysql -u root -p$MYSQL_PASSWORD << EOF
CHANGE MASTER TO
  MASTER_HOST='$MASTER_IP',
  MASTER_USER='$REPL_USER',
  MASTER_PASSWORD='$REPL_PASSWORD',
  MASTER_LOG_FILE='$LOG_FILE',
  MASTER_LOG_POS=$LOG_POS;

START SLAVE;
SHOW SLAVE STATUS\G
EOF

echo"✓ 從庫配置完成"
else
echo"用法：bash setup_replication.sh [master|slave]"
exit 1
fi

#!/bin/bash
# 文件名：analyze_slow_queries.sh
# 用途：分析慢查詢日誌，給出優化建議

SLOW_LOG="/var/log/mysql/slow.log"

if [ ! -f "$SLOW_LOG" ]; then
echo"慢查詢日誌不存在：$SLOW_LOG"
exit 1
fi

echo"=== MySQL 慢查詢分析報告 ==="
echo"日誌路徑：$SLOW_LOG"
echo"分析時間：$(date '+%Y-%m-%d %H:%M:%S')"
echo""

# 使用 pt-query-digest 分析
pt-query-digest "$SLOW_LOG" --limit=10 | head -100

echo""
echo"=== 優化建議 ==="
echo"1. 檢查 Top 10 慢查詢的執行計劃（EXPLAIN）"
echo"2. 為缺失索引的查詢添加索引"
echo"3. 考慮使用 JOIN 代替子查詢"
echo"4. 避免 SELECT *，僅查詢必需字段"
echo"5. 檢查是否有全表掃描（type=ALL）"