我的服务器应用程序通过单独的线程与 MySQL 建立多个连接。每个连接都会触发一个SELECT查询并获取结果，然后应用程序将其返回给其连接的用户。
我正在使用InnoDB。令我惊讶的是，我发现很奇怪的是，如果我增加 MySQL 的连接数量，查询性能就会下降，结果获取时间也会增加。下面的表格显示了相同的内容。
这个数据是当我在 MySQL 表中有 3333 条记录时生成的SELECT基于给定的随机参数的查询会从中获取大约 450 条记录。每条记录大约有 10 个字段，所有字段总共包含 1.2 KB 的数据。 （因此，单SELECT查询总共获取 1.2 * 450 = 540 KB 数据）
╔═══════════╦═══════════════╦══════════════╗
║ Number of ║Query execution║ Result fetch ║
║connections║ time range ║ time range ║
║ to MySQL ║ (in seconds) ║ (in seconds) ║
╠═══════════╬═══════════════╬══════════════╣
║ 1 ║ 0.02 to 0.06 ║ 0.03 to 0.18 ║
║ 7 ║ 0.23 to 0.64 ║ 0.54 to 0.74 ║
║ 17 ║ 0.32 to 1.71 ║ 0.53 to 1.18 ║
║ 37 ║ 0.37 to 2.01 ║ 0.70 to 1.70 ║
║ 117 ║ 1.13 to 3.29 ║ 2.48 to 3.25 ║
╚═══════════╩═══════════════╩══════════════╝
我在这里不明白的是，为什么当连接数增加时，MySQL 会花费更多时间？特别是当没有对表进行更新时，MySQL 应该处理SELECT来自单独线程中每个连接的请求。从而并发处理查询。因此，理想情况下，性能和获取不应显着下降。
我不介意与数据库建立单一连接，但问题是我的服务器性能会随之显着下降。成千上万的用户（连接到我的服务器）将不得不等待该单个线程轮到他们。
在解决了一些相关问题后，我尝试增加innodb_buffer_pool_size到 1 GB 但运气不好。
这是我的所有 InnoDB 参数：
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 2097152
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 1073741824
innodb_change_buffer_max_size 25
innodb_change_buffering all
innodb_checksum_algorithm crc32
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
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 2
innodb_flush_method normal
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 268435456
innodb_log_compressed_pages ON
innodb_log_file_size 262144000
innodb_log_files_in_group 2
innodb_log_group_home_dir .\
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 300
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 64
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 8
innodb_thread_sleep_delay 0
innodb_undo_directory .
innodb_undo_logs 128
innodb_undo_tablespaces 0
innodb_use_native_aio OFF
innodb_use_sys_malloc ON
innodb_version 5.6.28
innodb_write_io_threads 16
有人可以点亮吗？这困扰了我很长时间。
(Note:我在这个问题中没有提到实际的查询，因为查询有点复杂，而且这个问题与该查询无关。但这是在查询相同时随着连接数的增加而导致性能下降的问题）
UPDATE 1
Here is SHOW CREATE TABLE我的表的输出：
CREATE TABLE `profiles` (
`SRNO` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`HANDLE_FIRST` int(10) unsigned NOT NULL,
`HANDLE_SECOND` bigint(20) unsigned NOT NULL,
`USERID` binary(16) NOT NULL,
`UNIQUESTRING` char(10) NOT NULL,
`CLIENT_VERSION` smallint(5) unsigned NOT NULL,
`ISCONNECTED` bit(1) NOT NULL,
`ISPROFILEPRESENT` bit(1) NOT NULL,
`USERNAME` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`GENDER` tinyint(1) DEFAULT NULL,
`DND` bit(1) DEFAULT NULL,
`STATUS` varchar(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`PROFILE_URL` varchar(128) DEFAULT NULL,
PRIMARY KEY (`SRNO`),
UNIQUE KEY `USERID` (`USERID`),
KEY `USERID_INDEX` (`USERID`),
KEY `UNIQUESTRING_INDEX` (`UNIQUESTRING`),
KEY `ISCONNECTED_INDEX` (`ISCONNECTED`),
KEY `ISPROFILEPRESENT_INDEX` (`ISPROFILEPRESENT`)
) ENGINE=InnoDB AUTO_INCREMENT=9250 DEFAULT CHARSET=utf8
CREATE TABLE `blockers` (
`BLOCKER_PROFILE_SRNO` bigint(20) unsigned NOT NULL,
`BLOCKED_PROFILE_SRNO` bigint(20) unsigned NOT NULL,
UNIQUE KEY `BLOCKER_PROFILE_SRNO` (`BLOCKER_PROFILE_SRNO`,`BLOCKED_PROFILE_SRNO`),
KEY `BLOCKER_PROFILE_SRNO_INDEX` (`BLOCKER_PROFILE_SRNO`),
KEY `BLOCKED_PROFILE_SRNO_INDEX` (`BLOCKED_PROFILE_SRNO`),
CONSTRAINT `fk_BlockedIndex` FOREIGN KEY (`BLOCKED_PROFILE_SRNO`) REFERENCES `profiles` (`SRNO`),
CONSTRAINT `fk_BlockerIndex` FOREIGN KEY (`BLOCKER_PROFILE_SRNO`) REFERENCES `profiles` (`SRNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
这是我正在运行的查询：
select prfls.*
from profiles as prfls
left outer join blockers as blkr1 on blkr1.blocker_profile_srno = prfls.srno
and blkr1.blocked_profile_srno = 6443
left outer join blockers as blkr2 on blkr2.blocker_profile_srno = 6443
and blkr2.blocked_profile_srno = prfls.srno
where blkr1.blocker_profile_srno is null
and blkr2.blocker_profile_srno is null
and ( (prfls.uniquestring like 'phk5600dcc%')
or (prfls.uniquestring like 'phk5600dcf%')
)
and prfls.isconnected=1
and prfls.isprofilepresent=1
limit 450
该查询本质上是一个准备好的语句，其中blocked_profile_srno, blocker_profile_srno and uniquestring每个查询的参数不断变化。然而blocked_profile_srno and blocker_profile_srno始终保持相等（在上面的查询中，它们的值为 6443）。桌子blockers是空白的（我已将其放在适当的位置以供将来使用，但目前其中没有数据）
当 117 个连接同时运行查询时，输出SHOW GLOBAL STATUS LIKE 'Threads_running';大多数时候是1。但是有时会达到27。同时，输出SHOW GLOBAL STATUS LIKE 'Max_used_connections'; was 130
UPDATE 2
我可以从下面的 Rick James 回答中得知，优化查询会减少查询执行时间范围。这个时间范围仍然随着连接数量的增加而增加，但在可接受的范围内。这就是我接受答案的原因。
