面试题：MariaDB中索引优化策略相关问题

Question

在MariaDB中，当查询性能出现问题时，从索引角度考虑，你会采取哪些优化策略？请举例说明不同类型索引（如普通索引、唯一索引、联合索引）在优化查询时的适用场景。

Answer 1

从索引角度优化 MariaDB 查询性能的策略

1. 检查索引是否缺失：

   • 分析查询语句，确定频繁用于 WHERE、JOIN 等条件中的列。例如查询语句 SELECT * FROM users WHERE email = '[e-mail]';，如果 email 列没有索引，应考虑添加索引。可以使用 EXPLAIN 关键字查看查询执行计划，若某列在 possible_keys 中无索引，而查询又频繁使用该列过滤数据，就需要添加索引。

2. 优化现有索引：

   • 普通索引：
     • 适用场景：适用于经常在 WHERE 子句中作为条件查询，但数据不要求唯一的列。例如在一个博客系统中，文章的分类列 category，一篇文章可能属于某个分类，不同文章可以属于相同分类。查询 SELECT * FROM articles WHERE category = '技术'; 这种情况下，在 category 列添加普通索引 CREATE INDEX idx_category ON articles (category); 能提升查询性能，因为索引可以帮助快速定位到符合条件的行。
   • 唯一索引：
     • 适用场景：当列中的数据必须保证唯一性时使用。比如用户表中的 username 列，每个用户的用户名必须唯一。创建唯一索引 CREATE UNIQUE INDEX idx_username ON users (username);，不仅能保证数据的唯一性，而且在查询 SELECT * FROM users WHERE username = 'specific_user'; 时，由于唯一性，数据库引擎能更高效地定位到目标行，提升查询效率。
   • 联合索引：
     • 适用场景：当多个列经常一起出现在 WHERE 子句中作为条件时使用。例如在订单表 orders 中，经常根据 customer_id 和 order_date 进行查询 SELECT * FROM orders WHERE customer_id = 123 AND order_date >= '2023 - 01 - 01';，可以创建联合索引 CREATE INDEX idx_customer_date ON orders (customer_id, order_date);。注意联合索引遵循最左前缀原则，即查询条件必须从索引的最左边开始使用，若查询 SELECT * FROM orders WHERE order_date >= '2023 - 01 - 01';，此联合索引将不会被使用，因为没有从最左边的 customer_id 开始。

3. 删除冗余和无效索引：

   • 冗余索引是指存在多个索引，其中一个索引的列是另一个索引列的前缀。例如已经有索引 CREATE INDEX idx_fullname ON users (first_name, last_name);，再创建 CREATE INDEX idx_firstname ON users (first_name); 就是冗余的，可删除。无效索引是指在查询执行计划中从未被使用过的索引，通过 EXPLAIN 以及数据库的查询日志分析等手段找出并删除，以减少索引维护的开销。