面试题：SQLite性能调优之索引优化策略

Question

假设在一个具有复杂查询逻辑的SQLite数据库应用中，包含多个表关联查询，且数据量较大。请详细说明如何根据查询语句的特点，设计和优化索引以提升整体查询性能，并举例说明可能出现的索引滥用情况及其后果。

Answer 1

设计和优化索引提升查询性能

1. 分析查询语句：仔细研究每个查询语句，确定哪些列频繁用于 WHERE 子句、JOIN 子句以及 ORDER BY 子句等。例如，对于查询语句 SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id WHERE customers.city = 'New York' ORDER BY orders.order_date;，这里 orders.customer_id、customers.id、customers.city 和 orders.order_date 都是关键列。
2. 单表索引：
   • WHERE 子句列：如果查询经常基于某个列过滤数据，如上述 customers.city，创建单列索引 CREATE INDEX idx_customers_city ON customers(city);。这样在执行查询时，数据库可以快速定位到符合条件的行，而不需要全表扫描。
   • JOIN 子句列：在连接条件的列上创建索引很重要。例如 orders.customer_id 和 customers.id，为这两列分别创建索引。CREATE INDEX idx_orders_customer_id ON orders(customer_id); 和 CREATE INDEX idx_customers_id ON customers(id);。这样在执行 JOIN 操作时，数据库可以快速匹配相关联的行。
3. 复合索引：当多个列一起频繁用于查询条件时，可创建复合索引。对于上述查询，可创建复合索引 CREATE INDEX idx_orders_customers ON orders(customer_id, order_date);。复合索引的顺序很关键，一般将选择性高（重复值少）的列放在前面。在查询时，如果 WHERE 子句或 JOIN 子句能利用复合索引的前导列，就可以使用该索引来加速查询。
4. 覆盖索引：如果查询只需要某些列的数据，且这些列可以构成一个索引，那么创建覆盖索引可以避免回表操作。例如，对于查询 SELECT customer_id, order_date FROM orders WHERE order_amount > 1000;，可以创建覆盖索引 CREATE INDEX idx_orders_amount ON orders(order_amount, customer_id, order_date);。这样数据库在索引中就能获取到所需的数据，而无需再从表中读取，提高查询效率。

索引滥用情况及其后果

1. 过度创建索引：在每个列上都创建索引，会占用大量的存储空间。因为每个索引都需要额外的空间来存储索引数据。例如，一个表有100列，若为每列都创建索引，数据库文件大小会显著增加，不仅浪费磁盘空间，还会影响磁盘I/O性能。
2. 不必要的复合索引：创建复合索引时，如果组合的列并非经常一起用于查询条件，就是不必要的。例如创建 CREATE INDEX idx_unnecessary ON orders(customer_id, product_id);，而实际查询中很少同时基于这两列进行条件筛选。这样的索引不仅占用空间，在插入、更新和删除操作时，数据库还需要额外维护这些不必要的索引，导致操作性能下降。
3. 索引列顺序不当：在复合索引中，如果列的顺序不符合查询的使用模式，会导致索引无法有效利用。比如创建 CREATE INDEX idx_wrong_order ON orders(order_date, customer_id);，但查询主要是基于 customer_id 进行筛选，由于 customer_id 不是复合索引的前导列，该索引可能无法被使用，起不到优化作用。