MySQL(57)如何选择合适的索引列？

Victor356
59 阅读4分钟

选择合适的索引列是数据库性能优化中的关键任务。合理选择索引列不仅能显著提高查询性能，还能避免不必要的存储和维护开销。以下是选择索引列的一些原则和详细解释，并结合代码示例说明。

选择索引列的原则

  1. 高选择性列：选择性高的列能显著减少返回的行数，提高查询效率。
  2. 频繁使用的列：经常出现在 WHERE、JOIN、ORDER BY 和 GROUP BY 子句中的列。
  3. 前缀列：在复合索引中，前缀列的选择非常重要。
  4. 避免低选择性列：如性别、布尔值等列，选择性低，不适合作为索引列。
  5. 考虑查询模式：根据实际查询需求设计索引。
  6. 限制索引数量：索引并不是越多越好，过多的索引会增加维护开销。

示例表结构

假设有一个名为 employees 的表，用于存储员工信息：

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2),
    hire_date DATE,
    gender CHAR(1)
);

选择索引列的示例

示例查询1：高选择性列

查询特定薪水范围内的员工：

SELECT emp_name, department_id FROM employees WHERE salary > 60000;

salary 列的选择性较高，适合作为索引列：

CREATE INDEX idx_salary ON employees(salary);

示例查询2：频繁使用的列

频繁查询特定部门的员工：

SELECT emp_name, salary FROM employees WHERE department_id = 2;

department_id 列在查询中频繁使用，适合作为索引列：

CREATE INDEX idx_department_id ON employees(department_id);

示例查询3：复合索引列

组合查询部门和薪水的员工：

SELECT emp_name, hire_date FROM employees WHERE department_id = 2 AND salary > 50000;

department_id 和 salary 的组合出现在查询中，可以创建复合索引：

CREATE INDEX idx_dept_salary ON employees(department_id, salary);

示例查询4：避免低选择性列

查询性别为男性的员工：

SELECT emp_name, department_id FROM employees WHERE gender = 'M';

gender 列的选择性较低，不适合作为单独的索引列。在这种情况下，可以考虑创建复合索引，例如：

CREATE INDEX idx_gender_dept ON employees(gender, department_id);

使用 EXPLAIN 验证索引选择

使用 EXPLAIN 分析查询的执行计划，验证是否使用了创建的索引。

验证示例查询1

EXPLAIN SELECT emp_name, department_id FROM employees WHERE salary > 60000;

假设 EXPLAIN 输出如下：

+----+-------------+----------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key      | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | employees| NULL       | range | idx_salary    | idx_salary| 5     | NULL | 100   |   50.00  | Using where |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+

key 列中显示 idx_salary，表示查询使用了索引 idx_salary。

验证示例查询2

EXPLAIN SELECT emp_name, salary FROM employees WHERE department_id = 2;

假设 EXPLAIN 输出如下：

+----+-------------+----------+------------+-------+----------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys  | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+----------------+---------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employees| NULL       | ref   | idx_department_id| idx_department_id| 4    | const | 100  |   100.00 | Using where |
+----+-------------+----------+------------+-------+----------------+---------------+---------+-------+------+----------+-------------+

key 列中显示 idx_department_id，表示查询使用了索引 idx_department_id。

验证示例查询3

EXPLAIN SELECT emp_name, hire_date FROM employees WHERE department_id = 2 AND salary > 50000;

假设 EXPLAIN 输出如下：

+----+-------------+----------+------------+-------+---------------------+----------------+---------+------+-------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys       | key            | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------------+----------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | employees| NULL       | ref   | idx_dept_salary     | idx_dept_salary| 8       | const| 100   |   50.00  | Using where |
+----+-------------+----------+------------+-------+---------------------+----------------+---------+------+-------+----------+-------------+

key 列中显示 idx_dept_salary，表示查询使用了复合索引 idx_dept_salary。

索引选择的高级策略

1. 涉及范围查询的索引列

范围查询会影响索引的使用。例如，以下查询：

SELECT emp_name, hire_date FROM employees WHERE department_id = 2 AND hire_date > '2023-01-01';

我们可以创建复合索引，但需要注意范围查询的列应放在索引的后面：

CREATE INDEX idx_dept_hire_date ON employees(department_id, hire_date);

2. 使用覆盖索引

覆盖索引能显著提高查询性能，当查询所有的列都包含在索引中时，数据库可以完全从索引中读取数据：

CREATE INDEX idx_covering ON employees(department_id, salary, emp_name);

这个索引可以优化以下查询：

SELECT emp_name FROM employees WHERE department_id = 2 AND salary > 50000;

3. 分析查询性能和重新设计索引

定期使用 EXPLAIN 分析查询性能，确保索引设计的有效性。如果查询性能不佳，可能需要重新设计索引。

小结

选择合适的索引列是数据库优化中的关键步骤。通过理解高选择性列、频繁使用的列和查询模式，可以有效设计索引，显著提高查询性能。使用 EXPLAIN 分析查询执行计划，可以验证索引的有效性，并根据需要调整索引设计。合理的索引策略不仅能提高查询速度，还能优化存储和维护开销，确保数据库系统的高效运行。

avatar
文章
阅读
粉丝