目录
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为关键词建立一个目录
题目二
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、验证问题是无法使用索引的。因为索引查询必须要符合最左匹配原则
题目三
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、||‘%’作用是字符串连接
题目四
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：用于给日期添加月份值
题目五
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语句左边都只能保留原始的列，不允许对列得值进行修改
总结
本文的所有题目均来自《数据库系统概念》（黑宝书）、山东大学数据库实验七。不可用于商业用途转发。
如果能帮助到大家，大家可以点点赞、收收藏呀~
1583
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
