Mysql-索引失效
0.背景
借鉴抖音“老猿说开发”总结的一句口诀。
索引可能失效场景如下:模型数空运最快
序号 | 关键字 | 含义 | 解释 |
---|---|---|---|
1 | 模 | 模糊查询 | 当使用模糊查询(比如使用LIKE 操作符)时,如果查询条件的开头使用了通配符(如% ),那么索引可能会失效。因为通配符在查询开始位置时,数据库无法利用索引进行高效的检索,需要进行全表扫描。 |
2 | 型 | 数据类型 | 当索引列与查询条件的数据类型不匹配时,索引可能会失效。比如,如果索引列是整数类型,但是查询条件使用了字符串类型,那么索引可能无法被使用。 |
3 | 数 | 内部函数 | 当在查询条件中使用了内部函数时,索引可能会失效。因为内部函数会对查询条件进行处理,导致无法直接利用索引进行匹配。 |
4 | 空 | NULL、空值 | 当查询条件中涉及到NULL 或空值时,索引可能会失效。因为索引一般不会包含NULL 值,所以在涉及到NULL 的查询条件下,索引可能无法被有效利用。 |
5 | 运 | 运算 | 当查询条件中包含了运算操作时,索引可能会失效。比如,如果在查询条件中使用了算术运算符或逻辑运算符,索引可能无法被利用。 |
6 | 最 | 最左前缀原则 | MySQL遵循最左前缀原则,即只有在索引的最左前缀被使用时,索引才能被利用。如果查询条件不满足最左前缀原则,那么索引可能会失效。 |
7 | 快 | 全表扫描更快时 | 在某些情况下,全表扫描可能比使用索引更快,比如当数据量很小或者索引列的基数(即不同值的个数)非常低时,使用索引可能会导致额外的I/O开销,反而不如直接进行全表扫描快速。 |
1.解析
1.1 使用模糊查询
当使用 LIKE
操作符进行模糊查询时,如果查询条件的开头是一个通配符 %
,如 LIKE '%abc'
,那么索引可能会失效。因为通配符 %
表示任意字符,包括零个字符,这导致数据库无法利用索引的前缀来快速定位匹配的行。
考虑一个实际的例子,假设我们有一个名为 names
的表,其中包含一个名为 name
的列,我们想要查询所有以 "abc" 结尾的名字。
如果我们执行以下查询:
SELECT * FROM names WHERE name LIKE '%abc';
在这个查询中,LIKE
操作符的模式是以 %
开头,这意味着我们要查找所有以 "abc" 结尾的名字。数据库无法利用索引来直接定位到以 "abc" 结尾的行,因为索引是按照顺序存储的,而查询条件的开头是一个通配符。这就导致了索引失效,数据库可能会选择进行全表扫描来满足查询条件,这可能会降低性能,尤其是在大型表中。
在设计数据库时,如果需要进行模糊查询,应尽量避免在查询条件的开头使用通配符,以便数据库可以有效利用索引来提高查询性能。
1.2 数据类型不匹配
当索引列与查询条件的数据类型不匹配时,索引可能会失效。这可能会导致数据库无法使用索引来加速查询,从而降低查询性能。
假设我们有一个包含用户信息的表 users
,其中包含一个名为 age
的列,存储用户的年龄。假设 age
列是整数类型,并且我们在 age
列上创建了一个索引。
现在,如果我们执行以下查询:
SELECT * FROM users WHERE age = '30';
在这个查询中,我们试图查询所有年龄为 30 的用户。然而,如果 age
列是整数类型,而我们在查询条件中使用了字符串 '30'
,那么这两者的数据类型不匹配。
即使我们在 age
列上有一个索引,数据库也无法有效利用索引来加速查询,因为它无法将字符串 '30'
转换为整数类型进行比较。这将导致索引失效,数据库可能会选择进行全表扫描来满足查询条件,这可能会降低查询性能,尤其是在大型表中。
在设计数据库时,需要确保索引列与查询条件的数据类型匹配,以便数据库可以有效地利用索引来加速查询。
1.3 使用了内部函数
当在查询条件中使用了内部函数时,索引可能会失效。
这是因为内部函数会对查询条件进行处理,导致无法直接使用索引来加速查询。
假设我们有一个包含用户信息的表 users
,其中包含一个名为 created_at
的列,存储用户创建账户的时间。
现在,如果我们执行以下查询:
SELECT * FROM users WHERE DATE(created_at) = '2022-01-01';
在这个查询中,我们试图查询所有在 2022 年 1 月 1 日创建账户的用户。虽然我们在 created_at
列上可能有一个索引,但是由于我们在查询条件中使用了 DATE()
函数对 created_at
列进行了处理,数据库无法直接利用索引来加速查询。
当我们使用内部函数时,数据库需要在查询执行之前对每一行数据进行函数计算,然后再进行条件判断。这会导致无法利用索引来加速查询,可能会选择进行全表扫描来满足查询条件,尤其是在大型表中,这可能会降低查询性能。
在设计数据库时,应尽量避免在查询条件中使用内部函数,以确保数据库可以有效地利用索引来加速查询。如果需要对列进行函数处理,可以考虑在应用程序层面进行处理,而不是在数据库查询中使用内部函数。
1.4 NULL或空值
当查询条件涉及到 NULL
或空值
时,索引可能会失效。这是因为索引一般不包含 NULL
值,当查询条件中涉及到 NULL
值时,数据库可能无法有效利用索引来加速查询。
假设我们有一个名为 users
的表,其中包含一个名为 email
的列,存储用户的电子邮件地址,我们在 email
列上创建了一个索引。
现在,如果我们执行以下查询:
SELECT * FROM users WHERE email IS NULL;
在这个查询中,我们试图查询所有电子邮件地址为空的用户。虽然我们在 email
列上可能有一个索引,但是由于我们在查询条件中使用了 IS NULL
条件,数据库可能无法有效利用索引来加速查询。
因为索引一般不包含 NULL
值,当查询条件涉及到 NULL
值时,数据库可能会选择进行全表扫描来满足查询条件,这可能会导致性能下降,尤其是在大型表中。
同理,假设我们有一个名为 products
的表,其中包含一个名为 category
的列,存储产品的类别信息,我们在 category
列上创建了一个索引。
现在,如果我们执行以下查询:
SELECT * FROM products WHERE category = '';
在这个查询中,我们试图查询所有类别为空的产品。虽然我们在 category
列上可能有一个索引,但是由于我们在查询条件中使用了空值,数据库可能无法有效利用索引来加速查询。
在设计数据库时,需要考虑到查询条件涉及到 NULL
或空值的情况,并确保数据库可以有效利用索引来加速查询。
1.5 进行运算
当查询条件中包含运算符时,索引可能会失效。这是因为索引存储的是原始数据的值,而不是计算结果。
假设我们有一个名为 orders
的表,其中包含一个名为 total_amount
的列,存储订单的总金额,我们在 total_amount
列上创建了一个索引。
现在,如果我们执行以下查询:
SELECT * FROM orders WHERE total_amount + 10 = 100;
在这个查询中,我们试图查询所有总金额加上10等于100的订单。
虽然我们在 total_amount
列上可能有一个索引,但是由于查询条件中包含了运算符 +
,数据库无法直接利用索引来加速查询。
因为索引存储的是原始数据的值,而不是计算结果。当查询条件中包含运算符时,数据库可能会选择进行全表扫描来满足查询条件,这可能会导致性能下降,尤其是在大型表中。
在设计数据库时,需要考虑到查询条件中是否包含运算符,以及如何优化查询以利用索引来加速查询。
1.6 不符合最左前缀匹配原则
如果一个复合索引(即包含多个列的索引)被创建,那么在查询时,只有从索引的最左边开始的列被使用时,索引才能被有效地利用。
假设我们有一个包含用户信息的表 users
,其中包含了 first_name
和 last_name
两列,并且我们在这两列上创建了一个复合索引 (first_name, last_name)
。
现在,如果我们执行以下查询:
SELECT * FROM users WHERE last_name = 'Smith';
在这个查询中,我们只使用了复合索引的第二列 last_name
,而没有使用第一列 first_name
。根据最左前缀原则,如果查询中没有使用索引的第一列,那么索引就无法被有效利用,相当于索引失效。
因此,即使我们在 first_name
和 last_name
列上创建了复合索引,但是在这个查询中,数据库可能会选择进行全表扫描来满足查询条件,这可能会导致性能下降,尤其是在大型表中。
为了确保索引能够被有效利用,应该尽量保证查询条件中包含复合索引的前缀列,以满足最左前缀原则。
1.7 全表扫描更快时
在某些情况下,全表扫描可能比使用索引更快。这可能发生在索引的基数(不同值的数量)非常低,或者数据量非常小的情况下。
假设我们有一个名为 students
的表,其中包含了 grade
列,存储了学生的年级信息,我们在 grade
列上创建了一个索引。
现在,如果我们执行以下查询:
SELECT * FROM students WHERE grade = '12th';
假设在我们的数据集中,只有很少的学生在 12 年级,这意味着索引的基数非常低。在这种情况下,数据库可能会选择执行全表扫描,而不是使用索引。因为进行全表扫描可能比使用索引更快,尤其是在数据量较小的情况下。
此外,如果表中的数据量非常小,比如只有几十行,那么即使使用索引也不会带来明显的性能提升。在这种情况下,数据库可能会选择进行全表扫描,而不是使用索引。
在某些情况下,全表扫描可能比使用索引更快,尤其是当索引的基数非常低或者数据量非常小的情况下。
在设计数据库时,需要综合考虑查询条件、索引的基数以及数据量的大小,以选择最合适的查询执行计划。