关于后端:Mysql系列一MySQL语句执行流程

41次阅读

共计 8486 个字符，预计需要花费 22 分钟才能阅读完成。

首发博客地址

首发博客地址

系列文章地址

参考文章

MySQL 逻辑架构

连接器

连贯命令个别是这么写的

mysql -h$ip -P$port -u$user -p

那么

什么是连接器？

MySQL 连接器（MySQL Connector）是用于连贯和与 MySQL 数据库进行交互的驱动程序。它提供了与 MySQL 数据库服务器通信的性能，包含建设连贯、执行查问、更新数据等。

MySQL 提供了多种连接器，罕用的有以下几种：

  1. JDBC 连接器：JDBC（Java Database
    Connectivity）是 Java 语言的一种规范 API，用于连贯和操作各种数据库。MySQL 提供了一个 JDBC 驱动程序，能够通过 JDBC 连接器在 Java 应用程序中连贯和操作 MySQL 数据库。
  2. ODBC 连接器：ODBC（Open Database
    Connectivity）是一种通用的数据库拜访接口，能够在不同的编程语言和操作系统中连贯和操作多种数据库。MySQL 提供了一个 ODBC 驱动程序，能够通过 ODBC 连接器在反对 ODBC 的应用程序中连贯和操作 MySQL 数据库。
  3. .NET 连接器：MySQL 提供了一个专门用于.NET 平台的连接器，能够在.NET 应用程序中连贯和操作 MySQL 数据库。它提供了与 ADO.NET 兼容的接口，能够不便地在.NET 开发环境中应用 MySQL 数据库。
  4. Python 连接器：MySQL 提供了一个 Python 连接器，能够在 Python 应用程序中连贯和操作 MySQL 数据库。该连接器是通过 Python 的规范数据库 API（Python
    DB API）实现的。

应用 MySQL 连接器，能够通过配置连贯参数（如主机名、端口号、用户名、明码等）来建设与 MySQL 数据库的连贯。连接器还提供了执行 SQL 语句、获取查问后果、处理事务等性能，能够不便地进行数据库的操作。

以下是一个应用 JDBC 连接器连贯 MySQL 数据库的示例：

import java.sql.*;

public class MySQLExample {public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "root";
        String password = "password";

        try {
            // 加载 MySQL JDBC 驱动程序
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 建设与 MySQL 数据库的连贯
            Connection conn = DriverManager.getConnection(url, username, password);

            // 执行 SQL 查问
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM users");

            // 解决查问后果
            while (rs.next()) {int id = rs.getInt("id");
                String name = rs.getString("name");
                System.out.println("id:" + id + ", name:" + name);
            }

            // 敞开连贯
            rs.close();
            stmt.close();
            conn.close();} catch (Exception e) {e.printStackTrace();
        }
    }
}

下面的示例中，通过加载 MySQL
JDBC 驱动程序，应用 JDBC 连接器建设了与 MySQL 数据库的连贯。而后执行了一个查问语句，并解决了查问后果。最初敞开了连贯。通过 JDBC 连接器，能够不便地在 Java 应用程序中连贯和操作 MySQL 数据库。

连接器连贯流程

mysql -h$ip -P$port -u$user -p 是一个连贯到 MySQL 服务器的命令，其中 $ip 是要连贯的 MySQL 服务器的主机名或 IP 地址，$port
是 MySQL 服务器的端口号，$user 是要连贯的用户名，-p 示意须要输出明码。

上面是应用 mysql -h$ip -P$port -u$user -p 命令进行连贯的具体流程：

  1. 当咱们在终端或命令提示符中输出 mysql -h$ip -P$port -u$user -p 命令后，操作系统会启动一个新的过程来执行该命令。
  2. 操作系统会在零碎的可执行程序门路中搜寻 mysql 可执行文件，并找到它。
  3. 执行 mysql 可执行文件后，会启动一个 MySQL 客户端程序。这个程序是用 C 或 C++ 编写的，负责与 MySQL 服务器进行通信。
  4. MySQL 客户端程序会解析命令行参数，并找到 -h$ip 参数指定的主机名或 IP 地址，-P$port 参数指定的端口号，-u$user
    参数指定的用户名。
  5. MySQL 客户端程序会尝试与指定的 MySQL 服务器建设网络连接，应用$ip 和 $port 参数指定的主机名（或 IP 地址）和端口号。
  6. 如果网络连接胜利建设，MySQL 客户端程序会发送一条连贯申请给 MySQL 服务器，申请包含用户名和明码。
  7. MySQL 服务器收到连贯申请后，会进行身份验证，查看用户名和明码是否正确。如果验证通过，服务器会返回一个胜利的连贯响应。
  8. MySQL 客户端程序接管到连贯响应后，连贯建设胜利，此时会提醒输出明码。
  9. 咱们须要输出与 $user 参数指定的用户名对应的明码，按下回车键确认。
  10. 如果明码正确，MySQL 客户端程序会发送验证明码的申请给 MySQL 服务器。
  11. MySQL 服务器收到明码验证申请后，会进行验证。如果明码验证通过，服务器会返回一个胜利的验证响应。
  12. MySQL 客户端程序接管到验证响应后，连贯胜利建设。此时，咱们能够在命令行中输出 SQL 语句，并将其发送给 MySQL 服务器进行执行。
  13. MySQL 服务器接管到 SQL 语句后，会执行相应的操作，比方查问、插入、更新等。
  14. MySQL 服务器执行完 SQL 语句后，会将后果返回给 MySQL 客户端程序。
  15. MySQL 客户端程序接管到后果后，会将结果显示在命令行中供咱们查看和解决。
  16. 当咱们退出 MySQL 客户端程序或输出 quit 命令敞开连贯时，MySQL 客户端程序会发送一个断开连接的申请给 MySQL 服务器，服务器会敞开与客户端的连贯。

以上是应用 mysql -h$ip -P$port -u$user -p 命令进行连贯的具体流程。通过这个流程，咱们能够在终端或命令提示符中连贯到指定的 MySQL 服务器，并进行数据库操作。

这里须要留神的有两点：

  1. 不要间接将明码跟在 -p 命令前面，不平安
  2. ** 一个用户胜利建设连贯后，即便你用管理员账号对这个用户的权限做了批改，也不会影响曾经存在连贯的权限。批改实现后，只有再新建的连贯才会应用新的权限设置
    **

如何查看连贯状态

要查看以后连贯到 MySQL 服务器的会话和连贯信息，能够应用以下办法：

  1. 通过 MySQL 命令行客户端：在命令行中输出 mysql 命令，连贯到 MySQL 服务器。而后应用以下命令查看连贯信息：

    SHOW PROCESSLIST;

    这个命令将显示以后所有流动的 MySQL 连贯和会话的详细信息，包含连贯的 ID、主机名、用户、执行的 SQL 语句等。

  2. 通过 MySQL 管理工具：如果应用 MySQL 的管理工具，如 phpMyAdmin、MySQL Workbench
    等，能够应用这些工具提供的界面来查看连贯信息。通常会有一个“以后连贯”或相似的选项，点击即可显示以后连贯的详细信息。

  3. 通过查问零碎表：能够应用以下 SQL 查问语句查看连贯信息：

    SELECT * FROM information_schema.processlist;

    这个查问语句将返回一个蕴含以后连贯和会话信息的后果集，包含连贯的 ID、主机名、用户、执行的 SQL 语句等。

通过以上办法，您能够不便地查看以后连贯到 MySQL 服务器的会话和连贯信息。这对于监控和调试数据库连贯十分有用。

什么是 MySQL 长连贯

MySQL 长连贯是指在应用程序与 MySQL 服务器之间放弃长久的连贯，而不是每次执行操作都建设和断开连接。绝对于短连贯，长连贯能够缩小连贯和断开的开销，进步性能。

在 MySQL 中，长连贯能够通过以下形式实现：

  1. 在应用程序中应用连接池：连接池是一组事后创立的、可重复使用的数据库连贯。应用程序从连接池中获取连贯，并在应用结束后将连贯返回到连接池中，而不是每次操作都创立和敞开连贯。连接池能够在应用程序启动时初始化，并在应用程序敞开时销毁。
  2. 调整 MySQL 服务器的参数：MySQL 服务器有一些参数能够调整来反对长连贯。其中一个要害参数是 wait_timeout
    ，它定义了一个连贯在闲暇一段时间后被服务器敞开的工夫。默认状况下，wait_timeout 较短，实用于短连贯。能够将 wait_timeout
    设置为较大的值，以反对长连贯。

长连贯的长处包含：

  • 缩小连贯和断开的开销：长连贯防止了频繁的连贯建设和断开，缩小了网络开销和服务器累赘。
  • 疾速响应：应用长连贯，应用程序能够更快地执行数据库操作，因为不须要从新建设连贯。
  • 资源利用率高：长连贯能够重复使用数据库连贯，缩小了服务器资源的占用。

然而，长连贯也有一些注意事项：

  • 连贯资源占用：长连贯占用了 MySQL 服务器的连贯资源，因而须要确保服务器的连接数限度和资源配置正当。
  • 内存透露：长时间运行的应用程序可能会导致内存透露，因为连贯没有及时开释。因而，应该定期检查和清理不再应用的连贯。

要应用长连贯，请确保应用程序中正确配置连接池，并依据须要调整 MySQL 服务器的参数。这样能够充分利用长连贯的劣势，进步应用程序的性能和效率。

须要留神的是，MySQL 默认长连接时间是 8 小时

因为长连贯导致 OOM 怎么办

如果长连贯导致了内存不足（OOM），能够思考以下几种形式来解决该问题：

  1. 缩小连接池中的连接数：如果连接数过多导致内存不足，能够尝试缩小连接池中的连接数。通过调整连接池的配置参数，例如最大连接数（max
    connections），能够限度连贯的数量。
  2. 设置连贯超时工夫：减少连贯的闲暇超时工夫（idle
    timeout），使得闲暇的连贯在一段时间后被主动敞开，开释内存资源。能够通过设置 MySQL 服务器的 wait_timeout 参数或连接池的相干配置来实现。
  3. 定期清理有效连贯：定期检查连接池中的连贯，清理掉长时间未应用或已敞开的连贯，开释内存资源。能够编写一个定时工作或应用连接池提供的相干机制来实现。
  4. 思考应用短连贯：如果长连贯依然导致 OOM，能够思考应用短连贯的形式。在每次数据库操作之后，立刻敞开连贯，防止长时间占用连贯资源。不过，这样会引入连贯建设和断开的开销，可能会升高性能。
  5. 减少服务器内存：如果以上办法无奈解决 OOM 问题，能够思考减少服务器的内存容量，以提供更多的资源来反对长连贯。

在解决 OOM 问题时，须要依据具体情况进行调整和优化。能够依据应用程序的负载状况、数据库连贯的应用状况以及服务器的资源配置来抉择适合的办法。同时，也倡议进行性能测试和监控，以及仔细分析和调优应用程序和数据库的性能瓶颈。

查问缓存

什么是 MySQL 查问缓存

MySQL 查问缓存是 MySQL 数据库的一项性能，用于缓存查问的后果集。它能够在执行查问语句时，将查问后果缓存到内存中，以便后续雷同的查问能够间接从缓存中获取后果，而不须要再次执行雷同的查问操作。

MySQL 查问缓存的工作原理如下：

  1. 当执行一个查问语句时，MySQL 会首先查看查问缓存，看看是否有与以后查问语句齐全匹配的缓存后果。
  2. 如果有匹配的缓存后果，MySQL 会间接从缓存中获取后果，并将其返回给客户端，而不须要再次执行查问。
  3. 如果没有匹配的缓存后果，MySQL 会执行查问操作，获取查问后果，并将其存储到缓存中，以备后续雷同的查问应用。

MySQL 查问缓存的长处包含：

  • 进步查问性能：对于雷同的查问语句，能够间接从缓存中获取后果，防止了再次执行查问的开销，进步了查问性能。
  • 缩小服务器负载：查问缓存能够缩小数据库服务器的负载，因为雷同的查问不须要再次执行，缩小了 CPU 和 IO 的耗费。

然而，MySQL 查问缓存也有一些限度和注意事项：

  • 查问缓存的匹配粒度：MySQL 查问缓存是依据齐全匹配查问语句的形式进行缓存的，即查问语句的文本完全相同。如果查问语句中有任何渺小的差别，比方空格、大小写等，就无奈从缓存中获取后果。
  • 缓存的更新频率：当对某个表进行更新操作（插入、更新、删除）时，与该表相干的缓存会被清空，须要从新执行查问。这可能导致缓存的频繁生效，升高了缓存的成果。
  • 内存耗费：查问缓存须要占用肯定的内存空间来存储缓存后果，如果缓存的数据量较大，可能会占用大量的内存资源。此外，查问缓存是基于查问语句进行缓存的，对于雷同的查问语句，即便查问后果不同，也会应用雷同的缓存空间。

因为一些限度和性能问题，MySQL 5.7 版本开始移除了查问缓存性能。在较新的 MySQL 版本中，通常倡议通过其余伎俩（如索引优化、查问优化）来进步查问性能，而不是依赖查问缓存。

为什么不倡议应用查问缓存

查问缓存在过来是 MySQL 的一个性能，用于进步查问性能。然而，随着工夫的推移，查问缓存的应用逐步被认为不再是一个举荐的数据库优化策略。以下是一些起因，解释为什么不倡议应用查问缓存：

  1. 锁粒度和高并发问题：MySQL 查问缓存的实现形式导致在高并发环境下存在锁粒度问题。当对某个表进行更新操作时，相干的查问缓存会被锁定，从而导致其余查问被阻塞，升高了并发性能。
  2. 缓存生效频繁：MySQL 查问缓存的缓存生效频率较高。** 一旦对某个表进行了更新操作，与该表相干的缓存都会被清空，须要从新执行查问
    **。这导致缓存的命中率较低，而缓存生效的开销却很高。

3.

内存耗费和不可扩大：查问缓存须要占用肯定的内存空间来存储缓存后果，如果缓存的数据量较大，可能会占用大量的内存资源。而且，查问缓存是基于查问语句进行缓存的，对于雷同的查问语句，即便查问后果不同，也会应用雷同的缓存空间。这导致了内存的节约，并且不适用于大规模数据库和高并发环境。

  1. 缓存一致性和数据更新问题：因为 MySQL 查问缓存是以查问语句为根底进行缓存的，当有数据更新操作时，缓存的一致性可能会受到影响。如果多个客户端同时对同一表进行更新操作，会导致查问缓存中的数据与理论数据不统一。
  2. 查问优化器限度：应用查问缓存后，MySQL 查问优化器的抉择余地受到限制。查问缓存只实用于齐全匹配的查问语句，如果查问语句有渺小差别（如空格、大小写等），无奈从缓存中获取后果。这可能会导致查问优化器无奈灵便抉择最佳的查问打算。

思考到以上问题，MySQL 5.7 版本开始移除了查问缓存性能，并且在更高版本中不再举荐应用。古代的数据库优化策略更侧重于调整索引、查问优化、缓存层、硬件降级等方面来进步性能和可扩展性。

须要留神 MySQL8.0 版本曾经删掉了查问缓存的性能

分析器

什么是 MySQL 分析器

MySQL 分析器（MySQL Analyzer）是指用于剖析和优化 MySQL 数据库性能的工具或技术。它能够帮忙开发人员和数据库管理员辨认数据库查问的瓶颈，并提出性能优化倡议。

MySQL 分析器通常提供以下性能：

  1. 查问执行打算剖析：分析器能够解析和剖析数据库查问的执行打算。执行打算形容了 MySQL 查问优化器将如何执行查问，包含应用哪些索引、连贯形式、排序形式等。通过剖析执行打算，能够确定查问的性能瓶颈和潜在优化点。
  2. 查问性能评估：分析器能够评估查问的性能，并提供查问的执行工夫、扫描的行数、应用的索引等要害指标。通过比拟不同查问的性能评估后果，能够确定哪些查问须要优化以进步性能。
  3. 索引剖析与优化：分析器能够帮忙辨认数据库表中缺失或不适合的索引。它能够剖析查问的执行状况，查看哪些查问没有应用索引，或者哪些查问应用了慢速索引。基于这些剖析后果，能够提出创立、批改或删除索引的倡议。
  4. SQL 语句优化倡议：分析器能够剖析 SQL 语句，提供优化倡议。它能够查看 SQL 语句的语法、查问构造和性能问题，并提供无关如何重写查问、应用更无效的查问形式的倡议。
  5. 零碎性能监控：一些分析器还提供零碎性能监控性能，能够收集和剖析数据库服务器的性能指标，如 CPU 利用率、内存应用状况、磁盘 IO 等。这些指标能够帮忙识别系统瓶颈和资源瓶颈，并进行性能调优。

常见的 MySQL 分析器工具包含 Explain、pt-query-digest、Percona Toolkit、MySQL Enterprise
Monitor 等。这些工具提供了丰盛的剖析和优化性能，能够帮忙开发人员和数据库管理员更好地优化 MySQL 数据库的性能。

如果你的语句不对，就会收到 You have an error in your SQL syntax 的谬误揭示，比方上面这个语句 select 少打了结尾的字母“s”。

mysql> elect * from t where ID=1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1

优化器

什么是 MySQL 优化器

MySQL 优化器（MySQL Optimizer）是 MySQL 数据库中的一个组件，负责剖析和优化查问语句的执行打算，以进步查问性能。它依据查问语句的构造和表的统计信息，抉择最佳的查问执行策略和拜访门路。

MySQL 优化器的次要性能包含：

  1. 查问重写：MySQL 优化器会对查问语句进行重写，以便更好地利用索引、防止全表扫描等。它能够通过扭转查问的连贯程序、引入长期表、应用笼罩索引等形式来优化查问。
  2. 查问优化：MySQL 优化器会剖析查问语句的构造和表的统计信息，以抉择最佳的执行策略。它会思考索引的抉择、连贯类型（如嵌套循环连贯、哈希连贯、排序连贯）、排序形式、聚合操作等因素，以尽量减少查问的老本和执行工夫。
  3. 索引抉择：MySQL 优化器会依据查问条件和表的索引信息，抉择最适宜的索引来减速查问。它会思考索引的选择性、覆盖度、排序形式等因素，以进步索引的效率和查问的性能。
  4. 统计信息收集：MySQL 优化器会定期收集和更新表的统计信息，如行数、索引散布、列的基数等。这些统计信息对于优化器抉择最佳执行打算至关重要，它能够依据统计信息来预计查问的老本和抉择最佳的执行门路。

MySQL 优化器是一个智能的查问执行打算生成器，它会依据查问语句和表的信息，通过优化算法来抉择最佳的查问执行策略。但有时候，优化器的决策可能不是最优的，因而，对于特定的查问场景，可能须要手动干涉和优化查问语句、索引设计等方面。

优化器是在表外面有多个索引的时候，决定应用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连贯程序。比方你执行上面这样的语句，这个语句是执行两个表的
join：

mysql
>
select *
    from t1
             join t2 using (ID)
    where t1.c = 10
      and t2.d = 20;
  • 既能够先从表 t1 外面取出 c=10 的记录的 ID 值，再依据 ID 值关联到表 t2，再判断 t2 外面 d 的值是否等于 20。
  • 也能够先从表 t2 外面取出 d=20 的记录的 ID 值，再依据 ID 值关联到 t1，再判断 t1 外面 c 的值是否等于 10。

这两种执行办法的逻辑后果是一样的，然而执行的效率会有不同，而优化器的作用就是决定抉择应用哪一个计划。

执行器

什么是 MySQL 执行器

MySQL 执行器（MySQL Executor）是 MySQL 数据库的一个组件，负责执行查问语句并返回后果。它是 MySQL 查询处理的最初一步，负责将优化器生成的查问执行打算转换为具体的操作和后果。

MySQL 执行器的次要性能包含：

  1. 查问解析和预处理：执行器首先对查问语句进行解析，辨认查问类型、表名、列名等元素，并对其中的占位符进行替换。而后，执行器会进行查问预处理，检查和验证查问语句的语法和语义是否正确。
  2. 权限验证：在执行查问之前，执行器会进行权限验证，查看以后用户是否具备执行查问所需的权限。它会检查用户是否具备拜访特定数据库、表或列的权限，以及执行特定操作（如 SELECT、INSERT、UPDATE、DELETE）的权限。
  3. 查问执行：执行器会依据优化器生成的查问执行打算，执行具体的操作。它会依据查问的类型（如 SELECT、INSERT、UPDATE、DELETE）和所需的操作，进行表的扫描、索引的搜寻、数据的读取、批改等操作。
  4. 数据传输和后果返回：在执行查问操作时，执行器会从磁盘或内存中读取数据，并将后果传输给客户端。它负责解决查问后果的封装和传输，以及解决数据的排序、分页等需要。
  5. 事务管理：执行器还负责事务的治理，包含事务的启动、提交、回滚等操作。它会依据查问操作的须要，主动开始、提交或回滚事务，并确保查问的一致性和隔离性。

MySQL 执行器是 MySQL 查询处理的外围组件之一，负责理论执行查问操作，并将后果返回给客户端。它与优化器、存储引擎等组件协同工作，实现数据库查问的全过程。

本文由 mdnice 多平台公布

正文完
后端
发表至： 后端
2023-09-03
 0
关于后端:Python-Type-Hints-从入门到实践
深度分析java8的新特性lambda和stream流看完你学会了吗
关于后端:代码重构面向单元测试
关于后端:程序员如何用技术变现上