我有很长一段时间处于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
所有表都在时间戳字段上进行日期分区
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需要定期手动运行“分析表”以确保其准确性.
祝好运.
