MySQL：从索引优化到秒杀锁机制

CREATE TABLE `product` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `shop_id` bigint unsigned NOT NULL,
  `category_id` int unsigned NOT NULL,
  `status` tinyint unsigned NOT NULL COMMENT '1-上架 2-下架',
  `create_time` datetime NOT NULL,
  `title` varchar(200) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_shop_category` (`shop_id`, `category_id`),  -- 现有索引
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB;
​

SELECT * FROM product 
WHERE shop_id = 10086 
  AND category_id = 10 
  AND status = 1 
ORDER BY create_time DESC 
LIMIT 20;

ALTER TABLE product ADD INDEX `idx_cover` 
(shop_id, category_id, status, create_time, title, price);

SELECT t.* FROM product t
INNER JOIN (
    SELECT id FROM product 
    WHERE shop_id = 10086 AND category_id = 10 AND status = 1 
    ORDER BY create_time DESC 
    LIMIT 10000, 20  -- 只在索引层扫描，不回表
) tmp ON t.id = tmp.id;

-- 第一页
SELECT * FROM product 
WHERE shop_id = 10086 
ORDER BY create_time DESC, id DESC 
LIMIT 20;
​
-- 第二页（记住上一页最后一条的时间戳）
SELECT * FROM product 
WHERE shop_id = 10086 
  AND (create_time < '2024-03-20 10:00:00' 
       OR (create_time = '2024-03-20 10:00:00' AND id < 12345))
ORDER BY create_time DESC, id DESC 
LIMIT 20;

@Transactional
public void deduct(Long skuId) {
    Stock stock = stockMapper.selectBySkuId(skuId);  // 快照读，无锁！
    if (stock.getCount() > 0) {
        stockMapper.decreaseCount(skuId);  // UPDATE有锁，但判断逻辑已错过
    }
}

t1: 线程A SELECT → count=1（快照读）
t1: 线程B SELECT → count=1（快照读，同时成功）
t2: 线程A 判断>0 → 准备扣减
t2: 线程B 判断>0 → 准备扣减（都认为自己能买）
t3: 线程A UPDATE → count=0
t4: 线程B UPDATE → count=-1（超卖！）

@Transactional
public void deduct(Long skuId) {
    // 当前读 + 排他锁（X锁），阻塞其他事务
    Stock stock = stockMapper.selectForUpdate(skuId);
    if (stock.getCount() > 0) {
        stockMapper.decreaseCount(skuId);
    }
}

UPDATE stock 
SET count = count - 1, version = version + 1 
WHERE sku_id = 1001 AND version = #{oldVersion};

UPDATE stock 
SET count = count - 1 
WHERE sku_id = 1001 AND count > 0;

@Transactional
public boolean deduct(Long skuId) {
    int rows = stockMapper.decreaseWithCondition(skuId);
    return rows > 0;  // rows=0表示库存不足或并发冲突
}

MySQL优化体系
├── 查询优化
│   ├── 索引设计
│   │   ├── 联合索引：等值查询列在前，排序列在后
│   │   └── 覆盖索引：避免回表（Using index）
│   ├── 排序优化
│   │   ├── 消除Filesort：利用索引天然有序性
│   │   └── 降序索引：MySQL 8.0支持DESC
│   ├── 分页优化
│   │   ├── 延迟关联：子查询先limit再回表
│   │   └── 游标分页：Keyset（时间戳+ID）替代OFFSET
│   └── 引擎优化
│       └── ICP：条件下推至存储引擎层过滤
└── 事务与锁
    ├── 隔离级别（RR）
    │   ├── MVCC：快照读实现一致性非锁定读
    │   └── Next-Key Lock：防幻读（Record+Gap）
    ├── 锁类型
    │   ├── 行锁：命中索引
    │   └── 表锁：未命中索引（全表扫描）
    └── 并发控制
        ├── 悲观锁：SELECT FOR UPDATE（阻塞）
        ├── 乐观锁：Version CAS（重试）
        └── 数据库原子性：UPDATE WHERE（无锁化）