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

# 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 = 16G innodb_log_buffer_size = 128M key_buffer_size = 1G query_cache_size = 256M # 连接配置 max_connections = 3000 wait_timeout = 300 interactive_timeout = 300 # 存储配置 innodb_file_per_table = 1 innodb_flush_method = O_DIRECT innodb_io_capacity = 8000 innodb_read_io_threads = 32 innodb_write_io_threads = 32 innodb_purge_threads = 8 innodb_page_cleaners = 8 # 日志配置 slow_query_log = 1 slow_query_log_file = /var/lib/mysql/slow-query.log long_query_time = 0.1 # 并行查询 innodb_parallel_read_threads = 8 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); # 使用分区表 CREATE TABLE fgedu_fgfgsales ( id INT PRIMARY KEY AUTO_INCREMENT, product_id INT, amount DECIMAL(10,2), sale_date DATE ) PARTITION BY RANGE (YEAR(sale_date)) ( PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN (2024), PARTITION p2024 VALUES LESS THAN (2025) ); # 验证性能改进 # 查看查询执行计划 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：验证性能改进
# 步骤7：测试与验证

# 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 = 4GB work_mem = 64MB temp_buffers = 32MB maintenance_work_mem = 1GB # 存储配置 effective_cache_size = 16GB random_page_cost = 4 seq_page_cost = 1 # 写入配置 synchronous_commit = on wal_buffers = 64MB checkpoint_completion_target = 0.9 max_wal_size = 4GB min_wal_size = 320MB # 日志配置 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 = 100 # 并行查询 max_parallel_workers = 8 max_parallel_workers_per_gather = 4 EOF # 重启PostgreSQL服务 systemctl restart postgresql # 优化SQL查询 # 原查询 SELECT * FROM fgedu_fgfgsales WHERE date >= ‘2023-01-01’ AND date <= '2023-12-31'; # 优化后查询 SELECT product_id, SUM(amount) FROM fgedu_fgfgsales WHERE date >= ‘2023-01-01’ AND date <= '2023-12-31' GROUP BY product_id; # 创建索引 CREATE INDEX idx_date ON fgedu_fgfgsales(date); CREATE INDEX idx_product_id ON fgedu_fgfgsales(product_id); # 启用并行查询 ALTER TABLE fgedu_fgfgsales SET (parallel_workers = 4); # 验证性能改进 # 查看查询执行计划 EXPLAIN ANALYZE SELECT product_id, SUM(amount) FROM fgedu_fgfgsales 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：验证性能改进
# 步骤7：测试与验证

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

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

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

# 添加以下参数
memory_target = 32G
processes = 2000
open_cursors = 3000
sga_target = 16G
pga_aggregate_target = 8G
parallel_max_servers = 32
parallel_min_servers = 4

# 重启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);

# 使用分区表
CREATE TABLE fgedu_fgfgsales (
id NUMBER PRIMARY KEY,
product_id NUMBER,
amount NUMBER,
sale_date DATE
) PARTITION BY RANGE (sale_date) (
PARTITION p2022 VALUES LESS THAN (TO_DATE(‘2023-01-01’, ‘YYYY-MM-DD’)),
PARTITION p2023 VALUES LESS THAN (TO_DATE(‘2024-01-01’, ‘YYYY-MM-DD’)),
PARTITION p2024 VALUES LESS THAN (TO_DATE(‘2025-01-01’, ‘YYYY-MM-DD’))
);

# 验证性能改进
# 查看执行计划
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;
/