MySQL 索引失效的几个场景

学习心得 做棵大树 4周前 (03-21) 63次浏览 0个评论
文章目录[隐藏]

MySQL 索引失效通常发生在一些特定的查询场景中,这些场景包括但不限于对索引列使用函数或运算表达式、LIKE 通配符左侧开放、OR 条件筛选、类型不匹配或隐式转换等

场景

以下是一些导致 MySQL 索引失效的常见场景:

  1. 函数操作:如果查询条件中对索引列使用了函数(如 ABS、YEAR 等),那么索引将无法被利用。
  2. 运算操作:在查询条件中对索引列进行算术运算(加、减、乘、除)也会导致索引失效。
  3. LIKE 通配符左侧开放:使用 LIKE 模式匹配时,如果以通配符开头(即'%xx'),则索引不会被使用。
  4. OR 条件筛选:当使用 OR 连接多个条件时,如果至少有一个条件未使用索引,可能会导致整个查询的索引失效。
  5. 类型不匹配:如果查询中的列与索引列数据类型不一致,或者进行了隐式类型转换,索引同样会失效。
  6. 联合索引非最左匹配:对于组合索引,如果查询没有包含组合索引的最左侧列,那么索引将不会被使用。
  7. 数据分布不均匀:当索引列的数据分布极度不均匀时,MySQL 可能会选择全表扫描而不是使用索引,比如索引列重复值太多。
  8. IS NULL 和 IS NOT NULL:在索引列上使用 IS NULL 可能导致索引失效,因为索引不存储 null 值。而 IS NOT NULL 则可能走索引,但这取决于具体情况。

例子

以下是一些具体的例子,展示了 MySQL 索引失效的常见场景:

  1. 函数操作导致索引失效:

    SELECT * FROM users WHERE YEAR(birthdate) = 1990;

    在这个例子中,使用了 YEAR 函数对 birthdate 列进行操作,导致无法使用索引。

  2. 运算操作导致索引失效:

    SELECT * FROM products WHERE price * 0.9 < 50;

    在这个例子中,对 price 列进行了乘法运算,导致无法使用索引。

  3. LIKE 通配符左侧开放导致索引失效:

    SELECT * FROM customers WHERE name LIKE '%Smith';

    在这个例子中,LIKE 模式以通配符开头,导致无法使用索引。

  4. OR 条件筛选导致索引失效:

    SELECT * FROM orders WHERE status = 'pending' OR customer_id > 1000;

    在这个例子中,OR 条件筛选了 status 和 customer_id 两个列,如果其中一个条件未使用索引,则整个查询的索引可能失效。

  5. 类型不匹配导致索引失效:

    SELECT * FROM employees WHERE salary = '5000';

    在这个例子中,salary 列是整数类型,但查询中的值是字符串类型,导致索引失效。

  6. 联合索引非最左匹配导致索引失效:

    SELECT * FROM orders WHERE order_date > '2022-01-01' AND total_amount > 1000;

    假设 orders 表有一个组合索引(order_date, total_amount),但查询没有包含 order_date 列,导致索引失效。

  7. 数据分布不均匀导致索引失效:

    SELECT * FROM employees WHERE department_id = 100;

    如果 employees 表中 department_id 列的数据分布极度不均匀,且大部分员工都属于一个部门,那么即使有索引,MySQL 也可能选择全表扫描而不是使用索引。

这些例子展示了一些常见的 MySQL 索引失效的场景,通过避免这些情况,可以提高查询性能并充分利用索引的优势。

避免索引失效的方法包括合理设计查询语句、确保数据类型一致性、正确使用索引列以及考虑组合索引的顺序等。在设计数据库和编写查询语句时,应充分理解索引的使用原则和限制,以充分利用索引提高查询效率。


做棵大树 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明MySQL 索引失效的几个场景
喜欢 (0)
[欢迎投币]
分享 (0)
关于作者:
一个整天无所事事的,有时候忽然热血的孩子
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址