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扫描下方二维码关注公众号和小程序↓↓↓
Loading...