sql优化----索引(你奶奶看了都能学会)

最新推荐文章于 2025-03-22 15:25:30 发布

鲨鱼辣椒3345678

最新推荐文章于 2025-03-22 15:25:30 发布

阅读量1.3k

点赞数 26

文章标签：数据库

本文链接：https://blog.youkuaiyun.com/m0_68882181/article/details/142216337

版权

一. 索引的介绍

1.1索引的作用

通过创建索引，数据库可以直接定位到包含所需数据的位置，避免了全表扫描，从而大大提高了查询效率。特别是在处理大数据量时，索引能够显著减少查询时间。

索引就好比一本书的目录部分，通过目录找到对应文章的页码，便可以快速定位到需要的文章。

B-Tree和B+Tree

目前大部分数据库系统及文件系统都采用B-Tree和B+Tree作为索引结构。

如上图是一个b+树

可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

总结一下，比如要找的数据项是36，首先去缓存块1发现36>35 走p3到达缓存块4 判断 35<65走p1

走缓存块9 查到36，所以真实的数据全部保存到叶子节点

1.2索引的类型

主键索引，唯一索引，普通索引，复合索引等等，今天主要就说这四个

1.3索引的优缺点

优点：

缺点：

二.索引的使用

开始前：

工具:navicat，sqlyoung都可以我这里用的是navicat

数据库：网上找一个百万条数据的数据库导入数据库中

1.1主键索引

当你在创建表的时候设置主键(primary key)的时候会自动创建一个主键索引

1.2唯一索引

当你在创建表的时候设置唯一约束(unique key)的时候会自动创建一个唯一索引

1.3普通索引

alter table book3 add index idx_price(price)

解释：给表book3添加索引名为idx_price 给price字段设置普通索引

查看索引：

show index from book3

可以看到已经有了索引

删除索引

alter table book3 drop index idx_price

解释：删除book3表下的索引名字为idx_price

注意：主键索引和唯一索引的索引名字就是本身的字段名

下面我们开始测试索引的性能

这是一条百万数据记录的一个表student_info 点击执行

select * from student_info where course_id=10092

可以发现运行时间是03.60秒

下来我们给course_id增加一个普通索引

#添加索引
alter table student_info add index idx_course(course_id)

再次执行上面查询代码，可以看到时间为0.093 查询速度变得更快

下面是一些经常使用索引的场景

#使用索引的原则
#1.项目需求中唯一性的字段
#2.where经常查询的字段
#3.区分度不大的字段不经常加索引 如性别字段
#4.多表时添加索引，一般夺标联查不建议超过三张
#5.经常使用order by的列需要添加索引
#6.精彩增删改的表不要添加索引

1.4联合索引

上述都是单条件查询，实际情况中多条件查询更常见，这时候就有个联合索引，

但同时，联合索引的操作不当会引起索引失效，这个我们后面再说

alter table student_info add index mul_idx(student_id,name,course_id)

三.索引失效

1.#居左原则，如果最左边的索引没走则索引失效

在创建联合索引的时候，如果没走最左边的索引则索引失效

alter table student_info add index mul_idx(student_id,name,course_id)

这个xplain意思就是判断当前的sql语句是否走了索引

#索引失效 一位是从name条件查询 没有最左边的字段
EXPLAIN select * from student_info where name='HmoJCL'

主要是看这两个字段

前面有后面空也是没有走索引

如果走索引了两个都会有值

同理

#没走索引
EXPLAIN select * from student_info where course_id='10028'
#走索引
EXPLAIN select * from student_info where student_id>183878

那这条sql语句有没有走索引呢？

EXPLAIN select * from student_info where name='HmoJCL'  and student_id=69360

答案是：走索引

原因是：mysql底层有优化器会自动交换位置

仔细观察下面代码，如果跨字段走不走索引呢？

EXPLAIN select * from student_info where student_id=69360 and course_id=69360

答案是：走一半

前面student_id 走索引后面course_id 不走索引

2.模糊查询不走索引

模糊查询也是不走索引的

alter table student_info add index idx_name(name)

3.#where 后使用内置函数时索引失效

explain select * from student_info where SUBSTR(name,3)='HmoJCL'

但是这里注意一点

一下情况走索引

#常量的运算不会引起索引失效  mysql底层有优化器可以帮忙
explain select * from student_info where id=1

4.当where后有条件时，范围条件右侧的索引失效

#解决方案：当创建索引时，把范围相关的索引尽量放到后面
#alter table student_info add index mul_idx(name,student_id)
EXPLAIN select * from student_info where student_id>16 and name='HmoJCL'
explain select * from student_info where name='HmoJCL' and student_id > '3'

5.否定也会让索引失效

#否定也会让普通索引失效(!=,<>,is not null)
explain select * from student_info where student_id !=3;
explain select * from student_info where name is not null;
explain select * from student_info where student_id <>3

6.OR关键字中，如果有一个不走索引，则都不会走索引

#使用or关键字时，建议两边都使用索引列
explain select * from student_info where name='aaa' or course_id=123

EXPLAIN select * from student_info where student_id>8546231 or name='HmoJCL'