索引失效的几种情况

最新推荐文章于 2024-03-05 13:06:44 发布

可乐٩(♡㉨♡ )۶

最新推荐文章于 2024-03-05 13:06:44 发布

阅读量1.4k

点赞数

分类专栏： mysql 文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/weixin_44799198/article/details/119991556

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一. 索引失效的几种情况

索引并不是时时都会生效的，比如以下几种情况，将导致索引失效

如果条件中有or，即使其中有部分条件带索引也不会使用(这也是为什么尽量少用or的原因)(user_id 没有加索引)
注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引


     # 索引生效
     select * from user where id = 140  
     
     # 索引不生效
     select * from user where id = 140 or user_id  = 124

对于复合索引，如果不使用前列，后续列也将无法使用，类电话簿。
like查询是以%开头

     # 索引生效
     select * from user where name like 'M%'  
     
     # 索引不生效
     select * from user where name like '%M'

存在索引列的数据类型隐形转换，则用不上索引，比如列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
注意：name是个字符串。

    # 索引生效
     select * from user where name = ‘123’
     
    # 索引不生效
     select * from user where name = 123

where 子句里对索引列上有数学运算，用不上索引

    # 索引生效
     select * from user where id = 1
     
    # 索引不生效
     select * from user where id  =  id +1

where 子句里对有索引列使用函数，用不上索引

    # 索引生效
     select * from user where id = 1
     
    # 索引不生效
     select * from user where  ABD(id) = 1

如果mysql估计使用全表扫描要比使用索引快,则不使用索引
存在NULL值条件

    # 索引不生效
     select * from user where  name = null

二. 什么情况下不推荐使用索引？

数据唯一性差（一个字段的取值只有几种时）的字段不要使用索引

比如性别，只有两种可能数据。意味着索引的二叉树级别少，多是平级。这样的二叉树查找无异于全表扫描。

频繁更新的字段不要使用索引

比如logincount登录次数，频繁变化导致索引也频繁变化，增大数据库工作量，降低效率。

字段不在where语句出现时不要添加索引,如果where后含IS NULL /IS NOT NULL/ like ‘%输入符%’等条件，不建议使用索引

只有在where语句出现，mysql才会去使用索引

4） where 子句里对索引列使用不等于（<>），使用索引效果一般

三. 什么情况下应该建立索引

1. 表的主关键字

自动建立唯一索引

如zl_yhjbqk（用户基本情况）中的hbs_bh（户标识编号）

2. 表的字段唯一约束

ORACLE利用索引来保证数据的完整性

如lc_hj（流程环节）中的lc_bh+hj_sx（流程编号+环节顺序）

3. 直接条件查询的字段

在SQL中用于条件约束的字段

如zl_yhjbqk（用户基本情况）中的qc_bh（区册编号）

select * from zl_yhjbqk where qc_bh=’7001’

4. 查询中与其它表关联的字段

字段常常建立了外键关系

如zl_ydcf（用电成份）中的jldb_bh（计量点表编号）

select * from zl_ydcf a,zl_yhdb b where 
                                   a.jldb_bh=b.jldb_bh and b.jldb_bh=’540100214511’

5. 查询中排序的字段

排序的字段如果通过索引去访问那将大大提高排序速度

select * from zl_yhjbqk order by qc_bh（建立qc_bh索引）

select * from zl_yhjbqk where 
                       qc_bh='7001' order by cb_sx（建立qc_bh+cb_sx索引，
                                              注：只是一个索引，其中包括qc_bh和cb_sx字段）

6. 查询中统计或分组统计的字段

select max(hbs_bh) from zl_yhjbqk

select qc_bh,count(*) from zl_yhjbqk group by qc_bh

四.哪些字段要设置索引

1.表的主键、外键必须有索引；

2.数据量超过300的表应该有索引；

3.在where从句，group by从句，order by从句，on从句中的列添加索引

4.索引应该建在选择性高的字段上；

5.索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

6.复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

五.索引是怎么查询的

1. 簇列索引

写个例子来说明：
执行建表语句：

CREATE TABLE `student` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '主键id',
  `student_no` VARCHAR(64) COMMENT '学号',
  `name` VARCHAR(64) COMMENT '学生姓名',
  `age` INT COMMENT '学生年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='学生信息表';

插入 5 条数据：

insert into student(student_no,name,age) values(101,"Alice",18);
insert into student(student_no,name,age) values(102,"Bob",19);
insert into student(student_no,name,age) values(104,"Brandt",15);
insert into student(student_no,name,age) values(105,"David",19);
insert into student(student_no,name,age) values(109,"David",18);

在插入的过程中，MySQL 会用你指定的主键，在这里是递增主键，维护起一棵 B+树，主键从 1 开始递增，插入五条，所以是 1 到 5：
在这里插入图片描述

从 1 到 5，一个一个插入，你会看到 B+树在插入的过程中是怎么维护它的几个特性的：

有序：左边节点比右边小
自平衡：左右两边数量趋于相等
节点分裂：节点在遇到元素数量超过节点容量时，是如何分裂成两个的，这个也是 MySQL页分裂的原理

执行SQL语句：

select * from student where id = 5;

# 1. 从上到下，先找到 3，5 比它大，找右节点
# 2. 接着找到 4，发现 5 还是比它大，继续找右节点
# 3. 这次到达叶子节点了，叶子节点是一个递增的数组，那就用二分法，找到 id=5 的数据

以上使用的是簇列索引，簇列索引只会加快主键索引，但是如果想根据姓名查询呢？就得使用二级索引。

2. 二级索引

看看上面这棵树就知道，数据并没有按照姓名进行组织，所以，还是只能全表扫描。

不想全表扫描，怎么办？那就给姓名字段也加个索引，让数据按照姓名有规律的进行组织：

create index idx_name on student(name);

这时候 MySQL 又会建一棵新的 B+树：

在这里插入图片描述

发现这棵树的叶子节点，只有姓名和主键ID两个字段，没有行的完整数据，这时候你执行：

select * from student where name = "David";

MySQL 到刚刚创建的这棵 B+树查询，快速查到有两条姓名是“David”的记录，并且拿到它们的主键，分别是 4 和 5，但是要的是select *呀，怎么办？

别忘了，MySQL 在一开始就给建了一棵 B+树了，把这两棵树，放在一起，拿着从这棵树上查到的两个主键ID，去聚簇索引找，事情不就解决了？
在这里插入图片描述

这个不带行数据完整信息的索引，就叫二级索引（secondary index），也叫辅助索引。

3. 复合索引

如果还想根据姓名和年龄同时查询呢？

select * from student where name = "David" and age = 18;

还是那个道理，数据虽然按照 name 有规律的组织了，但是没有按照 age 有规律组织，所以我们要给 name 和 age 同时建索引：

create index idx_name_age on student(name,age);

这时候 MySQL 又会建一棵 B+树，这下 B+树的节点里面，不只有 name，还有 age 了：

在这里插入图片描述
注意观察用红色虚线框出来的那两个节点，这是这棵树和上面那棵只给 name 建索引的树的唯一区别，两个元素换了个位，因为排序时，是先用 name 比较大小，如果 name 相同，则用 age 比较

可乐٩(♡㉨♡ )۶

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
索引失效的几种情况

一. 索引失效的几种情况索引并不是时时都会生效的，比如以下几种情况，将导致索引失效如果条件中有or，即使其中有部分条件带索引也不会使用(这也是为什么尽量少用or的原因)(user_id 没有加索引)注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引 # 索引生效 select * from user where id = 140 # 索引不生效 select * from user where id = 140 or u
复制链接

扫一扫