mybatis 使用及 SQL 语句优化小结

【摘要】 MyBatis 作为一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。

一、mybatis 总结

1.mybatis 查询/更新语句，没有找到符合条件的记录，会返回什么?

返回类型为 String 的， 实际返回 null；

返回类型为对象的：实际返回 null ；

返回类型为列表等集合: 实际返回空集合[]；

返回类型为 Boolean：实际返回 false

当数据库语句插入条件不满足，会返回 false；比如使用 dual 的 SQL 语句；

2.mybatis 会抛出哪些常见异常？

不是所有的异常，都认为程序出异常要报错，

比如用户重复收藏商品，可以直接返回成功，这时候对“违反唯一键”异常要特殊处理。

(1) 插入语句

DataIntegrityViolationException： 违反非空约束、数据大小超过约束

DuplicateKeyException：违反唯一键约束

CannotAcquireLockException: for update nowait 超时

(2)更新语句

条件不满足时，会返回 false

数据库操作应该判断返回值，比如下面 BUG：

(3) bug 类，表字段找不到等场景

MyBatisSystemException

BadSqlGrammarException

3.事务里面套用 for update，看上去绕过了事务

等 for update 获取锁后，select 出来的是最新的数据

4.for update 是一种行级锁，又叫排它锁

一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行；

如果查询条件带有主键，会锁行数据，如果没有，会锁表。

如果一定要用 FOR UPDATE，建议加上 NOWAIT 或 for update wait 3

二、SQL 优化

1. 约束条件：数据表增加表的约束条件，防止脏数据。

2. limit1：如果我们知道返回结果只有 1 条，就可以使用 LIMIT 1，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。

3.拼写风格：SQL 保留字使用英文大写，其他使用英文小写；提高可读性。

4.Like：使用 like 加通配符，可能使得索引失效，会触发全表扫描。如果要让索引生效，那么 LIKE 后面就不能以（%）开头，比如使用 LIKE '%太 %'或 LIKE '%太'的时候就会对全表进行扫描。如果使用 LIKE '太 %'，同时检索的字段进行了索引的时候，则不会进行全表扫描。

5.对常用于搜索的字段添加索引，能极大增加查询效率

6.使用“自连接”优于子查询。

7.使用视图：

视图可以理解成给一个查询 SQL 起个别名。

只不过提前经过编译，视图不能传入变量，不保存数据

视图的优点是：隔绝数据表操作

8.使用临时表：

9.IN/EXIST 使用类似两层 for 循环，遵循小表驱动大表原则。

如果两个表中一个较小，一个是大表，则子查询表大的用 exists，子查询表小的用 in：例如：表 A（小表），表 B（大表）

select * from A where cc in (select cc from B) ;// 效率低，用到了 A 表上 cc 列的索引；

select * from A where exists(select cc from B where cc=A.cc) ;// 效率高，用到了 B 表上 cc 列的索引。

10.不建议使用索引的情况：

（1）总数据量很少。

（2）数据重复度大，且不同取值分布均匀，比如性别男女比例各接近 50%。

11.唯一索引/唯一键有 3 个字段时，按其中 1 个字段查询，是否比没有索引效率高？

---- 没有，这种情况创建唯一键，更多的是为了保证数据正确性。

（1）唯一索引和唯一键的区别？

（2）创建联合索引时，我们需要注意创建时的顺序问题，因为联合索引 (x, y, z) 和 (z, y, x) 在使用的时候效率可能会存在差别。比如刚才举例的 (x, y, z)，如果查询条件是 WHERE x=1 AND y=2 AND z=3，就可以匹配上联合索引；如果查询条件是 WHERE y=2，就无法匹配上联合索引。

12.连接表：

（1）连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。

（2）对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。比如 user_id 在 product_comment 表和 user 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

14.索引失效情况

（1）在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。

（2）索引列尽量设置为 NOT NULL 约束。

15.数据表字段，不要用 bool 类型，用 int2 代替 bool 类型，增加扩展能力

本文分享自华为云社区《mybatis 使用及 SQL 语句优化小结》，原文作者：搬搬砖打打游戏 。

点击关注，第一时间了解华为云新鲜技术~