exist和in的区别？

exists用于对外表记录做筛选。exists会遍历外表，将外查询表的每一行，代入内查询进行判断。当exists里的条件语句能够返回记录行时，条件就为真，返回外表当前记录。反之如果exists里的条件语句不能返回记录行，条件为假，则外表当前记录被丢弃。

select a.* from A awhere exists(select 1 from B b where a.id=b.id)

in是先把后边的语句查出来放到临时表中，然后遍历临时表，将临时表的每一行，代入外查询去查找。

select * from Awhere id in(select id from B)

• EXISTS 一旦找到匹配行就会立即返回，通常在子查询返回较大数据集时性能更好。
• IN 通常会评估整个子查询并构建一个值列表，然后再进行匹配，在处理较大数据集时可能性能较差。

truncate、delete与drop区别？

• TRUNCATE：快速清空表，但保留结构。适用于需要清空但不删除表的场景。
• DELETE：逐行删除数据，可以选择性删除某些记录。适用于只想删除部分数据的场合，且支持事务。
• DROP：删除整个表定义及其所有数据。适用于不再需要表结构或其数据的情况。

相同点：

1. truncate和不带where子句的delete、以及drop都会删除表内的数据。
2. drop、truncate都是DDL语句（数据定义语言），执行后会自动提交。

不同点：

1. truncate 和 delete 只删除数据不删除表的结构；drop 语句将删除表的结构被依赖的约束、触发器、索引；
2. 一般来说，执行速度: drop > truncate > delete。

having和where区别？

• 二者作用的对象不同，where子句作用于表和视图，having作用于组。
• where在数据分组前进行过滤，having在数据分组后进行过滤。

count(*) 和 count(1)哪个快?

按照性能排序是：count(*) = count(1) > count(主键字段) > count(字段)

1. count(主键字段)的执行过程：

比如说，id是主键字段。

• 如果表里只有主键索引，那么，InnoDB 循环遍历聚簇索引，将读取到的记录返回给 server 层，然后读取记录中的 id 值，并根据 id 值判断是否为 NULL，如果不为 NULL，就将 count 变量加 1。
• 如果表里有二级索引时，InnoDB 循环遍历的对象就不是聚簇索引，而是二级索引。因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间，所以二级索引树比聚簇索引树小，这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小，因此「优化器」优先选择的是二级索引。

2. count(1) 的执行过程：

如果表里只有主键索引，没有二级索引时。那么，InnoDB 循环遍历聚簇索引（主键索引），将读取到的记录返回给 server 层，但是不会读取记录中的任何字段的值，因为 count 函数的参数是 1，不是字段，所以不需要读取记录中的字段值。参数 1 很明显并不是 NULL，因此 server 层每从 InnoDB 读取到一条记录，就将 count 变量加 1。

显然，count(1) 相比 count(主键字段) 少一个步骤，就是不需要读取记录中的字段值，所以通常会说 count(1) 执行效率会比 count(主键字段) 高一点。

但是，如果表里有二级索引时，InnoDB 循环遍历的对象就二级索引了。

3. count(*) 的执行过程(mysql官方文档推荐)：

count(*) 其实等于 count(0)，也就是说，当你使用 count(*) 时，MySQL 会将 * 参数转化为参数 0 来处理。

所以，count(*) 执行过程跟 count(1) 执行过程基本一样的，性能没有什么差异。

4. count(字段) 的执行过程：采用全表扫描的方式来统计

MySQL多表查询时有哪些连接方式

当进行多表查询时，在 MySQL 中常用的连接方式有以下几种：

• 内连接（INNER JOIN）：返回同时满足连接条件的行。它通过比较连接列的值，将两个或多个表中匹配的行组合在一起。
• 左外连接（LEFT JOIN）：返回左表中的所有行，以及与左表匹配的右表的行。如果右表中没有匹配的行，对应的列将填充为 NULL。
• 右外连接（RIGHT JOIN）：返回右表中的所有行，以及与右表匹配的左表的行。如果左表中没有匹配的行，对应的列将填充为 NULL。
• 自连接（Self JOIN）：将单个表视为两个独立的表，使用别名来引用同一个表。这种连接适用于在同一个表中根据某些条件关联不同的行。
• 交叉连接（CROSS JOIN）：返回两个表的笛卡尔积，即所有可能的组合。它将第一个表的每一行与第二个表的每一行进行组合。

还有一个是全外连接（FULL JOIN）：返回左右两个表中的所有行。如果某个表中没有匹配的行，对应的列将填充为 NULL。需要注意 MySQL 不支持 FULL JOIN 可以使用UNION ALL 模拟。

为什么大厂不建议使用多表join

最主要的原因就是join的效率比较低：MySQL是使用了嵌套循环（Nested-Loop Join）的方式来实现关联查询的，就是要通过两层循环，用第一张表做外循环，第二张表做内循环，外循环的每一条记录跟内循环中的记录作比较，符合条件的就输出。

1. 性能问题：
   • 多表 JOIN 会增加查询的复杂性，可能导致性能下降，特别是在数据量大时。
   • 数据库需要在执行查询时处理更多的行和列，这可能导致更高的 I/O 操作和内存使用。
2. 可读性和维护性：
   • 复杂的 JOIN 查询会使 SQL 语句变得难以理解，导致维护成本增加。
   • 当查询需要频繁修改时，复杂的 JOIN 会让代码更容易出错。
3. 索引利用率：
   • 多表 JOIN 可能会导致数据库无法有效利用索引，影响查询的优化。
   • 如果 JOIN 的字段没有适当的索引，查询性能会显著下降。
4. 锁竞争：
   • 多表 JOIN 可能导致更长时间的行锁或表锁，从而增加锁竞争的可能性，影响并发性能。
5. 数据完整性：
   • 复杂的 JOIN 查询可能掩盖数据问题或不一致性，使得调试较为困难。
   • 难以确保在 JOIN 查询中返回的数据符合业务逻辑和数据完整性要求。

那应该怎么写？

• 分解查询：在内存中自己做关联，即先从数据库中把数据查出来之后，再次查询，然后再进行数据封装。
• 考虑数据冗余：在某些情况下，可以考虑数据冗余来减少 JOIN 的需要。
• 数据冗余，宽表：就是基于一定的join关系，把数据库中多张表的数据打平做一张大宽表，可以同步到ES或者干脆直接在数据库中直接查都可以

UNION 与UNION ALL 的区别？

UNION和UNION ALL是在SQL中用于合并查询结果集的操作符，它们之间存在以下区别：

• UNION：UNION用于合并两个或多个查询结果集，并去除重复的行。它将多个查询的结果合并为一个结果集，并自动去除重复的行。在执行UNION操作时，数据库会进行额外的去重操作，这可能会带来一定的性能开销。
• UNION ALL：UNION ALL同样用于合并查询结果集，但不去除重复的行。它将多个查询的结果简单地合并在一起，包括重复的行。相比于UNION，UNION ALL不进行去重操作，因此执行效率更高。

总结来说：在使用时，可以根据具体的需求来选择合适的操作符。如果需要去除重复的行，可以使用UNION；如果不需要去重，或者对性能要求较高，可以使用UNION ALL。需要注意的是，使用UNION或UNION ALL时，要求被合并的查询结果的列数和列类型保持一致。

where 1 = 1的作用？会影响性能吗？

WHERE 1=1 表达式的值始终为 TRUE，因此它不会对查询结果产生任何实际的限制或筛选作用。它的主要作用是为条件语句提供一个基础条件，方便在构建动态SQL时，后续的查询条件可以更加灵活地拼接。

对于性能而言，WHERE 1=1 表达式并不会造成明显的性能损失。数据库优化器会意识到 1=1 永远为 TRUE，因此它不会对查询的执行计划产生任何实际影响。WHERE 1=1 在执行时不会进行额外的计算，因为它本质上是一个常量布尔值。

• 查询优化：MySQL数据库会在查询时自动优化查询条件，对于 WHERE 1=1 这样的条件，它不会引入额外的计算负担。优化器通常会跳过 1=1，并且直接处理剩下的查询条件。
• 执行计划：在 MySQL 的查询执行计划中，WHERE 1=1 通常不会出现在最终的执行过程中。优化器会识别到它的无用性，并忽略它对查询结果的影响。
• 影响微乎其微：如果查询中包含大量复杂的条件，WHERE 1=1 的影响几乎可以忽略不计。它只是一个常量，任何现代数据库都能轻松处理这样简单的条件

LIMIT 100000000,10和LIMIT 10 的执行速度是否相同?

速度差很多，limit 10 快很多!

原因如下:

• LIMIT 100000000,10 需要先处理(通常是读取并跳过)前 100000000 条记录，然后再获取到需要的 10条记录，开销或本很大，因为需要扫描 100000000 数据才能得到后面的10条教据，会导致大量的磁盘 I/O
• LIMIT 10 从结果集第一个记录开始扫描直接返回前 10 条记录。

通常面对 LIMIT 100000000,10 这种大分页的情况，可以先使用条件过滤掉，比如使用主键 ID 来进行范围过滤，然后再 limit 10。

MySQL 中的数据排序是怎么实现的?

排序过程中，如果排序字段命中索引，则利用索引排序。反之，使用文件排序。

使用索引排序：当ORDER BY子句中的列恰好有对应的索引时，MySQL可以直接利用索引来完成排序操作，这是最高效的排序方式。索引本身是有序的，所以MySQL只需要按照索引的顺序扫描即可得到排序后的结果。

文件排序(filesort)：当无法使用索引进行排序时，MySQL会使用文件排序。

• 如果数据量少，则在内存中排序。具体是使用单路或双路排序
• 如果数据量较大，超过了系统变量 sort_bufer_ size 的大小，还会使用临时文件来协助排序。一般使用归并排序

具体的排序过程如下：

1. 首先，MySQL会检查ORDER BY子句中的列是否有可用的索引。如果有可用的索引，MySQL会直接使用索引进行排序。
2. 如果没有可用的索引，MySQL会执行以下步骤:
   1. 从表中读取满足条件的所有行
   2. 对于每一行，只保存需要排序的列和可以唯一标识行的列(如主键)使用快速排序算法在内存中对这些数据进行排序
   3. 如果数据量太大，会使用临时文件来辅助排序
   4. 根据排序结果回表查询所需的列

需要注意的是，ORDER BY的使用可能会对查询性能产生显著影响。我们可以通过以下方式优化排序操作:

• 在经常需要排序的列上创建适当的索引。
• 尽量避免对大数据集进行排序。
• 如果可能，尽量使用覆盖索引，即索引包含了查询所需的所有列，这样可以避免回表操作。
• 适当调整系统变量，如增加 sort_bufer_ size 的大小。
• 考虑使用限制结果集大小的 LMIT 子句，特别是在只需要前几条记录的情况下。

总的来说，MVSQL的排序实现既考虑了效率(通过利用索引)，又保证了在没有合适索引的情况下也能完成排序(通过文件排序)。理解这些原理对于优化数据库查询性能至关重要，

什么是单路排序？双路排序？

双路排序

有一个叫 max length for_sort cata参数，默认是4096字节，如果 select列的数据长度超过它,则 MySQL采用row id排序，即把 row id(有主键就是主键)+排序字段放置到 sort bufer 中排序

比如现在要执行: select a,b,c from t1 where a='seven'order by b;

假设select 列的数据长度超过了 max_length_for_sort_data，为了节省排序占用的空间，此时 sort bufer 只会有放置 id 和b 来排序。

排序后，再通过id 回表查询得到 a、b、c，最终将最后的结果集返回给客户端。所以排序需要多个回表的过程，等于需要两次查询，也叫双路排序(Two-Pass Sort)。

单路排序

假设 select 列的数据没有超过 max length for_sortdata，则可以进行单路排序(Singile-Pass Sort)，就是将select 的字段都放置到 sort buffer 中

排序后直接得到结果集，返回给客户端即可，相比双路排序它减少了回表的动作，因此效率更高

开始 MySQL 只有双路排序，后续优化推出了单路排序。

磁盘文件临时排序

如果查询的数据超过 sort_bufer，说明内存放不下了，因此需要利用磁盘文件进行外部排序，一般会使用归并排序，简单理解就是将数据分为很多份文件，单独对文件排序，之后再合并成一个有序的大文件。利用磁盘排序效率会更低，针对一些情况可以调大sort buffer size，避免磁盘临时文件排序,

本文来自在线网站：seven的菜鸟成长之路，作者：seven，转载请注明原文链接：www.seven97.top