MySQL常见的索引失效场景

天下之猴

发布于 2024-09-11 19:54:17

950

发布于 2024-09-11 19:54:17

前提准备

在数据库中运行下面语句


CREATE TABLE `t_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `id_no` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '身份编号',
  `username` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `union_idx` (`id_no`,`username`),
  KEY `create_time_idx` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1001', 'Tom1', 11, '2022-02-27 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1002', 'Tom2', 12, '2022-02-26 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1003', 'Tom3', 13, '2022-02-25 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1004', 'Tom4', 14, '2023-02-25 09:04:23');

创建了组合索引和主索引以及二级索引

索引创建和删除语句如下,方便大家自己进行其他测试,建议自己将所有语句运行一边,使用explain + 查询语句看看运行计划,加深一边印象

CREATE INDEX 索引名 ON 表名 (`索引列1`,`索引列2`);

drop Index '索引名' ON 表名;

索引的作用

索引类似于一本书的目录,当你想在一本数学书中找到三角函数那章如果没有目录只能随机翻或者一页一页翻,有索引只需要找到目录变能快速定位

主索引和二级索引的存储形式区别

主键索引的叶子节点存储的是整行数据，而非指向数据的指针。
主键索引的叶子节点按照主键的顺序存储数据行，因此主键索引实际上就是数据在磁盘上的物理存储顺序。
二级索引的叶子节点存储的是索引键值及指向相应数据行的物理指针。
二级索引的叶子节点按照索引键值的顺序存储指针，而非实际数据行。

主索引不失效,二级索引失效的特殊情况

order by 导致索引失效

select * from t_user order by id_no; //不走索引
select * from t_user order by id desc; //走索引

not in

select * from t_user where id not in (2,3); //走索引
select * from t_user where id_no not in ('2','3'); //不走索引

<>(不等于比较)

explain select * from t_user where id_no <> '1002'; //不走索引
explain select * from t_user where id != 2; //走索引

二级索引和主索引失效的情况

not exits 与is not null

select * from t_user u1 where not exists (select 1 from t_user u2 where u2.id  = 2 and u2.id = u1.id);
explain SELECT * from t_user WHERE id is not null; // 不走索引
explain SELECT * from t_user WHERE id is null; // 走索引

索引结果参与运算

select * from t_user where id = 1 ; //走索引
select * from t_user where id = 2 - 1 ; //不走索引

索引使用了函数

select * from t_user where SUBSTR(id_no,1,3) = '100';

糊匹配的占位符位于条件的首部

like '%abc%';
like '%abc';

查询条件与索引类型不一样

select * from t_user where id_no = 1002;

OR导致的索引失效

select * from t_user where id = 2 or age = 17; //不走索引,一边为索引一边不为索引,只有两边都为索引才会生效
select * from t_user where id  > 1 or id  < 80; //不走索引,两边都进行比较不会走索引
select * from t_user where id BETWEEN 1 and 80; //走索引

联合索引失效的情况

联合索引在mysql中的常见语句如下

KEY `union_idx` (`列1`,`列2`,`列3`)

在上述中我们创建的三个列组成的联合索引

查询条件不满足最左匹配原则

当我们使用where查询条件中没有列1时将会造成索引失效,走全表扫描,例如下面查询语句

select * from t_user where username = 'Tom2' and age = 12;

因此想要索引生效有下面两种写法

select * from table where id_no = '1002' and username = 'Tom2';
select id_no from t_user where username = 'Tom2'; //覆盖索引

拓展:覆盖索引

覆盖索引即二级索引包含了查询需要的所有列,并且你的操作字段中也只有索引字段,那么就会走索引了,前面提到的失效情况就无效了,但是这种情况一般较少,索引建多了会占用空间,写操作变慢(插入数据的时候也要更新B+树中索引的位置)不说,可能恰尔其反导致优化不知道选择哪个索引,(选择困难症犯了有没有)倒是查询性能也下降

本文系转载，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql索引