查看MySQL 连接信息--连接空闲时间及正在执行的SQL

MySQL 客户端与MySQL server建立连接后，就可以执行SQL语句了。

如何查看一个连接上是否正在执行SQL语句，或者连接是否处于空闲呢？

下面我们做下测试。

1.查看连接的空闲时间

首先看下测试程序。

代码中，每3s执行一条sql语句。

//conn_idle_time.go
package main

import (
        "database/sql"
        "log"
        "time"

        _ "github.com/go-sql-driver/mysql"

)

var DB *sql.DB
var dataBase = "root:Aa123456@tcp(127.0.0.1:3306)/?loc=Local&parseTime=true"

func mysqlInit() {
        var err error
        DB, err = sql.Open("mysql", dataBase)
        if err != nil {
                log.Fatalln("open db fail:", err)
        }

        DB.SetMaxOpenConns(1)

        err = DB.Ping()
        if err != nil {
                log.Fatalln("ping db fail:", err)
        }
}

func main() {
        mysqlInit()

        for {
                execSql()
                time.Sleep(3*time.Second)
        }
}


func execSql() {
        var connection_id int
        err := DB.QueryRow("select CONNECTION_ID()").Scan(&connection_id)
        if err != nil {
                log.Println("query connection id failed:", err)
                return
        }

        log.Println("connection id:", connection_id)
}

启动程序，输出结果如下：

2019/10/13 12:20:59 connection id: 5
2019/10/13 12:21:02 connection id: 5
2019/10/13 12:21:05 connection id: 5
2019/10/13 12:21:08 connection id: 5
2019/10/13 12:21:11 connection id: 5
2019/10/13 12:21:14 connection id: 5
2019/10/13 12:21:17 connection id: 5
2019/10/13 12:21:20 connection id: 5
2019/10/13 12:21:23 connection id: 5
2019/10/13 12:21:26 connection id: 5
2019/10/13 12:21:29 connection id: 5

可以看到，连接MySQL的connection id 为5。

接着，通过show processlist查看连接情况：

mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host            | db   | Command | Time | State    | Info             |
+----+------+-----------------+------+---------+------+----------+------------------+
|  3 | root | localhost       | NULL | Query   |    0 | starting | show processlist |
|  5 | root | localhost:51823 | NULL | Sleep   |    0 |          | NULL             |
+----+------+-----------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host            | db   | Command | Time | State    | Info             |
+----+------+-----------------+------+---------+------+----------+------------------+
|  3 | root | localhost       | NULL | Query   |    0 | starting | show processlist |
|  5 | root | localhost:51823 | NULL | Sleep   |    3 |          | NULL             |
+----+------+-----------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host            | db   | Command | Time | State    | Info             |
+----+------+-----------------+------+---------+------+----------+------------------+
|  3 | root | localhost       | NULL | Query   |    0 | starting | show processlist |
|  5 | root | localhost:51823 | NULL | Sleep   |    1 |          | NULL             |
+----+------+-----------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host            | db   | Command | Time | State    | Info             |
+----+------+-----------------+------+---------+------+----------+------------------+
|  3 | root | localhost       | NULL | Query   |    0 | starting | show processlist |
|  5 | root | localhost:51823 | NULL | Sleep   |    3 |          | NULL             |
+----+------+-----------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host            | db   | Command | Time | State    | Info             |
+----+------+-----------------+------+---------+------+----------+------------------+
|  3 | root | localhost       | NULL | Query   |    0 | starting | show processlist |
|  5 | root | localhost:51823 | NULL | Sleep   |    2 |          | NULL             |
+----+------+-----------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host            | db   | Command | Time | State    | Info             |
+----+------+-----------------+------+---------+------+----------+------------------+
|  3 | root | localhost       | NULL | Query   |    0 | starting | show processlist |
|  5 | root | localhost:51823 | NULL | Sleep   |    1 |          | NULL             |
+----+------+-----------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

可以看到localhost:51823是连接MySQL server使用的端口，当Command列为Sleep时，表示当前连接是空闲的，Time列显示了处于当前状态的时间。

每执行一次SQL语句，Sleep状态的Time都会被重置为0.

如果当前连接一直没有执行SQL语句，那么Sleep状态的Time会一直增加，直到达到连接最大时间(由参数wait_timeout控制)，最后连接断开。

再来看下，如何查看正在执行的SQL语句。

2.查看连接正在执行的SQL

将上面示例程序执行的SQL语句改为:

select SLEEP(10)

查看连接情况:

mysql> show processlist;
+----+------+-----------------+------+---------+------+------------+------------------+
| Id | User | Host            | db   | Command | Time | State      | Info             |
+----+------+-----------------+------+---------+------+------------+------------------+
|  3 | root | localhost       | NULL | Query   |    0 | starting   | show processlist |
|  6 | root | localhost:52186 | NULL | Query   |    1 | User sleep | select SLEEP(10) |
+----+------+-----------------+------+---------+------+------------+------------------+
2 rows in set (0.00 sec)

mysql> show processlist;
+----+------+-----------------+------+---------+------+------------+------------------+
| Id | User | Host            | db   | Command | Time | State      | Info             |
+----+------+-----------------+------+---------+------+------------+------------------+
|  3 | root | localhost       | NULL | Query   |    0 | starting   | show processlist |
|  7 | root | localhost:52200 | NULL | Query   |    3 | User sleep | select SLEEP(10) |
+----+------+-----------------+------+---------+------+------------+------------------+
2 rows in set (0.00 sec)

mysql> show processlist;
+----+------+-----------------+------+---------+------+------------+------------------+
| Id | User | Host            | db   | Command | Time | State      | Info             |
+----+------+-----------------+------+---------+------+------------+------------------+
|  3 | root | localhost       | NULL | Query   |    0 | starting   | show processlist |
|  7 | root | localhost:52200 | NULL | Query   |    7 | User sleep | select SLEEP(10) |
+----+------+-----------------+------+---------+------+------------+------------------+
2 rows in set (0.00 sec)

mysql> show processlist;
+----+------+-----------------+------+---------+------+------------+------------------+
| Id | User | Host            | db   | Command | Time | State      | Info             |
+----+------+-----------------+------+---------+------+------------+------------------+
|  3 | root | localhost       | NULL | Query   |    0 | starting   | show processlist |
|  7 | root | localhost:52200 | NULL | Query   |    8 | User sleep | select SLEEP(10) |
+----+------+-----------------+------+---------+------+------------+------------------+
2 rows in set (0.00 sec)

mysql> show processlist;
+----+------+-----------------+------+---------+------+------------+------------------+
| Id | User | Host            | db   | Command | Time | State      | Info             |
+----+------+-----------------+------+---------+------+------------+------------------+
|  3 | root | localhost       | NULL | Query   |    0 | starting   | show processlist |
|  7 | root | localhost:52200 | NULL | Query   |   10 | User sleep | select SLEEP(10) |
+----+------+-----------------+------+---------+------+------------+------------------+
2 rows in set (0.00 sec)

mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host            | db   | Command | Time | State    | Info             |
+----+------+-----------------+------+---------+------+----------+------------------+
|  3 | root | localhost       | NULL | Query   |    0 | starting | show processlist |
|  7 | root | localhost:52200 | NULL | Sleep   |   12 |          | NULL             |
+----+------+-----------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

从输出可以看到，Command为Query表示正在执行语句，Info列显示了正在执行的具体SQL语句。