1、使用索引优化查询使用场景数据库中存在大量数据需要频繁进行搜索查询索引是提高查询效率的关键。代码示例CREATE TABLE student( id INT AUTO_INCREMENT, name VARCHAR(100), card_id INT PRIMARY KEY (id) ); --创建card_id字段的索引 CREATE INDEX index_card ON student(card_id);index_card是B树结构它把card_id的值排序后存储。执行select * from student where card_id1时1快速定位数据库从索引树的根节点开始通过二分查找的方式在O(log2 n)次比较内找到对应值的第一个叶子结点。2顺序扫描沿叶子链表快速读取所有card_id1的指针。3回表根据指针取出完整的行数据注索引会带来写入时的额外维护成本要在查询频繁的列上谨慎创建。2、增加查询条件避免全表扫描使用条件表中数据量大全表扫描耗时长。--全表扫描 SELECT * FROM student WHERE name LIKE %杰克% --优化 SELECT * FROM student WHERE card_id 1 AND name LIKE %杰克%1此处前后顺序不影响查询效果数据库查询优化器会自动设置先查询找出card_id1的列再执行name LIKE %杰克%2复合索引name,card_id1索引有效name使用 或 LIKE *% 2索引失效未写name或 LIKE %* 。3、使用JOIN代替子查询使用场景多表联合查询注MySQL5.6及以上版本会自动将IN转换为JOIN但在某些特定场景如子查询中包含复杂函数、GROUP BY或HAVING或旧版本中优化器可能会强制先执行子查询物化成临时表。代码示例CREATE TABLE card( id INT AUTO_INCREMENT, class VARCHAR(100), PRIMARY KEY (id) ); --子查询 SELECT * FROM student WHERE card_id IN (SELECT id FROM card WHERE class 666; --JOIN查询 SELECT student.* FROM student JOIN card ON student.card_idcard.id WHERE card.class666;4、使用临时表使用场景复杂多步查询代码示例CREATE TABLE student( id INT AUTO_INCREMENT, name VARCHAR(100), class_id INT, PRIMARY KEY (id) ); --创建临时表 CREATE TEMPORARY TABLE temp_class_count SELECT class_id, COUNT(*) AS stu_count FROM student GROUP BY class_id; --临时表使用后数据量仍然很大 CREATE INDEX IND_CLASS_ID ON temp_class_count(class_id); --查询 SELECT class_id, temp_class_count.stu_count FROM student JOIN temp_student ON student.class_idtemp_class_count.class_id;JOIN和临时表使用区分如果子查询里没有GROUP BY、DISTINCT、SUM等聚合函数用JOIN 替换 IN走索引改路线。如果查询里带了GROUP BY且结果还要和其他表 JOIN用手动创建临时表先压缩再上路。如果子查询里既有聚合函数外部又有 IN先建临时表存聚合结果再JOIN外部表。5、避免使用函数和操作符使用场景在WHERE子句中避免对列使用函数和操作符代码示例--使用函数 SELECT * FROM student WHERE YEAR(birth) 2006; --优化 SELECT * FROM student WHERE birth BETWEEN 20060101 AND 20061231;使用函数会导致索引失效因为它必须对每行数据使用函数。6、使用正规划和反正规化使用场景数据库设计中的正规化可以减少数据冗余而反正规化可以提高查询效率。合理平衡这两者可以获得最佳性能。--正规化设计 CREATE TABLE student( id INT AUTO_INCREMENT, name VARCHAR(100), class_id INT, PRIMARY KEY (id), FOREIGN KEY (class_id) REFERENCES class(class_id) ); CREATE TABLE class( class_id INT AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY(class_id) ); --反正规化设计 CREATE TABLE student_1( id INT AUTO_INCREMENT, name VARCHAR(100), class_id INT, PRIMARY KEY (id) );1正规化写入频繁、强一致性系统面临高频数据变更且不允许脏数据出现例如金融账户系统金额变动频繁、ERP企业资源计划员工档案部门频繁变动。操作参考1.分表存储例如员工存员工表部门存部门表通过department_id关联。2.强依赖外键逻辑1非高并发使用外键转账一类的强一致性必须使用外键2高并发电商秒杀高并发吞吐弃用数据库外键使用应用层代码事务。在代码中进行“查→插数据→提交”的原子性操作。Transactional // 利用Spring的声明式事务自动开启和回滚 public void addEmployee(String name, Long deptId) { // 先查部门是否存在 Department dept departmentRepository.findById(deptId) .orElseThrow(() - new BusinessException(部门不存在)); Employee emp new Employee(); emp.setName(name); emp.setDepartmentId(deptId); employeeRepository.save(emp); }无外键约束时逻辑删除没有外键约束最大的噩梦是“管理员把部门删了但 1000 个员工的 department_id 还指着它”。解决办法是永远不执行DELETE只执行UPDATE。表设计在departments表中加一个deleted_at或is_deleted字段。查询拦截在代码的全局查询范围中强制加上WHERE deleted_at IS NULL软删除。删除逻辑删除部门时代码只需要更新deleted_at now()。关联校验删除前查询employees表是否有department_id 当前ID且未被删除的员工。如果有直接抛出异常“该部门下还有员工禁止删除”。-- 伪代码逻辑 DELETE_CHECK: SELECT COUNT(*) FROM employees WHERE department_id ?; IF COUNT 0 THEN RETURN 部门非空无法删除; ELSE UPDATE departments SET deleted_at NOW() WHERE id ?;并发防穿透分布式锁或乐观锁应用程序无法完全替代数据库外键的最大软肋也是高并发下最容易出现的 Bug。场景管理员 A 正在删除部门 1尚未提交事务管理员 B 同时给部门 1 添加员工。在代码中A 查了“没人”B 也查了“没人”结果 A 删掉了部门B 插入了员工导致员工成了“孤儿”。解决方案应用层兜底方案 A悲观锁在查询部门是否存在时加上SELECT ... FOR UPDATE锁住这行记录让 B 等待 A 操作完成。方案 B分布式锁用 Redis 锁住dept_lock_5这个 Key强制串行化操作。方案 C状态机删除部门时先把部门状态改为DELETING删除中阻止新增员工。3.更新策略只需要执行UPDATE departments SET name技术研发部 WHERE id1;2反正规划系统面临千万级数据量的多表 JOIN且对响应时间要求极高毫秒级时必须牺牲存储空间换取查询时间。典型场景后台管理系统的“员工列表”页面需要同时显示员工名部门名、数据大屏每秒钟刷新统计、历史数据归档报表。3折中方案实际企业级开发如 Spring Boot MyBatis/JPA中纯粹的“正规化”或“反正规化”都很少见用得最多的是“部分反正规化冗余关键字段”。具体操作在employees表中既保留department_id用于精准关联和更新也冗余一个department_name用于列表展示。CREATE TABLE employees ( id INT AUTO_INCREMENT, name VARCHAR(100), department_id INT, -- ① 留着用于关联查询保证逻辑完整性 department_name VARCHAR(100), -- ② 冗余字段保证列表页免JOIN PRIMARY KEY (id) );既然冗余了字段就必须保证它们同步。Transactional public void updateDepartmentName(Long deptId, String newName) { // 1. 更新部门表正规化操作 departmentRepository.updateName(deptId, newName); // 2. 同步更新所有员工表中的冗余部门名称反正规化的代价 employeeRepository.updateDepartmentNameByDeptId(deptId, newName); // 执行 SQL: UPDATE employees SET department_name ? WHERE department_id ? }7、使用查询缓存MYSQL8.0弃用高并发写入场景下会成为性能瓶颈1、当对表进行更新时MYSQL会锁住查询缓存区域清除所有涉及该表的所有缓存条目2、若缓存两块大量该表的碎片数据清除缓存的过程会持有全局锁阻塞所有正在进行的查询和写入导致数据库QPS急剧下降使用场景相同的查询被频繁执行使用查询缓存避免重复的数据库扫描。代码示例-- 启用查询缓存 SET global query_cache_size 1000000; SET global query_cache_type 1; -- 执行查询 SELECT name FROM employees WHERE department_id 1;