mysql笔记四（sql查询二）

二 关键词使用常见问题：
什么是笛卡尔积？
‌笛卡尔积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。例如，如果有两个集合A={a,b}和B={1,2}，那么它们的笛卡尔积为{(a,1),(a,2),(b,1),(b,2)}。

1 select 查询结果集；
select * from table 查询表中所有字段，只取需要用到的字段；避免查询过多字段占用内存；
select tab.name as name from table as tab 使用as作为别名；
select 后面使用函数，子查询。

2 内部执行过程？
一条sql的大概执行过程：

https://blog.youkuaiyun.com/wangzhongyu_/article/details/117574400?spm=1001.2014.3001.5501

3 关联表优化；join 执行原理
驱动表：首先被访问的表；
被驱动表：与驱动表匹配的表成为被驱动表。

通常情况下是这样使用的：
LEFT JOIN：在左连接中，左边的表通常作为驱动表。
RIGHT JOIN：在右连接中，右边的表作为驱动表。
INNER JOIN：对于内连接，MySQL会自动选择数据量较小的表作为驱动表。

explain查看执行过程：
1 Block Nested-Loop Join；
mysql使用了一个叫join buffer的缓冲区去减少循环次数，这个缓冲区默认是256KB，可以通过命令show variables like 'join_%'查看
其具体的做法是，将第一表中符合条件的列一次性查询到缓冲区中，然后遍历一次第二个表，并逐一和缓冲区的所有值比较，将比较结果加入结果集中。
优化：增大join buffer size的大小（因为一次缓存的数据越多，那么外层表循环的次数就越少）。

2 Nested Loop Join；
Nested Loop Join（NLJ）先遍历表驱动表，然后根据驱动表中取出的每行数据中的值，去被驱动表中查询满足条件的记录。
注意：具体哪个是驱动表是mysql自动优化和选择；
优化点：on后跟的字段加索引；小表驱动大表；减少驱动表数据量；

注意：具体哪个是驱动表是mysql会自动优化和选择；
为了优化join算法采用Index nested-loop join算法，在连接字段上建立索引字段，注意索引失效的情况；

4 on和where的区别？

select * from table1  t1 left join table2 t2 on t1.id = t2.id where t1.name = '小明'
select * from table1  t1 left join table2 t2 on t1.id = t2.id on t1.name = '小明'

执行顺序和作用范围不同：先执行on，on条件是满足关联条件才会放到结果集中，where用于过滤连接后（满足on后面条件）的结果集。
注：如果是只过滤被驱动表过滤，那必须放在on后；
right join或left join，条件放在on后面和where后面，可能导致不同的结果集；inner join 条件放在on和where中结果是一样的。

5 in,exist区别；
in会走索引；在mysql中，超过1000不会报错，但也是有数据量限制的，但不建议数据集超过1000，但in中数据量过大索引就会失效。并且in and not in 并不是全量值，排除了null值。
Exists查询仅在内部表上可以使用到索引。当子查询结果集很大，而外部表较小的时候，Exists的Block Nested Loop(Block 嵌套循环)的作用开始显现，并弥补外部表无法用到索引的缺陷，查询效率会优于IN。

6 in和or区别？
IN操作符适用于在一个条件中指定多个值，适用于固定的值列表。
OR操作符适用于在多个条件之间添加逻辑关系，适用于动态的条件组合。
有些场景下，可以达到相同的结果，但是两者性能不同，一般是使用in性能更好。

7 between and 和大于小于
原理一样，执行效率一样，between and相当于>= and <=。
查询某个范围内的值时使用between and。

8 between and 和in
有些业务可以使用2种方式实现，从基本的效率角度来看，BETWEEN AND语句在执行计划中显示的成本通常低于IN语句。使用时要具体分析。

9 去重方式有哪些？
DISTINCT用于去除所有重复的行；

SELECT DISTINCT name，age FROM students;

groupby 去重根据指定列分组，只保留每组一行；

SELECT first_name, last_name FROM students GROUP BY first_name, last_name;

union去重，由于 Union 操作会去除重复记录并进行排序，所以它的执行效率相对较低；
union all与distinct，合并结果集并去除重复项；

SELECT first_name FROM users WHERE first_name = 'John' OR first_name = 'Jane';

还有窗口函数，子查询，临时表方式。

GROUP BY和DISTINCT比较：
使用场景‌：如果只需要去除重复的行，则使用DISTINCT；如果需要对数据进行分组并进行聚合计算（如SUM、COUNT、AVG等），则使用GROUP BY。
性能比较：索引的情况下：group by和distinct都能使用索引，效率相同。无索引的情况下：distinct效率高于group by。原因是distinct 和 group by都会进行分组操作，但group by可能会进行排序，触发filesort，导致sql执行效率低下（mysql8.0之后优化了，性能一样）。
重复量多时，GROUP BY总的处理效率可能比DISTINCT高；

10 limit的2种方式;
limit 10 取多少条
limit 0,10 从0位开始取10条；
limit 10 offset 10 从0位开始取10条；
LIMIT 0,10和LIMIT 10 OFFSET 0是等价的。
数据量大时查询慢；
1 如果id自增，先根据id过滤数据再查出数据，select xx from table where id > xx limit 0,10 ；
2 覆盖索引，子查询先查主键，再根据主键查数据；

SELECT * FROM table_name a INNER JOIN (SELECT id FROM table_name LIMIT 3400000,20) b USING (id);

11 or的使用会走索引吗？
如果多个条件字段都有索引，可能会走索引，如果多个条件不同还中有函数之类的不会走索引，通过看执行计划分析；
优化：条件字段加索引；使用UNION代替OR，分别对每个条件进行查询，然后将结果合并；还可以使用in代替or，如改为where type in (1,2,3)。

12 常用函数和groupby
group by的常规用法是配合聚合函数，利用分组信息进行统计。
count()，返回指定列中数据的个数
sum()，返回指定列中数据的总和
avg()，返回指定列中数据的平均值
min()，返回指定列中数据的最小值
max()，返回指定列中数据的最大值
执行原理mysql5.7：
没有索引，会使用内存临时表存select中的数据，然后对数据进行默认排序；
优化：加索引；如果字段不能加索引，在sql中加上order by null就不会排序了。

注：
Using temporary; 表示使用了临时表；
Using filesort ，表示需要排序。
mysql8.0之后去掉了自动排序。

13 groupby和having;
having后面加条件，根据条件对分组后的数据过滤；
having 在 select 语句里与 group by联合使用时，having限制的是组，而不是行。having子句聚合函数计算的结果可以当条件来使用，where子句中不能使用聚集函数。

14 子查询？
子查询是嵌套在另一个 SELECT, INSERT, UPDATE, 或 DELETE查询的 SQL 查询。子查询的结果作为条件或结果。
14.1 使用位置：select，from，where，exist，having中；

14.2 根据子查询结果分类：
标量子查询指返回单个值（一个结果）的子查询。通常用于 SELECT 列表或者 WHERE 子句中；
多行子查询是指返回多行数据的子查询。通常与 IN、ANY 或 ALL 等运算符结合使用；
表子查询是指返回一整张表的结果集，通常用于 FROM 子句中。表子查询允许你将一个子查询的结果作为临时表来使用；

14.3根据内外查询关系：
相关子查询：是指子查询依赖于外层查询的某些列；
非相关子查询：是指子查询与外层查询无关，独立执行并返回结果；

关注执行顺序，避免多次执行子查询：尽量使用非相关子查询。
非相关子查询执行过程：
a:单独执行子查询；
b:将子查询结果作为外层查询参数，并执行外层查询。
相关子查询执行过程:
a:先从外查询中取一条记录；
b:从外查询中取出子查询涉及到的值，并执行子查询；
c:子查询的结果与where做匹配，成立则记录加入结果集，否则丢弃；
d:重复执行步骤a。

子查询使用优化：
使用关联子查询替代子查询；
使用EXISTS替代IN子查询，EXISTS比IN子查询更有效率，因为它在找到第一个匹配项后就停止搜索；
确保子查询中涉及的列有适当的索引；
使用临时表；

15 order by
order by中的列已经被索引，使用索引最左前列，避免出现filesort；
explain观察查询类型和索引使用情况。