mysql.help_topic 这是使用了这个表的主键,因为它是连续的,不会中断的,也可以换成任意一个相似的表
优化前
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('2016,2017,2018,2019,2020',',',help_topic_id+1),',',-1) AS num FROM mysql.help_topic WHERE help_topic_id < LENGTH('2016,2017,2018,2019,2020')-LENGTH(REPLACE('2016,2017,2018,2019,2020',',',''))+1
sql优化,使用mysql 变量 @type
优化后
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@type,',',help_topic_id+1),',',-1) AS class_id FROM mysql.help_topic ,(select @type:=(select ${type} from organization_info where organization_id=#{organizationId} limit 1)) as init WHERE LENGTH(@type)-LENGTH(REPLACE(@type,',',''))+1 >help_topic_id