MySQL性能优化实战指南：从入门到精通的完整优化体系

🚀 前言：在当今数据驱动的时代，MySQL作为世界上最流行的开源关系型数据库，其性能优化能力直接决定了应用系统的响应速度和用户体验。本文将从多个维度深入探讨MySQL优化策略，帮助你构建高性能的数据库系统。

🎯 优化概览：性能提升的四大支柱

🔧 硬件层优化：性能的基石

1. CPU优化策略

# 查看CPU信息
lscpu

# 查看CPU使用率
top -p $(pgrep mysqld)

# 设置MySQL进程CPU亲和性
taskset -cp 0-3 $(pgrep mysqld)

CPU优化要点：

• 核心数量：MySQL主要受益于高频率CPU，而非大量核心
• NUMA架构：在多CPU系统中合理配置NUMA策略
• 超线程：根据工作负载特性决定是否启用

2. 内存优化配置

[mysqld]
# InnoDB缓冲池大小（物理内存的70-80%）
innodb_buffer_pool_size = 8G

# 缓冲池实例数（CPU核心数或8，取较小值）
innodb_buffer_pool_instances = 8

# 查询缓存（MySQL 8.0已移除）
query_cache_size = 0
query_cache_type = 0

# 排序缓冲区
sort_buffer_size = 2M

# 连接缓冲区
read_buffer_size = 128K
read_rnd_buffer_size = 256K

# 临时表大小
tmp_table_size = 64M
max_heap_table_size = 64M

3. 存储系统优化

# SSD优化配置
echo noop > /sys/block/sda/queue/scheduler
echo 0 > /sys/block/sda/queue/rotational

# 文件系统优化（ext4）
mount -o noatime,data=writeback /dev/sda1 /var/lib/mysql

# I/O调度器优化
echo deadline > /sys/block/sda/queue/scheduler

⚙️ 配置层优化：参数调优的艺术

1. InnoDB存储引擎优化

[mysqld]
# === InnoDB核心配置 ===
# 数据文件路径和大小
innodb_data_file_path = ibdata1:1G:autoextend

# 日志文件配置
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M

# 刷新策略（1=最安全，2=性能最佳）
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# 并发控制
innodb_thread_concurrency = 0
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# 锁等待超时
innodb_lock_wait_timeout = 50

# 死锁检测
innodb_deadlock_detect = ON

# 页面大小（默认16K，根据工作负载调整）
innodb_page_size = 16K

# 自适应哈希索引
innodb_adaptive_hash_index = ON

2. 连接和网络优化

[mysqld]
# === 连接配置 ===
# 最大连接数
max_connections = 1000

# 连接超时
wait_timeout = 28800
interactive_timeout = 28800

# 网络包大小
max_allowed_packet = 64M

# 连接队列大小
back_log = 512

# 跳过域名解析
skip-name-resolve

# 线程缓存
thread_cache_size = 64

3. 日志和监控配置

[mysqld]
# === 日志配置 ===
# 慢查询日志
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON

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

# 二进制日志
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 1G

# 性能模式
performance_schema = ON

🔍 SQL层优化：查询性能的核心

1. 索引优化策略

-- 创建高效的复合索引
CREATE INDEX idx_user_status_time ON users(status, created_at, user_id);

-- 前缀索引优化
CREATE INDEX idx_email_prefix ON users(email(10));

-- 函数索引（MySQL 8.0+）
CREATE INDEX idx_upper_name ON users((UPPER(name)));

-- 部分索引
CREATE INDEX idx_active_users ON users(user_id) WHERE status = 'active';

索引设计原则：

-- ❌ 错误的索引使用
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- ✅ 正确的索引使用
SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

-- 索引覆盖查询
CREATE INDEX idx_order_covering ON orders(user_id, status, order_date, total_amount);
SELECT user_id, status, order_date, total_amount 
FROM orders 
WHERE user_id = 12345 AND status = 'completed';

2. 查询优化技巧

-- 使用EXPLAIN分析查询计划
EXPLAIN FORMAT=JSON 
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id, u.name
HAVING order_count > 5;

-- 优化子查询为JOIN
-- ❌ 低效的子查询
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE status = 'completed');

-- ✅ 高效的JOIN查询
SELECT DISTINCT u.* 
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

-- 使用EXISTS替代IN（大数据集）
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.status = 'completed'
);

3. 分页查询优化

-- ❌ 传统分页（深度分页性能差）
SELECT * FROM users ORDER BY id LIMIT 100000, 20;

-- ✅ 游标分页优化
SELECT * FROM users 
WHERE id > 100000 
ORDER BY id 
LIMIT 20;

-- ✅ 延迟关联优化
SELECT u.* FROM users u
INNER JOIN (
    SELECT id FROM users ORDER BY created_at DESC LIMIT 100000, 20
) t ON u.id = t.id;

-- ✅ 基于时间的分页
SELECT * FROM orders 
WHERE created_at < '2024-01-01 12:00:00'
ORDER BY created_at DESC 
LIMIT 20;

4. 批量操作优化

-- 批量插入优化
INSERT INTO users (name, email, status) VALUES
('User1', 'user1@example.com', 'active'),
('User2', 'user2@example.com', 'active'),
('User3', 'user3@example.com', 'active');

-- 批量更新优化
UPDATE users 
SET status = CASE 
    WHEN id IN (1,2,3) THEN 'active'
    WHEN id IN (4,5,6) THEN 'inactive'
    ELSE status
END
WHERE id IN (1,2,3,4,5,6);

-- 使用ON DUPLICATE KEY UPDATE
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (1, 1, NOW())
ON DUPLICATE KEY UPDATE 
    login_count = login_count + 1,
    last_login = NOW();

📊 表结构优化：数据存储的智慧

1. 数据类型优化

-- ❌ 低效的数据类型选择
CREATE TABLE users_bad (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    age INT,
    salary DECIMAL(10,2),
    status VARCHAR(50),
    created_at DATETIME
);

-- ✅ 优化的数据类型选择
CREATE TABLE users_optimized (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age TINYINT UNSIGNED,
    salary DECIMAL(8,2),
    status ENUM('active', 'inactive', 'pending') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_status_created (status, created_at),
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

2. 表分区策略

-- 按时间分区
CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2),
    status ENUM('pending', 'completed', 'cancelled'),
    
    PRIMARY KEY (id, order_date),
    INDEX idx_user_date (user_id, order_date)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 按哈希分区
CREATE TABLE user_logs (
    id BIGINT AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    action VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (id, user_id)
) ENGINE=InnoDB
PARTITION BY HASH(user_id)
PARTITIONS 8;

3. 表结构规范化与反规范化

-- 适度反规范化提升查询性能
CREATE TABLE order_summary (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    user_name VARCHAR(50) NOT NULL,  -- 冗余字段
    user_email VARCHAR(100) NOT NULL, -- 冗余字段
    order_count INT UNSIGNED DEFAULT 0,
    total_amount DECIMAL(12,2) DEFAULT 0.00,
    last_order_date TIMESTAMP NULL,
    
    UNIQUE KEY uk_user (user_id),
    INDEX idx_amount (total_amount),
    INDEX idx_last_order (last_order_date)
) ENGINE=InnoDB;

-- 触发器维护冗余数据一致性
DELIMITER //
CREATE TRIGGER tr_update_order_summary
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_summary (user_id, user_name, user_email, order_count, total_amount, last_order_date)
    SELECT NEW.user_id, u.name, u.email, 1, NEW.amount, NEW.created_at
    FROM users u WHERE u.id = NEW.user_id
    ON DUPLICATE KEY UPDATE
        order_count = order_count + 1,
        total_amount = total_amount + NEW.amount,
        last_order_date = GREATEST(last_order_date, NEW.created_at);
END//
DELIMITER ;

🏗️ 架构层优化：扩展性的保障

1. 读写分离架构

@Configuration
public class DatabaseConfig {
    
    @Bean
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource() {vvvvvvvv
        return DataSourceBuilder.create().build();
    }
    
    @Bean
    @ConfigurationProperties("spring.datasource.slave")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().build();
    }
    
    @Bean
    public DataSource routingDataSource() {
        Map<Object, Object> dataSourceMap = new HashMap<>();
        dataSourceMap.put("master", masterDataSource());
        dataSourceMap.put("slave", slaveDataSource());
        
        DynamicDataSource routingDataSource = new DynamicDataSource();
        routingDataSource.setDefaultTargetDataSource(masterDataSource());
        routingDataSource.setTargetDataSources(dataSourceMap);
        return routingDataSource;
    }
}

@Component
public class DynamicDataSource extends AbstractRoutingDataSource {
    
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSourceType();
    }
}

@Service
public class UserService {
    
    @Autowired
    private UserMapper userMapper;
    
    @ReadOnly
    public List<User> findUsers(UserQuery query) {
        return userMapper.selectByQuery(query);
    }
    
    @Transactional
    public void createUser(User user) {
        userMapper.insert(user);
    }
}

2. 分库分表策略

@Configuration
public class ShardingConfig {
    
    @Bean
    public DataSource shardingDataSource() throws SQLException {
        // 配置分库策略
        DatabaseShardingStrategyConfiguration dbShardingStrategy = 
            new DatabaseShardingStrategyConfiguration("user_id", new DatabaseShardingAlgorithm());
        
        // 配置分表策略
        TableShardingStrategyConfiguration tableShardingStrategy = 
            new TableShardingStrategyConfiguration("user_id", new TableShardingAlgorithm());
        
        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
        shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(dbShardingStrategy);
        shardingRuleConfig.setDefaultTableShardingStrategyConfig(tableShardingStrategy);
        
        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, new Properties());
    }
    
    private TableRuleConfiguration getOrderTableRuleConfiguration() {
        TableRuleConfiguration result = new TableRuleConfiguration("orders", "ds${0..1}.orders_${0..15}");
        result.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE", "id"));
        return result;
    }
}

// 自定义分片算法
public class DatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        Long userId = shardingValue.getValue();
        return "ds" + (userId % 2);
    }
}

public class TableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        Long userId = shardingValue.getValue();
        return "orders_" + (userId % 16);
    }
}

3. 缓存架构设计

@Configuration
@EnableCaching
public class CacheConfig {
    
    @Bean
    public CacheManager cacheManager() {
        RedisCacheManager.Builder builder = RedisCacheManager
            .RedisCacheManagerBuilder
            .fromConnectionFactory(redisConnectionFactory())
            .cacheDefaults(cacheConfiguration());
        
        return builder.build();
    }
    
    private RedisCacheConfiguration cacheConfiguration() {
        return RedisCacheConfiguration.defaultCacheConfig()
            .entryTtl(Duration.ofMinutes(30))
            .serializeKeysWith(RedisSerializationContext.SerializationPair.fromSerializer(new StringRedisSerializer()))
            .serializeValuesWith(RedisSerializationContext.SerializationPair.fromSerializer(new GenericJackson2JsonRedisSerializer()));
    }
}

@Service
public class UserCacheService {
    
    @Cacheable(value = "users", key = "#userId")
    public User getUserById(Long userId) {
        return userMapper.selectById(userId);
    }
    
    @CacheEvict(value = "users", key = "#user.id")
    public void updateUser(User user) {
        userMapper.updateById(user);
    }
    
    // 缓存预热
    @PostConstruct
    public void warmUpCache() {
        List<User> hotUsers = userMapper.selectHotUsers();
        hotUsers.forEach(user -> {
            redisTemplate.opsForValue().set(
                "users::" + user.getId(), 
                user, 
                Duration.ofHours(2)
            );
        });
    }
}

📈 性能监控与调优

1. 性能监控指标

-- 查看当前连接状态
SHOW PROCESSLIST;

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS;

-- 查看慢查询统计
SELECT 
    schema_name,
    SUM(count_star) as total_queries,
    SUM(sum_timerZeeeeeeeeeeeeeeeeeeeeeee                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    _wait)/1000000000000 as total_time_sec,
    AVG(avg_timer_wait)/1000000000000 as avg_time_sec
FROM performance_schema.events_statements_summary_by_digest 
WHERE schema_name IS NOT NULL 
GROUP BY schema_name 
ORDER BY total_time_sec DESC;

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

2. 自动化监控脚本

#!/bin/bash

# MySQL性能监控脚本
MYSQL_USER="monitor"
MYSQL_PASS="password"
MYSQL_HOST="localhost"
MYSQL_PORT="3306"

# 获取MySQL状态
get_mysql_status() {
    mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -P$MYSQL_PORT -e "$1" 2>/dev/null
}

# 监控连接数
check_connections() {
    CURRENT_CONNECTIONS=$(get_mysql_status "SHOW STATUS LIKE 'Threads_connected';" | awk 'NR==2{print $2}')
    MAX_CONNECTIONS=$(get_mysql_status "SHOW VARIABLES LIKE 'max_connections';" | awk 'NR==2{print $2}')
    
    CONNECTION_USAGE=$(echo "scale=2; $CURRENT_CONNECTIONS * 100 / $MAX_CONNECTIONS" | bc)
    
    echo "Connection Usage: $CONNECTION_USAGE% ($CURRENT_CONNECTIONS/$MAX_CONNECTIONS)"
    
    if (( $(echo "$CONNECTION_USAGE > 80" | bc -l) )); then
        echo "WARNING: High connection usage detected!"
    fi
}

# 监控慢查询
check_slow_queries() {
    SLOW_QUERIES=$(get_mysql_status "SHOW STATUS LIKE 'Slow_queries';" | awk 'NR==2{print $2}')
    echo "Slow Queries: $SLOW_QUERIES"
}

# 监控InnoDB缓冲池命中率
check_buffer_pool_hit_rate() {
    BUFFER_POOL_READS=$(get_mysql_status "SHOW STATUS LIKE 'Innodb_buffer_pool_reads';" | awk 'NR==2{print $2}')
    BUFFER_POOL_READ_REQUESTS=$(get_mysql_status "SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';" | awk 'NR==2{print $2}')
    
    if [ "$BUFFER_POOL_READ_REQUESTS" -gt 0 ]; then
        HIT_RATE=$(echo "scale=4; (1 - $BUFFER_POOL_READS / $BUFFER_POOL_READ_REQUESTS) * 100" | bc)
        echo "Buffer Pool Hit Rate: $HIT_RATE%"
        
        if (( $(echo "$HIT_RATE < 95" | bc -l) )); then
            echo "WARNING: Low buffer pool hit rate!"
        fi
    fi
}

# 执行监控
echo "=== MySQL Performance Monitor ==="
echo "Timestamp: $(date)"
check_connections
check_slow_queries
check_buffer_pool_hit_rate
echo "================================="

3. 性能调优工具

import mysql.connector
import json
from datetime import datetime

class MySQLTuner:
    def __init__(self, host, user, password, database):
        self.connection = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=database
        )
        self.cursor = self.connection.cursor()
    
    def analyze_slow_queries(self):
        """分析慢查询"""
        query = """
        SELECT 
            digest_text,
            count_star,
            avg_timer_wait/1000000000000 as avg_time_sec,
            sum_timer_wait/1000000000000 as total_time_sec
        FROM performance_schema.events_statements_summary_by_digest 
        WHERE digest_text IS NOT NULL 
        ORDER BY avg_timer_wait DESC 
        LIMIT 10
        """
        
        self.cursor.execute(query)
        results = self.cursor.fetchall()
        
        print("Top 10 Slow Queries:")
        for row in results:
            print(f"Query: {row[0][:100]}...")
            print(f"Count: {row[1]}, Avg Time: {row[2]:.3f}s, Total Time: {row[3]:.3f}s")
            print("-" * 50)
    
    def analyze_index_usage(self):
        """分析索引使用情况"""
        query = """
        SELECT 
            object_schema,
            object_name,
            index_name,
            count_star,
            sum_timer_wait/1000000000000 as total_time_sec
        FROM performance_schema.table_io_waits_summary_by_index_usage 
        WHERE object_schema NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys')
        AND count_star > 0
        ORDER BY count_star DESC
        LIMIT 20
        """
        
        self.cursor.execute(query)
        results = self.cursor.fetchall()
        
        print("Index Usage Statistics:")
        for row in results:
            print(f"Schema: {row[0]}, Table: {row[1]}, Index: {row[2]}")
            print(f"Usage Count: {row[3]}, Total Time: {row[4]:.3f}s")
            print("-" * 50)
    
    def get_recommendations(self):
        """获取优化建议"""
        recommendations = []
        
        # 检查缓冲池大小
        self.cursor.execute("SHOW VARIABLES LIKE 'innodb_buffer_pool_size'")
        buffer_pool_size = int(self.cursor.fetchone()[1])
        
        if buffer_pool_size < 1024 * 1024 * 1024:  # 小于1GB
            recommendations.append("Consider increasing innodb_buffer_pool_size")
        
        # 检查慢查询日志
        self.cursor.execute("SHOW VARIABLES LIKE 'slow_query_log'")
        slow_log_enabled = self.cursor.fetchone()[1]
        
        if slow_log_enabled == 'OFF':
            recommendations.append("Enable slow query log for performance analysis")
        
        return recommendations
    
    def generate_report(self):
        """生成性能报告"""
        report = {
            'timestamp': datetime.now().isoformat(),
            'slow_queries': [],
            'index_usage': [],
            'recommendations': self.get_recommendations()
        }
        
        # 保存报告
        with open(f'mysql_performance_report_{datetime.now().strftime("%Y%m%d_%H%M%S")}.json', 'w') as f:
            json.dump(report, f, indent=2)
        
        print("Performance report generated successfully!")
    
    def close(self):
        self.cursor.close()
        self.connection.close()

# 使用示例
if __name__ == "__main__":
    tuner = MySQLTuner('localhost', 'root', 'password', 'test')
    tuner.analyze_slow_queries()
    tuner.analyze_index_usage()
    tuner.generate_report()
    tuner.close()

🚀 高级优化技巧

1. 查询缓存替代方案（MySQL 8.0+）

@Service
public class QueryCacheService {
    
    @Autowired
    private RedisTemplate<String, Object> redisTemplate;
    
    @Autowired
    private UserMapper userMapper;
    
    public List<User> getCachedUsers(UserQuery query) {
        String cacheKey = generateCacheKey("users", query);
        
        // 尝试从缓存获取
        List<User> cachedResult = (List<User>) redisTemplate.opsForValue().get(cacheKey);
        if (cachedResult != null) {
            return cachedResult;
        }
        
        // 缓存未命中，查询数据库
        List<User> users = userMapper.selectByQuery(query);
        
        // 存入缓存，设置过期时间
        redisTemplate.opsForValue().set(cacheKey, users, Duration.ofMinutes(15));
        
        return users;
    }
    
    private String generateCacheKey(String prefix, Object query) {
        return prefix + ":" + DigestUtils.md5Hex(JSON.toJSONString(query));
    }
    
    // 缓存预热策略
    @Scheduled(fixedRate = 3600000) // 每小时执行一次
    public void warmUpCache() {
        List<UserQuery> hotQueries = getHotQueries();
        hotQueries.parallelStream().forEach(query -> {
            try {
                getCachedUsers(query);
            } catch (Exception e) {
                log.error("Cache warm-up failed for query: {}", query, e);
            }
        });
    }
}

2. 连接池优化配置

spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      # 连接池配置
      minimum-idle: 10
      maximum-pool-size: 50
      idle-timeout: 300000
      max-lifetime: 1800000
      connection-timeout: 30000
      
      # 连接测试
      connection-test-query: SELECT 1
      validation-timeout: 5000
      
      # 性能优化
      auto-commit: false
      read-only: false
      
      # 连接池监控
      register-mbeans: true
      
      # 数据库连接属性
      data-source-properties:
        cachePrepStmts: true
        prepStmtCacheSize: 250
        prepStmtCacheSqlLimit: 2048
        useServerPrepStmts: true
        useLocalSessionState: true
        rewriteBatchedStatements: true
        cacheResultSetMetadata: true
        cacheServerConfiguration: true
        elideSetAutoCommits: true
        maintainTimeStats: false

3. 数据库版本升级策略

-- MySQL 8.0 新特性利用

-- 1. 窗口函数优化排名查询
SELECT 
    user_id,
    order_amount,
    ROW_NUMBER() OVER (ORDER BY order_amount DESC) as rank
FROM orders
WHERE order_date >= '2024-01-01';

-- 2. CTE（公共表表达式）简化复杂查询
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 0 as level
    FROM categories 
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, name;

-- 3. JSON函数优化
SELECT 
    id,
    JSON_EXTRACT(metadata, '$.tags') as tags,
    JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.title')) as title
FROM articles
WHERE JSON_CONTAINS(metadata, '{"status": "published"}');

-- 4. 不可见索引（测试索引效果）
CREATE INDEX idx_test ON users(email) INVISIBLE;
-- 测试性能后决定是否启用
ALTER INDEX idx_test ON users VISIBLE;

📋 性能优化检查清单

✅ 硬件层面

• CPU：选择高频率CPU，合理配置NUMA
• 内存：配置足够的InnoDB缓冲池
• 存储：使用SSD，优化I/O调度器
• 网络：确保网络带宽充足，延迟较低

✅ 配置层面

• InnoDB参数：缓冲池、日志文件、刷新策略
• 连接配置：最大连接数、超时设置
• 日志配置：慢查询日志、错误日志、二进制日志
• 字符集：使用utf8mb4字符集

✅ SQL层面

• 索引设计：合理创建和使用索引
• 查询优化：避免全表扫描，优化JOIN
• 分页优化：使用游标分页或延迟关联
• 批量操作：使用批量插入和更新

✅ 架构层面

• 读写分离：分离读写操作，减轻主库压力
• 分库分表：水平拆分大表和数据库
• 缓存架构：多级缓存，减少数据库访问
• 监控告警：完善的性能监控体系

🎯 总结与最佳实践

🔥 核心优化原则

1. 测量先行：始终基于实际性能数据进行优化
2. 渐进优化：从影响最大的瓶颈开始优化
3. 全栈思维：从硬件到应用层的整体优化
4. 持续监控：建立完善的性能监控和告警机制

🚀 性能提升路径

💡 优化效果预期

• 硬件优化：性能提升20-50%
• 配置调优：性能提升10-30%
• SQL优化：性能提升50-200%
• 架构优化：性能提升100-500%

---

🎉 结语：MySQL性能优化是一个系统性工程，需要从多个维度综合考虑。通过合理的硬件配置、精心的参数调优、高效的SQL设计和科学的架构规划，我们可以构建出高性能、高可用的数据库系统。记住，优化永远是一个持续的过程，需要根据业务发展和数据增长不断调整策略。

---

持续优化，永无止境！让我们一起构建更快、更稳定的MySQL数据库系统！ 🚀