MySQL 大数据量多条件查询优化

1.创建索引

        查询首先是要进行创建时间降序排序，所以联合索引的第一个字段就使用创建时间字段，其余条件跟上即可。

2.查看代码运行时间哪里慢，并分析sql哪里慢

        因为使用了mybatis的分页插件，所以它会帮我们先执行一个count的sql，查询总数，那么就会很慢，那就自己手动写一个，因为mybatis版本原因，没法关闭查询总数，所以查询sql也需要自己写了，sql直接查询并且返回数据的话，会导致很慢四十秒开外了。所以要对sql进行优化，下面是xml文件。

 最开始用的就是最普通的一个单表查询语句，但是会发现搜索的时候条件变化时sql会变慢

 

---

 所以更改为下面这样！

<sql id="whereSql">
    <where>
        <if test="params.id != null and params.id != ''">
            AND ID = #{params.id}
        </if>
        <if test="params.severity != null and params.severity[0] != 'UNKNOWN' and params.severity.size() > 0 ">
            AND severity in
            <foreach collection="params.severity" item="i" index="index" open="(" separator="," close=")">
                #{i}
            </foreach>
        </if>
        <if test="params.severity != null and params.severity.size() > 0 and params.severity[0] == 'UNKNOWN'">
            AND severity is null
        </if>

        <if test="params.availabilityimpact != null and params.availabilityimpact[0] != 'UNKNOWN' and params.availabilityimpact.size() > 0 ">
            AND availabilityimpact in
            <foreach collection="params.availabilityimpact" item="i" index="index" open="(" separator="," close=")">
                #{i}
            </foreach>
        </if>
        <if test="params.availabilityimpact != null and params.availabilityimpact.size() > 0 and params.availabilityimpact[0] == 'UNKNOWN'">
            AND availabilityimpact is null
        </if>

        <if test="params.obtainallprivilege != null and params.obtainallprivilege != ''">
            AND obtainAllPrivilege = #{params.obtainallprivilege}
        </if>
        <if test="params.version != null and params.version != ''">
            AND version like concat('%',#{params.version},'%')
        </if>
    </where>
    ORDER BY publishedDate DESC
</sql>

<select id="selectByParams" resultType="com.entity.AllCve">
    SELECT ac.ID, ac.severity, ac.obtainAllPrivilege, ac.version, ac.availabilityImpact, ac.publishedDate,
    ac.lastModifiedDate
    FROM all_cve as ac,( SELECT ID FROM all_cve <include refid="whereSql"/> limit #{params.pageNumber},#{params.limit}) as a
    WHERE ac.ID = a.ID
    ORDER BY ac.publishedDate DESC
</select>

<select id="selectByCount" resultType="java.util.Map">
    SELECT COUNT(publishedDate) AS sum
    FROM all_cve
    <include refid="whereSql"/>
</select>

这样写sql，经过多次测试，各种条件的sql都可以满足毫秒级查询，并且深翻页也得到了解决！

无条件sql

SELECT
	//需要的字段
FROM
	all_cve AS ac,(
	SELECT
		ID 
	FROM
		all_cve 
	ORDER BY
		publishedDate DESC 
		LIMIT 0,
		10 
	) AS a 
WHERE
	ac.ID = a.ID 
ORDER BY
	ac.publishedDate DESC 

 有条件sql

SELECT
	//需要的字段
FROM
	all_cve AS ac,(
	SELECT
		ID 
	FROM
		all_cve 
	WHERE
		severity IN ( 'CRITICAL', 'HIGH' ) 
		AND availabilityimpact IN ( 'PARTIAL' ) 
		AND version LIKE concat( '%', '2', '%' ) 
	ORDER BY
		publishedDate DESC 
		LIMIT 0,
		10 
	) AS a 
WHERE
	ac.ID = a.ID 
ORDER BY
	ac.publishedDate DESC 

 

注意：中间测试过使用的是in一个id的list但是那样在有条件的时候返回的数据是正常的跟id集合的顺序一致，但是当无条件查询的时候，id集合是降序排序，但是返回的数据并不是时间降序排序！

sql语句后拼接这个排序即可解决in一个list的返回数据的顺序问题