优化 LIMIT 分页
在大多数应用场景中，都需要将数据进行分页，一般会使用 LIMIT + offset 方法实现，同时+上合适的 order by 语句；若这种方式有索引的帮助，效率通常会不错，否则的话就需要大量的文件排序操作，还有一种情况，当偏移量非常大时，前面的大部分数据都会被抛弃，这样的代码太高；要优化这种查询时，要么在页面中限制分页的数量，要么优化大偏移量的性能，优化此类查询的最简单办法就是尽可能的使用覆盖索引，而不是查询所有的列
mysql> explain select film_id,description from film order by title limit 50,5; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | film | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select film_id,description from film inner join (select film_id from film order by title limit 50,5) as lim using(film_id); +----+-------------+------------+------------+--------+---------------+-----------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+-----------+---------+-------------+------+----------+-------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 55 | 100.00 | NULL | | 1 | PRIMARY | film | NULL | eq_ref | PRIMARY | PRIMARY | 2 | lim.film_id | 1 | 100.00 | NULL | | 2 | DERIVED | film | NULL | index | NULL | idx_title | 514 | NULL | 55 | 100.00 | Using index | +----+-------------+------------+------------+--------+---------------+-----------+---------+-------------+------+----------+-------------+
通过如上对比，能够发现第二条 SQL 语句扫描的数据行更少，所以可以通过这种方式对 LIMIT 进行优化
优化 union
MySQL 通过创建并填充临时表的方式来执行 union 查询，因此很多优化策略在 union 查询中都没法很好的使用到；经常需要手动的将 where、limit、order by 等子句下推到各个子查询中，以便于优化器可以充分利用这些条件进行优化
除非服务器确实需要消除重复行数据，否则一定要使用 union all，因为没有 all 关键字，MySQL 会在查询时给临时表 + 上 distinct 关键字，这个操作的代价很高
用户自定义变量
用户自定义变量是一个容易被遗忘的 MySQL 特性，但若能用好的话，在某些场景下可以写出非常高效的查询语句，在查询中混合使用过程化、关系化逻辑时，自定义变量会非常有用
用户自定义变量是一个可以用来存储内容的临时容器，在连接 MySQL 整个过程中都存在
使用自定义变量
mysql> set @min_actor:=(select min(actor_id) from actor); Query OK, 0 rows affected (0.00 sec) mysql> select @min_actor; +------------+ | @min_actor | +------------+ | 1 | +------------+ 1 row in set (0.00 sec) mysql> set @last_week:=current_date-interval 1 week; Query OK, 0 rows affected (0.00 sec) mysql> select @last_week; +------------+ | @last_week | +------------+ | 2023-05-23 | +------------+
自定义变量的限制
1、无法使用查询缓存
2、不能在使用常量或标识符的地方使用自定义变量，例如：表名、列名或 LIMIT 子句
3、用户自定义变量的生命周期在一个连接内有效，所以不能用它们来作连接之间的通信
4、不能显示声明自定义变量的类型
5、MySQL 在特定场景下，可能会将这些变量优化掉，可能导致代码不按预想的方式运行
6、赋值符号 := 优先级非常低，所以在使用赋值表达式时应该明确使用的符号
7、使用未定义变量，不会产生任何语法错误
自定义变量的使用案例
- 变量赋值后，使用此变量
mysql> select @rownum:=@rownum+1 as rownum,actor_id from actor limit 10; +--------+----------+ | rownum | actor_id | +--------+----------+ | 1 | 58 | | 2 | 92 | | 3 | 182 | | 4 | 118 | | 5 | 145 | | 6 | 194 | | 7 | 76 | | 8 | 112 | | 9 | 67 | | 10 | 190 | +--------+----------+
- 避免重复查询刚刚更新的数据 > 当需要高效的更新一条记录的时间戳时，同时希望查询当前记录中存放的时间戳是什么，如下：
一般情况下，我们都是先把数据更新上去后，再通过主键查询这条数据的信息
create table t1(id int,lastUpdated date); insert into t1 values(1,now()); update t1 set lastUpdated=now() where id=1; select lastUpdated from t1 where id=1;
上述的操作可以分为两个步骤来操作，直接使用自定义变量代替 >
update t1 set lastUpdated=now() where id=1 and @now:=now(); select @now;
- 确认取值的顺序性 > 在赋值、读取变量时可能是在查询的不同阶段
mysql> set @rownum:=0; Query OK, 0 rows affected (0.00 sec) mysql> select @rownum:=@rownum+1 as cnt,actor_id from actor where @rownum<=1; +------+----------+ | cnt | actor_id | +------+----------+ | 1 | 58 | | 2 | 92 | +------+----------+
where、select 在查询的不同阶段执行，所以可以看到两条记录被查询出来，这不符合预期
set @rownum:=0; select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1 order by first_name;
当引入 order by 子句以后，发现打印出了全部结果，这是因为 order by 引入了文件排序，而 where 条件是在文件排序之前取值的.
解决这个问题的关键在于：让变量的赋值、取值发生在执行查询的同一阶段
mysql> set @rownum:=0; Query OK, 0 rows affected (0.00 sec) mysql> select @rownum as cnt,actor_id from actor where (@rownum:=@rownum+1)<=1; +------+----------+ | cnt | actor_id | +------+----------+ | 1 | 58 | +------+----------+
个人在工作当中，常常会用自定义变量在测试环境写存储过程，主要是为了简便演示>测试数据的工作，提高工作中的效率
海量数据解耦优化处理
当操作大数据量时，解耦是必不可少的，可靠的反范式化设计、业务层面的解耦上升到数据库设计层面
单行数据字段不宜太多，尽量能让一些字段分散到中间表中，根据其使用场景来取字段信息，
MySQL 5.7 版本支持 JSON 类型字段，此字段内容建议不要存无用的信息，因为内容越多占用的磁盘空间越高，会导致我们单表所能存储的数据量大小大大降低
在表数据量逐渐递增时，原有的业务需要关联多张表获取数据，到后面这将会是一个很大头的问题，所以，在前期的数据库设计、适当的反范式化设计是相当重要的，尽管需要多开发一些业务代码以及处理数据的一致性逻辑等，但为我们的瓶颈>MySQL 带来极大的益处，减少了 IO 成本开销，减少给数据库带来的压力！
冗余那些 JOIN 表只需要 1～2 个字段信息的内容，可以将它冗余到表中，当后续这两个字段内容发生改变时，可以通过事件的方法触达> 异步更新表中的内容，以达到数据的最终一致性
在业务设计开发过程中，减少 IO 次数，频繁请求数据库的操作，尽量以一条可视化 SQL（不通过 MyBatis-Plus 便捷操作> 它为我们提供了便利，并不是让我们这么消耗数据库性能的，它更方便的是为了给我们提供单表内的查询，关于多表或基于多字段统计时尽量还是使用可视化 SQL 语句去编码实现）
在处理大表数据时，不应该一次性把所有的数据全捞出来，高并发处理的场景都是基于分而治之的思想去做的，以批次的方式去处理业务数据，比如：1000、2000、3000 方式，控制好数据操作的时长，以这个时长为间隔去处理下一批数据> 分布式任务调度以串行的方式去处理每一条任务，当然，处理的前提是表的索引、执行效率要保障好！
基于测试、生产环境，后续服务资源升级或降级，批次处理的这个数量应该以配置的形式去处理，可以基于我们的实际诉求去动态调整
接口设计也是一方面的提升，接口的设计也应该遵循于设计模式的初衷，如：单一职责；每个接口处理的数据应该独立区分，不应该所有的事情都由一个接口去完成，接口返回的参数内容应该也是一一对应的，过多返回无用的字段对于我们网络传输是效率极低下的，即使字段是空值，但不要忽略我们 Java 中每种类型只要你定义了，它就一定会占用空间的，更何况说，基于前后端交互，它的序列化机制、网络交互机制所需要耗费的成本资源了！
比如：查询会员等级配置，前端只需要用到等级Id、等级编码、等级名称，那么就可以基于解耦，单独定义一个实体只存放这三个字段，在操作 DB 时，不要 SELECT *，也只查询 id、level_code、level_name，这样既减少了数据库的 IO 交互，又减少了网络传输中的成本，也减少了序列化实体的字段数
其次，缓存要运用到位，不是所有的数据都是需要经过数据库去取出来的，对于一些基础数据，一旦它确认了，就不会再发生改变时，完全可以将它以时效性的机制存入到 Redis 缓存中，让它基于内存去交互，而不是经过数据库->磁盘交互，内存->磁盘时长约等于 1:1000，用好缓存的同时，要保证缓存一致性，这就又引出了缓存、MySQL 数据一致性问题，但 MySQL 变更后同时要更新缓存信息或删除缓存信息
服务中处理缓存的地方尽量将它统一放在一个地方处理，若出现了缓存、MySQL 数据一致性问题时，你可能都不知道是哪个地方使用了缓存，这是编码规范的一部分；为了确保缓存一致性，采用删除缓存的方式并不是那么可靠，因为 Redis 中有缓存过期策略这个机制，即使你调用了删除，但有可能它不会立马就删除这些缓存，它会基于过期策略有一个较小的缓冲期
异步对于提高整体效率也是一个很重要的部分，对于那些没有依赖性的数据时，完全可以让它以异步的方式先去处理后返回数据，CompletableFuture 异步编排是一个很好的并发编程 API
基于高并发场景下，多个用户同时访问同一批数据，会对 MySQL 产生大量无效的、重复的请求，此时，可以基于此分析 > 重复的请求一定是一样的数据，那么此数据我们可以在这个时刻先用缓存的方式存储起来，将给予 MySQL 压力转移到缓存中间件，访问方式以磁盘转换为内存，提高接口吞吐量、减少响应的时长
基于此场景，对于这个接口可以对访问数据库的地方 + 分布式读锁，Redis setex 是一个较好的选择
解耦部分：
1、首先抢到锁的人先请求数据库获取这部分数据，然后将其缓存起来；后面的用户线程可以直接读取缓存起来的这部分数据，以达到再同一个时刻访问同一份数据时，可以减少对 MySQL 造成的压力，其而言之，这就是
缓存击穿2、保证访问这部分数据内容的运行时间能让它达到最优解，以我前面介绍的方式对数据库表以及数据部分进行优化，确保在最短时间内数据能够到达！
详解：当多个用户同时进来时，优先从缓存中读取数据，若缓存中无数据，
第一个用户线程会抢到这把锁>查询数据库返回数据、存入缓存，那么其他用户线程就拿不到这把锁了，此时我们可以让它先阻塞一小段时间 >Thread.sleep(500);，等这个时间过去了，其他的用户线程可以再从缓存中读取数据，后续此类高频率请求的接口可以大大提升效率
总结
该篇博文简要分析了为什么会查询慢？以不同角度的优化方式提高数据访问的效率，简单地分析 MySQL 语法解析器、查询优化器处理的过程，以理论+实战结合方式加深印象，提高辨识度；最主要的大数据量查询优化、海量数据优化处理，这些场景都是博主在实际工作中处理过的，实战是校验真理的唯一标准，解耦设计、异步、缓存、适当反范式化设计等，都是一些日常中会使用的技术场景，后面会有文章主要讲述 MySQL 锁、MVCC、分区等概念｜实战
大家有什么问题或者更好的建议，可以在文末评论，一起探讨喔，主打技术共同进步、提升！
如果觉得博文不错，关注我 vnjohn，后续会有更多实战、源码、架构干货分享！
推荐专栏：Spring、MySQL，订阅一波不再迷路
大家的「关注❤️ + 点赞👍 + 收藏⭐」就是我创作的最大动力！谢谢大家的支持，我们下文见！
