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

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

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的缓冲空间.

鉴于您的查询：

SELECT a.msgId,7) as DlvStatus 
  FROM inserted_history a
  LEFT JOIN submitted_history b ON b.msgId = a.msgId  -- USES 1 column of PK
  LEFT JOIN delivered_history c ON a.msgId = c.msgId  -- USES 1 column of PK 
 WHERE a.inTime BETWEEN '2010-08-10 00:00:00' AND '2010-08-010 23:59:59' -- NO key 
   AND a.systemId='ND_arber' -- Uses non-unique PK

以下是我看到的问题：
A)您的_history表在具有’Timestamp’数据类型的列上进行分区,YET您不是JOIN / WHERE条件中的那些列.没有这些信息,引擎必须命中每个分区.
B)对submitted_history和delivered_history的访问仅使用2列PK的1列.你只是获得PK的部分利益.你能获得更多列成为JOIN的一部分吗？您必须为此表找到的行数尽可能接近“1”.
C)msgID = varchar(64),这是每个表的PK的第1列.每张桌子上的钥匙都是**巨大** !!
– 尝试减小PK的列大小,或使用不同的列.

您的其他键的数据模式显示您在非PK键中占用了大量磁盘/ RAM空间.

问题1)每个表的“显示索引FROM”(Link)报告的内容是什么？ “基数”一栏将向您展示每个密钥的真实效果.基数越小,该指数的最低/更低效.您希望基数尽可能接近“总行数”以获得理想的性能.

问题2)您是否可以重新考虑sql,使每个表的JOIN’d列是该表的基数最高的那些？

问题3)’timestamp’数据类型的列真的是分区的最佳列吗？如果您的访问模式总是使用’msgId’,并且msgId是PK的第1列,那么.

问题4)msgId是唯一的吗？我的猜测是肯定的,PK的第二列并不是必需的.

阅读优化sql(Link)并获得表的索引基数报告.这是了解如何优化查询的途径.您希望解释计划的“行”为N * 1 * 1.

侧面注意：InnoDb& MyISAM引擎不会自动更新非唯一列的表基数,DBA需要定期手动运行“分析表”以确保其准确性.

祝好运.

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

解决方法

猜你在找的MsSQL相关文章