mysql：慢select无需地点和连接

Question

我有下表：

| ticket | CREATE TABLE `ticket` (
  `ticket_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `client_id` binary(16) NOT NULL,
  `owner_id` binary(16) DEFAULT NULL,
  `requester_id` binary(16) NOT NULL,
  `ticket_type` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(18) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `priority` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL,
  `subject` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created` datetime(3) NOT NULL,
  `updated` datetime(3) NOT NULL,
  `eta` datetime(3) NOT NULL,
  `version` bigint(20) NOT NULL DEFAULT '0',
  `office_id` binary(16) DEFAULT NULL,
  `department_id` binary(16) DEFAULT NULL,
  `requester` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `requester_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'IPDEPLOY',
  `assignment_group_id` binary(16) DEFAULT NULL,
  PRIMARY KEY (`ticket_id`),
  KEY `idx_ticket_office_id` (`office_id`),
  KEY `idx_ticket_department_id` (`department_id`),
  KEY `idx_ticket_client_id` (`client_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3116172 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC ENCRYPTION='Y';

mysql> select count(*) from ticket;
+----------+
| count(*) |
+----------+
|  1190895 |
+----------+
mysql> SHOW TABLE STATUS WHERE name LIKE 'ticket' \G
*************************** 1. row ***************************
           Name: ticket
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1101291
 Avg_row_length: 375
    Data_length: 413089792
Max_data_length: 0
   Index_length: 153468928
      Data_free: 6291456
 Auto_increment: 3121563
    Create_time: 2022-05-18 09:51:06
    Update_time: 2022-05-19 12:06:18
     Check_time: NULL
      Collation: utf8mb4_unicode_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC ENCRYPTION="Y"
        Comment:
1 row in set (0.00 sec)

简单的查询间歇性地花费大量时间（此类查询的30％大约需要500毫秒以上（有些最多需要3秒））：

mysql> select * from ticket limit 20, 20;
...
20 rows in set (0.77 sec)

当分析时，所有时间都花在开始阶段：

mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=37 order by duration DESC limit 2 \G
*************************** 1. row ***************************
           QUERY_ID: 37
                SEQ: 2
              STATE: starting
           DURATION: 0.760068
           CPU_USER: NULL
         CPU_SYSTEM: NULL
  CONTEXT_VOLUNTARY: NULL
CONTEXT_INVOLUNTARY: NULL
       BLOCK_OPS_IN: NULL
      BLOCK_OPS_OUT: NULL
      MESSAGES_SENT: NULL
  MESSAGES_RECEIVED: NULL
  PAGE_FAULTS_MAJOR: NULL
  PAGE_FAULTS_MINOR: NULL
              SWAPS: NULL
    SOURCE_FUNCTION: NULL
        SOURCE_FILE: NULL
        SOURCE_LINE: NULL
*************************** 2. row ***************************
           QUERY_ID: 37
                SEQ: 11
              STATE: Sending data
           DURATION: 0.000234
           CPU_USER: NULL
         CPU_SYSTEM: NULL
  CONTEXT_VOLUNTARY: NULL
CONTEXT_INVOLUNTARY: NULL
       BLOCK_OPS_IN: NULL
      BLOCK_OPS_OUT: NULL
      MESSAGES_SENT: NULL
  MESSAGES_RECEIVED: NULL
  PAGE_FAULTS_MAJOR: NULL
  PAGE_FAULTS_MINOR: NULL
              SWAPS: NULL
    SOURCE_FUNCTION: exec
        SOURCE_FILE: sql_executor.cc
        SOURCE_LINE: 195

MySQL手册关于起始状态并不多：“声明执行开始时的第一阶段。”。我该如何找出MySQL在这种状态下实际在做什么？这种行为的原因可能是什么？

MySQL 5.7.27-30-57-log在Centos Linux上运行7.7.1908在Azure的容器中。 DB根本没有加载。

Innodb_buffer_pool_size设置为12G

爱意和普罗米修斯指标：

​/I.SSTATIC.NET/paspy.png“ alt =”在此处输入图像描述”>

​href =“ https://i.sstatic.net/ymqj9.png” rel =“ nofollow noreferrer”>

Answer 1

最后，我们通过将WSREP_SYNC_WAIT设置为0来解决此问题。