MySQL 数据访问与查询优化：提升性能的实战策略和解耦优化技巧

前言

博文：构建优化之城：MySQL 数据建模、数据类型优化与索引常识全面解析
博文：MySQL 数据结构优化与索引细节解析：打造高效数据库的优化秘笈

简要说明了一些索引的基本知识及分类、技术名词，索引的数据结构、生产如何调优、调优的细节如何处理、如何避免生产慢 SQL，该篇博文会继续从以下几点来对 MySQL 调优部分进行分析：

1. 为什么会查询慢？
2. 优化数据访问、执行过程
3. 大数据量查询优化
4. 海量数据解耦优化处理

为什么会查询慢？

查询慢的原因有很多种，不一定是数据库设计不合理或索引使用不正当或 SQL 编写有问题等等
一般情况下，表里数据量不是特别多时，其实改变 SQL 语句的差别不大，但是当表中的数据量形成了一定规模数以后，查询慢的情况就会经常发生，以下是查询慢可能会发生的原因

1. 网络：网络对 MySQL 影响非常大，在进行数据访问时，很多情况下都是在数据中心进行存放的；当需要读取跨异地的数据时，网络将会成为一个至关重要的影响点，特别是在分布式环境中，影响更为突出，因此要尽量减少网络对于数据访问的影响；比如：数据库服务节点与部署服务的节点尽量保持在同一个地域内，不要一个在海外，一个在国内等
2. CPU：在执行不同的操作时，通过 CPU 轮转来完成各个任务的执行，所以时间片的分配会在一定程度下影响数据库的查询效率，尽量减少在业务代码中循环获取数据库连接读取数据
3. IO：在进行 SQL 调优，最关键的点其实就是要优化 IO 成本量
4. 上下文切换：N 多个线程在执行，某个进程的时间片用完以后，就会切换到另外一个进程去执行，切换时会比较浪费时间
5. 系统调用：操作系统内核中的核心概念，涉及到 IO 模型，一般是由具体的 IO 框架来控制的，无法进行优化
6. 生成统计信息：MySQL 中 > show profiles、performance schema 这些统计信息的生成，都需要占用一定的资源，此时也会影响数据的查询
7. 锁等待时间：在并发场景中，锁是非常麻烦的一个问题；在 MySQL 有表锁、行锁，锁机制是与存储引擎相关的；经常用的存储引擎是 MyISAM、InnoDB，MyISAM 里面有两种锁：共享读锁、独占写锁，锁名称不同，内部实现也不一样；MyISAM 在加锁时只会锁别； InnoDB 可以锁表也可以锁行，但是需要注意的是，InnoDB 锁的是索引，若没有对应的索引可以加锁的话，那么就会由行锁退化为表锁

优化数据访问

查询数据低效原因

从数据方面看，查询性能低下的主要原因：访问的数据太多，某些查询不可避免的需要筛选大量的数据，也就是说，这是 IO 问题，因为我们知道大部分的数据都是持久化到磁盘中的，有时候就算加了索引也不一定可以用到索引

确认应用程序是否在检索大量超过需要的数据（数据量超过 30% 会触发 filesort）或者说你通过索引已经过滤了一部分数据，但过滤后的这部分数据你又因为排序的原因导致这部分数据又造成了 filesort，可以通过观察执行计划得知，如下索引：

mysql> select count(*) from rental;
+----------+
| count(*) |
+----------+
|    16044 |
+----------+
1 row in set (0.00 sec)

mysql> explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by rental_date,inventory_id;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | rental | NULL       | ALL  | rental_date   | NULL | NULL    | NULL | 16008 |    50.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+

如上，可以得出检索了数据行 16008 记录，过滤的行数太多了，完全可以通过改变条件来保证检索的数据行减少，如下：

mysql> explain select rental_id,staff_id from rental where rental_date>'2006-05-25' order by rental_date,inventory_id;\
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | rental | NULL       | range | rental_date   | rental_date | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+

如上可以看到只是改变了简单的查询条件，查询的行数就改变了很多，所以有时候就可以通过适当的调整来减少数据访问的行数；比如：当用户表数据量比较多时，默认我只展示最近一个月的用户出来

是否请求了不需要的数据

查询不需要的记录

在工作中经常会误以为 MySQL 只会返回需要的数据，实际上 MySQL 会先返回所有的结果再进行计算，在日常的开发习惯中，经常会先用 select 语句查询大量的结果集，然后获取前面的 N 行数据后关闭结果集，优化方式其实在查询后面添加 LIMIT > 限制结果集数量

多表关联时返回全部列字段

查询时尽量不要使用 SELECT *，用到什么列就查什么列；多表关联尤其不要用 *，建议表民后追加别名，也就是说你有两张以上的表进行关联时，不要写 *

重复查询相同的字段值

若需要不断重复执行相同的查询，且每次返回相同的数据，基于这样的应用场景，可以将这部分数据缓存起来，能够提高查询效率（Redis、Spring Session，当然本地缓存不是很推荐，数据量大或占用的空间大小多时对 JVM 年轻代、老年代会带来负担）

优化执行过程

查询缓存

在解析查询语句之前，若查询缓存是开启的，那么 MySQL 会优先检查这个查询是否命中查询缓存中的数据，若查询恰好命中了查询缓存，那么在返回结果之前会先检查用户权限，若权限没有问题，MySQL 会跳过所有的阶段，就会直接从缓存中拿到结果后就返回给客户端；虽然查询缓存在 MySQL 8 里面给去掉了，但是在 5.x 还是有的，对于某些不经常改变的字典表数据完全可以使用查询缓存来加快查询的访问效率

查询优化处理

MySQL 查询完缓存之后会经过以下步骤 > 解析 SQL、预处理、优化 SQL 执行计划，在这其中某个步骤出现了问题，都可能会终止查询操作，两块：语法解析器、查询优化器

语法解析器

所谓语法解析器 > MySQL 通过关键字将 SQL 语句进行解析，生成一颗解析树，解析器会将使用 MySQL 语法规则验证后解析查询，例如：验证使用了错误的关键字或者顺序是否正确等等，预处理器会进一步检查解析树是否合法；例如：表名、列名是否存在，是否存在歧义，还会验证权限等等；AST(抽象语法树)没必要自己解析的，可以使用 Apache Calcite 开源项目组件进行解析，Calcite > 一款开源 SQL 解析工具，可以将各种 SQL 语句解析成抽象语法树，之后再通过 AST 就可以把 SQL 中所需要的表达式算法和关系体现在具体的代码中

当语法没有问题后，相应的是由优化器将其转换为执行计划，一条查询语句可以使用非常多的执行方式，最后都可以得到对应的结果，但是不同的执行方式带来的效率是不同的，优化器最主要的目的就是要选择最有效的执行计划

查询优化器

MySQL 使用的是基于成本的优化器
在优化时会尝试预测一个查询使用某种查询计划时的成本，并选择其中成本最小的一个（使用不到正确的索引与成本也有很大关系），如下：

mysql> select count(*) from film_actor;
+----------+
| count(*) |
+----------+
|     5462 |
+----------+
1 row in set (0.00 sec)

mysql> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 1104.399000 |
+-----------------+-------------+

通过以上查询可以看出，执行了一个 COUNT 操作，一共查询出了 5462 条记录，耗费的成本是 1104.3990，show status like 'last_query_cost' 这是在当前会话中返回最后一条 SQL 语句执行的耗费时间成本，若换另外一条语句来执行，如下：

mysql> select count(*) from film;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.01 sec)

mysql> show status like 'last_query_cost';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| Last_query_cost | 211.999000 |
+-----------------+------------+

不同的 SQL 语句在执行时所耗费的时间成本是不同的，当得到这个结果值之后，可以适当调整我们的 SQL 语句，以达到最优的方式

在实际业务开发中，COUNT(1) 计数的方式尽量少用，除非是我们那些需要逻辑分页的查询场景下，其他的业务下，我们可以将 O(N) 计数的方式变为 O(1)，也就是说我们提前把这个数计算好，而不是每次要查询时在去单独统计这个数出来

以上时间成本的参数值即 > IO_COST、CPU_COST 开销总和，它通常也是评价 SQL 查询执行效率的一个常用指标

1. 它作为比较各个查询之间开销依据
2. 它只能检测比较简单的查询开销，对于包含子查询、union 查询是测试不出来的
3. 当我们执行查询时，MySQL 会自动生成一个执行计划，也就是 Query Plan，通常会有很多种不同实现方式，它会选择最低成本的那个，而这个 COST 值就是开销最低的那一个

在很多情况下，MySQL 会选择错误的执行计划，产生此问题的根本原因在于以下几点：

1. 统计信息不准确，InnoDB 因为其 MVCC(多版本并发控制) 架构，并不能维护一个数据表行数的精确统计信息，在某些情况下进行了大量的增删改查操作之后，可能会导致统计的信息不精确

mysql> show index from film_actor;
+------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| film_actor |          0 | PRIMARY        |            1 | actor_id    | A         |         200 |     NULL | NULL   |      | BTREE      |         |               |
| film_actor |          0 | PRIMARY        |            2 | film_id     | A         |        5462 |     NULL | NULL   |      | BTREE      |         |               |
| film_actor |          1 | idx_fk_film_id |            1 | film_id     | A         |         997 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

如上表示的 film_actor 表中的索引统计信息，当进行大量的查询之后，可能会造成基数 Cardinality 统计不精确，那么就会有可能产生错误的选择
2. 执行计划成本不等价于实际执行的成本

有时候某些执行计划需要读取更多的页面，但是它的成本却更小，因为这些页面都是顺序读或这些页面都已经在内存中时，那么它的访问成本将会很小，MySQL 层面并不知道哪些页面在内存中，哪些在磁盘，所以查询时执行过程到底需要多少次 IO 是无法得知的

3. MySQL 最优可能与你想的不一样

MySQL 优化是基于成本模型的优化，但是有可能不是最快的优化；A join B join C，一定是先读 A 再读 B 再读 C 嘛？或者先读 C 再读 B 再读 A 嘛？这个过程是没办法预估的，它是根据优化器内部的一个规则来选择先读哪个再读哪个表的

4. MySQL 不会考虑不受其控制的操作成本

在计算成本值时，并不会考虑有多少的并发情况，因为 MySQL 并不知道，也无法预估，只能基于单条查询来做预估，所以这个值不是那么准确，但大部分情况下是没有问题的

5. MySQL 不会考虑不受其控制的操作成本

当执行存储过程或用户自定义函数时，因为不是 MySQL 提供的默认功能，所以 MySQL 中可能不会有对应的统计信息，因此不会考虑此类操作的成本

优化器的优化策略

1. 静态优化表示直接对解析树进行分析后完成优化
2. 动态优化表示与查询的上下文（查询缓存）有关，也可能跟取值、索引对应的行数有关
3. MySQL 对查询的静态优化只需要一次，但对动态优化在每次执行时都需要重新评估，举例说明：比如 A join B，A 表在查询缓存里，B 表不在，这里就要做个判断，A 表在内存或磁盘完全是两种不一样的处理方式，所以查询上下文指的是在一个会话里，之前的查询操作对当前的 SQL 语句产生的影响

优化器的优化类型

1. 重新定义关联表的顺序

数据表的关联并不总是按照查询中的指定顺序进行的，决定关联顺序是优化器很重要的功能，比如：A join B join C，看起来是先读 A 再读 B 再读 C，实际上可能不是这样的，可能会先读 C 再读 B 最后再读 A，它里面有一个优化机制在作判断

2. 外连接转换为内连接，内连接的效率要高于外连接，原因：在于外连接在操作步骤上要比内连接多出来一步
3. 使用等价变换规则，MySQL 可以使用一些等价的变化来简化、规划表达式

例如：条件中包含 a != 4 这个判断，可以替换成 a > 4 & a < 4 这个操作；在实际工作时，能用一个表达式解决绝不要用两个表达式，方便优化器进行优化.

4. 优化 COUNT、MAX、MIN

索引、列是否可以为空，通常可以帮助 MySQL 来优化这类表达式，例如：要找到某一列的最小值，只需要查询索引的最左端记录即可，无须进行全文扫描比较

5. 预估并转化为常数表达式

当 MySQL 检测到一个表达式可以转换为常数时，就会一直把表达式作为常数进行处理

mysql> explain select film.film_id,film_actor.actor_id from film inner join film_actor using(film_id) where film.film_id = 1;
+----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys  | key            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | film       | NULL       | const | PRIMARY        | PRIMARY        | 2       | const |    1 |   100.00 | Using index |
|  1 | SIMPLE      | film_actor | NULL       | ref   | idx_fk_film_id | idx_fk_film_id | 2       | const |   10 |   100.00 | Using index |
+----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+

type = const，const 表示常数，执行效率是比较高的，尽可能把我们 SQL 中的表达式转化成常量值

6. 索引覆盖扫描：当索引中的列包含在所有查询中需要使用到的列时，可以使用覆盖索引
7. 子查询优化：MySQL 在某些情况下，可以将子查询转换为一种效率更高的形式，从而减少多个查询、多次对数据进行访问，例如：经常查询的数据放入到缓存中
8. 等值传播
   若两个列的值通过等值关联，那么 MySQL 能够把其中一个列的 where 条件传递到另外一个

explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500;

使用了 film_id 字段进行等值关联，film_id 列不仅适用于 film 表同时也适用于 film_actor 表

explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500 and film_actor.film_id > 500;

关联查询

MySQL 关联查询很重要，但是关联查询执行的策略比较简单；MySQL 对任何关联都会执行嵌套循环的关联操作，即 MySQL 先在一张表中循环取出单条数据，然后再嵌套到下一表中寻找匹配的行，直到找到所有表中匹配的行为止

根据各个表匹配的行，返回查询中需要的各个列，MySQL 会尝试在最后一个关联表中找到所有匹配的行，若最后一个关联表无法找到更多的行之后，MySQL 返回到上一层次的关联表，看是否能够找到更多匹配的记录，以此类推迭代执行

整体的思路如此，但是要注意实际的执行过程中有多种形式

通过案例来演示，不同顺序执行方式对查询性能的影响

mysql> explain select film.film_id,film.title,film.release_year,actor.actor_id,actor.first_name,actor.last_name from film inner join film_actor using(film_id) inner join actor using(actor_id);
+----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys          | key     | key_len | ref                       | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------------+------+----------+-------------+
|  1 | SIMPLE      | actor      | NULL       | ALL    | PRIMARY                | NULL    | NULL    | NULL                      |  200 |   100.00 | NULL        |
|  1 | SIMPLE      | film_actor | NULL       | ref    | PRIMARY,idx_fk_film_id | PRIMARY | 2       | sakila.actor.actor_id     |   27 |   100.00 | Using index |
|  1 | SIMPLE      | film       | NULL       | eq_ref | PRIMARY                | PRIMARY | 2       | sakila.film_actor.film_id |    1 |   100.00 | NULL        |
+----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------------+------+----------+-------------+

执行完以后会发现执行顺序：actor、film_actor、film，查看所耗费的成本如下：

mysql> show status like 'last_query_cost'; 
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 7892.932509 |
+-----------------+-------------+

若想按照自己预想的规定顺序执行，使用 straight_join 关键字，如下：

mysql> explain select straight_join film.film_id,film.title,film.release_year,actor.actor_id,actor.first_name,actor.last_name from film inner join film_actor using(film_id) inner join actor using(actor_id);
+----+-------------+------------+------------+--------+------------------------+----------------+---------+----------------------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys          | key            | key_len | ref                        | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+------------------------+----------------+---------+----------------------------+------+----------+-------------+
|  1 | SIMPLE      | film       | NULL       | ALL    | PRIMARY                | NULL           | NULL    | NULL                       | 1000 |   100.00 | NULL        |
|  1 | SIMPLE      | film_actor | NULL       | ref    | PRIMARY,idx_fk_film_id | idx_fk_film_id | 2       | sakila.film.film_id        |    5 |   100.00 | Using index |
|  1 | SIMPLE      | actor      | NULL       | eq_ref | PRIMARY                | PRIMARY        | 2       | sakila.film_actor.actor_id |    1 |   100.00 | NULL        |
+----+-------------+------------+------------+--------+------------------------+----------------+---------+----------------------------+------+----------+-------------+

表加载顺序：film、film_actor、actor，查看其耗费的成本

mysql> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 8885.087226 |
+-----------------+-------------+

通过这两种对比，可以看出 MySQL 选择了成本更低的方式来执行 SQL 语句

排序优化

无论如何排序，都是一个成本很高的操作，从性能的角度出发，应该尽可能的避免排序或尽可能避免对大量数据排序

推荐使用 索引排序，但是当不能使用索引时，MySQL 就需要自己进行排序，若数据量小则在内存中进行，若数据量大就需要使用磁盘，MySQL 称之为 filesort

若需排序的数量小于排序缓冲区（show variables like '%sort_buffer_size%'）MySQL 使用内存进行快速排序操作，若内存不够排序，MySQL 就会将树分块，对每个独立块使用快速排序进行排序，并将各个块排序结果存放在磁盘上，然后对每个排好序的块进行合并，最后返回结果

mysql> show variables like '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size        | 262144  |
+-------------------------+---------+

在进行排序时还会涉及到两种排序算法 > 两次传输排序、单次传输排序

两次传输排序

第一次数据读取是将需要排序的字段读取出来，然后进行排序；第二次是将排好序的结果按照所需去读取数据行
这种方式效率比较低，原因：第二次读取数据时因为已经排好序，需要去读取所有记录，而此时更多的是随机 IO，读取数据成本会比较高
两次传输的优势在于，在排序时存储尽可能少的数据，让排序缓冲区可以尽可能的容纳行数来进行排序操作

单次传输排序

先读取查询需要的所有列，然后再根据给定列进行排序，最后直接返回排序后的结果，此方式只需要一次顺序 IO 读取所有的数据，无须任何的随机 IO，问题在于查询列特别多时，会占用大量的存储空间，无法存储大量的数据

当需要排序的列总大小超过 max_length_for_sort_data 参数定义的字节数，MySQL 会选择两次传输排序，否则使用单次传输排序，Of Course，用户可以设置此参数的值来选择排序的方式

mysql> show variables like '%max_length_for_sort_data%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024  |
+--------------------------+-------+

大数据量查询优化

基于大数据量查询优化场景下，从而优化特定类型的查询

优化 COUNT 查询

在进行 COUNT 计数查询时需要注意以下几点：

1. 总有人认为 MyISAM 存储引擎中 COUNT 函数效率比较快，这是有前提条件的，只有没有任何 where 条件的 COUNT(*) 才是比较快的；MyISAM 有一个变量来记录插入的行数，所以会比较快，但是一旦携带了 WHERE 条件去统计数量，变量记录的值就不准确了，仍然还是要和普通查询一样，挨个遍历
2. 使用近似值，在某些应用场景中，不需要完全精确的值，可以参考使用近似值来代替，比如：使用 Explain 来获取近似值或采用计算近似值的算法 > HyperLogLog
3. 一般情况下，COUNT 需要扫描大量的行才能获取精确的数据，其实是更复杂的优化，在实际操作时可以考虑使用索引覆盖扫描或增加汇总表提前统计好数量或引入外部缓存系统；插入数据时进行汇总表、外部缓存的累加操作，等需要总的记录数时直接取值就可以了，不需要从数据表中进行统计，但是一定要借助外部的一些系统

优化关联查询

首先要确保 on、using 子句中的关联列有索引，在创建索引时要考虑到关联的顺序

业务系统中，一般都是关联主键、外键没有什么问题；特殊情况下，会用普通列作关联查询，这时就可能会出现问题，所以最好还是使用索引列；使用索引列的效率是比较高的，既然创建了索引就一定要用，比如 > 建了主键还不用主键来优化就没什么意义了

确保任何 group by、order by 中表达式只涉及到一个表中的列（关联表查询时，只使用一个表中的字段进行排序、分组）这样 MySQL 才有可能使用索引来优化这个过程；group by、order by 能使用索引还是要用，这样效率是比较高的，不使用的话就会有问题，比如：filesort 磁盘轮转排序效率低缓

优化子查询

子查询优化，建议是尽可能使用关联查询进行代替，不要使用对应的子查询；在某些应用场景里，可以用子查询也可以用 JOIN 方式，不推荐使用子查询；每次 SELECT 时会得到一个结果，子查询的记过会放到临时表里，临时表就会涉及到 IO，与其这样还不如直接使用 JOIN 关联数据，效率可能还会更高一些

优化 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、使用未定义变量，不会产生任何语法错误

自定义变量的使用案例

1. 变量赋值后，使用此变量

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 |
+--------+----------+

2. 避免重复查询刚刚更新的数据 > 当需要高效的更新一条记录的时间戳时，同时希望查询当前记录中存放的时间戳是什么，如下：
   一般情况下，我们都是先把数据更新上去后，再通过主键查询这条数据的信息

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;

3. 确认取值的顺序性 > 在赋值、读取变量时可能是在查询的不同阶段

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，订阅一波不再迷路

大家的「关注❤️ + 点赞👍 + 收藏⭐」就是我创作的最大动力！谢谢大家的支持，我们下文见！