SQL相关注意事项_sql开发需要注意的点

SQL技巧

1. 写完SQL先explain查看执行计划（SQL性能优化）

日常开发写SQL的时候，尽量养成这个好习惯呀：写完SQL后，用explain分析一下，尤其注意走不走索引。

2、操作delete或者update语句，加个limit(SQL后悔药）

• 「降低写错SQL的代价」, 你在命令行执行这个SQL的时候，如果不加limit，执行的时候一个「不小心手抖」，可能数据全删掉了，如果「删错」了呢？加了limit 200，就不一样了。删错也只是丢失200条数据，可以通过binlog日志快速恢复的。
• 「SQL效率很可能更高」，你在SQL行中，加了limit 1，如果第一条就命中目标return， 没有limit的话，还会继续执行扫描表。
• 「避免了长事务」，delete执行时,如果age加了索引，MySQL会将所有相关的行加写锁和间隙锁，所有执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。
• 「数据量大的话，容易把CPU打满」 ,如果你删除数据量很大时，不加 limit限制一下记录数，容易把cpu打满，导致越删越慢的。

3. SQL书写格式，关键字大小保持一致，使用缩进。（SQL规范优雅）

即：SELECT,  FROM WHERE等数据库的关键字要统一大写。

4. INSERT语句标明对应的字段名称（SQL规范优雅）

INSERT INTO 表 (要插入的字段名)values(前面对应字段的值)

5. 写完SQL语句，检查where,order by,group by后面的列，多表关联的列是否已加索引，优先考虑组合索引。（SQL性能优化）

6.修改或删除重要数据前，要先备份，先备份，先备份（SQL后悔药）

如果要修改或删除数据，在执行SQL前一定要先备份要修改的数据，万一误操作，还能吃口「后悔药」~

7. where后面的字段，留意其数据类型的隐式转换（SQL性能优化）

几张索引失效的情况

8.减少不必要的字段返回，如使用select <具体字段> 代替 select * （SQL性能优化）

9.数据库和表的字符集统一使用UTF8（SQL规范优雅）

统一使用UTF8编码

• 可以避免乱码问题
• 可以避免，不同字符集比较转换，导致的索引失效问题

10. 索引命名要规范，主键索引名为 pk_ 字段名；唯一索引名为 uk _字段名 ；普通索引名则为 idx _字段名。（SQL规范优雅）

说明：pk_ 即 primary key；uk _ 即 unique key；idx _ 即 index 的简称。

11. WHERE从句中不对列进行函数转换和表达式计算

假设loginTime加了索引

「反例：」

select userId,loginTime from loginuser where Date_ADD(loginTime,Interval 7 DAY) >=now();

「正例：」

explain  select userId,loginTime from loginuser where  loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);

「理由：」

• 索引列上使用mysql的内置函数，索引失效

12.如果修改/更新数据过多，考虑批量进行。

批量插入

和在代码中循环插入单条数据相比：

1. 单条插入方式： 在代码中使用循环，每次循环插入一条数据。这意味着每次插入都会执行一次数据库操作，包括连接数据库、执行插入语句、关闭数据库连接等。如果插入的数据量比较大，这种方式会导致频繁的数据库连接和关闭操作，可能会影响性能。

2. 批量插入方式： 使用批量插入的方式，可以一次性插入多条数据。在上面的示例代码中，使用了MyBatis的foreach标签，将数据集合中的每个元素拼接成插入语句的一部分，然后一次性执行插入操作。这样可以减少数据库连接和关闭的次数，提高插入数据的效率。

总的来说，批量插入方式比循环单条插入方式更高效。它减少了数据库连接和关闭的次数，减少了网络开销，提高了插入数据的速度。因此，在需要批量插入大量数据时，推荐使用批量插入方式。

代码解析：

在这段代码中，使用的是普通的INSERT INTO语句，当执行插入操作时，如果插入的数据中存在重复的主键或唯一索引，MySQL会触发唯一键冲突的异常，导致插入失败。因此，这段代码适用于在插入数据之前先进行主键或唯一索引的校验，确保没有重复的值。

1、可以数据库该主键存在，抛出异常。

2、先根据该主键查询，不存在，再插入；存在，看更新还是其它操作。

但是!!!!!

这种方式可以在代码层面进行唯一性检验，提供更灵活的处理方式，如返回具体的错误信息或执行特定的逻辑。但需要注意的是，由于存在并发操作的情况，即使先查询再插入，仍然可能发生重复插入的情况。因此，在高并发的场景下，还需要通过数据库层面的唯一性约束来确保数据的一致性和完整性。

   若是存在是更新的话，可以使用批量修改的方式一，既可以插入也可以修改，唯一性检验也是数据库层面的。

都根据业务来。

<!-- 批量新增-->
<insert id="batchSave" parameterType="java.util.List">
    INSERT INTO lp_user_test_batch
    (
    id,
    user_id,
    user_name,
    user_age,
    type,
    create_time,
    update_time
    )
    VALUES
    <foreach collection="list" item="item" index="index" separator=",">
        (
        #{item.id,jdbcType=BIGINT},
        #{item.userId,jdbcType=VARCHAR},
        #{item.userName,jdbcType=VARCHAR},
        #{item.userAge,jdbcType=INTEGER},
        #{item.type,jdbcType=INTEGER},
        #{item.createTime,jdbcType=TIMESTAMP},
        #{item.updateTime,jdbcType=TIMESTAMP}
        )
    </foreach>
</insert>
 

批量修改

代码循环修改单条数据和一次性批量修改的区别：与插入同理

1.方式一
批量新增或更新方式
注：需要给唯一主键添加唯一索引，update才会生效

这段代码在主键不冲突时执行插入操作，而在主键冲突时执行更新操作。具体来说，当执行插入操作时，如果插入的数据中存在重复的主键或唯一索引，MySQL会触发唯一键冲突的异常。为了避免异常的抛出，可以使用ON DUPLICATE KEY UPDATE语句来指定在冲突时执行的更新操作。
在这段代码中，ON DUPLICATE KEY UPDATE语句指定了需要更新的列和对应的新值。当插入的数据中存在重复的主键或唯一索引时，MySQL会执行更新操作，将冲突的记录更新为新的值；如果没有冲突，则执行插入操作，插入新的记录。
因此，这段代码实现了在主键冲突时执行更新操作，而在主键不冲突时执行插入操作的功能。

<!-- 批量新增或更新-->
<insert id="batchSaveOrUpdate" parameterType="java.util.List">
    INSERT INTO lp_user_test_batch
    (
    id,
    user_id,
    user_name,
    user_age,
    type,
    create_time,
    update_time
    )
    VALUES
    <foreach collection="list" item="item" index="index" separator=",">
        (
        #{item.id,jdbcType=BIGINT},
        #{item.userId,jdbcType=VARCHAR},
        #{item.userName,jdbcType=VARCHAR},
        #{item.userAge,jdbcType=INTEGER},
        #{item.type,jdbcType=INTEGER},
        #{item.createTime,jdbcType=TIMESTAMP},
        #{item.updateTime,jdbcType=TIMESTAMP}
        )
    </foreach>
    ON DUPLICATE KEY UPDATE
    user_name = VALUES(user_name),
    user_age = VALUES(user_age),
    type = VALUES(type),
    update_time = VALUES(update_time)
</insert>
 

优点：速度快
缺点：使用特殊语法 on duplicate key update 语法 增加sql难度性

2.方式二
单条sql+批量方式的SqlSession

<update id="updateByUserId" >
    UPDATE  lp_user_test_batch
    SET
    user_name = #{userName,jdbcType=VARCHAR},
    user_age = #{userAge,jdbcType=INTEGER},
    type = #{type,jdbcType=INTEGER},
    update_time = #{updateTime,jdbcType=TIMESTAMP}
    WHERE user_id = #{userId,jdbcType=VARCHAR}
</update>
 

@Resource(name = "sqlSessionFactory")
private SqlSessionFactory sqlSessionFactory;
 
/**
 * 利用 MyBatis 批处理特性，批量更新
 */
public void batchUpdate(List<UserTestBatchDO> testBatchDAOList) {
    //集合非空
    if (CollectionUtils.isEmpty(testBatchDAOList)) {
        return;
    }
    //批处理方式 SqlSession
    SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
    //获得对应的Mapper
    UserTestBatchDOMapper userTestBatchDOMapper = sqlSession.getMapper(UserTestBatchDOMapper.class);
    try {
        for (UserTestBatchDO testBatchDO : testBatchDAOList) {
            userTestBatchDOMapper.updateByUserId(testBatchDO);
        }
        //统一提交
        sqlSession.commit();
        //清理缓存，防止溢出
        sqlSession.clearCache();
    } catch (Exception e) {
        //没有提交的数据可以回滚
        sqlSession.rollback();
    } finally {
        //关闭 sqlSession
        sqlSession.close();
    }
}
 

优点：通过日志观察，生成一条执行语句sql ,多行参数，统一commit
缺点：比方式一速度略慢