MySQL统计反斜杠分隔地址空分段 多查询合并单查询优化

需求说明 #

需要从MySQL表的3个以反斜杠（\）为分隔符的地址字段中提取指定分段，校验分段去除首尾空格后是否为空，统计所有空分段的数量。
3个地址字段的固定分段结构：

• address1：streetname\postalcode\city\country，共4段
• address2：Name3\streetname\postalcode\city\country，共5段
• address3：Name3\streetname\postalcode\city\country，共5段
  字段实际存储样例：

"1","12","streetName\00000\city\SE" "Name12\streetName\00000\city\SE","Name13\streetName\00001\city\SE"
"2","11","streetName\\city\SE","Name11\\00011\city\SE","Name12\\\city\SE"
"3","13","\\\","\\\\","\\\SE"
"4","14","\\\SE","some\\\\SE","address3\\\\SE"
"5","15","street1\\city1\SE","name15\\city2\SE","name15\street3\12345\city3\SE"
"6","16","street1\12345\\SE","name16\street2\\\SE","name16\\\city3\SE"

原有方案问题 #

原有写法为每个字段的每个分段单独执行一条查询，全量统计需要跑6条独立SQL：

• 每条SQL都触发一次全表扫描，数据量大时IO开销极高
• 同一个分段的空值判断逻辑在SELECT和WHERE子句中重复计算，浪费CPU资源
• 多次发起数据库请求，额外增加网络连接和传输开销
  原有SQL示例：

SELECT COUNT(LENGTH(TRIM(SUBSTRING_INDEX(address1,'\\',1))) = 0 ) 
        AS "CustomerAddress.StreetName" 
FROM library.table_demo 
WHERE LENGTH (TRIM(SUBSTRING_INDEX(address1,'\\',1))) = 0 ;

SELECT COUNT(LENGTH (TRIM(SUBSTRING_INDEX (SUBSTRING_INDEX(address1,"\\",2) ,"\\",-1)) )=0) 
        AS "CustomerAddress.PostalCode" 
FROM library.table_demo 
WHERE LENGTH (TRIM(SUBSTRING_INDEX (SUBSTRING_INDEX(address1,"\\",2) ,"\\",-1)) )=0 ;

SELECT COUNT(LENGTH( TRIM( SUBSTRING_INDEX (SUBSTRING_INDEX(address1,"\\",3) ,"\\",-1) ) =0) ) 
        AS "CustomerAddress.City"    
FROM library.table_demo 
WHERE LENGTH ( TRIM( SUBSTRING_INDEX (SUBSTRING_INDEX(address1,"\\",3) ,"\\",-1))) = 0 ;

优化实现 #

核心思路是单次全表扫描完成所有指标统计，利用MySQL中布尔表达式返回1/0的特性，用SUM(判断条件)直接累计空值数量，避免重复扫描和重复计算。

全MySQL版本兼容写法 #

SELECT
  -- address1 空分段统计
  SUM(LENGTH(TRIM(SUBSTRING_INDEX(address1, '\\', 1))) = 0) AS `address1.StreetName`,
  SUM(LENGTH(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(address1, '\\', 2), '\\', -1))) = 0) AS `address1.PostalCode`,
  SUM(LENGTH(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(address1, '\\', 3), '\\', -1))) = 0) AS `address1.City`,
  -- address2 空分段统计（第2段为街道、第3段为邮编、第4段为城市）
  SUM(LENGTH(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(address2, '\\', 2), '\\', -1))) = 0) AS `address2.StreetName`,
  SUM(LENGTH(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(address2, '\\', 3), '\\', -1))) = 0) AS `address2.PostalCode`,
  SUM(LENGTH(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(address2, '\\', 4), '\\', -1))) = 0) AS `address2.City`,
  -- address3 空分段统计（结构与address2一致）
  SUM(LENGTH(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(address3, '\\', 2), '\\', -1))) = 0) AS `address3.StreetName`,
  SUM(LENGTH(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(address3, '\\', 3), '\\', -1))) = 0) AS `address3.PostalCode`,
  SUM(LENGTH(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(address3, '\\', 4), '\\', -1))) = 0) AS `address3.City`
FROM library.table_demo;

MySQL 8.0+ 高可读性写法 #

用CTE预先拆分所有分段，避免重复写嵌套的SUBSTRING_INDEX逻辑，后续维护调整更方便：

WITH parsed_address AS (
  SELECT
    -- 预拆分address1各分段
    TRIM(SUBSTRING_INDEX(address1, '\\', 1)) AS a1_street,
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(address1, '\\', 2), '\\', -1)) AS a1_postal,
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(address1, '\\', 3), '\\', -1)) AS a1_city,
    -- 预拆分address2各分段
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(address2, '\\', 2), '\\', -1)) AS a2_street,
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(address2, '\\', 3), '\\', -1)) AS a2_postal,
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(address2, '\\', 4), '\\', -1)) AS a2_city,
    -- 预拆分address3各分段
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(address3, '\\', 2), '\\', -1)) AS a3_street,
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(address3, '\\', 3), '\\', -1)) AS a3_postal,
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(address3, '\\', 4), '\\', -1)) AS a3_city
  FROM library.table_demo
)
SELECT
  SUM(LENGTH(a1_street) = 0) AS `address1.StreetName`,
  SUM(LENGTH(a1_postal) = 0) AS `address1.PostalCode`,
  SUM(LENGTH(a1_city) = 0) AS `address1.City`,
  SUM(LENGTH(a2_street) = 0) AS `address2.StreetName`,
  SUM(LENGTH(a2_postal) = 0) AS `address2.PostalCode`,
  SUM(LENGTH(a2_city) = 0) AS `address2.City`,
  SUM(LENGTH(a3_street) = 0) AS `address3.StreetName`,
  SUM(LENGTH(a3_postal) = 0) AS `address3.PostalCode`,
  SUM(LENGTH(a3_city) = 0) AS `address3.City`
FROM parsed_address;

优化收益 #

• 全表扫描次数从6次降到1次，大表场景下性能提升可达数倍
• 去掉WHERE子句的重复判断，每个分段的计算逻辑只执行1次，降低CPU消耗
• 所有统计结果一次返回，减少数据库请求次数和网络传输开销
• 高可读性版本逻辑分层清晰，后续新增统计字段时修改成本更低

注意：MySQL中反斜杠是转义字符，所有分隔符参数必须写为'\\'才能匹配到字符串中实际存储的单个反斜杠，否则会出现拆分错误。

内容的提问来源于stack exchange，提问作者Noel Alex Makumuli