我有很长一段时间处于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需要定期手动运行"分析表"以确保其准确性.
祝好运.
你给出的解释计划:
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需要定期手动运行"分析表"以确保其准确性.
祝好运.