MySQL InnoDB 与 MyISAM 中的复杂查询性能

问题描述

我在 4GB RAM 云服务器上运行 MariaDB 10.0.15，并带有用于数据库的本地 SSD 磁盘.

I am running on MariaDB 10.0.15 on a 4GB RAM cloud server with Local SSD disk for database.

我的模特:

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

我有这张表 raw_detections 有超过 1 亿条记录.我想对特定用户在特定时期内的所有 raw_detections 求和.这是我遇到问题的查询:

I have this table raw_detections with more than 100 million records. I want to sum all raw_detections for a specific user in a specific period. This is the query that I am having problem:

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);

有些用户每月的检测次数很多(数万次).

There are some users that have a lot of detections (tens of thousands) per month.

当 raw_detection 表是 MyISAM 时，此查询需要 2-3 秒才能运行.当表是 InnoDB 时，运行需要 10-20s.我的问题是:

When raw_detection table is MyISAM, this query takes 2-3s to run. When the table is InnoDB, it takes 10-20s to run. My questions are:

1) 为什么会这样?2) 如何提高 MyISAM 和 InnoDB 的性能

1) Why this is happening?2) How can I improve this performance for both MyISAM and 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:我的 Innodb 和 MyISAM 设置:

UPDATE 2:My Innodb and MyISAM settings:

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 is faster when the query is simple, but it's much slower in a high concurrent environment as its table level lock comparing to InnoDB's row level lock.

MyISAM 表可以简单地成为热点并减慢每个连接查询，因此最好避免使用它.

A MyISAM table can simply become a hotspot and slow every joining query down, so better avoid using it.

这篇关于MySQL InnoDB 与 MyISAM 中的复杂查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！