select va.*, vm.license_plate , wsm.work_site_name, sd2.dept_name as project_name from vehicle_alarm va inner join vehicle_manage vm on vm.id = va.vehicle_id inner join work_site_manage wsm on wsm.id = vm.work_site_id inner join sys_dept sd1 on va.company_id = sd1.dept_id and sd1.del_flag = '0' and sd1.dept_type = '1' inner join sys_dept sd2 on va.project_id = sd2.dept_id and sd2.del_flag = '0' and sd2.dept_type = '3' where wsm.del_flag = '0' and wsm.del_flag = '0' and vm.del_flag = '0' and vm.license_plate like '%川ADG59%' and wsm.project_id in ( 114 , 118 , 121 , 123 , 125 , 127 , 129 , 131 , 133 , 135 , 137 , 139 , 141 , 143 , 150 , 157 , 3004 , 3016 , 3017 , 3044 ) order by va.alarm_start_time desc 这一行and vm.license_plate like '%川ADG59%'导致查询时间过长 pgsql数据库

<think>我们面对的问题是：在PostgreSQL中优化包含模糊查询`LIKE '%川ADG59%'`的SQL性能，该查询涉及多表连接（`vehicle_alarm`和`vehicle_manage`等）。由于前导通配符`%`导致无法使用索引，需要进行优化。 根据引用[2]和引用[4]，PostgreSQL中模糊查询的优化方法包括使用索引（如b-tree索引、gin倒排索引）以及避免全表扫描。结合引用[4]中提到的《PostgreSQL模糊查询优化》，我们可以采用以下方案： ### 优化方案 #### 1. **使用表达式索引（反转函数索引）** 原理：将字符串反转后存储，并创建索引，将前导模糊查询转换为后导模糊查询。 ```sql -- 在vehicle_manage表上添加反转列和索引 ALTER TABLE vehicle_manage ADD COLUMN license_plate_rev text; UPDATE vehicle_manage SET license_plate_rev = reverse(license_plate); CREATE INDEX idx_vm_plate_rev ON vehicle_manage (license_plate_rev); -- 修改查询条件 SELECT va.*, vm.license_plate, wsm.work_site_name, sd2.dept_name as project_name FROM vehicle_alarm va INNER JOIN vehicle_manage vm ON vm.id = va.vehicle_id INNER JOIN work_site_manage wsm ON wsm.id = vm.work_site_id INNER JOIN sys_dept sd1 ON va.company_id = sd1.dept_id AND sd1.del_flag = '0' AND sd1.dept_type = '1' INNER JOIN sys_dept sd2 ON va.project_id = sd2.dept_id AND sd2.del_flag = '0' AND sd2.dept_type = '3' WHERE wsm.del_flag = '0' AND vm.del_flag = '0' AND vm.license_plate_rev LIKE reverse('川ADG59') || '%' -- 使用反转列索引 ORDER BY va.alarm_start_time DESC; ``` **优点**：利用索引加速查询，避免全表扫描。 #### 2. **使用pg_trgm扩展和GIN索引（推荐）** PostgreSQL的pg_trgm扩展支持任意位置的模糊查询索引。引用[4]提到gin倒排索引可用于模糊查询优化。 ```sql -- 启用pg_trgm扩展 CREATE EXTENSION IF NOT EXISTS pg_trgm; -- 在vehicle_manage.license_plate上创建GIN索引 CREATE INDEX idx_vm_plate_gin ON vehicle_manage USING gin (license_plate gin_trgm_ops); -- 修改查询条件（使用更高效的写法） SELECT va.*, vm.license_plate, wsm.work_site_name, sd2.dept_name as project_name FROM vehicle_alarm va INNER JOIN vehicle_manage vm ON vm.id = va.vehicle_id INNER JOIN work_site_manage wsm ON wsm.id = vm.work_site_id INNER JOIN sys_dept sd1 ON va.company_id = sd1.dept_id AND sd1.del_flag = '0' AND sd1.dept_type = '1' INNER JOIN sys_dept sd2 ON va.project_id = sd2.dept_id AND sd2.del_flag = '0' AND sd2.dept_type = '3' WHERE wsm.del_flag = '0' AND vm.del_flag = '0' AND vm.license_plate LIKE '%川ADG59%' -- 原始条件，但可以利用GIN索引 ORDER BY va.alarm_start_time DESC; ``` **优点**：支持任意位置的模糊匹配，索引效率高。 #### 3. **减少连接的数据量** 先通过子查询或CTE缩小`vehicle_manage`表的范围，再与其他表连接。 ```sql WITH vm_cte AS ( SELECT id, license_plate, work_site_id FROM vehicle_manage WHERE license_plate LIKE '%川ADG59%' -- 如果使用pg_trgm索引，这里会先走索引 AND del_flag = '0' ) SELECT va.*, vm.license_plate, wsm.work_site_name, sd2.dept_name as project_name FROM vehicle_alarm va INNER JOIN vm_cte vm ON vm.id = va.vehicle_id INNER JOIN work_site_manage wsm ON wsm.id = vm.work_site_id AND wsm.del_flag = '0' INNER JOIN sys_dept sd1 ON va.company_id = sd1.dept_id AND sd1.del_flag = '0' AND sd1.dept_type = '1' INNER JOIN sys_dept sd2 ON va.project_id = sd2.dept_id AND sd2.del_flag = '0' AND sd2.dept_type = '3' ORDER BY va.alarm_start_time DESC; ``` **优点**：先过滤车辆表，减少后续连接的数据量。 ### 性能对比 | 方法 | 索引类型 | 是否支持前导`%` | 适用场景 | |--------------------|--------------|----------------|------------------------| | **表达式索引(反转)** | B-tree | ✅ | 简单模糊查询 | | **pg_trgm扩展** | GIN倒排索引 | ✅ | 复杂模糊查询（推荐） | | **子查询过滤** | 依赖原索引 | ✅ | 减少连接数据量 | ### 实施步骤 1. **评估数据特征**：检查`vehicle_manage`表中`license_plate`字段的分布和长度。 2. **选择索引方案**： - 如果车牌长度固定（如7位），优先使用反转索引。 - 如果车牌长度不固定或需要更灵活的匹配，使用pg_trgm的GIN索引。 3. **创建索引**： ```sql -- 方案1：反转索引 ALTER TABLE vehicle_manage ADD COLUMN license_plate_rev text; UPDATE vehicle_manage SET license_plate_rev = reverse(license_plate); CREATE INDEX idx_vm_plate_rev ON vehicle_manage (license_plate_rev); -- 方案2：pg_trgm索引 CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX idx_vm_plate_gin ON vehicle_manage USING gin (license_plate gin_trgm_ops); ``` 4. **重写查询**：根据选择的索引方案调整WHERE条件。 5. **验证执行计划**：使用`EXPLAIN ANALYZE`确认索引被使用。 > ⚠️ **注意**： > - 添加索引会占用存储空间，并影响写操作性能。 > - 使用pg_trgm时，模糊查询的字符串长度不能太短（至少3个字符），否则可能退化为全表扫描。 --- §§相关问题§§ 1. 如何验证PostgreSQL中索引是否被查询使用？ 2. pg_trgm扩展对多字节字符（如中文）的支持如何？ 3. 在哪些场景下反转索引比pg_trgm更高效？ 4. 如何监控PostgreSQL中慢查询的性能变化？ [^2]: PostgreSQL模糊查询优化方法 [^4]: PostgreSQL模糊查询优化及索引选择</think>### PostgreSQL模糊查询性能优化方案 针对您的查询中`LIKE '%川ADG59%'`导致的性能问题，以下是针对PostgreSQL的优化方案（基于引用[2][4]的模糊查询优化原则）： #### 1. **使用pg_trgm扩展和GIN索引（推荐方案）** ```sql -- 启用pg_trgm扩展（需超级用户权限） CREATE EXTENSION IF NOT EXISTS pg_trgm; -- 在vehicle_manage.license_plate上创建GIN索引 CREATE INDEX idx_vm_plate_gin ON vehicle_manage USING gin (license_plate gin_trgm_ops); -- 优化后的查询（使用索引加速） SELECT va.*, vm.license_plate, wsm.work_site_name, sd2.dept_name as project_name FROM vehicle_alarm va INNER JOIN vehicle_manage vm ON vm.id = va.vehicle_id INNER JOIN work_site_manage wsm ON wsm.id = vm.work_site_id INNER JOIN sys_dept sd1 ON va.company_id = sd1.dept_id AND sd1.del_flag = '0' AND sd1.dept_type = '1' INNER JOIN sys_dept sd2 ON va.project_id = sd2.dept_id AND sd2.del_flag = '0' AND sd2.dept_type = '3' WHERE wsm.del_flag = '0' AND vm.del_flag = '0' AND vm.license_plate LIKE '%川ADG59%' -- 现在可使用索引 ORDER BY va.alarm_start_time DESC; ``` **原理**： `pg_trgm`将文本拆分为3字符片段（trigram），GIN索引支持任意位置的模糊匹配[^2]。此方案对中文支持良好（需确保数据库编码为UTF-8）。 #### 2. **反转列+函数索引（替代方案）** ```sql -- 添加反转列 ALTER TABLE vehicle_manage ADD COLUMN license_plate_rev text; -- 填充反转数据 UPDATE vehicle_manage SET license_plate_rev = reverse(license_plate); -- 创建函数索引 CREATE INDEX idx_vm_plate_rev ON vehicle_manage (license_plate_rev); -- 修改查询条件 SELECT ... WHERE reverse(vm.license_plate) LIKE reverse('川ADG59') || '%' ``` **适用场景**：当无法使用pg_trgm时，此方案将`LIKE '%川ADG59%'`转换为`LIKE '95GDA川%'`（后导通配符可走索引）[^4]。 #### 3. **查询重写优化** ```sql WITH filtered_vm AS ( SELECT id, license_plate, work_site_id FROM vehicle_manage WHERE license_plate LIKE '%川ADG59%' -- 先过滤车辆表 AND del_flag = '0' ) SELECT va.*, vm.license_plate, wsm.work_site_name, sd2.dept_name as project_name FROM vehicle_alarm va INNER JOIN filtered_vm vm ON vm.id = va.vehicle_id INNER JOIN work_site_manage wsm ON wsm.id = vm.work_site_id AND wsm.del_flag = '0' INNER JOIN sys_dept sd1 ON va.company_id = sd1.dept_id AND sd1.del_flag = '0' AND sd1.dept_type = '1' INNER JOIN sys_dept sd2 ON va.project_id = sd2.dept_id AND sd2.del_flag = '0' AND sd2.dept_type = '3' ORDER BY va.alarm_start_time DESC; ``` **优势**：通过CTE先缩小数据集，减少后续表连接的数据量。 #### 4. **其他优化措施 - **索引优化**：确保连接字段（如`va.vehicle_id`, `vm.work_site_id`）有B-tree索引 - **统计信息更新**：定期执行`ANALYZE vehicle_manage;` - **查询监控**：使用`pg_stat_statements`监控慢查询[^4] ### 性能对比 | 方案 | 索引类型 | 响应时间 | 存储开销 | 维护复杂度 | |--------------------|--------------|----------|----------|------------| | pg_trgm + GIN | GIN倒排索引 | ★★★★☆ | 较高 | 低 | | 反转列+函数索引 | B-tree | ★★★☆☆ | 中等 | 中 | | 查询重写 | 依赖原索引 | ★★☆☆☆ | 无 | 低 | ### 实施建议 1. **优先选择方案1**（pg_trgm+GIN），尤其当车牌字段较长时 2. 若车牌数据量极大（>1亿行），结合分区表使用 3. 避免在WHERE子句中使用函数包装`license_plate`（如`lower(license_plate)`），这会禁用索引 > 📌 **验证索引使用**：添加`EXPLAIN ANALYZE`前缀查看执行计划，确认是否使用索引： > ```sql > EXPLAIN ANALYZE > SELECT ... WHERE vm.license_plate LIKE '%川ADG59%'; > ``` ---