面试题：MySQL在SAN上的索引优化策略

Question

在MySQL基于SAN存储时，从索引设计、使用和维护角度，详细说明如何进行索引优化以提升整体性能，可举例说明。

Answer 1

索引设计

1. 选择合适的列：
   • 选择经常出现在 WHERE 子句、连接条件（JOIN）中的列。例如，在一个订单表 orders 中，有 customer_id、order_date 等列，如果经常根据客户ID查询订单，那么 customer_id 就适合作为索引列。
   • 避免选择低基数（重复值多）的列，如性别列（通常只有男、女两个值），这类列建立索引对性能提升不大。
2. 复合索引：
   • 当多个列经常同时出现在 WHERE 子句中时，考虑创建复合索引。例如，在一个员工表 employees 中，经常根据 department 和 salary 范围查询员工，可创建复合索引 (department, salary)。注意复合索引的顺序，最常用的列放在前面。
   • 复合索引遵循最左前缀原则，即如果创建了 (col1, col2, col3) 的复合索引，只有在 WHERE col1 = value1 AND col2 = value2 AND col3 = value3 或者 WHERE col1 = value1 AND col2 = value2 或者 WHERE col1 = value1 等以 col1 开头的查询条件下才能使用该索引。
3. 覆盖索引：
   • 尽量设计覆盖索引，即索引包含查询所需的所有列。例如，查询语句 SELECT customer_id, order_amount FROM orders WHERE customer_id = 123，如果创建索引 (customer_id, order_amount)，那么查询可以直接从索引中获取数据，而无需回表操作，大大提升性能。

索引使用

1. 查询优化：
   • 确保查询语句能够使用到索引。例如，避免在索引列上使用函数，如 SELECT * FROM orders WHERE YEAR(order_date) = 2023，这种情况下索引 order_date 无法使用，应改为 SELECT * FROM orders WHERE order_date >= '2023 - 01 - 01' AND order_date < '2024 - 01 - 01'。
   • 避免使用 LIKE '%value'，这种查询无法使用索引，而 LIKE 'value%' 可以使用索引。如 SELECT * FROM products WHERE product_name LIKE 'apple%' 可以利用 product_name 列的索引。
2. 执行计划分析：
   • 使用 EXPLAIN 关键字分析查询语句的执行计划，查看索引是否被正确使用。例如，执行 EXPLAIN SELECT * FROM orders WHERE customer_id = 123，从执行计划中查看 key 字段，如果显示为 customer_id 索引名，则说明索引被正确使用；如果为 NULL，则说明索引未被使用，需要优化查询或索引设计。

索引维护

1. 定期重建索引：
   • 随着数据的不断插入、更新和删除，索引可能会出现碎片化，导致性能下降。在MySQL中，可以使用 ALTER TABLE table_name REBUILD INDEX index_name 来重建索引。例如，对于订单表 orders 的 customer_id 索引，可以执行 ALTER TABLE orders REBUILD INDEX customer_id_index，以优化索引结构，提升性能。
2. 删除无用索引：
   • 定期检查数据库中的索引，删除那些不再被使用的索引。无用索引不仅占用存储空间，还会在数据修改时增加维护成本。可以通过分析查询日志或者使用一些工具（如pt - advisor）来找出无用索引，然后使用 DROP INDEX index_name ON table_name 命令删除，如 DROP INDEX unused_index ON products。