MySQL--什么时候才需要添加索引

最近在学习深入MySQL,记录一下学习历程和一些问题，下面的几种情况是我从网上搜到的，但是在实际测试中却和网上的结果有出入，希望得到大佬的指点

什么时候才需要添加索引？

首先创建表

drop table if exists index_test01;
create table `index_test01`(
	`id` int(11) NOT NULL AUTO_INCREMENT,/*AUTO_INCREMENT表示自增*/
`a` int(11) ,
`b` int(11) ,
`c` int(11) ,
`d` int(11) ,
PRIMARY KEY (`id`),/*主键*/
KEY `idx_a` (`a`),/*为a字段创建索引*/
KEY `idx_b_c` (`b`,`c`)/*为b,c字段创建联合索引*/
);
drop procedure if exists  insert_index_test01;  /*如果存储过程insert_index_test01存在就删除它*/
 delimiter $$ /*改变结束符  MYSQL的默认结束符为";"*/
create procedure insert_index_test01()  /*创建存储过程insert_index_test01*/
begin
declare i int;/*声明变量i*/
set i=1;/*设置i的初始值为1*/
while (i<100000)do /*循环10万次*/
insert into index_test01(a,b,c,d) values(i,i,i,i); /*自定义SQL  这里是为index_test01添加10万条数据*/
set i=i+1;
end while;
end $$
delimiter ;/*还原结束符*/
call insert_index_test01(); /* 运行存储过程insert_t9_1 */

想要看到明显的差距的话需要加表的数据
反复执行

insert into index_test01(a,b,c,d)
select a,b,c,d from index_test01;

情况1：条件字段加索引

现在对比一下不同SQL的查询速度
这个是不走索引的SQL

select * from index_test01 where c=9000

查询结果：
耗时0.45S

这个是走索引的SQL

select * from index_test01 where a=9000

耗时0.065S

我们再用explain在看一下

 explain select * from index_test01 where a=9000

可以看到rows字段只有16行，代表只扫描了一行

 explain select * from index_test01 where c=9000

可以看到rows字段只有1596288行，进行了一个全表扫描

情况2：聚合函数

直接上SQL

select max(a) from index_test01 

select max(d) from index_test01 

第一条耗时0.033s
第二条耗时0.47s
差距还是非常明显的

在看在explain下两条SQL的差距

explain select max(a) from index_test01 ;

explain select max(d) from index_test01 ;

这是也是进行了一个全表的扫描

情况3 排序

当我在进行排序时出现了一个疑问，我的语句是这样的

 select a from index_test01
where a<5000
 ORDER BY a

查询耗时4.248S

这里可以看到只扫描了17万行，是走了索引的
第二条SQL

select d from index_test01
where d<5000
 ORDER BY d

耗时4.625S

扫描了150万行，进行了一个全表的扫描，但是时间却和第一条差不多，不知道是什么原因，希望有大佬解惑。

情况4 避免回表

什么是避免回表呢？以下是个人理解，如果有不对的地方欢迎指正
我们索引所存储的只是有索引的那个字段和他的行id，当我们使用索引查出来之后是获取她的行id，在通过行id去进行扫描获得完整的数据。

select a,d from index_test01 where a<5000

因为我们只有a字段有索引
所以这里我们需要分为两步
1.通过a字段索引找到行id
2.通过行id再去扫描一次磁盘获取d字段的数据再返回

select b,c from index_test01 where b<5000

这里我们是有b、c字段的联合索引
索引我们只要通过索引就可以直接获取到b、c两个字段的值 直接返回出去
减少了一次磁盘的扫描，所以会快一些。

但是我在实际的测试中发现两条SQL的执行时间都是7秒左右，并没有太大差别，甚至会出现 联合索引比单字段索引更慢的情况，希望能有大佬解惑。

情况5 关联查询

创建表index_test02，表结构与index_test01一致

drop table if exists index_test02;
create table index_test02 like index_test01; /* 创建表index_test02，表结构与index_test01一致 */
insert into index_test02 
select * from index_test01 LIMIT 2000; /*添加表数据*/

走索引的关联查询

select a.a,b.a from index_test01 a
inner join index_test02 b on a.a=b.a

耗时2.402S

不走索引的关联查询

select a.d,b.d from index_test01 a
inner join index_test02 b on a.d=b.d

耗时152.070S

相差了一百多秒

到此就结束了，我们做一个总结
一共有5种情况

1. 数据检索时在条件字段添加索引
2. 聚合函数对聚合字段添加索引
3. 对排序字段添加索引
4. 为了防止回表添加索引
5. 关联查询在关联字段添加索引
   其中3、4这两种情况我的测试并不符合网上所述，具体原因等我日后更加深入学习后或者有大佬指出问题后再做解答吧