数据库|SQL行转列方式优化查询性能实践数据库|database|sql

文章目录

- - 问题
  - 优化过程
  - SQL分析

问题
案例：项目中发现如下类似SQL，该查询语句非常低下，嵌套子查询

SELECTinfo.* fromAinfowhereEXISTS ( SELECTb.idFROMBb whereb.family_memberREGEXPinfo.name AND b.`status`=0 );

其中匹配的字段信息如下所示，是一组成员姓名，其中每个人信息是以逗号分割的。

文章图片

在尝试使用like，join关联查询查询效率也很慢，尝试使用函数索引的方式但是因为是动态关联，函数参数是动态的也不适用。
在分析数据后发现单列数据虽然存在多个，但是数据的格式是固定的，即以逗号分割，这个逗号可能出现在开头，中间，结尾。于是想到了行转列的方式，以逗号进行拆分将数据进行转换，然后进行关联匹配。
优化过程
创建中间表help_topic_index表，如果不想创建可以直接使用mysql.help_topic。

CREATE TABLE `help_topic_index` ( `help_topic_id` int(11) NOT NULL ) ENGINE=InnoDBDEFAULT CHARSET=utf8mb4;

构建表数据，就是从0开始的索引下标数据，这里最大设置1000的。

-- 创建测试存储过程，使用完注意删除 CREATE DEFINER=`root`@`%` PROCEDURE help_topic_index_call() BEGIN declare i int; set i=0; while i<=1000 do insert into help_topic_index(help_topic_id) values(i); set i=i+1; end while; END-- 执行 call help_topic_index_call();

SQL分析
SUBSTRING_INDEX函数

SUBSTRING_INDEX(s, delimiter, number)

返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。
1、如果 number 是正数，返回第 number 个字符左边的字符串。
1、如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。

SELECTa.name_list原始数据, substring_index( a.name_list, ',', b.help_topic_id + 1 )当前子串, substring_index( substring_index( a.name_list, ',', b.help_topic_id + 1 ), ',', - 1 )解析结果 FROM ( SELECT ',张三,,李四,王五' as name_listfrom dual) a INNER JOIN help_topic_index b on b.help_topic_id < ( length( a.name_list) - length( REPLACE ( a.name_list, ',', '' )) + 1 )

【数据库|SQL行转列方式优化查询性能实践】

文章图片

SQL分析：
【1】b.help_topic_id < ( length( a.name_list ) - length( REPLACE ( a.name_list, ‘,’, ‘’ )) + 1 ) 条件用于计算去除分割符后有多个组数据
【2】substring_index( a.name_list, ‘,’, b.help_topic_id + 1 ) 当前子串。通过help_topic_index中间表递增的序列值，通过substring_index依次得到子串数据
【3】上一步通过正向获取每一组子串，如上图所示前缀是一样的，所以每一次获取后缀的数据即可，这样就可以解析出最终的结果
【4】通过上面的SQL就可以获取到行转列的数据，然后将转换后的结果通过SQL精确匹配即可，在根据实际情况设置索引这样整体的查询效率就很快了。
【5】上面的案例中几十万的数据，原先的查询需要几十秒，优化后几毫秒内可以查询出结果。