使用LEFT JOIN时,Mysql查询长时间处于’SENDING DATA’状态

发布时间：2020-05-24 16:51:14 所属栏目：MsSql 来源：互联网

导读：我有很长一段时间处于SENDING DATA状态的查询. 有人可以帮我这个：下面是详细信息 Mysql查询： select a.msgId,a.senderId,a.destination,a.inTime,a.status as InStatus,b.status as SubStatus,c.deliverTime,substr(c.receipt,82,7) as DlvStatus fr

我有很长一段时间处于SENDING DATA状态的查询.
有人可以帮我这个：下面是详细信息

Mysql查询：

select a.msgId,a.senderId,a.destination,a.inTime,a.status as InStatus,b.status as SubStatus,c.deliverTime,substr(c.receipt,82,7) as DlvStatus 
from inserted_history a  left join submitted_history b  on b.msgId = a.msgId left join delivered_history c  on a.msgId = c.msgId 
where a.inTime between '2010-08-10 00:00:00' and '2010-08-010 23:59:59' and a.systemId='ND_arber'

deliver_history中的总记录：223870168

inserted_history中的总记录：264817239

submitted_history中的总记录：226637058

解释查询返回：

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra  
  1,SIMPLE,a,systemId,idx_time,14,const,735310,Using where  
  1,b,PRIMARY,66,gwreports2.a.msgId,2270405,1,c,2238701,

为deliver_history创建表

CREATE TABLE `delivered_history` (
 `msgId` VARCHAR(64) NOT NULL,`systemId` VARCHAR(12) NOT NULL,`deliverTime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',`smscId` VARCHAR(64) NOT NULL,`smsc` VARCHAR(20) NOT NULL,`receipt` BLOB NULL,`errcode` INT(11) NULL DEFAULT NULL,PRIMARY KEY (`msgId`,`deliverTime`),INDEX `systemId` (`systemId`),INDEX `smsc` (`smsc`),INDEX `idx_time` (`deliverTime`)
)
ROW_FORMAT=DEFAULT

为inserted_history创建表

CREATE TABLE `inserted_history` (
 `msgId` VARCHAR(64) NOT NULL,`senderId` VARCHAR(15) NOT NULL,`destination` VARCHAR(15) NOT NULL,`inTime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',`status` VARCHAR(20) NOT NULL,`msgText` BLOB NULL,`msgType` VARCHAR(15) NULL DEFAULT NULL,`inTime`),INDEX `senderId` (`senderId`),INDEX `destination` (`destination`),INDEX `status` (`status`),INDEX `idx_time` (`inTime`)
)
ROW_FORMAT=DEFAULT

为submitted_history创建表

CREATE TABLE `submitted_history` (
 `msgId` VARCHAR(64) NOT NULL,`submitTime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',`smsc` VARCHAR(16) NOT NULL,`errcode` INT(6) NULL DEFAULT '0',`submitTime`),INDEX `idx_time` (`submitTime`)
)
ROW_FORMAT=DEFAULT

所有表都在时间戳字段上进行日期分区

Mysql Server中的全局变量列表

Variable_name,Value  
  auto_increment_increment,1  
  auto_increment_offset,1  
  autocommit,ON  
  automatic_sp_privileges,ON  
  back_log,50  
  basedir,/usr/  
  big_tables,OFF  
  binlog_cache_size,32768  
  binlog_format,STATEMENT  
  bulk_insert_buffer_size,8388608  
  character_set_client,latin1  
  character_set_connection,latin1  
  character_set_database,latin1  
  character_set_filesystem,binary  
  character_set_results,latin1  
  character_set_server,latin1  
  character_set_system,utf8  
  character_sets_dir,/usr/share/mysql/charsets/  
  collation_connection,latin1_swedish_ci  
  collation_database,latin1_swedish_ci  
  collation_server,latin1_swedish_ci  
  completion_type,0  
  concurrent_insert,1  
  connect_timeout,10  
  datadir,/var/lib/mysql/  
  date_format,%Y-%m-%d  
  datetime_format,%Y-%m-%d %H:%i:%s  
  default_week_format,0  
  delay_key_write,ON  
  delayed_insert_limit,100  
  delayed_insert_timeout,300  
  delayed_queue_size,1000  
  div_precision_increment,4  
  engine_condition_pushdown,ON  
  error_count,0  
  event_scheduler,OFF  
  expire_logs_days,10  
  flush,OFF  
  flush_time,0  
  foreign_key_checks,ON  
  ft_boolean_syntax,+ -><()~*:        &|  
  ft_max_word_len,84  
  ft_min_word_len,4  
  ft_query_expansion_limit,20  
  ft_stopword_file,(built-in)  
  general_log,OFF  
  general_log_file,/var/run/mysqld/mysqld.log  
  group_concat_max_len,1024  
  have_community_features,YES  
  have_compress,YES  
  have_crypt,YES  
  have_csv,YES  
  have_dynamic_loading,YES  
  have_geometry,YES  
  have_innodb,YES  
  have_ndbcluster,NO  
  have_openssl,DISABLED  
  have_partitioning,YES  
  have_query_cache,YES  
  have_rtree_keys,YES  
  have_ssl,DISABLED  
  have_symlink,YES  
  hostname,smscdb  
  identity,0  
  ignore_builtin_innodb,OFF  
  init_connect,init_file,init_slave,innodb_adaptive_hash_index,ON  
  innodb_additional_mem_pool_size,1048576  
  innodb_autoextend_increment,8  
  innodb_autoinc_lock_mode,1  
  innodb_buffer_pool_size,8388608  
  innodb_checksums,ON  
  innodb_commit_concurrency,0  
  innodb_concurrency_tickets,500  
  innodb_data_file_path,ibdata1:10M:autoextend  
  innodb_data_home_dir,innodb_doublewrite,ON  
  innodb_fast_shutdown,1  
  innodb_file_io_threads,4  
  innodb_file_per_table,OFF  
  innodb_flush_log_at_trx_commit,1  
  innodb_flush_method,innodb_force_recovery,0  
  innodb_lock_wait_timeout,50  
  innodb_locks_unsafe_for_binlog,OFF  
  innodb_log_buffer_size,1048576  
  innodb_log_file_size,5242880  
  innodb_log_files_in_group,2  
  innodb_log_group_home_dir,./  
  innodb_max_dirty_pages_pct,90  
  innodb_max_purge_lag,0  
  innodb_mirrored_log_groups,1  
  innodb_open_files,300  
  innodb_rollback_on_timeout,OFF  
  innodb_stats_on_metadata,ON  
  innodb_support_xa,ON  
  innodb_sync_spin_loops,20  
  innodb_table_locks,ON  
  innodb_thread_concurrency,8  
  innodb_thread_sleep_delay,10000  
  innodb_use_legacy_cardinality_algorithm,ON  
  insert_id,0  
  interactive_timeout,28800  
  join_buffer_size,131072  
  keep_files_on_create,OFF  
  key_buffer_size,1073741824  
  key_cache_age_threshold,300  
  key_cache_block_size,1024  
  key_cache_division_limit,100  
  language,/usr/share/mysql/english/  
  large_files_support,ON  
  large_page_size,0  
  large_pages,OFF  
  last_insert_id,0  
  lc_time_names,en_US  
  license,GPL  
  local_infile,ON  
  locked_in_memory,OFF  
  log,OFF  
  log_bin,ON  
  log_bin_trust_function_creators,OFF  
  log_bin_trust_routine_creators,OFF  
  log_error,log_output,FILE  
  log_queries_not_using_indexes,OFF  
  log_slave_updates,OFF  
  log_slow_queries,OFF  
  log_warnings,1  
  long_query_time,10.000000  
  low_priority_updates,OFF  
  lower_case_file_system,OFF  
  lower_case_table_names,0  
  max_allowed_packet,536870912  
  max_binlog_cache_size,4294963200  
  max_binlog_size,104857600  
  max_connect_errors,10  
  max_connections,151  
  max_delayed_threads,20  
  max_error_count,64  
  max_heap_table_size,16777216  
  max_insert_delayed_threads,20  
  max_join_size,18446744073709551615  
  max_length_for_sort_data,1024  
  max_prepared_stmt_count,16382  
  max_relay_log_size,0  
  max_seeks_for_key,4294967295  
  max_sort_length,1024  
  max_sp_recursion_depth,0  
  max_tmp_tables,32  
  max_user_connections,0  
  max_write_lock_count,4294967295  
  min_examined_row_limit,0  
  multi_range_count,256  
  myisam_data_pointer_size,6  
  myisam_max_sort_file_size,2146435072  
  myisam_recover_options,BACKUP  
  myisam_repair_threads,1  
  myisam_sort_buffer_size,8388608  
  myisam_stats_method,nulls_unequal  
  myisam_use_mmap,OFF  
  net_buffer_length,16384  
  net_read_timeout,30  
  net_retry_count,10  
  net_write_timeout,60  
  new,OFF  
  old,OFF  
  old_alter_table,OFF  
  old_passwords,OFF  
  open_files_limit,20000  
  optimizer_prune_level,1  
  optimizer_search_depth,62  
  optimizer_switch,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on  
  pid_file,/var/run/mysqld/mysqld.pid  
  plugin_dir,/usr/lib/mysql/plugin  
  port,3306  
  preload_buffer_size,32768  
  profiling,OFF  
  profiling_history_size,15  
  protocol_version,10  
  pseudo_thread_id,0  
  query_alloc_block_size,8192  
  query_cache_limit,1073741824  
  query_cache_min_res_unit,4096  
  query_cache_size,536870912  
  query_cache_type,ON  
  query_cache_wlock_invalidate,OFF  
  query_prealloc_size,8192  
  rand_seed1,rand_seed2,range_alloc_block_size,4096  
  read_buffer_size,131072  
  read_only,OFF  
  read_rnd_buffer_size,33554432  
  relay_log,relay_log_index,relay_log_info_file,relay-log.info  
  relay_log_purge,ON  
  relay_log_space_limit,0  
  report_host,report_password,report_port,3306  
  report_user,rpl_recovery_rank,0  
  secure_auth,OFF  
  secure_file_priv,server_id,3  
  skip_external_locking,ON  
  skip_networking,OFF  
  skip_show_database,OFF  
  slave_compressed_protocol,OFF  
  slave_exec_mode,STRICT  
  slave_load_tmpdir,/tmp  
  slave_net_timeout,3600  
  slave_skip_errors,OFF  
  slave_transaction_retries,10  
  slow_launch_time,2  
  slow_query_log,OFF  
  slow_query_log_file,/var/run/mysqld/mysqld-slow.log  
  socket,/var/run/mysqld/mysqld.sock  
  sort_buffer_size,67108864  
  sql_auto_is_null,ON  
  sql_big_selects,ON  
  sql_big_tables,OFF  
  sql_buffer_result,OFF  
  sql_log_bin,ON  
  sql_log_off,OFF  
  sql_log_update,ON  
  sql_low_priority_updates,OFF  
  sql_max_join_size,18446744073709551615  
  sql_mode,sql_notes,ON  
  sql_quote_show_create,ON  
  sql_safe_updates,OFF  
  sql_select_limit,18446744073709551615  
  sql_slave_skip_counter,sql_warnings,OFF  
  ssl_ca,ssl_capath,ssl_cert,ssl_cipher,ssl_key,storage_engine,MyISAM  
  sync_binlog,0  
  sync_frm,ON  
  system_time_zone,IST  
  table_definition_cache,256  
  table_lock_wait_timeout,50  
  table_open_cache,500  
  table_type,MyISAM  
  thread_cache_size,8  
  thread_handling,one-thread-per-connection  
  thread_stack,196608  
  time_format,%H:%i:%s  
  time_zone,SYSTEM  
  timed_mutexes,OFF  
  timestamp,1282125419  
  tmp_table_size,16777216  
  tmpdir,/tmp  
  transaction_alloc_block_size,8192  
  transaction_prealloc_size,4096  
  tx_isolation,REPEATABLE-READ  
  unique_checks,ON  
  updatable_views_with_limit,YES  
  version,5.1.37-1ubuntu5-log  
  version_comment,(Ubuntu)  
  version_compile_machine,i486  
  version_compile_os,debian-linux-gnu  
  wait_timeout,28800  
  warning_count,0

解决方法

你给出的解释计划：

id,Extra
1,systemId idx_time),Using where
1,

显示你正在击中：735310 * 2270405 * 2238701 = 3T行!!!!!!
实际上,您没有充分利用索引.

如何解释你的“解释计划”：
对于表’a'(735310)中的每一行,您按表’b’2270405次.
对于您在表’b’中点击的每一行,您点击表’c’2238701次.
如您所见,这是一个指数问题.

是的,8MB的InnoDb缓冲区空间很小,但是将解释计划降低到xxxx * 1 * 1将导致令人难以置信的速度,即使是8MB的缓冲空间.

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!