-- MySQL 備份單表（導(dǎo)出結(jié)構(gòu)和數(shù)據(jù)）
mysqldump -u 用戶名 -p 數(shù)據(jù)庫(kù)名 表名 > backup_table.sql

-- PostgreSQL 備份整個(gè)數(shù)據(jù)庫(kù)
pg_dump -U 用戶名 -d 數(shù)據(jù)庫(kù)名 > backup_db.sql

-- 恢復(fù)備份（通用）
mysql -u 用戶名 -p 數(shù)據(jù)庫(kù)名 < backup.sql
psql -U 用戶名 -d 數(shù)據(jù)庫(kù)名 -f backup.sql

-- MySQL 查詢所有表結(jié)構(gòu)
SELECT 
    TABLE_NAME AS 表名,
    COLUMN_NAME AS 字段名,
    DATA_TYPE AS 數(shù)據(jù)類型,
    COLUMN_COMMENT AS 描述
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_SCHEMA = '數(shù)據(jù)庫(kù)名';

-- MySQL 查看未使用索引的表
SELECT 
    object_schema, object_name, index_name
FROM 
    performance_schema.table_io_waits_summary_by_index_usage
WHERE 
    index_name IS NOT NULL
    AND count_star = 0;

-- PostgreSQL 查找缺失索引
SELECT 
    relname AS 表名,
    seq_scan - idx_scan AS 全表掃描次數(shù)差,
    CASE 
        WHEN seq_scan - idx_scan > 0 THEN '建議添加索引'
        ELSE '無(wú)需優(yōu)化'
    END AS 建議
FROM 
    pg_stat_all_tables
WHERE 
    schemaname = 'public';

-- MySQL 查看正在執(zhí)行的慢查詢
SHOW FULL PROCESSLIST;
-- 或通過(guò)慢查詢?nèi)罩痉治觯ㄐ杼崆伴_(kāi)啟配置）

-- PostgreSQL 監(jiān)控活躍查詢
SELECT 
    pid, query, now() - query_start AS 執(zhí)行時(shí)長(zhǎng)
FROM 
    pg_stat_activity
WHERE 
    state = 'active'
    AND query NOT LIKE '%pg_stat_activity%';

-- 批量插入（MySQL/PostgreSQL通用）
INSERT INTO users (name, age) VALUES
('Alice', 25),
('Bob', 30),
('Charlie', 28);

-- 批量更新（MySQL）
UPDATE users
SET status = CASE id
    WHEN 1 THEN 'active'
    WHEN 2 THEN 'inactive'
END
WHERE id IN (1, 2);

-- 批量更新（PostgreSQL）
UPDATE users AS u
SET status = tmp.status
FROM (VALUES
    (1, 'active'),
    (2, 'inactive')
) AS tmp(id, status)
WHERE u.id = tmp.id;

-- 通用：刪除重復(fù)記錄（保留id最大的一條）
DELETE FROM orders
WHERE id NOT IN (
    SELECT MAX(id)
    FROM orders
    GROUP BY order_no
);

-- MySQL（LIMIT-OFFSET）
SELECT * FROM products 
ORDER BY id
LIMIT 10 OFFSET 20;  -- 第3頁(yè)，每頁(yè)10條

-- PostgreSQL（窗口函數(shù)高效分頁(yè)）
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM products
) AS tmp
WHERE row_num BETWEEN 21 AND 30;

-- MySQL 查看用戶權(quán)限
SHOW GRANTS FOR '用戶名'@'主機(jī)';

-- PostgreSQL 列出所有用戶角色
SELECT rolname, rolcreaterole, rolsuper 
FROM pg_roles;

-- MySQL 查看表大小
SELECT 
    TABLE_NAME AS 表名,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 大小(MB)
FROM 
    INFORMATION_SCHEMA.TABLES
WHERE 
    TABLE_SCHEMA = '數(shù)據(jù)庫(kù)名'
ORDER BY 
    (DATA_LENGTH + INDEX_LENGTH) DESC;

-- PostgreSQL 查看數(shù)據(jù)庫(kù)大小
SELECT 
    pg_size_pretty(pg_database_size('數(shù)據(jù)庫(kù)名')) AS 大小;

-- 示例：根據(jù)參數(shù)動(dòng)態(tài)篩選（偽代碼邏輯）
SET @sql = CONCAT('
    SELECT * FROM orders 
    WHERE 1=1
    ', IF(region IS NOT NULL, ' AND region = ?', ''),
    ' ORDER BY create_time DESC'
);
PREPARE stmt FROM @sql;
EXECUTE stmt USING region_value;

-- MySQL 拆分逗號(hào)分隔的字符串
SELECT 
    id,
    SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', n), ',', -1) AS tag
FROM 
    products
CROSS JOIN 
    (SELECT 1 AS n UNION SELECT 2 UNION SELECT 3) numbers
WHERE 
    n <= LENGTH(tags) - LENGTH(REPLACE(tags, ',', '')) + 1;

-- PostgreSQL 使用 regexp_split_to_table
SELECT 
    id, 
    regexp_split_to_table(tags, ',') AS tag
FROM 
    products;

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl  
./mysqltuner.pl --socket /var/lib/mysql/mysql.sock  

pt-query-digest /var/lib/mysql/slow.log > slow_report.log  
# 分析指定時(shí)間范圍  
pt-query-digest --since '2025-04-28 00:00:00' --until '2025-04-29 00:00:00' slow.log  

pt-variable-advisor localhost --socket /var/lib/mysql/mysql.sock  

wget https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh  
./tuning-primer.sh