数据库

-- 事务开始
START TRANSACTION;

-- 从账户 A 扣款 100 元
UPDATE account SET balance = balance - 100 WHERE id = 'A';

-- 向账户 B 存款 100 元
UPDATE account SET balance = balance + 100 WHERE id = 'B';

-- 提交事务
COMMIT;

-- 如果中间任何一步失败,执行 ROLLBACK 回滚
-- ROLLBACK;

-- 不符合 1NF:地址字段包含省市区
CREATE TABLE bad_user (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    address VARCHAR(200)  -- "北京市朝阳区望京街道"
);

-- 符合 1NF:地址拆分为多个字段
CREATE TABLE good_user (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    province VARCHAR(50),
    city VARCHAR(50),
    district VARCHAR(50),
    detail VARCHAR(200)
);

-- 不符合 2NF:订单明细表中商品名称部分依赖主键
CREATE TABLE bad_order_item (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- 依赖 product_id,不依赖 order_id
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- 符合 2NF:商品名称放到商品表
CREATE TABLE product (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE good_order_item (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- 表结构定义数据类型和约束
CREATE TABLE user_account (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    username VARCHAR(64) NOT NULL COMMENT '用户名',
    phone VARCHAR(20) UNIQUE COMMENT '手机号',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '状态:1正常,0禁用',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    
    -- 唯一约束
    UNIQUE KEY uk_username (username),
    -- 索引
    KEY idx_phone (phone),
    KEY idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户账户表';

-- 复杂查询示例
SELECT 
    u.username,
    COUNT(o.id) AS order_count,
    SUM(o.amount) AS total_amount
FROM user_account u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 1
  AND o.created_at >= '2026-01-01'
GROUP BY u.id
HAVING total_amount > 10000
ORDER BY total_amount DESC
LIMIT 10;

-- 约束示例
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status TINYINT NOT NULL,
    
    -- 外键约束:保证 user_id 存在
    CONSTRAINT fk_user FOREIGN KEY (user_id) 
        REFERENCES user_account(id),
    -- 检查约束:保证金额为正
    CONSTRAINT chk_amount CHECK (amount > 0)
);

事务A: SELECT * FROM orders WHERE id=1 FOR UPDATE;  -- 加行锁
事务B: UPDATE orders SET status=2 WHERE id=1;        -- 等待锁释放

数据库(Database)
  └── 表(Table)
        ├── 行(Row) / 记录(Record)
        │     ├── 列(Column) / 字段(Field)
        │     ├── 列(Column)
        │     └── 列(Column)
        └── 行(Row)

-- 创建数据库
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 使用数据库
USE shop;

-- 创建表
CREATE TABLE user_account (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    username VARCHAR(64) NOT NULL COMMENT '用户名',
    phone VARCHAR(20) UNIQUE COMMENT '手机号',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '状态:1正常,0禁用',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户账户表';

-- 插入数据(行)
INSERT INTO user_account (username, phone, status) 
VALUES ('zhangsan', '13800138000', 1);

-- 查询数据
SELECT * FROM user_account WHERE username = 'zhangsan';

-- 约束示例
CREATE TABLE product (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,              -- 非空约束
    sku VARCHAR(50) UNIQUE,                  -- 唯一约束
    price DECIMAL(10,2) NOT NULL,
    status TINYINT DEFAULT 1,                -- 默认值
    CHECK (price > 0),                       -- 检查约束
    
    INDEX idx_name (name),                   -- 索引
    INDEX idx_status (status)
);

CREATE TABLE order_item (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    
    -- 外键约束
    CONSTRAINT fk_order FOREIGN KEY (order_id) 
        REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_product FOREIGN KEY (product_id) 
        REFERENCES product(id)
);

-- 创建索引
CREATE INDEX idx_user_phone ON user_account(phone);
CREATE INDEX idx_user_status_created ON user_account(status, created_at);

-- 查看索引
SHOW INDEX FROM user_account;

-- 使用 EXPLAIN 分析查询是否命中索引
EXPLAIN SELECT * FROM user_account WHERE phone = '13800138000';

// Java 代码示例(Spring 事务管理)
@Service
public class AccountService {
    
    @Autowired
    private AccountMapper accountMapper;
    
    @Transactional  // Spring 声明式事务
    public void transfer(String fromAccount, String toAccount, BigDecimal amount) {
        // 1. 检查余额
        Account from = accountMapper.selectById(fromAccount);
        if (from.getBalance().compareTo(amount) < 0) {
            throw new RuntimeException("余额不足");
        }
        
        // 2. 扣款
        accountMapper.decreaseBalance(fromAccount, amount);
        
        // 3. 存款
        accountMapper.increaseBalance(toAccount, amount);
        
        // 方法正常结束自动提交,抛出异常自动回滚
    }
}

-- SQL 事务示例
START TRANSACTION;

-- 查询余额并加锁(悲观锁)
SELECT balance FROM account WHERE id = 'A' FOR UPDATE;

-- 扣款
UPDATE account SET balance = balance - 100 WHERE id = 'A';

-- 存款
UPDATE account SET balance = balance + 100 WHERE id = 'B';

-- 提交事务
COMMIT;

-- 如果出现异常,回滚
-- ROLLBACK;

@Service
public class UserService {
    
    @Transactional
    public Long register(UserRegisterDTO dto) {
        // 1. 检查用户名是否唯一
        if (userMapper.selectByUsername(dto.getUsername()) != null) {
            throw new BusinessException("用户名已存在");
        }
        
        // 2. 检查手机号是否唯一
        if (userMapper.selectByPhone(dto.getPhone()) != null) {
            throw new BusinessException("手机号已注册");
        }
        
        // 3. 密码加密
        String encryptedPassword = passwordEncoder.encode(dto.getPassword());
        
        // 4. 插入用户记录
        User user = new User();
        user.setUsername(dto.getUsername());
        user.setPhone(dto.getPhone());
        user.setPassword(encryptedPassword);
        user.setStatus(1);
        userMapper.insert(user);
        
        // 5. 初始化用户资料
        UserProfile profile = new UserProfile();
        profile.setUserId(user.getId());
        profile.setNickname(dto.getUsername());
        userProfileMapper.insert(profile);
        
        // 6. 记录注册日志
        UserLog log = new UserLog();
        log.setUserId(user.getId());
        log.setAction("register");
        log.setIp(dto.getIp());
        userLogMapper.insert(log);
        
        return user.getId();
    }
}

@Service
public class OrderService {
    
    @Transactional(rollbackFor = Exception.class)
    public Long createOrder(OrderCreateDTO dto) {
        // 1. 检查商品库存(使用行锁)
        Product product = productMapper.selectByIdForUpdate(dto.getProductId());
        if (product.getStock() < dto.getQuantity()) {
            throw new BusinessException("库存不足");
        }
        
        // 2. 扣减库存
        productMapper.decreaseStock(dto.getProductId(), dto.getQuantity());
        
        // 3. 创建订单
        Order order = new Order();
        order.setOrderNo(generateOrderNo());
        order.setUserId(dto.getUserId());
        order.setProductId(dto.getProductId());
        order.setQuantity(dto.getQuantity());
        order.setAmount(product.getPrice().multiply(new BigDecimal(dto.getQuantity())));
        order.setStatus(OrderStatus.CREATED.getCode());
        orderMapper.insert(order);
        
        // 4. 扣减用户余额
        accountMapper.decreaseBalance(dto.getUserId(), order.getAmount());
        
        // 5. 更新订单状态为已支付
        orderMapper.updateStatus(order.getId(), OrderStatus.PAID.getCode());
        
        // 6. 发送订单创建消息(异步)
        messageProducer.sendOrderCreatedMessage(order.getId());
        
        return order.getId();
    }
}

-- 慢查询:全表扫描
SELECT * FROM orders WHERE DATE(created_at) = '2026-03-29';
-- 问题:索引列使用了函数,索引失效

-- 优化后:范围查询,命中索引
SELECT * FROM orders 
WHERE created_at >= '2026-03-29 00:00:00' 
  AND created_at < '2026-03-30 00:00:00';

-- 慢查询:未命中索引的排序
SELECT * FROM orders 
WHERE user_id = 12345 
ORDER BY created_at DESC 
LIMIT 10;
-- 问题:如果有 idx_user_id,可以命中索引,但需要 filesort

-- 优化:创建组合索引
CREATE INDEX idx_user_created ON orders(user_id, created_at DESC);

EXPLAIN SELECT * FROM orders WHERE user_id = 12345;

-- 关键字段:
-- type:访问类型,const > eq_ref > ref > range > index > ALL
-- key:实际使用的索引
-- rows:预估扫描行数
-- Extra:Using index(覆盖索引)、Using filesort(文件排序)、Using temporary(临时表)

-- 推荐:完整的表设计
CREATE TABLE user_account (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    username VARCHAR(64) NOT NULL COMMENT '用户名',
    phone VARCHAR(20) COMMENT '手机号',
    email VARCHAR(100) COMMENT '邮箱',
    password VARCHAR(100) NOT NULL COMMENT '密码(加密)',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '状态:1正常,0禁用',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    deleted_at DATETIME COMMENT '删除时间(软删除)',
    
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_phone (phone),
    UNIQUE KEY uk_email (email),
    KEY idx_status (status),
    KEY idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户账户表';

-- × 不推荐:SELECT *
SELECT * FROM user_account WHERE status = 1;

-- √ 推荐:明确字段
SELECT id, username, phone, status FROM user_account WHERE status = 1;

-- × 不推荐:索引列使用函数
SELECT * FROM user_account WHERE DATE(created_at) = '2026-03-29';

-- √ 推荐:范围查询
SELECT * FROM user_account 
WHERE created_at >= '2026-03-29 00:00:00' 
  AND created_at < '2026-03-30 00:00:00';

-- × 不推荐:大偏移量分页
SELECT * FROM user_account ORDER BY id LIMIT 100000, 10;

-- √ 推荐:延迟关联
SELECT u.* FROM user_account u
INNER JOIN (
    SELECT id FROM user_account ORDER BY id LIMIT 100000, 10
) t ON u.id = t.id;

// × 不推荐:长事务
@Transactional
public void processOrder(Long orderId) {
    // 1. 查询订单
    Order order = orderMapper.selectById(orderId);
    
    // 2. 调用第三方支付接口(耗时操作)
    paymentService.callThirdPartyPayment(order);
    
    // 3. 更新订单状态
    orderMapper.updateStatus(orderId, OrderStatus.PAID);
    
    // 4. 发送短信通知(耗时操作)
    smsService.sendNotification(order);
    
    // 问题:事务包含了远程调用和消息发送,持有锁时间过长
}

// √ 推荐:缩短事务范围
public void processOrder(Long orderId) {
    // 1. 查询订单(不需要事务)
    Order order = orderMapper.selectById(orderId);
    
    // 2. 调用第三方支付接口(不需要事务)
    paymentService.callThirdPartyPayment(order);
    
    // 3. 更新订单状态(需要事务)
    orderService.updateOrderStatus(orderId, OrderStatus.PAID);
    
    // 4. 发送短信通知(异步,不需要事务)
    smsService.sendNotificationAsync(order);
}

@Service
public class OrderService {
    @Transactional
    public void updateOrderStatus(Long orderId, OrderStatus status) {
        orderMapper.updateStatus(orderId, status);
    }
}