来源：juejin.cn/post/7444014749321461811
👉 欢迎加入小哈的星球，你将获得: 专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论
新项目:《从零手撸：仿小红书（微服务架构）》 正在持续爆肝中，基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17..., 点击查看项目介绍； 《从零手撸：前后端分离博客项目（全栈开发）》 2期已完结,演示链接：http://116.62.199.48/; 截止目前，累计输出 73w+ 字，讲解图 3088+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，Spring Cloud Alibaba 等等，戳我加入学习，解锁全部项目，已有2500+小伙伴加入
故事背景
在八股文中，说到如何进行数据库的优化，除了基本的索引优化，经常会提到分库分表，说是如果业务量剧增，数据库性能会到达瓶颈，如果单表数据超过两千万，数据查询效率就会变低，就要引入分库分表巴拉巴拉。
我同事也问我，我们数据表有些是上亿数据的，为什么不用分库分表，如果我没接触过分库分表我也会觉得大数据表就要分库分表呀，这是八股文一直以来教导的东西。但是我就跟他说，分库分表很坑爹，最近才让我遇到一个BUG......
系统复杂度upup
业务中有个设备表数据量很大，到现在为止已经有5、6亿数据了。在4年前，前人们已经尝试了分库分表技术，分了4个库，5个表，我只是负责维护这个业务发现他们用了分库分表。但是在查询表数据的时候看到是查询ES的，我就问为什么要用ES？
同事回答查询分库分表一定要带分片才能走到路由，否则会查询全部库和全部表，意思是不查分片字段，单表只用一个SQL，但是分库分表要用20个SQL.....所以引入了ES进行数据查询。
但是引入ES之后又引入一个新的问题，就是ES和数据库的数据同步问题。他们使用了logstash做数据同步，但不是实时的，在logstash设置了每20秒同步一次。
因为要使用分库分表，引入了shardingjdbc，因为查询方便引入了es，因为要处理数据同步问题引入了logstash......所以系统复杂度不是高了一点半点，之前发现有个字段长度设置小了，还要改20张表。
分页问题
最近遇到一个奇怪的bug，在一个设备的单表查询翻页失败，怎么翻都只显示第一页的数据，一开始我以为是分页代码有问题，看了半天跟其他表是一样的，其他表分页没问题，见鬼了。后面再细看发现这个单表的数据源是设备数据源，用的是shardingjdbc的配置。
之前就看过shardingjdbc有一些sql是不支持的，怀疑就是这个原因，百度了一下果然是有bug。
想了一下有两个解决办法，第一个是升级shardingjdbc的版本，据说是4.1之后修复了该问题，但是还没有尝试。
第二个办法是把分库分表业务的数据源跟单表区分开，单表业务使用普通的数据源后分页数据正常显示。
关于数据库优化
一般来说数据库优化，可以从几个角度进行优化：
1、硬件优化
1) 提升存储性能
使用SSD：替换传统机械硬盘（HDD），SSD能提供更快的随机读写速度。 增加存储带宽：采用RAID（推荐RAID 10）提高数据存储的读写速度和冗余。 内存扩展：尽量让数据库缓存更多的数据，减少IO操作。
2) 增强CPU性能
使用多核高频率CPU，支持更高并发。 分析数据库对CPU的利用情况，确保不被CPU性能瓶颈限制。
3) 提高网络带宽
优化服务器与客户端之间的网络延迟和带宽，尤其是分布式数据库的场景中。 使用高速网络接口（如10GbE网卡）。
2、软件层面优化
1) 数据库配置
调整数据库缓冲池（Buffer Pool）的大小，确保能缓存大部分热数据。 优化日志文件的写入（如MySQL中调整 innodb_log_buffer_size）。使用内存数据库或缓存技术（如Redis、Memcached）加速访问速度。
2) 分布式架构
对于高并发需求，采用分布式数据库（如TiDB、MongoDB）进行读写分离或数据分片。
3) 数据库索引
选择合适的索引类型：如B+树索引、哈希索引等，根据查询特点选择适配的索引。 避免冗余索引，定期清理无用索引。
4) 数据库版本升级
保持数据库版本为最新的稳定版本，利用最新的优化特性和Bug修复。
3. SQL层面优化
1) 查询优化
减少不必要的字段：只查询需要的列，避免使用 SELECT *。加速排序和分组：在 ORDER BY和GROUP BY字段上建立索引。拆分复杂查询：将复杂的SQL分解为多个简单查询或视图。 分页查询优化：如避免大OFFSET分页，可以使用索引条件替代（如 WHERE id > last_seen_id）。
2) 合理使用索引
对频繁用于WHERE、JOIN、GROUP BY等的字段建立索引。 避免在索引列上使用函数或隐式转换。
3) 减少锁定
尽量使用小事务，减少锁定范围。 使用合适的事务隔离级别，避免不必要的资源等待。
4) SQL调优工具
使用数据库自带的分析工具（如MySQL的EXPLAIN、SQL Server的性能监控工具）来分析查询计划并优化执行路径。
4. 综合优化
定期进行性能分析：定期查看慢查询日志，优化慢查询。 清理历史数据：对于不再使用的历史数据，可存储到冷数据仓库，减少主数据库的负载。 使用连接池：通过数据库连接池（如HikariCP）管理和复用连接，降低创建和销毁连接的开销。
tips:
现网的数据库是64核128G内存，测试环境是32核64G，加上现网数据库配置的优化，现网数据库查询大表的速度是测试环境的3倍！所以服务器硬件配置和数据库配置都很重要。下面是数据库的配置文件，仅供参考
[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
总结
如果我没用过分库分表，面试官问我数据库优化，我可能也会回答分库分表。但是踩过几个坑之后可能会推荐其他的方式。
1、按业务分表，比如用户表放在用户库，订单表放在订单库，用微服务的思想切割数据库减少数据库压力。
2、如果数据量超过10E，可以考虑上分布式数据库，融合了OLAP和OLTP的优点，毕竟mysql其实不适合做大数据量的查询统计。评论区也可以推荐一下有哪些好的数据库。
3、按时间归档数据表，每天或者每个月把历史数据存入历史数据表，适用于大数据量且历史数据查询较少的业务。
每个技术都有它的利弊，比如微服务、分库分表、分布式数据库等。按需选择技术类型，切勿过度设计！
👉 欢迎加入小哈的星球，你将获得: 专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论
新项目:《从零手撸：仿小红书（微服务架构）》 正在持续爆肝中，基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17..., 点击查看项目介绍； 《从零手撸：前后端分离博客项目（全栈开发）》 2期已完结,演示链接：http://116.62.199.48/; 截止目前，累计输出 73w+ 字，讲解图 3088+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，Spring Cloud Alibaba 等等，戳我加入学习，解锁全部项目，已有2500+小伙伴加入
2. 弃用 RestTemplate，来了解一下官方推荐的 WebClient 吧！
最近面试BAT，整理一份面试资料《Java面试BATJ通关手册》，覆盖了Java核心技术、JVM、Java并发、SSM、微服务、数据库、数据结构等等。
获取方式：点“在看”，关注公众号并回复 Java 领取，更多内容陆续奉上。
PS：因公众号平台更改了推送规则，如果不想错过内容，记得读完点一下“在看”，加个“星标”，这样每次新文章推送才会第一时间出现在你的订阅列表里。
点“在看”支持小哈呀，谢谢啦
