日常开发常见MySQL性能优化策略及应用场景

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    address VARCHAR(255),
    phone_number VARCHAR(20)
);

CREATE TABLE menu_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    restaurant_id INT,
    item_name VARCHAR(100),
    price DECIMAL(10, 2)
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    item_id INT,
    quantity INT,
    order_status VARCHAR(50),
    order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_details (
    order_id INT,
    restaurant_id INT,
    delivery_status VARCHAR(50),
    delivery_time TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (restaurant_id) REFERENCES menu_items(restaurant_id)
);

// getPopularTopicPosts 尝试从缓存获取热门话题的帖子列表，如果缓存未命中，则从数据库获取并更新缓存
func getPopularTopicPosts(topicID string) ([]string, error) {
    // 构建缓存键名
    postsKey := "popular_topic_" + topicID + "_posts"

    // 尝试从Redis缓存中获取帖子列表
    postsJSON, err := cache.Get(ctx, postsKey).Result()
    if err == nil {
        // 缓存命中，反序列化JSON数据到切片
        var posts []string
        err = json.Unmarshal([]byte(postsJSON), &posts)
        if err == nil {
            fmt.Println("从缓存中获取热门话题帖子列表")
            return posts, nil // 返回帖子列表
        }
        // 如果反序列化失败，打印错误并返回空列表
        fmt.Println("反序列化缓存数据失败:", err)
        return nil, err
    }

    // 缓存未命中，模拟从数据库获取数据
    fmt.Println("缓存未命中，从数据库中获取热门话题帖子列表")
    // 这里应是数据库查询逻辑，此处使用模拟数据代替
    posts := []string{"帖子1", "帖子2", "帖子3"} // 假设的数据库查询结果

    // 序列化帖子列表为JSON字符串
    postsJSON, err = json.Marshal(posts)
    if err != nil {
        // 如果序列化失败，打印错误并返回错误
        fmt.Println("序列化帖子列表失败:", err)
        return nil, err
    }

    // 将序列化后的帖子列表存入缓存，并设置1小时的过期时间
    err = cache.SetEX(ctx, postsKey, time.Hour, string(postsJSON)).Err()
    if err != nil {
        // 如果缓存设置失败，打印错误并返回错误
        fmt.Println("设置缓存数据失败:", err)
        return nil, err
    }

    // 返回帖子列表
    return posts, nil
}

// Ticket 代表一个门票的结构
type Ticket struct {
    EventID  int
    TicketID int
    Version  int // 用于乐观
// TicketService 处理票务服务的逻辑
type TicketService struct {
    DB *sql.DB
}

// PurchaseTicket 尝试购买门票
func (ts *TicketService) PurchaseTicket(eventID int, ticketID int) error {
    // 开始事务
    tx, err := ts.DB.Begin()
    if err != nil {
        return err
    }

    // 检查门票是否存在且未被售出
    var ticket Ticket
    err = tx.QueryRow("SELECT * FROM tickets WHERE event_id = ? AND ticket_id = ? FOR UPDATE", eventID, ticketID).Scan(&ticket.EventID, &ticket.TicketID, &ticket.Version)
    if err != nil {
        tx.Rollback()
        return err
    }

    // 检查门票是否已被售出
    if ticket.Version == -1 { // 假设-1表示门票已售出
        tx.Rollback()
        return fmt.Errorf("票已售完")
    }

    // 更新门票状态为已售出
    // 使用乐观锁，通过版本号检查在事务开始后门票是否被修改过
    updatedRows, err := tx.Exec("UPDATE tickets SET version = -1 WHERE event_id = ? AND ticket_id = ? AND version = ?", eventID, ticketID, ticket.Version)
    if err != nil {
        tx.Rollback()
        return err
    }

    // 检查是否更新了行
    if updatedRows == 0 {
        tx.Rollback()
        return fmt.Errorf("购票失败，门票可能已被其他人购买")
    }

    // 提交事务
    return tx.Commit()
}

    CREATE TABLE orders (
        order_id INT NOT NULL,
        user_id INT NOT NULL,
        order_date DATE NOT NULL,
        -- 其他订单字段
    ) ENGINE=InnoDB
    PARTITION BY RANGE (TO_DAYS(order_date)) (
        PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
        PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
        -- 为每个月创建一个分区
    );

    // 根据用户ID进行哈希分片
    func getShardKey(userID int) int {
        return userID % numShards // 假设有numShards个分片
    }

    // 获取对应分片的数据库连接
    func getDBConnection(shardKey int) *sql.DB {
        // 根据分片键获取数据库连接
    }

    type Shard struct {
        ID     int
        Load   float64
        MaxLoad float64
    }

    func (s *Shard) MigrateData(target *Shard) {
        // 数据迁移逻辑
    }

    func balanceShards(shards []*Shard) {
        // 负载均衡逻辑
    }

    -- 在主分片上设置复制
    CHANGE MASTER TO 
    MASTER_HOST='master_host', 
    MASTER_USER='master_user', 
    MASTER_PASSWORD='master_password', 
    MASTER_LOG_FILE='binlog_file', 
    MASTER_LOG_POS=binlog_position;

    -- 在从分片上启动复制
    START SLAVE;

-- 清理一年前的日报数据
DELETE FROM daily_reports WHERE date < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- 优化日报表
OPTIMIZE TABLE daily_reports;