# 2. 实施步骤
# 步骤1：分析数据库性能瓶颈
# 步骤2：调整MySQL参数
# 步骤3：优化SQL查询
# 步骤4：创建合适的索引
# 步骤5：验证性能改进
# 步骤6：测试与验证

# 3. 应用效果
# 提高了数据库的查询速度
# 增加了并发处理能力
# 减少了数据库的负载

# 分析数据库性能瓶颈
# 查看慢查询日志
mysql -u root -p -e “SHOW VARIABLES LIKE ‘slow_query_log’;”
mysql -u root -p -e “SHOW VARIABLES LIKE ‘slow_query_log_file’;”

# 调整MySQL参数
cat > /etc/my.cnf << 'EOF' [mysqld] # 内存配置 innodb_buffer_pool_size = 8G innodb_log_buffer_size = 32M key_buffer_size = 512M query_cache_size = 128M # 连接配置 max_connections = 2000 wait_timeout = 300 interactive_timeout = 300 # 存储配置 innodb_file_per_table = 1 innodb_flush_method = O_DIRECT innodb_io_capacity = 4000 innodb_read_io_threads = 16 innodb_write_io_threads = 16 # 日志配置 slow_query_log = 1 slow_query_log_file = /var/lib/mysql/slow-query.log long_query_time = 0.5 EOF # 重启MySQL服务 systemctl restart mysqld # 优化SQL查询 # 原查询 SELECT * FROM fgedu_users WHERE name LIKE '%test%'; # 优化后查询 SELECT id, name, email FROM fgedu_users WHERE name LIKE '%test%'; # 创建索引 CREATE INDEX idx_name ON fgedu_users(name); # 验证性能改进 # 查看查询执行计划 EXPLAIN SELECT id, name, email FROM fgedu_users WHERE name LIKE '%test%'; # 测试查询性能 mysql -u root -p -e "SELECT benchmark(1000000, SHA1('test'));"

# 2. 实施步骤
# 步骤1：分析数据库性能瓶颈
# 步骤2：调整PostgreSQL参数
# 步骤3：优化SQL查询
# 步骤4：创建合适的索引
# 步骤5：验证性能改进
# 步骤6：测试与验证

# 3. 应用效果
# 提高了数据库的查询速度
# 增加了并发处理能力
# 减少了数据库的负载

# 分析数据库性能瓶颈
# 查看慢查询
psql -U postgres -c “SELECT * FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10;”

# 调整PostgreSQL参数
cat > /var/lib/pgsql/data/postgresql.conf << 'EOF' # 基本配置 listen_addresses = '*' port = 5432 max_connections = 1000 # 内存配置 shared_buffers = 2GB work_mem = 32MB temp_buffers = 16MB maintenance_work_mem = 512MB # 存储配置 effective_cache_size = 8GB random_page_cost = 4 seq_page_cost = 1 # 写入配置 synchronous_commit = on wal_buffers = 32MB checkpoint_completion_target = 0.9 max_wal_size = 2GB min_wal_size = 160MB # 日志配置 log_destination = 'stderr' logging_collector = on log_directory = 'pg_log' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 100MB log_min_duration_statement = 500 EOF # 重启PostgreSQL服务 systemctl restart postgresql # 优化SQL查询 # 原查询 SELECT * FROM fgedu_sales WHERE date >= ‘2023-01-01’ AND date <= '2023-12-31'; # 优化后查询 SELECT product_id, SUM(amount) FROM fgedu_sales WHERE date >= ‘2023-01-01’ AND date <= '2023-12-31' GROUP BY product_id; # 创建索引 CREATE INDEX idx_date ON fgedu_sales(date); CREATE INDEX idx_product_id ON fgedu_sales(product_id); # 验证性能改进 # 查看查询执行计划 EXPLAIN ANALYZE SELECT product_id, SUM(amount) FROM fgedu_sales WHERE date >= ‘2023-01-01’ AND date <= '2023-12-31' GROUP BY product_id; # 测试查询性能 psql -U postgres -c "SELECT benchmark(1000000, SHA1('test'));"

# 2. 实施步骤
# 步骤1：分析数据库性能瓶颈
# 步骤2：调整Oracle参数
# 步骤3：优化SQL查询
# 步骤4：创建合适的索引
# 步骤5：验证性能改进
# 步骤6：测试与验证

# 3. 应用效果
# 提高了数据库的查询速度
# 增加了并发处理能力
# 减少了数据库的负载

# 分析数据库性能瓶颈
# 查看AWR报告
sqlplus / as sysdba
@?/rdbms/admin/awrrpt.sql

# 调整Oracle参数
# 编辑init.ora文件
vim $ORACLE_HOME/dbs/initfgedudb.ora

# 添加以下参数
memory_target = 16G
processes = 1000
open_cursors = 3000
sga_target = 8G
pga_aggregate_target = 4G

# 重启Oracle服务
sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
STARTUP;

# 优化SQL查询
# 原查询
SELECT * FROM fgedu_employees WHERE department_id = 100;

# 优化后查询
SELECT employee_id, first_name, last_name, email FROM fgedu_employees WHERE department_id = 100;

# 创建索引
CREATE INDEX idx_department_id ON fgedu_employees(department_id);

# 验证性能改进
# 查看执行计划
EXPLAIN PLAN FOR SELECT employee_id, first_name, last_name, email FROM fgedu_employees WHERE department_id = 100;
SELECT * FROM TABLE(dbms_xplan.display);

# 测试查询性能
DECLARE
start_time TIMESTAMP;
end_time TIMESTAMP;
BEGIN
start_time := SYSTIMESTAMP;
FOR i IN 1..1000000 LOOP
NULL;
END LOOP;
end_time := SYSTIMESTAMP;
DBMS_OUTPUT.PUT_LINE(‘Elapsed time: ‘ || (end_time – start_time));
END;
/