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

2022-01-15 00:00:00 mariadb mysql innodb myisam query-performance

我在 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.

相关文章