MySQL问题：索引失效

<think>我们讨论MySQL索引失效的原因。索引失效会导致查询性能下降，甚至可能退化为全表扫描。常见的索引失效原因包括：1.使用`!=`或者`<>`操作符：如果使用不等于操作符，MySQL可能无法使用索引。2.使用`OR`连接条件：如果OR条件中有一列没有索引，那么整个查询都不会使用索引。但是，如果OR条件中的每个列都有索引，那么可能会使用索引合并（indexmerge）。3.对索引列进行函数操作或表达式计算：例如，在索引列上使用函数（如`DATE()`，`SUBSTR()`）或进行数学运算（如`col+1=10`）。4.使用`LIKE`以通配符开头：如`LIKE'%abc'`，这种前缀模糊匹配会导致索引失效；但后缀匹配`LIKE'abc%'`可以使用索引。5.类型转换：如果索引列是字符串类型，而查询条件中使用了数字，那么MySQL可能会进行隐式类型转换，导致索引失效。6.复合索引未使用最左前缀：复合索引按照最左前缀原则匹配，如果查询条件中没有使用复合索引的第一列，则索引可能不会被使用。7.使用`IN`或`NOTIN`：在MySQL中，使用`IN`通常还是会使用索引的，但是当`IN`的值过多时，优化器可能会选择全表扫描。`NOTIN`则通常不会使用索引。8.索引列上使用`ISNULL`或`ISNOTNULL`：这可能会导致索引失效，取决于数据分布和优化器的选择。不过，如果列定义为`NOTNULL`，则`ISNULL`条件永远不会成立。9.全表扫描比使用索引更快：当表中数据量很小，或者需要返回大部分数据行时，优化器可能选择全表扫描。下面我们通过一些示例来说明：示例1：使用函数导致索引失效假设有表`t1`，在列`create_time`上建立了索引，以下查询会导致索引失效：SELECT*FROMt1WHEREDATE(create_time)='2020-01-01';应该改为：SELECT*FROMt1WHEREcreate_time>='2020-01-01'ANDcreate_time<'2020-01-02';示例2：复合索引未使用最左前缀假设有复合索引`idx_name_age`(name,age)，以下查询可以使用索引：SELECT*FROMt1WHEREname='Tom';SELECT*FROMt1WHEREname='Tom'ANDage=20;但以下查询无法使用复合索引（除非单独有age的索引）：SELECT*FROMt1WHEREage=20;示例3：隐式类型转换假设列`phone`是`varchar`类型，并且有索引，以下查询会导致索引失效：SELECT*FROMt1WHEREphone=13800138000;因为这里将字符串列与数字比较，MySQL会将字符串转换为数字，相当于在列上使用了函数。应该改为：SELECT*FROMt1WHEREphone='13800138000';示例4：使用OR导致索引失效假设有索引在列`a`上，没有索引在列`b`上：SELECT*FROMt1WHEREa=1ORb=2;这个查询将不会使用列`a`上的索引，因为OR条件中有一个列没有索引。为了避免索引失效，我们应当：-避免在索引列上使用函数或表达式。-尽量避免使用`OR`，可以改用`UNION`或`UNIONALL`。-注意复合索引的最左前缀原则。-注意`LIKE`查询的通配符位置。-避免隐式类型转换。-使用`EXPLAIN`分析查询语句，查看索引使用情况。下面我们给出一个示例，使用`EXPLAIN`来查看查询是否使用了索引。假设有表`employees`：```sqlCREATETABLEemployees(idINTPRIMARYKEY,nameVARCHAR(100),ageINT,departmentVARCHAR(100),hire_dateDATE,INDEXidx_name_age(name,age),INDEXidx_hire_date(hire_date));```查询1：使用最左前缀```sqlEXPLAINSELECT*FROMemployeesWHEREname='John';```这里应该会使用到索引`idx_name_age`。查询2：不使用最左前缀```sqlEXPLAINSELECT*FROMemployeesWHEREage=30;```这里不会使用复合索引`idx_name_age`，因为age不是最左列。查询3：在hire_date上使用函数```sqlEXPLAINSELECT*FROMemployeesWHEREYEAR(hire_date)=2020;```这里不会使用索引`idx_hire_date`，因为使用了函数。我们可以通过改写查询来避免使用函数：```sqlEXPLAINSELECT*FROMemployeesWHEREhire_dateBETWEEN'2020-01-01'AND'2020-12-31';```这样就能使用索引了。注意：优化器的行为可能会因为MySQL版本、数据量、数据分布等因素而有所不同，因此在实际环境中，使用`EXPLAIN`来验证索引使用情况是非常重要的。最后，我们提供一些优化索引使用的建议：1.经常出现在WHERE、ORDERBY、GROUPBY、JOINON子句中的列，可以考虑建立索引。2.避免创建过多的索引，因为索引会占用空间，并且影响写操作的性能。3.使用覆盖索引（即索引包含所有查询的列）来避免回表。4.定期分析表（使用`ANALYZETABLE`）更新索引统计信息，帮助优化器做出更好的决策。现在，根据要求，在最后提出相关问题。</think>### MySQL索引失效原因及优化方案 索引失效会导致查询性能大幅下降，常见原因及解决方案如下： #### 1. **违反最左前缀原则（复合索引）** **原因**：复合索引未从最左侧字段开始使用 **示例**：索引 `(a,b,c)`，查询条件为 `WHERE b=1 AND c=2` **优化方案**： ```sql -- 创建复合索引 CREATE INDEX idx_composite ON table_name(col1, col2, col3); -- 有效查询（使用索引） SELECT * FROM table_name WHERE col1 = 'A' AND col2 = 'B'; -- 失效查询（跳过col1） SELECT * FROM table_name WHERE col2 = 'B'; -- ❌ 索引失效 ``` #### 2. **在索引列上使用函数或计算** **原因**：对索引列进行函数处理或计算 **示例**：`WHERE YEAR(create_time) = 2023` **优化方案**： ```sql -- 原始失效查询 SELECT * FROM orders WHERE YEAR(order_date) = 2023; -- ❌ -- 优化为范围查询 SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'; -- ✅ ``` #### 3. **隐式类型转换** **原因**：数据类型不匹配导致转换 **示例**：字符串列用数字查询 `WHERE phone = 13800138000` **优化方案**： ```sql -- phone是VARCHAR类型 SELECT * FROM users WHERE phone = '13800138000'; -- ✅ -- 错误示例（触发类型转换） SELECT * FROM users WHERE phone = 13800138000; -- ❌ ``` #### 4. **LIKE模糊查询以通配符开头** **原因**：`%value%` 无法使用索引 **优化方案**： ```sql -- 索引失效 SELECT * FROM products WHERE name LIKE '%apple%'; -- ❌ -- 可优化为（需全文索引支持） ALTER TABLE products ADD FULLTEXT(name); SELECT * FROM products WHERE MATCH(name) AGAINST('apple'); ``` #### 5. **使用OR连接非索引列** **原因**：OR条件包含无索引列 **优化方案**： ```sql -- 原始查询（status无索引） SELECT * FROM orders WHERE order_id = 100 OR status = 'shipped'; -- ❌ -- 优化方案1：拆分查询 SELECT * FROM orders WHERE order_id = 100 UNION ALL SELECT * FROM orders WHERE status = 'shipped'; -- 优化方案2：添加索引 ALTER TABLE orders ADD INDEX idx_status(status); ``` #### 6. **索引列使用不等于（!= 或 <>）** **原因**：不等于操作无法有效利用索引 **优化方案**： ```sql -- 低效查询 SELECT * FROM users WHERE age <> 30; -- ❌ -- 优化为范围查询+覆盖索引 SELECT id FROM users WHERE age < 30 OR age > 30; -- ✅ ``` #### 7. **索引列使用IS NULL/IS NOT NULL** **原因**：空值判断可能绕过索引 **优化方案**： ```sql -- 添加条件索引 CREATE INDEX idx_email ON users(email) WHERE email IS NOT NULL; -- 查询优化 SELECT * FROM users WHERE email IS NOT NULL; ``` #### 8. **数据分布不均导致优化器弃用索引** **原因**：当值重复率过高时（>30%），优化器选择全表扫描 **解决方案**： ```sql -- 强制使用索引 SELECT * FROM users FORCE INDEX(idx_status) WHERE status = 'active'; -- 优化统计信息 ANALYZE TABLE users; ``` #### 9. **JOIN字段类型/字符集不匹配** **原因**：连接字段类型不一致 **优化方案**： ```sql -- 统一字符集和排序规则 ALTER TABLE orders CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; ALTER TABLE customers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; ``` ### 诊断工具 使用 `EXPLAIN` 分析查询： ```sql EXPLAIN SELECT * FROM users WHERE email LIKE 'user%@example.com'; ``` 关键指标： - `type`：`index`或`range`表示使用索引 - `key`：显示实际使用的索引 - `rows`：扫描行数（越少越好） - `Extra`：避免出现 `Using filesort` 或 `Using temporary` ### 最佳实践 1. 使用覆盖索引减少回表 2. 避免 `SELECT *`，只查询必要字段 3. 定期执行 `OPTIMIZE TABLE` 整理碎片 4. 监控慢查询日志：`SET GLOBAL slow_query_log = ON;`