面试题：MySQL中如何在复杂表结构下避免冗余索引

Question

假设存在一个包含多个关联字段且有复杂查询需求的MySQL表，在这种情况下，阐述你如何设计索引以避免冗余，同时满足查询性能要求，请结合具体的SQL语句及索引设计思路说明。

Answer 1

索引设计思路

1. 覆盖索引：对于常见的查询，设计索引时尽量包含查询中涉及的所有列，这样查询时可直接从索引中获取数据，无需回表操作，提升查询性能。例如对于查询SELECT column1, column2, column3 FROM your_table WHERE condition;，若经常有此查询，可创建复合索引CREATE INDEX idx_column1_column2_column3 ON your_table (column1, column2, column3);。
2. 最左前缀原则：复合索引在使用时遵循最左前缀原则。如果查询条件为WHERE column1 = value1 AND column2 = value2;，则索引CREATE INDEX idx_column1_column2 ON your_table (column1, column2);可有效利用。若查询为WHERE column2 = value2;，此索引则无法使用。
3. 避免冗余索引：冗余索引是指一个索引的前导列是另一个索引的前缀。例如已有索引CREATE INDEX idx_column1_column2 ON your_table (column1, column2);，再创建CREATE INDEX idx_column1 ON your_table (column1);就是冗余的，因为前者已包含后者功能。
4. 分析查询频率：对频繁查询的字段组合创建索引，对于很少使用的查询条件字段，可不创建索引，避免索引过多影响插入、更新性能。

具体SQL语句示例

假设有表orders，包含字段order_id, customer_id, order_date, total_amount，常见查询有：

1. 根据客户ID查询订单信息

SELECT order_id, order_date, total_amount FROM orders WHERE customer_id = 123;

此时可创建索引CREATE INDEX idx_customer_id ON orders (customer_id); 2. 根据客户ID和订单日期范围查询订单信息

SELECT order_id, total_amount FROM orders WHERE customer_id = 123 AND order_date BETWEEN '2023 - 01 - 01' AND '2023 - 12 - 31';

可创建复合索引CREATE INDEX idx_customer_id_order_date ON orders (customer_id, order_date); ，因遵循最左前缀原则，先列customer_id 。 3. 若有查询SELECT customer_id, order_date FROM orders WHERE total_amount > 1000; 可创建索引CREATE INDEX idx_total_amount ON orders (total_amount);，若此查询不频繁，可不创建此索引以避免过多索引影响写操作性能。