MySQL 索引失效的几种场景

MySQL 索引 面试 About 2,153 words

模糊查询

模糊查询,头部模糊匹配。(尾部模糊匹配不会失效)

注意:如果覆盖索引能够使用,头部模糊查询也会走索引。

create table t_user(id int primary key, name varchar(25), age int, no int);
create index idx_age_name on t_user(age, name);
insert into t_user values(1, 'aaa', 10, 1), (2,'bbb', 20, 2), (3, 'ccc', 30, 3);

获取全部字段

explain select * from t_user where name like '%abc';

输出:

mysql> explain select * from t_user where name like '%abc';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

id是主键,(age, name)是联合索引。

利用覆盖索引,仅获取有索引的字段。

explain select id, age, name from t_user where name like '%abc';

输出:

mysql> explain select id, age, name from t_user where name like '%abc';
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t_user | NULL       | index | NULL          | idx_age_name | 108     | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

使用函数

在索引列使用函数,如substring()等。

联合索引

  • 联合索引使用小于、大于(应使用小于等于、大于等于)
  • 联合索引部分失效,where条件跳过了复合索引的部分

or

or连接的条件,如果or前面的字段有索引,or后面的字段没有索引,也会使索引失效。

其他

  • 字符串类型字段使用时不加引号
  • MySQL评估使用索引比全表更慢,则不适用索引
Views: 1,517 · Posted: 2022-05-12

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb/LiteNote

扫描下方二维码关注公众号和小程序↓↓↓

扫描下方二维码关注公众号和小程序↓↓↓


Today On History
Browsing Refresh