Mysql
MySQL - 設計+優化問題
我覺得我的數據庫設計可能犯了一個錯誤。
目前我有 73182 張桌子，4 天前我有 72236 張桌子，而就在 2 個月前，我們只有大約 20k 張桌子 - 所以我們的增長飆升。
本質上，每個客戶都有自己的 CRM 表集，這些表幾乎都非常相似，只是它只保存他們獨有的數據。通過一些重構，我可以非常輕鬆地將數據複製到具有大量行的幾個表中（這意味著我可能會刪除近 71000 個表）。但是，我想對此提出想法和意見，因為 CRM 中發生了很多數據和操作，所以我認為查找會更快，因為使用多個鍵的需要更少。
我在Google“雲”上使用 MySQL。我現在的伺服器是 8vCPU@52GB 記憶體，下一層是 16vCPU@104GB 記憶體。我正在折騰是否將每個表的文件 InnoDB 文件設置為 = NO，目前它是 = YES。我純粹使用 InnoDB。
innodb_read_io_threads innodb_write_io_threads都設置為 4（預設），我無法更改它們 - Google支持代表通知我，如果我能給出足夠清楚的理由為什麼我需要更改它；他們會允許的。理想情況下，我希望它達到 16 左右。我還沒有在 InnoDB 狀態中看到任何掛起的讀/寫，我會偶爾檢查一下。
這是我傾倒的所有統計數據（對不起，很長的文章）
這是我目前的變數
allow_client_hostname_forwarding=ON allow_repl_client_for_replication_config=OFF auto_generate_certs=ON auto_increment_increment=1 auto_increment_offset=1 autocommit=ON automatic_sp_privileges=ON avoid_temporal_upgrade=OFF back_log=850 basedir=/mysql/ big_tables=OFF bind_address=* binlog_cache_size=32768 binlog_checksum=CRC32 binlog_direct_non_transactional_updates=OFF binlog_error_action=ABORT_SERVER binlog_format=ROW binlog_group_commit_sync_delay=0 binlog_group_commit_sync_no_delay_count=0 binlog_gtid_simple_recovery=ON binlog_max_flush_queue_time=0 binlog_order_commits=ON binlog_row_image=FULL binlog_rows_query_log_events=OFF binlog_stmt_cache_size=32768 block_encryption_mode=aes-128-ecb block_slave_sql_access=0 block_user_access=33280 bulk_insert_buffer_size=8388608 character_set_client=utf8 character_set_connection=utf8 character_set_database=utf8 character_set_filesystem=binary character_set_results=utf8 character_set_server=utf8 character_set_system=utf8 character_sets_dir=/usr/share/mysql/charsets/ check_proxy_users=OFF collation_connection=utf8_general_ci collation_database=utf8_general_ci collation_server=utf8_general_ci completion_type=NO_CHAIN concurrent_insert=AUTO connect_timeout=10 core_file=OFF datadir=/mysql/datadir/ date_format=%Y-%m-%d datetime_format=%Y-%m-%d %H:%i:%s default_authentication_plugin=mysql_native_password default_password_lifetime=0 default_storage_engine=InnoDB default_tmp_storage_engine=InnoDB default_week_format=0 delay_key_write=ON delayed_insert_limit=100 delayed_insert_timeout=300 delayed_queue_size=1000 disabled_storage_engines= disconnect_on_expired_password=ON div_precision_increment=4 end_markers_in_json=OFF enforce_gtid_consistency=ON enforce_storage_engine= eq_range_index_dive_limit=200 error_count=0 event_scheduler=OFF expire_logs_days=0 explicit_defaults_for_timestamp=OFF external_user= flush=OFF flush_time=0 force_ssl_for_tcp=NONE foreign_key_checks=ON ft_boolean_syntax=+ -><()~*:\"\"&| ft_max_word_len=84 ft_min_word_len=4 ft_query_expansion_limit=20 ft_stopword_file=(built-in) general_log=OFF general_log_file=/mysql/logs/general.log group_concat_max_len=1024 gtid_executed_compression_period=1000 gtid_mode=ON gtid_next=AUTOMATIC gtid_owned= gtid_purged=5fd39415-ca37-11e6-9729-42010a800034:1-387725664 have_compress=YES have_crypt=YES have_dynamic_loading=YES have_geometry=YES have_openssl=YES have_profiling=YES have_query_cache=YES have_rtree_keys=YES have_ssl=YES have_statement_timeout=YES have_symlink=YES host_cache_size=803 hostname=localhost identity=0 ignore_builtin_innodb=OFF ignore_db_dirs= init_connect= init_file= init_slave= innodb_adaptive_flushing=ON innodb_adaptive_flushing_lwm=10 innodb_adaptive_hash_index=ON innodb_adaptive_hash_index_parts=8 innodb_adaptive_max_sleep_delay=150000 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_chunk_size=134217728 innodb_buffer_pool_dump_at_shutdown=ON innodb_buffer_pool_dump_now=OFF innodb_buffer_pool_dump_pct=25 innodb_buffer_pool_filename=ib_buffer_pool innodb_buffer_pool_instances=8 innodb_buffer_pool_load_abort=OFF innodb_buffer_pool_load_at_startup=ON innodb_buffer_pool_load_now=OFF innodb_buffer_pool_size=40802189312 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_default_row_format=dynamic innodb_disable_sort_file_cache=OFF innodb_doublewrite=ON innodb_fast_shutdown=1 innodb_file_format=Barracuda innodb_file_format_check=ON innodb_file_format_max=Barracuda innodb_file_per_table=ON innodb_fill_factor=100 innodb_flush_log_at_timeout=1 innodb_flush_log_at_trx_commit=1 innodb_flush_method=O_DIRECT innodb_flush_neighbors=2 innodb_flush_sync=ON innodb_flushing_avg_loops=5 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=5000 innodb_io_capacity_max=10000 innodb_large_prefix=ON innodb_lock_wait_timeout=50 innodb_locks_unsafe_for_binlog=OFF innodb_log_buffer_size=16777216 innodb_log_checksums=ON innodb_log_compressed_pages=ON innodb_log_file_size=536870912 innodb_log_files_in_group=2 innodb_log_group_home_dir=./ innodb_log_write_ahead_size=8192 innodb_lru_scan_depth=2048 innodb_max_dirty_pages_pct=75.000000 innodb_max_dirty_pages_pct_lwm=0.000000 innodb_max_purge_lag=0 innodb_max_purge_lag_delay=0 innodb_max_undo_log_size=1073741824 innodb_monitor_disable= innodb_monitor_enable=all 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=2000 innodb_optimize_fulltext_only=OFF innodb_page_cleaners=8 innodb_page_size=16384 innodb_print_all_deadlocks=OFF innodb_purge_batch_size=300 innodb_purge_rseg_truncate_frequency=128 innodb_purge_threads=4 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_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=ON innodb_support_xa=ON innodb_sync_array_size=1 innodb_sync_spin_loops=30 innodb_table_locks=ON innodb_temp_data_file_path=ibtmp1:12M:autoextend innodb_thread_concurrency=0 innodb_thread_sleep_delay=10000 innodb_tmpdir= innodb_undo_directory=./ innodb_undo_log_truncate=OFF innodb_undo_logs=128 innodb_undo_tablespaces=0 innodb_use_native_aio=ON innodb_version=5.7.14 innodb_write_io_threads=4 insert_id=0 interactive_timeout=28800 internal_tmp_disk_storage_engine=InnoDB join_buffer_size=262144 keep_files_on_create=OFF key_buffer_size=8388608 key_cache_age_threshold=300 key_cache_block_size=1024 key_cache_division_limit=100 large_files_support=ON large_page_size=0 large_pages=OFF last_insert_id=0 lc_messages=en_US lc_messages_dir=/usr/share/mysql/ lc_time_names=en_US license=GPL local_infile=ON lock_wait_timeout=31536000 locked_in_memory=OFF log_bin=ON log_bin_basename=/mysql/binlog/mysql-bin log_bin_index=/mysql/binlog/mysql-bin.index log_bin_trust_function_creators=OFF log_bin_use_v1_row_events=OFF log_builtin_as_identified_by_password=OFF log_error=stderr log_error_verbosity=3 log_n_first_received_events=4 log_n_first_sent_events=4 log_output=TABLE log_queries_not_using_indexes=OFF log_slave_updates=ON log_slow_admin_statements=OFF log_slow_slave_statements=OFF log_statements_unsafe_for_binlog=ON log_syslog=OFF log_syslog_facility=daemon log_syslog_include_pid=ON log_syslog_tag= log_throttle_queries_not_using_indexes=0 log_timestamps=UTC log_warnings=2 long_query_time=2.000000 low_priority_updates=OFF lower_case_file_system=OFF lower_case_table_names=0 master_info_repository=TABLE master_verify_checksum=OFF max_allowed_packet=268435456 max_binlog_cache_size=18446744073709547520 max_binlog_size=104857600 max_binlog_stmt_cache_size=18446744073709547520 max_connect_errors=100 max_connections=4000 max_delayed_threads=20 max_digest_length=1024 max_error_count=64 max_execution_time=0 max_heap_table_size=16777216 max_insert_delayed_threads=20 max_join_size=18446744073709551615 max_length_for_sort_data=1024 max_points_in_geometry=65536 max_prepared_stmt_count=16382 max_relay_log_size=0 max_seeks_for_key=18446744073709551615 max_sort_length=1024 max_sp_recursion_depth=0 max_tmp_tables=32 max_user_connections=0 max_write_lock_count=18446744073709551615 metadata_locks_cache_size=1024 metadata_locks_hash_instances=8 min_examined_row_limit=0 multi_range_count=256 mysql_native_password_proxy_users=OFF net_buffer_length=16384 net_read_timeout=30 net_retry_count=10 net_write_timeout=60 new=OFF ngram_token_size=2 offline_mode=OFF old=OFF old_alter_table=OFF old_passwords=0 open_files_limit=1048576 optimizer_prune_level=1 optimizer_search_depth=62 optimizer_trace=enabled=off,one_line=off optimizer_trace_limit=1 optimizer_trace_max_mem_size=16384 optimizer_trace_offset=-1 parser_max_mem_size=18446744073709551615 performance_schema=OFF performance_schema_accounts_size=0 performance_schema_digests_size=0 performance_schema_events_stages_history_long_size=0 performance_schema_events_stages_history_size=0 performance_schema_events_statements_history_long_size=0 performance_schema_events_statements_history_size=0 performance_schema_events_transactions_history_long_size=0 performance_schema_events_transactions_history_size=0 performance_schema_events_waits_history_long_size=0 performance_schema_events_waits_history_size=0 performance_schema_hosts_size=0 performance_schema_max_cond_classes=0 performance_schema_max_cond_instances=0 performance_schema_max_digest_length=0 performance_schema_max_file_classes=0 performance_schema_max_file_handles=0 performance_schema_max_file_instances=0 performance_schema_max_index_stat=0 performance_schema_max_memory_classes=0 performance_schema_max_metadata_locks=0 performance_schema_max_mutex_classes=0 performance_schema_max_mutex_instances=0 performance_schema_max_prepared_statements_instances=0 performance_schema_max_program_instances=0 performance_schema_max_rwlock_classes=0 performance_schema_max_rwlock_instances=0 performance_schema_max_socket_classes=0 performance_schema_max_socket_instances=0 performance_schema_max_sql_text_length=0 performance_schema_max_stage_classes=0 performance_schema_max_statement_classes=0 performance_schema_max_statement_stack=0 performance_schema_max_table_handles=0 performance_schema_max_table_instances=0 performance_schema_max_table_lock_stat=0 performance_schema_max_thread_classes=0 performance_schema_max_thread_instances=0 performance_schema_session_connect_attrs_size=0 performance_schema_setup_actors_size=0 performance_schema_setup_objects_size=0 performance_schema_users_size=0 pid_file=/mysql/mysql.pid plugin_dir=/usr/lib/mysql/plugin/ port=3306 preload_buffer_size=32768 profiling=OFF profiling_history_size=15 protocol_version=10 proxy_user= pseudo_slave_mode=OFF pseudo_thread_id=162 query_alloc_block_size=8192 query_cache_limit=1048576 query_cache_min_res_unit=4096 query_cache_size=1048576 query_cache_type=OFF query_cache_wlock_invalidate=OFF query_prealloc_size=8192 rand_seed1=0 rand_seed2=0 range_alloc_block_size=4096 range_optimizer_max_mem_size=8388608 rbr_exec_mode=STRICT read_buffer_size=131072 read_only=OFF read_rnd_buffer_size=262144 relay_log=/mysql/relaylog/relay-log relay_log_basename=/mysql/relaylog/relay-log relay_log_index=/mysql/relaylog/relay-log.index relay_log_info_file=relay-log.info relay_log_info_repository=TABLE relay_log_purge=ON relay_log_recovery=ON relay_log_space_limit=0 report_host= report_password= report_port=3306 report_user= require_secure_transport=OFF require_super_for_system_schema_ddl=information_schema,mysql,performance_schema,sys require_super_for_system_table_writes=event,func,plugin,proc,heartbeat reserved_super_connections=20 rpl_semi_sync_master_async_notify_sleep_interval=1000000 rpl_semi_sync_master_async_notify_socket=/semisync/semisync.sock rpl_semi_sync_master_async_notify_timeout=5000000 rpl_semi_sync_master_do_async_notify=ON rpl_semi_sync_master_enabled=ON rpl_semi_sync_master_slave_lag_heartbeat_frequency_us=500000 rpl_semi_sync_master_timeout=3000 rpl_semi_sync_master_trace_level=32 rpl_semi_sync_master_wait_for_slave_count=1 rpl_semi_sync_master_wait_no_slave=ON rpl_semi_sync_master_wait_point=AFTER_SYNC rpl_semi_sync_slave_enabled=OFF rpl_semi_sync_slave_lag_enabled=OFF rpl_semi_sync_slave_trace_level=32 rpl_stop_slave_timeout=31536000 secure_auth=ON secure_file_priv=/mysql/tmp/ server_id=1655813737 server_id_bits=32 server_uuid=5fd39415-ca37-11e6-9729-42010a800034 session_track_gtids=OFF session_track_schema=ON session_track_state_change=OFF session_track_system_variables=time_zone,autocommit,character_set_client,character_set_results,character_set_connection session_track_transaction_info=OFF sha256_password_auto_generate_rsa_keys=ON sha256_password_private_key_path=private_key.pem sha256_password_proxy_users=OFF sha256_password_public_key_path=public_key.pem show_compatibility_56=OFF show_old_temporals=OFF skip_external_locking=ON skip_name_resolve=ON skip_networking=OFF skip_show_database=OFF slave_allow_batching=OFF slave_checkpoint_group=512 slave_checkpoint_period=300 slave_compressed_protocol=OFF slave_exec_mode=STRICT slave_load_tmpdir=/mysql/tmp slave_max_allowed_packet=1073741824 slave_net_timeout=30 slave_parallel_type=DATABASE slave_parallel_workers=0 slave_pending_jobs_size_max=16777216 slave_preserve_commit_order=OFF slave_rows_search_algorithms=TABLE_SCAN,INDEX_SCAN slave_skip_errors=OFF slave_sql_verify_checksum=ON slave_transaction_retries=10 slave_type_conversions= slow_launch_time=2 slow_query_log=ON slow_query_log_file=/mysql/logs/slow.log socket=/mysql/mysql.sock sort_buffer_size=262144 sql_auto_is_null=OFF sql_big_selects=ON sql_buffer_result=OFF sql_log_bin=ON sql_log_off=OFF sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION sql_notes=ON sql_quote_show_create=ON sql_safe_updates=OFF sql_select_limit=18446744073709551615 sql_slave_skip_counter=0 sql_warnings=OFF ssl_ca=/mysql/datadir/client_ca_cert.pem ssl_capath= ssl_cert=/mysql/datadir/server_cert.pem ssl_cipher=ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES128-SHA:ECDHE-ECDSA-AES256-SHA:ECDHE-RSA-AES256-SHA:AES128-GCM-SHA256:AES128-SHA:AES256-SHA:DES-CBC3-SHA ssl_crl= ssl_crlpath= ssl_key=/mysql/datadir/server_pkey.pem stored_program_cache=256 super_read_only=OFF sync_binlog=1 sync_frm=ON sync_master_info=10000 sync_relay_log=10000 sync_relay_log_info=10000 system_time_zone=UTC system_user_table=ON table_definition_cache=1400 table_open_cache=2000 table_open_cache_instances=16 thread_cache_size=48 thread_handling=one-thread-per-connection thread_stack=262144 time_format=%H:%i:%s time_zone=SYSTEM timestamp=1511213904.401692 tls_version=TLSv1,TLSv1.1,TLSv1.2 tmp_table_size=16777216 tmpdir=/mysql/tmp transaction_alloc_block_size=8192 transaction_allow_batching=OFF transaction_prealloc_size=4096 transaction_write_set_extraction=OFF tx_isolation=REPEATABLE-READ tx_read_only=OFF unique_checks=ON updatable_views_with_limit=YES version=5.7.14-google-log version_comment=(Google) version_compile_machine=x86_64 version_compile_os=Linux wait_timeout=28800 warning_count=0這是我的“SHOW ENGINE INNODB STATUS”（現在是非高峰時間）
Per second averages calculated from the last 21 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 454 srv_active, 0 srv_shutdown, 18 srv_idle srv_master_thread log flush and writes: 472 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 22416 OS WAIT ARRAY INFO: signal count 20532 RW-shared spins 0, rounds 23685, OS waits 11571 RW-excl spins 0, rounds 2629, OS waits 45 RW-sx spins 1, rounds 30, OS waits 1 Spin rounds per wait: 23685.00 RW-shared, 2629.00 RW-excl, 30.00 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 923538034 Purge done for trx's n:o < 923538030 undo n:o < 0 state: running but idle History list length 2188 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 375459551693520, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 375459551692608, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 375459551694432, not started 0 lock struct(s), heap size 1136, 0 row lock(s) -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 132950 OS file reads, 17513 OS file writes, 11810 OS fsyncs 20.48 reads/s, 16384 avg bytes/read, 43.52 writes/s, 18.48 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 81, seg size 83, 110 merges merged operations: insert 147, delete mark 5, delete 3 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 10092529, node heap has 119 buffer(s) Hash table size 10092529, node heap has 41 buffer(s) Hash table size 10092529, node heap has 29 buffer(s) Hash table size 10092529, node heap has 1761 buffer(s) Hash table size 10092529, node heap has 77 buffer(s) Hash table size 10092529, node heap has 13 buffer(s) Hash table size 10092529, node heap has 14 buffer(s) Hash table size 10092529, node heap has 351 buffer(s) 3181.13 hash searches/s, 2702.35 non-hash searches/s --- LOG --- Log sequence number 231371250929 Log flushed up to 231371250912 Pages flushed up to 231371250069 Last checkpoint at 231371237449 0 pending log flushes, 0 pending chkp writes 9390 log i/o's done, 12.86 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 41778413568 Dictionary memory allocated 15070313 Buffer pool size 2490064 Free buffers 2352880 Database pages 134779 Old database pages 49908 Modified db pages 8 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 8, not young 0 0.05 youngs/s, 0.00 non-youngs/s Pages read 132333, created 2446, written 7006 20.28 reads/s, 0.19 creates/s, 29.81 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 134779, unzip_LRU len: 0 I/O sum[0]:cur[1808], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 311258 Free buffers 294470 Database pages 16487 Old database pages 6106 Modified db pages 1 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 2, not young 0 0.05 youngs/s, 0.00 non-youngs/s Pages read 16164, created 323, written 1314 2.67 reads/s, 0.05 creates/s, 7.29 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 16487, unzip_LRU len: 0 I/O sum[0]:cur[226], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 311258 Free buffers 294680 Database pages 16281 Old database pages 6029 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 1, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 16022, created 259, written 593 2.14 reads/s, 0.00 creates/s, 1.81 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 16281, unzip_LRU len: 0 I/O sum[0]:cur[226], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 311258 Free buffers 293338 Database pages 17627 Old database pages 6526 Modified db pages 1 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 3, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 17306, created 321, written 930 1.00 reads/s, 0.00 creates/s, 3.43 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 17627, unzip_LRU len: 0 I/O sum[0]:cur[226], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 311258 Free buffers 294783 Database pages 16173 Old database pages 5990 Modified db pages 2 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 15824, created 349, written 1188 6.48 reads/s, 0.00 creates/s, 7.52 writes/s Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 16173, unzip_LRU len: 0 I/O sum[0]:cur[226], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 311258 Free buffers 294111 Database pages 16842 Old database pages 6237 Modified db pages 3 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 16515, created 327, written 847 2.48 reads/s, 0.00 creates/s, 2.14 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 16842, unzip_LRU len: 0 I/O sum[0]:cur[226], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 311258 Free buffers 293676 Database pages 17285 Old database pages 6400 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 2, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 16974, created 311, written 499 2.38 reads/s, 0.00 creates/s, 0.29 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 17285, unzip_LRU len: 0 I/O sum[0]:cur[226], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 311258 Free buffers 294669 Database pages 16286 Old database pages 6031 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 15999, created 287, written 816 1.29 reads/s, 0.14 creates/s, 4.38 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 16286, unzip_LRU len: 0 I/O sum[0]:cur[226], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 311258 Free buffers 293153 Database pages 17798 Old database pages 6589 Modified db pages 1 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 17529, created 269, written 819 1.86 reads/s, 0.00 creates/s, 2.95 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 17798, unzip_LRU len: 0 I/O sum[0]:cur[226], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=1, Main thread ID=139645190752000, state: sleeping Number of rows inserted 232159, updated 7538, deleted 72, read 74948240 1.14 inserts/s, 10.19 updates/s, 0.00 deletes/s, 62857.63 reads/s
73K 表是糟糕的設計，不利於性能。如果你把它降到2K，那可能是合理的。
有一些設置是表的記憶體；他們將在數百或數千，但不是 73K。
本質上，每個客戶都有自己的 CRM 表集，這些表幾乎都非常相似，只是它只保存他們獨有的數據。通過一些重構，我可以非常輕鬆地將數據複製到具有大量行的幾個表中（這意味著我可能會刪除近 71000 個表）。但是，我想對此提出想法和意見，因為 CRM 中發生了很多數據和操作，所以我認為查找會更快，因為使用多個鍵的需要更少。
作為一般規則，多租戶解決方案不應使用表實現。沒有任何理由讓我有理由相信這條一般規則在這裡不適用。
您有一個自然複合鍵，其中包括
customer_id. 在此基礎上將其劃分為表格，然後在任何其他 id 欄位上，將不再有意義。