MySQL面试题补

内连接和外连接的区别：

○1.功能和用法不同：内连接是连接两表都满足情况的数据；而外连接是以一边的表为主表，另一个表只显示匹配的行；
○2.用途：内连接一般是用于检索不同表需要根据共同的列值进行匹配的；而外连接要保留一个表的数据，其他表需要查询匹配的记录；
○3.性能：内连接的性能要优于外连接；因为内连接处理的记录少；

on、where、having区别

on是用于表连接的筛选条件，实在表进行连接时起作用； where是用于将结果集根据条件进行过滤操作；无法跟随聚合函数，用在将表查询完成之后； having是对分组之后的结果集进行过滤操作，可以使用聚合函数；

什么是笛卡尔积

在数据库中就是连接表是没有连接条件，导致返回的结果集是所有的数据；

链表查询有哪些优化操作注意事项？

1.对于连接条件可以使用合适的索引；

2.可以对查询字段使用索引，尽量不要使用select ，内存放不下；

3.小表驱动大表原理：让数据量较少的表作为驱动表去连接数据量大的表，以减少查询过程中需要扫描的数据量；

MySQL内存缓存:

MySQL内存缓存是一种机制，用于存储查询结果并加速读取操作。通过将完整的查询语句及其结果存储在内存中，MySQL内存缓存可以在下次相同查询请求时直接返回缓存的结果，而不必再次执行查询过程。这样可以减少对磁盘和数据库引擎的访问，提高响应速度和整体性能。
在MySQL中，有两种常见的内存缓存机制：
1.查询缓存（Query Cache）：这是MySQL提供的一种内置缓存机制，用于缓存查询结果。当执行一个查询语句时，MySQL会先检查查询缓存中是否有对应的结果。如果有，则直接返回缓存结果；否则，执行查询并将结果存入缓存。查询缓存可以避免多次执行相同查询造成的性能损耗。但请注意，从MySQL 8.0版本开始，查询缓存功能已被弃用。
2.InnoDB缓冲池（Buffer Pool）：对于使用InnoDB存储引擎的MySQL数据库，InnoDB缓冲池是一种重要的内存缓存机制。它用于缓存数据和索引，以便更快地访问这些数据。当数据库需要读取或写入数据时，它首先会尝试在缓冲池中找到这些数据。如果找到了，就可以直接在内存中完成操作，而无需访问磁盘。这样可以大大提高数据库的性能。

连表查询的底层原理？

首先说明驱动表（主表）和非驱动表（从表）
内连接：一般是通过查询优化器决定的；
左连接：一般是左边的表为主表，右边的表为从表；
右连接：一般是右边的表为主表，左边的表为从表；
了解了驱动表和被驱动表以后，现在我们看下MySQL究竟是怎么做join查询的。
简单嵌套循环连接（当然MySQL默认没有采用这种算法。） 简单嵌套循环连接（Simple Nested-Loop join）是从驱动表A中取出一条数据，遍历表B，将匹配到的数据放到result.. 以此类推, 如下图所示：

算法简单粗暴，比如驱动表A有10条，被驱动表B有100条，那么扫描次数是A+A*B, 每一次扫描其实就是从硬盘中读取数据加载到内存中,也就是一次IO，而IO是最大的瓶颈，所以效率低下。

块嵌套循环连接（引入了join buffer） 块嵌套循环连接(Block Nested-Loop Join)是对上面一种算法的优化，引入了join buffer缓冲区，将驱动表join相关的部分数据列、缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。

索索引嵌套循环连接
索引嵌套循环连接(Index Nested-Loop Join)就是效率最高的，前提条件是被驱动表的关联字段建立了索引。通过驱动表匹配条件直接与被驱动表的索引进行匹配，避免和内存表的每条记录去进行比较，这样极大的减少了对内存表的匹配次数。

Hash Join(MySQL 8) 从MySQL8后面的版本开始废弃块嵌套循环连接，默认使用了Hash Join的方式。
Hash Join算法利用哈希表来实现和加速数据库中的join操作。Hash Join的基本原理是将其中一个表（通常称为“构建表”）加载到内存中，并生成哈希表。然后，对另一个表（通常称为“扫描表”）进行扫描，匹配哈希表中的数据，最终得到连接结果。这种方式在当其中一个表较小或者可以完全缓存到内存中时，效率较高。Hash Join只能应用于等值连接，这是由Hash的特点决定的。

MySQL的数据是如何存储到磁盘的？

MySQL中的每个数据库都存储为文件系统上的目录，每个表存储为一个单独文件，mysql存储在磁盘中不是以行数据为单位的，而是以数据页为单位的，一页数据默认是16KB。存储多行数据，数据之间通过双向链表进行连接，页之间也通过链表进行连接。
行溢出：一个页的大小就是16KB，大于这个值就是行溢出；
数据页：一个数据页的上限是16K；

表到底是什么概念？

表（Table）是数据库的核心组件之一，用于存储和组织数据。表是一个二维的数据结构，由行（Row）和列（Column）组成。每一列代表一个特定的数据类型（如整数、字符串、日期等），而每一行则包含了一组与这些列相对应的数据值。
逻辑上是表，物理上是什么？
物理上实际就是数据页；

ACID事务特性以及隔离级别？

原子性：
事务是一个不可分割的工作单元，事务中的操作要么全部发生，要么全部不发生；如果一部分成功，一部分失败，那么就进行回滚操作；
实现：原子性依靠undolog回滚日志实现，每次对数据进行修改和删除插入的操作都会生成一条undolog来记录操作之前的数据状态，使用rollback将执行的sql语句效果进行撤销操作；
一致性：
事务前后的数据完整性一致；
实现：依靠的是其他三个特性实现的；
隔离性：
保证事务不被其他事务的并发操作环境干扰，多个并发事务之间要相互隔离；
实现：通过锁机制实现的，事务对数据操作的时候对数据加锁，保证事务操作数据的前后看到的数据是一致的；
持久性：
指的是事务一旦被提交，那么他对数据的改变就是永久的；
实现：通过redo log实现，redo log记录的是对数据库的操作；

OLTP与OLAP的区别？

oltp（联机事务处理）：是传统的关系型数据库的主要应用，用于基本的、日常的事务处理，例如银行的交易记录；
olap(联机分析处理)：是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供了直观易懂的查询结果，。常见的应用是复杂的动态报表系统；
总体来说，OLTP用于日常处理、OLAP用于数据分析。

DML是什么呢？

dml是数据操作语言，用于检索或者修改数据，我们平常所说的增删改查就属于dml；

ddl是什么？

ddl是数据定义语言，用于操作数据结构，例如创建表，删除表，更改索引等；

DCL是什么？

数据控制语言，用于定义数据库用户的权限，比如创建用户，授权用户，删除用户等都是DCL。

能说说varchar与char的区别是什么吗？

char是一种固定长度的类型，varchar则是一种可变长度的类型。比如char(128)和varchar(128)，前者无论字符串长短，在磁盘上，都会占据固定的128字符大小。后者是可变长度，不过它最大也不能超过128。

既然varchar是变长，那是不是设置varchar（1000）一定比varchar（100）好？

不是这样的。虽然varchar是变长，在相同长度下，磁盘空间占用一样，将值设置更大一些，弹性空间也更大。但也不是完全没有代价的
在内存加载的时候，每次都是按最大空间来分配的。显然，在排序场景，或者一些临时表聚合场景，更大空间会产生明显的不利影响。

varchar是变长，char是定长，那能用varchar完全代替char么？

varchar的优点是更灵活。但是char也不是一无是处的。
首先，varchar会额外用一个字节存储长度信息，而char则节约了一个字节；
其次，char的存储空间都是一次性分配的，存储是固定连续的，而varchar的存储的长度是可变的，当varchar更改前后数据长度不一致时，就不可避免的会出现碎片的问题。针对此，需要进行碎片消除作业，也是额外的成本。
一般来说，长度固定的字段，还是用char比较合适，比如Hash，就很适合用char。

varchar(11)和int(11)中的50，有什么区别？

varcahr中代表能存11个字符，int中只是代表显示长度，对大多数应用没有意义，只是规定一些工具用来显示字符的个数，比如int(1)和int(20)存储和计算其实是一样的。

接下来能说说delete和truncate的区别吗？

delete是删除行；truncate是整表删除。具体来说，有下面几点区别：
1.truncate之后，会释放空间；delete之后，不会释放空间，因为delete只是在行上标记删除，后续可以复用；
2.delete因为是DML，会产生redo log；truncate是DDL则不会；
3.truncate效率更高；
4.truncate之后，id从头开始；delete不会。

你知道MySQL有哪些存储引擎吗？

首先有Innodb引擎，它提供了对数据库ACID事务的支持，并且还提供了行级锁和外键的约束。Innodb的设计目标就是处理大数据容量的数据库系统； 还有MyIASM引擎，它是原本Mysql的默认引擎，不提供事务的支持，也不支持行级锁和外键； 最后还有一个MEMORY引擎，它的所有数据都在内存中，数据的处理速度快，但是安全性不高，很少使用。 那么ACID是什么呢？ 它是原子性、一致性、隔离性和持久性的缩写。

主键和外键分别是什么？

主键是表中的一个或多个字段，它的值用于唯一的标识表中的某一条记录。
外键是说某张表b的主键，在另一张表a中被使用，那么a中该字段可以使用的范围，取决于b。外键约束主要用来维护两个表之间数据的一致性。

那么一张表一定有主键吗？

是的。一定有。如果主动设置，则采用设置的。否则会自动生成一个默认的行。

你怎么查看有多少个Sql语句在执行？

可以用show processlist，它是显示用户正在运行的线程的命令。需要注意的是，用户都只能看到自己正在运行的线程，除非是root用户，或者专门进行了授权的用户。