一、MySQL 索引概述

索引是数据库中用于提高查询效率的数据结构。它就像书籍的目录，通过索引，数据库可以快速定位到所需数据，而不必逐行扫描整个表。MySQL 支持多种类型的索引，常见的有：

• 主键索引：一种特殊的唯一索引，不允许有空值。每个表只能有一个主键索引，用于唯一标识表中的每一行记录。
• 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
• 普通索引：最基本的索引类型，没有任何限制，用于提高查询效率。
• 全文索引：主要用于全文搜索，适用于 CHAR、VARCHAR 或 TEXT 类型的列。

二、创建索引

1. 创建表时创建索引

-- 创建一个名为 users 的表，并在 id 列上创建主键索引，在 email 列上创建唯一索引，在 age 列上创建普通索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    age INT,
    INDEX idx_age (age)
);

2. 在已存在的表上创建索引

-- 在 users 表的 name 列上创建普通索引
CREATE INDEX idx_name ON users (name);

-- 在 users 表的 age 和 gender 列上创建组合索引
CREATE INDEX idx_age_gender ON users (age, gender);

三、使用索引进行查询优化

假设我们有一个 orders 表，包含 order_id、customer_id、order_date 和 amount 列，并且在 customer_id 和 order_date 列上创建了组合索引。

-- 创建 orders 表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    INDEX idx_customer_date (customer_id, order_date)
);

-- 插入一些示例数据
INSERT INTO orders (order_id, customer_id, order_date, amount)
VALUES (1, 101, '2024-01-01', 100.00),
       (2, 102, '2024-01-02', 200.00),
       (3, 101, '2024-01-03', 150.00);

-- 使用索引进行查询
SELECT * FROM orders WHERE customer_id = 101 AND order_date = '2024-01-03';

在这个查询中，由于 customer_id 和 order_date 列上有组合索引，数据库可以快速定位到符合条件的记录，避免了全表扫描。

四、索引失效实例及原因分析

1. 索引列使用函数或计算

-- 假设 `age` 列有索引
SELECT * FROM users WHERE YEAR(birth_date) = 1990;

原因：索引是基于列的原始值建立的，当对索引列使用函数或进行计算时，数据库无法直接使用索引来定位数据，只能进行全表扫描。
解决方法：尽量避免在索引列上使用函数或计算，如果需要根据日期进行筛选，可以直接使用日期范围进行查询。

2. 隐式类型转换

-- `id` 是整数类型，这里传入字符串类型的值
SELECT * FROM users WHERE id = '123';

原因：MySQL 会对查询条件进行隐式类型转换，将字符串转换为整数。这种类型转换会导致索引无法正常使用，因为索引是基于整数类型建立的。
解决方法：确保查询条件的数据类型与索引列的数据类型一致。

3. 范围查询右侧的列索引失效

-- 假设存在组合索引 (age, name)
SELECT * FROM users WHERE age > 20 AND name = 'John';

原因：在组合索引中，当使用范围查询（如 >、<、BETWEEN 等）时，范围查询右侧的列将无法使用索引。因为范围查询会导致索引的有序性被破坏，数据库无法继续使用右侧的列进行快速定位。
解决方法：尽量将等值查询条件放在前面，范围查询条件放在后面。

4. LIKE 以通配符 % 开头

-- 假设 `name` 列有索引
SELECT * FROM users WHERE name LIKE '%John';

原因：当 LIKE 查询以通配符 % 开头时，数据库无法使用索引进行快速定位，因为它不知道从哪里开始匹配。
解决方法：如果可能，尽量将 % 放在后面，如 LIKE 'John%'，这样可以使用索引进行前缀匹配。

5. OR 前后存在非索引列

-- 假设 `age` 列有索引，`address` 列没有索引
SELECT * FROM users WHERE age = 25 OR address = 'New York';

原因：当使用 OR 连接查询条件时，如果其中一个列没有索引，数据库可能会选择全表扫描，而不是使用索引。
解决方法：确保 OR 前后的列都有索引，或者将 OR 查询拆分成多个 UNION 查询。

五、总结

索引是提高数据库查询效率的重要手段，但需要正确使用才能发挥其作用。在创建和使用索引时，要注意避免上述索引失效的情况，同时根据实际业务需求合理设计索引，以达到最佳的性能优化效果。