ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE 
    tbl_name [, tbl_name] ...

-- 單表分析
ANALYZE TABLE customers;
-- 多表分析（適用于批量維護）
ANALYZE TABLE orders, order_items;
-- 不寫入二進制日志（主從復制環(huán)境）
ANALYZE NO_WRITE_TO_BINLOG TABLE large_table;

-- 查看索引統(tǒng)計信息
SHOW INDEX FROM customers;
-- 查看直方圖信息（MySQL 8.0+）
SELECT * FROM information_schema.column_statistics
WHERE table_name = 'customers';

-- 查看自動分析設置
SHOW VARIABLES LIKE 'innodb_stats_auto_recalc';
-- 設置自動分析閾值（默認10%變化觸發(fā)）
SET GLOBAL innodb_stats_persistent_sample_pages = 200;
ALTER TABLE customers STATS_SAMPLE_PAGES = 500;

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name] ...

-- 單表優(yōu)化
OPTIMIZE TABLE order_archive;
-- 批量優(yōu)化所有表
SELECT CONCAT('OPTIMIZE TABLE ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'mydb' 
AND engine = 'InnoDB'
INTO OUTFILE '/tmp/optimize_tables.sql';
SOURCE /tmp/optimize_tables.sql;

-- 查看表碎片率（InnoDB）
SELECT table_name, 
       data_free / (data_length + index_length) AS frag_ratio
FROM information_schema.tables
WHERE table_schema = 'mydb'
AND data_length > 0;
-- 優(yōu)化前后性能對比
EXPLAIN ANALYZE SELECT * FROM large_table WHERE create_time > '2023-01-01';

-- 使用pt-online-schema-change工具（Percona Toolkit）
pt-online-schema-change --alter="ENGINE=InnoDB" D=mydb,t=large_table
-- 使用gh-ost工具（GitHub）
gh-ost --alter="ENGINE=InnoDB" --database=mydb --table=large_table

-- 每周維護腳本示例
SET @db = 'mydb';
SET @threshold = 0.3; -- 碎片率閾值
SELECT CONCAT('ANALYZE TABLE ', table_name, ';') AS analyze_cmd
FROM information_schema.tables
WHERE table_schema = @db
AND engine = 'InnoDB';
SELECT CONCAT('OPTIMIZE TABLE ', table_name, ';') AS optimize_cmd
FROM (
    SELECT table_name, 
           data_free / (data_length + index_length) AS frag_ratio
    FROM information_schema.tables
    WHERE table_schema = @db
    AND engine = 'InnoDB'
    AND data_length > 0
) t WHERE frag_ratio > @threshold;

-- 查詢效率變化監(jiān)控
SELECT * FROM sys.schema_table_statistics
WHERE table_schema = 'mydb';
-- 碎片率監(jiān)控視圖
CREATE VIEW frag_monitor AS
SELECT table_schema, table_name, 
       ROUND(data_free/(1024*1024),2) AS frag_mb,
       ROUND(data_free/(data_length+index_length)*100,2) AS frag_pct
FROM information_schema.tables
WHERE data_length > 0
ORDER BY frag_mb DESC;

-- 查看阻塞會話
SELECT * FROM performance_schema.threads 
WHERE PROCESSLIST_COMMAND = 'Query' 
AND PROCESSLIST_STATE LIKE '%optimize%';
-- 安全終止優(yōu)化操作
KILL [process_id];

# 檢查磁盤空間
df -h /var/lib/mysql
# 臨時更改tmpdir（需要重啟）
[mysqld]
tmpdir = /mnt/bigtmp

-- 從庫延遲監(jiān)控
SHOW SLAVE STATUS\G
-- 使用NO_WRITE_TO_BINLOG
OPTIMIZE NO_WRITE_TO_BINLOG TABLE audit_log;

# 分塊優(yōu)化（使用pt-archiver）
pt-archiver --source h=localhost,D=mydb,t=large_table \
  --purge --where "1=1" --limit 1000 --commit-each