session,x$session - xiaoboluo768/mysql-system-schema GitHub Wiki

• 查看当前用户会话的进程列表信息，与processlist&x$processlist视图类似，但是session视图过滤掉了后台线程，只显示前台(用户)线程相关的统计数据，数据来源：sys.processlist

  • 该视图在MySQL 5.7.9中新增

• session,x$session字段含义与processlist,x$processlist视图相同，详见 2.3.22. processlist,x$processlist

• 视图定义语句

# session
CREATE OR REPLACE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW session
AS
SELECT * FROM sys.processlist
WHERE conn_id IS NOT NULL AND command != 'Daemon';

# x$session
CREATE OR REPLACE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW x$session
AS
SELECT * FROM sys.x$processlist
WHERE conn_id IS NOT NULL AND command != 'Daemon';

• 视图查询信息示例

admin@localhost : sys 12:44:22> select * from session where command='query' and conn_id!=connection_id()\G;
*************************** 1. row ***************************
                thd_id: 48
              conn_id: 6
                  user: admin@localhost
                    db: xiaoboluo
              command: Query
                state: Sending data
                  time: 72
    current_statement: select * from test limit 1 for update
    statement_latency: 1.20 m
              progress: NULL
          lock_latency: 169.00 us
        rows_examined: 0
            rows_sent: 0
        rows_affected: 0
            tmp_tables: 0
      tmp_disk_tables: 0
            full_scan: NO
        last_statement: NULL
last_statement_latency: NULL
        current_memory: 461 bytes
            last_wait: wait/io/table/sql/handler
    last_wait_latency: Still Waiting
                source: handler.cc:3185
          trx_latency: NULL
            trx_state: NULL
        trx_autocommit: NULL
                  pid: 3788
          program_name: mysql
1 row in set (0.15 sec)

ERROR: 
No query specified

admin@localhost : sys 12:45:09> select * from x$session where command='query' and conn_id!=connection_id()\G;
*************************** 1. row ***************************
                thd_id: 48
              conn_id: 6
                  user: admin@localhost
                    db: xiaoboluo
              command: Query
                state: Sending data
                  time: 91
    current_statement: select * from test limit 1 for update
    statement_latency: 91077336919000
              progress: NULL
          lock_latency: 169000000
        rows_examined: 0
            rows_sent: 0
        rows_affected: 0
            tmp_tables: 0
      tmp_disk_tables: 0
            full_scan: NO
        last_statement: NULL
last_statement_latency: NULL
        current_memory: 461
            last_wait: wait/io/table/sql/handler
    last_wait_latency: Still Waiting
                source: handler.cc:3185
          trx_latency: NULL
            trx_state: NULL
        trx_autocommit: NULL
                  pid: 3788
          program_name: mysql
1 row in set (0.13 sec)

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-session.html

---

上一篇： schema_unused_indexes视图 | 下一篇： session_ssl_status视图