面试题：MySQL多列索引优化查询场景

Question

在一个电子商务数据库中，有`orders`表，字段包括`customer_id`、`order_date`、`order_status`和`total_amount`。业务需求是快速查询出特定客户（`customer_id`）在某一时间段内（`order_date`），且订单状态为已完成（`order_status = 'completed'`）的订单，并按`total_amount`降序排列。你如何构建多列索引来优化这个查询？请详细说明思路和索引构建语句。

Answer 1

1. 思路：
   • 此查询涉及到customer_id、order_date和order_status这几个字段的筛选，以及按total_amount排序。
   • 为了优化查询，索引应该包含查询中使用到的过滤条件字段和排序字段。由于查询条件有customer_id、order_date和order_status，排序字段是total_amount，所以可以创建一个复合索引。
   • 复合索引的字段顺序很重要，应该将选择性高（区分度大）的字段放在前面。一般来说，customer_id区分度较高，然后是order_date，接着是order_status，最后是total_amount。这样可以最大程度利用索引来加速查询。
2. 索引构建语句（以MySQL为例）：

CREATE INDEX idx_customer_date_status_amount ON orders (customer_id, order_date, order_status, total_amount);

在上述语句中，CREATE INDEX用于创建索引，idx_customer_date_status_amount是自定义的索引名称，ON orders表示在orders表上创建索引，括号内按顺序列出了索引包含的字段。

不同数据库对于索引创建的语法可能略有不同，比如在Oracle中创建复合索引的语法如下：

CREATE INDEX idx_customer_date_status_amount ON orders (customer_id, order_date, order_status, total_amount);

语法基本类似，但在一些细节和特定功能上可能存在差异。例如，Oracle有更多的索引类型和参数可设置，如FREELIST等相关设置，这里仅以简单创建复合索引为例。

在SQL Server中创建复合索引语法如下：

CREATE INDEX idx_customer_date_status_amount ON orders (customer_id, order_date, order_status, total_amount);

语法和MySQL、Oracle类似，但SQL Server也有其独特的索引选项，如INCLUDE关键字等，可用于覆盖索引等操作。