[已解决] datetime+varchar两个独立索引后查询卡主了

2658 1 2022-11-20 15:19

本帖最后由 fuhao009 于 2022-11-21 16:51 编辑

greatsql8.0.25-16
cpu: 32
内存: 64G

-----------strace日志---------------：

17919 14:40:16.961052 futex(0x7fde3a06e0fc, FUTEX_WAIT_PRIVATE, 29, NULL <unfinished ...>
17918 14:40:16.961187 futex(0x7fde3a06e77c, FUTEX_WAIT_PRIVATE, 5, NULL <unfinished ...>
17916 14:40:16.961213 futex(0x3d80184, FUTEX_WAIT_PRIVATE, 34, NULL <unfinished ...>
17892 14:40:16.961226 restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
17891 14:40:16.961242 restart_syscall(<... resuming interrupted poll ...> <unfinished ...>
17890 14:40:16.961255 futex(0x7fd55c03d93c, FUTEX_WAIT_PRIVATE, 1, NULL <unfinished ...>
17889 14:40:16.961267 futex(0x7fd52ccbb3f4, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17888 14:40:16.961279 futex(0x7fd52cc56e84, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17887 14:40:16.961291 futex(0x7fd52cb92664, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17886 14:40:16.961303 futex(0x7fd52cb8e214, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17885 14:40:16.961314 futex(0x7fd52cb29be4, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17884 14:40:16.961326 futex(0x7fd52cac5574, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17883 14:40:16.961338 futex(0x7fd52ca61004, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17882 14:40:16.961348 futex(0x7fd52c99c7e4, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17881 14:40:16.961358 futex(0x7fd52c9760c4, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17880 14:40:16.961369 futex(0x7fd52c90d1f4, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17879 14:40:16.961378 futex(0x7fd52c8a8bc4, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17878 14:40:16.961388 futex(0x7fd52c844554, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17877 14:40:16.961398 futex(0x7fd52c7dffe4, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17876 14:40:16.961407 futex(0x7fd52c71b7c4, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17875 14:40:16.961417 futex(0x7fd52c717334, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17874 14:40:16.961426 futex(0x7fd52c6b2dc4, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17873 14:40:16.961436 futex(0x7fd52c5d78f4, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17872 14:40:16.961446 futex(0x7fd52c5d3464, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17871 14:40:16.961457 futex(0x7fd52c56eef4, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17870 14:40:16.961467 futex(0x7fd52c4aa704, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17869 14:40:16.961479 futex(0x7fd52c4a62d4, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17868 14:40:16.961489 futex(0x7fd52c3e1b04, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17867 14:40:16.961500 futex(0x7fd52c3ce364, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17866 14:40:16.961509 futex(0x7fd52c369d54, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17865 14:40:16.961519 futex(0x7fd52c305744, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17864 14:40:16.961529 futex(0x7fd52c2a10f4, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17863 14:40:16.961538 futex(0x7fd52c232ae4, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17862 14:40:16.961556 futex(0x7fd52c1ce1a4, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17861 14:40:16.961569 futex(0x7fd52c162b64, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17860 14:40:16.961579 futex(0x7fd52c0995f4, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17859 14:40:16.961590 futex(0x7fd52c095664, FUTEX_WAIT_PRIVATE, 3, NULL <unfinished ...>
17858 14:40:16.961600 futex(0x7fd52c02a5b4, FUTEX_WAIT_PRIVATE, 13, NULL <unfinished ...>
17857 14:40:16.961612 restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
17856 14:40:16.961624 restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
17855 14:40:16.961636 futex(0x7fd55c38647c, FUTEX_WAIT_PRIVATE, 6455, NULL <unfinished ...>
17854 14:40:16.961648 futex(0x7fd55c383c04, FUTEX_WAIT_PRIVATE, 12209, NULL <unfinished ...>
17853 14:40:16.961660 futex(0x7fd55c169a14, FUTEX_WAIT_PRIVATE, 1, NULL <unfinished ...>
17851 14:40:16.961671 futex(0x3d80184, FUTEX_WAIT_PRIVATE, 34, NULL <unfinished ...>
17850 14:40:16.961683 futex(0x3d80184, FUTEX_WAIT_PRIVATE, 34, NULL <unfinished ...>
17722 14:40:16.961694 futex(0x3d80184, FUTEX_WAIT_PRIVATE, 34, NULL <unfinished ...>
17654 14:40:16.961705 futex(0x3ce7ac4, FUTEX_WAIT_PRIVATE, 11, NULL <unfinished ...>
17653 14:40:16.961722 rt_sigtimedwait([HUP QUIT USR1 USR2 TERM],  <unfinished ...>
17652 14:40:16.961773 futex(0x7838274, FUTEX_WAIT_PRIVATE, 1, NULL <unfinished ...>
17651 14:40:16.961787 futex(0x7fde380253ec, FUTEX_WAIT_PRIVATE, 4513, NULL <unfinished ...>
17650 14:40:16.961798 futex(0x7fde3802534c, FUTEX_WAIT_PRIVATE, 5133, NULL <unfinished ...>
17649 14:40:16.961810 futex(0x7fde380252ac, FUTEX_WAIT_PRIVATE, 7539, NULL <unfinished ...>
17648 14:40:16.961819 futex(0x7fde3802520c, FUTEX_WAIT_PRIVATE, 5539, NULL <unfinished ...>
17647 14:40:16.961832 restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
17646 14:40:16.961843 futex(0x7fde380255cc, FUTEX_WAIT_PRIVATE, 1, NULL <unfinished ...>
17641 14:40:16.961855 restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
17640 14:40:16.961870 restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
17639 14:40:16.961885 restart_syscall(<... resuming interrupted nanosleep ...> <unfinished ...>
17638 14:40:16.961895 futex(0x7fde380258ec, FUTEX_WAIT_PRIVATE, 1, NULL <unfinished ...>
17637 14:40:16.961907 restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
17636 14:40:16.961918 restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
17635 14:40:16.961930 restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
17634 14:40:16.961941 restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
17633 14:40:16.961950 restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
17632 14:40:16.961962 restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
17631 14:40:16.961975 restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
17630 14:40:16.961986 restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
17629 14:40:16.961998 restart_syscall(<... resuming interrupted nanosleep ...> <unfinished ...>
17628 14:40:16.962009 futex(0x7fde383ff8cc, FUTEX_WAIT_PRIVATE, 531, NULL <unfinished ...>
17627 14:40:16.962019 restart_syscall(<... resuming interrupted nanosleep ...> <unfinished ...>
17626 14:40:16.962031 futex(0x7fde383ff8cc, FUTEX_WAIT_PRIVATE, 531, NULL <unfinished ...>
17625 14:40:16.962043 restart_syscall(<... resuming interrupted nanosleep ...> <unfinished ...>
17624 14:40:16.962055 restart_syscall(<... resuming interrupted nanosleep ...> <unfinished ...>
17623 14:40:16.962067 futex(0x7fde383ff8cc, FUTEX_WAIT_PRIVATE, 531, NULL <unfinished ...>
17622 14:40:16.962077 restart_syscall(<... resuming interrupted nanosleep ...> <unfinished ...>
17621 14:40:16.962089 restart_syscall(<... resuming interrupted nanosleep ...> <unfinished ...>
17620 14:40:16.962102 restart_syscall(<... resuming interrupted nanosleep ...> <unfinished ...>
17619 14:40:16.962114 restart_syscall(<... resuming interrupted nanosleep ...> <unfinished ...>
17618 14:40:16.962129 restart_syscall(<... resuming interrupted futex ...> <unfinished ...>
17617 14:40:16.962141 io_getevents(0x7fde3c498000, 1, 256,  <unfinished ...>
17616 14:40:16.962153 io_getevents(0x7fde3c49b000, 1, 256,  <unfinished ...>
17615 14:40:16.962165 io_getevents(0x7fde3c49e000, 1, 256,  <unfinished ...>
17614 14:40:16.962177 io_getevents(0x7fde4ca5e000, 1, 256,  <unfinished ...>
17613 14:40:16.962189 io_getevents(0x7fde4ca67000, 1, 256,  <unfinished ...>
17612 14:40:16.962206 io_getevents(0x7fde4ca6a000, 1, 256,  <unfinished ...>
17611 14:40:16.962218 io_getevents(0x7fde4ca6d000, 1, 256,  <unfinished ...>
17610 14:40:16.962231 io_getevents(0x7fde4cc57000, 1, 256,  <unfinished ...>
17609 14:40:16.962241 io_getevents(0x7fde4ca61000, 1, 256,  <unfinished ...>
17608 14:40:16.962254 io_getevents(0x7fde4ca64000, 1, 256,  <unfinished ...>
17598 14:40:16.962264 restart_syscall(<... resuming interrupted restart_syscall ...> <unfinished ...>
17891 14:40:16.962283 <... restart_syscall resumed>) = 0 <0.001033>
17857 14:40:16.962298 <... restart_syscall resumed>) = -1 ETIMEDOUT (Connection timed out) <0.000679>
17891 14:40:16.962408 poll([{fd=53, events=POLLIN|POLLRDNORM}, {fd=41, events=POLLIN|POLLRDNORM}, {fd=52, events=POLLIN|POLLRDNORM}, {fd=43, events=POLLIN|POLLRDNORM}, {fd=51, events=POLLIN|POLLRDNORM}, {fd=54, events=POLLIN|POLLRDNORM}], 6, 2 <unfinished ...>
17857 14:40:16.962455 futex(0x7fd55c019c00, FUTEX_WAKE_PRIVATE, 1) = 0 <0.000040>
17857 14:40:16.962560 futex(0x7fd55c019c64, FUTEX_WAIT_BITSET_PRIVATE|FUTEX_CLOCK_REALTIME, 1041165, {tv_sec=1668926416, tv_nsec=964535500}, 0xffffffff <unfinished ...>
17891 14:40:16.964571 <... poll resumed>) = 0 (Timeout) <0.002119>
17857 14:40:16.964613 <... futex resumed>) = -1 ETIMEDOUT (Connection timed out) <0.002010>
17891 14:40:16.964634 poll([{fd=53, events=POLLIN|POLLRDNORM}, {fd=41, events=POLLIN|POLLRDNORM}, {fd=52, events=POLLIN|POLLRDNORM}, {fd=43, events=POLLIN|POLLRDNORM}, {fd=51, events=POLLIN|POLLRDNORM}, {fd=54, events=POLLIN|POLLRDNORM}], 6, 2 <unfinished ...>
17857 14:40:16.964665 futex(0x7fd55c019c00, FUTEX_WAKE_PRIVATE, 1) = 0 <0.000029>
17857 14:40:16.964733 futex(0x7fd55c019c64, FUTEX_WAIT_BITSET_PRIVATE|FUTEX_CLOCK_REALTIME, 1041167, {tv_sec=1668926416, tv_nsec=966713700}, 0xffffffff <unfinished ...>
17891 14:40:16.966755 <... poll resumed>) = 0 (Timeout) <0.002097>
17891 14:40:16.966797 poll([{fd=53, events=POLLIN|POLLRDNORM}, {fd=41, events=POLLIN|POLLRDNORM}, {fd=52, events=POLLIN|POLLRDNORM}, {fd=43, events=POLLIN|POLLRDNORM}, {fd=51, events=POLLIN|POLLRDNORM}, {fd=54, events=POLLIN|POLLRDNORM}], 6, 2 <unfinished ...>
17857 14:40:16.966820 <... futex resumed>) = -1 ETIMEDOUT (Connection timed out) <0.002075>
17857 14:40:16.966857 futex(0x7fd55c019c00, FUTEX_WAKE_PRIVATE, 1) = 0 <0.000026>
17857 14:40:16.966914 futex(0x7fd55c019c64, FUTEX_WAIT_BITSET_PRIVATE|FUTEX_CLOCK_REALTIME, 1041169, {tv_sec=1668926416, tv_nsec=968903500}, 0xffffffff <unfinished ...>
17856 14:40:16.968263 <... restart_syscall resumed>) = -1 ETIMEDOUT (Connection timed out) <0.006632>
17856 14:40:16.968305 futex(0x7fd55c387418, FUTEX_WAKE_PRIVATE, 1) = 0 <0.000021>
17856 14:40:16.968357 access("/mjxt/mysql/", W_OK) = 0 <0.000025>
17856 14:40:16.968430 open("/mjxt/mysql/DISK_CHECKWR38R8", O_RDWR|O_CREAT|O_EXCL, 0600) = 49 <0.000051>
17856 14:40:16.968520 write(49, "MGR IO CHECK", 12) = 12 <0.000032>
17856 14:40:16.968590 close(49)       = 0 <0.000018>
17856 14:40:16.968634 unlink("/mjxt/mysql/DISK_CHECKWR38R8") = 0 <0.000041>
17856 14:40:16.968722 sendto(42, "\0", 1, 0, NULL, 0) = 1 <0.000039>
17891 14:40:16.968812 <... poll resumed>) = 1 ([{fd=43, revents=POLLIN|POLLRDNORM}]) <0.001997>
17856 14:40:16.968837 futex(0x7fd55c38744c, FUTEX_WAIT_BITSET_PRIVATE|FUTEX_CLOCK_REALTIME, 20321, {tv_sec=1668926417, tv_nsec=68814800}, 0xffffffff <unfinished ...>
17891 14:40:16.968853 recvfrom(43, "\0", 1024, 0, NULL, NULL) = 1 <0.000021>
17891 14:40:16.968923 recvfrom(43, 0x7fd4919c2e74, 1024, 0, NULL, NULL) = -1 EAGAIN (Resource temporarily unavailable) <0.000013>
17891 14:40:16.969011 write(52, "\27\3\3\1\210\357\r\225\310\232\242K\22[q\5}\357\372\7X-\373\246\236\211\303\276\343\326\264f"..., 397 <unfinished ...>

-----------my.cnf---------------

[client]
socket = /mjxt/mysql/mysql.sock
[mysql]
loose-skip-binary-as-hex
prompt="(\\D)[\\u@GreatSQL][\\d]>"
no-auto-rehash
[mysqld]
basedir = /mjxt/GreatSQL
# 需要替换部分
slow_query_log_file = /mjxt/mysql/slow.log
report_host = 10.33.158.9
user = mysql
port = 3306

#mgr settings
loose-plugin_load_add = 'mysql_clone.so'
loose-plugin_load_add = 'group_replication.so'
loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"
#MGR本地节点IP

ORT，请自行替换
loose-group_replication_local_address = "10.133.105.117:33061"
#MGR集群所有节点IP

ORT，请自行替换
loose-group_replication_group_seeds = '10.133.105.117:33061,10.133.105.118:33061,10.133.105.119:33061'
loose-group_replication_start_on_boot = ON
loose-group_replication_bootstrap_group = OFF
loose-group_replication_exit_state_action = READ_ONLY
# 快单主模式
loose-group_replication_single_primary_mode = ON
loose-group_replication_majority_after_mode = ON
loose-group_replication_communication_max_message_size = 10M
# 不启用仲裁节点
# greatsql shell 需要安装yum install python3-libs -y
loose-group_replication_arbitrator = 0
# 快读单主模式支持回放
loose-group_replication_single_primary_fast_mode = 1
# 网络开销阈值
loose-group_replication_request_time_threshold = 100
loose-group_replication_primary_election_mode = GTID_FIRST
loose-group_replication_unreachable_majority_timeout = 30
loose-group_replication_member_expel_timeout = 5
loose-group_replication_autorejoin_tries = 288

# 主从同步
slave_exec_mode=IDEMPOTENT
gtid_executed_compression_period = 1000
gtid_mode = ON
enforce_gtid_consistency = TRUE
master_info_repository = table
relay_log_info_repository = table
slave-skip-errors=1062,1053

# 集群内必须唯一
server_id = 104
datadir=/mjxt/mysql
socket=/mjxt/mysql/mysql.sock
log-error=/mjxt/mysql/mysqld.log

pid-file = mysql.pid
character-set-server = UTF8MB4
# 关闭只使用IP连接数据库
#skip_name_resolve = 1
#若你的MySQL数据库主要运行在境外，请务必根据实际情况调整本参数
default_time_zone = "+8:00"
# 关闭xmysql插件,不然会启用33060端口
mysqlx=0

#myisam settings
key_buffer_size = 0
myisam_sort_buffer_size = 128M

#performance setttings
lock_wait_timeout = 3600
open_files_limit = 65535
back_log = 1024
max_connections = 1024
max_connect_errors = 1000000
table_open_cache = 2048
table_definition_cache = 2048
thread_stack = 512K
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 96M
max_heap_table_size = 96M

#log settings
log_timestamps = SYSTEM
log_error_verbosity = 3
slow_query_log = 1
log_slow_extra = 1
long_query_time = 0.01
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 0
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_bin = /mjxt/mysql/binlog
binlog_format = ROW
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
# memory/sql/Log_event 会增占用内存
# 不记录select到binlog
binlog_rows_query_log_events = 0
binlog_expire_logs_seconds = 604800
binlog_checksum = CRC32

#replication settings
# 设置并行复制方式：
# 1. DATABASE：默认值，基于库的并行复制方式
# 2. LOGICAL_CLOCK：基于组提交的并行复制方式
relay_log_recovery = 1
slave_parallel_type = LOGICAL_CLOCK
# 并行复制线程数可以设置为逻辑CPU数量的2倍
slave_parallel_workers = 32
binlog_transaction_dependency_tracking = WRITESET
# 与主库提交的顺序完全一致
slave_preserve_commit_order = 1
slave_checkpoint_period = 2

#启用InnoDB并行查询优化功能
loose-force_parallel_execute = ON
#设置每个SQL语句的并行查询最大并发度
loose-parallel_default_dop = 8
#设置系统中总的并行查询线程数，可以和最大逻辑CPU数量一样
loose-parallel_max_threads = 32
#并行执行时leader线程和worker线程使用的总内存大小上限，可以设置物理内存的5-10%左右
loose-parallel_memory_limit = 8G

#innodb settings
transaction_isolation = READ-COMMITTED
# 内存的75%
innodb_buffer_pool_size = 32G
innodb_log_buffer_size = 32M
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_doublewrite_files = 2
innodb_max_undo_log_size = 4G
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 10000
innodb_io_capacity_max = 10000
innodb_open_files = 65534
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
# DDL日志文件大小
innodb_online_alter_log_max_size = 4G
# ddl的错误日志
innodb_print_ddl_logs = 1
innodb_status_file = 1
# 若是通过SHOW ENGINE INNODB STATUS 仅在必要时输出Standard监控结果到交互式mysql客户端，且还要显示Lock Monitor的话，只需开启innodb_status_output_locks参数即可，innodb_status_output开不开无所谓
innodb_status_output = 0
innodb_status_output_locks = 1
innodb_sort_buffer_size = 64M

#innodb monitor settings
innodb_monitor_enable = "module_innodb"
innodb_monitor_enable = "module_server"
innodb_monitor_enable = "module_dml"
innodb_monitor_enable = "module_ddl"
innodb_monitor_enable = "module_trx"
innodb_monitor_enable = "module_os"
innodb_monitor_enable = "module_purge"
innodb_monitor_enable = "module_log"
innodb_monitor_enable = "module_lock"
innodb_monitor_enable = "module_buffer"
innodb_monitor_enable = "module_index"
innodb_monitor_enable = "module_ibuf_system"
innodb_monitor_enable = "module_buffer_page"
innodb_monitor_enable = "module_adaptive_hash"

# 临时表
temptable_use_mmap = 8G
#pfs settings
performance_schema = 1
#performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%memory%=on'

# https://dev.mysql.com/doc/refman ... cation-options.html
# 流控
loose-group_replication_flow_control_mode = DISABLED
# loose-group_replication_flow_control_mode = "QUOTA"

表结构：
CREATE TABLE `xxxx` (

  `vc_trade_id` varchar(38) NOT NULL,
  `vc_gantry_id` varchar(19) NOT NULL ,

  `vc_hour_batch_no` varchar(10) NOT NULL,
  `ts_trans_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `c_center_send_flag` tinyint DEFAULT '0',
  `vc_fee_info3` varchar(400) DEFAULT NULL,

  PRIMARY KEY (`vc_trade_id`),
  KEY `idx_center_send_flag` (`c_center_send_flag`),
  KEY `idx_gantry_id` (`vc_gantry_id`),
  KEY `idx_hour_batch_no` (`vc_hour_batch_no`),
  KEY `idx_trans_time` (`ts_trans_time`),
  KEY `idx_lane_id` (`vc_fee_info3`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

查询语句

explain format=tree
select count(*) as count,count(case when c_center_send_flag=2 then 1 end) as sentcount from gantry_jour FORCE INDEX(idx_trans_time,idx_gantry_id) where ts_trans_time between '2022-11-18 00:00:00' and '2022-11-19 00:00:00' and vc_gantry_id='G005633001010610010' and vc_fee_info3='202912';