前言

以下问题都可以直接从chatgpt获取标准答案，或参考相关知识点大纲
mysql索引与sql优化面试题(建议收藏)

索引基础知识专题

1、说一下你对索引结构的理解

2、B+树相当于二叉搜索树、B树、哈希的优点是什么？

3、对高重复性字段创建索引，索引的结构是什么样的？

select * from my_table where city_code in ["371500","371400","371700","371600","371100","371000","371300","370800","370900","370200","370300","370100","370600","370700","370400","370500","621100","621000","623000","620400","620500","620600","620700","620100"]

比如我的mysql工单表中有个字段city_code，但city_code取值就是国内和海外城市的code码，国内的地级市只有300个左右，海外的城市大约有1000多个，如果工单表中数据足够多，那么city_code的重复性还是比较高的，对city_code字段创建了索引，索引的结构大体是什么样的？假如数据量特别大，索引高度是四层的话那结构又是什么样的？ 此处索引是如何辅助mysql快速完成这种范围查询的？

4、页大小为4KB,一个索引的指针为32bit，真实的数据大小为1024bit，这个三层B+树的索引总计需要多少页来存储？最多能存储多少数据？

5、三层B+树相比于四层B+树速度真的慢很多吗

6、按索引性能的由高到底说一下索引的的几种执行类型

7、聚簇索引与非聚簇索引区别

8、myisam与innodb区别

9、一个常见的三层高的聚簇索引，根据主键查询的过程是什么样的？这个过程的时间复杂度是什么？大概需要进行多少次IO？

sql基础知识专题

• 说一下sql中的执行顺序
• 分别说一下主键、外键、非空、唯一性索引
• order by和group by是否无法出现在同一个sql里
• 谈一下mysql架构工作流程

创建索引策略专题

适合创建索引的情况有哪些？不适合的有哪些？（11种适合，7种不适合）

索引失效判断与分析专题

select a,b,c from table where a=13 and b>16 and c=4;

select a,b,c from table where a=13 and b = 16 and c > 4;

select * from table where a = 1 or b = 2 or c =3

mysql多索引选择策略专题

我给name和age两个字段分别创建了一个索引(idx_name,idx_name)，那么mysql实际运行这条sql语句的时候，到底走的是哪条索引呢？

select  * from student where name = 'wzh' and age = 25;

提示：本题考察的是一个sql查询设计多个索引时mysql的索引选择策略，mysql会默认选择成本更低的策略，其中区分度高的字段优先放到联合索引的前列

模糊查询索引生效策略专题

select  * from table where name like "%王%" ;

select  * from table where name like "王%" ;

select  * from table where name like "%王" ;

1、哪条个sql索引能生效？为什么前缀模糊匹配能生效？

提示：要向面试官解释清楚mysql字符串索引中字符串排列的规则是什么，mysql是如何基于这个排列规则使用字符索引高效辅助完成前缀匹配的工作的

2、mysql中char类型字段的索引是不是和字典树比较像

2、第一和第三个sql索引无效，所有有没有这个索引完全没区别吗？

3、对于需要做全模糊查询的需求，应该使用什么技术？

Order by索引专题

select city,name,age from user where city = '杭州' order by create_time limit 10000;

• 对city字段创建索引idx_city，分析sql执行性能
• 对city，create_time字段创建索引idx_city_createTime，分析sql执行性能
• 对city，create_time字段创建索引 idx_city_name_createTime，分析sql执行性能

SELECT * FROM posts where provinceCode = 'xxx' ORDER BY created_ds DESC, like_count DESC;

• 该sql的含义是什么？
• 对city，create_time字段创建索引idx_provinceCode_createDs_linkCount，分析sql执行性能
• 对city，create_time字段创建索引idx_provinceCode_linkCount_createDs，分析sql执行性能

题型 group by索引专题

select city ,count(*) as num from staff where age = 30 group by city;

• 分析下无索引情况下的sql执行性能与执行原理
• 对city字段创建索引idx_city，分析sql执行性能
• 对city，create_time字段创建索引idx_city_createTime，分析sql执行性能
• 对city，create_time字段创建索引 idx_city_name_createTime，分析sql执行性能

SELECT * FROM posts where provinceCode = 'xxx' ORDER BY created_ds DESC, like_count DESC;

• 该sql的含义是什么？
• 对city，create_time字段创建索引idx_provinceCode_createDs_linkCount，分析sql执行性能
• 对city，create_time字段创建索引idx_provinceCode_linkCount_createDs，分析sql执行性能

limit 专题

1、说一下limit的底层原理是什么，为什么使用limit能明显降低查询速度？‘

# sql1
SELECT * from tb_template limit 0, 10;

# sql2
SELECT * from tb_template limit 59991, 10;

# sql3
SELECT  * from tb_template where id >= 59901;

2、说一下上面三个sql的执行性能

3、你对涉及limit查询的性能优化思考有哪些？

连表查询专题

1、mysql中有几种查询类型
2、什么是驱动表与被驱动表，sql连表查询中驱动表和被驱动表是固定的吗?
3、左连接，左表一定是驱动表，右表一定是被驱动表吗(反例可以看sql8)

4、有两张表，表user的主键是id和表order的主键是id，user_id字段对应user表的id，且这个字段建有索引idx_user_id，另一个字段status建有索引idx_status，分别指明各个sql的驱动表与被驱动表，已经对应的连表性能

# sql1
SELECT * FROM user u JOIN orders o ON o.user_id = u.id;

# sql2
SELECT * FROM user u JOIN orders o ON o.user_id = u.id;

# sql3
SELECT * FROM user u JOIN orders o ON o.user_id = u.id WHERE u.age BETWEEN 20 AND 30;

# sql4
SELECT * FROM user u JOIN orders o ON o.user_id = u.id WHERE u.age BETWEEN 20 AND 30;

# sql5
SELECT * FROM orders o JOIN user u ON u.id = o.user_id WHERE o.status = 1;

# sql6
SELECT *
FROM (
    SELECT user_id, SUM(amount) total
    FROM orders
    GROUP BY user_id
) t
JOIN user u ON u.id = t.user_id;

# sql7
SELECT *
FROM user u
JOIN orders o ON o.user_id = u.id
WHERE u.id = 100;

# sql8
select
		*
from 
	  play_log p 
left join 
  	song_info s on p.song_id = s.song_id
left join 
		user_info u  on p.user_id = u.user_id
where 
	 s.singer_name = '周杰伦' and u.age between 18 and 25;

说下这个复杂sql的含义是什么，连表的时间复杂度是什么

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num
;

范围查询专题

1、exists和in的区别是什么？exists一定是先遍历外表再查内表吗(反例可以看sql3)？in一定是先遍历内表再查外表吗？
2、有一个需求，要求在user用户表筛选出下过单的所有用户id，其中订单表是order，要求分别用exists和in来实现
3、题目2的两条sql如下，请你分析下什么情况下用in性能好，什么情况下用exists性能好

SELECT * FROM user u WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

SELECT * FROM user u
WHERE u.id IN (
    SELECT o.user_id
    FROM orders o
);

索引创建专题

1、什么时候时候适合创建联合索引

2、如果where条件有多个字段，怎么创建索引比较好？案例sql参考

select  * from student where name = 'wzh' and age = 25;

3、分别说明为什么对order by/groupby/join on/Distinct加了索引后能有效提升查询速度？

4、对较大的char类型创建索引需要注意什么？

5、如果要你针对这条sql创建索引，如果对这个sql创建索引，你觉得是只对city创建索引好，还是对city，name,age三个字段创建索引好？

select city,name,age from user where city = 'xxx';

复杂sql分析题

# 复杂sql1
select 
   tmp.month, tmp.rank as ranking, tmp.song_name, tmp.cnt as play_pv 
from (
	    select 
			s.song_name,
			p.song_id,
			MONTH(p.fdate) as month,
			count(*) as cnt,
			ROW_NUMBER() OVER(partition by (MONTH(p.fdate)) order by count(*) desc, p.song_id asc) as 'rank' 
	    from 
			play_log p 
		left join 
			song_info s on p.song_id = s.song_id
		left join 
			user_info u  on p.user_id = u.user_id
		where 
		   u.age Between 18 and 25 and s.singer_name = '周杰伦' and YEAR(p.fdate) = 2022
		group by 
		   p.song_id,s.song_name, MONTH(p.fdate)
   ) tmp 
where tmp.rank <= 3;

1、分析这个sql是用来做什么的？
2、如果让你创建索引来优化这个sql，你该怎么做？本sql中涉及到group by 的操作无法避免使用临时表了吗？
3、根据你创建的索引，评估分析一下mysql对这条sql的的执行顺序和执行性能，包括三表连接，分组，查询，聚合函数执行的这几步过程的分析

explain select 
    user_id, visit_nums
from (
   select 
       v.user_id as user_id, count(*) as visit_nums 
	from 
       visit_tb v 
	where 
      v.visit_time between '2022-09-02 00:00:00' and '2022-09-03 00:00:00' and 
      exists (select 1 from  order_tb o where v.user_id = o.user_id and o.order_time 
      between '2022-09-02 00:00:00' and '2022-09-03 00:00:00') 
      group by v.user_id order by visit_nums desc) tmp

1、分析这个sql是用来做什么的？
2、如果让你创建索引来优化这个sql，你该怎么做？
3、根据你创建的索引，评估分析一下mysql对这条sql的的执行顺序和执行性能