【2021-09-16】mysql基础学习索引

学习链接：https://www.cnblogs.com/zsql/p/13854191.html

-- https://www.cnblogs.com/zsql/p/13854191.html
show index  from  user; -- 查看city表中的索引信息
show index  from  department; -- 查看city表中的索引信息
create index idx_d_id on user (d_id); -- 创建索引
DROP INDEX idx_d_id ON user; -- 删除索引



-- 哪些慢查询后怎么去分析呢，当然是借助explain啦【一定要用小表驱动大表】
explain select * from department d left join user u on u.d_id = d.id;
/*
可以看到key这一列都是为null，所以两个表都是全表扫描呢
*/


-- [*] 两表left join
create index idx_d_id on user (d_id);
DROP INDEX idx_d_id ON user; -- 删除索引
explain select * from department d left join user u on u.d_id = d.id;
/* 
无索引： 
	USER:全表扫描
	注释：department表在id上有主键索引，但是作为左连接，department需要保留全部的数据，所以建立索引是没什么影响的

有索引：user表d_id
	看结果可以得出user表使用了索引，减少了数据的读取
	注释：可以得出left join主要的优化在于右表的索引的创建，那right join也是一样在于左表的索引的情况
*/


-- [*] 两表inner join
create index idx_d_id on user (d_id);
DROP INDEX idx_d_id ON user; -- 删除索引
explain select * from department d inner join user u on u.d_id = d.id;
/* 
无索引： 
	USER:全表扫描
	注释：department表在id上有主键索引，但是作为左连接，department需要保留全部的数据，所以建立索引是没什么影响的

有索引：user表d_id
	好像优化器会选择把小的表来驱动大的表，全表扫描小的表，大的表走索引。
*/




-- [*] 单表order by DEPARTMENT主键
create index idx_d_id on user (d_id);
DROP INDEX idx_d_id ON user; -- 删除索引
explain select * from user order by d_id;
/* 
无索引： 
输出：
	USER:filesort

有索引：user表d_id
输出：
	USER:filesort
	
原因：创建了索引，没有使用覆盖索引；选择以全部列返回查询时就出现【回表】
*/



-- [*] 单表order by DEPARTMENT主键
create index idx_d_id on user (d_id);
DROP  INDEX  idx_d_id  ON  user; -- 删除索引
explain select d_id from user order by d_id;
/* 
无索引： 
输出：
	USER:filesort

有索引：user表d_id
输出：
	USER:index
	
原因：这里只返回d_id,索引排序使用了索引d_id,会发生【覆盖索引】
*/




-- [*] 两表left join, order by 其他
create index idx_d_id on user (d_id);
DROP  INDEX  idx_d_id  ON  user; -- 删除索引
explain select * from department d left join user u on u.d_id = d.id order by d.d_name; 
/* 
无索引： 
输出：
	DEPARTMENT：临时表，filesort
	USER:全表扫描

有索引：user表d_id
输出：
	DEPARTMENT：filesort
	
原因：未知
*/




-- [*] 两表left join, order by DEPARTMENT主键
create index idx_d_id on user (d_id);
DROP  INDEX  idx_d_id  ON  user; -- 删除索引
explain select * from department d left join user u on u.d_id = d.id order by d.id;
/* 
无索引： 
输出：
	DEPARTMENT：临时表，filesort
	USER:全表扫描

有索引：user表d_id
输出：
	创建索引后user表的rows为1，没有使用filesort和临时表
	
原因：完美
*/


-- [*] 两表left join, order by USER主键
create index idx_d_id on user (d_id);
DROP  INDEX  idx_d_id  ON  user; -- 删除索引
explain select * from department d left join user u on u.d_id = d.id order by u.d_id; 
/* 
无索引： 
输出：
	DEPARTMENT：临时表，filesort
	USER:全表扫描

有索引：user表d_id
输出：
	DEPARTMENT：临时表，filesort
	
原因：把排序条件换成user的字段后，使用了临时表和filesort【有索引】
*/



-- [*] 两表left join, order by 其他
create index idx_d_id on user (d_id);
DROP  INDEX  idx_d_id  ON  user; -- 删除索引
explain select * from department d left join user u on u.d_id = d.id order by d.d_name; 
/* 
无索引： 
输出：
	DEPARTMENT：临时表，filesort
	USER:全表扫描

有索引：user表d_id
输出：
	USER：filesort
	
原因：把排序字段换成department表的d_name列（没有索引），使用了filesort【有索引】
*/





-- [*] 两表inner join, order by USER主键和其他
create index idx_d_id on user (d_id);
DROP  INDEX  idx_d_id  ON  user; -- 删除索引
explain select * from department d inner join user u on u.d_id = d.id order by d.d_name,u.id; 
/* 
无索引： 
输出：
	USER:全表扫描，filesort

有索引：user表d_id
输出：
	DEPARTMENT：临时表，filesort
	
原因：使用了user表的id排序，新增临时表【有索引】
*/

		

-- [*] 两表inner join, order by USER主键和其他
create index idx_d_id on user (d_id);
DROP  INDEX  idx_d_id  ON  user; -- 删除索引
explain select d.* from department d inner join user u on u.d_id = d.id order by d.d_name,u.id;
/* 
无索引： 
输出：
	USER:全表扫描，filesort

有索引：user表d_id
输出：
	DEPARTMENT：临时表，filesort
	USER:index
		
原因：这里只返回department表的全部列，还是使用了临时表和filesort
*/


-- [*] 两表inner join, order by 其他【department表全部字段】
create index idx_d_id on user (d_id);
DROP  INDEX  idx_d_id  ON  user; -- 删除索引
explain select d.* from department d inner join user u on u.d_id = d.id order by d.d_name;
/* 
无索引： 
输出：
	USER:全表扫描
	DEPARTMENT：临时表，filesort

有索引：user表d_id
输出：
	DEPARTMENT：filesort
	USER:index
		
原因：去掉user的id列排序后少了临时表【有索引】
*/



-- [*] 两表inner join, order by DEPARTMENT主键【department表全部字段】
create index idx_d_id on user (d_id);
DROP  INDEX  idx_d_id  ON  user; -- 删除索引
explain select d.* from department d inner join user u on u.d_id = d.id order by d.id; 
/* 
无索引： 
输出：
	USER:全表扫描
	DEPARTMENT：临时表，filesort

有索引：user表d_id
输出：
	USER:index
		
原因：使用department的id排序，没了filesort【有索引】
*/