sql - MySQL性能查询

Question

我已将我的 drupal 站点从一台 mysql 服务器移至另一台服务器。

旧机器有 1 个 CPU，1GB 内存新机器有 4 个 CPU，4GB 内存。

我在这个查询上的性能有很大的负面差异(2 分钟对 2 秒)

    select distinct c.client
    from client_table c
    LEFT JOIN reps r on ( c.client = r.client )
    where r.user_id is NULL
    AND c.client not in ( select distinct client from billing where first_purchase = 1 ) 


NEW                         OLD
| connect_timeout           10                    |connect_timeout            5
| have_federated_engine     DISABLED              | have_federated_engine      YES
| max_connections           100                   | max_connections            400
| max_seeks_for_key         18446744073709551615  | max_seeks_for_key          4294967295
| max_write_lock_count      18446744073709551615  | max_write_lock_count       4294967295
| myisam_max_sort_file_size 9223372036853727232   | myisam_max_sort_file_size  2147483647
| max_binlog_cache_size     18446744073709547520  | max_binlog_cache_size      4294967295
| myisam_recover_options    BACKUP                | myisam_recover_options     OFF
| range_alloc_block_size    4096                  | range_alloc_block_size     2048
| table_cache               457                   | table_cache                307
| version                   5.0.67-0ubuntu6-log   | version                    5.0.51a-3ubuntu5.4-log
| version_compile_machine   x86_64                | version_compile_machine    i486


ONLY on NEW | relay_log                       |
ONLY on NEW | relay_log_index                 |               
ONLY on NEW | relay_log_info_file             | relay-log.info
ONLY on NEW innodb_adaptive_hash_index        | ON

关于如何确定导致问题的原因或如何解决问题的任何想法？

Answer 1

您可能需要 rebuild your indexes在新实例上。