《面试必考》— MySQL调优指南与踩坑记录（持续更新.....）

本文链接：https://blog.youkuaiyun.com/m0_68921558/article/details/139738117

哈喽，大家好！在平常开发过程中会遇到许多意想不到的坑，本篇文章就记录在开发过程中遇到一些常见的问题

一、replace into 隐藏风险

在上面博客中说到接了一个小活（传送门：一套玩转Datahub，多shard订阅数据（阿里云K8S与Datahub实操）_datahub 订阅量-优快云博客），对接Datahub。为了图省事，没采用saveOrUpdata，直接使用replace into，结果寄了。话不多说，代码如下

  <insert id="tranoutageResult">
        replace into power_measuration_tranoutage
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="psrId != null">psr_id,</if>
            <if test="psrType != null">psr_type,</if>
            <if test="astId != null">ast_id,</if>
            <if test="astType != null">ast_type,</if>
            <if test="termId != null">term_id,</if>
            <if test="termType != null">term_type,</if>
            <if test="startTime != null">start_time,</if>
            <if test="endTime != null">end_time,</if>
            <if test="eventState != null">event_state,</if>
            <if test="eventType != null">event_type,</if>
            <if test="content != null">content,</if>
            <if test="source != null">source</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="psrId != null">#{psrId},</if>
            <if test="psrType != null">#{psrType},</if>
            <if test="astId != null">#{astId},</if>
            <if test="astType != null">#{astType},</if>
            <if test="termId != null">#{termId},</if>
            <if test="termType != null">#{termType},</if>
            <if test="startTime != null">#{startTime},</if>
            <if test="endTime != null">#{endTime},</if>
            <if test="eventState != null">#{eventState},</if>
            <if test="eventType != null">#{eventType},</if>
            <if test="content != null">#{content},</if>
            <if test="source != null">#{source}</if>
        </trim>
    </insert>

        replace into 着实把我坑了一把，没办法只能全网搜寻，这个表是三个字段组成一个联合索引，结果看到这篇文章：MySQL replace into那些隐藏的风险 - Jia-Xin - 博客园 (cnblogs.com)一切都了然。

二、去重问题

一开始从数据库中查询出来，然后在代码中去重，结果数据量大的话，直接寄了。代码如下

//java去重方式：
        List<ExamPaperAnswer> latestAnswers = new ArrayList<>();

        for (ExamPaperAnswer examPaperAnswer : list) {
            boolean shouldAdd = true;

            for (ExamPaperAnswer latestAnswer : latestAnswers) {
                if (examPaperAnswer.getTaskExamId().equals(latestAnswer.getTaskExamId())) {
                    shouldAdd = false;

                    // 通过examPaperAnswer的id选取最大的一个
                    if (examPaperAnswer.getId() > latestAnswer.getId()) {
                        latestAnswers.remove(latestAnswer);
                        latestAnswers.add(examPaperAnswer);
                    }
                    break; // Exit the loop since we found a match
                }
            }

            if (shouldAdd) {
                latestAnswers.add(examPaperAnswer);
            }
        }

这样始终不是办法，在SQL中就是这样的

SELECT * FROM exam_sys_exam_paper_answer WHERE id IN(SELECT
    MAX( id )
FROM
    `exam_sys_exam_paper_answer` 
GROUP BY
    exam_paper_id,
    create_user)

三、根据不同的列来筛选出对应的值

SELECT
        d.dept_name AS xValue,
        COALESCE(SUM(
        CASE
        WHEN #{type} IS NULL THEN IFNULL(points_a, 0) + IFNULL(points_b, 0)
        WHEN #{type} = 1 THEN IFNULL(points_a, 0)
        WHEN #{type} = 2 THEN IFNULL(points_b, 0)
        END
        ), 0) AS yValue,
        d.dept_id AS zValue
        FROM
        exam_sys_dept d
        LEFT JOIN exam_sys_stat_dept_point p ON d.dept_id = p.lv1_dept_id
        WHERE d.parent_id = 200
        <if test="deptId != null">
            AND p.lv1_dept_id = #{deptId}
        </if>
        <if test="week == 'current'">
            AND WEEK(p.update_time, 1) = WEEK(NOW(), 1)
        </if>
        <if test="month == 'current'">
            AND MONTH(p.update_time) = MONTH(NOW())
        </if>

        GROUP BY
        d.dept_name, d.dept_id
        ORDER BY
        yValue DESC

四、获取重复的列

select t1.* from exam_sys_stat_point t1  join (
    select points_c from exam_sys_stat_point group by  points_c having  count(*) >1
) t2 on t1.points_c = t2.points_c

五、获取过去七天数据

 SELECT
        DATE_FORMAT( update_time, '%Y-%m-%d' ) AS xValue,
        SUM( points_c ) AS yValue,
        lv1_dept_id AS zValue
        FROM
        exam_sys_stat_point
        WHERE
                update_time >= CURDATE() - INTERVAL 6 DAY  -- 从今天（含）开始前6天
    AND update_time <= CURDATE()  
        GROUP BY
        DATE_FORMAT(update_time, '%Y-%m-%d'),
        lv1_dept_id
        ORDER BY xValue;

六、传参方式

  List<ExamSysStudyHisDetail>  selectExamSysStudyHisDetailByUserId(Long userId, Long bomId, Long[] ids);

  <select id="selectExamSysStudyHisDetailByUserId" parameterType="Long" resultMap="ExamSysStudyHisDetailResult">
        <include refid="selectExamSysStudyHisDetailVo"/>
        where   user_id =#{arg0} and bom_id =#{arg1}
        and
        asset_id IN
        <foreach item="id" collection="arg2" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>