⭐⭐⭐ Spring Boot 项目实战	⭐⭐⭐ Spring Cloud 项目实战
《Dubbo 实现原理与源码解析 —— 精品合集》	《Netty 实现原理与源码解析 —— 精品合集》
《Spring 实现原理与源码解析 —— 精品合集》	《MyBatis 实现原理与源码解析 —— 精品合集》
《Spring MVC 实现原理与源码解析 —— 精品合集》	《数据库实体设计合集》
《Spring Boot 实现原理与源码解析 —— 精品合集》	《Java 面试题 + Java 学习指南》

摘要: 原创出处 juejin.cn/post/7444014749321461811 「玛奇玛」欢迎转载，保留摘要，谢谢！

🙂🙂🙂关注微信公众号：【芋道源码】有福利：

RocketMQ / MyCAT / Sharding-JDBC 所有源码分析文章列表

RocketMQ / MyCAT / Sharding-JDBC 中文注释源码 GitHub 地址

您对于源码的疑问每条留言都将得到认真回复。甚至不知道如何读源码也可以请教噢。

新的源码解析文章实时收到通知。每周更新一篇左右。

认真的源码交流微信群。

故事背景

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

我同事也问我，我们数据表有些是上亿数据的，为什么不用分库分表，如果我没接触过分库分表我也会觉得大数据表就要分库分表呀，这是八股文一直以来教导的东西。但是我就跟他说，分库分表很坑爹，最近才让我遇到一个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、按时间归档数据表，每天或者每个月把历史数据存入历史数据表，适用于大数据量且历史数据查询较少的业务。

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

芋道源码 —— 纯源码解析博客

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