在40岁老架构师 尼恩的读者交流群(50+)中,最近有小伙伴拿到了一线互联网企业如得物、阿里、滴滴、极兔、有赞、希音、百度、网易、美团的面试资格,遇到很多很重要的面试题:
1.谈谈你对MySQL联合索引的认识?
2.在MySQL中,联合索引是如何实现的?请简述其工作原理。
3.什么是最左前缀匹配原则?为什么要遵守?
4.MySQL一定要遵循最左前缀匹配吗?
最近有小伙伴在面试贝壳+soul,又遇到了相关的面试题。小伙伴懵了,因为没有遇到过,所以支支吾吾的说了几句,面试官不满意,面试挂了。
所以,尼恩给大家做一下系统化、体系化的梳理,使得大家内力猛增,可以充分展示一下大家雄厚的 “技术肌肉”,让面试官爱到 “不能自已、口水直流”,然后实现”offer直提”。
当然,这道面试题,以及参考答案,也会收入咱们的 《尼恩Java面试宝典PDF》V171版本,供后面的小伙伴参考,提升大家的 3高 架构、设计、开发水平。
最新《尼恩 架构笔记》《尼恩高并发三部曲》《尼恩Java面试宝典》的PDF,请关注本公众号【技术自由圈】获取,回复:领电子书
- 尼恩说在前面
- 联合索引和 mysql 调优的关系
- 基础知识:MySQL索引机制
- 索引的创建
- 索引的查询
- 索引的查询
- MySQL联合索引
- 什么是联合索引
- 联合索引存储结构
- 最左前缀匹配原则
- 1 全值匹配查询时
- 2 匹配左边的列时
- 3 匹配列前缀
- 4 匹配范围值
- 5 精确匹配某一列并范围匹配另外一列
- 6 排序
- 为什么要遵循最左前缀匹配?
- 一定要遵循最左前缀匹配吗?
- 联合索引注意事项
- 说在最后:有问题找老架构取经
mysql 调优 的一个核心动作,就是 通过 联合索引 实现 索引覆盖。
在MySQL中,合理使用联合索引可以提高查询效率,通过 联合索引 实现 索引覆盖 ,常常需要注意一些技巧:
选择合适的列: 联合索引的列顺序非常重要。应该优先选择最频繁用于查询条件的列,以提高索引的效率。其次考虑选择性高的列,这样可以过滤出更少的数据。
避免冗余列: 联合索引的列应该尽量避免包含冗余列,即多个索引的前缀相同。这样会增加索引的维护成本,并占用更多的存储空间。
避免过度索引: 不要为每个查询都创建一个新的联合索引。应该根据实际情况,分析哪些查询是最频繁的,然后创建针对这些查询的索引。
覆盖索引: 如果查询的列都包含在联合索引中,并且不需要访问表的其他列,那么MySQL可以直接使用索引来执行查询,而不必访问表,这种索引称为覆盖索引,可以提高查询性能。
使用EXPLAIN进行查询计划分析: 使用MySQL的EXPLAIN语句可以查看MySQL执行查询的执行计划,以便优化查询语句和索引的使用。
定期优化索引: 随着数据库的使用,索引的效率可能会下降,因此需要定期进行索引的优化和重建,以保持查询性能的稳定性。
分析查询日志: 监控数据库的查询日志,分析哪些查询是最频繁的,以及它们的查询模式,可以帮助确定需要创建的联合索引。
避免过度索引更新: 避免频繁地更新索引列,因为每次更新索引都会增加数据库的负载和IO操作。
综上所述,联合索引是mysql 调优的一个核心动作, 通过 联合索引进行mysql 调优时,需要综合考虑列的选择、索引的覆盖、查询的频率和模式等因素,以提高MySQL数据库的查询性能。
正因为如此, 联合索引 是面试的重点和难点。
回答这个面试题,我们可以从最为基础的MySQL索引机制 开始讲起。
在关系型数据库中,索引是一种单独的、物理的数据,对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合,以及相应的指向表中物理标识这些值的数据页的逻辑指针清单。
在关系型数据库中,索引是一种用来帮助快速检索目标数据的存储结构。
使用CREATE语句创建
CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]);
使用ALTER语句创建
ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);
建表时DDL语句中创建
CREATE TABLE tableName(
columnName1 INT(8) NOT NULL,
columnName2 ....,
.....,
INDEX [indexName] (columnName(length))
);
SHOW INDEX from tableName;
ALTER TABLE table_name DROP INDEX index_name;
DROP INDEX index_name ON table_name;
下述SQL会用到索引,因为where子句中,几个搜索条件顺序调换不影响查询结果,因为MySQL中有查询优化器,会自动优化查询顺序。
select * from table_name where a = '1' and b = '2' and c = '3'
select * from table_name where b = '2' and a = '1' and c = '3'
select * from table_name where c = '3' and b = '2' and a = '1'
下述SQL,都从最左边开始连续匹配,用到了索引。
select * from table_name where a = '1'
select * from table_name where a = '1' and b = '2'
select * from table_name where a = '1' and b = '2' and c = '3'
下述SQL中,没有从最左边开始,最后查询没有用到索引,用的是全表扫描。
select * from table_name where b = '2'
select * from table_name where c = '3'
select * from table_name where b = '1' and c = '3'
下述SQL中,如果不连续时,只用到了a列的索引,b列和c列都没有用到
select * from table_name where a = '1' and c = '3'
select * from table_name where a like 'As%'; //前缀都是排好序的,走索引查询
select * from table_name where a like '%As'; //全表查询
select * from table_name where a like '%As%'; //全表查询
下述SQL,可以对最左边的列进行范围查询
select * from table_name where a > 1 and a < 3
多个列同时进行范围查找时,只有对索引最左边的那个列进行范围查找才用到B+树索引,也就是只有a用到索引。 在1<a<3的范围内b是无序的,不能用索引,找到1<a<3的记录后,只能根据条件 b > 1继续逐条过滤。
select * from table_name where a > 1 and a < 3 and b > 1;
select * from table_name where a = 1 and b > 3;
select * from table_name order by b,c,a limit 10;
select * from table_name order by b,c,a limit 10;
在以下SQL中颠倒顺序,没有用到索引
select * from table_name order by a limit 10;
select * from table_name order by a,b limit 10;
以下SQL中会用到部分索引,联合索引左边列为常量,后边的列排序可以用到索引
select * from table_name where a =1 order by b,c limit 10;
SELECT * FROM table_name WHERE B = `xxx` AND C = `xxx`;
SELECT * FROM `table_name ` WHERE B = `xxx` AND C = `xxx`
UNION ALL
SELECT * FROM `table_name ` WHERE B = `xxx` AND C = `xxx` AND A = "yyy"
......
SELECT * FROM `table_name ` WHERE B = `xxx` AND C = `xxx` AND A = "zzz";
但是跳跃扫描机制也有很多限制,比如多表联查时无法触发、SQL条件中有分组操作也无法触发、SQL中用了DISTINCT去重也无法触发等等,总之有很多限制条件,具体的可以参考《MySQL官网8.0-跳跃扫描》。
set @@optimizer_switch = 'skip_scan=off|on';
实现职业转型,极速上岸
关注职业救助站公众号,获取每天职业干货
助您实现职业转型、职业升级、极速上岸
---------------------------------
实现架构转型,再无中年危机
关注技术自由圈公众号,获取每天技术千货
一起成为牛逼的未来超级架构师
几十篇架构笔记、5000页面试宝典、20个技术圣经
请加尼恩个人微信 免费拿走
暗号,请在 公众号后台 发送消息:领电子书
如有收获,请点击底部的"在看"和"赞",谢谢