2024年Java最全mysql 索引面试再也不怕啦(1)，mysql存储过程面试题

最后

还有Java核心知识点+全套架构师学习资料和视频+一线大厂面试宝典+面试简历模板可以领取+阿里美团网易腾讯小米爱奇艺快手哔哩哔哩面试题+Spring源码合集+Java架构实战电子书+2021年最新大厂面试题。

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

创建全文索引的方法如下：

##方式一

create FULLTEXT index 索引名 on 表名(字段1);

create fulltext index index_status on r_resume(status)

##方式二

ALTER TABLE 表名 add FULLTEXT INDEX 索引名(字段1);

ALTER TABLE r_resume add fulltext INDEX index_status(status);

和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如

select * from r_resume where MATCH(status) AGAINST(‘我目前已离职’)

全文索引使用注意事项：

show variables like ‘%ft%’;

• 全文索引必须在字符串、文本字段上建立。

• 全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）

• 全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa

• 全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*

索引原理

---

MySQL官方对索引定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。

索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。

索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。

索引涉及的理论知识：二分查找法、Hash 和 B+Tree。

B 树结构

• 索引值和data数据分布在整棵树结构中

• 每个节点可以存放多个索引值及对应的data数据

• 树节点中的多个索引值从左到右升序排列

B树的搜索：从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。

B+ 树结构

• 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值

• 叶子节点包含了所有的索引值和data数据

• 叶子节点用指针连接，提高区间的访问性能

相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。

聚集索引

聚簇索引和非聚簇索引：B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行记录分开存放就属于非聚簇索引。

在InnoDB引擎中，主键索引采用的就是聚簇索引结构存储。

聚簇索引是一种数据存储方式，InnoDB的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。 这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引。

索引分析与优化

---

Explain

MySQL 提供了一个 EXPLAIN 命令，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信息，供开发人员有针对性的优化。例如：

EXPLAIN select * from r_resume where MATCH(status) AGAINST(‘我目前已离职’) ;

explain select * from r_resume where id> 2195333;

• select_type

表示查询的类型。常用的值如下：

• SIMPLE ： 表示查询语句不包含子查询或union

• PRIMARY：表示此查询是最外层的查询

• UNION：表示此查询是UNION的第二个或后续的查询

• DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果

• UNION RESULT：UNION的结果

• SUBQUERY：SELECT子查询语句

• DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。

explain select * from r_resume where id> 2195333 union select * from r_resume where id=1;

explain select * from r_resume where id in(select id from r_resume where sex=‘男’);

• type

表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。常用属性值如下，从上至下效率依次增强。

• ALL：表示全表扫描，性能最差。

• index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。

• range：表示使用索引范围查询。使用>、>=、<、<=、in等等。

• ref：表示使用非唯一索引进行单值查询。

• eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。

• const：表示使用主键或唯一索引做等值查询，常量查询。

• NULL：表示不用访问表，速度最快。

explain select * from r_resume where id= 2195333 union select * from r_resume where id=1;

explain select * from r_resume where id in(select id from r_resume where sex=‘男’);

explain select * from r_resume where sex=‘男’;

explain select * from r_resume where id> 2195333;

explain select * from r_resume where id= 2195333;

• possible_keys：表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称。

• key：表示查询时真正使用到的索引，显示的是索引名称。

• rows：MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低。

• key_len：表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。

key_len的计算规则如下：

• 字符串类型

字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4

char(n)：n*字符集长度

varchar(n)：n * 字符集长度 + 2字节

• 数值类型

TINYINT：1个字节

SMALLINT：2个字节

MEDIUMINT：3个字节

INT、FLOAT：4个字节

BIGINT、DOUBLE：8个字节

• 时间类型

DATE：3个字节

TIMESTAMP：4个字节

DATETIME：8个字节

• 字段属性

NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项。

• Extra：表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：

• Using where: 表示查询需要通过索引回表查询数据

• Using index: 表示查询需要通风索引就可以满足所需数据

• Using fifilesort：查询出来的结果需要额外的排序，数据量小在内存，大的话在磁盘，因此有Using fifilesort建议优化。

• Using temprorary：查询使用到了临时表，一般出现于去重、分组等操作。

回表查询

在之前介绍过，InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询，它的性能比扫一遍索引树低。

总结：通过索引查询主键值，然后再去聚簇索引查询记录信息

覆盖索引

在MySQL官网，类似的说法出现在explain查询计划优化章节，即explain的输出结果Extra字段为Using index时，能够触发索引覆盖。不管是SQL-Server官网，还是MySQL官网，都表达了：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。

实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。

最左前缀原则

复合索引使用时遵循最左前缀原则，最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。

LIKE 查询

MySQL 在使用 like 模糊查询时，索引能不能起作用？

MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引。

explain select * from r_resume where status like ‘%离职%’;（没有用到索引）

explain select * from r_resume where status like ‘离职%’; （用到了索引）

explain select * from r_resume where status like ‘离职%’; （没有用到）

NULL 查询

如果 MySQL 表的某一列含有 NULL 值，那么包含该列的索引是否有效？

有效。

explain select * from r_resume where email is null;

对MySQL来说，NULL是一个特殊的值，从概念上讲，NULL意味着“一个未知值”，它的处理方式与其他值有些不同。比如：不能使用=，<，>这样的运算符，对NULL做算术运算的结果都是NULL，count时不会包括NULL行等，NULL比空字符串需要更多的存储空间等。

虽然MySQL可以在含有NULL的列上使用索引，但NULL和其他数据还是有区别的，不建议列上允许为NULL。最好设置NOT NULL，并给一个默认值，比如0和 ‘’ 空字符串等，如果是datetime类型，也可以设置系统当前时间或某个固定的特殊值，例如’1970-01-01 00:00:00’。

查询优化

---

慢查询定位

1、开启慢查询

查看 MySQL 数据库是否开启了慢查询日志和慢查询日志文件的存储位置的命令如下：

show variables like ‘%slow_query_log%’;

通过如下命令开启慢查询日志：

SET global slow_query_log = ON; //开启慢查询的开关

SET global slow_query_log_file = ‘OAK-slow.log’; //修改慢查询日志存放的位置

SET global log_queries_not_using_indexes = ON; //没有用到索引的查询就会记录

SET long_query_time = 10;//查询时间超过10s 就会记录

2、慢日志查询方式

• 直接找到对应的文件，通过记事本查看。

time：日志记录的时间

User@Host：执行的用户及主机

Query_time：执行的时间

Lock_time：锁表时间

Rows_sent：发送给请求方的记录数，结果数量

Rows_examined：语句扫描的记录条数

SET timestamp：语句执行的时间点

select…：执行的具体的SQL语句

• 使用mysqldumpslow查看

MySQL 提供了一个慢查询日志分析工具mysqldumpslow，可以通过该工具分析慢查询日志内容。

在 MySQL bin目录下执行下面命令可以查看该使用格式。

perl mysqldumpslow.pl --help

慢查询优化

慢查询原因总结

• 全表扫描：explain分析type属性all

• 全索引扫描：explain分析type属性index

• 索引过滤性不好：靠索引字段选型、数据量和状态、表设计

• 频繁的回表查询开销：尽量少用select *，使用覆盖索引

如何判断是否为慢查询？

MySQL判断一条语句是否为慢查询语句，主要依据SQL语句的执行时间，它把当前语句的执行时间跟 long_query_time 参数做比较，如果语句的执行时间 > long_query_time，就会把这条执行语句记录到慢查询日志里面。long_query_time 参数的默认值是 10s，该参数值可以根据自己的业务需要进行调整。

如何判断是否应用了索引？

总结

蚂蚁面试比较重视基础，所以Java那些基本功一定要扎实。蚂蚁的工作环境还是挺赞的，因为我面的是稳定性保障部门，还有许多单独的小组，什么三年1班，很有青春的感觉。面试官基本水平都比较高，基本都P7以上，除了基础还问了不少架构设计方面的问题，收获还是挺大的。

---

经历这次面试我还通过一些渠道发现了需要大厂真实面试主要有：蚂蚁金服、拼多多、阿里云、百度、唯品会、携程、丰巢科技、乐信、软通动力、OPPO、银盛支付、中国平安等初，中级，高级Java面试题集合，附带超详细答案，希望能帮助到大家。

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

条语句是否为慢查询语句，主要依据SQL语句的执行时间，它把当前语句的执行时间跟 long_query_time 参数做比较，如果语句的执行时间 > long_query_time，就会把这条执行语句记录到慢查询日志里面。long_query_time 参数的默认值是 10s，该参数值可以根据自己的业务需要进行调整。

如何判断是否应用了索引？

总结

蚂蚁面试比较重视基础，所以Java那些基本功一定要扎实。蚂蚁的工作环境还是挺赞的，因为我面的是稳定性保障部门，还有许多单独的小组，什么三年1班，很有青春的感觉。面试官基本水平都比较高，基本都P7以上，除了基础还问了不少架构设计方面的问题，收获还是挺大的。

---

经历这次面试我还通过一些渠道发现了需要大厂真实面试主要有：蚂蚁金服、拼多多、阿里云、百度、唯品会、携程、丰巢科技、乐信、软通动力、OPPO、银盛支付、中国平安等初，中级，高级Java面试题集合，附带超详细答案，希望能帮助到大家。

[外链图片转存中…(img-032T63Kn-1714866829559)]

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取