MySQL性能优化全攻略：从原理到实践

一、引言

• MySQL在Web开发中的重要性
• 优化带来的价值：性能提升、成本降低、用户体验改善
• 全栈视角下的MySQL优化意义

二、MySQL优化层次体系

1. 架构层优化

-- 读写分离示例
-- 主库：写操作
INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com');

-- 从库：读操作
SELECT * FROM users WHERE id = 1;

2. 设计层优化

• 范式与反范式的平衡
• 数据类型选择原则
• 表结构设计最佳实践

3. SQL层优化

• 慢查询定位与分析
• 执行计划解读
• 索引优化策略

4. 配置层优化

• 关键参数调优
• 缓存机制优化
• 连接池配置

三、索引优化深度解析

1. 索引原理

// B+树索引结构类比
public class BPlusTree {
    // 非叶子节点：只存储索引键和指针
    // 叶子节点：存储完整数据，形成链表
}

2. 索引类型及适用场景

-- 聚簇索引 vs 非聚簇索引
-- 创建复合索引
CREATE INDEX idx_user_created ON users(created_at, status);

-- 覆盖索引示例
EXPLAIN SELECT id, name FROM users WHERE created_at > '2023-01-01';

3. 索引失效的常见场景

-- 1. 函数操作导致索引失效
SELECT * FROM users WHERE DATE(created_at) = '2023-01-01'; -- ❌
SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2023-01-02'; -- ✅

-- 2. 前导模糊查询
SELECT * FROM users WHERE name LIKE '%张%'; -- ❌
SELECT * FROM users WHERE name LIKE '张%'; -- ✅

-- 3. 类型转换
SELECT * FROM users WHERE phone = 13800138000; -- ❌
SELECT * FROM users WHERE phone = '13800138000'; -- ✅

四、SQL语句优化实战

1. 查询优化

-- 分页优化：传统 vs 优化版
-- 传统分页（数据量大时性能差）
SELECT * FROM orders ORDER BY id LIMIT 10000, 20;

-- 优化分页（使用游标）
SELECT * FROM orders WHERE id > 10000 ORDER BY id LIMIT 20;

-- EXISTS vs IN 的选择
-- 当子查询结果集大时使用EXISTS
SELECT * FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000);

-- 当子查询结果集小时使用IN
SELECT * FROM users WHERE id IN (1, 2, 3, 4, 5);

2. 批量操作优化

# Python批量插入优化
# 不推荐：逐条插入
for user in user_list:
    cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)", (user['name'], user['email']))

# 推荐：批量插入
data = [(user['name'], user['email']) for user in user_list]
cursor.executemany("INSERT INTO users (name, email) VALUES (%s, %s)", data)

// Java MyBatis批量插入
@Insert("<script>" +
        "INSERT INTO users (name, email) VALUES " +
        "<foreach collection='list' item='user' separator=','>" +
        "(#{user.name}, #{user.email})" +
        "</foreach>" +
        "</script>")
void batchInsertUsers(List<User> users);

五、数据库设计优化

1. 表结构设计

-- 垂直分表：将大字段分离
-- 主表
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at TIMESTAMP
);

-- 扩展表（存储不常用的大字段）
CREATE TABLE users_profile (
    user_id BIGINT PRIMARY KEY,
    avatar TEXT,
    biography TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

2. 数据类型优化

-- 选择合适的数据类型
CREATE TABLE optimized_table (
    -- 使用TINYINT代替布尔值
    is_active TINYINT(1) NOT NULL DEFAULT 0,
    
    -- 使用INT UNSIGNED存储非负数
    view_count INT UNSIGNED NOT NULL DEFAULT 0,
    
    -- 使用DECIMAL精确存储金额
    price DECIMAL(10, 2) NOT NULL,
    
    -- 使用TIMESTAMP存储时间戳
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

六、配置参数调优

1. 关键配置项

# my.cnf 关键配置
[mysqld]
# 缓冲池大小（通常设为物理内存的50-70%）
innodb_buffer_pool_size = 4G

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

# 连接相关
max_connections = 500
thread_cache_size = 50

# 查询缓存（MySQL 8.0已移除，5.7版本可配置）
query_cache_type = 0

七、全栈视角的优化实践

1. 后端优化策略

# Django ORM优化示例
# 避免N+1查询
# 不推荐
users = User.objects.all()
for user in users:
    print(user.profile.bio)  # 每次循环都会查询数据库

# 推荐：使用select_related或prefetch_related
users = User.objects.select_related('profile').all()
for user in users:
    print(user.profile.bio)  # 一次查询搞定

// Spring Boot + JPA优化
@Entity
public class User {
    @Id
    private Long id;
    
    // 懒加载大字段
    @Basic(fetch = FetchType.LAZY)
    @Lob
    private String biography;
    
    // 使用@NamedEntityGraph优化查询
    @NamedEntityGraph(
        name = "User.withProfile",
        attributeNodes = @NamedAttributeNode("profile")
    )
}

2. 前端配合优化

<template>
  <div>
    <!-- 分页组件减少单次数据量 -->
    <el-pagination
      @size-change="handleSizeChange"
      @current-change="handleCurrentChange"
      :current-page="currentPage"
      :page-sizes="[10, 20, 50, 100]"
      :page-size="pageSize"
      layout="total, sizes, prev, pager, next, jumper"
      :total="total">
    </el-pagination>
    
    <!-- 虚拟滚动处理大数据量 -->
    <virtual-list :size="50" :remain="20" :items="userList">
      <template v-slot:default="item">
        <user-item :user="item" />
      </template>
    </virtual-list>
  </div>
</template>

<script>
export default {
  data() {
    return {
      currentPage: 1,
      pageSize: 20,
      total: 0,
      userList: []
    }
  },
  methods: {
    async loadUsers() {
      // 只请求需要的数据
      const params = {
        page: this.currentPage,
        size: this.pageSize,
        fields: 'id,name,avatar' // 只请求必要字段
      }
      const { data } = await this.$api.getUsers(params)
      this.userList = data.items
      this.total = data.total
    }
  }
}
</script>

八、监控与诊断工具

1. 性能监控

-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 分析执行计划
EXPLAIN FORMAT=JSON 
SELECT u.name, COUNT(o.id) 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
GROUP BY u.id;

-- 查看索引使用情况
SELECT * FROM sys.schema_index_statistics;

九、实战案例分享

案例1：电商系统订单查询优化

• 问题：订单列表查询缓慢
• 分析：复合索引缺失 + 分页性能问题
• 解决方案：创建(用户ID, 创建时间)复合索引 + 游标分页

案例2：社交平台Feed流优化

• 问题：好友动态查询性能差
• 分析：多表关联 + 大数据量排序
• 解决方案：读写分离 + 缓存策略 + 预聚合

十、总结与最佳实践

1. 优化原则：先监控分析，再优化调整
2. 优化顺序：架构 → 设计 → SQL → 配置
3. 持续优化：建立性能监控体系，定期review