# 下载MySQL YUM仓库
sudo dnf install -y https://dev.mysql.com/get/mysql80-community-release-el8-4.noarch.rpm

# 验证仓库是否添加成功
sudo dnf repolist enabled | grep "mysql.*-community.*"

# 安装MySQL服务器
sudo dnf install -y mysql-community-server

# 启动MySQL服务并设置开机自启
sudo systemctl enable --now mysqld

# 检查服务状态
sudo systemctl status mysqld

# 获取临时密码
sudo grep 'temporary password' /var/log/mysqld.log

# 运行安全脚本
sudo mysql_secure_installation

[mysqld]
# Basic Settings
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Character Set Settings
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

# InnoDB Settings (核心优化)
innodb_buffer_pool_size=12G    # (建议为总内存的50-70%)
innodb_buffer_pool_instances=8 # (每个实例至少1GB)
innodb_log_file_size=2G        # (较大的日志文件提高写入性能)
innodb_flush_method=O_DIRECT   # (减少双缓冲开销)
innodb_flush_log_at_trx_commit=2 # (平衡性能与持久性，生产环境可设为1)
innodb_read_io_threads=16      # (根据CPU核心数调整)
innodb_write_io_threads=16     # (根据CPU核心数调整)

# Connection Settings (连接数优化)
max_connections=1000           # (根据应用需求调整)
thread_cache_size=100          # (减少线程创建开销)

# Query Cache Settings (查询缓存已弃用，保持禁用)
query_cache_type=0             
query_cache_size=0             

# Other Performance Settings
table_open_cache=4000          # (减少表打开操作) 
tmp_table_size=256M            # (提高临时表处理能力)
max_heap_table_size=256M       # (内存表大小限制)

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

sudo systemctl restart mysqld

-- InnoDB缓冲池命中率检查 (>95%为佳)
SELECT 
    CONCAT(ROUND(100*(SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests')/
    ((SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests') + 
    (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads')),2),'%') AS hit_rate;

-- InnoDB缓冲池使用情况监控  
SELECT 
    ROUND((data_size + index_size) / POWER(1024,3),2) AS total_data_gb,
    ROUND(buffer_pool_size / POWER(1024,3),2) AS buffer_pool_gb,
    ROUND(((data_size + index_size) / buffer_pool_size)*100,2) AS percent_in_memory,
    ROUND((buffer_pool_size - free_buffers)/POWER(1024,3),2) AS used_memory_gb,
    ROUND(free_buffers/POWER(1024,3),2) AS free_memory_gb,
    ROUND((buffer_pool_size - free_buffers)/buffer_pool_size*100,2) AS buffer_pool_full_percent 
FROM (
    SELECT 
        SUM(data_length+index_length) data_size,
        SUM(index_length) index_size,
        @@innodb_buffer_pool_size buffer_pool_size,
        @@innodb_buffer_pool_chunk_size * @@innodb_buffer_pool_instances * ((@@innodb_buffer_pool_size)/(@@innodb_buffer_pool_chunk_size * @@innodb_buffer_pool_instances)-FLOOR((@@innodb_buffer_pool_size)/(@@innodb_buffer_pool_chunk_size * @@innodb_buffer_pool_instances))) free_buffers 
    FROM information_schema.tables WHERE engine='InnoDB'
) t;

-- 启用慢查询日志（记录执行超过2秒的查询）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_output = 'FILE';

-- MySQL Workbench或命令行查看慢查询日志位置：
SHOW VARIABLES LIKE '%slow_query_log%';

-- EXPLAIN分析慢查询示例：
EXPLAIN SELECT * FROM large_table WHERE user_id = '12345';

-- SQL语句性能分析工具：
SET profiling = ON;
-- [执行你的SQL语句]
SHOW PROFILE;
SHOW PROFILES;

-- 查找未使用索引的表和索引：
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;

-- Redundant Indexes检查：
SELECT 
    s.indexed_table,
    s.indexed_column,
    GROUP_CONCAT(s.index_name ORDER BY seq_in_index) as indexes_on_column,
    COUNT(*) as num_indexes_on_column,
    GROUP_CONCAT(i.non_unique ORDER BY seq_in_index) as uniqueness_of_indexes,
    GROUP_CONCAT(i.sub_part ORDER BY seq_in_index) as subparts_of_indexes_on_column  
FROM (
    SELECT 
        table_schema as indexed_db, table_name as indexed_table, index_name, column_name as indexed_column, seq_in_index  
    FROM information_schema.statistics  
    WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
) s  
JOIN information_schema.statistics i ON s.indexed_db = i.table_schema AND s.indexed_table = i.table_name AND s.index_name = i.index_name  
GROUP BY indexed_db, indexed_table, indexed_column  
HAVING COUNT(*) >1;