专栏名称: 芋道源码

纯 Java 源码分享公众号，目前有「Dubbo」「SpringCloud」「Java 并发」「RocketMQ」「Sharding-JDBC」「MyCAT」「Elastic-Job」「SkyWalking」「Spring」等等

拒绝盲目跟风，分库分表真的不是万能的！

芋道源码 · 公众号 · Java · 2025-01-22 09:36

正文

👉 这是一个或许对你有用的社群
🐱 一对一交流/面试小册/简历优化/求职解惑，欢迎加入「芋道快速开发平台」知识星球。下面是星球提供的部分资料：
《项目实战（视频）》：从书中学，往事上“练”
《互联网高频面试题》：面朝简历学习，春暖花开
《架构 x 系统设计》：摧枯拉朽，掌控面试高频场景题
《精进 Java 学习指南》：系统学习，互联网主流技术栈
《必读 Java 源码专栏》：知其然，知其所以然

👉这是一个或许对你有用的开源项目
国产 Star 破 10w+ 的开源项目，前端包括管理后台 + 微信小程序，后端支持单体和微服务架构。
功能涵盖 RBAC 权限、SaaS 多租户、数据权限、商城、支付、工作流、大屏报表、微信公众号、ERP、CRM、AI 大模型等等功能：
Boot 多模块架构：https://gitee.com/zhijiantianya/ruoyi-vue-pro
Cloud 微服务架构：https://gitee.com/zhijiantianya/yudao-cloud
视频教程：https://doc.iocoder.cn
【国内首批】支持 JDK 17/21 + SpringBoot 3.3、JDK 8/11 + Spring Boot 2.7 双版本

来源：juejin.cn/post/
7444014749321461811

故事背景

在八股文中，说到如何进行数据库的优化，除了基本的索引优化，经常会提到分库分表，说是如果业务量剧增，数据库性能会到达瓶颈，如果单表数据超过两千万，数据查询效率就会变低，就要引入分库分表巴拉巴拉。

我同事也问我，我们数据表有些是上亿数据的，为什么不用分库分表，如果我没接触过分库分表我也会觉得大数据表就要分库分表呀，这是八股文一直以来教导的东西。但是我就跟他说，分库分表很坑爹，最近才让我遇到一个BUG......

基于 Spring Boot + MyBatis Plus + Vue & Element 实现的后台管理系统 + 用户小程序，支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能
项目地址：https://github.com/YunaiV/ruoyi-vue-pro
视频教程：https://doc.iocoder.cn/video/

系统复杂度upup

业务中有个设备表数据量很大，到现在为止已经有5、6亿数据了。在4年前，前人们已经尝试了分库分表技术，分了4个库，5个表，我只是负责维护这个业务发现他们用了分库分表。但是在查询表数据的时候看到是查询ES的，我就问为什么要用ES？

同事回答查询分库分表一定要带分片才能走到路由，否则会查询全部库和全部表，意思是不查分片字段，单表只用一个SQL，但是分库分表要用20个SQL.....所以引入了ES进行数据查询。

但是引入ES之后又引入一个新的问题，就是ES和数据库的数据同步问题。他们使用了logstash做数据同步，但不是实时的，在logstash设置了每20秒同步一次。

因为要使用分库分表，引入了shardingjdbc，因为查询方便引入了es，因为要处理数据同步问题引入了logstash......所以系统复杂度不是高了一点半点，之前发现有个字段长度设置小了，还要改20张表。

基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 实现的后台管理系统 + 用户小程序，支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能
项目地址：https://github.com/YunaiV/yudao-cloud
视频教程：https://doc.iocoder.cn/video/

分页问题

最近遇到一个奇怪的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、按时间归档数据表，每天或者每个月把历史数据存入历史数据表，适用于大数据量且历史数据查询较少的业务。

每个技术都有它的利弊，比如微服务、分库分表、分布式数据库等。按需选择技术类型，切勿过度设计！

欢迎加入我的知识星球，全面提升技术能力。

👉 加入方式，“长按”或“扫描”下方二维码噢：

星球的内容包括：项目实战、面试招聘、源码解析、学习路线。

文章有帮助的话，在看，转发吧。
谢谢支持哟 (*^__^*）