面试题：MySQL在高并发场景下索引与锁优化的综合方案设计

索引设计原则

选择合适的列：在商品库存扣减场景中，商品ID列适合创建索引，因为查询和更新操作常基于商品ID。例如，在products表中，对product_id列创建索引。
```
CREATE INDEX idx_product_id ON products(product_id);
```
在订单创建场景，用户ID、订单创建时间等常用查询过滤的列也可创建索引。比如在orders表中，对user_id和create_time创建复合索引：
```
CREATE INDEX idx_user_create_time ON orders(user_id, create_time);
```
避免过多索引：过多索引会增加写操作的开销，因为每次数据更新时，索引也需要同步更新。只针对经常用于查询条件、连接条件的列创建索引。
前缀索引：对于较长的字符串列，可以使用前缀索引来减少索引空间占用。例如，商品描述字段product_description很长，可以对前10个字符创建前缀索引：
```
CREATE INDEX idx_product_desc ON products(product_description(10));
```

锁的选择

商品库存扣减：通常使用排他锁（FOR UPDATE）。因为库存扣减操作必须保证原子性，防止多个并发请求同时扣减库存导致超卖。

START TRANSACTION;
SELECT stock FROM products WHERE product_id = 1 FOR UPDATE;
-- 检查库存并进行扣减逻辑
UPDATE products SET stock = stock - 1 WHERE product_id = 1;
COMMIT;

订单创建：一般情况下，如果订单创建不涉及与其他订单的竞争关系（如不基于订单号等唯一约束进行并发操作），可不用锁。但如果存在如订单编号唯一性校验等场景，可以使用共享锁（LOCK IN SHARE MODE）。

START TRANSACTION;
SELECT order_number FROM orders WHERE order_number = '202310010001' LOCK IN SHARE MODE;
-- 检查订单编号是否存在，如果不存在则插入订单
INSERT INTO orders (order_number, user_id, create_time) VALUES ('202310010001', 1, NOW());
COMMIT;

锁粒度控制

行级锁：在商品库存扣减和订单创建场景中，尽量使用行级锁。例如上述库存扣减和订单创建的SQL示例，都是基于某一行数据进行操作，行级锁能减少锁的争用范围，提高并发性能。以库存扣减为例，FOR UPDATE锁只锁住products表中product_id对应的那一行数据，其他行的数据仍可被并发访问。
表级锁：除非必要，应避免使用表级锁。因为表级锁会锁住整个表，大大降低系统并发性能。只有在需要对整个表进行操作，且操作频率较低的情况下才考虑使用，如批量删除订单时：
```
LOCK TABLES orders WRITE;
DELETE FROM orders WHERE create_time < '2023-01-01';
UNLOCK TABLES;
```

性能优化思路

优化查询语句：确保查询语句能够有效地利用索引。使用EXPLAIN关键字分析查询计划，查看索引是否被正确使用。例如：
```
EXPLAIN SELECT stock FROM products WHERE product_id = 1 FOR UPDATE;
```
根据分析结果调整查询语句或索引设计。
合理设置事务隔离级别：不同的事务隔离级别对锁的使用和并发性能有影响。对于电商项目，通常可选择READ - COMMITTED隔离级别，在保证数据一致性的同时，有较好的并发性能。可通过以下语句设置：
```
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
```
缓存机制：对于一些不经常变化的数据，如商品基本信息等，可以使用缓存（如Redis）。在库存扣减和订单创建时，先从缓存中读取数据，减少对数据库的直接访问，提高系统响应速度。例如，在扣减库存前先从Redis中获取库存信息，扣减后再更新Redis缓存和数据库。
异步处理：对于一些非关键的操作，如订单创建后的日志记录等，可以采用异步处理的方式。使用消息队列（如RabbitMQ、Kafka）将这些任务发送到队列中，由专门的消费者进行处理，避免阻塞订单创建的主流程，提高系统整体性能。

星途面试题库

面试题：MySQL在高并发场景下索引与锁优化的综合方案设计

知识考点

面试题答案

索引设计原则

锁的选择

锁粒度控制

性能优化思路