担心搞不定面试官？吃透这份MySQL面试习题，助你斩获大厂Offer

drop 直接整个表的结构都删除,再想记录数据,要重新建表

truncate:清空表的数据,并且释放空间,表结构还在,清空索引,不可回滚

delete:删除表中的指定数据,不释放空间,不清除索引,可以回滚

如何定位低效的sql?

======================================================================

慢查询日志

在业务系统中,除了使用主键进行的查询,其他的都会在测试库上测试其耗时,慢查询的统计主要由运维做.

慢查询的优化首先要搞明白慢的原因是什么?是查询条件没有命中索引?是load了不需要的数据列?还是数据量太大?

1. 首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对语句进行分析以及重写

2. 分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能地命中索引

3. 如果对语句地优化已经无法进行,可以考虑表中地数据量是否太大,如果是可以进行横向或者纵向地分表

横向分表和纵向分表

====================================================================

• 纵向分表

将本来可以在同一个表的内容，人为划分为多个表

对于一个博客系统，文章标题，作者，分类，创建时间等，是变化频率慢，查询次数多，而且最好有很好的实时性的数据，我们把它叫做冷数据。而博客的浏览量，回复数等，类似的统计信息，或者别的变化频率比较高的数据，我们把它叫做活跃数据。所以，在进行数据库结构设计的时候，就应该考虑分表，首先是纵向分表的处理。

• 横向分表

将用户表user分为user1,user2对id做特殊处理

InnoDB和MyISAB的区别

===========================================================================

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，

4. 因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

5. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

索引的基本原理

==================================================================

索引是存储引擎用于快速找到记录的一种数据结构.

索引用来快速地寻找那些具有特定值地记录,如果没有索引,一般来说执行查询时遍历整张表

索引的原理:就是把无序的数据变成有序的查询

1. 把创建了索引的列的内容进行排序

2. 对排序结果生成倒排表

3. 在倒排表内容上拼上数据地址链

4. 在查询的时候,先拿到倒排表内容,再取出数据地址链,从而拿到具体数据

Mysql索引为什么不常用Hash?

=============================================================================

从内存角度,据库中的索引一般是存储在磁盘上的，使用hash索引需要在内存中构建hash表，而表的数据量很大的时候可能无法把全量的索引列数据一次性装入内存；B+树每一个超级节点的大小可以设置成一个数据页的大小，每次查询只加载符合条件的少部分数据页，而不必把全量的索引数据都加载到内存。

从业务场景上，如果只需要根据特定条件查询一条数据的话确实hash更快，但是在实际业务中经常会查询多条、查询某个条件范围内的数据，这时候由于B+树索引有序，并且有链表相连，可以找第一个匹配上的，然后顺着链表把符合查询条件的数据一次取出来；而hash则无法做到这样的范围条件查询，因为是无序的，只能需要逐条遍历匹配。

Mysql索引为什么不用红黑树?

===========================================================================

树的查询时间跟树的高度有关，B+树是一棵多路搜索树可以降低树的高度，提高查找效率。此外，操作系统针对硬盘读写的最小单元是块(block)，一个block大小一般是4KB, 也就是一次至少会读取4KB; 红黑树是二叉树，每层只有两个节点，加载一部分节点需要多次磁盘随机IO操作，效率非常低。

Mysql聚簇和非聚簇索引的区别

===========================================================================

都是B+树的数据结构

• 聚簇索引:将数据存储与索引放到了一块,并且是按照一定的顺序组织的,找到索引也就找到了数据,数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的.

• 非聚簇索引:叶子节点不存储数据,存储的是数据行地址,也就是说根据索引查找到数据行的位置再去磁盘查找数据,这个就有点类似一本树的目录

优势

1. 查询通过聚簇索引可以直接获取数据,相比非聚簇索引需要第二次查询(非覆盖索引的情况下)效率要高

2. 聚簇索引对于范围查询的效率很高,因为其数据是按照大小排列的

3. 聚簇索引适合用在排序的场合,非聚簇索引不适合

劣势

1. 维护索引很昂贵,特别是插入新行或者主键被更新导致要分页的时候,建议在大量插入新行后,选择负载较低的时间段,通过optimize table优化表,因为必须被移动的行数据可能造成碎片,使用独享表可以弱化碎片

2. 表如果使用UUID(随机ID)作为主键,数据存储会很稀疏,这就会出现聚簇索引有可能比全表扫描更慢,所以建议使用Int的auto_increment作为主键

如果主键比较大的话,那辅助索引将会变的更大,因为辅助索引的叶子存储的是主键值,过长的主键值,会导致非叶子节点占用更多的物理空间

• InnoDB中一定有主键,主键一定是聚簇索引,不手动设置,则会使用unique索引,没有unique索引,则会使用数据库内部的一个行的隐藏id来当作主键索引.在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找,非聚簇索引都是辅助索引,像复合索引,前缀索引,唯一索引,辅助索引叶子节点存储的不再是行的物理位置,而是主键值.

• MyISM使用的是非聚簇索引,没有聚簇索引,非聚簇索引的两颗B+树看上去没什么不同,节点的结构完全一致,只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助索引B+树存储了辅助键,表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别,由于索引树是独立的,通过索引键检索无需访问主键的索引树

如果涉及到大数据量的排序,全表扫描,count之类的操作的话,还是MyISAM占优势,因为索引所占的空间小,这些操作是需要在内存中完成的

Mysql索引的数据结构,各自优劣

============================================================================

B+树-----平衡多叉树

索引设计的原则

==================================================================

查询更快,占用空间更小

mysql执行计划怎么看?(explain)

=================================================================================

explain select * from A whrer X=? and Y=?

select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序

id相同，执行顺序由上至下

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id相同不同，同时存在

id号每个号码，表示一趟独立的查询。一个sql 的查询趟数越少越好。

selectType:查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

SIMPLE:简单的 select 查询,查询中不包含子查询或者UNION

PRIMARY:查询中若包含任何复杂的子部分，最外层查询则被标记为Primary

DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生)

MySQL会递归执行这些子查询, 把结果放在临时表里。

SUBQUERY:在SELECT或WHERE列表中包含了子查询

DEPENDENT SUBQUERY:在SELECT或WHERE列表中包含了子查询,子查询基于外层

UNCACHEABLE SUBQUREY

UNION:若第二个SELECT出现在UNION之后，则被标记为UNION；

若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED

UNION RESULT:从UNION表获取结果的SELECT

table:显示这一行的数据是关于哪张表的

partitions:代表分区表中的命中情况，非分区表，该项为null

type :优化sql的重要字段,也是我们判断sql性能和优化程度的重要指标

possible_keys:显示可能应用在这张表中的索引，一个或多个。

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key:实际使用的索引。如果为NULL，则没有使用索引

key_len:表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len字段能够帮你检查是否充分的利用上了索引

ref:显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

rows:rows列显示MySQL认为它执行查询时必须检查的行数。

filtered:这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数

Extra:包含不适合在其他列中显示但十分重要的额外信息

索引失效的场景

==================================================================

1. where语句中包含or时，可能会导致索引失效(使用or并不是一定会使索引失效，你需要看or左右两边的查询列是否命中相同的索引)。

2. where语句中索引列使用了负向查询，可能会导致索引失效,负向查询包括：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等。

3. 索引字段可以为null，使用is null或is not null时，可能会导致索引失效

4. 在索引列上使用内置函数，一定会导致索引失效

5. 对索引列进行运算，一定会导致索引失效

6. like通配符可能会导致索引失效

7. 联合索引中，where中索引列违背最左匹配原则，一定会导致索引失效

8. MySQL优化器的最终选择，不走索引

mysql主从同步原理

======================================================================

mysql主从同步的过程

Mysql的主从复制中主要有三个线程:master(binlog dump thread),slave(I/O thread,sql thread),Master一条线程和Slave中的两条线程.

主节点binlog,主从复制的基础是主库记录数据库的所有变更记录到binlog,binlog是数据库服务器启动的那一刻起,保存所有修改数据库结构或内容的一个文件

主节点log dump线程,当binlog有变动时,log dump线程读取其内容并发送给节点.

从节点I/O线程接受binlog内容,并将其写入到relay log中

从节点的SQL线程读取relay log文件内容对数据更新进行重放,最终保证主从数据库的一致性

注:主从节点使用binlog文件+position偏移量来定位主从同步的位置,从节点会保存其已接收到的偏移量,如果从节点发送宕机重启,则会自动从position的位置发起同步

由于mysql默认的复制方式是异步的,主库把日志发送给从库后不关心从库是否已经处理,这样会产生一个问题就是假设主库挂了,从库处理失败了,这时候从库升为主库后,日志就丢失了,由此产生两个概念

全同步复制

主库写入binlog后强制同步日志到从库,所有的从库都执行完成后才返回给客户端,但是很显然这个方式的话性能会收到严重影响

半同步复制

和全同步不同的是,半同步复制的逻辑是这样,从库写入日志成功后返回ACK确认为主库,主库收到至少一个从库的确认就认为写操作完成

简述mysql索引类型及对数据库的性能的影响

=================================================================================

• 普通索引:允许被索引的数据列包含重复的值

• 唯一索引:可以保证数据记录的唯一性

• 主键:是一种特殊的索引,在一张表只能定义一个主键索引,主键用于唯一标识的一条记录,使用关键字PRIMARY KEY来创建

• 联合索引:索引可以覆盖多个数据列,例如Index(columnA,columnB)索引

• 全文索引:通过建立倒排索引,可以极大的提升检索效率,解决判断字段是否包含的问题,是目前搜索引擎的一种关键技术.

索引可以极大的提高数据的查询数据.

通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能

但是会降低插入,删除,更新表的速度,因为在执行这些写操作时,还要操作索引文件

索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大,如果非聚簇索引很多,一旦聚簇索引改变,那么所有非聚簇索引都会跟着变

当然了，除了背，还得理解，否则对面试过后的工作帮助也不大！我这里有一份各个大厂的 Java 面试真题合辑和源码剖析视频**，想要的同学可以 【[我的交流企鹅群（点这里】 找群主小助理即可获取！

。

Java架构速成笔记涵盖内容如下：

============================================================================

• 专题一：扎牢基础，深入底层，面试我可造火箭（多线程与高并发+深入JMM+JVM调优案例式实战化指导+经常被问操作系统原理剖析+从底向上网络原理解读+案例式深入解析23种设计模式+设计原则水到渠成）

• 专题二：洞悉分布，明察并行，一路架构冲云霄（消息中间件+分布式）

• 专题三：高薪必问，无处可避，不如迎头直接上（深入透彻完整解析Redis+源码层面无死角解析Netty）

• 专题四：未来主流，必知必会，服务微化量绝招（RPC构建分布式+Spring Boot+Spring Cloud+Docker+k8s）

• 专题五：分布存储，高效写读，优化性能带你飞（Mysql优化+FastDFS+OpenResty）

• 专题六：团队协作，项目管理，测试开发一体化（maven+git+Jenkins+FindBugs）

• 专题七：庖丁解牛，手写源码，一步一步成大牛（JDK源码解析+Spring源码解析+MyBatis源码解析+Dubbo源码解析+Spring MVC源码解析+Netty源码解析）

专题一：扎牢基础，深入底层，面试我可造火箭

================================================================================

1.多线程与高并发

2.深入JMM

3.JVM调优案例式实战化指导

4.经常被问操作系统原理剖析

5.从底向上网络原理解读

6.案例式深入解析23种设计模式

7.设计原则水到渠成

专题二：洞悉分布，明察并行，一路架构冲云霄

================================================================================

1.消息中间件

2.分布式

专题三：高薪必问，无处可避，不如迎头直接上

================================================================================

1.深入透彻完整解析Redis

2.源码层面无死角解析Netty

专题四：未来主流，必知必会，服务微化量绝招

================================================================================

1.RPC构建分布式

2.Spring Boot

3.Spring Cloud

4.Docker

5.k8s

专题五：分布存储，高效写读，优化性能带你飞

================================================================================

1.Mysql优化

2.FastDFS

3.OpenResty

专题六：团队协作，项目管理，测试开发一体化

================================================================================

1.maven

2.git

3.Jenkins

4.FindBugs

专题七：庖丁解牛，手写源码，一步一步成大牛

================================================================================

1.JDK源码解析

2.Spring源码解析

3.MyBatis源码解析

4.Dubbo源码解析

5.Spring MVC源码解

最后

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

8a982.png)

2.Spring源码解析

3.MyBatis源码解析

4.Dubbo源码解析

5.Spring MVC源码解

最后

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

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