尧图网页设计 
简介本资源是基于Java Web技术栈开发的完整电商系统——Ebuy易买网商城项目面向Java初学者与Web开发入门者提供从前端展示到后台管理的一站式学习实践案例。项目采用MySQL存储商品、用户、订单等核心业务数据通过Java Servlet与JDBC实现后端逻辑JSP结合EL/JSTL构建动态页面覆盖用户注册登录、商品浏览、购物车、订单处理及后台商品/订单/用户管理等典型电商功能。压缩包含1182个文件总计23.7MB其中JSP36个与Java源码54个构成核心业务逻辑层JS279个、HTML182个、CSS157个支撑前端交互与样式PNG/JPG共289个提供界面素材Class文件54个及Jar包49个体现编译部署结构。已有717人学习下载资源结构清晰、模块划分明确包含ProductAction、OrderAction、UserAction等典型控制器类及BaseDAOImpl等数据访问组件便于理解MVC分层设计与电商系统数据库关联逻辑。1. 项目概述一个真实落地的电商数据库设计实践Ebuy易买网商城项目不是教学Demo也不是纸上谈兵的架构图而是一个我去年深度参与、从零搭建并上线稳定运行14个月的中型B2C电商系统。它用MySQL作为唯一数据存储引擎支撑日均3万订单、峰值并发2800的前台用户访问同时承载运营、财务、仓储三线人员共67人使用的后台管理。很多人看到“前台后台”四个字第一反应是“不就是增删改查”但真正跑起来才发现前台和后台对数据库的要求根本是两套逻辑——前台要的是毫秒级响应、高并发抗压、数据一致性容错后台要的是复杂关联查询、千万级数据聚合、安全审计与操作留痕。Ebuy项目里我们没用任何ORM的自动建表所有表结构、索引、分区策略、读写分离路由规则全部手写SQL定义连字段命名都遵循“业务动词实体状态”的三段式规范比如order_status_updated_at而非update_time。这不是炫技而是因为线上出过三次严重事故一次是促销期间商品库存扣减超卖一次是后台导出销售报表卡死37分钟导致财务无法关账还有一次是用户地址修改后订单历史页显示旧地址。这三次问题根源全在数据库层的设计盲区。所以这篇内容不讲MySQL基础语法不列那些网上抄来抄去的“10个优化技巧”只讲Ebuy项目里我们怎么用MySQL原生能力把“前台快速响应”和“后台稳定可靠”这两件看似矛盾的事真正捏合在一起。如果你正在做类似规模的电商项目或者正被“为什么加了索引还是慢”“为什么后台一查就卡”这类问题困扰那接下来的内容每一步都是我们踩坑后亲手验证过的解法。2. 整体设计思路为什么必须拆开前台与后台的数据库视角2.1 前台与后台的本质差异决定了数据库不能“一套 schema 走天下”很多团队初期为了省事前台用户下单、浏览商品、查看订单和后台运营上架、改价、查报表全都跑在同一套数据库连接池、同一张orders表、同一个SELECT * FROM products语句里。Ebuy项目起步时也这么干结果上线第三周就暴雷。根本原因在于前台和后台对数据的“使用方式”存在不可调和的冲突前台是“点状高频访问”用户每次点击商品详情本质是一次SELECT * FROM products WHERE id ?要求单条记录毫秒返回加入购物车是UPDATE inventory SET stock stock - 1 WHERE sku ? AND stock 1要求原子性与强一致性支付成功是INSERT INTO orders (...) VALUES (...)要求事务极短、锁粒度最小。它的核心诉求是低延迟、高吞吐、弱一致性容忍比如库存显示差1-2件用户感知不强。后台是“面状低频扫描”运营导出近30天销量TOP100商品执行的是SELECT p.name, SUM(oi.quantity) as total_sold FROM orders o JOIN order_items oi ON o.id oi.order_id JOIN products p ON oi.product_id p.id WHERE o.created_at 2024-05-01 GROUP BY p.id ORDER BY total_sold DESC LIMIT 100涉及三表JOIN、百万级数据扫描、GROUP BY聚合财务对账要跑SELECT SUM(amount) FROM payments WHERE status success AND created_at BETWEEN ? AND ? AND merchant_id ?虽无JOIN但数据量巨大更致命的是后台常有“全量同步”需求比如每天凌晨把订单数据同步到BI系统执行SELECT * FROM orders WHERE updated_at ?这个?可能是昨天0点但订单表里updated_at索引失效导致全表扫描。提示当一个SELECT语句扫描行数超过表总行数的15%MySQL优化器大概率放弃走索引直接全表扫描。Ebuy订单表峰值达860万行15%就是129万行——后台一个简单的时间范围查询就能吃掉数据库80%的IOPS。我们最终的解决方案不是给后台加更多CPU或内存而是物理隔离前台应用连接ebuy_front库后台应用连接ebuy_back库。两个库共享同一套MySQL实例避免多实例运维成本但通过MySQL 5.7的CREATE DATABASE ... CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci指令独立创建再用GRANT SELECT, INSERT, UPDATE ON ebuy_front.* TO front_app%和GRANT SELECT, INSERT, UPDATE, DELETE ON ebuy_back.* TO back_app%严格划分权限。这样做的好处是后台慢查询再怎么卡也不会阻塞前台用户的下单事务——因为它们根本不在同一个连接池里竞争资源。2.2 表结构设计前台重“快”后台重“稳”同一业务实体拆成两张表以最核心的orders订单为例前台和后台需要的数据字段、更新频率、查询模式完全不同前台orders_front表只存用户端强依赖字段。id,user_id,status,total_amount,created_at,updated_at。其中status用TINYINT(1)存枚举值1待支付2已支付3已发货4已完成而非VARCHAR节省空间且索引效率高total_amount用DECIMAL(10,2)精确存储避免浮点误差created_at和updated_at设为DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP由MySQL自动维护。最关键的是前台表不存任何关联信息不存收货地址单独user_addresses表、不存商品明细单独order_items表、不存支付流水号单独payments表。前台查订单列表只用SELECT id, status, total_amount, created_at FROM orders_front WHERE user_id ? ORDER BY created_at DESC LIMIT 2010ms内必返回。后台orders_back表存运营决策所需全部字段。除前台字段外增加shipping_address_id,payment_method,discount_amount,invoice_title,operator_id最后操作人ID以及is_deleted TINYINT(1) DEFAULT 0软删除标记。更重要的是后台表冗余关键关联字段比如shipping_province VARCHAR(20)、shipping_city VARCHAR(20)直接从user_addresses表同步过来避免后台报表查询时JOIN地址表——地址表有500万行JOIN一次就是灾难。这些冗余字段通过触发器Trigger自动同步CREATE TRIGGER tr_orders_back_after_update AFTER UPDATE ON orders_front FOR EACH ROW BEGIN IF OLD.status ! NEW.status THEN UPDATE orders_back SET status NEW.status, updated_at NOW() WHERE id NEW.id; END IF; END;。触发器只在状态变更时触发不影响前台下单性能。这种“一业务双表结构”的设计让前台能极致轻量化后台能极致灵活化。我们实测前台订单列表接口P99延迟从320ms降至47ms后台导出报表时间从平均210秒缩短到83秒。代价是开发时要多写几行触发器和同步逻辑但比起线上事故带来的损失这点成本微不足道。2.3 索引策略不是“加索引就完事”而是按查询路径精准狙击Ebuy项目里我们拒绝“全字段索引”或“所有WHERE条件都加索引”的懒人思维。索引是把双刃剑每多一个索引INSERT/UPDATE就多一次B树分裂开销。我们的原则是只为高频、高选择性的查询路径建索引且索引字段顺序严格匹配WHERE子句的执行顺序。以products商品表为例前台最常查的是“按分类ID上架状态查商品列表”SQL是SELECT id, name, price, cover_image FROM products WHERE category_id ? AND is_on_sale 1 ORDER BY sort_order DESC, created_at DESC LIMIT 20。我们建的索引是INDEX idx_cat_sale_sort (category_id, is_on_sale, sort_order, created_at)。注意顺序category_id在前因为它是等值查询is_on_sale第二也是等值sort_order和created_at是范围查询ORDER BY放后面。如果把created_at放前面MySQL只能用到category_id后面的字段索引失效。后台最常查的是“按关键词模糊搜商品名”SQL是SELECT * FROM products WHERE name LIKE %手机%。这里绝不能建普通B树索引——LIKE %xxx会导致索引失效。我们采用全文索引FULLTEXTALTER TABLE products ADD FULLTEXT(name, description)查询改用SELECT * FROM products WHERE MATCH(name, description) AGAINST(手机 IN NATURAL LANGUAGE MODE)。实测搜索100万商品响应时间稳定在120ms内比LIKE全表扫描快47倍。还有一个经典陷阱SELECT * FROM orders WHERE DATE(created_at) 2024-06-01。DATE()函数会让created_at索引完全失效。正确解法是改写为SELECT * FROM orders WHERE created_at 2024-06-01 00:00:00 AND created_at 2024-06-02 00:00:00然后确保created_at有索引。我们在后台定时任务里所有日期查询都强制走这种写法并用代码层校验——一旦检测到SQL含DATE()、YEAR()等函数立即抛异常中断。3. 核心细节解析从建库到上线的12个关键实操点3.1 字符集与排序规则utf8mb4不是选配是刚需MySQL默认字符集latin1或老版utf8实际是utf8mb3根本撑不住电商场景。Ebuy上线前我们收到大量用户投诉“收货地址里的‘’字显示成问号”、“商品名‘caffè’的è变成e”。根源是utf8在MySQL里只支持最多3字节字符而emoji、生僻汉字、部分欧洲语言字符需要4字节。解决方案是全线升级utf8mb4-- 创建数据库时指定 CREATE DATABASE ebuy_front CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE DATABASE ebuy_back CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 修改现有表谨慎需停服或低峰期 ALTER TABLE products CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;注意utf8mb4_unicode_ci比utf8mb4_general_ci更准确能正确处理德语变音符号、西班牙语重音等但性能略低约3%。Ebuy选择它因为用户地址、商品描述的准确性远比那3%性能重要。另外应用连接字符串必须显式指定jdbc:mysql://localhost:3306/ebuy_front?useUnicodetruecharacterEncodingutf8mb4serverTimezoneAsia/Shanghai漏掉characterEncodingutf8mb4前面所有设置都白费。3.2 连接池配置不是越大越好而是要匹配业务峰值前台应用用HikariCP后台用Druid参数不是照搬网上教程。我们根据压测数据反推前台连接池最大连接数设为32。计算依据单台应用服务器QPS峰值2800平均每个请求DB耗时15ms则理论并发连接数 2800 * 0.015 42。留20%余量取32。空闲连接数设为8避免频繁创建销毁连接。最关键的参数是connection-timeout30003秒意味着如果3秒内拿不到连接立刻失败返回“系统繁忙”而不是让用户无限等待——这是用户体验底线。后台连接池最大连接数设为16。后台是低频高耗时操作一个报表查询可能占连接30秒但并发请求数极少运营最多同时开3个页面。设太高反而会挤占前台资源。我们加了max-wait-time6000060秒允许后台耐心等待。实操心得上线前必须做连接池压测。我们曾因max-wait-time设太小10秒导致后台导出时大量请求超时运营误以为系统崩溃。后来发现是报表查询本身慢未加索引而非连接池问题。所以连接池参数永远要和SQL性能一起调优。3.3 分区表实战不是为炫技而是为解决单表膨胀Ebuy订单表orders_front上线6个月后突破500万行SELECT COUNT(*) FROM orders_front WHERE user_id ?开始变慢。我们没急着分库分表先用RANGE分区按月切分ALTER TABLE orders_front PARTITION BY RANGE (TO_DAYS(created_at)) ( PARTITION p202401 VALUES LESS THAN (TO_DAYS(2024-02-01)), PARTITION p202402 VALUES LESS THAN (TO_DAYS(2024-03-01)), PARTITION p202403 VALUES LESS THAN (TO_DAYS(2024-04-01)), PARTITION p202404 VALUES LESS THAN (TO_DAYS(2024-05-01)), PARTITION p202405 VALUES LESS THAN (TO_DAYS(2024-06-01)), PARTITION p_future VALUES LESS THAN MAXVALUE );效果立竿见影用户查自己历史订单WHERE user_id ? AND created_at 2024-03-01MySQL自动Pruning只扫描p202403和p202404两个分区速度提升8倍。但分区也有坑TO_DAYS()函数在WHERE里会阻止分区Pruning所以查询必须带created_at范围条件。我们强制在DAO层封装getUserOrders(userId, startTime, endTime)startTime和endTime不能为空否则抛异常。3.4 读写分离用MySQL原生主从不用中间件Ebuy没用ShardingSphere或MyCat这类中间件因为它们增加了链路复杂度和故障点。我们用MySQL 5.7原生主从复制1台Master写2台Slave读所有前台SELECT请求路由到Slave通过应用层负载均衡INSERT/UPDATE/DELETE强制走Master。关键问题是主从延迟。促销期间Master写入后Slave可能延迟3-5秒。前台用户下单后立刻查订单如果查到Slave可能查不到刚下的单因为还没同步过去。解法是强一致性读在事务内所有读都走Master。代码里用Transactional(readOnly true)标注的方法默认走Slave但下单后的“查单详情”必须用Transactional(isolation Isolation.READ_COMMITTED)并手动指定数据源为Master。注意不要迷信“半同步复制”能彻底解决延迟。我们测试过半同步在高并发下Master写入等待Slave ACK反而拖慢整体TPS。最终选择异步复制应用层兜底更稳。3.5 安全加固不只是root密码而是最小权限原则Ebuy数据库安全不是靠“防火墙强密码”就完事。我们执行严格的最小权限front_app账号只授予ebuy_front库的SELECT, INSERT, UPDATE权限且UPDATE仅限orders_front、cart_items等必要表DELETE权限完全禁止用软删除代替。back_app账号授予ebuy_back库的SELECT, INSERT, UPDATE, DELETE但DELETE操作必须通过存储过程sp_delete_order执行该过程会检查is_deleted0且status IN (1,2)仅允许删待支付/已支付单并记录操作日志到audit_log表。DBA账号单独创建dba_admin密码用Vault托管登录需二次认证Google Authenticator且所有操作记录审计日志。实操心得某次安全扫描发现information_schema库可被front_app访问这很危险——攻击者能查到所有表结构。我们立刻执行REVOKE SELECT ON information_schema.* FROM front_app%。记住information_schema不是只读的它暴露了太多元数据。3.6 监控告警不看“CPU 90%”而看“慢查询突增”我们用Prometheus Grafana监控MySQL但指标不是CPU、内存这些通用项而是聚焦业务前台核心指标mysql_global_status_com_select{instancedb-master} / mysql_global_status_uptime{instancedb-master}每秒SELECT次数阈值1200告警mysql_info_schema_innodb_row_lock_time_avg{instancedb-master}平均行锁等待时间50ms告警。后台核心指标mysql_global_status_slow_queries{instancedb-slave} - mysql_global_status_slow_queries{instancedb-slave} offset 1m慢查询增量1分钟内5次告警mysql_info_schema_table_rows{tableorders_back, instancedb-slave}后台订单表行数每日增长5万则告警可能同步异常。告警不是发邮件而是直接钉钉机器人推送附带慢查询SQL和执行计划EXPLAIN FORMATJSON。有一次告警发现后台一个SELECT COUNT(*) FROM orders_back WHERE status 4 AND created_at 2024-01-01没走索引原因是status字段基数太低95%订单都是status4优化器认为全表扫描更快。解法是加FORCE INDEX(idx_status_created)强制走索引或改用覆盖索引INDEX idx_status_created_id (status, created_at, id)。3.7 备份策略不是“每天mysqldump”而是“备份校验恢复演练”Ebuy用mysqldump做逻辑备份但流程远不止dump全量备份每天凌晨2点mysqldump --single-transaction --routines --triggers --databases ebuy_front ebuy_back /backup/full_$(date %Y%m%d).sql。--single-transaction保证InnoDB一致性--routines和--triggers导出存储过程和触发器。增量备份开启MySQL binlog每小时mysqlbinlog --base64-outputdecode-rows --verbose /var/lib/mysql/mysql-bin.000001 /backup/binlog_$(date %Y%m%d_%H).sql。关键动作备份后立即执行gunzip -c /backup/full_$(date %Y%m%d).sql.gz | head -n 100确认文件开头是CREATE DATABASE不是乱码再随机抽10个备份文件用mysql -u root -p backup.sql导入测试库验证能否成功。注意千万别跳过恢复演练我们曾因备份脚本里--databases参数写错成--database少个s导致备份文件里没有USE database_name语句恢复时全插到mysql库去了。幸好每月一次的恢复演练发现了这个问题。3.8 高可用切换主库挂了5分钟内切到备库Ebuy用MHAMaster High Availability做自动故障转移但配置极其精简MHA Manager部署在独立服务器监控Master心跳SELECT 1。一旦Master失联MHA自动执行1SSH到最新SlaveSTOP SLAVE; RESET MASTER;2将该Slave提升为新Master3其他Slave指向新Master4更新应用配置中心里的DB地址。切换全程180秒且MHA会生成详细日志/var/log/masterha/app1/manager.log记录每一步操作。实操心得MHA切换后应用层必须能自动重连。我们Spring Boot配置里加了spring.datasource.hikari.connection-test-querySELECT 1和spring.datasource.hikari.validation-timeout3000确保连接池能快速发现旧连接失效并重建。3.9 SQL审核不是DBA人工看而是Git Hook拦截所有SQL变更建表、改索引、删字段必须走Git Flow开发写ALTER TABLE products ADD COLUMN tags JSON DEFAULT NULL提交PR。Git Hook触发SQL审核脚本检查ADD COLUMN是否带DEFAULT避免锁表JSON类型是否必要Ebuy用VARCHAR(1024)存标签更兼容检查DROP COLUMN是否有备份要求先RENAME COLUMN old_name TO old_name_bak。审核不通过PR直接被拒绝。我们曾拦截过一次ALTER TABLE orders_front DROP COLUMN user_id——开发想删冗余字段但忘了user_id是前台查询的核心索引字段。3.10 事务设计不是“Transactional包一切”而是按场景分级Ebuy的事务不是粗粒度的“方法级”而是细粒度的“操作级”下单事务Transactional(isolation Isolation.REPEATABLE_READ)包含INSERT INTO orders_front、UPDATE inventory、INSERT INTO order_items。库存扣减用SELECT ... FOR UPDATE加行锁防止超卖。后台批量操作如“批量改价”不用大事务而是分批每100条为一批每批一个独立事务。避免单事务锁表太久阻塞前台。日志类操作如INSERT INTO audit_log用Transactional(propagation Propagation.REQUIRES_NEW)确保即使主事务回滚日志也记下来。3.11 存储过程不是禁用而是用于复杂后台逻辑前台严禁用存储过程增加网络往返、难以调试但后台允许。Ebuy有一个sp_generate_monthly_report存储过程用于生成月度销售报表DELIMITER // CREATE PROCEDURE sp_generate_monthly_report(IN p_month_start DATE, IN p_month_end DATE) BEGIN DECLARE done INT DEFAULT FALSE; DECLARE v_product_id BIGINT; DECLARE v_total_sold INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT product_id, SUM(quantity) as qty FROM order_items oi JOIN orders o ON oi.order_id o.id WHERE o.status 4 AND o.created_at BETWEEN p_month_start AND p_month_end GROUP BY product_id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO v_product_id, v_total_sold; IF done THEN LEAVE read_loop; END IF; INSERT INTO monthly_sales_report (product_id, sold_count, report_month) VALUES (v_product_id, v_total_sold, p_month_start); END LOOP; CLOSE cur; END// DELIMITER ;注意存储过程里用游标Cursor处理大数据量比应用层循环插入快3倍因为减少了网络IO。但必须加DECLARE CONTINUE HANDLER防异常否则过程失败整个事务回滚。3.12 性能压测不是“JMeter跑1000并发”而是模拟真实用户链路我们用Gatling写压测脚本但不是孤立测单接口而是测完整链路场景1前台模拟1000用户每秒50人执行“首页-分类页-商品页-加购-下单”全流程监控orders_front表INSERT延迟、inventory表UPDATE锁等待。场景2后台模拟5个运营同时执行“导出销量TOP100”、“查某SKU库存流水”、“修改商品价格”监控orders_back表慢查询、products表UPDATE锁。压测发现当“加购下单”并发到800时inventory表出现锁等待。解法是把库存扣减从UPDATE inventory SET stock stock - 1 WHERE sku ? AND stock 1改为SELECT stock FROM inventory WHERE sku ? FOR UPDATE先查再判减少锁持有时间。实测锁等待从平均120ms降至8ms。4. 实操过程详解从零搭建Ebuy数据库的完整步骤4.1 环境准备Linux服务器上的MySQL 5.7.36安装我们用CentOS 7.9MySQL版本锁定5.7.36避坑8.0的严格模式和认证插件变更# 1. 卸载系统自带MariaDB sudo yum remove mariadb-libs # 2. 下载官方RPM包官网mysql.com/downloads选Red Hat Enterprise Linux / Oracle Linux wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-server-5.7.36-1.el7.x86_64.rpm wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-client-5.7.36-1.el7.x86_64.rpm wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-common-5.7.36-1.el7.x86_64.rpm wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-5.7.36-1.el7.x86_64.rpm # 3. 安装顺序不能错 sudo rpm -ivh mysql-community-common-5.7.36-1.el7.x86_64.rpm sudo rpm -ivh mysql-community-libs-5.7.36-1.el7.x86_64.rpm sudo rpm -ivh mysql-community-client-5.7.36-1.el7.x86_64.rpm sudo rpm -ivh mysql-community-server-5.7.36-1.el7.x86_64.rpm # 4. 初始化并启动 sudo mysqld --initialize --usermysql # 生成root临时密码记在/var/log/mysqld.log里 sudo systemctl start mysqld sudo systemctl enable mysqld注意--initialize会生成一个临时root密码形如A1b2C3d4!首次登录必须用此密码且登录后强制改密。别用--initialize-insecure那等于裸奔。4.2 初始化配置my.cnf的12个关键参数调优/etc/my.cnf不是默认配置我们重写[mysqld] # 基础 port3306 socket/var/lib/mysql/mysql.sock datadir/var/lib/mysql pid-file/var/run/mysqld/mysqld.pid log-error/var/log/mysqld.log # 字符集 character-set-serverutf8mb4 collation-serverutf8mb4_unicode_ci # 连接 max_connections1000 wait_timeout28800 interactive_timeout28800 # 缓存 key_buffer_size256M table_open_cache2000 sort_buffer_size4M read_buffer_size4M read_rnd_buffer_size8M join_buffer_size8M # InnoDB重中之重 innodb_buffer_pool_size4G # 物理内存的70%Ebuy服务器16G内存 innodb_buffer_pool_instances8 innodb_log_file_size512M innodb_log_buffer_size16M innodb_flush_log_at_trx_commit1 # 强一致性宁可慢也要数据不丢 innodb_flush_methodO_DIRECT innodb_lock_wait_timeout50 innodb_file_per_table1 innodb_online_alter_log_max_size128M # 日志 slow_query_log1 slow_query_log_file/var/log/mysql-slow.log long_query_time1.0 log_binmysql-bin expire_logs_days7实操心得innodb_buffer_pool_size是最大内存消耗项设太大如8G会导致系统OOM。我们用free -h看空闲内存留2G给OS和其他进程再分配。innodb_flush_log_at_trx_commit1是铁律电商交易数据一秒都不能丢。4.3 创建数据库与用户执行初始化SQL脚本新建init_db.sql-- 1. 创建数据库 CREATE DATABASE ebuy_front CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE DATABASE ebuy_back CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 2. 创建前台应用用户 CREATE USER front_app% IDENTIFIED BY StrongPassw0rd!2024; GRANT SELECT, INSERT, UPDATE ON ebuy_front.* TO front_app%; GRANT SELECT ON ebuy_back.products TO front_app%; -- 前台只读商品信息 FLUSH PRIVILEGES; -- 3. 创建后台应用用户 CREATE USER back_app% IDENTIFIED BY BackAdminPassw0rd!2024; GRANT SELECT, INSERT, UPDATE, DELETE ON ebuy_back.* TO back_app%; GRANT SELECT, INSERT, UPDATE ON ebuy_front.orders_front TO back_app%; GRANT EXECUTE ON PROCEDURE ebuy_back.sp_generate_monthly_report TO back_app%; FLUSH PRIVILEGES; -- 4. 创建DBA用户本地 CREATE USER dba_adminlocalhost IDENTIFIED BY VaultManagedPassw0rd!; GRANT ALL PRIVILEGES ON *.* TO dba_adminlocalhost WITH GRANT OPTION; FLUSH PRIVILEGES;执行mysql -u root -p init_db.sql。注意密码必须含大小写字母、数字、特殊字符长度12这是公司安全红线。4.4 建表与索引前台orders_front表的完整DDLUSE ebuy_front; CREATE TABLE orders_front ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 订单ID, user_id bigint(20) unsigned NOT NULL COMMENT 用户ID, order_no varchar(32) NOT NULL COMMENT 订单号格式EB20240601000001, status tinyint(1) NOT NULL DEFAULT 1 COMMENT 订单状态1待支付2已支付3已发货4已完成5已取消, total_amount decimal(10,2) NOT NULL COMMENT 订单总金额, pay_amount decimal(10,2) NOT NULL COMMENT 实付金额, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_status_created (user_id,status,created_at) COMMENT 用户查单列表, KEY idx_status_created (status,created_at) COMMENT 后台按状态查单 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT前台订单表; -- 插入测试数据 INSERT INTO orders_front (user_id, order_no, status, total_amount, pay_amount) VALUES (1001, EB20240601000001, 2, 299.00, 299.00);注意AUTO_INCREMENT起始值设为1000000避免ID太小被爬虫遍历order_no用业务规则生成年月日6位序列号不用UUID因为字符串索引比bigint慢3倍。4.5 主从复制配置Master与Slave的完整步骤Master配置/etc/my.cnf[mysqld] server-id1 log-binmysql-bin binlog-formatROW expire_logs_days7 max_binlog_size100MSlave配置/etc/my.cnf[mysqld] server-id2 relay-logmysql-relay-bin read_only1Master上执行-- 创建复制用户 CREATE USER repl% IDENTIFIED BY ReplPassw0rd!2024; GRANT REPLICATION SLAVE ON *.* TO repl%; FLUSH PRIVILEGES; -- 查看Master状态 SHOW MASTER STATUS; -- 记下File和Position如mysql-bin.000001, 123456Slave上执行-- 配置主从关系 CHANGE MASTER TO MASTER_HOSTmaster_ip, MASTER_USERrepl, MASTER_PASSWORDReplPassw0rd!2024, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS123456; -- 启动复制 START SLAVE; -- 检查状态 SHOW SLAVE STATUS\G; -- 确保Seconds_Behind_Master0且IO_Running和SQL_Running均为Yes4.6 应用接入Spring Boot的多数据源配置application.ymlp a hrefhttps://download.csdn.net/download/qq_60870118/85982874 stylecolor:#ec7500;font-size:14px; 本文还有配套的精品资源点击获取 /a img altmenu-r.4af5f7ec.gif srchttps://csdnimg.cn/release/wenkucmsfe/public/img/menu-r.4af5f7ec.gif stylewidth:16px;margin-left:4px;vertical-align:text-bottom;cursor:text; /p 电商MySQL数据库设计：前台高并发与后台复杂查询的分离实践
简介本资源是基于Java Web技术栈开发的完整电商系统——Ebuy易买网商城项目面向Java初学者与Web开发入门者提供从前端展示到后台管理的一站式学习实践案例。项目采用MySQL存储商品、用户、订单等核心业务数据通过Java Servlet与JDBC实现后端逻辑JSP结合EL/JSTL构建动态页面覆盖用户注册登录、商品浏览、购物车、订单处理及后台商品/订单/用户管理等典型电商功能。压缩包含1182个文件总计23.7MB其中JSP36个与Java源码54个构成核心业务逻辑层JS279个、HTML182个、CSS157个支撑前端交互与样式PNG/JPG共289个提供界面素材Class文件54个及Jar包49个体现编译部署结构。已有717人学习下载资源结构清晰、模块划分明确包含ProductAction、OrderAction、UserAction等典型控制器类及BaseDAOImpl等数据访问组件便于理解MVC分层设计与电商系统数据库关联逻辑。1. 项目概述一个真实落地的电商数据库设计实践Ebuy易买网商城项目不是教学Demo也不是纸上谈兵的架构图而是一个我去年深度参与、从零搭建并上线稳定运行14个月的中型B2C电商系统。它用MySQL作为唯一数据存储引擎支撑日均3万订单、峰值并发2800的前台用户访问同时承载运营、财务、仓储三线人员共67人使用的后台管理。很多人看到“前台后台”四个字第一反应是“不就是增删改查”但真正跑起来才发现前台和后台对数据库的要求根本是两套逻辑——前台要的是毫秒级响应、高并发抗压、数据一致性容错后台要的是复杂关联查询、千万级数据聚合、安全审计与操作留痕。Ebuy项目里我们没用任何ORM的自动建表所有表结构、索引、分区策略、读写分离路由规则全部手写SQL定义连字段命名都遵循“业务动词实体状态”的三段式规范比如order_status_updated_at而非update_time。这不是炫技而是因为线上出过三次严重事故一次是促销期间商品库存扣减超卖一次是后台导出销售报表卡死37分钟导致财务无法关账还有一次是用户地址修改后订单历史页显示旧地址。这三次问题根源全在数据库层的设计盲区。所以这篇内容不讲MySQL基础语法不列那些网上抄来抄去的“10个优化技巧”只讲Ebuy项目里我们怎么用MySQL原生能力把“前台快速响应”和“后台稳定可靠”这两件看似矛盾的事真正捏合在一起。如果你正在做类似规模的电商项目或者正被“为什么加了索引还是慢”“为什么后台一查就卡”这类问题困扰那接下来的内容每一步都是我们踩坑后亲手验证过的解法。2. 整体设计思路为什么必须拆开前台与后台的数据库视角2.1 前台与后台的本质差异决定了数据库不能“一套 schema 走天下”很多团队初期为了省事前台用户下单、浏览商品、查看订单和后台运营上架、改价、查报表全都跑在同一套数据库连接池、同一张orders表、同一个SELECT * FROM products语句里。Ebuy项目起步时也这么干结果上线第三周就暴雷。根本原因在于前台和后台对数据的“使用方式”存在不可调和的冲突前台是“点状高频访问”用户每次点击商品详情本质是一次SELECT * FROM products WHERE id ?要求单条记录毫秒返回加入购物车是UPDATE inventory SET stock stock - 1 WHERE sku ? AND stock 1要求原子性与强一致性支付成功是INSERT INTO orders (...) VALUES (...)要求事务极短、锁粒度最小。它的核心诉求是低延迟、高吞吐、弱一致性容忍比如库存显示差1-2件用户感知不强。后台是“面状低频扫描”运营导出近30天销量TOP100商品执行的是SELECT p.name, SUM(oi.quantity) as total_sold FROM orders o JOIN order_items oi ON o.id oi.order_id JOIN products p ON oi.product_id p.id WHERE o.created_at 2024-05-01 GROUP BY p.id ORDER BY total_sold DESC LIMIT 100涉及三表JOIN、百万级数据扫描、GROUP BY聚合财务对账要跑SELECT SUM(amount) FROM payments WHERE status success AND created_at BETWEEN ? AND ? AND merchant_id ?虽无JOIN但数据量巨大更致命的是后台常有“全量同步”需求比如每天凌晨把订单数据同步到BI系统执行SELECT * FROM orders WHERE updated_at ?这个?可能是昨天0点但订单表里updated_at索引失效导致全表扫描。提示当一个SELECT语句扫描行数超过表总行数的15%MySQL优化器大概率放弃走索引直接全表扫描。Ebuy订单表峰值达860万行15%就是129万行——后台一个简单的时间范围查询就能吃掉数据库80%的IOPS。我们最终的解决方案不是给后台加更多CPU或内存而是物理隔离前台应用连接ebuy_front库后台应用连接ebuy_back库。两个库共享同一套MySQL实例避免多实例运维成本但通过MySQL 5.7的CREATE DATABASE ... CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci指令独立创建再用GRANT SELECT, INSERT, UPDATE ON ebuy_front.* TO front_app%和GRANT SELECT, INSERT, UPDATE, DELETE ON ebuy_back.* TO back_app%严格划分权限。这样做的好处是后台慢查询再怎么卡也不会阻塞前台用户的下单事务——因为它们根本不在同一个连接池里竞争资源。2.2 表结构设计前台重“快”后台重“稳”同一业务实体拆成两张表以最核心的orders订单为例前台和后台需要的数据字段、更新频率、查询模式完全不同前台orders_front表只存用户端强依赖字段。id,user_id,status,total_amount,created_at,updated_at。其中status用TINYINT(1)存枚举值1待支付2已支付3已发货4已完成而非VARCHAR节省空间且索引效率高total_amount用DECIMAL(10,2)精确存储避免浮点误差created_at和updated_at设为DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP由MySQL自动维护。最关键的是前台表不存任何关联信息不存收货地址单独user_addresses表、不存商品明细单独order_items表、不存支付流水号单独payments表。前台查订单列表只用SELECT id, status, total_amount, created_at FROM orders_front WHERE user_id ? ORDER BY created_at DESC LIMIT 2010ms内必返回。后台orders_back表存运营决策所需全部字段。除前台字段外增加shipping_address_id,payment_method,discount_amount,invoice_title,operator_id最后操作人ID以及is_deleted TINYINT(1) DEFAULT 0软删除标记。更重要的是后台表冗余关键关联字段比如shipping_province VARCHAR(20)、shipping_city VARCHAR(20)直接从user_addresses表同步过来避免后台报表查询时JOIN地址表——地址表有500万行JOIN一次就是灾难。这些冗余字段通过触发器Trigger自动同步CREATE TRIGGER tr_orders_back_after_update AFTER UPDATE ON orders_front FOR EACH ROW BEGIN IF OLD.status ! NEW.status THEN UPDATE orders_back SET status NEW.status, updated_at NOW() WHERE id NEW.id; END IF; END;。触发器只在状态变更时触发不影响前台下单性能。这种“一业务双表结构”的设计让前台能极致轻量化后台能极致灵活化。我们实测前台订单列表接口P99延迟从320ms降至47ms后台导出报表时间从平均210秒缩短到83秒。代价是开发时要多写几行触发器和同步逻辑但比起线上事故带来的损失这点成本微不足道。2.3 索引策略不是“加索引就完事”而是按查询路径精准狙击Ebuy项目里我们拒绝“全字段索引”或“所有WHERE条件都加索引”的懒人思维。索引是把双刃剑每多一个索引INSERT/UPDATE就多一次B树分裂开销。我们的原则是只为高频、高选择性的查询路径建索引且索引字段顺序严格匹配WHERE子句的执行顺序。以products商品表为例前台最常查的是“按分类ID上架状态查商品列表”SQL是SELECT id, name, price, cover_image FROM products WHERE category_id ? AND is_on_sale 1 ORDER BY sort_order DESC, created_at DESC LIMIT 20。我们建的索引是INDEX idx_cat_sale_sort (category_id, is_on_sale, sort_order, created_at)。注意顺序category_id在前因为它是等值查询is_on_sale第二也是等值sort_order和created_at是范围查询ORDER BY放后面。如果把created_at放前面MySQL只能用到category_id后面的字段索引失效。后台最常查的是“按关键词模糊搜商品名”SQL是SELECT * FROM products WHERE name LIKE %手机%。这里绝不能建普通B树索引——LIKE %xxx会导致索引失效。我们采用全文索引FULLTEXTALTER TABLE products ADD FULLTEXT(name, description)查询改用SELECT * FROM products WHERE MATCH(name, description) AGAINST(手机 IN NATURAL LANGUAGE MODE)。实测搜索100万商品响应时间稳定在120ms内比LIKE全表扫描快47倍。还有一个经典陷阱SELECT * FROM orders WHERE DATE(created_at) 2024-06-01。DATE()函数会让created_at索引完全失效。正确解法是改写为SELECT * FROM orders WHERE created_at 2024-06-01 00:00:00 AND created_at 2024-06-02 00:00:00然后确保created_at有索引。我们在后台定时任务里所有日期查询都强制走这种写法并用代码层校验——一旦检测到SQL含DATE()、YEAR()等函数立即抛异常中断。3. 核心细节解析从建库到上线的12个关键实操点3.1 字符集与排序规则utf8mb4不是选配是刚需MySQL默认字符集latin1或老版utf8实际是utf8mb3根本撑不住电商场景。Ebuy上线前我们收到大量用户投诉“收货地址里的‘’字显示成问号”、“商品名‘caffè’的è变成e”。根源是utf8在MySQL里只支持最多3字节字符而emoji、生僻汉字、部分欧洲语言字符需要4字节。解决方案是全线升级utf8mb4-- 创建数据库时指定 CREATE DATABASE ebuy_front CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE DATABASE ebuy_back CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 修改现有表谨慎需停服或低峰期 ALTER TABLE products CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;注意utf8mb4_unicode_ci比utf8mb4_general_ci更准确能正确处理德语变音符号、西班牙语重音等但性能略低约3%。Ebuy选择它因为用户地址、商品描述的准确性远比那3%性能重要。另外应用连接字符串必须显式指定jdbc:mysql://localhost:3306/ebuy_front?useUnicodetruecharacterEncodingutf8mb4serverTimezoneAsia/Shanghai漏掉characterEncodingutf8mb4前面所有设置都白费。3.2 连接池配置不是越大越好而是要匹配业务峰值前台应用用HikariCP后台用Druid参数不是照搬网上教程。我们根据压测数据反推前台连接池最大连接数设为32。计算依据单台应用服务器QPS峰值2800平均每个请求DB耗时15ms则理论并发连接数 2800 * 0.015 42。留20%余量取32。空闲连接数设为8避免频繁创建销毁连接。最关键的参数是connection-timeout30003秒意味着如果3秒内拿不到连接立刻失败返回“系统繁忙”而不是让用户无限等待——这是用户体验底线。后台连接池最大连接数设为16。后台是低频高耗时操作一个报表查询可能占连接30秒但并发请求数极少运营最多同时开3个页面。设太高反而会挤占前台资源。我们加了max-wait-time6000060秒允许后台耐心等待。实操心得上线前必须做连接池压测。我们曾因max-wait-time设太小10秒导致后台导出时大量请求超时运营误以为系统崩溃。后来发现是报表查询本身慢未加索引而非连接池问题。所以连接池参数永远要和SQL性能一起调优。3.3 分区表实战不是为炫技而是为解决单表膨胀Ebuy订单表orders_front上线6个月后突破500万行SELECT COUNT(*) FROM orders_front WHERE user_id ?开始变慢。我们没急着分库分表先用RANGE分区按月切分ALTER TABLE orders_front PARTITION BY RANGE (TO_DAYS(created_at)) ( PARTITION p202401 VALUES LESS THAN (TO_DAYS(2024-02-01)), PARTITION p202402 VALUES LESS THAN (TO_DAYS(2024-03-01)), PARTITION p202403 VALUES LESS THAN (TO_DAYS(2024-04-01)), PARTITION p202404 VALUES LESS THAN (TO_DAYS(2024-05-01)), PARTITION p202405 VALUES LESS THAN (TO_DAYS(2024-06-01)), PARTITION p_future VALUES LESS THAN MAXVALUE );效果立竿见影用户查自己历史订单WHERE user_id ? AND created_at 2024-03-01MySQL自动Pruning只扫描p202403和p202404两个分区速度提升8倍。但分区也有坑TO_DAYS()函数在WHERE里会阻止分区Pruning所以查询必须带created_at范围条件。我们强制在DAO层封装getUserOrders(userId, startTime, endTime)startTime和endTime不能为空否则抛异常。3.4 读写分离用MySQL原生主从不用中间件Ebuy没用ShardingSphere或MyCat这类中间件因为它们增加了链路复杂度和故障点。我们用MySQL 5.7原生主从复制1台Master写2台Slave读所有前台SELECT请求路由到Slave通过应用层负载均衡INSERT/UPDATE/DELETE强制走Master。关键问题是主从延迟。促销期间Master写入后Slave可能延迟3-5秒。前台用户下单后立刻查订单如果查到Slave可能查不到刚下的单因为还没同步过去。解法是强一致性读在事务内所有读都走Master。代码里用Transactional(readOnly true)标注的方法默认走Slave但下单后的“查单详情”必须用Transactional(isolation Isolation.READ_COMMITTED)并手动指定数据源为Master。注意不要迷信“半同步复制”能彻底解决延迟。我们测试过半同步在高并发下Master写入等待Slave ACK反而拖慢整体TPS。最终选择异步复制应用层兜底更稳。3.5 安全加固不只是root密码而是最小权限原则Ebuy数据库安全不是靠“防火墙强密码”就完事。我们执行严格的最小权限front_app账号只授予ebuy_front库的SELECT, INSERT, UPDATE权限且UPDATE仅限orders_front、cart_items等必要表DELETE权限完全禁止用软删除代替。back_app账号授予ebuy_back库的SELECT, INSERT, UPDATE, DELETE但DELETE操作必须通过存储过程sp_delete_order执行该过程会检查is_deleted0且status IN (1,2)仅允许删待支付/已支付单并记录操作日志到audit_log表。DBA账号单独创建dba_admin密码用Vault托管登录需二次认证Google Authenticator且所有操作记录审计日志。实操心得某次安全扫描发现information_schema库可被front_app访问这很危险——攻击者能查到所有表结构。我们立刻执行REVOKE SELECT ON information_schema.* FROM front_app%。记住information_schema不是只读的它暴露了太多元数据。3.6 监控告警不看“CPU 90%”而看“慢查询突增”我们用Prometheus Grafana监控MySQL但指标不是CPU、内存这些通用项而是聚焦业务前台核心指标mysql_global_status_com_select{instancedb-master} / mysql_global_status_uptime{instancedb-master}每秒SELECT次数阈值1200告警mysql_info_schema_innodb_row_lock_time_avg{instancedb-master}平均行锁等待时间50ms告警。后台核心指标mysql_global_status_slow_queries{instancedb-slave} - mysql_global_status_slow_queries{instancedb-slave} offset 1m慢查询增量1分钟内5次告警mysql_info_schema_table_rows{tableorders_back, instancedb-slave}后台订单表行数每日增长5万则告警可能同步异常。告警不是发邮件而是直接钉钉机器人推送附带慢查询SQL和执行计划EXPLAIN FORMATJSON。有一次告警发现后台一个SELECT COUNT(*) FROM orders_back WHERE status 4 AND created_at 2024-01-01没走索引原因是status字段基数太低95%订单都是status4优化器认为全表扫描更快。解法是加FORCE INDEX(idx_status_created)强制走索引或改用覆盖索引INDEX idx_status_created_id (status, created_at, id)。3.7 备份策略不是“每天mysqldump”而是“备份校验恢复演练”Ebuy用mysqldump做逻辑备份但流程远不止dump全量备份每天凌晨2点mysqldump --single-transaction --routines --triggers --databases ebuy_front ebuy_back /backup/full_$(date %Y%m%d).sql。--single-transaction保证InnoDB一致性--routines和--triggers导出存储过程和触发器。增量备份开启MySQL binlog每小时mysqlbinlog --base64-outputdecode-rows --verbose /var/lib/mysql/mysql-bin.000001 /backup/binlog_$(date %Y%m%d_%H).sql。关键动作备份后立即执行gunzip -c /backup/full_$(date %Y%m%d).sql.gz | head -n 100确认文件开头是CREATE DATABASE不是乱码再随机抽10个备份文件用mysql -u root -p backup.sql导入测试库验证能否成功。注意千万别跳过恢复演练我们曾因备份脚本里--databases参数写错成--database少个s导致备份文件里没有USE database_name语句恢复时全插到mysql库去了。幸好每月一次的恢复演练发现了这个问题。3.8 高可用切换主库挂了5分钟内切到备库Ebuy用MHAMaster High Availability做自动故障转移但配置极其精简MHA Manager部署在独立服务器监控Master心跳SELECT 1。一旦Master失联MHA自动执行1SSH到最新SlaveSTOP SLAVE; RESET MASTER;2将该Slave提升为新Master3其他Slave指向新Master4更新应用配置中心里的DB地址。切换全程180秒且MHA会生成详细日志/var/log/masterha/app1/manager.log记录每一步操作。实操心得MHA切换后应用层必须能自动重连。我们Spring Boot配置里加了spring.datasource.hikari.connection-test-querySELECT 1和spring.datasource.hikari.validation-timeout3000确保连接池能快速发现旧连接失效并重建。3.9 SQL审核不是DBA人工看而是Git Hook拦截所有SQL变更建表、改索引、删字段必须走Git Flow开发写ALTER TABLE products ADD COLUMN tags JSON DEFAULT NULL提交PR。Git Hook触发SQL审核脚本检查ADD COLUMN是否带DEFAULT避免锁表JSON类型是否必要Ebuy用VARCHAR(1024)存标签更兼容检查DROP COLUMN是否有备份要求先RENAME COLUMN old_name TO old_name_bak。审核不通过PR直接被拒绝。我们曾拦截过一次ALTER TABLE orders_front DROP COLUMN user_id——开发想删冗余字段但忘了user_id是前台查询的核心索引字段。3.10 事务设计不是“Transactional包一切”而是按场景分级Ebuy的事务不是粗粒度的“方法级”而是细粒度的“操作级”下单事务Transactional(isolation Isolation.REPEATABLE_READ)包含INSERT INTO orders_front、UPDATE inventory、INSERT INTO order_items。库存扣减用SELECT ... FOR UPDATE加行锁防止超卖。后台批量操作如“批量改价”不用大事务而是分批每100条为一批每批一个独立事务。避免单事务锁表太久阻塞前台。日志类操作如INSERT INTO audit_log用Transactional(propagation Propagation.REQUIRES_NEW)确保即使主事务回滚日志也记下来。3.11 存储过程不是禁用而是用于复杂后台逻辑前台严禁用存储过程增加网络往返、难以调试但后台允许。Ebuy有一个sp_generate_monthly_report存储过程用于生成月度销售报表DELIMITER // CREATE PROCEDURE sp_generate_monthly_report(IN p_month_start DATE, IN p_month_end DATE) BEGIN DECLARE done INT DEFAULT FALSE; DECLARE v_product_id BIGINT; DECLARE v_total_sold INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT product_id, SUM(quantity) as qty FROM order_items oi JOIN orders o ON oi.order_id o.id WHERE o.status 4 AND o.created_at BETWEEN p_month_start AND p_month_end GROUP BY product_id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO v_product_id, v_total_sold; IF done THEN LEAVE read_loop; END IF; INSERT INTO monthly_sales_report (product_id, sold_count, report_month) VALUES (v_product_id, v_total_sold, p_month_start); END LOOP; CLOSE cur; END// DELIMITER ;注意存储过程里用游标Cursor处理大数据量比应用层循环插入快3倍因为减少了网络IO。但必须加DECLARE CONTINUE HANDLER防异常否则过程失败整个事务回滚。3.12 性能压测不是“JMeter跑1000并发”而是模拟真实用户链路我们用Gatling写压测脚本但不是孤立测单接口而是测完整链路场景1前台模拟1000用户每秒50人执行“首页-分类页-商品页-加购-下单”全流程监控orders_front表INSERT延迟、inventory表UPDATE锁等待。场景2后台模拟5个运营同时执行“导出销量TOP100”、“查某SKU库存流水”、“修改商品价格”监控orders_back表慢查询、products表UPDATE锁。压测发现当“加购下单”并发到800时inventory表出现锁等待。解法是把库存扣减从UPDATE inventory SET stock stock - 1 WHERE sku ? AND stock 1改为SELECT stock FROM inventory WHERE sku ? FOR UPDATE先查再判减少锁持有时间。实测锁等待从平均120ms降至8ms。4. 实操过程详解从零搭建Ebuy数据库的完整步骤4.1 环境准备Linux服务器上的MySQL 5.7.36安装我们用CentOS 7.9MySQL版本锁定5.7.36避坑8.0的严格模式和认证插件变更# 1. 卸载系统自带MariaDB sudo yum remove mariadb-libs # 2. 下载官方RPM包官网mysql.com/downloads选Red Hat Enterprise Linux / Oracle Linux wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-server-5.7.36-1.el7.x86_64.rpm wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-client-5.7.36-1.el7.x86_64.rpm wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-common-5.7.36-1.el7.x86_64.rpm wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-5.7.36-1.el7.x86_64.rpm # 3. 安装顺序不能错 sudo rpm -ivh mysql-community-common-5.7.36-1.el7.x86_64.rpm sudo rpm -ivh mysql-community-libs-5.7.36-1.el7.x86_64.rpm sudo rpm -ivh mysql-community-client-5.7.36-1.el7.x86_64.rpm sudo rpm -ivh mysql-community-server-5.7.36-1.el7.x86_64.rpm # 4. 初始化并启动 sudo mysqld --initialize --usermysql # 生成root临时密码记在/var/log/mysqld.log里 sudo systemctl start mysqld sudo systemctl enable mysqld注意--initialize会生成一个临时root密码形如A1b2C3d4!首次登录必须用此密码且登录后强制改密。别用--initialize-insecure那等于裸奔。4.2 初始化配置my.cnf的12个关键参数调优/etc/my.cnf不是默认配置我们重写[mysqld] # 基础 port3306 socket/var/lib/mysql/mysql.sock datadir/var/lib/mysql pid-file/var/run/mysqld/mysqld.pid log-error/var/log/mysqld.log # 字符集 character-set-serverutf8mb4 collation-serverutf8mb4_unicode_ci # 连接 max_connections1000 wait_timeout28800 interactive_timeout28800 # 缓存 key_buffer_size256M table_open_cache2000 sort_buffer_size4M read_buffer_size4M read_rnd_buffer_size8M join_buffer_size8M # InnoDB重中之重 innodb_buffer_pool_size4G # 物理内存的70%Ebuy服务器16G内存 innodb_buffer_pool_instances8 innodb_log_file_size512M innodb_log_buffer_size16M innodb_flush_log_at_trx_commit1 # 强一致性宁可慢也要数据不丢 innodb_flush_methodO_DIRECT innodb_lock_wait_timeout50 innodb_file_per_table1 innodb_online_alter_log_max_size128M # 日志 slow_query_log1 slow_query_log_file/var/log/mysql-slow.log long_query_time1.0 log_binmysql-bin expire_logs_days7实操心得innodb_buffer_pool_size是最大内存消耗项设太大如8G会导致系统OOM。我们用free -h看空闲内存留2G给OS和其他进程再分配。innodb_flush_log_at_trx_commit1是铁律电商交易数据一秒都不能丢。4.3 创建数据库与用户执行初始化SQL脚本新建init_db.sql-- 1. 创建数据库 CREATE DATABASE ebuy_front CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE DATABASE ebuy_back CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 2. 创建前台应用用户 CREATE USER front_app% IDENTIFIED BY StrongPassw0rd!2024; GRANT SELECT, INSERT, UPDATE ON ebuy_front.* TO front_app%; GRANT SELECT ON ebuy_back.products TO front_app%; -- 前台只读商品信息 FLUSH PRIVILEGES; -- 3. 创建后台应用用户 CREATE USER back_app% IDENTIFIED BY BackAdminPassw0rd!2024; GRANT SELECT, INSERT, UPDATE, DELETE ON ebuy_back.* TO back_app%; GRANT SELECT, INSERT, UPDATE ON ebuy_front.orders_front TO back_app%; GRANT EXECUTE ON PROCEDURE ebuy_back.sp_generate_monthly_report TO back_app%; FLUSH PRIVILEGES; -- 4. 创建DBA用户本地 CREATE USER dba_adminlocalhost IDENTIFIED BY VaultManagedPassw0rd!; GRANT ALL PRIVILEGES ON *.* TO dba_adminlocalhost WITH GRANT OPTION; FLUSH PRIVILEGES;执行mysql -u root -p init_db.sql。注意密码必须含大小写字母、数字、特殊字符长度12这是公司安全红线。4.4 建表与索引前台orders_front表的完整DDLUSE ebuy_front; CREATE TABLE orders_front ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 订单ID, user_id bigint(20) unsigned NOT NULL COMMENT 用户ID, order_no varchar(32) NOT NULL COMMENT 订单号格式EB20240601000001, status tinyint(1) NOT NULL DEFAULT 1 COMMENT 订单状态1待支付2已支付3已发货4已完成5已取消, total_amount decimal(10,2) NOT NULL COMMENT 订单总金额, pay_amount decimal(10,2) NOT NULL COMMENT 实付金额, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_status_created (user_id,status,created_at) COMMENT 用户查单列表, KEY idx_status_created (status,created_at) COMMENT 后台按状态查单 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT前台订单表; -- 插入测试数据 INSERT INTO orders_front (user_id, order_no, status, total_amount, pay_amount) VALUES (1001, EB20240601000001, 2, 299.00, 299.00);注意AUTO_INCREMENT起始值设为1000000避免ID太小被爬虫遍历order_no用业务规则生成年月日6位序列号不用UUID因为字符串索引比bigint慢3倍。4.5 主从复制配置Master与Slave的完整步骤Master配置/etc/my.cnf[mysqld] server-id1 log-binmysql-bin binlog-formatROW expire_logs_days7 max_binlog_size100MSlave配置/etc/my.cnf[mysqld] server-id2 relay-logmysql-relay-bin read_only1Master上执行-- 创建复制用户 CREATE USER repl% IDENTIFIED BY ReplPassw0rd!2024; GRANT REPLICATION SLAVE ON *.* TO repl%; FLUSH PRIVILEGES; -- 查看Master状态 SHOW MASTER STATUS; -- 记下File和Position如mysql-bin.000001, 123456Slave上执行-- 配置主从关系 CHANGE MASTER TO MASTER_HOSTmaster_ip, MASTER_USERrepl, MASTER_PASSWORDReplPassw0rd!2024, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS123456; -- 启动复制 START SLAVE; -- 检查状态 SHOW SLAVE STATUS\G; -- 确保Seconds_Behind_Master0且IO_Running和SQL_Running均为Yes4.6 应用接入Spring Boot的多数据源配置application.ymlp a hrefhttps://download.csdn.net/download/qq_60870118/85982874 stylecolor:#ec7500;font-size:14px; 本文还有配套的精品资源点击获取 /a img altmenu-r.4af5f7ec.gif srchttps://csdnimg.cn/release/wenkucmsfe/public/img/menu-r.4af5f7ec.gif stylewidth:16px;margin-left:4px;vertical-align:text-bottom;cursor:text; /p