【MySQL】常见的SQL优化方式（二）
目录
1、limit 优化
（1）延迟关联（索引覆盖+子查询）
（2） 已知位置查询
2、group by 优化
（1）使用索引
（2）避免排序
（3）分析查询
3、count 优化
（1）常见的几种 COUNT() 用法及其效率对比
（2）性能排序
4、update 优化
（1）举例说明
（2）优化建议
1、limit 优化
当使用 LIMIT 进行大数据量分页时，比如 LIMIT 2000000, 10，MySQL 需要先从头开始排序并扫描前 2000010 条记录，但实际上我们只关心从第 2000000 到 2000010 的 10 条数据。这种操作的代价非常高，因为它需要 MySQL 扫描大量不必要的行，浪费了很多时间和资源。
（1）延迟关联（索引覆盖+子查询）
使用索引覆盖：首先，通过查询一个较小的字段（比如主键 ID），快速得到所需的 ID 列表。因为查询主键或索引列时可以直接从索引中返回，不需要访问全表数据，速度快很多。
子查询（或者JOIN）再获取完整数据：拿到 ID 列表之后，再通过 IN 子查询或 JOIN 来查询完整的数据行。这一步只查询所需的几条记录，而不是像之前那样扫描大量无关的数据。
#1、利用索引，先获取需要的主键 ID
SELECT id FROM tb_sku ORDER BY id LIMIT 2000000, 10;#2、通过子查询，查询这些 ID 对应的完整记录
EXPLAIN SELECT * FROM tb_sku t,
(SELECT id FROM tb_sku ORDER BY id LIMIT 2000000,10) a
WHERE t.id = a.id;（2） 已知位置查询
如果有一个单调递增的主键，我们可以利用这个特性来避免使用 OFFSET。比如我们知道上一页的最后一条记录的 id 是 16030，那么下一页可以直接从 id = 16030 开始查
SELECT * FROM tb_sku WHERE id > 16030 LIMIT 10; 所以，偏移量大的时候，LIMIT 查询可能会很慢。为了优化，可以借助延迟查询或者已知位置查询，这两种方式可以减少不必要的记录扫描，提升查询效率。
2、group by 优化
在写 SQL 查询时，GROUP BY 是我们经常用来对数据进行分组的操作，但它也容易成为性能的瓶颈，那怎样才可以让分组操作更快呢？
（1）使用索引
分组操作时，使用索引可以大幅提高效率。数据库通过索引可以快速定位到需要的数据，而不用去扫描整个表。和其他查询一样，GROUP BY 也遵循最左前缀法则，意思是如果我们用多个字段来分组，数据库只会使用最左边的字段上的索引来加速操作。所以，如果我们在 GROUP BY 中使用的字段是有索引的，并且顺序和索引的定义一致，就能提升查询效率。
# 执行分组操作，根据 profession 字段分组
EXPLAIN SELECT profession, COUNT(*)
FROM tb_user
GROUP BY profession;
# 创建新的联合索引
CREATE INDEX idx_user_pro_age_sta ON tb_user(profession, age, status);
# 执行分组操作，根据 profession 字段分组
EXPLAIN SELECT profession, COUNT(*)
FROM tb_user
GROUP BY profession;
# 执行分组操作，根据 profession 和 age 字段分组
EXPLAIN SELECT profession, COUNT(*)
FROM tb_user
GROUP BY profession, age;（2）避免排序
GROUP BY 默认会对分组字段进行排序。如果不需要排序，可以通过 ORDER BY NULL 来告诉数据库不需要额外排序，这样可以节省排序的开销。
SELECT profession, COUNT(*)
FROM tb_user
GROUP BY profession
ORDER BY NULL;（3）分析查询
想知道 GROUP BY 是否使用了索引？可以用 EXPLAIN 命令查看执行计划，它会告诉我们查询是如何被执行的，是否用到了索引，是否有全表扫描等信息。如果发现索引没用上，那就得考虑调整 GROUP BY 字段的顺序或添加适合的索引了。
EXPLAIN SELECT profession, COUNT(*)
FROM tb_user
GROUP BY profession| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | tb_user | index | idx_user_pro_age_sta | 10 | NULL | 20 | Using index |
分析结果：
- type：
index表示使用了索引扫描，这意味着查询是基于索引执行的，不再是全表扫描 (ALL)，因此性能提升。 - possible_keys：显示可用的索引，表明查询可以使用
idx_user_pro_age_sta。 - key：实际使用的索引是
idx_user_pro_age_sta，表明查询已经利用了我们创建的联合索引。 - rows：这里显示的是估计扫描的行数。
- Extra：
Using index表示查询直接从索引中获取数据，避免回表（从索引到表中的其他列查找），提高了效率。
3、count 优化
在 MySQL 中，COUNT() 函数经常用于统计行数，但它的效率会根据用法不同而有所差异。MyISAM 和 InnoDB 两种存储引擎在处理 COUNT(*) 时是不同的：
- MyISAM：它会直接从磁盘读取已经存储的总行数，这样当执行
COUNT(*)时，如果没有WHERE条件，可以直接返回这个数值，效率非常高。 - InnoDB：它不会像 MyISAM 那样保存行数，而是需要一行行地把数据从引擎中读出来并累加，所以在 InnoDB 中执行
COUNT(*)的效率相对低一些，尤其当有WHERE条件时，每一行都得判断。
（1）常见的几种 COUNT() 用法及其效率对比
COUNT(*)：
- 不取具体的字段值，InnoDB 做了优化，直接按行数累加，效率是最高的。所以在不知道该选哪种写法时，推荐使用
COUNT(*)。
COUNT(主键)：
- InnoDB 会遍历整张表，取出每一行的主键，然后返回给服务层，服务层按行累加。主键不会为
NULL，因此每一行都会被计算在内。
COUNT(字段)：
- 如果字段没有
NOT NULL约束，InnoDB 会一行行把数据取出来，返回给服务层，服务层判断该字段是否为NULL，非NULL才计数。 - 如果字段有
NOT NULL约束，那么服务层会直接按行累加，因为字段不可能为NULL。
COUNT(1)：
- InnoDB 遍历表时不取具体字段值，只是简单遍历，服务层给每一行都加一个数字“1”，然后累加。这和
COUNT(*)差不多，性能几乎一样。
（2）性能排序
按效率从低到高排序：COUNT(字段) < COUNT(主键) < COUNT(1) ≈ COUNT(*)。
因此，最优的选择是 COUNT(*)，因为它的查询方式被专门优化过，性能最好。
4、update 优化
在 InnoDB 存储引擎中，行锁是针对索引加的锁，而不是针对记录加的锁。这样设计的好处是提高了并发性能，但前提是要确保使用了有效的索引。若使用的索引失效，行锁会升级为表锁，这会大大降低并发性能。
（1）举例说明
假设有一个 users 表，包含字段 id（主键索引）和 name（没有索引）。我们有以下两个 UPDATE 语句：
UPDATE users SET age = 30 WHERE id = 1;- 在这个语句中，条件
id = 1是一个索引，因此 InnoDB 只会给id为 1 的这一行加锁。这使得其他事务仍然可以并发地访问和修改表中其他行的数据，性能得到优化。
UPDATE users SET age = 30 WHERE name = '韦一笑';- 这个语句的条件
name = '韦一笑'没有索引，因此 InnoDB 会在扫描整个表来查找满足条件的行。此时，由于无法加行锁，行锁将会升级为表锁，整个users表会被锁住。这意味着在当前事务未提交之前，其他事务无法对这个表进行任何操作，导致并发性能降低。
（2）优化建议
创建合适的索引：确保更新操作的条件字段有索引，避免不必要的表锁升级。
避免大范围更新：尽量减少更新操作对大范围记录的影响，尤其是在高并发环境下。
推荐：
【MySQL】常见的SQL优化方式（一）-CSDN博客
https://blog.csdn.net/m0_65277261/article/details/142376280?spm=1001.2014.3001.5501【计算机组成原理】存储器知识_计算机组成原理存储器知识点-CSDN博客https://blog.csdn.net/m0_65277261/article/details/134770339?spm=1001.2014.3001.5501