## 1. 查看执行计划
$ /yashanb/app/yasdb/bin/psql -U fgedu -d fgedudb -c “EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE customer_id = 123 AND order_date >= ‘2023-01-01’;”

## 2. 分析慢查询日志
$ tail -n 100 /yashanb/app/yasdb/log/yasdb.log | grep “duration:”

## 3. 使用pg_stat_statements
$ /yashanb/app/yasdb/bin/psql -U fgedu -d fgedudb -c “SELECT query, calls, total_exec_time, mean_exec_time, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20;”

## 4. 分析表和索引统计信息
$ /yashanb/app/yasdb/bin/psql -U fgedu -d fgedudb -c “SELECT relname, n_live_tup, n_dead_tup, last_analyze FROM pg_stat_user_tables WHERE relname = ‘fgedu_orders’;”

## 5. 分析索引使用情况
$ /yashanb/app/yasdb/bin/psql -U fgedu -d fgedudb -c “SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE tablename = ‘fgedu_orders’;”

## 6. 分析锁等待情况
$ /yashanb/app/yasdb/bin/psql -U fgedu -d fgedudb -c “SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid, blocked_activity.query AS blocked_query, blocking_activity.query AS blocking_query FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.pid != blocked_locks.pid WHERE NOT blocked_locks.GRANTED;”

## 1. 索引优化
– 创建合适的索引
CREATE INDEX idx_orders_customer_date ON fgedu_orders(customer_id, order_date);

– 重建索引
REINDEX INDEX idx_orders_customer_date;

– 更新统计信息
ANALYZE fgedu_orders;

## 2. SQL语句优化
– 避免全表扫描
— 优化前
SELECT * FROM fgedu_orders WHERE customer_id = 123;

— 优化后
SELECT order_id, order_date, total_amount FROM fgedu_orders WHERE customer_id = 123;

– 优化多表连接
— 优化前
SELECT * FROM fgedu_orders o, fgedu_customers c WHERE o.customer_id = c.customer_id AND o.status = ‘completed’;

— 优化后
SELECT o.order_id, o.order_date, o.total_amount, c.customer_name FROM fgedu_orders o INNER JOIN fgedu_customers c ON o.customer_id = c.customer_id WHERE o.status = ‘completed’;

– 优化子查询
— 优化前
SELECT * FROM fgedu_orders WHERE customer_id IN (SELECT customer_id FROM fgedu_customers WHERE city = ‘Beijing’);

— 优化后
SELECT o.* FROM fgedu_orders o INNER JOIN fgedu_customers c ON o.customer_id = c.customer_id WHERE c.city = ‘Beijing’;

– 避免在WHERE子句中使用函数
— 优化前
SELECT * FROM fgedu_orders WHERE DATE(order_date) = ‘2023-01-01’;

— 优化后
SELECT * FROM fgedu_orders WHERE order_date >= ‘2023-01-01’ AND order_date < '2023-01-02'; ## 3. 表结构优化 - 使用分区表 CREATE TABLE fgedu_orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE, total_amount DECIMAL(10,2), status VARCHAR(20) ) PARTITION BY RANGE (order_date); CREATE TABLE fgedu_orders_2023 PARTITION OF fgedu_orders FOR VALUES FROM ('2023-01-01') TO ('2024-01-01'); - 合理设计表结构 -- 优化前 CREATE TABLE fgedu_orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE, order_details TEXT, status VARCHAR(20) ); -- 优化后 CREATE TABLE fgedu_orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE, total_amount DECIMAL(10,2), status VARCHAR(20) ); CREATE TABLE fgedu_order_details ( detail_id INT PRIMARY KEY, order_id INT, product_id INT, quantity INT, price DECIMAL(10,2), FOREIGN KEY (order_id) REFERENCES fgedu_orders(order_id) ); ## 4. 数据库参数优化 - 内存参数优化 shared_buffers = '16GB' work_mem = '256MB' maintenance_work_mem = '2GB' - I/O参数优化 checkpoint_timeout = 30min max_wal_size = '4GB' checkpoint_completion_target = 0.9 - 并发参数优化 max_connections = 1000 bgwriter_delay = 200ms bgwriter_lru_maxpages = 100 bgwriter_lru_multiplier = 2.0

## 1. 识别慢SQL
– 使用慢查询日志
– 使用pg_stat_statements
– 使用pg_stat_activity

## 2. 分析慢SQL
– 查看执行计划
– 分析表和索引统计信息
– 分析锁等待情况
– 分析系统资源使用情况

## 3. 制定优化方案
– 索引优化方案
– SQL语句优化方案
– 表结构优化方案
– 数据库参数优化方案

## 4. 实施优化方案
– 创建或修改索引
– 优化SQL语句
– 修改表结构
– 调整数据库参数

## 5. 验证优化效果
– 执行优化后的SQL语句
– 比较优化前后的执行时间
– 监控系统性能变化
– 确认优化效果

## 6. 持续监控
– 定期分析慢SQL
– 及时发现新的慢SQL
– 持续优化系统性能