MySQL性能优化实战指南：从基础调优到高可用架构

🌟 引言

MySQL作为世界上最流行的开源关系型数据库，在互联网应用中扮演着至关重要的角色。随着业务规模的增长和数据量的激增，数据库性能优化成为了系统架构中的关键环节。本文将从多个维度深入探讨MySQL性能优化的最佳实践，帮助你构建高性能、高可用的数据库系统。

🏗️ MySQL架构与存储引擎

1. MySQL架构概览

-- MySQL架构层次
/*
1. 连接层 (Connection Layer)
   - 连接处理
   - 授权认证
   - 安全管理

2. 服务层 (Service Layer)
   - SQL解析器
   - 查询优化器
   - 缓存和缓冲

3. 引擎层 (Engine Layer)
   - InnoDB
   - MyISAM
   - Memory

4. 存储层 (Storage Layer)
   - 文件系统
   - 数据文件
   - 日志文件
*/

存储引擎对比分析

// StorageEngineAnalyzer.java - 存储引擎性能分析工具
package com.mysql.optimization;

import java.sql.*;
import java.util.*;
import java.util.concurrent.TimeUnit;

public class StorageEngineAnalyzer {
    
    private final Connection connection;
    private final Map<String, EngineMetrics> engineMetrics;
    
    public StorageEngineAnalyzer(Connection connection) {
        this.connection = connection;
        this.engineMetrics = new HashMap<>();
    }
    
    // 存储引擎性能指标
    public static class EngineMetrics {
        private String engineName;
        private long insertTime;
        private long selectTime;
        private long updateTime;
        private long deleteTime;
        private long storageSize;
        private boolean supportsTransactions;
        private boolean supportsFulltext;
        private boolean supportsForeignKeys;
        
        // 构造函数和getter/setter
        public EngineMetrics(String engineName) {
            this.engineName = engineName;
        }
        
        public void setInsertTime(long insertTime) { this.insertTime = insertTime; }
        public void setSelectTime(long selectTime) { this.selectTime = selectTime; }
        public void setUpdateTime(long updateTime) { this.updateTime = updateTime; }
        public void setDeleteTime(long deleteTime) { this.deleteTime = deleteTime; }
        public void setStorageSize(long storageSize) { this.storageSize = storageSize; }
        public void setSupportsTransactions(boolean supportsTransactions) { 
            this.supportsTransactions = supportsTransactions; 
        }
        public void setSupportsFulltext(boolean supportsFulltext) { 
            this.supportsFulltext = supportsFulltext; 
        }
        public void setSupportsForeignKeys(boolean supportsForeignKeys) { 
            this.supportsForeignKeys = supportsForeignKeys; 
        }
        
        @Override
        public String toString() {
            return String.format(
                "Engine: %s\n" +
                "Insert Time: %d ms\n" +
                "Select Time: %d ms\n" +
                "Update Time: %d ms\n" +
                "Delete Time: %d ms\n" +
                "Storage Size: %d bytes\n" +
                "Transactions: %s\n" +
                "Fulltext: %s\n" +
                "Foreign Keys: %s\n",
                engineName, insertTime, selectTime, updateTime, deleteTime,
                storageSize, supportsTransactions, supportsFulltext, supportsForeignKeys
            );
        }
    }
    
    // 创建测试表
    public void createTestTables() throws SQLException {
        String[] engines = {"InnoDB", "MyISAM", "Memory"};
        
        for (String engine : engines) {
            String createTableSQL = String.format("""
                CREATE TABLE IF NOT EXISTS test_table_%s (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(100) NOT NULL,
                    email VARCHAR(100),
                    age INT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_name (name),
                    INDEX idx_email (email)
                ) ENGINE=%s
                """, engine.toLowerCase(), engine);
            
            try (Statement stmt = connection.createStatement()) {
                stmt.execute("DROP TABLE IF EXISTS test_table_" + engine.toLowerCase());
                stmt.execute(createTableSQL);
            }
        }
    }
    
    // 性能测试
    public void performanceTest(int recordCount) throws SQLException {
        String[] engines = {"innodb", "myisam", "memory"};
        
        for (String engine : engines) {
            EngineMetrics metrics = new EngineMetrics(engine.toUpperCase());
            
            // 插入性能测试
            metrics.setInsertTime(testInsertPerformance(engine, recordCount));
            
            // 查询性能测试
            metrics.setSelectTime(testSelectPerformance(engine));
            
            // 更新性能测试
            metrics.setUpdateTime(testUpdatePerformance(engine));
            
            // 删除性能测试
            metrics.setDeleteTime(testDeletePerformance(engine));
            
            // 存储大小
            metrics.setStorageSize(getTableSize(engine));
            
            // 功能支持检查
            checkEngineFeatures(metrics, engine);
            
            engineMetrics.put(engine, metrics);
        }
    }
    
    private long testInsertPerformance(String engine, int recordCount) throws SQLException {
        String insertSQL = String.format(
            "INSERT INTO test_table_%s (name, email, age) VALUES (?, ?, ?)", engine);
        
        long startTime = System.currentTimeMillis();
        
        try (PreparedStatement pstmt = connection.prepareStatement(insertSQL)) {
            connection.setAutoCommit(false);
            
            for (int i = 1; i <= recordCount; i++) {
                pstmt.setString(1, "User" + i);
                pstmt.setString(2, "user" + i + "@example.com");
                pstmt.setInt(3, 20 + (i % 50));
                pstmt.addBatch();
                
                if (i % 1000 == 0) {
                    pstmt.executeBatch();
                }
            }
            
            pstmt.executeBatch();
            connection.commit();
            connection.setAutoCommit(true);
        }
        
        return System.currentTimeMillis() - startTime;
    }
    
    private long testSelectPerformance(String engine) throws SQLException {
        String selectSQL = String.format(
            "SELECT * FROM test_table_%s WHERE name LIKE ? AND age > ?", engine);
        
        long startTime = System.currentTimeMillis();
        
        try (PreparedStatement pstmt = connection.prepareStatement(selectSQL)) {
            for (int i = 0; i < 1000; i++) {
                pstmt.setString(1, "User%");
                pstmt.setInt(2, 25);
                
                try (ResultSet rs = pstmt.executeQuery()) {
                    while (rs.next()) {
                        // 模拟数据处理
                        rs.getString("name");
                        rs.getString("email");
                        rs.getInt("age");
                    }
                }
            }
        }
        
        return System.currentTimeMillis() - startTime;
    }
    
    private long testUpdatePerformance(String engine) throws SQLException {
        String updateSQL = String.format(
            "UPDATE test_table_%s SET age = age + 1 WHERE id BETWEEN ? AND ?", engine);
        
        long startTime = System.currentTimeMillis();
        
        try (PreparedStatement pstmt = connection.prepareStatement(updateSQL)) {
            connection.setAutoCommit(false);
            
            for (int i = 0; i < 100; i++) {
                int start = i * 100 + 1;
                int end = (i + 1) * 100;
                
                pstmt.setInt(1, start);
                pstmt.setInt(2, end);
                pstmt.executeUpdate();
            }
            
            connection.commit();
            connection.setAutoCommit(true);
        }
        
        return System.currentTimeMillis() - startTime;
    }
    
    private long testDeletePerformance(String engine) throws SQLException {
        String deleteSQL = String.format(
            "DELETE FROM test_table_%s WHERE id > ?", engine);
        
        long startTime = System.currentTimeMillis();
        
        try (PreparedStatement pstmt = connection.prepareStatement(deleteSQL)) {
            pstmt.setInt(1, 9000);
            pstmt.executeUpdate();
        }
        
        return System.currentTimeMillis() - startTime;
    }
    
    private long getTableSize(String engine) throws SQLException {
        String sizeSQL = """
            SELECT 
                ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
            FROM information_schema.tables 
            WHERE table_schema = DATABASE() 
            AND table_name = ?
            """;
        
        try (PreparedStatement pstmt = connection.prepareStatement(sizeSQL)) {
            pstmt.setString(1, "test_table_" + engine);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return (long) (rs.getDouble("size_mb") * 1024 * 1024);
                }
            }
        }
        
        return 0;
    }
    
    private void checkEngineFeatures(EngineMetrics metrics, String engine) throws SQLException {
        String engineSQL = "SELECT * FROM information_schema.engines WHERE engine = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(engineSQL)) {
            pstmt.setString(1, engine.toUpperCase());
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    String transactions = rs.getString("TRANSACTIONS");
                    String xa = rs.getString("XA");
                    String savepoints = rs.getString("SAVEPOINTS");
                    
                    metrics.setSupportsTransactions("YES".equals(transactions));
                    // MyISAM不支持事务，InnoDB支持
                    // Memory引擎支持事务但不持久化
                }
            }
        }
        
        // 检查全文索引支持
        if ("myisam".equals(engine) || "innodb".equals(engine)) {
            metrics.setSupportsFulltext(true);
        }
        
        // 检查外键支持
        if ("innodb".equals(engine)) {
            metrics.setSupportsForeignKeys(true);
        }
    }
    
    // 生成性能报告
    public void generateReport() {
        System.out.println("=== MySQL存储引擎性能对比报告 ===\n");
        
        for (EngineMetrics metrics : engineMetrics.values()) {
            System.out.println(metrics);
            System.out.println("----------------------------------------");
        }
        
        // 推荐建议
        System.out.println("=== 使用建议 ===");
        System.out.println("InnoDB: 推荐用于OLTP系统，支持事务、外键、行级锁");
        System.out.println("MyISAM: 适合读多写少的场景，表级锁，不支持事务");
        System.out.println("Memory: 适合临时数据、缓存，数据存储在内存中");
    }
    
    // 清理测试数据
    public void cleanup() throws SQLException {
        String[] engines = {"innodb", "myisam", "memory"};
        
        try (Statement stmt = connection.createStatement()) {
            for (String engine : engines) {
                stmt.execute("DROP TABLE IF EXISTS test_table_" + engine);
            }
        }
    }
}

2. InnoDB存储引擎深度优化

缓冲池优化

-- InnoDB缓冲池配置优化
-- my.cnf配置文件

[mysqld]
# 缓冲池大小 (建议设置为物理内存的70-80%)
innodb_buffer_pool_size = 8G

# 缓冲池实例数 (建议设置为CPU核心数，最大64)
innodb_buffer_pool_instances = 8

# 缓冲池预热
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

# 缓冲池状态监控
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE
FROM performance_schema.global_status 
WHERE VARIABLE_NAME LIKE 'Innodb_buffer_pool%'
ORDER BY VARIABLE_NAME;

-- 缓冲池命中率计算
SELECT 
    ROUND(
        (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100, 2
    ) AS buffer_pool_hit_ratio
FROM (
    SELECT 
        VARIABLE_VALUE AS Innodb_buffer_pool_reads
    FROM performance_schema.global_status 
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) AS reads
CROSS JOIN (
    SELECT 
        VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
    FROM performance_schema.global_status 
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) AS requests;

事务日志优化

-- 事务日志配置
[mysqld]
# 日志文件大小 (建议设置为缓冲池大小的25%)
innodb_log_file_size = 2G

# 日志文件数量
innodb_log_files_in_group = 2

# 日志缓冲区大小
innodb_log_buffer_size = 64M

# 日志刷新策略
# 0: 每秒刷新一次 (性能最好，但可能丢失1秒数据)
# 1: 每次事务提交都刷新 (最安全，性能较差)
# 2: 每次事务提交写入OS缓存，每秒刷新到磁盘 (折中方案)
innodb_flush_log_at_trx_commit = 1

-- 监控事务日志状态
SHOW ENGINE INNODB STATUS\G

-- 查看日志相关状态
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE
FROM performance_schema.global_status 
WHERE VARIABLE_NAME LIKE '%log%'
AND VARIABLE_NAME LIKE 'Innodb%'
ORDER BY VARIABLE_NAME;

🔍 索引设计与优化

1. 索引类型与选择策略

// IndexOptimizer.java - 索引优化分析工具
package com.mysql.optimization;

import java.sql.*;
import java.util.*;
import java.util.regex.Pattern;

public class IndexOptimizer {
    
    private final Connection connection;
    private final String databaseName;
    
    public IndexOptimizer(Connection connection, String databaseName) {
        this.connection = connection;
        this.databaseName = databaseName;
    }
    
    // 索引分析结果
    public static class IndexAnalysis {
        private String tableName;
        private String indexName;
        private String columnName;
        private long cardinality;
        private String indexType;
        private boolean isUnique;
        private double selectivity;
        private long indexSize;
        private String recommendation;
        
        // 构造函数和getter/setter
        public IndexAnalysis(String tableName, String indexName, String columnName) {
            this.tableName = tableName;
            this.indexName = indexName;
            this.columnName = columnName;
        }
        
        // getter和setter方法
        public void setCardinality(long cardinality) { this.cardinality = cardinality; }
        public void setIndexType(String indexType) { this.indexType = indexType; }
        public void setUnique(boolean unique) { isUnique = unique; }
        public void setSelectivity(double selectivity) { this.selectivity = selectivity; }
        public void setIndexSize(long indexSize) { this.indexSize = indexSize; }
        public void setRecommendation(String recommendation) { this.recommendation = recommendation; }
        
        @Override
        public String toString() {
            return String.format(
                "Table: %s, Index: %s, Column: %s\n" +
                "Cardinality: %d, Type: %s, Unique: %s\n" +
                "Selectivity: %.4f, Size: %d bytes\n" +
                "Recommendation: %s\n",
                tableName, indexName, columnName, cardinality, indexType, 
                isUnique, selectivity, indexSize, recommendation
            );
        }
    }
    
    // 分析所有表的索引
    public List<IndexAnalysis> analyzeAllIndexes() throws SQLException {
        List<IndexAnalysis> analyses = new ArrayList<>();
        
        // 获取所有表
        List<String> tables = getAllTables();
        
        for (String table : tables) {
            analyses.addAll(analyzeTableIndexes(table));
        }
        
        return analyses;
    }
    
    private List<String> getAllTables() throws SQLException {
        List<String> tables = new ArrayList<>();
        
        String sql = """
            SELECT table_name 
            FROM information_schema.tables 
            WHERE table_schema = ? 
            AND table_type = 'BASE TABLE'
            """;
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, databaseName);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    tables.add(rs.getString("table_name"));
                }
            }
        }
        
        return tables;
    }
    
    private List<IndexAnalysis> analyzeTableIndexes(String tableName) throws SQLException {
        List<IndexAnalysis> analyses = new ArrayList<>();
        
        // 获取表的索引信息
        String indexSQL = """
            SELECT 
                s.index_name,
                s.column_name,
                s.cardinality,
                s.non_unique,
                i.index_type
            FROM information_schema.statistics s
            JOIN information_schema.statistics i ON s.index_name = i.index_name 
                AND s.table_name = i.table_name
            WHERE s.table_schema = ? 
            AND s.table_name = ?
            ORDER BY s.index_name, s.seq_in_index
            """;
        
        try (PreparedStatement pstmt = connection.prepareStatement(indexSQL)) {
            pstmt.setString(1, databaseName);
            pstmt.setString(2, tableName);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    IndexAnalysis analysis = new IndexAnalysis(
                        tableName,
                        rs.getString("index_name"),
                        rs.getString("column_name")
                    );
                    
                    analysis.setCardinality(rs.getLong("cardinality"));
                    analysis.setIndexType(rs.getString("index_type"));
                    analysis.setUnique(rs.getInt("non_unique") == 0);
                    
                    // 计算选择性
                    double selectivity = calculateSelectivity(tableName, rs.getString("column_name"));
                    analysis.setSelectivity(selectivity);
                    
                    // 获取索引大小
                    long indexSize = getIndexSize(tableName, rs.getString("index_name"));
                    analysis.setIndexSize(indexSize);
                    
                    // 生成建议
                    String recommendation = generateRecommendation(analysis);
                    analysis.setRecommendation(recommendation);
                    
                    analyses.add(analysis);
                }
            }
        }
        
        return analyses;
    }
    
    private double calculateSelectivity(String tableName, String columnName) throws SQLException {
        String sql = String.format("""
            SELECT 
                COUNT(DISTINCT %s) / COUNT(*) as selectivity
            FROM %s
            """, columnName, tableName);
        
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            if (rs.next()) {
                return rs.getDouble("selectivity");
            }
        } catch (SQLException e) {
            // 如果计算失败，返回默认值
            return 0.0;
        }
        
        return 0.0;
    }
    
    private long getIndexSize(String tableName, String indexName) throws SQLException {
        String sql = """
            SELECT 
                ROUND(((index_length) / 1024 / 1024), 2) AS index_size_mb
            FROM information_schema.tables 
            WHERE table_schema = ? 
            AND table_name = ?
            """;
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, databaseName);
            pstmt.setString(2, tableName);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return (long) (rs.getDouble("index_size_mb") * 1024 * 1024);
                }
            }
        }
        
        return 0;
    }
    
    private String generateRecommendation(IndexAnalysis analysis) {
        StringBuilder recommendation = new StringBuilder();
        
        // 选择性分析
        if (analysis.selectivity < 0.01) {
            recommendation.append("选择性过低(").append(String.format("%.4f", analysis.selectivity))
                         .append(")，考虑删除此索引; ");
        } else if (analysis.selectivity > 0.95) {
            recommendation.append("选择性很高，适合作为主键或唯一索引; ");
        }
        
        // 基数分析
        if (analysis.cardinality < 100) {
            recommendation.append("基数较低，可能不需要索引; ");
        }
        
        // 索引大小分析
        if (analysis.indexSize > 100 * 1024 * 1024) { // 100MB
            recommendation.append("索引过大，考虑优化或分区; ");
        }
        
        // 索引类型建议
        if ("BTREE".equals(analysis.indexType)) {
            recommendation.append("B-Tree索引适合范围查询; ");
        }
        
        if (recommendation.length() == 0) {
            recommendation.append("索引配置合理");
        }
        
        return recommendation.toString();
    }
    
    // 检测冗余索引
    public List<String> findRedundantIndexes() throws SQLException {
        List<String> redundantIndexes = new ArrayList<>();
        
        String sql = """
            SELECT 
                table_name,
                GROUP_CONCAT(DISTINCT index_name) as indexes,
                GROUP_CONCAT(column_name ORDER BY seq_in_index) as columns
            FROM information_schema.statistics 
            WHERE table_schema = ?
            AND index_name != 'PRIMARY'
            GROUP BY table_name, index_name
            HAVING COUNT(*) > 1
            """;
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, databaseName);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    String tableName = rs.getString("table_name");
                    String indexes = rs.getString("indexes");
                    String columns = rs.getString("columns");
                    
                    redundantIndexes.add(String.format(
                        "Table: %s, Indexes: %s, Columns: %s", 
                        tableName, indexes, columns
                    ));
                }
            }
        }
        
        return redundantIndexes;
    }
    
    // 检测未使用的索引
    public List<String> findUnusedIndexes() throws SQLException {
        List<String> unusedIndexes = new ArrayList<>();
        
        // 需要开启performance_schema
        String sql = """
            SELECT 
                object_schema,
                object_name,
                index_name
            FROM performance_schema.table_io_waits_summary_by_index_usage 
            WHERE index_name IS NOT NULL
            AND index_name != 'PRIMARY'
            AND count_star = 0
            AND object_schema = ?
            """;
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, databaseName);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    unusedIndexes.add(String.format(
                        "Schema: %s, Table: %s, Index: %s",
                        rs.getString("object_schema"),
                        rs.getString("object_name"),
                        rs.getString("index_name")
                    ));
                }
            }
        } catch (SQLException e) {
            // performance_schema可能未启用
            unusedIndexes.add("无法检测未使用索引：performance_schema未启用或无权限");
        }
        
        return unusedIndexes;
    }
    
    // 生成索引优化建议
    public void generateIndexOptimizationReport() throws SQLException {
        System.out.println("=== MySQL索引优化报告 ===\n");
        
        // 分析所有索引
        List<IndexAnalysis> analyses = analyzeAllIndexes();
        System.out.println("1. 索引分析结果:");
        for (IndexAnalysis analysis : analyses) {
            System.out.println(analysis);
        }
        
        // 检测冗余索引
        List<String> redundantIndexes = findRedundantIndexes();
        System.out.println("2. 冗余索引:");
        if (redundantIndexes.isEmpty()) {
            System.out.println("未发现冗余索引");
        } else {
            redundantIndexes.forEach(System.out::println);
        }
        
        // 检测未使用索引
        List<String> unusedIndexes = findUnusedIndexes();
        System.out.println("\n3. 未使用索引:");
        if (unusedIndexes.isEmpty()) {
            System.out.println("未发现未使用索引");
        } else {
            unusedIndexes.forEach(System.out::println);
        }
        
        System.out.println("\n=== 优化建议 ===");
        System.out.println("1. 删除选择性低于1%的索引");
        System.out.println("2. 合并冗余的复合索引");
        System.out.println("3. 为高频查询字段添加索引");
        System.out.println("4. 定期监控索引使用情况");
    }
}

2. 复合索引设计原则

-- 复合索引设计示例
CREATE TABLE user_orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    order_status TINYINT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 错误的索引设计 (违反最左前缀原则)
-- CREATE INDEX idx_wrong ON user_orders (order_date, user_id, order_status);

-- 正确的索引设计 (遵循最左前缀原则)
-- 1. 根据查询频率和选择性排序
CREATE INDEX idx_user_status_date ON user_orders (user_id, order_status, order_date);

-- 2. 覆盖索引设计 (包含查询所需的所有字段)
CREATE INDEX idx_covering ON user_orders (user_id, order_status, order_date, amount);

-- 查询优化示例
-- 能够使用索引的查询
EXPLAIN SELECT * FROM user_orders 
WHERE user_id = 12345 
AND order_status = 1 
AND order_date >= '2025-01-01';

-- 部分使用索引的查询 (只能使用user_id部分)
EXPLAIN SELECT * FROM user_orders 
WHERE user_id = 12345 
AND order_date >= '2025-01-01';

-- 无法使用索引的查询 (违反最左前缀)
EXPLAIN SELECT * FROM user_orders 
WHERE order_status = 1 
AND order_date >= '2025-01-01';

-- 索引选择性分析
SELECT 
    COUNT(DISTINCT user_id) / COUNT(*) AS user_id_selectivity,
    COUNT(DISTINCT order_status) / COUNT(*) AS status_selectivity,
    COUNT(DISTINCT order_date) / COUNT(*) AS date_selectivity
FROM user_orders;

-- 索引使用情况监控
SELECT 
    table_schema,
    table_name,
    index_name,
    count_read,
    count_write,
    count_fetch,
    count_insert,
    count_update,
    count_delete
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE table_schema = 'your_database'
ORDER BY count_read DESC;

🚀 查询优化技巧

1. SQL查询优化器

// QueryOptimizer.java - 查询优化分析工具
package com.mysql.optimization;

import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class QueryOptimizer {
    
    private final Connection connection;
    
    public QueryOptimizer(Connection connection) {
        this.connection = connection;
    }
    
    // 查询执行计划分析
    public static class ExecutionPlan {
        private int id;
        private String selectType;
        private String table;
        private String partitions;
        private String type;
        private String possibleKeys;
        private String key;
        private String keyLen;
        private String ref;
        private long rows;
        private double filtered;
        private String extra;
        private String optimizationSuggestion;
        
        // 构造函数和getter/setter
        public ExecutionPlan() {}
        
        public void setId(int id) { this.id = id; }
        public void setSelectType(String selectType) { this.selectType = selectType; }
        public void setTable(String table) { this.table = table; }
        public void setPartitions(String partitions) { this.partitions = partitions; }
        public void setType(String type) { this.type = type; }
        public void setPossibleKeys(String possibleKeys) { this.possibleKeys = possibleKeys; }
        public void setKey(String key) { this.key = key; }
        public void setKeyLen(String keyLen) { this.keyLen = keyLen; }
        public void setRef(String ref) { this.ref = ref; }
        public void setRows(long rows) { this.rows = rows; }
        public void setFiltered(double filtered) { this.filtered = filtered; }
        public void setExtra(String extra) { this.extra = extra; }
        public void setOptimizationSuggestion(String optimizationSuggestion) { 
            this.optimizationSuggestion = optimizationSuggestion; 
        }
        
        @Override
        public String toString() {
            return String.format(
                "ID: %d, Type: %s, Table: %s\n" +
                "Access Type: %s, Key: %s, Rows: %d\n" +
                "Filtered: %.2f%%, Extra: %s\n" +
                "Suggestion: %s\n",
                id, selectType, table, type, key, rows, 
                filtered, extra, optimizationSuggestion
            );
        }
    }
    
    // 分析查询执行计划
    public List<ExecutionPlan> analyzeQuery(String sql) throws SQLException {
        List<ExecutionPlan> plans = new ArrayList<>();
        
        // 执行EXPLAIN
        String explainSQL = "EXPLAIN FORMAT=JSON " + sql;
        
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(explainSQL)) {
            
            if (rs.next()) {
                String jsonPlan = rs.getString(1);
                // 这里可以解析JSON格式的执行计划
                // 为简化示例，使用传统的EXPLAIN
            }
        }
        
        // 使用传统EXPLAIN格式
        String traditionalExplain = "EXPLAIN " + sql;
        
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(traditionalExplain)) {
            
            while (rs.next()) {
                ExecutionPlan plan = new ExecutionPlan();
                
                plan.setId(rs.getInt("id"));
                plan.setSelectType(rs.getString("select_type"));
                plan.setTable(rs.getString("table"));
                plan.setPartitions(rs.getString("partitions"));
                plan.setType(rs.getString("type"));
                plan.setPossibleKeys(rs.getString("possible_keys"));
                plan.setKey(rs.getString("key"));
                plan.setKeyLen(rs.getString("key_len"));
                plan.setRef(rs.getString("ref"));
                plan.setRows(rs.getLong("rows"));
                plan.setFiltered(rs.getDouble("filtered"));
                plan.setExtra(rs.getString("Extra"));
                
                // 生成优化建议
                String suggestion = generateOptimizationSuggestion(plan);
                plan.setOptimizationSuggestion(suggestion);
                
                plans.add(plan);
            }
        }
        
        return plans;
    }
    
    private String generateOptimizationSuggestion(ExecutionPlan plan) {
        StringBuilder suggestions = new StringBuilder();
        
        // 访问类型分析
        switch (plan.type) {
            case "ALL":
                suggestions.append("全表扫描，建议添加索引; ");
                break;
            case "index":
                suggestions.append("索引扫描，考虑优化WHERE条件; ");
                break;
            case "range":
                suggestions.append("范围扫描，性能较好; ");
                break;
            case "ref":
                suggestions.append("索引查找，性能良好; ");
                break;
            case "eq_ref":
                suggestions.append("唯一索引查找，性能最佳; ");
                break;
            case "const":
                suggestions.append("常量查找，性能最优; ");
                break;
        }
        
        // 扫描行数分析
        if (plan.rows > 10000) {
            suggestions.append("扫描行数过多(").append(plan.rows).append(")，需要优化; ");
        }
        
        // 过滤率分析
        if (plan.filtered < 10.0) {
            suggestions.append("过滤率过低(").append(String.format("%.2f", plan.filtered))
                       .append("%)，考虑添加更精确的索引; ");
        }
        
        // Extra信息分析
        if (plan.extra != null) {
            if (plan.extra.contains("Using filesort")) {
                suggestions.append("使用文件排序，考虑添加ORDER BY索引; ");
            }
            if (plan.extra.contains("Using temporary")) {
                suggestions.append("使用临时表，考虑优化GROUP BY或DISTINCT; ");
            }
            if (plan.extra.contains("Using where")) {
                suggestions.append("使用WHERE过滤，考虑优化索引覆盖; ");
            }
            if (plan.extra.contains("Using index")) {
                suggestions.append("使用覆盖索引，性能良好; ");
            }
        }
        
        if (suggestions.length() == 0) {
            suggestions.append("查询优化良好");
        }
        
        return suggestions.toString();
    }
    
    // 慢查询分析
    public List<SlowQuery> analyzeSlowQueries() throws SQLException {
        List<SlowQuery> slowQueries = new ArrayList<>();
        
        // 开启慢查询日志
        try (Statement stmt = connection.createStatement()) {
            stmt.execute("SET GLOBAL slow_query_log = 'ON'");
            stmt.execute("SET GLOBAL long_query_time = 1"); // 1秒以上的查询
        }
        
        // 从performance_schema获取慢查询信息
        String sql = """
            SELECT 
                digest_text,
                count_star,
                sum_timer_wait/1000000000000 as total_time_sec,
                avg_timer_wait/1000000000000 as avg_time_sec,
                sum_rows_examined,
                sum_rows_sent
            FROM performance_schema.events_statements_summary_by_digest 
            WHERE avg_timer_wait > 1000000000000  -- 1秒以上
            ORDER BY sum_timer_wait DESC 
            LIMIT 20
            """;
        
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                SlowQuery slowQuery = new SlowQuery();
                slowQuery.setQuery(rs.getString("digest_text"));
                slowQuery.setExecutionCount(rs.getLong("count_star"));
                slowQuery.setTotalTime(rs.getDouble("total_time_sec"));
                slowQuery.setAverageTime(rs.getDouble("avg_time_sec"));
                slowQuery.setRowsExamined(rs.getLong("sum_rows_examined"));
                slowQuery.setRowsSent(rs.getLong("sum_rows_sent"));
                
                slowQueries.add(slowQuery);
            }
        }
        
        return slowQueries;
    }
    
    public static class SlowQuery {
        private String query;
        private long executionCount;
        private double totalTime;
        private double averageTime;
        private long rowsExamined;
        private long rowsSent;
        
        // getter和setter方法
        public void setQuery(String query) { this.query = query; }
        public void setExecutionCount(long executionCount) { this.executionCount = executionCount; }
        public void setTotalTime(double totalTime) { this.totalTime = totalTime; }
        public void setAverageTime(double averageTime) { this.averageTime = averageTime; }
        public void setRowsExamined(long rowsExamined) { this.rowsExamined = rowsExamined; }
        public void setRowsSent(long rowsSent) { this.rowsSent = rowsSent; }
        
        @Override
        public String toString() {
            return String.format(
                "Query: %s\n" +
                "Execution Count: %d, Total Time: %.2fs, Avg Time: %.2fs\n" +
                "Rows Examined: %d, Rows Sent: %d\n",
                query, executionCount, totalTime, averageTime, rowsExamined, rowsSent
            );
        }
    }
    
    // 查询重写建议
    public String suggestQueryRewrite(String originalQuery) {
        StringBuilder suggestions = new StringBuilder();
        String query = originalQuery.toLowerCase().trim();
        
        // 检查SELECT *
        if (query.contains("select *")) {
            suggestions.append("避免使用SELECT *，明确指定需要的字段; ");
        }
        
        // 检查子查询
        if (query.contains("in (select") || query.contains("exists (select")) {
            suggestions.append("考虑将子查询改写为JOIN; ");
        }
        
        // 检查LIKE前缀通配符
        if (query.contains("like '%")) {
            suggestions.append("避免LIKE前缀通配符，无法使用索引; ");
        }
        
        // 检查函数使用
        Pattern functionPattern = Pattern.compile("where\\s+\\w+\\s*\\(");
        if (functionPattern.matcher(query).find()) {
            suggestions.append("避免在WHERE子句中使用函数，影响索引使用; ");
        }
        
        // 检查OR条件
        if (query.contains(" or ")) {
            suggestions.append("考虑将OR条件改写为UNION; ");
        }
        
        // 检查ORDER BY RAND()
        if (query.contains("order by rand()")) {
            suggestions.append("避免使用ORDER BY RAND()，性能极差; ");
        }
        
        if (suggestions.length() == 0) {
            suggestions.append("查询语句结构良好");
        }
        
        return suggestions.toString();
    }
    
    // 生成查询优化报告
    public void generateQueryOptimizationReport(String sql) throws SQLException {
        System.out.println("=== 查询优化分析报告 ===\n");
        
        System.out.println("原始查询:");
        System.out.println(sql);
        System.out.println();
        
        // 执行计划分析
        List<ExecutionPlan> plans = analyzeQuery(sql);
        System.out.println("执行计划分析:");
        for (ExecutionPlan plan : plans) {
            System.out.println(plan);
        }
        
        // 查询重写建议
        String rewriteSuggestions = suggestQueryRewrite(sql);
        System.out.println("查询重写建议:");
        System.out.println(rewriteSuggestions);
        
        // 慢查询分析
        List<SlowQuery> slowQueries = analyzeSlowQueries();
        System.out.println("\n慢查询TOP 10:");
        slowQueries.stream().limit(10).forEach(System.out::println);
    }
}

2. 高级查询优化技巧

-- 查询优化实战案例

-- 1. 分页查询优化
-- 传统分页 (性能差，OFFSET越大越慢)
SELECT * FROM users 
ORDER BY id 
LIMIT 1000000, 20;

-- 优化后的分页 (使用游标)
SELECT * FROM users 
WHERE id > 1000000 
ORDER BY id 
LIMIT 20;

-- 2. COUNT查询优化
-- 避免COUNT(*)在大表上的使用
-- 使用近似值
SELECT table_rows 
FROM information_schema.tables 
WHERE table_schema = 'your_db' 
AND table_name = 'your_table';

-- 3. EXISTS vs IN 优化
-- 当子查询结果集较小时，使用IN
SELECT * FROM orders o
WHERE o.user_id IN (
    SELECT u.id FROM users u WHERE u.status = 'active'
);

-- 当子查询结果集较大时，使用EXISTS
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM users u 
    WHERE u.id = o.user_id AND u.status = 'active'
);

-- 4. JOIN优化
-- 小表驱动大表
SELECT /*+ USE_INDEX(o, idx_user_id) */ 
    o.*, u.name
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
AND o.order_date >= '2025-01-01';

-- 5. 窗口函数优化
-- 使用窗口函数替代子查询
SELECT 
    user_id,
    order_date,
    amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) as rn
FROM orders
HAVING rn <= 3;  -- 每个用户最近3个订单

-- 6. 临时表优化
-- 使用CTE替代临时表
WITH monthly_sales AS (
    SELECT 
        DATE_FORMAT(order_date, '%Y-%m') as month,
        SUM(amount) as total_amount
    FROM orders
    WHERE order_date >= '2025-01-01'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
),
avg_sales AS (
    SELECT AVG(total_amount) as avg_amount
    FROM monthly_sales
)
SELECT 
    ms.month,
    ms.total_amount,
    ms.total_amount - avg_s.avg_amount as diff_from_avg
FROM monthly_sales ms
CROSS JOIN avg_sales avg_s
ORDER BY ms.month;

-- 7. 批量操作优化
-- 批量插入
INSERT INTO user_logs (user_id, action, created_at)
VALUES 
    (1, 'login', NOW()),
    (2, 'logout', NOW()),
    (3, 'purchase', NOW())
ON DUPLICATE KEY UPDATE
    action = VALUES(action),
    created_at = VALUES(created_at);

-- 批量更新
UPDATE users u
JOIN (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    WHERE order_date >= '2025-01-01'
    GROUP BY user_id
) o ON u.id = o.user_id
SET u.order_count = o.order_count;

⚙️ MySQL配置优化

1. 服务器参数调优

# my.cnf - MySQL配置优化
[mysqld]
# 基础配置
port = 3306
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
datadir = /var/lib/mysql
tmpdir = /tmp

# 字符集配置
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'

# 连接配置
max_connections = 1000              # 最大连接数
max_connect_errors = 100000         # 最大连接错误数
connect_timeout = 60                # 连接超时时间
wait_timeout = 28800               # 等待超时时间
interactive_timeout = 28800         # 交互超时时间
max_allowed_packet = 256M           # 最大包大小

# 缓冲区配置
key_buffer_size = 256M              # MyISAM索引缓冲区
sort_buffer_size = 4M               # 排序缓冲区
read_buffer_size = 2M               # 顺序读缓冲区
read_rnd_buffer_size = 8M           # 随机读缓冲区
join_buffer_size = 8M               # JOIN缓冲区
thread_cache_size = 64              # 线程缓存

# InnoDB配置
innodb_buffer_pool_size = 8G        # 缓冲池大小 (物理内存的70-80%)
innodb_buffer_pool_instances = 8    # 缓冲池实例数
innodb_log_file_size = 2G           # 日志文件大小
innodb_log_files_in_group = 2       # 日志文件数量
innodb_log_buffer_size = 64M        # 日志缓冲区大小
innodb_flush_log_at_trx_commit = 1  # 日志刷新策略
innodb_flush_method = O_DIRECT      # 刷新方法
innodb_file_per_table = 1           # 独立表空间
innodb_open_files = 65535           # 打开文件数限制
innodb_io_capacity = 2000           # IO容量
innodb_io_capacity_max = 4000       # 最大IO容量
innodb_read_io_threads = 8          # 读IO线程数
innodb_write_io_threads = 8         # 写IO线程数
innodb_thread_concurrency = 0       # 线程并发数 (0表示不限制)
innodb_lock_wait_timeout = 120      # 锁等待超时时间

# 查询缓存 (MySQL 8.0已移除)
# query_cache_type = 1
# query_cache_size = 256M
# query_cache_limit = 2M

# 临时表配置
tmp_table_size = 256M               # 内存临时表大小
max_heap_table_size = 256M          # 堆表最大大小

# 二进制日志配置
log-bin = mysql-bin                 # 启用二进制日志
binlog_format = ROW                 # 二进制日志格式
binlog_cache_size = 2M              # 二进制日志缓存
max_binlog_size = 1G                # 最大二进制日志大小
expire_logs_days = 7                # 日志保留天数
sync_binlog = 1                     # 同步二进制日志

# 慢查询日志
slow_query_log = 1                  # 启用慢查询日志
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1                 # 慢查询阈值 (秒)
log_queries_not_using_indexes = 1   # 记录未使用索引的查询

# 错误日志
log-error = /var/log/mysql/error.log

# 性能监控
performance_schema = ON             # 启用性能监控
performance_schema_max_table_instances = 12500
performance_schema_max_table_handles = 4000

# 安全配置
skip-name-resolve                   # 跳过DNS解析
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

2. 性能监控与调优工具

// MySQLMonitor.java - MySQL性能监控工具
package com.mysql.optimization;

import java.sql.*;
import java.util.*;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;

public class MySQLMonitor {
    
    private final Connection connection;
    private final ScheduledExecutorService scheduler;
    private final Map<String, Double> previousMetrics;
    
    public MySQLMonitor(Connection connection) {
        this.connection = connection;
        this.scheduler = Executors.newScheduledThreadPool(2);
        this.previousMetrics = new HashMap<>();
    }
    
    // 性能指标
    public static class PerformanceMetrics {
        private double qps;              // 每秒查询数
        private double tps;              // 每秒事务数
        private double connectionUsage;   // 连接使用率
        private double bufferPoolHitRatio; // 缓冲池命中率
        private double slowQueryRatio;    // 慢查询比率
        private long activeConnections;   // 活跃连接数
        private long threadsRunning;      // 运行线程数
        private long innodbRowsRead;      // InnoDB读取行数
        private long innodbRowsInserted;  // InnoDB插入行数
        private long innodbRowsUpdated;   // InnoDB更新行数
        private long innodbRowsDeleted;   // InnoDB删除行数
        
        // getter和setter方法
        public void setQps(double qps) { this.qps = qps; }
        public void setTps(double tps) { this.tps = tps; }
        public void setConnectionUsage(double connectionUsage) { this.connectionUsage = connectionUsage; }
        public void setBufferPoolHitRatio(double bufferPoolHitRatio) { this.bufferPoolHitRatio = bufferPoolHitRatio; }
        public void setSlowQueryRatio(double slowQueryRatio) { this.slowQueryRatio = slowQueryRatio; }
        public void setActiveConnections(long activeConnections) { this.activeConnections = activeConnections; }
        public void setThreadsRunning(long threadsRunning) { this.threadsRunning = threadsRunning; }
        public void setInnodbRowsRead(long innodbRowsRead) { this.innodbRowsRead = innodbRowsRead; }
        public void setInnodbRowsInserted(long innodbRowsInserted) { this.innodbRowsInserted = innodbRowsInserted; }
        public void setInnodbRowsUpdated(long innodbRowsUpdated) { this.innodbRowsUpdated = innodbRowsUpdated; }
        public void setInnodbRowsDeleted(long innodbRowsDeleted) { this.innodbRowsDeleted = innodbRowsDeleted; }
        
        @Override
        public String toString() {
            return String.format(
                "QPS: %.2f, TPS: %.2f\n" +
                "Connection Usage: %.2f%%, Buffer Pool Hit Ratio: %.2f%%\n" +
                "Slow Query Ratio: %.2f%%, Active Connections: %d\n" +
                "Threads Running: %d\n" +
                "InnoDB Rows - Read: %d, Inserted: %d, Updated: %d, Deleted: %d",
                qps, tps, connectionUsage, bufferPoolHitRatio, slowQueryRatio,
                activeConnections, threadsRunning, innodbRowsRead, 
                innodbRowsInserted, innodbRowsUpdated, innodbRowsDeleted
            );
        }
    }
    
    // 收集性能指标
    public PerformanceMetrics collectMetrics() throws SQLException {
        PerformanceMetrics metrics = new PerformanceMetrics();
        Map<String, Long> statusValues = getGlobalStatus();
        Map<String, Long> variableValues = getGlobalVariables();
        
        // 计算QPS (每秒查询数)
        long currentQueries = statusValues.getOrDefault("Questions", 0L);
        long uptime = statusValues.getOrDefault("Uptime", 1L);
        double qps = (double) currentQueries / uptime;
        metrics.setQps(qps);
        
        // 计算TPS (每秒事务数)
        long comCommit = statusValues.getOrDefault("Com_commit", 0L);
        long comRollback = statusValues.getOrDefault("Com_rollback", 0L);
        double tps = (double) (comCommit + comRollback) / uptime;
        metrics.setTps(tps);
        
        // 连接使用率
        long threadsConnected = statusValues.getOrDefault("Threads_connected", 0L);
        long maxConnections = variableValues.getOrDefault("max_connections", 1L);
        double connectionUsage = (double) threadsConnected / maxConnections * 100;
        metrics.setConnectionUsage(connectionUsage);
        
        // 缓冲池命中率
        long bufferPoolReads = statusValues.getOrDefault("Innodb_buffer_pool_reads", 0L);
        long bufferPoolReadRequests = statusValues.getOrDefault("Innodb_buffer_pool_read_requests", 1L);
        double bufferPoolHitRatio = (1.0 - (double) bufferPoolReads / bufferPoolReadRequests) * 100;
        metrics.setBufferPoolHitRatio(bufferPoolHitRatio);
        
        // 慢查询比率
        long slowQueries = statusValues.getOrDefault("Slow_queries", 0L);
        double slowQueryRatio = (double) slowQueries / currentQueries * 100;
        metrics.setSlowQueryRatio(slowQueryRatio);
        
        // 其他指标
        metrics.setActiveConnections(threadsConnected);
        metrics.setThreadsRunning(statusValues.getOrDefault("Threads_running", 0L));
        metrics.setInnodbRowsRead(statusValues.getOrDefault("Innodb_rows_read", 0L));
        metrics.setInnodbRowsInserted(statusValues.getOrDefault("Innodb_rows_inserted", 0L));
        metrics.setInnodbRowsUpdated(statusValues.getOrDefault("Innodb_rows_updated", 0L));
        metrics.setInnodbRowsDeleted(statusValues.getOrDefault("Innodb_rows_deleted", 0L));
        
        return metrics;
    }
    
    private Map<String, Long> getGlobalStatus() throws SQLException {
        Map<String, Long> statusMap = new HashMap<>();
        
        String sql = "SHOW GLOBAL STATUS";
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                String name = rs.getString("Variable_name");
                String value = rs.getString("Value");
                
                try {
                    statusMap.put(name, Long.parseLong(value));
                } catch (NumberFormatException e) {
                    // 忽略非数字值
                }
            }
        }
        
        return statusMap;
    }
    
    private Map<String, Long> getGlobalVariables() throws SQLException {
        Map<String, Long> variableMap = new HashMap<>();
        
        String sql = "SHOW GLOBAL VARIABLES";
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                String name = rs.getString("Variable_name");
                String value = rs.getString("Value");
                
                try {
                    variableMap.put(name, Long.parseLong(value));
                } catch (NumberFormatException e) {
                    // 忽略非数字值
                }
            }
        }
        
        return variableMap;
    }
    
    // 检查锁等待
    public List<String> checkLockWaits() throws SQLException {
        List<String> lockWaits = new ArrayList<>();
        
        String sql = """
            SELECT 
                r.trx_id waiting_trx_id,
                r.trx_mysql_thread_id waiting_thread,
                r.trx_query waiting_query,
                b.trx_id blocking_trx_id,
                b.trx_mysql_thread_id blocking_thread,
                b.trx_query blocking_query
            FROM information_schema.innodb_lock_waits w
            INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
            INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
            """;
        
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                String lockInfo = String.format(
                    "Waiting TRX: %s (Thread: %d) - %s\n" +
                    "Blocking TRX: %s (Thread: %d) - %s",
                    rs.getString("waiting_trx_id"),
                    rs.getLong("waiting_thread"),
                    rs.getString("waiting_query"),
                    rs.getString("blocking_trx_id"),
                    rs.getLong("blocking_thread"),
                    rs.getString("blocking_query")
                );
                lockWaits.add(lockInfo);
            }
        }
        
        return lockWaits;
    }
    
    // 启动监控
    public void startMonitoring(int intervalSeconds) {
        scheduler.scheduleAtFixedRate(() -> {
            try {
                PerformanceMetrics metrics = collectMetrics();
                System.out.println("=== MySQL Performance Metrics ===");
                System.out.println(metrics);
                
                List<String> lockWaits = checkLockWaits();
                if (!lockWaits.isEmpty()) {
                    System.out.println("\n=== Lock Waits Detected ===");
                    lockWaits.forEach(System.out::println);
                }
                
                System.out.println("\n" + "=".repeat(50) + "\n");
                
            } catch (SQLException e) {
                System.err.println("监控过程中发生错误: " + e.getMessage());
            }
        }, 0, intervalSeconds, TimeUnit.SECONDS);
    }
    
    // 停止监控
    public void stopMonitoring() {
        scheduler.shutdown();
    }
}

🔄 主从复制配置与优化

1. 主从复制架构

# 主服务器配置 (my.cnf)
[mysqld]
# 服务器ID (必须唯一)
server-id = 1

# 启用二进制日志
log-bin = mysql-bin
binlog_format = ROW
binlog_do_db = production_db

# 同步设置
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

# 从服务器配置 (my.cnf)
[mysqld]
server-id = 2
relay-log = relay-bin
read_only = 1
super_read_only = 1

# 复制过滤
replicate-do-db = production_db
replicate-ignore-table = production_db.temp_table

2. 主从复制监控

// ReplicationMonitor.java - 主从复制监控
package com.mysql.replication;

import java.sql.*;
import java.util.*;

public class ReplicationMonitor {
    
    private final Connection masterConnection;
    private final Connection slaveConnection;
    
    public ReplicationMonitor(Connection master, Connection slave) {
        this.masterConnection = master;
        this.slaveConnection = slave;
    }
    
    // 复制状态信息
    public static class ReplicationStatus {
        private String slaveIOState;
        private String masterLogFile;
        private long masterLogPos;
        private String relayMasterLogFile;
        private long execMasterLogPos;
        private long secondsBehindMaster;
        private boolean slaveIORunning;
        private boolean slaveSQLRunning;
        private String lastError;
        
        // getter和setter方法
        public void setSlaveIOState(String slaveIOState) { this.slaveIOState = slaveIOState; }
        public void setMasterLogFile(String masterLogFile) { this.masterLogFile = masterLogFile; }
        public void setMasterLogPos(long masterLogPos) { this.masterLogPos = masterLogPos; }
        public void setRelayMasterLogFile(String relayMasterLogFile) { this.relayMasterLogFile = relayMasterLogFile; }
        public void setExecMasterLogPos(long execMasterLogPos) { this.execMasterLogPos = execMasterLogPos; }
        public void setSecondsBehindMaster(long secondsBehindMaster) { this.secondsBehindMaster = secondsBehindMaster; }
        public void setSlaveIORunning(boolean slaveIORunning) { this.slaveIORunning = slaveIORunning; }
        public void setSlaveSQLRunning(boolean slaveSQLRunning) { this.slaveSQLRunning = slaveSQLRunning; }
        public void setLastError(String lastError) { this.lastError = lastError; }
        
        public long getSecondsBehindMaster() { return secondsBehindMaster; }
        public boolean isSlaveIORunning() { return slaveIORunning; }
        public boolean isSlaveSQLRunning() { return slaveSQLRunning; }
        public String getLastError() { return lastError; }
        
        @Override
        public String toString() {
            return String.format(
                "Slave IO State: %s\n" +
                "Master Log File: %s, Position: %d\n" +
                "Relay Master Log File: %s, Exec Position: %d\n" +
                "Seconds Behind Master: %d\n" +
                "Slave IO Running: %s, Slave SQL Running: %s\n" +
                "Last Error: %s",
                slaveIOState, masterLogFile, masterLogPos,
                relayMasterLogFile, execMasterLogPos, secondsBehindMaster,
                slaveIORunning, slaveSQLRunning, lastError
            );
        }
    }
    
    // 检查复制状态
    public ReplicationStatus checkReplicationStatus() throws SQLException {
        ReplicationStatus status = new ReplicationStatus();
        
        String sql = "SHOW SLAVE STATUS";
        try (Statement stmt = slaveConnection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            if (rs.next()) {
                status.setSlaveIOState(rs.getString("Slave_IO_State"));
                status.setMasterLogFile(rs.getString("Master_Log_File"));
                status.setMasterLogPos(rs.getLong("Read_Master_Log_Pos"));
                status.setRelayMasterLogFile(rs.getString("Relay_Master_Log_File"));
                status.setExecMasterLogPos(rs.getLong("Exec_Master_Log_Pos"));
                
                Object secondsBehind = rs.getObject("Seconds_Behind_Master");
                status.setSecondsBehindMaster(secondsBehind != null ? rs.getLong("Seconds_Behind_Master") : -1);
                
                status.setSlaveIORunning("Yes".equals(rs.getString("Slave_IO_Running")));
                status.setSlaveSQLRunning("Yes".equals(rs.getString("Slave_SQL_Running")));
                status.setLastError(rs.getString("Last_Error"));
            }
        }
        
        return status;
    }
    
    // 检查复制延迟
    public boolean isReplicationHealthy(long maxDelaySeconds) throws SQLException {
        ReplicationStatus status = checkReplicationStatus();
        
        // 检查IO和SQL线程是否运行
        if (!status.isSlaveIORunning() || !status.isSlaveSQLRunning()) {
            System.err.println("复制线程未运行: IO=" + status.isSlaveIORunning() + 
                             ", SQL=" + status.isSlaveSQLRunning());
            return false;
        }
        
        // 检查是否有错误
        if (status.getLastError() != null && !status.getLastError().isEmpty()) {
            System.err.println("复制错误: " + status.getLastError());
            return false;
        }
        
        // 检查延迟
        if (status.getSecondsBehindMaster() > maxDelaySeconds) {
            System.err.println("复制延迟过大: " + status.getSecondsBehindMaster() + "秒");
            return false;
        }
        
        return true;
    }
    
    // 获取主服务器状态
    public Map<String, Object> getMasterStatus() throws SQLException {
        Map<String, Object> masterStatus = new HashMap<>();
        
        String sql = "SHOW MASTER STATUS";
        try (Statement stmt = masterConnection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            if (rs.next()) {
                masterStatus.put("File", rs.getString("File"));
                masterStatus.put("Position", rs.getLong("Position"));
                masterStatus.put("Binlog_Do_DB", rs.getString("Binlog_Do_DB"));
                masterStatus.put("Binlog_Ignore_DB", rs.getString("Binlog_Ignore_DB"));
            }
        }
        
        return masterStatus;
    }
}

💾 备份与恢复策略

1. 全量备份脚本

#!/bin/bash
# mysql_backup.sh - MySQL全量备份脚本

# 配置参数
DB_HOST="localhost"
DB_PORT="3306"
DB_USER="backup_user"
DB_PASS="backup_password"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

# 创建备份目录
mkdir -p $BACKUP_DIR

# 备份函数
backup_database() {
    local db_name=$1
    local backup_file="$BACKUP_DIR/${db_name}_${DATE}.sql"
    
    echo "开始备份数据库: $db_name"
    
    # 使用mysqldump进行备份
    mysqldump \
        --host=$DB_HOST \
        --port=$DB_PORT \
        --user=$DB_USER \
        --password=$DB_PASS \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        --hex-blob \
        --quick \
        --lock-tables=false \
        --master-data=2 \
        $db_name > $backup_file
    
    if [ $? -eq 0 ]; then
        echo "备份成功: $backup_file"
        
        # 压缩备份文件
        gzip $backup_file
        echo "压缩完成: ${backup_file}.gz"
        
        # 计算校验和
        md5sum "${backup_file}.gz" > "${backup_file}.gz.md5"
        
    else
        echo "备份失败: $db_name"
        exit 1
    fi
}

# 获取所有数据库列表
databases=$(mysql -h$DB_HOST -P$DB_PORT -u$DB_USER -p$DB_PASS -e "SHOW DATABASES;" | grep -Ev "^(Database|information_schema|performance_schema|mysql|sys)$")

# 备份每个数据库
for db in $databases; do
    backup_database $db
done

# 清理过期备份
find $BACKUP_DIR -name "*.gz" -mtime +$RETENTION_DAYS -delete
find $BACKUP_DIR -name "*.md5" -mtime +$RETENTION_DAYS -delete

echo "备份任务完成"

2. 增量备份脚本

#!/bin/bash
# mysql_incremental_backup.sh - MySQL增量备份脚本

BACKUP_DIR="/backup/mysql/incremental"
BINLOG_DIR="/var/lib/mysql"
DATE=$(date +%Y%m%d_%H%M%S)

# 创建备份目录
mkdir -p $BACKUP_DIR

# 获取当前二进制日志文件和位置
CURRENT_LOG=$(mysql -e "SHOW MASTER STATUS\G" | grep "File:" | awk '{print $2}')
CURRENT_POS=$(mysql -e "SHOW MASTER STATUS\G" | grep "Position:" | awk '{print $2}')

echo "当前二进制日志: $CURRENT_LOG, 位置: $CURRENT_POS"

# 刷新日志
mysql -e "FLUSH LOGS"

# 备份二进制日志文件
if [ -f "$BINLOG_DIR/$CURRENT_LOG" ]; then
    cp "$BINLOG_DIR/$CURRENT_LOG" "$BACKUP_DIR/${CURRENT_LOG}_${DATE}"
    echo "增量备份完成: $BACKUP_DIR/${CURRENT_LOG}_${DATE}"
else
    echo "二进制日志文件不存在: $BINLOG_DIR/$CURRENT_LOG"
    exit 1
fi

# 记录备份信息
echo "$DATE,$CURRENT_LOG,$CURRENT_POS" >> "$BACKUP_DIR/backup_log.txt"

3. 恢复脚本

#!/bin/bash
# mysql_restore.sh - MySQL恢复脚本

DB_HOST="localhost"
DB_PORT="3306"
DB_USER="root"
DB_PASS="password"
BACKUP_FILE=$1
TARGET_DB=$2

if [ -z "$BACKUP_FILE" ] || [ -z "$TARGET_DB" ]; then
    echo "用法: $0 <备份文件> <目标数据库>"
    exit 1
fi

echo "开始恢复数据库: $TARGET_DB"
echo "备份文件: $BACKUP_FILE"

# 检查备份文件是否存在
if [ ! -f "$BACKUP_FILE" ]; then
    echo "备份文件不存在: $BACKUP_FILE"
    exit 1
fi

# 如果是压缩文件，先解压
if [[ $BACKUP_FILE == *.gz ]]; then
    echo "解压备份文件..."
    gunzip -c $BACKUP_FILE > /tmp/restore_temp.sql
    RESTORE_FILE="/tmp/restore_temp.sql"
else
    RESTORE_FILE=$BACKUP_FILE
fi

# 创建目标数据库
mysql -h$DB_HOST -P$DB_PORT -u$DB_USER -p$DB_PASS -e "CREATE DATABASE IF NOT EXISTS $TARGET_DB;"

# 恢复数据
mysql -h$DB_HOST -P$DB_PORT -u$DB_USER -p$DB_PASS $TARGET_DB < $RESTORE_FILE

if [ $? -eq 0 ]; then
    echo "数据库恢复成功"
    
    # 清理临时文件
    if [ -f "/tmp/restore_temp.sql" ]; then
        rm /tmp/restore_temp.sql
    fi
else
    echo "数据库恢复失败"
    exit 1
fi

🔧 故障排查与诊断

1. 常见问题诊断

-- 1. 检查连接数
SHOW PROCESSLIST;
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

-- 2. 检查锁等待
SELECT * FROM information_schema.innodb_locks;
SELECT * FROM information_schema.innodb_lock_waits;
SELECT * FROM information_schema.innodb_trx;

-- 3. 检查慢查询
SHOW STATUS LIKE 'Slow_queries';
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

-- 4. 检查缓冲池状态
SHOW ENGINE INNODB STATUS;
SELECT * FROM information_schema.innodb_buffer_pool_stats;

-- 5. 检查表空间使用情况
SELECT 
    table_schema,
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY (data_length + index_length) DESC;

2. 性能诊断工具

// MySQLDiagnostic.java - MySQL诊断工具
package com.mysql.diagnostic;

import java.sql.*;
import java.util.*;

public class MySQLDiagnostic {
    
    private final Connection connection;
    
    public MySQLDiagnostic(Connection connection) {
        this.connection = connection;
    }
    
    // 诊断连接问题
    public void diagnoseConnections() throws SQLException {
        System.out.println("=== 连接诊断 ===");
        
        // 当前连接数
        String sql = "SHOW STATUS LIKE 'Threads_connected'";
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                int connected = rs.getInt("Value");
                System.out.println("当前连接数: " + connected);
            }
        }
        
        // 最大连接数
        sql = "SHOW VARIABLES LIKE 'max_connections'";
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                int maxConnections = rs.getInt("Value");
                System.out.println("最大连接数: " + maxConnections);
            }
        }
        
        // 连接历史峰值
        sql = "SHOW STATUS LIKE 'Max_used_connections'";
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                int maxUsed = rs.getInt("Value");
                System.out.println("历史最大连接数: " + maxUsed);
            }
        }
        
        // 拒绝连接数
        sql = "SHOW STATUS LIKE 'Connection_errors_max_connections'";
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                int errors = rs.getInt("Value");
                System.out.println("连接被拒绝次数: " + errors);
            }
        }
    }
    
    // 诊断慢查询
    public void diagnoseSlowQueries() throws SQLException {
        System.out.println("\n=== 慢查询诊断 ===");
        
        // 慢查询数量
        String sql = "SHOW STATUS LIKE 'Slow_queries'";
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                int slowQueries = rs.getInt("Value");
                System.out.println("慢查询数量: " + slowQueries);
            }
        }
        
        // 慢查询阈值
        sql = "SHOW VARIABLES LIKE 'long_query_time'";
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                double threshold = rs.getDouble("Value");
                System.out.println("慢查询阈值: " + threshold + "秒");
            }
        }
        
        // 是否启用慢查询日志
        sql = "SHOW VARIABLES LIKE 'slow_query_log'";
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                String enabled = rs.getString("Value");
                System.out.println("慢查询日志: " + enabled);
            }
        }
    }
    
    // 诊断锁等待
    public void diagnoseLocks() throws SQLException {
        System.out.println("\n=== 锁等待诊断 ===");
        
        // 当前锁等待
        String sql = """
            SELECT 
                COUNT(*) as lock_waits
            FROM information_schema.innodb_lock_waits
            """;
        
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                int lockWaits = rs.getInt("lock_waits");
                System.out.println("当前锁等待数量: " + lockWaits);
            }
        }
        
        // 活跃事务数
        sql = "SELECT COUNT(*) as active_trx FROM information_schema.innodb_trx";
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                int activeTrx = rs.getInt("active_trx");
                System.out.println("活跃事务数量: " + activeTrx);
            }
        }
    }
    
    // 诊断缓冲池
    public void diagnoseBufferPool() throws SQLException {
        System.out.println("\n=== 缓冲池诊断 ===");
        
        // 缓冲池大小
        String sql = "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'";
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                long bufferPoolSize = rs.getLong("Value");
                System.out.println("缓冲池大小: " + (bufferPoolSize / 1024 / 1024) + "MB");
            }
        }
        
        // 缓冲池命中率
        Map<String, Long> status = new HashMap<>();
        sql = "SHOW STATUS LIKE 'Innodb_buffer_pool_%'";
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                status.put(rs.getString("Variable_name"), rs.getLong("Value"));
            }
        }
        
        long reads = status.getOrDefault("Innodb_buffer_pool_reads", 0L);
        long readRequests = status.getOrDefault("Innodb_buffer_pool_read_requests", 1L);
        double hitRatio = (1.0 - (double) reads / readRequests) * 100;
        
        System.out.println("缓冲池命中率: " + String.format("%.2f%%", hitRatio));
        System.out.println("物理读取次数: " + reads);
        System.out.println("逻辑读取次数: " + readRequests);
    }
    
    // 综合诊断
    public void runFullDiagnosis() throws SQLException {
        System.out.println("MySQL 性能诊断报告");
        System.out.println("=".repeat(50));
        
        diagnoseConnections();
        diagnoseSlowQueries();
        diagnoseLocks();
        diagnoseBufferPool();
        
        System.out.println("\n诊断完成");
    }
}

📋 总结与最佳实践

核心要点

1. 架构设计

   • 选择合适的存储引擎
   • 合理设计表结构和索引
   • 规划好数据分区策略

2. 性能优化

   • 定期分析和优化查询
   • 监控关键性能指标
   • 及时调整配置参数

3. 运维管理

   • 建立完善的备份恢复机制
   • 配置主从复制保证高可用
   • 实施有效的监控告警

4. 故障处理

   • 快速定位性能瓶颈
   • 及时处理锁等待问题
   • 建立故障应急预案

最佳实践建议

• 定期维护: 执行表优化、索引重建等维护任务
• 容量规划: 根据业务增长预测，提前规划资源
• 安全加固: 定期更新版本，加强访问控制
• 文档管理: 维护详细的配置和操作文档

通过系统性的性能优化和运维管理，可以确保MySQL数据库在高并发、大数据量场景下稳定高效运行。