SQL查询优化：如何根据查询语句创建索引

针对以下 SQL 语句：

select `id`, `name`, `price`, `num`, `sell_num`, `picture`, `is_need_quota`, `background_picture`, `discount_price`, `appid` from `box` where `category_id` = 2 and `start_time` < '2023-04-03 16:57:59' and `end_time` > '2023-04-03 16:57:59' and `status` = 1 and `is_need_quota` = 0 and (num =0 or num > sell_num) and `is_need_quot` = 1 and `deleted_at` is null order by `weight` desc, `price` asc

可以建立如下索引：

1. category_id
2. start_time
3. end_time
4. status
5. is_need_quota
6. num
7. sell_num
8. is_need_quot
9. deleted_at
10. weight
11. price

建议采用联合索引的方式，将查询条件中经常出现的字段放在前面建立索引，可以提高查询效率。其中，索引的顺序要与查询条件中字段的顺序相同。同时，为了优化排序操作，可以在最后加上排序字段的索引。