mysql索引失效的情况

-- 示例代码：索引失效的情况

-- 情况1：使用函数或表达式操作索引列
CREATE TABLE t_user (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    INDEX idx_age (age)
);

-- 查询时对索引列使用函数，导致索引失效
SELECT * FROM t_user WHERE LENGTH(name) = 5;  -- 索引失效，因为对name列使用了函数

-- 情况2：隐式类型转换
CREATE TABLE t_order (
    id INT PRIMARY KEY,
    order_id VARCHAR(20),
    amount DECIMAL(10, 2),
    INDEX idx_order_id (order_id)
);

-- 查询时隐式类型转换导致索引失效
SELECT * FROM t_order WHERE order_id = 12345;  -- 索引失效，因为order_id是VARCHAR类型，而查询条件是INT类型

-- 情况3：使用不等于 (!= 或 <>) 操作符
CREATE TABLE t_product (
    id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2),
    INDEX idx_price (price)
);

-- 查询时使用不等于操作符，可能导致索引失效
SELECT * FROM t_product WHERE price != 10.99;  -- 索引可能失效，因为MySQL优化器认为全表扫描更高效

-- 情况4：使用 OR 操作符连接多个条件
CREATE TABLE t_customer (
    id INT PRIMARY KEY,
    email VARCHAR(100),
    phone VARCHAR(20),
    INDEX idx_email (email),
    INDEX idx_phone (phone)
);

-- 查询时使用 OR 操作符连接多个条件，可能导致索引失效
SELECT * FROM t_customer WHERE email = 'example@example.com' OR phone = '1234567890';  -- 索引可能失效，因为OR操作符会使MySQL选择全表扫描

-- 情况5：使用 LIKE 模糊查询以通配符开头
CREATE TABLE t_article (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    INDEX idx_title (title)
);

-- 查询时使用 LIKE 模糊查询以通配符开头，导致索引失效
SELECT * FROM t_article WHERE title LIKE '%mysql%';  -- 索引失效，因为LIKE查询以通配符开头

解释说明：

1. 使用函数或表达式操作索引列：当在查询条件中对索引列使用函数（如 LENGTH()）时，MySQL无法直接利用索引，从而导致索引失效。
2. 隐式类型转换：当查询条件中的数据类型与索引列的数据类型不匹配时，MySQL会进行隐式类型转换，这会导致索引失效。
3. 使用不等于操作符：!= 或 <> 操作符可能会使MySQL优化器认为全表扫描比使用索引更高效，因此索引可能失效。
4. 使用 OR 操作符连接多个条件：当使用 OR 连接多个条件时，MySQL可能会选择全表扫描而不是使用索引，因为 OR 操作符会使索引的选择变得复杂。
5. 使用 LIKE 模糊查询以通配符开头：如果 LIKE 查询以通配符（如 %）开头，则MySQL无法利用索引进行快速查找，从而导致索引失效。