如何为带WHERE quantity>0与ORDER BY的MySQL查询创建高效索引

你的思路完全可行，但需要修正索引创建的语法，同时结合MySQL的特性来实现：

一、适用MySQL 8.0.13+版本：直接创建表达式索引 #

MySQL从8.0.13开始支持表达式索引，可以直接将quantity > 0作为索引的第一个字段，搭配查询条件字段和排序字段创建复合索引，这样既能快速过滤有效数据，又能让排序直接利用索引顺序。

正确的索引创建语句如下：

针对按仓库查询的场景 #

CREATE INDEX idx_stocks_warehouse_active ON stocks ((quantity > 0), warehouse_id, updated_at);

针对按商品查询的场景 #

CREATE INDEX idx_stocks_product_active ON stocks ((quantity > 0), product_id, updated_at);

索引生效逻辑：

• 索引第一个字段是表达式(quantity > 0)，MySQL会将其计算为布尔值（1代表quantity>0，0代表quantity<=0），查询时WHERE quantity > 0会精准匹配索引中值为1的条目，直接过滤掉无效数据。
• 第二个字段是查询中的等值条件（warehouse_id或product_id），进一步缩小查询范围。
• 第三个字段是updated_at，由于索引本身是有序存储的，ORDER BY updated_at DESC/ASC可以直接使用索引的顺序，避免额外的排序操作（即执行计划的Extra列不会出现Using filesort）。

二、适用MySQL 8.0.13以下版本：新增计算列替代 #

如果你的MySQL版本较低，不支持表达式索引，可以新增一个存储型计算列，基于quantity > 0生成，再基于这个列创建索引：

1. 新增计算列：

ALTER TABLE stocks ADD COLUMN is_active TINYINT GENERATED ALWAYS AS (quantity > 0) STORED;

2. 创建对应索引：

CREATE INDEX idx_stocks_warehouse_active ON stocks (is_active, warehouse_id, updated_at);
CREATE INDEX idx_stocks_product_active ON stocks (is_active, product_id, updated_at);

3. 查询时可以直接使用is_active = 1替代quantity > 0，效果完全一致，同样能利用索引完成过滤和排序。

注意事项 #

• 无需单独为quantity创建单字段索引，上述复合索引已经覆盖了你所有核心查询场景，效率更高。
• 验证索引是否生效，可以使用EXPLAIN命令查看执行计划，确认type列是ref或range，且Extra列没有Using filesort和冗余的Using where。

内容的提问来源于stack exchange，提问作者HubertNNN