MySQL InnoDB와 MyISAM의 복잡한 쿼리 성능 비교

MySQL InnoDB와 MyISAM의 복잡한 쿼리 성능 비교

데이터베이스용 로컬 SSD 디스크가 있는 4GB RAM 클라우드 서버에서 MariaDB 10.0.15를 실행하고 있습니다.

내 모델:

users <n---n> right_holders <n---n> tracks ---n> raw_detections

이 테이블이 있습니다.raw_detections1억개 이상의 기록을 가지고 있습니다.모두 정리하고 싶다raw_detections특정 기간의 특정 사용자에 대한 것입니다.문제가 있는 쿼리는 다음과 같습니다.

SELECT 
   DATE(raw_detection.created_at),
   SUM(1)
FROM `raw_detection` 
INNER JOIN `audio_sources` ON `audio_sources`.`id` = `raw_detection`.`audio_source_id` 
INNER JOIN `cities` ON `cities`.`id` = `audio_sources`.`city_id` 
INNER JOIN `tracks` ON `raw_detection`.`track_id` = `tracks`.`id` 
INNER JOIN `track_right_holders` ON `tracks`.`id` = `track_right_holders`.`track_id` 
INNER JOIN `right_holders` ON `track_right_holders`.`right_holder_id` = `right_holders`.`id` 
INNER JOIN `user_right_holders` ON `right_holders`.`id` = `user_right_holders`.`right_holder_id` 
WHERE `raw_detection`.`duplicated` = 0 
AND `user_right_holders`.`user_id` = 1 
AND (raw_detection.created_at >= '2015-01-18 00:00:00') 
AND (raw_detection.created_at <= '2015-02-19 23:59:59') 
AND (audio_sources.source_type = 'Radio') 
AND (track_right_holders.role = 'Interpreter') 
GROUP BY DATE(raw_detection.created_at);

한 달에 탐지 횟수가 많은 사용자도 있습니다(수만 개).

raw_detection 테이블이 MyISAM인 경우 이 쿼리를 실행하려면 2~3초 걸립니다.테이블이 InnoDB일 경우 10~20초 정도 걸립니다.질문은 다음과 같습니다.

1) 이 현상이 발생하는 이유 2) MyISAM과 InnoDB 양쪽에서 이 퍼포먼스를 향상시키는 방법

raw_detection스키마:

CREATE TABLE `raw_detection` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `score` int(11) NOT NULL,
  `track_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `audio_source_id` int(11) NOT NULL,
  `duplicated` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_raw_detection_on_duplicated_and_created_at` (`duplicated`,`created_at`),
  KEY `index_raw_detection_on_created_at` (`created_at`),
  KEY `index_raw_detections_audio_source` (`audio_source_id`,`duplicated`,`created_at`),
  KEY `index_raw_detection_on_track_id_and_duplicated_and_created_at` (`track_id`,`duplicated`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=126224926 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

업데이트 1: 쿼리 설명 출력

1   SIMPLE  user_right_holders  ref index_user_right_holders_on_user_id,index_user_right_holders_on_right_holder_id index_user_right_holders_on_user_id 5   const   6   Using where; Using temporary; Using filesort
1   SIMPLE  right_holders   eq_ref  PRIMARY PRIMARY 4   playax.user_right_holders.right_holder_id   1   Using index
1   SIMPLE  track_right_holders ref index_track_right_holders_on_track_id,index_track_right_holders_on_right_holder_id2 index_track_right_holders_on_right_holder_id2   773 playax.user_right_holders.right_holder_id,const 4   Using index condition; Using where
1   SIMPLE  tracks  eq_ref  PRIMARY PRIMARY 8   playax.track_right_holders.track_id 1   Using where; Using index
1   SIMPLE  raw_detection   ref index_raw_detection_on_duplicated_and_created_at,index_raw_detection_on_created_at,index_raw_detections_audio_source,index_raw_detection_on_track_id_and_duplicated_and_created_at,raw_detection2_ix    index_raw_detection_on_track_id_and_duplicated_and_created_at   5   playax.tracks.id,const  284 Using index condition
1   SIMPLE  audio_sources   eq_ref  PRIMARY,index_audio_sources_on_city_id,audio_source_city_id,audio_sources_source_type_ix    PRIMARY 4   playax.raw_detection.audio_source_id    1   Using where
1   SIMPLE  cities  eq_ref  PRIMARY PRIMARY 4   playax.audio_sources.city_id    1   Using index

업데이트 2: My Innodb 및 MyISAM 설정:

ignore_builtin_innodb   OFF
innodb_adaptive_flushing    ON
innodb_adaptive_flushing_lwm    10.000000
innodb_adaptive_hash_index  ON
innodb_adaptive_hash_index_partitions   1
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_populate OFF
innodb_buffer_pool_size 1073741824
innodb_change_buffer_max_size   25
innodb_change_buffering all
innodb_checksum_algorithm   innodb
innodb_checksums    ON
innodb_cleaner_lsn_age_factor   high_checkpoint
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_corrupt_table_action assert
innodb_data_file_path   ibdata1:12M:autoextend
innodb_data_home_dir    
innodb_disable_sort_file_cache  OFF
innodb_doublewrite  ON
innodb_empty_free_list_algorithm    backoff
innodb_fake_changes OFF
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  2
innodb_flush_method O_DIRECT
innodb_flush_neighbors  1
innodb_flushing_avg_loops   30
innodb_force_load_corrupted OFF
innodb_force_recovery   0
innodb_foreground_preflush  exponential_backoff
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  5000
innodb_io_capacity_max  10000
innodb_kill_idle_transaction    0
innodb_large_prefix OFF
innodb_lock_wait_timeout    50
innodb_locking_fake_changes ON
innodb_locks_unsafe_for_binlog  OFF
innodb_log_arch_dir ./
innodb_log_arch_expire_sec  0
innodb_log_archive  OFF
innodb_log_block_size   512
innodb_log_buffer_size  8388608
innodb_log_checksum_algorithm   innodb
innodb_log_compressed_pages ON
innodb_log_file_size    268435456
innodb_log_files_in_group   2
innodb_log_group_home_dir   ./
innodb_lru_scan_depth   1024
innodb_max_bitmap_file_size 104857600
innodb_max_changed_pages    1000000
innodb_max_dirty_pages_pct  75.000000
innodb_max_dirty_pages_pct_lwm  0.001000
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   4096
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_sched_priority_cleaner   19
innodb_show_locks_held  10
innodb_show_verbose_locks   0
innodb_simulate_comp_failures   0
innodb_sort_buffer_size 1048576
innodb_spin_wait_delay  6
innodb_stats_auto_recalc    ON
innodb_stats_method nulls_equal
innodb_stats_modified_counter   0
innodb_stats_on_metadata    OFF
innodb_stats_persistent ON
innodb_stats_persistent_sample_pages    20
innodb_stats_sample_pages   8
innodb_stats_traditional    ON
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_track_changed_pages  OFF
innodb_undo_directory   .
innodb_undo_logs    128
innodb_undo_tablespaces 0
innodb_use_atomic_writes    OFF
innodb_use_fallocate    OFF
innodb_use_global_flush_log_at_trx_commit   ON
innodb_use_native_aio   ON
innodb_use_stacktrace   OFF
innodb_use_sys_malloc   ON
innodb_version  5.6.22-71.0
innodb_write_io_threads 4
myisam_block_size   1024
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 536870912
myisam_stats_method nulls_unequal
myisam_use_mmap OFF

MyISAM은 쿼리가 단순할 때 더 빠르지만 InnoDB의 행 레벨 잠금에 비해 테이블 레벨 잠금으로 높은 동시 환경에서는 훨씬 느립니다.

MyISAM 테이블은 단순히 핫스팟이 되어 모든 조인 쿼리가 느려질 수 있으므로 사용하지 않는 것이 좋습니다.

언급URL : https://stackoverflow.com/questions/29168374/complex-query-performance-in-mysql-innodb-versus-myisam