ShardingJDBC有点坑，数据库优化别再无脑回答分库分表了！
-
替换传统机械硬盘（HDD），SSD能提供更快的随机读写速度。 -
增加存储带宽： 采用RAID（推荐RAID 10）提高数据存储的读写速度和冗余。 -
内存扩展： 尽量让数据库缓存更多的数据，减少IO操作。
-
使用多核高频率CPU，支持更高并发。 -
分析数据库对CPU的利用情况，确保不被CPU性能瓶颈限制。
-
优化服务器与客户端之间的网络延迟和带宽，尤其是分布式数据库的场景中。 -
使用高速网络接口（如10GbE网卡）。
-
调整数据库缓冲池（ Buffer Pool）的大小，确保能缓存大部分热数据。 -
优化日志文件的写入（如MySQL中调整 innodb_log_buffer_size）。
-
使用内存数据库或缓存技术（如Redis、Memcached）加速访问速度。
-
选择合适的索引类型：如B+树索引、哈希索引等，根据查询特点选择适配的索引。 -
避免冗余索引，定期清理无用索引。
-
减少不必要的字段： 只查询需要的列，避免使用 SELECT *。 -
加速排序和分组： 在 ORDER BY和GROUP BY字段上建立索引。 -
拆分复杂查询： 将复杂的SQL分解为多个简单查询或视图。 -
分页查询优化： 如避免大 OFFSET分页，可以使用索引条件替代（如WHERE id > last_seen_id）。
-
对频繁用于 WHERE、JOIN、GROUP BY等的字段建立索引。 -
避免在索引列上使用函数或隐式转换。
-
尽量使用小事务，减少锁定范围。 -
使用合适的事务隔离级别，避免不必要的资源等待。
EXPLAIN、SQL Server的性能监控工具）来分析查询计划并优化执行路径。-
定期进行性能分析： 定期查看慢查询日志，优化慢查询。 -
清理历史数据： 对于不再使用的历史数据，可存储到冷数据仓库，减少主数据库的负载。 -
使用连接池： 通过数据库连接池（如 HikariCP）管理和复用连接，降低创建和销毁连接的开销。
[universe]
bakupdir = /data/mysql/backup/7360
iops = 0
mem_limit_mb = 0
cpu_quota_percentage = 0
quota_limit_mb = 0
scsi_pr_level = 0
mycnf = /opt/mysql/etc/7360/my.cnf
run_user = actiontech-mysql
umask_dir = 0750
umask = 0640
id = mysql-mt1cbg
group_id = mysql-test
[mysql]
no-auto-rehash
prompt = '\\u@\\h:\\p\\R:\\m:\\s[\\d]> '
#default-character-set = utf8mb4
#tee = /data/mysql_tmp/mysql_operation.log
[mysqld]
super_read_only = 1
# DO NOT MODIFY, Universe will generate this part
port = 7360
server_id = 123
basedir = /opt/mysql/base/5.7.40
datadir = /data/mysql/data/7360
log_bin = /opt/mysql/log/binlog/7360/mysql-bin
tmpdir = /opt/mysql/tmp/7360
relay_log = /opt/mysql/log/relaylog/7360/mysql-relay
innodb_log_group_home_dir = /opt/mysql/log/redolog/7360
log_error = /data/mysql/data/7360/mysql-error.log
# 数据库ip
report_host = xxx
# BINLOG
binlog_error_action = ABORT_SERVER
binlog_format = row
binlog_rows_query_log_events = 1
log_slave_updates = 1
master_info_repository = TABLE
max_binlog_size = 250M
relay_log_info_repository = TABLE
relay_log_recovery = 1
sync_binlog = 1
# GTID #
gtid_mode = ON
enforce_gtid_consistency = 1
binlog_gtid_simple_recovery = 1
# ENGINE
default_storage_engine = InnoDB
innodb_buffer_pool_size = 64G
innodb_data_file_path = ibdata1:1G:autoextend
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 1000
innodb_log_buffer_size = 64M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 60
innodb_print_all_deadlocks = 1
#innodb_stats_on_metadata = 0
innodb_strict_mode = 1
#innodb_undo_logs = 128 #Deprecated In 5.7.19
#innodb_undo_tablespaces=3 #Deprecated In 5.7.21
innodb_max_undo_log_size = 4G
innodb_undo_log_truncate = 1
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_purge_threads = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_dump_pct = 25
innodb_sort_buffer_size = 8M
#innodb_page_cleaners = 8
innodb_buffer_pool_instances = 8
innodb_lock_wait_timeout = 10
innodb_io_capacity_max = 2000
innodb_flush_neighbors = 1
#innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_stats_persistent_sample_pages = 64
innodb_autoinc_lock_mode = 2
innodb_online_alter_log_max_size = 1G
innodb_open_files = 4096
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:50G
innodb_rollback_segments = 128
#innodb_numa_interleave = 1
# CACHE
key_buffer_size = 16M
tmp_table_size = 64M
max_heap_table_size = 64M
table_open_cache = 2000
query_cache_type = 0
query_cache_size = 0
max_connections = 3000
thread_cache_size = 200
open_files_limit = 65535
binlog_cache_size = 1M
join_buffer_size = 8M
sort_buffer_size = 2M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
table_definition_cache = 2000
table_open_cache_instances = 8
# SLOW LOG
slow_query_log = 1
slow_query_log_file = /data/mysql/data/7360/mysql-slow.log
log_slow_admin_statements = 1
log_slow_slave_statements = 1
long_query_time = 1
# SEMISYNC #
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_slave_enabled = 0
rpl_semi_sync_master_wait_for_slave_count = 1
rpl_semi_sync_master_wait_no_slave = 0
rpl_semi_sync_master_timeout = 30000
# CLIENT_DEPRECATE_EOF
session_track_schema = 1
session_track_state_change = 1
session_track_system_variables = '*'
# MISC
log_timestamps = SYSTEM
lower_case_table_names = 1
max_allowed_packet = 64M
read_only = 1
skip_external_locking = 1
skip_name_resolve = 1
skip_slave_start = 1
socket = /data/mysql/data/7360/mysqld.sock
pid_file = /data/mysql/data/7360/mysqld.pid
disabled_storage_engines = ARCHIVE,BLACKHOLE,EXAMPLE,FEDERATED,MEMORY,MERGE,NDB
log-output = TABLE,FILE
character_set_server = utf8mb4
secure_file_priv = ""
performance-schema-instrument = 'wait/lock/metadata/sql/mdl=ON'
performance-schema-instrument = 'memory/% = COUNTED'
expire_logs_days = 7
max_connect_errors = 1000000
interactive_timeout = 1800
wait_timeout = 1800
log_bin_trust_function_creators = 1
# MTS
slave-parallel-type = LOGICAL_CLOCK
slave_parallel_workers = 16
slave_preserve_commit_order = ON
slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN'
##BaseConfig
collation_server = utf8mb4_bin
explicit_defaults_for_timestamp = 1
transaction_isolation = READ-COMMITTED
##Unused
#plugin-load-add = validate_password.so
#validate_password_policy = MEDIUM
摘抄自网络，便于检索查找。
浙公网安备 33010602011771号