Mysql
針對關鍵查詢優化 my.ini
我有一個帶有相當複雜的儀表板查詢的數據庫系統。首先是細節。
數據模型：
DROP TABLE IF EXISTS `contract`; CREATE TABLE IF NOT EXISTS `contract` ( `id` int(11) NOT NULL AUTO_INCREMENT, `organisation_id` int(11) NOT NULL, `code` varchar(3) NOT NULL, `moh_code` varchar(20) NOT NULL, `moh_number` varchar(20) NOT NULL, `moh_variation` varchar(20) NOT NULL, `description` mediumtext NOT NULL, `start` datetime NOT NULL, `finish` datetime NOT NULL, `cities` text, `is_support_contract` tinyint(1) DEFAULT NULL, `is_intensive` tinyint(1) DEFAULT NULL, `moh_team_type` varchar(4) DEFAULT NULL, `moh_team_setting` varchar(1) DEFAULT NULL, `moh_service_type` varchar(2) DEFAULT NULL, `moh_target_population` int(4) DEFAULT NULL, `moh_facility_id` varchar(10) DEFAULT NULL, `moh_open_date` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), KEY `organisation_id_idx` (`organisation_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ; -- -------------------------------------------------------- -- -- Table structure for table `peer_engagement` -- DROP TABLE IF EXISTS `peer_engagement`; CREATE TABLE IF NOT EXISTS `peer_engagement` ( `id` int(11) NOT NULL AUTO_INCREMENT, `peer_id` int(11) NOT NULL, `ps_number_id` int(11) NOT NULL, `service_organisation_id` int(11) DEFAULT NULL, `past_service_disengaged` datetime DEFAULT NULL, `key_worker_id` int(11) DEFAULT NULL, `iss_clinical_service_id` int(11) DEFAULT NULL, `iss_psychiatrist_id` int(11) DEFAULT NULL, `iss_service_coordinator_id` int(11) DEFAULT NULL, `iss_declined_courier_back_address` varchar(255) DEFAULT NULL, `waitinglist_support_suggestions_made` mediumtext, `waiting_psw_id` int(11) DEFAULT NULL, `declined_waiting_what_support_avail` mediumtext, `referral_source_id` int(11) DEFAULT NULL, `iss_referred_by` int(11) DEFAULT NULL, `record_entered_by` int(11) NOT NULL, `record_entered` datetime NOT NULL, `waiting_list_priority_id` int(11) DEFAULT NULL, `assigned_psw_id` int(11) DEFAULT NULL, `assigned` datetime DEFAULT NULL, `assigned_by` int(11) DEFAULT NULL, `assigned_effective` datetime DEFAULT NULL, `last_reengaged` datetime DEFAULT NULL, `last_reengaged_id` int(11) DEFAULT NULL, `disengagement_started` datetime DEFAULT NULL, `disengagement_target` datetime DEFAULT NULL, `disengagement_type_id` int(11) DEFAULT NULL, `disengagement_comments` mediumtext, `status_id` int(11) DEFAULT NULL, `closed` datetime DEFAULT NULL, `closed_by` int(11) DEFAULT NULL, `is_intensive` varchar(5) DEFAULT NULL, `heard_about_us_id` int(11) DEFAULT NULL, `primhd_referral_no` varchar(50) DEFAULT NULL, `moved_to_and_delete_id` int(4) DEFAULT NULL, `deleted_date` datetime DEFAULT NULL, `is_gp_only` tinyint(4) NOT NULL, `referral_to_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), KEY `peer_id_idx` (`peer_id`), KEY `ps_number_id_idx` (`ps_number_id`), KEY `service_organisation_id_idx` (`service_organisation_id`), KEY `key_worker_id_idx` (`key_worker_id`), KEY `iss_clinical_service_id_idx` (`iss_clinical_service_id`), KEY `iss_psychiatrist_id_idx` (`iss_psychiatrist_id`), KEY `iss_service_coordinator_id_idx` (`iss_service_coordinator_id`), KEY `referral_source_id_idx` (`referral_source_id`), KEY `iss_referred_by_idx` (`iss_referred_by`), KEY `record_entered_by_idx` (`record_entered_by`), KEY `waiting_psw_id_idx` (`waiting_psw_id`), KEY `waiting_list_priority_id_idx` (`waiting_list_priority_id`), KEY `assigned_psw_id_idx` (`assigned_psw_id`), KEY `assigned_by_idx` (`assigned_by`), KEY `last_reengaged_id_idx` (`last_reengaged_id`), KEY `disengagement_type_id_idx` (`disengagement_type_id`), KEY `status_id_idx` (`status_id`), KEY `closed_by_idx` (`closed_by`), KEY `peer_engagement_heard_about_us_id_fk` (`heard_about_us_id`), KEY `moved_to_and_delete_id_foreign_key` (`moved_to_and_delete_id`), KEY `deleted_date` (`deleted_date`), KEY `disengagement_target` (`disengagement_target`), KEY `peer_engagement_referral_to_id_fk` (`referral_to_id`), KEY `is_intensive` (`is_intensive`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1502 ; -- -------------------------------------------------------- -- -- Table structure for table `peer_number` -- DROP TABLE IF EXISTS `peer_number`; CREATE TABLE IF NOT EXISTS `peer_number` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ps_number` varchar(5) NOT NULL, `contract_id` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), KEY `contract_id_idx` (`contract_id`), KEY `ps_number` (`ps_number`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=75981 ; -- -------------------------------------------------------- -- -- Table structure for table `person` -- DROP TABLE IF EXISTS `person`; CREATE TABLE IF NOT EXISTS `person` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` bigint(20) DEFAULT NULL, `name` varchar(100) NOT NULL, `suite` varchar(100) DEFAULT NULL, `hnr_street` varchar(200) NOT NULL, `suburb` varchar(50) NOT NULL, `city` varchar(50) NOT NULL, `postcode` varchar(5) NOT NULL, `manual_address_entry` tinyint(1) DEFAULT NULL, `current_living_situation_id` int(11) DEFAULT NULL, `dhb_area_id` int(11) DEFAULT NULL, `email` varchar(120) DEFAULT NULL, `phone_daytime` varchar(25) DEFAULT NULL, `phone_evening` varchar(25) DEFAULT NULL, `mobile` varchar(25) DEFAULT NULL, `fax` varchar(25) DEFAULT NULL, `emergency_contact_details` text, `notes` mediumtext, `dob` date DEFAULT NULL, `nhi` varchar(7) DEFAULT NULL, `gender_id` int(11) DEFAULT NULL, `ethnicity_id` int(11) DEFAULT NULL, `team_leader_id` int(11) DEFAULT NULL, `organisation_id` int(11) DEFAULT NULL, `role` varchar(100) DEFAULT NULL, `type` varchar(255) NOT NULL, `postal_address` varchar(200) DEFAULT NULL, `start_date` date DEFAULT NULL, `deleted_date` datetime DEFAULT NULL, `moved_to_and_delete_id` int(4) DEFAULT NULL, `employment_situation_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), KEY `user_id_idx` (`user_id`), KEY `current_living_situation_id_idx` (`current_living_situation_id`), KEY `dhb_area_id_idx` (`dhb_area_id`), KEY `gender_id_idx` (`gender_id`), KEY `team_leader_id_idx` (`team_leader_id`), KEY `organisation_id_idx` (`organisation_id`), KEY `moved_to_and_delete_id_foreign_key_person` (`moved_to_and_delete_id`), KEY `person_employment_situation_id_fk` (`employment_situation_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2377 ; -- -- Constraints for dumped tables -- -- -- Constraints for table `contract` -- ALTER TABLE `contract` ADD CONSTRAINT `contract_organisation_id_organisation_id` FOREIGN KEY (`organisation_id`) REFERENCES `organisation` (`id`); -- -- Constraints for table `peer_engagement` -- ALTER TABLE `peer_engagement` ADD CONSTRAINT `moved_to_and_delete_id_foreign_key` FOREIGN KEY (`moved_to_and_delete_id`) REFERENCES `peer_engagement` (`id`), ADD CONSTRAINT `peer_engagement_assigned_by_person_id` FOREIGN KEY (`assigned_by`) REFERENCES `person` (`id`), ADD CONSTRAINT `peer_engagement_assigned_psw_id_person_id` FOREIGN KEY (`assigned_psw_id`) REFERENCES `person` (`id`), ADD CONSTRAINT `peer_engagement_closed_by_person_id` FOREIGN KEY (`closed_by`) REFERENCES `person` (`id`), ADD CONSTRAINT `peer_engagement_disengagement_type_id_disengagement_type_id` FOREIGN KEY (`disengagement_type_id`) REFERENCES `disengagement_type` (`id`), ADD CONSTRAINT `peer_engagement_heard_about_us_id_fk` FOREIGN KEY (`heard_about_us_id`) REFERENCES `heard_about_us` (`id`), ADD CONSTRAINT `peer_engagement_iss_clinical_service_id_organisation_id` FOREIGN KEY (`iss_clinical_service_id`) REFERENCES `organisation` (`id`), ADD CONSTRAINT `peer_engagement_iss_psychiatrist_id_person_id` FOREIGN KEY (`iss_psychiatrist_id`) REFERENCES `person` (`id`), ADD CONSTRAINT `peer_engagement_iss_referred_by_person_id` FOREIGN KEY (`iss_referred_by`) REFERENCES `person` (`id`), ADD CONSTRAINT `peer_engagement_iss_service_coordinator_id_person_id` FOREIGN KEY (`iss_service_coordinator_id`) REFERENCES `person` (`id`), ADD CONSTRAINT `peer_engagement_key_worker_id_person_id` FOREIGN KEY (`key_worker_id`) REFERENCES `person` (`id`), ADD CONSTRAINT `peer_engagement_last_reengaged_id_peer_number_id` FOREIGN KEY (`last_reengaged_id`) REFERENCES `peer_number` (`id`), ADD CONSTRAINT `peer_engagement_peer_id_person_id` FOREIGN KEY (`peer_id`) REFERENCES `person` (`id`), ADD CONSTRAINT `peer_engagement_ps_number_id_peer_number_id` FOREIGN KEY (`ps_number_id`) REFERENCES `peer_number` (`id`), ADD CONSTRAINT `peer_engagement_record_entered_by_person_id` FOREIGN KEY (`record_entered_by`) REFERENCES `person` (`id`), ADD CONSTRAINT `peer_engagement_referral_source_id_referral_source_id` FOREIGN KEY (`referral_source_id`) REFERENCES `referral_source` (`id`), ADD CONSTRAINT `peer_engagement_referral_to_id_fk` FOREIGN KEY (`referral_to_id`) REFERENCES `referral_to` (`id`), ADD CONSTRAINT `peer_engagement_service_organisation_id_organisation_id` FOREIGN KEY (`service_organisation_id`) REFERENCES `organisation` (`id`), ADD CONSTRAINT `peer_engagement_status_id_status_id` FOREIGN KEY (`status_id`) REFERENCES `status` (`id`), ADD CONSTRAINT `peer_engagement_waiting_psw_id_person_id` FOREIGN KEY (`waiting_psw_id`) REFERENCES `person` (`id`), ADD CONSTRAINT `pwwi` FOREIGN KEY (`waiting_list_priority_id`) REFERENCES `waiting_list_priority` (`id`); -- -- Constraints for table `peer_number` -- ALTER TABLE `peer_number` ADD CONSTRAINT `peer_number_contract_id_contract_id` FOREIGN KEY (`contract_id`) REFERENCES `contract` (`id`); -- -- Constraints for table `person` -- ALTER TABLE `person` ADD CONSTRAINT `moved_to_and_delete_id_foreign_key_person` FOREIGN KEY (`moved_to_and_delete_id`) REFERENCES `person` (`id`), ADD CONSTRAINT `person_current_living_situation_id_current_living_situation_id` FOREIGN KEY (`current_living_situation_id`) REFERENCES `current_living_situation` (`id`), ADD CONSTRAINT `person_dhb_area_id_contract_id` FOREIGN KEY (`dhb_area_id`) REFERENCES `contract` (`id`), ADD CONSTRAINT `person_employment_situation_id_fk` FOREIGN KEY (`employment_situation_id`) REFERENCES `employment_situation` (`id`), ADD CONSTRAINT `person_gender_id_gender_id` FOREIGN KEY (`gender_id`) REFERENCES `gender` (`id`), ADD CONSTRAINT `person_organisation_id_organisation_id` FOREIGN KEY (`organisation_id`) REFERENCES `organisation` (`id`), ADD CONSTRAINT `person_team_leader_id_person_id` FOREIGN KEY (`team_leader_id`) REFERENCES `person` (`id`);詢問：
SELECT p.id AS p__id, p.peer_id AS p__peer_id, p.ps_number_id AS p__ps_number_id, p.service_organisation_id AS p__service_organisation_id, p.past_service_disengaged AS p__past_service_disengaged, p.key_worker_id AS p__key_worker_id, p.is_gp_only AS p__is_gp_only, p.iss_clinical_service_id AS p__iss_clinical_service_id, p.iss_psychiatrist_id AS p__iss_psychiatrist_id, p.iss_service_coordinator_id AS p__iss_service_coordinator_id, p.iss_declined_courier_back_address AS p__iss_declined_courier_back_address, p.waitinglist_support_suggestions_made AS p__waitinglist_support_suggestions_made, p.waiting_psw_id AS p__waiting_psw_id, p.declined_waiting_what_support_avail AS p__declined_waiting_what_support_avail, p.referral_source_id AS p__referral_source_id, p.iss_referred_by AS p__iss_referred_by, p.record_entered_by AS p__record_entered_by, p.record_entered AS p__record_entered, p.waiting_list_priority_id AS p__waiting_list_priority_id, p.assigned_psw_id AS p__assigned_psw_id, p.assigned AS p__assigned, p.assigned_by AS p__assigned_by, p.assigned_effective AS p__assigned_effective, p.last_reengaged AS p__last_reengaged, p.last_reengaged_id AS p__last_reengaged_id, p.disengagement_started AS p__disengagement_started, p.disengagement_target AS p__disengagement_target, p.disengagement_type_id AS p__disengagement_type_id, p.disengagement_comments AS p__disengagement_comments, p.status_id AS p__status_id, p.closed AS p__closed, p.closed_by AS p__closed_by, p.is_intensive AS p__is_intensive, p.heard_about_us_id AS p__heard_about_us_id, p.primhd_referral_no AS p__primhd_referral_no, p.moved_to_and_delete_id AS p__moved_to_and_delete_id, p.deleted_date AS p__deleted_date, p.referral_to_id AS p__referral_to_id, p2.id AS p2__id, p2.user_id AS p2__user_id, p2.name AS p2__name, p2.suite AS p2__suite, p2.hnr_street AS p2__hnr_street, p2.suburb AS p2__suburb, p2.city AS p2__city, p2.postcode AS p2__postcode, p2.manual_address_entry AS p2__manual_address_entry, p2.postal_address AS p2__postal_address, p2.current_living_situation_id AS p2__current_living_situation_id, p2.employment_situation_id AS p2__employment_situation_id, p2.dhb_area_id AS p2__dhb_area_id, p2.email AS p2__email, p2.phone_daytime AS p2__phone_daytime, p2.phone_evening AS p2__phone_evening, p2.mobile AS p2__mobile, p2.fax AS p2__fax, p2.emergency_contact_details AS p2__emergency_contact_details, p2.notes AS p2__notes, p2.dob AS p2__dob, p2.nhi AS p2__nhi, p2.gender_id AS p2__gender_id, p2.team_leader_id AS p2__team_leader_id, p2.start_date AS p2__start_date, p2.organisation_id AS p2__organisation_id, p2.role AS p2__role, p2.type AS p2__type, p2.moved_to_and_delete_id AS p2__moved_to_and_delete_id, p2.deleted_date AS p2__deleted_date, p3.id AS p3__id, p3.user_id AS p3__user_id, p3.name AS p3__name, p3.suite AS p3__suite, p3.hnr_street AS p3__hnr_street, p3.suburb AS p3__suburb, p3.city AS p3__city, p3.postcode AS p3__postcode, p3.manual_address_entry AS p3__manual_address_entry, p3.postal_address AS p3__postal_address, p3.current_living_situation_id AS p3__current_living_situation_id, p3.employment_situation_id AS p3__employment_situation_id, p3.dhb_area_id AS p3__dhb_area_id, p3.email AS p3__email, p3.phone_daytime AS p3__phone_daytime, p3.phone_evening AS p3__phone_evening, p3.mobile AS p3__mobile, p3.fax AS p3__fax, p3.emergency_contact_details AS p3__emergency_contact_details, p3.notes AS p3__notes, p3.dob AS p3__dob, p3.nhi AS p3__nhi, p3.gender_id AS p3__gender_id, p3.team_leader_id AS p3__team_leader_id, p3.start_date AS p3__start_date, p3.organisation_id AS p3__organisation_id, p3.role AS p3__role, p3.type AS p3__type, p3.moved_to_and_delete_id AS p3__moved_to_and_delete_id, p3.deleted_date AS p3__deleted_date, p4.id AS p4__id, p4.ps_number AS p4__ps_number, p4.contract_id AS p4__contract_id, c.id AS c__id, c.organisation_id AS c__organisation_id, c.code AS c__code, c.moh_code AS c__moh_code, c.moh_team_type AS c__moh_team_type, c.moh_team_setting AS c__moh_team_setting, c.moh_service_type AS c__moh_service_type, c.moh_target_population AS c__moh_target_population, c.moh_facility_id AS c__moh_facility_id, c.moh_open_date AS c__moh_open_date, c.moh_number AS c__moh_number, c.moh_variation AS c__moh_variation, c.description AS c__description, c.start AS c__start, c.finish AS c__finish, c.cities AS c__cities, c.is_support_contract AS c__is_support_contract, c.is_intensive AS c__is_intensive, COALESCE(p2.name, p3.name) AS p2__0 FROM peer_engagement p LEFT JOIN person p2 ON p.assigned_psw_id = p2.id LEFT JOIN person p3 ON p.waiting_psw_id = p3.id INNER JOIN peer_number p4 ON p.ps_number_id = p4.id INNER JOIN contract c ON p4.contract_id = c.id INNER JOIN person p5 ON p.peer_id = p5.id WHERE (p.is_intensive = 1 AND p.status_id in (0,1,2,3,4,5,7)解釋聲明
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE p ref peer_id_idx,ps_number_id_idx,status_id_idx,is_intensive,is_intensive_2 is_intensive 8 const 139 Using where 1 SIMPLE p2 eq_ref PRIMARY PRIMARY 4 mabel_mindandbody_co_nz.p.assigned_psw_id 1 1 SIMPLE p3 eq_ref PRIMARY PRIMARY 4 mabel_mindandbody_co_nz.p.waiting_psw_id 1 1 SIMPLE p5 eq_ref PRIMARY PRIMARY 4 mabel_mindandbody_co_nz.p.peer_id 1 1 SIMPLE p4 eq_ref PRIMARY,contract_id_idx PRIMARY 4 mabel_mindandbody_co_nz.p.ps_number_id 1 1 SIMPLE c eq_ref PRIMARY PRIMARY 4 mabel_mindandbody_co_nz.p4.contract_id 1配置是預設的 Ubuntu 12.04：
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_direct_non_transactional_updates OFF binlog_format STATEMENT binlog_stmt_cache_size 32768 bulk_insert_buffer_size 8388608 character_set_client utf8 character_set_connection utf8 character_set_database latin1 character_set_filesystem binary character_set_results utf8 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ collation_connection utf8_general_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci completion_type NO_CHAIN concurrent_insert AUTO connect_timeout 10 datadir /var/lib/mysql/ date_format %Y-%m-%d datetime_format %Y-%m-%d %H:%i:%s default_storage_engine InnoDB 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 external_user 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/lib/mysql/mindandbody.log group_concat_max_len 1024 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_profiling YES have_query_cache YES have_rtree_keys YES have_ssl DISABLED have_symlink YES hostname mindandbody identity 0 ignore_builtin_innodb OFF init_connect init_file init_slave innodb_adaptive_flushing ON innodb_adaptive_hash_index ON innodb_additional_mem_pool_size 8388608 innodb_autoextend_increment 8 innodb_autoinc_lock_mode 1 innodb_buffer_pool_instances 1 innodb_buffer_pool_size 134217728 innodb_change_buffering all 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_format Antelope innodb_file_format_check ON innodb_file_format_max Antelope innodb_file_per_table OFF innodb_flush_log_at_trx_commit 1 innodb_flush_method innodb_force_load_corrupted OFF innodb_force_recovery 0 innodb_io_capacity 200 innodb_large_prefix OFF innodb_lock_wait_timeout 50 innodb_locks_unsafe_for_binlog OFF innodb_log_buffer_size 8388608 Variable_name Value innodb_log_file_size 5242880 innodb_log_files_in_group 2 innodb_log_group_home_dir ./ innodb_max_dirty_pages_pct 75 innodb_max_purge_lag 0 innodb_mirrored_log_groups 1 innodb_old_blocks_pct 37 innodb_old_blocks_time 0 innodb_open_files 300 innodb_print_all_deadlocks OFF innodb_purge_batch_size 20 innodb_purge_threads 0 innodb_random_read_ahead OFF innodb_read_ahead_threshold 56 innodb_read_io_threads 4 innodb_replication_delay 0 innodb_rollback_on_timeout OFF innodb_rollback_segments 128 innodb_spin_wait_delay 6 innodb_stats_method nulls_equal innodb_stats_on_metadata ON innodb_stats_sample_pages 8 innodb_strict_mode OFF innodb_support_xa ON innodb_sync_spin_loops 30 innodb_table_locks ON innodb_thread_concurrency 0 innodb_thread_sleep_delay 10000 innodb_use_native_aio OFF innodb_use_sys_malloc ON innodb_version 5.5.37 innodb_write_io_threads 4 insert_id 0 interactive_timeout 28800 join_buffer_size 131072 keep_files_on_create OFF key_buffer_size 16777216 key_cache_age_threshold 300 key_cache_block_size 1024 key_cache_division_limit 100 large_files_support ON large_page_size 0 large_pages OFF last_insert_id 0 lc_messages en_US lc_messages_dir /usr/share/mysql/english/ lc_time_names en_US license GPL local_infile ON lock_wait_timeout 31536000 locked_in_memory OFF log OFF log_bin OFF log_bin_trust_function_creators OFF log_error log_output FILE log_queries_not_using_indexes ON 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 16777216 max_binlog_cache_size 18446744073709547520 max_binlog_size 104857600 max_binlog_stmt_cache_size 18446744073709547520 max_connect_errors 10 max_connections 60 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_long_data_size 16777216 max_prepared_stmt_count 16382 max_relay_log_size 0 max_seeks_for_key 18446744073709551615 max_sort_length 1024 max_sp_recursion_depth 0 max_tmp_tables 32 max_user_connections 0 max_write_lock_count 18446744073709551615 metadata_locks_cache_size 1024 min_examined_row_limit 0 multi_range_count 256 myisam_data_pointer_size 6 myisam_max_sort_file_size 9223372036853727232 myisam_mmap_size 18446744073709551615 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 Variable_name Value new OFF old OFF old_alter_table OFF old_passwords OFF open_files_limit 1024 optimizer_prune_level 1 optimizer_search_depth 62 optimizer_switch index_merge=on,index_merge_union=on,index_merge_so... performance_schema OFF performance_schema_events_waits_history_long_size 10000 performance_schema_events_waits_history_size 10 performance_schema_max_cond_classes 80 performance_schema_max_cond_instances 1000 performance_schema_max_file_classes 50 performance_schema_max_file_handles 32768 performance_schema_max_file_instances 10000 performance_schema_max_mutex_classes 200 performance_schema_max_mutex_instances 1000000 performance_schema_max_rwlock_classes 30 performance_schema_max_rwlock_instances 1000000 performance_schema_max_table_handles 100000 performance_schema_max_table_instances 50000 performance_schema_max_thread_classes 50 performance_schema_max_thread_instances 1000 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 proxy_user pseudo_slave_mode OFF pseudo_thread_id 205661 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_min_res_unit 4096 query_cache_size 16777216 query_cache_type ON query_cache_wlock_invalidate OFF query_prealloc_size 8192 rand_seed1 0 rand_seed2 0 range_alloc_block_size 4096 read_buffer_size 131072 read_only OFF read_rnd_buffer_size 262144 relay_log relay_log_index relay_log_info_file relay-log.info relay_log_purge ON relay_log_recovery OFF 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 0 skip_external_locking ON skip_name_resolve OFF skip_networking OFF skip_show_database OFF slave_compressed_protocol OFF slave_exec_mode STRICT slave_load_tmpdir /tmp slave_max_allowed_packet 1073741824 slave_net_timeout 3600 slave_skip_errors OFF slave_transaction_retries 10 slave_type_conversions slow_launch_time 2 slow_query_log OFF slow_query_log_file /var/lib/mysql/mindandbody-slow.log socket /var/run/mysqld/mysqld.sock sort_buffer_size 2097152 sql_auto_is_null OFF sql_big_selects ON sql_big_tables OFF sql_buffer_result OFF sql_log_bin ON sql_log_off OFF 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 0 sql_warnings OFF ssl_ca ssl_capath ssl_cert ssl_cipher ssl_key storage_engine InnoDB stored_program_cache 256 Variable_name Value sync_binlog 0 sync_frm ON sync_master_info 0 sync_relay_log 0 sync_relay_log_info 0 system_time_zone NZDT table_definition_cache 400 table_open_cache 256 thread_cache_size 8 thread_concurrency 10 thread_handling one-thread-per-connection thread_stack 196608 time_format %H:%i:%s time_zone SYSTEM timed_mutexes OFF timestamp 1441749665 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.5.37-0ubuntu0.12.04.1 version_comment (Ubuntu) version_compile_machine x86_64 version_compile_os debian-linux-gnu wait_timeout 28800 warning_count 0如何確定我的任何 my.ini 設置（或缺少）是否會不必要地降低查詢速度？
A
PRIMARY KEY是一個UNIQUE KEY；刪除其中的第二個：PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`),通過將此復合索引添加到 peer_engagement，您可能會獲得輕微的性能改進：
INDEX(is_intensive, status_id)innodb_buffer_pool_size 134217728
除非您在小型 VM 中執行，否則更改為大約 70% 的可用RAM。
加入緩衝區大小 131072
更改為 1M 可能會有所幫助。
請提供
EXPLAIN SELECT ...。
LIMIT沒有ORDER BY？該引擎可以免費為您提供任何 20 行的感覺。但是，使用 anORDER BY可能會使它變慢。一次要獲取的欄位非常多。
由於某些欄位是
TEXT，因此關閉了某些優化。