MySQL 查询（关键字）

关键字执行过程

Snipaste_2022-09-13_20-52-04

  1. FROM/JOIN/ON
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT（窗口函数即在此步骤执行）
  6. ORDER BY
  7. LIMIT

疑问：

  • 可以对 GROUP BY 的结果进行WHERE 筛选吗？（不可以！因为 WHERE 在 GROUP BY 之前执行）
  • 可以对窗口函数的执行结果进行过滤吗？（不可以！因为窗口函数在 SELECT 步骤执行，而这步是在 WHERE 和 GROUP BY 之后）
  • 可以对 GROUP BY 的结果再执行 ORDER BY 操作吗? （可以！ORDER BY 基本上是最后一个步骤了，所以可以对任何操作的执行结果执行 ORDER BY)
  • LIMIT 执行在哪个步骤? （最后一步！）

select

举个例子，比如执行器可能会先调用存储引擎的一个接口，去获取“users”表中的第一行数据，然后判断一下这个数据的”id”字段的值是否等于我们期望的一个值，如果不是的话，那就继续调用存储引擎的接口，去获取“users”表的下一行数据。

查询完毕之后会将查询发送到客户端，如果整个查询是可以被缓存的，那么就会缓存下来。

这里返回数据是逐步返回的，生成第一条结果的时候就能返回了。

优点: 服务器端无需存储太多的结果。也就不会因为要返回太多结果而消耗太多内存

count

count(*)

在 MySQL 5.7.18 之前，通过扫描聚集索引来InnoDB处理 语句

从 MySQL 5.7.18 开始， 通过遍历最小的可用二级索引来InnoDB处理

SELECT COUNT(*) FROM t1;

在执行上述查询时，server层会维护一个名叫count的变量，然后：

  1. server层向InnoDB要第一条记录。
  2. InnoDB找到idx_key1的第一条二级索引记录，并返回给server层（注意：由于此时只是统 计 记录数量，所以并不需要回表）。
  3. 由于COUNT函数的参数是 * ，MySQL会将 * 当作常数0处理。由于0并不是NULL，server层 给count变量加1。
  4. server层向InnoDB要下一条记录。
  5. InnoDB通过二级索引记录的next_record属性找到下一条二级索引记录，并返回给server层。 server层继续给count变量加1。
  6. … 重复上述过程，直到InnoDB向server层返回没记录可查的消息。
  7. server层将最终的count变量的值发送到客户端。

count(1)

count(1) 和count(*) 执行查询结果一样，最终也是返回一百万条数据，无论它们是否包含 NULL值

count(col)

使用索引字段统计行数能够命中索引，并且只统计不为NULL值的行数。

limit

MySQL的limit基本用法很简单。limit接收1或2个整数型参数，如果是2个参数，第一个是指定第一个返回记录行的偏移量，第二个是返回记录行的最大数目。初始记录行的偏移量是0。

SELECT * FROM t ORDER BY key1 LIMIT 5000, 1;

如果使用idx_key1执行上述查询，那么MySQL会这样处理：

  • server层向InnoDB要第1条记录，InnoDB从idx_key1中获取到第一条二级索引记录，然后进行回表操作得到完整的聚簇索引记录，然后返回给server层。server层准备将其发送给客户端，此时发现还有个LIMIT 5000, 1的要求，意味着符合条件的记录中的第5001条才可以真正发送给客户端，所以在这里先做个统计，我们假设server层维护了一个称作limit_count的变量用于统计已经跳过了多少条记录，此时就应该将limit_count设置为1。
  • server层再向InnoDB要下一条记录，InnoDB再根据二级索引记录的next_record属性找到下一条二级索引记录，再次进行回表得到完整的聚簇索引记录返回给server层。server层在将其发送给客户端的时候发现limit_count才是1，所以就放弃发送到客户端的操作，将limit_count加1，此时limit_count变为了2。
  • … 重复上述操作
  • 直到limit_count等于5000的时候，server层才会真正的将InnoDB返回的完整聚簇索引记录发送给客户端。
select * from user limit 0,100 ---------耗时0.03s
select * from user limit 10000,100 ---------耗时0.05s
select * from user limit 100000,100 ---------耗时0.13s
select * from user limit 500000,100 ---------耗时0.23s
select * from user limit 1000000,100 ---------耗时0.50s
select * from user limit 1800000,100 ---------耗时0.98s

深翻页优化

用id优化

先找到上次分页的最大ID,然后利用id上的索引来查询,类似于select * from user where id>1000000 limit 100.

这样的效率非常快,因为主键上是有索引的,但是这样有个缺点,就是ID必须是连续的,并且查询不能有where语句,因为where语句会造成过滤数据.

用覆盖索引优化

mysql的查询完全命中索引的时候,称为覆盖索引,是非常快的,因为查询只需要在索引上进行查找,之后可以直接返回,而不用再回数据表拿数据.因此我们可以先查出索引的ID,然后根据Id拿数据.

select * from (select id from job limit 1000000,100) a left join job b on a.id = b.id;

耗时0.2秒.

order by

全字段排序

Snipaste_2022-09-13_21-26-05

Extra 这个字段中的“Using filesort”表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

为了说明这个 SQL 查询语句的执行过程，我们先看一下 uid 这个索引的示意图。如下图所示：

Snipaste_2022-09-13_23-06-50

通常情况下，这个语句执行流程如下 ：

  1. 初始化 sort_buffer，确定放入 oid、price、uid 这三个字段；
  2. 从索引 uid 找到第一个满足 uid = 1 条件的主键 id，也就是图中的 ID-4；
  3. 到主键 id 索引取出整行，取 oid、price、uid 三个字段的值，存入 sort_buffer 中；
  4. 从索引 uid 取下一个记录的主键 id；
  5. 重复步骤 3、4 直到 uid 的值不满足查询条件为止；
  6. 对 sort_buffer 中的数据按照字段 oid 做快速排序；
  7. 按照排序结果取前 1000 行返回给客户端。

sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

rowid 排序

如果 MySQL 认为排序的单行长度太大会怎么做呢？

SET max_length_for_sort_data = 16;

max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。

  1. 初始化 sort_buffer，确定放入两个字段，即 price 和 id；
  2. 从索引 uid 找到第一个满足 uid= 1 条件的主键 id；
  3. 到主键 id 索引取出整行，取 price、id 这两个字段，存入 sort_buffer 中；
  4. 从索引 uid 取下一个记录的主键 id；
  5. 重复步骤 3、4 直到不满足 uid= 1 条件为止；
  6. 对 sort_buffer 中的数据按照字段 price 进行排序；
  7. 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 oid、price 2个字段返回给客户端。

    MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

MySQL 之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。

优化

  1. 因为数据是无序的，所以就需要排序。如果数据本身是有序的，那就不用排了。而索引数据本身是有序的，我们通过建立联合索引，优化 order by 语句

  2. 调整max_length_for_sort_data等参数优化

ALTER TABLE my_order ADD INDEX un_key (uid,price);

Snipaste_2022-09-13_23-12-59

在这个索引里面，我们依然可以用树搜索的方式定位到第一个满足 uid=1 的记录，并且额外确保了，接下来按顺序取“下一条记录”的遍历过程中，只要 uid 的值是 1，price 的值就一定是有序的。

这样整个查询过程的流程就变成了：

  1. 从索引 (uid,price) 找到第一个满足 city= 1 条件的主键 id；
  2. 到主键 id 索引取出整行，取 oid、price 2个字段的值，作为结果集的一部分直接返回；
  3. 从索引 (uid,price) 取下一个记录主键 id；
  4. 重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 uid=1 条件时循环结束。

这个查询过程不需要临时表，也不需要排序。接下来，我们用 EXPLAIN 的结果来印证一下。

Snipaste_2022-09-13_23-14-12

group by

实现方式有三种

索引名称 性能
松散索引 最好
紧凑索引 第二
临时文件（文件排序） 最差

语句例子：

EXPLAIN SELECT viewed_user_age as age, count(*) as num 
FROM t_user_view 
WHERE user_id = 1 
    AND viewed_user_age 
    BETWEEN 18 AND 22 
    AND viewed_user_sex = 1 
GROUP BY viewed_user_age

groupBy语句分别经历了3个执行阶段：

  • Using where：通过搜索可能的 idx_user_viewed_user 索引树定位到满足部分条件的 viewed_user_id，然后，回表继续查找满足其他条件的记录
  • Using temporary：使用临时表暂存待 groupBy 分组及统计字段信息
  • Using filesort：使用 sort_buffer 对分组字段进行排序

group by会默认排序，如果不希望排序，可以显示指定不排序。

explain select city ,count(*) as num from staff group by city order by null;

1. 松散索引

server 层直接从存储引擎读取到每个分组中聚合函数需要的那条记录，而不必读取每个分组中的所有记录进行聚合函数处理

从存储引擎读取分组记录时，会跳着着读，直接通过分组前缀（group by 字段的值）定位到分组中符合 where 条件的第一条或最后一条记录，而不需要读取分组的所有记录，可以减少 select 语句执行过程中需要读取的记录数，从而比紧凑索引扫描执行更快（有例外情况）

Snipaste_2022-09-14_11-39-29

2. 紧凑索引

  • 存储引擎按顺序一条一条读取记录，返回给 server 层。
  • server 层判断记录是否符合 where 条件。
  • server 层对符合条件的记录进行聚合函数逻辑处理。

group by 字段包含在索引中，并且满足索引最左匹配原则，server 层就可以顺序读取索引中的记录实现 group by，而不需要借助临时表。

Snipaste_2022-09-14_11-32-21

3. 临时文件

当无法找到合适的索引可以利用的时候，就不得不先读取需要的数据，然后通过临时表来完成 GROUP BY 操作。 Extra：Using temporary; Using filesort；

优化

原理就是让mysql利用索引，而避免进行建立临时表，进而进行文件排序（group by的第三种实现方式）。

join

join有三种算法，分别是Nested Loop Join，Hash join，Sort Merge Join。

1、Simple Nested Loop Join（SNLJ）

# 伪代码
for (r in R) { # 扫描R表（驱动表）
    for (s in S) {  # 扫描S表（被驱动表）
        if (r satisfy condition s) {   # 如果r和s满足join条件
            output <r, s>; # 返回结果集
        }
    }
}

Snipaste_2022-09-14_15-47-57

SNLJ就是两层循环全量扫描连接的两张表，得到符合条件的两条记录则输出，这也就是让两张表做笛卡尔积，比较次数是R * S，是比较暴力的算法，会比较耗时。

2、Index Nested Loop Join（INLJ）

# 伪代码
for (r in R) { # 扫描R表（驱动表）
    for (si in SIndex) { # 查询 Si 表的索引（固定3~4次IO，B+树高度）
        if (r satisfy condition si) {  # 如果 r 匹配了索引si
            output <r, s>; # 返回结果集
        }
    }
}

Snipaste_2022-09-14_15-46-51

INLJ是在SNLJ的基础上做了优化，通过连接条件确定可用的索引，在Inner Loop中扫描索引而不去扫描数据本身，从而提高Inner Loop的效率。 而INLJ也有缺点，就是如果扫描的索引是非聚簇索引，并且需要访问非索引的数据，会产生一个回表读取数据的操作，这就多了一次随机的I/O操作。

3、Block Nested Loop Join（BNLJ）

一般情况下，MySQL 优化器在索引可用的情况下，会优先选择使用 INLJ 算法，但是在无索引可用，或者判断 full scan 可能比使用索引更快的情况下，还是不会选择使用过于粗暴的 SNLJ 算法。 这里就出现了 BNLJ 算法了，BNLJ 在 SNLJ 的基础上使用了 join buffer，会提前读取 Inner Loop 所需要的记录到 buffer 中，以提高 Inner Loop 的效率。

# 伪代码
# for 驱动表
for (r in R) { # 扫描 R 表（被驱动表）
    for (sbu in SBuffer) {  # 将部分或者全部外部表 S 的记录保存到 Join Buffer 中，记为 SBuffer
        if (r satisfy condition sbu) {  # r 与 sbu 满足 join 条件
            output <r, s>; # 返回结果集
        }
    }
}

Snipaste_2022-09-14_15-45-15

总结

优先级：
Index Nested-LoopJoin -> Block Nested-Loop Join -> Simple Nested-Loop Join

当不使用 Index Nested-Loop Join 的时候，默认使用 Block Nested-Loop Join。

优化：

  1. 小表驱动大表，可以减少循环嵌套次数。
  2. 为匹配的条件增加索引：争取使用 Index Nested-Loop Join，减少内层表的循环次数
  3. 增大 join_buffer_size: 256KB
  4. 当用到 Block Nested-Loop Join时，字段越少，join buffer 所缓存的数据就越多，外层表的循环次数就越少；