automatem's questions

我在t2.small实例上使用 Amazon RDS 数据库运行相当标准的 LAMP 堆栈。今天我通过网站监控得知数据库似乎已完全“锁定”。我发现的症状是：

• 运行时长时间无查询运行

    show processlist
  

• Apache Web 服务器一点也不忙

查看 Cloudwatch 中配置的日志显示，在中断时，我发现所有吞吐量指标都接近 0。连接数达到 17 个峰值。连接限制默认配置为 InstanceMemory/12MB，因此约为 160连接。可释放内存从 264MB 变为 234MB。

RDS 实例的 CPU 信用余额图显示 CPU 始终为 250，Web 服务器也非常高。没有我可以指出的具体流量，我从 Web 服务器日志中挑选了一些 url 并计算了该小时内的请求，并与其他时间进行了比较。没有什么突出的。

我该如何进一步诊断？

SHOW VARIABLES

    auto_increment_increment    1
    auto_increment_offset   1
    autocommit  ON
    automatic_sp_privileges ON
    avoid_temporal_upgrade  OFF
    back_log    80
    basedir /rdsdbbin/mysql/
    big_tables  OFF
    bind_address    *
    binlog_cache_size   32768
    binlog_checksum CRC32
    binlog_direct_non_transactional_updates OFF
    binlog_error_action IGNORE_ERROR
    binlog_format   MIXED
    binlog_gtid_simple_recovery OFF
    binlog_max_flush_queue_time 0
    binlog_order_commits    ON
    binlog_row_image    FULL
    binlog_rows_query_log_events    OFF
    binlog_stmt_cache_size  32768
    binlogging_impossible_mode  IGNORE_ERROR
    block_encryption_mode   aes-128-ecb
    bulk_insert_buffer_size 8388608
    character_set_client    utf8
    character_set_connection    utf8
    character_set_database  utf8
    character_set_filesystem    binary
    character_set_results   utf8
    character_set_server    utf8
    character_set_system    utf8
    character_sets_dir  /rdsdbbin/mysql-5.6.27.R1/share/charsets/
    collation_connection    utf8_general_ci
    collation_database  utf8_general_ci
    collation_server    utf8_general_ci
    completion_type NO_CHAIN
    concurrent_insert   AUTO
    connect_timeout 10
    core_file   OFF
    datadir /rdsdbdata/db/
    date_format %Y-%m-%d
    datetime_format %Y-%m-%d %H:%i:%s
    default_storage_engine  InnoDB
    default_tmp_storage_engine  InnoDB
    default_week_format 0
    delay_key_write ON
    delayed_insert_limit    100
    delayed_insert_timeout  300
    delayed_queue_size  1000
    disconnect_on_expired_password  ON
    div_precision_increment 4
    end_markers_in_json OFF
    enforce_gtid_consistency    OFF
    eq_range_index_dive_limit   10
    error_count 0
    event_scheduler OFF
    expire_logs_days    0
    explicit_defaults_for_timestamp ON
    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    /rdsdbdata/log/general/mysql-general.log
    group_concat_max_len    1024
    gtid_executed   
    gtid_mode   OFF
    gtid_next   AUTOMATIC
    gtid_owned  
    gtid_purged 
    have_compress   YES
    have_crypt  YES
    have_dynamic_loading    YES
    have_geometry   YES
    have_openssl    YES
    have_profiling  YES
    have_query_cache    YES
    have_rtree_keys YES
    have_ssl    YES
    have_symlink    YES
    host_cache_size 278
    hostname    ip-10-6-0-211
    identity    0
    ignore_builtin_innodb   OFF
    ignore_db_dirs  
    init_connect    
    init_file   
    init_slave  
    innodb_adaptive_flushing    ON
    innodb_adaptive_flushing_lwm    10
    innodb_adaptive_hash_index  ON
    innodb_adaptive_max_sleep_delay 150000
    innodb_additional_mem_pool_size 8388608
    innodb_api_bk_commit_interval   5
    innodb_api_disable_rowlock  OFF
    innodb_api_enable_binlog    OFF
    innodb_api_enable_mdl   OFF
    innodb_api_trx_level    0
    innodb_autoextend_increment 64
    innodb_autoinc_lock_mode    1
    innodb_buffer_pool_dump_at_shutdown OFF
    innodb_buffer_pool_dump_now OFF
    innodb_buffer_pool_filename ib_buffer_pool
    innodb_buffer_pool_instances    8
    innodb_buffer_pool_load_abort   OFF
    innodb_buffer_pool_load_at_startup  OFF
    innodb_buffer_pool_load_now OFF
    innodb_buffer_pool_size 1418723328
    innodb_change_buffer_max_size   25
    innodb_change_buffering all
    innodb_checksum_algorithm   innodb
    innodb_checksums    ON
    innodb_cmp_per_index_enabled    OFF
    innodb_commit_concurrency   0
    innodb_compression_failure_threshold_pct    5
    innodb_compression_level    6
    innodb_compression_pad_pct_max  50
    innodb_concurrency_tickets  5000
    innodb_data_file_path   ibdata1:12M:autoextend
    innodb_data_home_dir    /rdsdbdata/db/innodb
    innodb_disable_sort_file_cache  OFF
    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   ON
    innodb_flush_log_at_timeout 1
    innodb_flush_log_at_trx_commit  1
    innodb_flush_method O_DIRECT
    innodb_flush_neighbors  1
    innodb_flushing_avg_loops   30
    innodb_force_load_corrupted OFF
    innodb_force_recovery   0
    innodb_ft_aux_table 
    innodb_ft_cache_size    8000000
    innodb_ft_enable_diag_print OFF
    innodb_ft_enable_stopword   ON
    innodb_ft_max_token_size    84
    innodb_ft_min_token_size    3
    innodb_ft_num_word_optimize 2000
    innodb_ft_result_cache_limit    2000000000
    innodb_ft_server_stopword_table 
    innodb_ft_sort_pll_degree   2
    innodb_ft_total_cache_size  640000000
    innodb_ft_user_stopword_table   
    innodb_io_capacity  200
    innodb_io_capacity_max  2000
    innodb_large_prefix OFF
    innodb_lock_wait_timeout    50
    innodb_locks_unsafe_for_binlog  OFF
    innodb_log_buffer_size  8388608
    innodb_log_compressed_pages ON
    innodb_log_file_size    134217728
    innodb_log_files_in_group   2
    innodb_log_group_home_dir   /rdsdbdata/log/innodb
    innodb_lru_scan_depth   1024
    innodb_max_dirty_pages_pct  75
    innodb_max_dirty_pages_pct_lwm  0
    innodb_max_purge_lag    0
    innodb_max_purge_lag_delay  0
    innodb_mirrored_log_groups  1
    innodb_monitor_disable  
    innodb_monitor_enable   
    innodb_monitor_reset    
    innodb_monitor_reset_all    
    innodb_old_blocks_pct   37
    innodb_old_blocks_time  1000
    innodb_online_alter_log_max_size    134217728
    innodb_open_files   2000
    innodb_optimize_fulltext_only   OFF
    innodb_page_size    16384
    innodb_print_all_deadlocks  OFF
    innodb_purge_batch_size 300
    innodb_purge_threads    1
    innodb_random_read_ahead    OFF
    innodb_read_ahead_threshold 56
    innodb_read_io_threads  4
    innodb_read_only    OFF
    innodb_replication_delay    0
    innodb_rollback_on_timeout  OFF
    innodb_rollback_segments    128
    innodb_sort_buffer_size 1048576
    innodb_spin_wait_delay  6
    innodb_stats_auto_recalc    ON
    innodb_stats_method nulls_equal
    innodb_stats_on_metadata    OFF
    innodb_stats_persistent ON
    innodb_stats_persistent_sample_pages    20
    innodb_stats_sample_pages   8
    innodb_stats_transient_sample_pages 8
    innodb_status_output    OFF
    innodb_status_output_locks  OFF
    innodb_strict_mode  OFF
    innodb_support_xa   ON
    innodb_sync_array_size  1
    innodb_sync_spin_loops  30
    innodb_table_locks  ON
    innodb_thread_concurrency   0
    innodb_thread_sleep_delay   10000
    innodb_undo_directory   .
    innodb_undo_logs    128
    innodb_undo_tablespaces 0
    innodb_use_native_aio   ON
    innodb_use_sys_malloc   ON
    innodb_version  5.6.27
    innodb_write_io_threads 4
    insert_id   0
    interactive_timeout 28800
    join_buffer_size    262144
    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 /rdsdbbin/mysql-5.6.27.R1/share/
    lc_time_names   en_US
    license GPL
    local_infile    ON
    lock_wait_timeout   31536000
    locked_in_memory    OFF
    log_bin ON
    log_bin_basename    /rdsdbdata/log/binlog/mysql-bin-changelog
    log_bin_index   /rdsdbdata/log/binlog/mysql-bin-changelog.index
    log_bin_trust_function_creators OFF
    log_bin_use_v1_row_events   OFF
    log_error   /rdsdbdata/log/error/mysql-error.log
    log_output  TABLE
    log_queries_not_using_indexes   OFF
    log_slave_updates   ON
    log_slow_admin_statements   OFF
    log_slow_slave_statements   OFF
    log_throttle_queries_not_using_indexes  0
    log_warnings    1
    long_query_time 10.000000
    low_priority_updates    OFF
    lower_case_file_system  OFF
    lower_case_table_names  0
    master_info_repository  TABLE
    master_verify_checksum  OFF
    max_allowed_packet  33554432
    max_binlog_cache_size   18446744073709547520
    max_binlog_size 134217728
    max_binlog_stmt_cache_size  18446744073709547520
    max_connect_errors  100
    max_connections 150
    max_delayed_threads 20
    max_digest_length   1024
    max_error_count 64
    max_heap_table_size 402653184
    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   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
    metadata_locks_hash_instances   8
    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  OFF
    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   0
    open_files_limit    65535
    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,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
    optimizer_trace enabled=off,one_line=off
    optimizer_trace_features    greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
    optimizer_trace_limit   1
    optimizer_trace_max_mem_size    16384
    optimizer_trace_offset  -1
    performance_schema  OFF
    performance_schema_accounts_size    -1
    performance_schema_digests_size -1
    performance_schema_events_stages_history_long_size  -1
    performance_schema_events_stages_history_size   -1
    performance_schema_events_statements_history_long_size  -1
    performance_schema_events_statements_history_size   -1
    performance_schema_events_waits_history_long_size   -1
    performance_schema_events_waits_history_size    -1
    performance_schema_hosts_size   -1
    performance_schema_max_cond_classes 80
    performance_schema_max_cond_instances   -1
    performance_schema_max_digest_length    1024
    performance_schema_max_file_classes 50
    performance_schema_max_file_handles 32768
    performance_schema_max_file_instances   -1
    performance_schema_max_mutex_classes    200
    performance_schema_max_mutex_instances  -1
    performance_schema_max_rwlock_classes   40
    performance_schema_max_rwlock_instances -1
    performance_schema_max_socket_classes   10
    performance_schema_max_socket_instances -1
    performance_schema_max_stage_classes    150
    performance_schema_max_statement_classes    168
    performance_schema_max_table_handles    -1
    performance_schema_max_table_instances  -1
    performance_schema_max_thread_classes   50
    performance_schema_max_thread_instances -1
    performance_schema_session_connect_attrs_size   -1
    performance_schema_setup_actors_size    100
    performance_schema_setup_objects_size   100
    performance_schema_users_size   -1
    pid_file    /rdsdbdata/log/mysql-3306.pid
    plugin_dir  /rdsdbbin/mysql-5.6.27.R1/lib/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    5139681
    query_alloc_block_size  8192
    query_cache_limit   1048576
    query_cache_min_res_unit    4096
    query_cache_size    1048576
    query_cache_type    OFF
    query_cache_wlock_invalidate    OFF
    query_prealloc_size 8192
    rand_seed1  0
    rand_seed2  0
    range_alloc_block_size  4096
    read_buffer_size    262144
    read_only   OFF
    read_rnd_buffer_size    524288
    relay_log   /rdsdbdata/log/relaylog/relaylog
    relay_log_basename  /rdsdbdata/log/relaylog/relaylog
    relay_log_index /rdsdbdata/log/relaylog/relaylog.index
    relay_log_info_file relay-log.info
    relay_log_info_repository   TABLE
    relay_log_purge ON
    relay_log_recovery  ON
    relay_log_space_limit   0
    report_host 
    report_password 
    report_port 3306
    report_user 
    rpl_stop_slave_timeout  31536000
    secure_auth ON
    secure_file_priv    /tmp/
    server_id   153211832
    server_id_bits  32
    server_uuid 7558fb76-4974-11e6-b1d8-067680180113
    show_old_temporals  OFF
    simplified_binlog_gtid_recovery OFF
    skip_external_locking   ON
    skip_name_resolve   OFF
    skip_networking OFF
    skip_show_database  OFF
    slave_allow_batching    OFF
    slave_checkpoint_group  512
    slave_checkpoint_period 300
    slave_compressed_protocol   OFF
    slave_exec_mode STRICT
    slave_load_tmpdir   /rdsdbdata/tmp
    slave_max_allowed_packet    1073741824
    slave_net_timeout   3600
    slave_parallel_workers  0
    slave_pending_jobs_size_max 16777216
    slave_rows_search_algorithms    TABLE_SCAN,INDEX_SCAN
    slave_skip_errors   OFF
    slave_sql_verify_checksum   ON
    slave_transaction_retries   10
    slave_type_conversions  
    slow_launch_time    2
    slow_query_log  OFF
    slow_query_log_file /rdsdbdata/log/slowquery/mysql-slowquery.log
    socket  /tmp/mysql.sock
    sort_buffer_size    262144
    sql_auto_is_null    OFF
    sql_big_selects ON
    sql_buffer_result   OFF
    sql_log_bin ON
    sql_log_off OFF
    sql_mode    NO_ENGINE_SUBSTITUTION
    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  /rdsdbdata/rds-metadata/ca-cert.pem
    ssl_capath  
    ssl_cert    /rdsdbdata/rds-metadata/server-cert.pem
    ssl_cipher  EXP1024-RC4-SHA:EXP1024-DES-CBC-SHA:AES256-SHA:AES128-SHA:DES-CBC3-SHA:DES-CBC-SHA:EXP-DES-CBC-SHA:EXP-RC2-CBC-MD5:RC4-SHA:RC4-MD5:EXP-RC4-MD5:NULL-SHA:NULL-MD5:DES-CBC3-MD5:DES-CBC-MD5:EXP-RC2-CBC-MD5:RC2-CBC-MD5:EXP-RC4-MD5:RC4-MD5:KRB5-DES-CBC3-MD5:KRB5-DES-CBC3-SHA:ADH-DES-CBC3-SHA:EDH-RSA-DES-CBC3-SHA:EDH-DSS-DES-CBC3-SHA:ADH-AES256-SHA:DHE-RSA-AES256-SHA:DHE-DSS-AES256-SHA:ADH-AES128-SHA:DHE-RSA-AES128-SHA:DHE-DSS-AES128-SHA:EXP-KRB5-RC4-MD5:EXP-KRB5-RC2-CBC-MD5:EXP-KRB5-DES-CBC-MD5:KRB5-RC4-MD5:KRB5-DES-CBC-MD5:ADH-RC4-MD5:EXP-ADH-RC4-MD5:DHE-DSS-RC4-SHA:EXP1024-DHE-DSS-RC4-SHA:EXP1024-DHE-DSS-DES-CBC-SHA:EXP-KRB5-RC4-SHA:EXP-KRB5-RC2-CBC-SHA:EXP-KRB5-DES-CBC-SHA:KRB5-RC4-SHA:KRB5-DES-CBC-SHA:ADH-DES-CBC-SHA:EXP-ADH-DES-CBC-SHA:EDH-RSA-DES-CBC-SHA:EXP-EDH-RSA-DES-CBC-SHA:EDH-DSS-DES-CBC-SHA:EXP-EDH-DSS-DES-CBC-SHA
    ssl_crl 
    ssl_crlpath 
    ssl_key /rdsdbdata/rds-metadata/server-key.pem
    storage_engine  InnoDB
    stored_program_cache    256
    sync_binlog 1
    sync_frm    ON
    sync_master_info    10000
    sync_relay_log  10000
    sync_relay_log_info 10000
    system_time_zone    UTC
    table_definition_cache  1400
    table_open_cache    2000
    table_open_cache_instances  16
    thread_cache_size   9
    thread_concurrency  10
    thread_handling one-thread-per-connection
    thread_stack    262144
    time_format %H:%i:%s
    time_zone   UTC
    timed_mutexes   OFF
    timestamp   1507496697.301036
    tmp_table_size  16777216
    tmpdir  /rdsdbdata/tmp
    transaction_alloc_block_size    8192
    transaction_allow_batching  OFF
    transaction_prealloc_size   4096
    tx_isolation    REPEATABLE-READ
    tx_read_only    OFF
    unique_checks   ON
    updatable_views_with_limit  YES
    version 5.6.27-log
    version_comment MySQL Community Server (GPL)
    version_compile_machine x86_64
    version_compile_os  Linux
    wait_timeout    28800
    warning_count   0

这是针对关键查询优化 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`),
  KEY `is_intensive_2` (`is_intensive`,`status_id`)
) 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`);

查询 1：

1) [EXPLAIN] 
  SELECT p.id AS p__id, p.status_id AS p__status_id,     p.disengagement_target AS p__disengagement_target, p2.id AS p2__id, p2.name AS p2__name, p3.id AS p3__id, p3.name AS p3__name, p4.id AS p4__id, p4.ps_number AS p4__ps_number, c.id AS c__id, c.code AS c__code, p5.id AS p5__id, p5.nhi AS p5__nhi, 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 = 0 
     AND p.status_id in (0, 1, 2, 3, 4, 5, 7)) 
  LIMIT 20

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  p   range   peer_id_idx,ps_number_id_idx,status_id_idx,is_inte...   is_intensive_2  10  NULL    611 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   

查询 2：

2) [EXPLAIN] 
  SELECT p.id AS p__id, p.status_id AS p__status_id,     p.disengagement_target AS p__disengagement_target, p2.id AS p2__id, p2.name AS p2__name, p3.id AS p3__id, p3.name AS p3__name, p4.id AS p4__id, p4.ps_number AS p4__ps_number, c.id AS c__id, c.code AS c__code, p5.id AS p5__id, p5.nhi AS p5__nhi, 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)) 
  LIMIT 20

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  p   range   peer_id_idx,ps_number_id_idx,status_id_idx,is_inte...   is_intensive_2  10  NULL    128 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   

区别仅在于is_intensive列。

这是数据细分：

SELECT is_intensive, status_id, COUNT( * ) 
FROM  `peer_engagement` 
GROUP BY is_intensive, status_id
ORDER BY is_intensive, status_id

is_intensive    status_id   count(*)
0   1   18
0   3   229
0   4   13
0   5   101
0   6   653
0   7   252
1   1   2
1   3   26
1   5   73
1   6   14
1   7   24

令我惊讶的是第一个查询被 index 显着加快了is_intensive_2，但是第二个查询却没有。我的预期恰恰相反：第一个查询覆盖了表的 50% 以上，因此执行表扫描，但是对于第二个查询，将使用索引。

我正在寻找一种方法来加快这两个查询的速度，即第一个查询现在执行的程度。

我有一个带有相当复杂的仪表板查询的数据库系统。首先是细节。

数据模型：

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 设置（或缺少设置）是否不必要地降低了我的查询速度？

我需要从时间表中删除重复项。我找到了这个解决方案并根据自己的需要对其进行了调整：

DROP TABLE IF EXISTS `activity`;
CREATE TABLE IF NOT EXISTS `activity` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `planned_start` datetime DEFAULT NULL,
  `planned_end` datetime DEFAULT NULL,
  `actual_start` datetime DEFAULT NULL,
  `actual_end` datetime DEFAULT NULL,
  `code_id` int(11) DEFAULT NULL,
  `setting_id` int(11) DEFAULT NULL,
  `notes` text,
  `travel_distance` decimal(8,2) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `submitted` tinyint(1) DEFAULT NULL,
  `approved` datetime DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  `peer_engagement_id` int(11) DEFAULT NULL,
  `person_id` int(11) DEFAULT NULL,
  `travel_notes` varchar(8000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `code_id_idx` (`code_id`),
  KEY `setting_id_idx` (`setting_id`),
  KEY `created_by_idx` (`created_by`),
  KEY `updated_by_idx` (`updated_by`),
  KEY `approved_by_idx` (`approved_by`),
  KEY `activity_peer_engagement_id_fk` (`peer_engagement_id`),
  KEY `activity_person_id_fk` (`person_id`),
  KEY `actual_start` (`actual_start`,`actual_end`),
  KEY `created` (`created`),
  KEY `person_id` (`person_id`,`actual_start`,`actual_end`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=165796 ;


SELECT
  COUNT(*) as occurrence
  , sub.id
  , SEC_TO_TIME(SUM(
      IF(a2start > a1start, a1end - a2start, a2end - a1start))) as duration
FROM
  (  SELECT
       a1.id
      , UNIX_TIMESTAMP(a1.actual_start) as a1start
      , UNIX_TIMESTAMP(a1.actual_end) as a1end
      , UNIX_TIMESTAMP(a2.actual_start) as a2start
      , UNIX_TIMESTAMP(a2.actual_end) as a2end
    FROM activity a1
    INNER JOIN activity a2
      ON (a1.id <> a2.id and a1.person_id=a2.person_id
      AND NOT(a1.actual_start > a2.actual_end OR a1.actual_end < a2.actual_start))
  ) sub

问题是我什至不能对我的查询运行解释，我的 mysql 服务器进入 100% CPU 使用率并且似乎在那里停留了几分钟。

我可以对内部查询运行解释：

explain SELECT
   a1.id
  , UNIX_TIMESTAMP(a1.actual_start) as a1start
  , UNIX_TIMESTAMP(a1.actual_end) as a1end
  , UNIX_TIMESTAMP(a2.actual_start) as a2start
  , UNIX_TIMESTAMP(a2.actual_end) as a2end
FROM activity a1
INNER JOIN activity a2
  ON (a1.id <> a2.id and a1.person_id=a2.person_id
  AND NOT(a1.actual_start > a2.actual_end OR a1.actual_end < a2.actual_start))

+----+-------------+-------+-------+----------------------------------------------+-----------+---------+--------------------------------------+--------+--------------------------+
| id | select_type | table | type  | possible_keys                                | key       | key_len | ref                                  | rows   | Extra                    |
+----+-------------+-------+-------+----------------------------------------------+-----------+---------+--------------------------------------+--------+--------------------------+
|  1 | SIMPLE      | a1    | index | activity_person_id_fk,actual_start,person_id | person_id | 23      | NULL                                 | 176586 | Using index              |
|  1 | SIMPLE      | a2    | ref   | activity_person_id_fk,actual_start,person_id | person_id | 5       | mabel_mindandbody_co_nz.a1.person_id |  19705 | Using where; Using index |
+----+-------------+-------+-------+----------------------------------------------+-----------+---------+--------------------------------------+--------+--------------------------+
2 rows in set (0.00 sec)

我的问题：

• 为什么不在这里解释工作？
• 我如何优化此查询以提供可接受的速度结果？

关于优化——除了我已经在我的表中使用的索引之外，我找不到任何其他东西。

我考虑过的另一种选择是添加一个额外的字段，将每天编码成一个数字。我确实知道时间表条目绝不会超过 24 小时，而且我确信不包括跨越午夜的时间表条目是可以接受的。因此，我希望在内部查询的这个附加列上使用较小的索引。

我有下表代表会员信息：

    CREATE TABLE IF NOT EXISTS `membership` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `organisation_id` int(11) NOT NULL,
      `membership_subcategory_id` int(11) NOT NULL,
      `start` datetime DEFAULT NULL,
      `end` datetime DEFAULT NULL,
      `amount` decimal(9,2) DEFAULT NULL,
      `amount_paid` decimal(9,2) DEFAULT NULL,
      `notes` mediumtext,
      `order_id` int(11) DEFAULT NULL,
      `payment_type` varchar(20) NOT NULL,
      `active` tinyint(4) NOT NULL DEFAULT '1',
      `cancelled` tinyint(4) NOT NULL DEFAULT '0',
      `cancelled_date` datetime DEFAULT NULL,
      `cancellation_reason` mediumtext,
      `certificate_sent` date DEFAULT NULL,
      `welcome_email_sent` date DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `id` (`id`),
      UNIQUE KEY `order_id_2` (`order_id`,`start`,`end`,`organisation_id`),
      KEY `membership_subcategory_id_idx` (`membership_subcategory_id`),
      KEY `organisation_id_idx` (`organisation_id`),
      KEY `order_id` (`order_id`)
    )

• organization_id 是成员
• 会员年从 7 月 1 日到 6 月 30 日，在每年的会员开始时开始记录 - 这可能是第一年的任何地方，但除非跳过一年，否则它总是 7 月 1 日
• membership_subcategory_id 是会员每年申请的行业类别。

我需要一个有效的查询来获取加入日期和最新的会员类别。

我试过这个查询，但我收到“组函数的无效使用”错误

SELECT m.organisation_id, m2.membership_subcategory_id, MIN( m.start ) 
FROM membership m
INNER JOIN membership m2 ON m.organisation_id = m2.organisation_id
WHERE MAX( m.start ) = m2.start
GROUP BY m.organisation_id, m2.membership_subcategory_id

我在 Web 服务器上有许多带有会话表的网站，这些网站被 Joomla CMS 和 Symfony 框架系统使用。当 slow_query_log=0.6 时，会话表的更新查询会定期出现在慢速日志中。

Joomla 会话表定义是

    CREATE TABLE IF NOT EXISTS `jos_session` (
      `username` varchar(150) DEFAULT '',
      `time` varchar(14) DEFAULT '',
      `session_id` varchar(200) NOT NULL DEFAULT '0',
      `guest` tinyint(4) DEFAULT '1',
      `userid` int(11) DEFAULT '0',
      `usertype` varchar(50) DEFAULT '',
      `gid` tinyint(3) unsigned NOT NULL DEFAULT '0',
      `client_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
      `data` longtext,
      PRIMARY KEY (`session_id`(64)),
      KEY `whosonline` (`guest`,`usertype`),
      KEY `userid` (`userid`),
      KEY `time` (`time`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

我尝试将存储引擎更改为 InnoDB，但没有任何效果，我尝试优化表并删除了所有索引，但主索引。此表中有记录有存储空间

Data    12,352.0    KiB
Index   144.0   KiB
Total   12,496.0    KiB

我的服务器配置

        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_uOFF        
    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/
    collation_connection             utf8_genera
    collation_database               latin1_swed
    collation_server                 latin1_swed
    completion_type                  NO_CHAIN   
    concurrent_insert                AUTO       
    connect_timeout                           10
    datadir                          /var/lib/my
    date_format                      %Y-%m-%d   
    datetime_format                  %Y-%m-%d %H
    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/my
    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                         nzs2.automa
    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            268435456
    innodb_change_buffering          all        
    innodb_checksums                 ON         
    innodb_commit_concurrency                  0
    innodb_concurrency_tickets               500
    innodb_data_file_path            ibdata1:10M
    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_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                   1.1.8      
    innodb_write_io_threads                    4
    insert_id                                  0
    interactive_timeout                    28800
    join_buffer_size                      262144
    keep_files_on_create             OFF        
    key_buffer_size                    268435456
    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/
    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    OFF        
    log_slave_updates                OFF        
    log_slow_queries                 ON         
    log_warnings                               1
    long_query_time                          0.6
    low_priority_updates             OFF        
    lower_case_file_system           OFF        
    lower_case_table_names                     0
    max_allowed_packet                  16777216
    max_binlog_cache_size                    ###
    max_binlog_size                    104857600
    max_binlog_stmt_cache_size               ###
    max_connect_errors                        10
    max_connections                          100
    max_delayed_threads                       20
    max_error_count                           64
    max_heap_table_size                 25165824
    max_insert_delayed_threads                20
    max_join_size                            ###
    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                        ###
    max_sort_length                         1024
    max_sp_recursion_depth                     0
    max_tmp_tables                            32
    max_user_connections                       0
    max_write_lock_count                     ###
    metadata_locks_cache_size               1024
    min_examined_row_limit                     0
    multi_range_count                        256
    myisam_data_pointer_size                   6
    myisam_max_sort_file_size                ###
    myisam_mmap_size                         ###
    myisam_recover_options           BACKUP     
    myisam_repair_threads                      1
    myisam_sort_buffer_size              8388608
    myisam_stats_method              nulls_unequ
    myisam_use_mmap                  OFF        
    net_buffer_length                      16384
    net_read_timeout                          30
    net_retry_count                           10
    net_write_timeout                         60
    new                              OFF        
              Variable_name             Value   
    old                              OFF        
    old_alter_table                  OFF        
    old_passwords                    OFF        
    open_files_limit                        2158
    optimizer_prune_level                      1
    optimizer_search_depth                    62
    optimizer_switch                 index_merge
    performance_schema               OFF        
    performance_schema_events_waits_h      10000
    performance_schema_events_waits_h         10
    performance_schema_max_cond_class         80
    performance_schema_max_cond_insta       1000
    performance_schema_max_file_class         50
    performance_schema_max_file_handl      32768
    performance_schema_max_file_insta      10000
    performance_schema_max_mutex_clas        200
    performance_schema_max_mutex_inst    1000000
    performance_schema_max_rwlock_cla         30
    performance_schema_max_rwlock_ins    1000000
    performance_schema_max_table_hand     100000
    performance_schema_max_table_inst      50000
    performance_schema_max_thread_cla         50
    performance_schema_max_thread_ins       1000
    pid_file                         /var/run/my
    plugin_dir                       /usr/lib/my
    port                                    3306
    preload_buffer_size                    32768
    profiling                        OFF        
    profiling_history_size                    15
    protocol_version                          10
    proxy_user                       
    pseudo_thread_id                      401326
    query_alloc_block_size                  8192
    query_cache_limit                    1048576
    query_cache_min_res_unit                4096
    query_cache_size                    25165824
    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.i
    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                   ON         
    slow_query_log_file              /var/log/my
    socket                           /var/run/my
    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                        ###
    sql_mode                         
    sql_notes                        ON         
    sql_quote_show_create            ON         
    sql_safe_updates                 OFF        
    sql_select_limit                         ###
    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
    sync_binlog                                0
    sync_frm                         ON         
              Variable_name             Value   
    sync_master_info                           0
    sync_relay_log                             0
    sync_relay_log_info                        0
    system_time_zone                 NZDT       
    table_definition_cache                  1024
    table_open_cache                        1024
    thread_cache_size                          8
    thread_concurrency                        10
    thread_handling                  one-thread-
    thread_stack                          196608
    time_format                      %H:%i:%s   
    time_zone                        SYSTEM     
    timed_mutexes                    OFF        
    timestamp                         1361481911
    tmp_table_size                      16777216
    tmpdir                           /tmp       
    transaction_alloc_block_size            8192
    transaction_prealloc_size               4096
    tx_isolation                     REPEATABLE-
    unique_checks                    ON         
    updatable_views_with_limit       YES        
    version                          5.5.29-0ubu
    version_comment                  (Ubuntu)   
    version_compile_machine          x86_64     
    version_compile_os               debian-linu
    wait_timeout                           28800
    warning_count                              0

如何提高这些查询的速度？

一个典型的查询是这样的

UPDATE `jos_session` 
SET `time`='1361469848',`userid`='0',`usertype`='',`username`='',
    `gid`='0',`guest`='1',`client_id`='0',
    `data`='__default|a:8:{s:15:\"session.counter\";i:1;s:19:\"session.timer.start\";i:1361469848;s:18:\"session.timer.last\";i:1361469848;s:17:\"session.timer.now\";i:1361469848;s:24:\"session.client.forwarded\";s:28:\"121.205.249.235, 23.15.14.22\";s:22:\"session.client.browser\";s:69:\"Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.1.4322)\";s:8:\"registry\";O:9:\"JRegistry\":3:{s:17:\"_defaultNameSpace\";s:7:\"session\";s:9:\"_registry\";a:2:{s:7:\"session\";a:1:{s:4:\"data\";O:8:\"stdClass\":0:{}}s:11:\"application\";a:1:{s:4:\"data\";O:8:\"stdClass\":1:{s:4:\"lang\";s:5:\"en-GB\";}}}s:7:\"_errors\";a:0:{}}s:4:\"user\";O:5:\"JUser\":19:{s:2:\"id\";i:0;s:4:\"name\";N;s:8:\"username\";N;s:5:\"email\";N;s:8:\"password\";N;s:14:\"password_clear\";s:0:\"\";s:8:\"usertype\";N;s:5:\"block\";N;s:9:\"sendEmail\";i:0;s:3:\"gid\";i:0;s:12:\"registerDate\";N;s:13:\"lastvisitDate\";N;s:10:\"activation\";N;s:6:\"params\";N;s:3:\"aid\";i:0;s:5:\"guest\";i:1;s:7:\"_params\";O:10:\"JParameter\":7:{s:4:\"_raw\";s:0:\"\";s:4:\"_xml\";N;s:9:\"_elements\";a:0:{}s:12:\"_elementPath\";a:1:{i:0;s:69:\"/var/www/www.seleconlight.com/libraries/joomla/html/parameter/element\";}s:17:\"_defaultNameSpace\";s:8:\"_default\";s:9:\"_registry\";a:1:{s:8:\"_default\";a:1:{s:4:\"data\";O:8:\"stdClass\":0:{}}}s:7:\"_errors\";a:0:{}}s:9:\"_errorMsg\";N;s:7:\"_errors\";a:0:{}}}country_cc|N;' 
WHERE session_id='k3fuukvh8lrfq244u6ic4it605';