【Mysql系列】（一）MySQL语句执行流程

2023-09-03 08:34:56 浏览数 (1)

MySQL 逻辑架构

连接器

连接命令一般是这么写的

代码语言：javascript复制
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 数据库的示例：

代码语言：javascript复制
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 -hip -Pport -uuser -p 是一个连接到 MySQL 服务器的命令，其中 ip 是要连接的 MySQL 服务器的主机名或 IP 地址，port 是 MySQL 服务器的端口号，user 是要连接的用户名，-p 表示需要输入密码。

下面是使用 mysql -hip -Pport -u

  1. 当我们在终端或命令提示符中输入 mysql -hip -Pport -u
  2. 操作系统会在系统的可执行程序路径中搜索 mysql 可执行文件，并找到它。
  3. 执行 mysql 可执行文件后，会启动一个 MySQL 客户端程序。这个程序是用 C 或 C 编写的，负责与 MySQL 服务器进行通信。
  4. MySQL 客户端程序会解析命令行参数，并找到 -hip 参数指定的主机名或 IP 地址，-Pport 参数指定的端口号，-u
  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 -hip -Pport -u

这里需要注意的有两点：

  1. 不要直接将密码跟在-p 命令后面，不安全
  2. **一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置 **

如何查看连接状态

要查看当前连接到 MySQL 服务器的会话和连接信息，可以使用以下方法：

通过 MySQL 命令行客户端：在命令行中输入 mysql 命令，连接到 MySQL 服务器。然后使用以下命令查看连接信息：

代码语言：javascript复制
SHOW PROCESSLIST;

这个命令将显示当前所有活动的 MySQL 连接和会话的详细信息，包括连接的 ID、主机名、用户、执行的 SQL 语句等。

通过 MySQL 管理工具：如果使用 MySQL 的管理工具，如 phpMyAdmin、MySQL Workbench 等，可以使用这些工具提供的界面来查看连接信息。通常会有一个“当前连接”或类似的选项，点击即可显示当前连接的详细信息。

通过查询系统表：可以使用以下 SQL 查询语句查看连接信息：

代码语言：javascript复制
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 查询缓存的缓存失效频率较高。**一旦对某个表进行了更新操作，与该表相关的缓存都会被清空，需要重新执行查询 **。这导致缓存的命中率较低，而缓存失效的开销却很高。

内存消耗和不可扩展：查询缓存需要占用一定的内存空间来存储缓存结果，如果缓存的数据量较大，可能会占用大量的内存资源。而且，查询缓存是基于查询语句进行缓存的，对于相同的查询语句，即使查询结果不同，也会使用相同的缓存空间。这导致了内存的浪费，并且不适用于大规模数据库和高并发环境。

  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”。

代码语言：javascript复制
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：

代码语言：javascript复制
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 多平台发布

数据库 缓存 mysql 连接 优化

0 人点赞