当前位置:  开发笔记 > 编程语言 > 正文

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

如何解决《使用LEFTJOIN时,Mysql查询长时间处于'SENDINGDATA'状态》经验,为你挑选了1个好方法。

我有很长一段时间处于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'

总记录在delivered_history:223870168

总记录在inserted_history:264817239

总记录在submitted_history:226637058

解释查询返回:

id  ,  select_type  ,  table  ,  type  ,  possible_keys  ,  key  ,  key_len  ,  ref  ,  rows  ,  Extra  
  1  ,  SIMPLE  ,  a  ,  ref  ,  systemId,idx_time  ,  systemId  ,  14  ,  const  ,  735310  ,  Using where  
  1  ,  SIMPLE  ,  b  ,  ref  ,  PRIMARY  ,  PRIMARY  ,  66  ,  gwreports2.a.msgId  ,  2270405  ,    
  1  ,  SIMPLE  ,  c  ,  ref  ,  PRIMARY  ,  PRIMARY  ,  66  ,  gwreports2.a.msgId  ,  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,
 `systemId` VARCHAR(12) 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,
 PRIMARY KEY (`msgId`, `inTime`),
 INDEX `systemId` (`systemId`),
 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,
 `systemId` VARCHAR(12) NOT NULL,
 `submitTime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
 `status` VARCHAR(20) NOT NULL,
 `smscId` VARCHAR(64) NOT NULL,
 `smsc` VARCHAR(16) NOT NULL,
 `errcode` INT(6) NULL DEFAULT '0',
 PRIMARY KEY (`msgId`, `submitTime`),
 INDEX `systemId` (`systemId`),
 INDEX `smsc` (`smsc`),
 INDEX `status` (`status`),
 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  

J Jorgenson.. 12

你给出的解释计划:

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

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

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

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

鉴于您的查询:

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  -- 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'数据类型的列上进行了分区,但是您不是JOIN/WHERE条件中的那些列.如果没有该信息,引擎必须命中每个分区.B)对submitted_history和delivered_history的访问仅使用2列PK的1列.你只是获得PK的部分利益.你能获得更多的列成为JOIN的一部分吗?您必须为此表找到的行数尽可能接近"1".
C)msgID = varchar(64),这是每个表的PK的第1列.每张桌子上的钥匙都是**巨大**!!
- 尝试减小PK的列大小,或使用不同的列.

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

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

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

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

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

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

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

祝好运.



1> J Jorgenson..:

你给出的解释计划:

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

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

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

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

鉴于您的查询:

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  -- 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'数据类型的列上进行了分区,但是您不是JOIN/WHERE条件中的那些列.如果没有该信息,引擎必须命中每个分区.B)对submitted_history和delivered_history的访问仅使用2列PK的1列.你只是获得PK的部分利益.你能获得更多的列成为JOIN的一部分吗?您必须为此表找到的行数尽可能接近"1".
C)msgID = varchar(64),这是每个表的PK的第1列.每张桌子上的钥匙都是**巨大**!!
- 尝试减小PK的列大小,或使用不同的列.

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

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

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

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

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

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

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

祝好运.

推荐阅读
放ch养奶牛
这个屌丝很懒,什么也没留下!
DevBox开发工具箱 | 专业的在线开发工具网站    京公网安备 11010802040832号  |  京ICP备19059560号-6
Copyright © 1998 - 2020 DevBox.CN. All Rights Reserved devBox.cn 开发工具箱 版权所有