JCSprout项目中的SQL优化最佳实践指南

JCSprout项目中的SQL优化最佳实践指南

JCSprout 👨‍🎓 Java Core Sprout : basic, concurrent, algorithm 项目地址: https://gitcode.com/gh_mirrors/jc/JCSprout

前言

SQL优化是数据库性能调优的核心环节，合理的SQL编写能显著提升查询效率。本文将基于JCSprout项目中总结的SQL优化经验，结合数据库原理，为开发者提供一套实用的SQL优化指南。

一、索引使用的基本原则

1.1 避免负向查询

负向查询（NOT IN、NOT LIKE等）往往会导致全表扫描：

-- 不推荐：无法使用索引
SELECT name FROM user WHERE id NOT IN (1,3,4);

-- 推荐：改为正向查询
SELECT name FROM user WHERE id IN (2,5,6);

原理：数据库索引通常采用B+树结构，这种结构适合范围查询和等值查询，但对否定条件的处理效率很低。

1.2 模糊查询的正确姿势

模糊查询要特别注意通配符的位置：

-- 不推荐：前导通配符导致索引失效
SELECT name FROM user WHERE name LIKE '%zhangsan';

-- 推荐：后导通配符可以使用索引
SELECT name FROM user WHERE name LIKE 'zhangsan%';

建议：对于频繁的模糊查询场景，可以考虑使用专门的全文检索引擎如Elasticsearch或Solr。

二、索引设计策略

2.1 选择性低的字段不适合建索引

索引的选择性是指索引列中不同值的数量与表中记录总数的比值。例如：

• 性别字段（男/女）选择性低，通常不值得建索引
• 身份证号字段选择性高，非常适合建索引

2.2 复合索引的最左前缀原则

复合索引遵循最左前缀匹配原则：

-- 假设有复合索引(username, pwd)
-- 以下查询都能使用索引
SELECT username FROM user WHERE username='zhangsan' AND pwd='123456';
SELECT username FROM user WHERE username='zhangsan';

-- 以下查询不能使用索引
SELECT username FROM user WHERE pwd='123456';

技巧：设计复合索引时，应将高选择性列放在左边，常用查询条件优先考虑。

三、SQL编写规范

3.1 避免字段上的计算

-- 不推荐：在字段上使用函数导致索引失效
SELECT name FROM user WHERE FROM_UNIXTIME(create_time) < CURDATE();

-- 推荐：将计算移到常量侧
SELECT name FROM user WHERE create_time < UNIX_TIMESTAMP(CURDATE());

3.2 明确指定返回记录数

当确定只返回一条记录时，使用LIMIT 1可以提前终止查询：

-- 推荐
SELECT name FROM user WHERE username='zhangsan' LIMIT 1;

3.3 类型匹配原则

-- 不推荐：隐式类型转换导致索引失效
SELECT name FROM user WHERE telno=18722222222;

-- 推荐：保持类型一致
SELECT name FROM user WHERE telno='18722222222';

四、表设计与数据类型

4.1 避免NULL值

字段默认值设为NOT NULL有诸多好处：

1. 减少存储空间（NULL需要额外空间记录）
2. 避免NULL值比较的特殊处理
3. 防止聚合函数统计时的意外结果

4.2 JOIN操作的类型一致

JOIN操作的关联字段类型必须严格一致，否则：

1. 无法使用索引
2. 可能导致隐式类型转换
3. 可能产生错误的JOIN结果

五、高级优化技巧

5.1 覆盖索引优化

设计索引时应考虑查询能否被索引完全覆盖：

-- 假设有索引(username, age)
-- 好的设计：查询只需访问索引
SELECT username, age FROM user WHERE username='zhangsan';

-- 不好的设计：需要回表查询
SELECT username, age, address FROM user WHERE username='zhangsan';

5.2 分页查询优化

大数据量分页时避免使用OFFSET：

-- 不推荐：OFFSET越大性能越差
SELECT * FROM user ORDER BY id LIMIT 10000, 20;

-- 推荐：使用条件过滤
SELECT * FROM user WHERE id > 10000 ORDER BY id LIMIT 20;

总结

SQL优化是一个系统工程，需要从索引设计、SQL编写、表结构设计等多个维度综合考虑。本文基于JCSprout项目的实践经验，总结了最核心的优化原则。实际应用中，还需要结合EXPLAIN分析执行计划，针对具体场景进行调优。记住：没有放之四海皆准的优化方案，只有最适合当前业务场景的优化策略。

JCSprout 👨‍🎓 Java Core Sprout : basic, concurrent, algorithm 项目地址: https://gitcode.com/gh_mirrors/jc/JCSprout