-- 創(chuàng)建測試表
CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100),
    created_time DATETIME,
    INDEX idx_name (name),
    INDEX idx_age (age),
    INDEX idx_created_time (created_time)
);

-- 雷區(qū)1.1：對索引列進行函數(shù)操作
-- 錯誤寫法：索引失效
EXPLAIN SELECT * FROM user WHERE DATE(created_time) = '2023-01-01';

-- 正確寫法：使用范圍查詢
EXPLAIN SELECT * FROM user
WHERE created_time >= '2023-01-01 00:00:00'
AND created_time < '2023-01-02 00:00:00';

-- 雷區(qū)1.2：隱式類型轉(zhuǎn)換
-- 錯誤寫法：name是字符串，但用了數(shù)字，導(dǎo)致索引失效
EXPLAIN SELECT * FROM user WHERE name = 123;

-- 正確寫法：類型匹配
EXPLAIN SELECT * FROM user WHERE name = '123';

-- 雷區(qū)1.3：前導(dǎo)模糊查詢
-- 錯誤寫法：LIKE以%開頭，索引失效
EXPLAIN SELECT * FROM user WHERE name LIKE '%三%';

-- 正確寫法：非前導(dǎo)模糊查詢，可以使用索引
EXPLAIN SELECT * FROM user WHERE name LIKE '蘇%';

-- 雷區(qū)1.4：OR條件使用不當(dāng)
-- 錯誤寫法：age有索引，email無索引，導(dǎo)致整個查詢無法使用索引
EXPLAIN SELECT * FROM user WHERE age = 25 OR email = 'test@example.com';

-- 正確寫法：使用UNION優(yōu)化OR查詢
EXPLAIN
SELECT * FROM user WHERE age = 25
UNION
SELECT * FROM user WHERE email = 'test@example.com';

-- 查看當(dāng)前事務(wù)隔離級別
SELECT @@transaction_isolation;

-- 設(shè)置隔離級別為REPEATABLE-READ（默認(rèn)）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 場景：轉(zhuǎn)賬業(yè)務(wù)中的幻讀問題
-- 會話1：事務(wù)A
START TRANSACTION;
SELECT COUNT(*) FROM account WHERE user_id = 1001; -- 返回2

-- 會話2：事務(wù)B  
START TRANSACTION;
INSERT INTO account (user_id, balance) VALUES (1001, 500);
COMMIT;

-- 會話1：事務(wù)A繼續(xù)
SELECT COUNT(*) FROM account WHERE user_id = 1001; -- 仍然返回2（可重復(fù)讀）
UPDATE account SET balance = balance + 100 WHERE user_id = 1001; -- 影響3行！
SELECT COUNT(*) FROM account WHERE user_id = 1001; -- 返回3，出現(xiàn)幻讀！
COMMIT;

-- 創(chuàng)建測試表，假設(shè)有1000萬數(shù)據(jù)
CREATE TABLE order (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10,2),
    status TINYINT,
    created_time DATETIME,
    INDEX idx_created_time (created_time)
);

-- 雷區(qū)：傳統(tǒng)的分頁寫法
-- 當(dāng)offset達到500萬時，性能急劇下降
EXPLAIN SELECT * FROM order
ORDER BY created_time DESC
LIMIT 5000000, 20;

-- 優(yōu)化方案1：游標(biāo)分頁（推薦）
-- 第一頁
SELECT * FROM order
ORDER BY created_time DESC, id DESC
LIMIT 20;

-- 第二頁：記住上一頁最后一條記錄的created_time和id
SELECT * FROM order
WHERE created_time < '2023-06-01 10:00:00'
   OR (created_time = '2023-06-01 10:00:00' AND id < 1000000)
ORDER BY created_time DESC, id DESC
LIMIT 20;

-- 優(yōu)化方案2：子查詢優(yōu)化（適用于非游標(biāo)場景）
SELECT * FROM order
WHERE id >= (
    SELECT id FROM order
    ORDER BY created_time DESC
    LIMIT 5000000, 1
)
ORDER BY created_time DESC
LIMIT 20;

-- 查看字符集配置
SHOW VARIABLES LIKE'character_set%';
SHOW VARIABLES LIKE'collation%';

-- 雷區(qū)：UTF8不是真正的UTF-8
-- MySQL的utf8最多支持3字節(jié)，無法存儲emoji等4字節(jié)字符
CREATE TABLE user_utf8 (
    id INT PRIMARY KEY,
    name VARCHAR(50) CHARACTER SET utf8
);

-- 插入emoji表情失敗
INSERT INTO user_utf8 VALUES (1, '張三??'); -- 錯誤！

-- 正確：使用utf8mb4
CREATE TABLE user_utf8mb4 (
    id INT PRIMARY KEY,
    name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

-- 插入emoji成功
INSERT INTO user_utf8mb4 VALUES (1, '張三??'); -- 成功！

-- 雷區(qū)：排序規(guī)則影響查詢結(jié)果
CREATE TABLE product (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 大小寫不敏感查詢
SELECT * FROM product WHERE name = 'apple'; -- 會匹配'Apple', 'APPLE'

-- 如果需要大小寫敏感，使用binary或特定collation
SELECT * FROM product WHERE name = BINARY 'apple'; -- 只匹配'apple'

-- 創(chuàng)建帶外鍵的表結(jié)構(gòu)
CREATE TABLE department (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOTNULL
);

CREATE TABLE employee (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOTNULL,
    department_id INT,
    FOREIGN KEY (department_id) 
        REFERENCES department(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- 雷區(qū)1：級聯(lián)刪除導(dǎo)致意外數(shù)據(jù)丟失
-- 刪除部門時，所有相關(guān)員工也被刪除，可能不是期望的行為
DELETE FROM department WHERE id = 1; -- 部門1的所有員工都被刪除！

-- 雷區(qū)2：外鍵鎖競爭
-- 會話1：刪除部門
START TRANSACTION;
DELETE FROM department WHERE id = 1; -- 持有部門1的鎖

-- 會話2：在同一個部門插入員工（被阻塞）
START TRANSACTION;
INSERT INTO employee (name, department_id) VALUES ('新員工', 1); -- 等待鎖

-- 雷區(qū)3：數(shù)據(jù)遷移困難
-- 導(dǎo)入數(shù)據(jù)時必須按正確順序，否則外鍵約束失敗

// Spring Boot中的Druid連接池配置
@Configuration
public class DruidConfig {
    
    @Bean
    @ConfigurationProperties("spring.datasource.druid")
    public DataSource dataSource() {
        return DruidDataSourceBuilder.create().build();
    }
}

// application.yml配置
spring:
  datasource:
    druid:
      # 雷區(qū)1：初始連接數(shù)過大，浪費資源
      initial-size: 50
      # 雷區(qū)2：最大連接數(shù)過小，并發(fā)時等待
      max-active: 20
      # 雷區(qū)3：最小空閑連接數(shù)不合理
      min-idle: 5
      # 雷區(qū)4：獲取連接超時時間過短
      max-wait: 3000
      # 雷區(qū)5：沒有配置連接有效性檢查
      validation-query: SELECT 1
      test-on-borrow: true
      test-on-return: false
      test-while-idle: true
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000

// 常見的連接泄露模式
public class UserService {
    
    // 錯誤寫法：連接未關(guān)閉
    public User getUser(int id) {
        Connection conn = dataSource.getConnection();
        // 執(zhí)行查詢...
        // 忘記調(diào)用conn.close()
        return user;
    }
    
    // 正確寫法：使用try-with-resources
    public User getUserCorrect(int id) {
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement("SELECT * FROM user WHERE id = ?")) {
            
            stmt.setInt(1, id);
            ResultSet rs = stmt.executeQuery();
            // 處理結(jié)果...
            return user;
        } catch (SQLException e) {
            thrownew RuntimeException(e);
        }
    }
}