[MySQL] MySQL数据库中唯一标识符(ID)的梳理总结

0 引言

mysql 数据库中有各类id，在近期初步研究 flink cdc 、debezium、mysql server id、server uuid、gtid、sesion id/connection id等相关概念后，基本有了一定的认知，趁热打铁，记录总结下来。

概述

server id = debezium BinaryLogClient 的 sid

定义

server-id用于标识数据库集群实例的节点，防止在链式主从、多主多从拓扑中导致SQL语句的无限循环

配置、查看、使用

[数据库] 浅谈mysql的serverId/serverUuid - 博客园/千千寰宇

server uuid

定义

server-uuid参数并不能取代server-id，他们有不同的作用。当主从同步时，如果主从实例的server-uuid相同会报错退出

server-uuid 与 gtid 的关系 : gtid = {server-uuid}:{transaction-id}

配置、查看、使用

[数据库] 浅谈mysql的serverId/serverUuid - 博客园/千千寰宇

transaction id = Xid = 事务ID

gtid = `{server uuid}:{transaction id}`

定义

GTID，又叫全局事务ID(Global Transaction ID)

用于取代过去传统的主从复制（即：基于binlog和position的异步复制）。

查看、使用

[数据库] MYSQL之binlog概述 - 博客园/千千寰宇

connection id = information_schema.processlist.id = session id = debezium BinaryLogClient 的 cid = debezium BinaryLogClient 的 thread id ≠ MYSQL `THREADS`表的 thread_id

定义

数据库连接ID(connection id) = 会话ID(session id)

= information_schema.processlist.id (可理解为：对外的线程ID、面向用户的线程ID)

= show processlist.id

= performance_schema.threads.processlist_id

= debezium BinaryLogClient 的 cid

= debezium BinaryLogClient 的 thread id

≠ MYSQL THREADS表的 thread_id (可理解为：对内的MYSQL服务层面的内部线程ID，基于 threads 表关联 processlist_id、mysql os thread id)

connection_id = session_id = PROCESSLIST_ID

> mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              15           |
+-----------------+
1 row in set (0.00 sec)


mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host            | db   | Command | Time | State    | Info             |
+----+------+-----------------+------+---------+------+----------+------------------+
|  3 | root | localhost:60989 | cmdb | Sleep   |  318 |          | NULL             |
|  4 | root | localhost:60990 | cmdb | Sleep   |  318 |          | NULL             |
| 15 | root | localhost       | NULL | Query   |    0 | starting | show processlist |
+----+------+-----------------+------+---------+------+----------+------------------+
3 rows in set (0.00 sec)
...

mysql> select * from  information_schema.processlist;
+----+------+-----------------+------+---------+------+-----------+-----------------------------------------------+
| ID | USER | HOST            | DB   | COMMAND | TIME | STATE     | INFO                                          |
+----+------+-----------------+------+---------+------+-----------+-----------------------------------------------+
|  3 | root | localhost:60989 | cmdb | Sleep   |  157 |           | NULL                                          |
| 15 | root | localhost       | NULL | Query   |    0 | executing | select * from  INFORMATION_SCHEMA.PROCESSLIST |
|  4 | root | localhost:60990 | cmdb | Sleep   |  157 |           | NULL                                          |
+----+------+-----------------+------+---------+------+-----------+-----------------------------------------------+
3 rows in set (0.00 sec)

mysql> select thread_id, name, processlist_id from performance_schema.threads;
+-----------+----------------------------------------+----------------+
| thread_id | name                                   | processlist_id |
+-----------+----------------------------------------+----------------+
|         1 | thread/sql/main                        |           NULL |
|         2 | thread/sql/thread_timer_notifier       |           NULL |
|         3 | thread/innodb/io_ibuf_thread           |           NULL |
|         4 | thread/innodb/io_log_thread            |           NULL |
|         5 | thread/innodb/io_read_thread           |           NULL |
|         6 | thread/innodb/io_read_thread           |           NULL |
|         7 | thread/innodb/io_read_thread           |           NULL |
|         8 | thread/innodb/io_read_thread           |           NULL |
|         9 | thread/innodb/io_write_thread          |           NULL |
|        10 | thread/innodb/io_write_thread          |           NULL |
|        11 | thread/innodb/io_write_thread          |           NULL |
|        12 | thread/innodb/io_write_thread          |           NULL |
|        13 | thread/innodb/page_cleaner_thread      |           NULL |
|        16 | thread/innodb/srv_lock_timeout_thread  |           NULL |
|        15 | thread/innodb/srv_error_monitor_thread |           NULL |
|        17 | thread/innodb/srv_monitor_thread       |           NULL |
|        18 | thread/innodb/srv_master_thread        |           NULL |
|        19 | thread/innodb/srv_worker_thread        |           NULL |
|        20 | thread/innodb/srv_worker_thread        |           NULL |
|        21 | thread/innodb/srv_purge_thread         |           NULL |
|        22 | thread/innodb/srv_worker_thread        |           NULL |
|        23 | thread/innodb/buf_dump_thread          |           NULL |
|        24 | thread/innodb/dict_stats_thread        |           NULL |
|        25 | thread/sql/signal_handler              |           NULL |
|        26 | thread/sql/compress_gtid_table         |              1 |
|        28 | thread/sql/one_connection              |              3 |
|        29 | thread/sql/one_connection              |              4 |
|        40 | thread/sql/one_connection              |             15 |
+-----------+----------------------------------------+----------------+
28 rows in set (0.00 sec)

PROCESSLIST_ID 即 debezium Client 内的 GreetingPacket.getThreadId()

//com.github.shyiko.mysql.binlog.BinaryLogClient | debezium

this.connectionId = greetingPacket.getThreadId(); // 即 PROCESSLIST_ID / SELECT CONNECTION_ID()

查看、使用

使用SHOW PROCESSLIST命令 | [数据库] 排查MySQL锁表情况及解决思路 - 博客园/千千寰宇

MYSQL THREAD ID = MYSQL服务的内部线程ID ≠ mysql connection (但存强关联关系)

Mysql如何处理client请求? thread id 关联 connection id (processlist id)

在Mysql中，连接管理线程(Connection manager threads)用于处理来自客户端的TCP/IP连接请求，它会将每个client connection关联到一个专门的 mysql thread
这个 thread 负责处理通过connection发出的所有请求(也包含请求的安全认证)。

THREAD_ID 的源码实现分析

performance_schema 里的一个内部计数器

//reference-doc : https://www.jianshu.com/p/1c6f7459ffed

PFS_thread* create_thread(PFS_thread_class *klass, const void *identity,
                          ulonglong processlist_id)
{
  PFS_thread *pfs;
  pfs_dirty_state dirty_state;

  pfs= global_thread_container.allocate(& dirty_state);
  if (pfs != NULL)
  {
    pfs->m_thread_internal_id=
      PFS_atomic::add_u64(&thread_internal_id_counter.m_u64, 1);
    pfs->m_parent_thread_internal_id= 0;
    pfs->m_processlist_id= static_cast<ulong>(processlist_id);
...

thread_internal_id_counter计数器一直增加。
可以看到这是performance_schema内部使用的。

Mysql thread : 与 MYSQL OS thread 、 MySQL connection 的关系

Mysql thread 与 OS thread : 存在对应多对一的关联关系

每个mysql threard将与一个os thread关联在一起，mysql thread销毁后，os thread不会被销毁，可以继续给其他mysql thread使用

如果所有os thread都被mysql thread用光了，下一个connection请求时将会创建新的os thread

mysql thread并非操作系统上的真实线程，只是mysql中的一个对象，但是会与os thread(操作系统的真实线程)关联起来。

使用一个connection连接、并按顺序执行几个事务，会使用不同的os thread来进行处理；当然，也可能是同一个os thread

如果只有一个os thread可用，所有事务会有同一个os thread处理；如果有多个os thread可用，将会轮换使用不同的os thread

如果kill掉任意一个os thread，mysql server将会shutdown。

因此，不要尝试kill掉某个事务对应的os thread，毫无意义，mysql 重新启动后，原先的事务该干的事还是得一样干完，以保证事务的ACID特性。

如果你kill mysql thread，将不会影响到实际的os thread，但是该mysql thread将被删除。

假如你正在执行一个 insert 事务且已经插入了100W数据，如果kill mysql thread，事务对应的trx_mysql_thread_id会设置为0，然后事务开始执行rollback

MySQL Thread Id ：对应关联 processlist.id/MySQL connection ID，生命周期基本一致

MYSQL线程模型 = 为每个连接(connection)分配一个mysql thread

mysql会为每个connection创建一个对应mysql thread，连接关闭后，mysql thread生命周期也终止。这个mysql thread可以在processlist、threads表中查看

如果当前连接正在进行事务处理，也可以通过 information_schema.innodb_trx 事务表查看，其中trx_mysql_thread_id字段为mysql thread id，与processlist中id一致
最简单的方式：使用 show processlist 查看当前连接，这个命令与查看information_schema.processlist表效果一致。
每一个连接进来时，都可以在看到一条新的记录。可以通过不断打开与关闭连接来测试，每次打开一个连接后在 processlist 中将会多出一条记录，连接关闭后这条记录也会被移除。

trx_mysql_thread_id = processlist.id = 执行事务的前台线程id = 会话ID = performance_schema.thread.processlistid

PERFORMANCE_SCHEMA.THREADS表 : 关联 processlist_id、mysql os thread id

> select THREAD_ID,PROCESSLIST_ID,THREAD_OS_ID from performance_schema.threads;
+-----------+----------------+--------------+
| THREAD_ID | PROCESSLIST_ID | THREAD_OS_ID |
+-----------+----------------+--------------+
|         1 |           NULL |        17721 |
|         2 |           NULL |        17728 |
|         3 |           NULL |        17729 |
|         4 |           NULL |        17730 |
|         5 |           NULL |        17731 |
|         6 |           NULL |        17732 |
|         7 |           NULL |        17733 |
|         8 |           NULL |        17734 |
|         9 |           NULL |        17735 |
|        10 |           NULL |        17736 |
|        11 |           NULL |        17737 |
|        12 |           NULL |        17738 |
|        13 |           NULL |        17739 |
|        15 |           NULL |        17743 |
|        16 |           NULL |        17744 |
|        17 |           NULL |        17745 |
|        18 |           NULL |        17746 |
|        19 |           NULL |        17747 |
|        20 |           NULL |        17749 |
|        21 |           NULL |        17748 |
|        22 |           NULL |        17750 |
|        23 |           NULL |        17751 |
|        24 |           NULL |        17752 |
|        25 |           NULL |        17763 |
|        26 |              1 |        17764 |
|        27 |              2 |        17969 |
|        44 |             19 |        31424 |
+-----------+----------------+--------------+

MYSQL OS Thread ID = 操作系统的线程ID = 对应关联 N 个 THREAD_ID

Mysql OS thread(thread_os_id) = 操作系统线程ID = ps -ef的mysql线程号

与MySQL Thread : 存在一对多关系

虽然每次打开连接，可以看到一个新的 mysql thread 产生，但是OS并不一定会为每个连接创建一个新的os thread。

如果mysql thread在生命周期中与一个os thread关联，thread_os_id字段将包含os thread ID

如果mysql thread在生命周期中没有和os thread关联，thread_os_id将为NULL

首先，看看mysqld进程对应的所有os线程。执行命令 ps -ef | grep mysql 得到mysqld的PID(进程ID)为 16286

999 16286 16267 4 22:39 ? 00:00:00 mysqld
ubuntu 16362 9909 0 22:39 pts/1 00:00:00 grep --color=auto mysql

操作系统中mysql os thread id

在windows下，thread_os_id可以在任务管理器中看到；
在linux下，thread_os_id和gettid()方法对应，可以使用perl、ps -L命令、或使用proc文件系统(/proc/[pid]/task/[tid])；

补充：MYSQL 表的ID

performance_schema.threads 表

> select * from performance_schema.threads
THREAD_ID|NAME|TYPE|PROCESSLIST_ID|PROCESSLIST_USER|PROCESSLIST_HOST|PROCESSLIST_DB|PROCESSLIST_COMMAND|PROCESSLIST_TIME|PROCESSLIST_STATE|PROCESSLIST_INFO|PARENT_THREAD_ID|ROLE|INSTRUMENTED|HISTORY|CONNECTION_TYPE|THREAD_OS_ID|
---------+----+----+--------------+----------------+----------------+--------------+-------------------+----------------+-----------------+----------------+----------------+----+------------+-------+---------------+------------+

THREAD_ID

PROCESSLIST_ID

THREAD_OS_ID

information_schema.innodb_trx 表

> select * from information_schema.innodb_trx
trx_id|trx_state|trx_started|trx_requested_lock_id|trx_wait_started|trx_weight|trx_mysql_thread_id|trx_query|trx_operation_state|trx_tables_in_use|trx_tables_locked|trx_lock_structs|trx_lock_memory_bytes|trx_rows_locked|trx_rows_modified|trx_concurrency_tickets|trx_isolation_level|trx_unique_checks|trx_foreign_key_checks|trx_last_foreign_key_error|trx_adaptive_hash_latched|trx_adaptive_hash_timeout|trx_is_read_only|trx_autocommit_non_locking|trx_waited_time|
------+---------+-----------+---------------------+----------------+----------+-------------------+---------+-------------------+-----------------+-----------------+----------------+---------------------+---------------+-----------------+-----------------------+-------------------+-----------------+----------------------+--------------------------+-------------------------+-------------------------+----------------+--------------------------+---------------+

trx_mysql_thread_id

trx_id

Y 推荐文献

X 参考文献

posted @ 2024-10-18 00:27 千千寰宇阅读(127) 评论(0) 编辑收藏举报

刷新页面返回顶部

千千寰宇

大数据与Java软件开发从业者，数智化转型实践者。

[MySQL] MySQL数据库中唯一标识符(ID)的梳理总结

0 引言

概述

server id = debezium BinaryLogClient 的 sid

server uuid

transaction id = Xid = 事务ID

gtid = `{server uuid}:{transaction id}`

connection id = information_schema.processlist.id = session id = debezium BinaryLogClient 的 cid = debezium BinaryLogClient 的 thread id ≠ MYSQL `THREADS`表的 thread_id

MYSQL THREAD ID = MYSQL服务的内部线程ID ≠ mysql connection (但存强关联关系)

MYSQL OS Thread ID = 操作系统的线程ID = 对应关联 N 个 THREAD_ID

补充：MYSQL 表的ID

performance_schema.threads 表

THREAD_ID

PROCESSLIST_ID

THREAD_OS_ID

information_schema.innodb_trx 表

trx_mysql_thread_id

trx_id

Y 推荐文献

X 参考文献

公告

千千寰宇

大数据与Java软件开发从业者，数智化转型实践者。

[MySQL] MySQL数据库中唯一标识符(ID)的梳理总结

0 引言

概述

server id = debezium BinaryLogClient 的 sid

server uuid

transaction id = Xid = 事务ID

gtid = {server uuid}:{transaction id}

connection id = information_schema.processlist.id = session id = debezium BinaryLogClient 的 cid = debezium BinaryLogClient 的 thread id ≠ MYSQL THREADS表 的 thread_id

MYSQL THREAD ID = MYSQL服务的内部线程ID ≠ mysql connection (但存强关联关系)

MYSQL OS Thread ID = 操作系统的线程ID = 对应关联 N 个 THREAD_ID

补充：MYSQL 表的ID

performance_schema.threads 表

THREAD_ID

PROCESSLIST_ID

THREAD_OS_ID

information_schema.innodb_trx 表

trx_mysql_thread_id

trx_id

Y 推荐文献

X 参考文献

公告

gtid = `{server uuid}:{transaction id}`

connection id = information_schema.processlist.id = session id = debezium BinaryLogClient 的 cid = debezium BinaryLogClient 的 thread id ≠ MYSQL `THREADS`表的 thread_id