MySQL索引优化实战：创建合适的索引

理解 MySQL 索引

索引是什么

MySQL 索引是一种数据结构，它可以帮助数据库快速定位和访问表中的数据。就好比一本书的目录，通过目录可以快速找到你想要的章节内容，而不需要逐页翻阅整本书。在数据库中，当执行查询语句时，如果没有索引，数据库可能需要全表扫描，即遍历表中的每一行数据来找到符合条件的记录，这在数据量较大时效率极低。而索引则通过特定的数据结构（如 B - Tree 等）存储了表中某些列的值以及对应行的物理位置或逻辑位置信息，使得数据库能够快速定位到满足查询条件的数据行。

索引的作用

1. 提高查询性能：最主要的作用就是加快数据的检索速度。例如，在一个包含数百万条用户记录的表中，要查找某个特定用户，如果没有索引，可能需要扫描数百万行数据。但如果在用户 ID 列上创建了索引，数据库可以直接通过索引快速定位到对应的用户记录，大大减少了查询时间。
2. 保证数据唯一性：某些类型的索引（如唯一索引）可以确保表中某列或多列组合的值是唯一的。比如，用户表中的邮箱列，通过创建唯一索引，可以防止出现重复的邮箱地址，保证数据的完整性和一致性。
3. 支持数据排序：当查询需要对结果进行排序时，如果排序的列上有索引，数据库可以利用索引的有序性来快速完成排序操作，而不需要在内存中进行额外的排序处理，提高了查询效率。

索引的数据结构

1. B - Tree 索引：这是 MySQL 中最常用的索引类型。B - Tree 是一种平衡树结构，它的每个节点最多可以有多个子节点，并且树的高度相对较低，这使得在查找数据时，平均需要比较的次数较少。例如，在一个 B - Tree 索引中查找某个值，从根节点开始，根据节点存储的键值范围决定下一步是向左子树还是右子树查找，直到找到目标值或者确定目标值不存在。B - Tree 索引适用于范围查询、精确匹配查询等多种场景。
2. Hash 索引：Hash 索引基于哈希表实现，它通过对索引列的值进行哈希计算，得到一个哈希值，然后根据这个哈希值直接定位到数据所在的位置。Hash 索引的优点是在精确匹配查询时速度非常快，因为它直接通过哈希值定位数据，不需要像 B - Tree 索引那样进行树的遍历。但它的缺点也很明显，不支持范围查询，例如不能使用大于、小于等比较运算符进行范围查找，因为哈希值之间并没有顺序关系。
3. 全文索引：主要用于文本类型的列，如文章内容、产品描述等。全文索引会对文本内容进行分词处理，然后构建索引。与普通的字符匹配不同，全文索引支持更复杂的查询，如模糊查询、语义查询等，能够更好地满足对文本数据的搜索需求。

创建索引的原则

选择合适的列创建索引

1. 频繁用于查询条件的列：如果一个列经常在 WHERE 子句中作为查询条件使用，那么在该列上创建索引通常会显著提高查询性能。例如，在订单表中，经常根据订单状态（如 “已支付”、“未支付”）来查询订单，那么可以在订单状态列上创建索引。

-- 在 orders 表的 status 列上创建普通索引
CREATE INDEX idx_status ON orders (status);

2. 用于连接条件的列：当使用 JOIN 操作连接多个表时，连接条件中的列应该创建索引。比如，订单表 orders 和用户表 users 通过用户 ID 进行连接，在 orders 表的 user_id 列和 users 表的 id 列上都应该创建索引。

-- 在 orders 表的 user_id 列上创建索引
CREATE INDEX idx_user_id ON orders (user_id);
-- 在 users 表的 id 列上创建索引（通常 id 列会是主键，本身就有索引，但这里作为示例说明）
CREATE INDEX idx_id ON users (id);

3. 用于排序的列：如果查询需要对某个列进行排序（ORDER BY 子句），在该列上创建索引有助于提高排序效率。例如，在文章表中，经常按照发布时间对文章进行排序展示，那么可以在发布时间列上创建索引。

-- 在 articles 表的 publish_time 列上创建索引
CREATE INDEX idx_publish_time ON articles (publish_time);

避免过度索引

1. 索引占用额外空间：每个索引都会占用一定的磁盘空间，随着索引数量的增加，数据库占用的存储空间也会显著增加。例如，一个包含大量数据的表，如果为每个列都创建索引，可能会导致数据库文件大小成倍增长，这不仅浪费存储空间，还会增加备份和恢复的时间及成本。
2. 降低数据修改性能：当对表中的数据进行插入、更新或删除操作时，数据库不仅要更新表中的数据，还要同时更新相关的索引。过多的索引会导致这些操作变得缓慢，因为数据库需要花费更多的时间来维护索引的一致性。例如，在一个有多个索引的表中插入一条新记录，数据库需要在每个索引中插入相应的索引项，这就增加了插入操作的时间开销。

索引列的基数

1. 什么是基数：基数指的是某列中不同值的数量。例如，在性别列中，可能只有 “男” 和 “女” 两个值，基数为 2；而在用户 ID 列中，每个用户都有唯一的 ID，基数就等于用户的数量。基数越高，索引的选择性就越好，即通过索引能够更准确地定位到数据。
2. 基数与索引性能的关系：对于基数较低的列，创建索引可能并不会带来明显的性能提升。例如，在一个有 100 万条记录的表中，性别列的基数为 2，如果在性别列上创建索引，数据库在查询时，即使使用了索引，也只能过滤掉一半的数据，仍然需要扫描大量的行，不如直接全表扫描效率高。因此，应该优先在基数较高的列上创建索引。

常见索引类型及创建方法

普通索引

1. 定义：普通索引是最基本的索引类型，它没有唯一性限制，可以包含重复的值。普通索引主要用于提高查询性能，在经常用于查询条件的列上创建普通索引是常见的优化手段。
2. 创建方法：
   • 使用 CREATE INDEX 语句：

-- 在 employees 表的 last_name 列上创建普通索引
CREATE INDEX idx_last_name ON employees (last_name);

- 在创建表时定义索引：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    INDEX idx_last_name (last_name)
);

唯一索引

1. 定义：唯一索引要求索引列的值必须唯一，不允许出现重复值。唯一索引不仅可以提高查询性能，还能保证数据的唯一性和完整性。例如，用户表中的邮箱列通常应该创建唯一索引，以确保每个用户的邮箱地址不重复。
2. 创建方法：
   • 使用 CREATE UNIQUE INDEX 语句：

-- 在 users 表的 email 列上创建唯一索引
CREATE UNIQUE INDEX idx_email ON users (email);

- 在创建表时定义唯一索引：

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    UNIQUE (email)
);

主键索引

1. 定义：主键索引是一种特殊的唯一索引，它的特点是不允许为空值，并且每个表只能有一个主键。主键通常用于唯一标识表中的每一行记录，是数据库中非常重要的概念。例如，订单表中的订单编号列可以作为主键，通过订单编号可以准确地定位到某一个订单记录。
2. 创建方法：
   • 在创建表时定义主键：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT
);

- 使用 `ALTER TABLE` 语句添加主键：

ALTER TABLE orders
ADD PRIMARY KEY (order_id);

组合索引

1. 定义：组合索引是指在多个列上创建的索引，也称为联合索引。组合索引可以提高涉及多个列的查询性能，并且在某些情况下，比单独为每个列创建索引更有效。例如，在订单详情表中，经常根据订单 ID 和产品 ID 来查询订单详情，那么可以在这两个列上创建组合索引。
2. 创建方法：
   • 使用 CREATE INDEX 语句：

-- 在 order_items 表的 order_id 和 product_id 列上创建组合索引
CREATE INDEX idx_order_product ON order_items (order_id, product_id);

- 在创建表时定义组合索引：

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    INDEX idx_order_product (order_id, product_id)
);

索引优化实战案例

案例一：单表查询优化

1. 场景描述：有一个员工表 employees，包含员工 ID（employee_id）、姓名（name）、部门（department）、入职时间（hire_date）等字段。现在经常需要根据部门名称查询该部门的员工信息。
2. 初始状态：表结构如下：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    hire_date DATE
);

在没有为 department 列创建索引之前，执行查询语句：

SELECT * FROM employees WHERE department = '销售部';

当数据量较大时，查询速度较慢，因为需要全表扫描。 3. 优化措施：为 department 列创建普通索引：

CREATE INDEX idx_department ON employees (department);

4. 优化效果：再次执行上述查询语句，查询速度显著提升。因为数据库可以通过 idx_department 索引快速定位到 department 为 “销售部” 的员工记录，而不需要扫描整个表。

案例二：多表连接查询优化

1. 场景描述：有两个表，订单表 orders 和订单详情表 order_items。orders 表包含订单 ID（order_id）、用户 ID（user_id）、订单日期（order_date）等字段；order_items 表包含订单详情 ID（order_item_id）、订单 ID（order_id）、产品 ID（product_id）、数量（quantity）等字段。现在需要查询某个用户在特定日期范围内的所有订单及其详情信息。
2. 初始状态：表结构如下：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT
);

查询语句为：

SELECT *
FROM orders
JOIN order_items ON orders.order_id = order_items.order_id
WHERE orders.user_id = 1 AND orders.order_date BETWEEN '2023 - 01 - 01' AND '2023 - 12 - 31';

由于没有合适的索引，查询性能较差，特别是在数据量较大时，连接操作和条件过滤都需要大量的时间。 3. 优化措施： - 在 orders 表的 user_id 和 order_date 列上创建组合索引：

CREATE INDEX idx_user_date ON orders (user_id, order_date);

- 在 `order_items` 表的 `order_id` 列上创建索引：

CREATE INDEX idx_order_id ON order_items (order_id);

4. 优化效果：优化后，上述查询语句的执行速度明显加快。idx_user_date 索引可以快速定位到符合用户 ID 和日期范围的订单记录，而 idx_order_id 索引则加速了 orders 表和 order_items 表之间的连接操作。

案例三：避免索引失效

1. 场景描述：有一个产品表 products，包含产品 ID（product_id）、产品名称（product_name）、价格（price）等字段。在 product_name 列上创建了索引。现在执行一个查询语句，尝试获取产品名称以 “手机” 开头的产品信息。
2. 初始状态：表结构如下：

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2),
    INDEX idx_product_name ON products (product_name)
);

查询语句为：

SELECT * FROM products WHERE product_name LIKE '%手机';

虽然 product_name 列上有索引，但由于 LIKE 操作符使用了前置通配符（%），索引会失效，数据库只能进行全表扫描，查询效率较低。 3. 优化措施：修改查询语句，将前置通配符改为后置通配符：

SELECT * FROM products WHERE product_name LIKE '手机%';

4. 优化效果：修改后的查询语句可以利用 idx_product_name 索引，查询速度得到提升。因为后置通配符的 LIKE 查询可以使用索引进行前缀匹配，而前置通配符会使索引无法发挥作用。

索引的维护与监控

索引的维护

1. 重建索引：随着数据的不断插入、更新和删除，索引可能会出现碎片化，导致性能下降。重建索引可以重新组织索引结构，提高索引的性能。在 MySQL 中，可以使用 ALTER TABLE 语句来重建索引。例如，对于 employees 表的 idx_last_name 索引，可以这样重建：

ALTER TABLE employees DROP INDEX idx_last_name;
CREATE INDEX idx_last_name ON employees (last_name);

2. 优化索引：MySQL 提供了 OPTIMIZE TABLE 语句，它不仅可以优化表的数据存储，还可以优化索引。该语句会重新组织表的物理存储，合并相邻的空闲空间，并且重新构建索引，以提高性能。例如：

OPTIMIZE TABLE employees;

索引的监控

1. 使用 EXPLAIN 关键字：EXPLAIN 是 MySQL 中非常重要的一个关键字，用于分析查询语句的执行计划。通过 EXPLAIN，可以查看查询语句是否使用了索引，以及使用了哪些索引，从而判断索引是否有效。例如，对于查询语句 SELECT * FROM employees WHERE department = '研发部';，使用 EXPLAIN 分析如下：

EXPLAIN SELECT * FROM employees WHERE department = '研发部';

执行上述语句后，会返回一个结果集，其中包含 id（查询的序列号）、select_type（查询类型）、table（涉及的表）、partitions（分区信息）、type（连接类型，如 ALL 表示全表扫描，index 表示使用索引等）、possible_keys（可能使用的索引）、key（实际使用的索引）、key_len（索引长度）、ref（哪些列或常量与索引进行比较）、rows（估计需要扫描的行数）、filtered（按表条件过滤的行百分比）等信息。通过分析这些信息，可以判断索引是否正常使用以及是否需要优化。 2. 查看索引统计信息：MySQL 提供了一些系统表和函数来查看索引的统计信息。例如，可以通过 information_schema.statistics 表来获取索引的相关信息。查询某个表的索引信息：

SELECT *
FROM information_schema.statistics
WHERE table_schema = 'your_database_name' AND table_name = 'your_table_name';

这个查询会返回指定数据库中指定表的所有索引的详细信息，包括索引名称、索引类型、索引列等。通过这些信息，可以了解索引的结构和使用情况，以便进行优化。

总结

在 MySQL 数据库中，创建合适的索引是优化查询性能的关键。通过深入理解索引的原理、选择合适的列创建索引、避免过度索引以及根据不同的查询场景选择合适的索引类型，可以显著提高数据库的性能。同时，定期对索引进行维护和监控，确保索引始终处于最佳状态，对于保证数据库的高效运行也至关重要。在实际开发和运维过程中，需要根据具体的业务需求和数据特点，灵活运用索引优化技术，以达到最优的性能表现。