# 2. 实施步骤
# 步骤1：部署PostgreSQL
# 步骤2：优化PostgreSQL配置
# 步骤3：优化索引设计
# 步骤4：优化查询语句
# 步骤5：验证性能改进

# 3. 应用效果
# 提高了数据库的性能
# 减少了查询响应时间
# 提高了系统的可靠性

# 部署PostgreSQL
dnf install -y postgresql-server postgresql-contrib
postgresql-setup –initdb
systemctl start postgresql
systemctl enable postgresql

# 优化PostgreSQL配置
cat > /var/lib/pgsql/data/postgresql.conf << 'EOF' # 基本配置 listen_addresses = '*' port = 5432 max_connections = 200 # 内存配置 shared_buffers = 8GB work_mem = 64MB maintenance_work_mem = 2GB # 存储配置 effective_cache_size = 24GB random_page_cost = 1.1 synchronous_commit = on # 写入配置 wal_buffers = 32MB checkpoint_completion_target = 0.9 max_wal_size = 2GB min_wal_size = 1GB # 查询优化 random_page_cost = 1.1 effective_io_concurrency = 200 # 统计信息 autovacuum = on autovacuum_max_workers = 4 autovacuum_naptime = 10min autovacuum_vacuum_scale_factor = 0.05 autovacuum_analyze_scale_factor = 0.025 # 扩展 shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all EOF # 重启PostgreSQL服务 systemctl restart postgresql # 创建测试表和索引 psql -U fgedu -d fgedudb << 'EOF' -- 创建测试表 CREATE TABLE fgedu_users ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) NOT NULL, age INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- 创建索引 CREATE INDEX idx_fgedu_users_email ON fgedu_users(email); CREATE INDEX idx_fgedu_users_created_at ON fgedu_users(created_at); -- 插入测试数据 INSERT INTO fgedu_users (name, email, age) SELECT 'User ' || generate_series(1, 1000000), 'user' || generate_series(1, 1000000) || '@example.com', floor(random() * 100)::int FROM generate_series(1, 1000000); -- 分析表 ANALYZE fgedu_users; -- 测试查询性能 EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE email = 'user500000@example.com'; EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE created_at > ‘2023-01-01’;
EXPLAIN ANALYZE SELECT age, COUNT(*) FROM fgedu_users GROUP BY age ORDER BY age;
EOF

# 优化查询语句
psql -U fgedu -d fgedudb << 'EOF' -- 优化前 EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE name LIKE '%User 500%'; -- 优化后：创建索引 CREATE INDEX idx_fgedu_users_name ON fgedu_users(name); -- 再次测试 EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE name LIKE '%User 500%'; -- 优化前 EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE age > 50 AND created_at > ‘2023-01-01’;

— 优化后：创建复合索引
CREATE INDEX idx_fgedu_users_age_created_at ON fgedu_users(age, created_at);

— 再次测试
EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE age > 50 AND created_at > ‘2023-01-01’;
EOF

# 验证性能改进
psql -U fgedu -d fgedudb << 'EOF' -- 查看查询性能 SELECT * FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10; -- 查看索引使用情况 SELECT * FROM pg_stat_user_indexes WHERE relname = 'fgedu_users'; -- 查看表统计信息 SELECT * FROM pg_stat_user_tables WHERE relname = 'fgedu_users'; EOF

# 2. 实施步骤
# 步骤1：部署MySQL
# 步骤2：优化MySQL配置
# 步骤3：优化索引设计
# 步骤4：优化查询语句
# 步骤5：验证性能改进

# 3. 应用效果
# 提高了数据库的性能
# 减少了查询响应时间
# 提高了系统的可靠性

# 部署MySQL
dnf install -y mysql-server

systemctl start mysqld
systemctl enable mysqld

# 安全配置
mysql_secure_installation

# 创建数据库和用户
mysql -u root -p << 'EOF' CREATE DATABASE fgedudb; CREATE USER 'fgedu'@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON fgedudb.* TO 'fgedu'@'%'; FLUSH PRIVILEGES; EOF # 优化MySQL配置 cat > /etc/my.cnf.d/mysql-server.cnf << 'EOF' [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysql/mysqld.log pid-file=/run/mysqld/mysqld.pid # 基本配置 port=3306 bind-address=0.0.0.0 max_connections=200 # 内存配置 innodb_buffer_pool_size=8G innodb_log_buffer_size=16M key_buffer_size=1G # 存储配置 innodb_file_per_table=1 innodb_flush_method=O_DIRECT innodb_io_capacity=2000 innodb_io_capacity_max=4000 # 写入配置 innodb_log_file_size=1G innodb_flush_log_at_trx_commit=2 # 查询优化 query_cache_type=0 query_cache_size=0 slow_query_log=1 slow_query_log_file=/var/log/mysql/slow-query.log long_query_time=1 # 统计信息 innodb_stats_on_metadata=0 EOF # 重启MySQL服务 systemctl restart mysqld # 创建测试表和索引 mysql -u fgedu -p fgedudb << 'EOF' -- 创建测试表 CREATE TABLE fgedu_users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) NOT NULL, age INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- 创建索引 CREATE INDEX idx_fgedu_users_email ON fgedu_users(email); CREATE INDEX idx_fgedu_users_created_at ON fgedu_users(created_at); -- 插入测试数据 DELIMITER // CREATE PROCEDURE insert_test_data() BEGIN DECLARE i INT DEFAULT 1; WHILE i <= 1000000 DO INSERT INTO fgedu_users (name, email, age) VALUES ('User ' || i, 'user' || i || '@example.com', FLOOR(RAND() * 100)); SET i = i + 1; END WHILE; END // DELIMITER ; CALL insert_test_data(); DROP PROCEDURE insert_test_data; -- 分析表 ANALYZE TABLE fgedu_users; -- 测试查询性能 EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE email = 'user500000@example.com'; EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE created_at > ‘2023-01-01’;
EXPLAIN ANALYZE SELECT age, COUNT(*) FROM fgedu_users GROUP BY age ORDER BY age;
EOF

# 优化查询语句
mysql -u fgedu -p fgedudb << 'EOF' -- 优化前 EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE name LIKE '%User 500%'; -- 优化后：创建索引 CREATE INDEX idx_fgedu_users_name ON fgedu_users(name); -- 再次测试 EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE name LIKE '%User 500%'; -- 优化前 EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE age > 50 AND created_at > ‘2023-01-01’;

— 优化后：创建复合索引
CREATE INDEX idx_fgedu_users_age_created_at ON fgedu_users(age, created_at);

— 再次测试
EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE age > 50 AND created_at > ‘2023-01-01’;
EOF

# 验证性能改进
mysql -u fgedu -p fgedudb << 'EOF' -- 查看慢查询日志 SHOW GLOBAL VARIABLES LIKE '%slow_query%'; -- 查看索引使用情况 SHOW INDEX FROM fgedu_users; -- 查看表统计信息 SHOW TABLE STATUS LIKE 'fgedu_users'; EOF

# 2. 实施步骤
# 步骤1：部署MongoDB
# 步骤2：优化MongoDB配置
# 步骤3：优化索引设计
# 步骤4：优化查询语句
# 步骤5：验证性能改进

# 3. 应用效果
# 提高了数据库的性能
# 减少了查询响应时间
# 提高了系统的可靠性

# 部署MongoDB
dnf install -y mongodb-server

systemctl start mongod
systemctl enable mongod

# 优化MongoDB配置
cat > /etc/mongod.conf << 'EOF' # for documentation of all options, see: # http://docs.mongodb.org/manual/reference/configuration-options/ # where to write logging data. systemLog: destination: file logAppend: true path: /var/log/mongodb/mongod.log # Where and how to store data. storage: dbPath: /var/lib/mongo journal: enabled: true wiredTiger: engineConfig: cacheSizeGB: 8 collectionConfig: blockCompressor: zstd indexConfig: prefixCompression: true # how the process runs processManagement: fork: true # fork and run in background pidFilePath: /var/run/mongodb/mongod.pid # location of pidfile timeZoneInfo: /usr/share/zoneinfo # network interfaces net: port: 27017 bindIp: 0.0.0.0 # Enter 0.0.0.0,:: to bind to all IPv4 and IPv6 addresses or, alternatively, use the net.bindIpAll setting. # security: # operationProfiling: slowOpThresholdMs: 100 mode: slowOp # replication: # sharding: ## Enterprise-Only Options # auditLog: # snmp: EOF # 重启MongoDB服务 systemctl restart mongod # 创建测试集合和索引 mongo << 'EOF' use fgedudb; -- 创建测试集合 for (let i = 1; i <= 1000000; i++) { db.fgedu_users.insertOne({ name: "User " + i, email: "user" + i + "@example.com", age: Math.floor(Math.random() * 100), created_at: new Date() }); } -- 创建索引 db.fgedu_users.createIndex({ email: 1 }); db.fgedu_users.createIndex({ created_at: 1 }); -- 测试查询性能 db.fgedu_users.find({ email: "user500000@example.com" }).explain("executionStats"); db.fgedu_users.find({ created_at: { $gt: new Date("2023-01-01") } }).explain("executionStats"); db.fgedu_users.aggregate([ { $group: { _id: "$age", count: { $sum: 1 } } }, { $sort: { _id: 1 } } ]).explain("executionStats"); EOF # 优化查询语句 mongo << 'EOF' use fgedudb; -- 优化前 db.fgedu_users.find({ name: { $regex: "User 500" } }).explain("executionStats"); -- 优化后：创建索引 db.fgedu_users.createIndex({ name: 1 }); -- 再次测试 db.fgedu_users.find({ name: { $regex: "User 500" } }).explain("executionStats"); -- 优化前 db.fgedu_users.find({ age: { $gt: 50 }, created_at: { $gt: new Date("2023-01-01") } }).explain("executionStats"); -- 优化后：创建复合索引 db.fgedu_users.createIndex({ age: 1, created_at: 1 }); -- 再次测试 db.fgedu_users.find({ age: { $gt: 50 }, created_at: { $gt: new Date("2023-01-01") } }).explain("executionStats"); EOF # 验证性能改进 mongo << 'EOF' use fgedudb; -- 查看索引使用情况 db.fgedu_users.getIndexes(); -- 查看集合统计信息 db.fgedu_users.stats(); -- 查看慢查询日志 db.adminCommand({ getLog: "slowops" }); EOF