mysql innodb存储引擎的sql查询、更新的执行过程

一、建立连接与查询

1、应用程序与mysql服务端建立连接就需要用到驱动程序，在java应用程序里可通过mysql-connector-java驱动来进行连接，这个驱动会在底层与数据库进行网络连接，如我们在代码中常常会引入这个驱动包：

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>8.0.21</version>
</dependency>

2、建立连接执行查询

package com.zcj.study.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * @author : zcj
* @description : jdbc 连接测试
* @date : 2019/12/5 11:53
*/
public class MysqlUtil {
    private static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String JDBC_URL = "jdbc:mysql://192.168.198.130:3306/test?useSSL=false&serverTimezone=UTC";
    private static final String JDBC_USERNAME = "root";
    private static final String JDBC_PASSWORD = "root123";

    public static void main(String[] args) throws Exception {
        //加载驱动
        Class.forName(JDBC_DRIVER);
        //建立连接
        Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD);
        //创建Statement对象
        Statement statement = connection.createStatement();
        String sql = "select * from user";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            System.out.println("id=" + resultSet.getInt(1) + ",username=" + resultSet.getString(2)
                    + ",password=" + resultSet.getString(3));
        }
        
        //关闭资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

3、查询结果

二、通过连接池实现连接的复用、减少连接的创建以及销毁频率

在应用程序中，常常都是多个线程并发请求数据，如果只有一个数据库连接，多线程争抢资源效率肯定是低下的。而如果每个线程都如上面例子去创建一个连接，执行完又关闭，那也会非常的低效。

通常都是通过连接池，维持多个数据库连接，让线程使用连接结束后放回连接池重复利用。常见的连接池有DBCP/DBCP2、C3P0、Druid、Hikari，其中Druid带有监控功能，而Hikari居于号称性能最好的连接池。不过平常使用的时候一般瓶颈都不在连接池，可以按需选择。

三、进一步了解sql查询过程

通常我们接下来可能就是建表、编写sql语句查询，如果执行慢就去创建索引。知道insert会增加一条数据，delete会删除数据，而对于sql语句的执行过程了解的并不够：

1. 连接池只是负责建立维护连接，每个客户端连接还需要分配一个工作线程去监听网络请求以及读取数据，也就是从网络连接中读取和解析出sql语句

2. 线程解析出sql后交由sql接口进行处理，sql接口会包含解析器、查询优化器、执行计划等：

   a、sql接口接收到sql语句后，首先需要解析sql，按照既定的语法对sql语句进行解析，理解sql语句想要做什么；

   b、接着由查询优化器分析sql生成查询路径树，选择最优查询路径，比如是否需要走索引查询、是否需要回表查询等；

   c、最后就是通过执行器调用存储引擎接口执行sql语句，存储引擎接口会按一定步骤去查询内存缓存、更新磁盘数据、查询磁盘数据等等

在mysql中，sql接口、sql解析器、查询优化器都是通用的，而存储引擎就有多种，如innodb、myisam、memory等。目前我们常用的是innodb（支持事务）

四、innodb更新的执行过程

首先mysql的增删改查都是发生在buffer pool内存缓冲池中，buffer pool是innodb中的一个核心组件

1. 根据条件判断待更新的数据是否存在于buffer pool（如果不存在，则从磁盘文件中加载进buffer pool中），对该条数据进行加锁

2. 把待更新的旧值写入到undo log日志文件中，以便于在事务内发生异常回滚

3. 更新buffer pool中的数据，通常把内存中更新后数据成为脏数据（因为此时buffer pool中的值与磁盘上的数据是不一致的）

4. 将更新日志写入redo log buffer（内存缓冲区）中

5. 提交事务时根据策略把redo log buffer中日志顺序刷盘，这个策略由innodb_flush_log_at_tx_commit配置项来控制：

   a、设置为0时，提交事务时redo log buffer中数据不会刷盘，此时如果宕机数据会丢失；

   b、设置为1时，提交事务需要将redo log刷入磁盘文件中，如果此时宕机，在重启后会把redo log数据写回buffer pool；

   c、设置为2时，在提交事务时，把redo log buffer的日志写入os cache中，由os cache控制写入磁盘，如果宕机可能会丢失一些短时间的数据

6. 当binlog日志打开时，在事务提交时也会写入binlog日志，写binlog日志时也有刷盘策略，由配置项sync_binlog控制：

   a、设置为0时，binlog日志进入的是os cache内存缓存，由文件系统控制缓存刷盘时机

   b、设置为1时，在提交事务时会强制写入binlog日志

7. 后台线程在适当时间会把buffer pool中的脏数据刷回磁盘

一般来说，对于关系型数据mysql，都建议innodb_flush_log_at_tx_commit设置为1，提交事务即写入日志文件，因为是顺序写盘，性能并不比随机写内存差。