Ubuntu Java日志中SQL语句如何优化

Ubuntu Java日志中SQL语句优化指南

1. 定位慢SQL：从日志中识别性能瓶颈

在Ubuntu环境中，首先需要通过慢查询日志定位执行效率低的SQL语句。对于MySQL数据库，可通过以下命令开启慢查询日志：

-- 临时开启慢查询日志（重启后失效）
SET GLOBAL slow_query_log = 'ON';
-- 设置慢查询时间阈值（单位：秒，如1秒）
SET GLOBAL long_query_time = 1;
-- 查看慢查询日志文件路径
SHOW VARIABLES LIKE 'slow_query_log_file';

对于Java应用，也可通过框架（如Spring Boot）的配置自动记录慢SQL，例如在application.properties中添加：

spring.datasource.druid.filter.slowsql.slow-sql-millis=1000
spring.datasource.druid.filter.slowsql.log-slow-sql=true

这会将执行时间超过1秒的SQL记录到日志中，便于后续分析。

2. 分析执行计划：用EXPLAIN定位低效操作

获取慢SQL后，使用EXPLAIN命令分析其执行计划，重点关注以下字段：

type：表示访问类型，效率从高到低依次为const（单条记录，如主键查询）、eq_ref（唯一索引关联）、ref（非唯一索引扫描）、range（范围扫描，如<、between）、index（索引全扫描）、ALL（全表扫描，需优先优化）。
rows：预估需要扫描的行数，行数越多说明效率越低。
Extra：额外信息，若出现Using filesort（需额外排序）、Using temporary（使用临时表，性能极差）、Using index（覆盖索引，无需回表）需重点关注。

例如，若type为ALL且rows很大，说明存在全表扫描，需优化索引。

3. 优化索引设计：避免索引失效与过度索引

3.1 创建合理索引

为查询频繁、过滤性强的字段创建索引（如user_id、order_status）。
对于联合查询，遵循最左前缀原则（如复合索引(shop_id, order_no)，查询条件需包含shop_id才能命中索引）。
避免隐式转换（如mobile字段为VARCHAR类型，查询时使用mobile=12345678901会导致索引失效，应改为mobile='12345678901'）。

3.2 避免索引失效场景

不要在索引列上使用函数（如SUBSTR(phone, 1, 3) = '138'，应改为phone >= '13800000000' AND phone < '13900000000'）。
避免OR连接非索引列（如name = 'iPhone' OR category_id = 10，应将category_id加上索引并改用UNION）。
减少IN查询的范围（如IN列表过长，可改为JOIN或分批次查询）。

4. 优化SQL语句：减少资源消耗

4.1 减少数据返回量

避免使用SELECT *，只查询必要的字段（如SELECT id, name, price FROM product）。
使用LIMIT限制结果集大小（如分页查询LIMIT 0, 10）。

4.2 优化查询逻辑

用EXISTS代替IN（当子查询结果集较大时，EXISTS效率更高，如SELECT num FROM a WHERE EXISTS (SELECT 1 FROM b WHERE num = a.num)）。
避免SELECT *与JOIN组合（只查询需要的字段，减少回表操作）。

对于大分页场景（如LIMIT 10000, 10），使用延迟关联（先查询ID再关联，减少回表次数）：

SELECT t1.* FROM product t1 
JOIN (SELECT id FROM product WHERE category_id = 1 ORDER BY create_time DESC LIMIT 10000, 10) t2 
ON t1.id = t2.id;

4.3 简化复杂查询

将复杂查询拆分为多个简单查询，减少数据库负担。
使用批量操作（如INSERT INTO table VALUES (?, ?), (?, ?)）代替循环单条插入，减少数据库交互次数。

5. 数据库配置与架构优化

5.1 调整数据库参数

增大innodb_buffer_pool_size（InnoDB缓冲池大小，建议设置为物理内存的50%-70%，用于缓存数据和索引）。
调整tmp_table_size和max_heap_table_size（内存临时表大小，避免大查询使用磁盘临时表）。

5.2 优化表结构

选择合适的数据类型（如用INT代替VARCHAR存储数字，用VARCHAR(255)代替CHAR(255)节省空间）。
对大表进行分表（水平分表：按时间或ID拆分；垂直分表：将不常用的字段拆分到单独表）。

5.3 使用缓存减少数据库访问

对频繁查询但不常变化的数据（如商品分类、配置信息），使用缓存（如Redis、Caffeine）存储，减少数据库查询次数。

5.4 考虑读写分离

对于读多写少的场景，将读操作分流到从库，减轻主库压力（可通过框架如MyBatis-Plus的读写分离配置实现）。

6. 监控与持续优化

使用监控工具（如Prometheus+Granafa、Arthas）实时监控SQL执行时间、数据库连接池状态、服务器资源使用情况。
定期分析慢查询日志，找出高频慢SQL并进行针对性优化。
根据业务增长情况，适时调整数据库架构（如分库分表、引入搜索引擎ES）。