目录
1实战目标
对比有无索引情况下数据检索速度,学会如何能够使用索引,掌握如何查询是否使用索引了
2前提知识
本次实战专注点在于如何利用索引来提高查询的效率。在使用索引的过程中我们不难发现有一些情况select查询语句是无法使用索引的,下面就对这些情况做一个总结
2.1索引失效的情况
2.1.1数据准备
新建一张学生表,并添加id为索引,id+age为索引。这里索引类型的选择权交给Oracle,Oracle会根据数据类型和数量自我选择
create table `student` (
`id` int not null comment 'id',
`name` varchar(255) collate utf8mb4_bin default null comment '姓名',
`age` int default null comment '年龄',
`birthday` datetime default null comment '生日',
primary key (`id`),
create index `idx_name` on `student` (`name`),
create index `idx_name_age` on `student` (`name`,`age`)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_bin;
2.1.2插入数据
insert into `student` values (1, '张三', 18, '2021-12-23 17:12:44');
insert into `student` values (2, '李四', 20, '2021-12-22 17:12:48');
2.1.3失效情况
1、查询条件中有or,即使有部分条件带索引也会失效
例如:
select * from `student` where id =1 or birthday = "2021-12-23"
此时id有索引但是birthday没有索引,所以索引将会失效
总结:查询条件中带有or,除非所有的查询条件都建有索引,否则索引失效
2、 like查询是以%开头
例如:
select * from student where name like "%三"
总结:模糊查询不能以%开头,但是可以把%放在查询条件的后面
3、索引类型和列类型要匹配
假如列类型为字符串,在查询时查询语句如下:
select * from student where name = 2222
此时查询将不会用索引,使用索引正确为:
select * from student where name = "张三"
总结:索引建立时类型为列值的类型,后面想要使用该索引要求查询列的类型和索引类型相同
4.索引值和列的值需要相同
在创建索引时使用的是当时列的值,假如在查询时对列的值进行了变化(计算),此时索引就将失效。例如:
select * from student where id-1 = 1
此时id-1会修改原本列的值(不是真的修改,是查询下的暂时修改),这会导致索引查询不可用
5.违背最左匹配原则
例如:
select * from student where age =18
由于此时关于age的索引是联合索引(name,age),在查询建立在联合索引基础上且第一个联合索引字段未生效时,第二个字段也不会生效
总结:联合索引的使用必须要符合最左匹配原则
6.如果mysql估计全表扫描要比使用索引要快,会不适用索引
7.other
这里对6、7的情况不再细致分析,如果大家平常在写sql时有遇到再去查找资料即可。常见的索引失效情况就是前面5种
3练习题(利用索引提高查询速度)
题目一
创建表格:
create table test7_01 as
select S.sid, S.name, S.birthday
from pub.student S
执行查询操作,观察查询时间 :
select * from
(select sid,name,birthday,
(select count(*) from test7_01 where substr(name,1,1)=substr(t1.name,1,1)) samefirstname
from pub.student_testindex t1)
where samefirstname=7
此时查询时间为:13.78s
create index test7_01_index on test7_01 (substr(name,1,1))
再次执行查询操作,观察查询时间 :
select * from
(select sid,name,birthday,
(select count(*) from test7_01 where substr(name,1,1)=substr(t1.name,1,1)) samefirstname
from pub.student_testindex t1)
where samefirstname=7
此时查询时间为:0.297s
关键点:
1、 创建一般索引的方法:
CREATE INDEX index_name ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
例如:
CREATE INDEX idx_name ON students (name);
2、索引只能在table中建立,不能在view中建立
3、创建一个普通索引(索引有colum1、colum2),这将有助于提高通过colum1和colum2进行搜索的查询性能
4、索引类似于目录,利用colum1创建索引就是以colum1为关键词建立一个目录
题目二![](https://img-blog.csdnimg.cn/direct/a39b3f4fcd404624a3e6311a1d8c4fcf.png)
create table test7_02 as
select sid,name,birthday from pub.student;
update test7_02
set birthday=to_date('19881018','yyyymmdd')
where substr(sid,12,1)='0'
create index test7_02_index on test7_02 (birthday,name)
关键点:
1、 在一般的where =语句中,索引是能正常运行的
2、验证问题是无法使用索引的。因为索引查询必须要符合最左匹配原则
题目三![](https://img-blog.csdnimg.cn/direct/f97947de0b154e04b31eb9386d14c23d.png)
create view test7_03 as
select * from
(select sid,name,birthday,
(select count(*) from pub.student
where name like substr(t1.name,1,1)||'%'
) samefirstname
from pub.student_testindex t1)
where samefirstname=7
关键点:
1、substr等函数中的操作不会调用索引
2、like语句匹配的是字符串
3、||‘%’作用是字符串连接
题目四![](https://img-blog.csdnimg.cn/direct/5e162538a221426581fbcfa3aca00c73.png)
create view test7_04 as
select * from
(select sid,name,birthday,
(select count(*) from pub.student
where birthday >= trunc(t1.birthday,'mm') and birthday <=last_day(t1.birthday)
) sameyearmonth,
(select count(*) from pub.student
where birthday >= trunc(t1.birthday,'YYYY') and birthday <= last_day(add_months(trunc(t1.birthday,'yyyy'),11))
) sameyear
from pub.student_testindex t1
)
where sameyearmonth=35
关键点:
1、to_char函数可以直接用来提取birthday中的年月日,但是使用函数会导致无法使用索引。因为使用函数后,会导致列的值发生变化,而索引是利用原始值来创建的,所以索引将会失效
2、所以这里仍然要用原始的列birthday来比较
3、trunc:能够对输入的列的值按要求来截取('mm'表示截取到月份),同时会将剩余部分自动设置为日期的起始值(因为日期必须完整才有意义)
last_day:返回birthday所在月份的最后一天
4、add_months:用于给日期添加月份值
题目五![](https://img-blog.csdnimg.cn/direct/371816743fa94df89147ae9676313ea2.png)
create view test7_05 as
select * from
(select sid,name,birthday,
(select count(*) from pub.student
where birthday=t1.birthday+1
) nextbirthday
from pub.student_testindex t1) where nextbirthday=7
关键点:
1、本题标红语句无法使用索引的原因:创建索引使用的是原始的列值。后面where语句中的birthday-1操作会先将birthday列的值减去1后形成新的列再与t1比较,所以此时无法使用索引
2、想要使用索引where语句左边都只能保留原始的列,不允许对列得值进行修改
总结
本文的所有题目均来自《数据库系统概念》(黑宝书)、山东大学数据库实验七。不可用于商业用途转发。
如果能帮助到大家,大家可以点点赞、收收藏呀~