数据库

1 SQL优化技巧

• WHERE和ORDER BY涉及的列建立索引

• WHERE 子句中避免使用!=，索引失效

• 左模糊like，索引失效

• or可能使索引失效，可以使用union all代替 SELECT * FROM t WHERE age = 18 OR name = 'John'; SELECT * FROM t WHERE age = 18 UNION SELECT * FROM t WHERE name = 'John';

• IN和NOT IN要慎用，查询参数较多时，会导致全表扫描 当查询条件中的参数列表较少，SQL优化器可能会选择使用嵌套循环连接（nested loop join）等算法，如果被查询的表中存在合适的索引，SQL优化器也可能使用索引来加速查询，避免全表扫描。 当SQL语句中使用了IN操作符，而参数列表较多时，SQL优化器可能会选择对全表进行扫描，然后逐条匹配记录。

• 在判断表中是否存在记录时，推荐使用 EXISTS代替SELECT COUNT(1)，原因如下： 效率更高：EXISTS不需要求记录数，只需要判断是否存在满足条件的记录，而SELECT COUNT(1)需要扫描所有满足条件的记录，并对每条记录都计数，效率相对较低。 占用系统资源更少：SELECT COUNT(1)需要占用内存来存储结果集，而EXISTS不需要存储结果集，仅占用必要的内部系统资源，因此在大数据量的情况下对系统资源的消耗更小。 语义更明确：EXISTS更符合语义，清晰明了地表达了"是否存在"的语义，而SELECT COUNT(1)则强调数量，不够清晰。 SELECT COUNT(1) FROM tb_user WHERE user_name='zhangsan'; SELECT 1 FROM tb_user WHERE EXISTS (SELECT 1 FROM tb_user WHERE user_name='zhangsan');

• 用批量插入或批量更新 当有一批处理的插入或更新时，用批量插入或批量更新，绝不会一条条记录的去更新。

  • 多条提交

  INSERT INTO user (id,username) VALUES(1,'xx'); INSERT INTO user (id,username) VALUES(2,'yy');

  • 批量提交 INSERT INTO user (id,username) VALUES(1,'xx'),(2,'yy'); 默认新增SQL有事务控制，导致每条都需要事务开启和事务提交，而批量处理是一次事务开启和提交，效率提升明显，达到一定量级，效果显著，平时看不出来。

• 合理分表 尽量控制单表数据量的大小，建议控制在500万以内 参考：juejin.cn/post/731078…

2 索引失效

• WHERE 子句中使用!=或<>

• 使用复合索引需要遵循最左前缀原则

• 字段类型不一致，比如数据库是varchar类型，但查询时使用int类型

• 使用函数，比如SELECT * FROM tb_user WHERE DATE(create_time) = '2023-12-01';

• 使用运算符，SELECT * FROM tb_user WHERE pwd_level - 1 = 3;

• 使用or

OR导致索引是在特定情况下的，并不是所有的OR都是使索引失效，如果OR连接的是同一个字段，那么索引不会失效，反之索引失效。

SEECT * FROM tb_user WHERE user_name = 'admin' OR user_type = 1;

• 左模糊

• NOT IN、NOT EXISTS导致索引失效

NOT IN子查询需要对子查询的结果集进行取反操作，这会导致查询优化器放弃使用索引，而使用全表扫描的方式进行查询，从而影响查询性能。 可以将NOT IN子查询转化为LEFT JOIN查询：

SELECT A.* FROM A LEFT JOIN B ON A.id = B.id WHERE B.id IS NULL;

• IS NULL走索引，IS NOT NULL不走索引 zhuanlan.zhihu.com/p/373171506

3 postgres 执行explain analyze结果分析

例一

explain analyze SELECT * FROM tb_user WHERE user_name = 'admin' OR user_type = 1;

执行结果：

Seq Scan on tb_user (cost=0.00..1625.01 rows=1 width=1409) (actual time=0.016..7.049 rows=1 loops=1) Filter: (((user_name)::text = 'admin'::text) OR (user_type = 1)) Rows Removed by Filter: 27800 Planning Time: 0.110 ms Execution Time: 7.091 ms

结果分析：

Seq Scan on tb_user，顺序扫描，即在tb_user表进行了全表扫描

cost=0.00..1625.01 rows=1 width=1409，代价估计 cost: 在..之前的是预估的启动代价，即找到符合改节点条件的第一个结果预估所需要的代价，在..之后的是预估的总代价。 rows:预估的行数 width：预估的结果宽度，单位是字节

actual time=0.016..7.049 rows=1 loops=1，真实执行信息 cost: 在..之前的是实际的启动时间，即找到符合改节点条件的第一个结果实际所需要的时间，在..之后的是节点实际的执行时间。 rows:该节点实际的返回行数 loops：该节点实际的重启次数。如果一个计划节点在运行过程中，它的相关参数值发生了变化，就需要重新运行这个计划节点。

Filter: (((user_name)::text = 'admin'::text) OR (user_type = 1))，Seq Scan节点上的过滤操作，过滤条件 Rows Removed by Filter: 27800，过滤操作过滤了多少行记录 Planning Time: 0.110 ms，生成查询计划的时间 Execution Time: 7.091 ms，实际的sql执行时间，不包括生成查询计划的时间

例二

explain analyze SELECT * FROM tb_user WHERE user_name = 'admin';

执行结果：

Index Scan using uk_tb_user_username on tb_user (cost=0.29..8.30 rows=1 width=1409) (actual time=0.016..0.017 rows=1 loops=1) Index Cond: ((user_name)::text = 'admin'::text) Planning Time: 0.119 ms Execution Time: 0.038 ms

结果分析：

Index Scan：基于索引扫描，但不只是返回索引列的值。使用了uk_tb_user_username索引 Index Cond：扫描条件 参考：blog.csdn.net/weixin_4478…

4 索引

b+树数据结构:juejin.cn/post/684490…

聚集索引和非聚集索引 www.cnblogs.com/xiaomaomao/…

4.1聚集索引（如主键索引）

索引的叶子节点包含了完整的表数据,那么这种索引就称为聚集索引。

因为表中存储的数据是通过聚集索引组织在一起的,所以聚集索引必须要有,且聚集索引只能存在一个。 既然聚集索引必须要有,可是有时候我们创建表的时候并没有设置主键,表照样创建成功,那么还有没有聚集索引呢?如果没有聚集索引 Innodb 的数据靠什么来组织维护呢?

• 如果存在主键,那么主键索引就是聚集索引
• 如果不存在主键,将会使用第一个唯一(UNIQUE)、非空的索引作为聚集索引
• 如果表中既没有主键索引,又没有合适的唯一索引,那么 Innodb 会自动维护一个 row_id(默认大小为 6B)来作为隐藏的聚集索引

4.2 非聚集索引（如年龄、indexcode这些）

非聚集索引查找过程：当通过非聚集索引来查询数据时,存储引擎会根据索引字段定位到最底层的叶子节点（叶子节点存放的是索引列的值 + 对应行记录的主键 id 值）,并通过叶子节点获得指向主键索引的主键id,然后通过主键id去主键索引(聚集索引)上找到一个完整的行记录，这个过程被称为回表。

5 Mybaties的序列化

配置typeHandler，写sql时，#{regTime,javaType=Date,jdbcType=VARCHAR,typeHandler=com.yihaomen.mybatis.type.MyDateTypeHandler}) www.cnblogs.com/happyflying… blog.csdn.net/java_tzx/ar… 5 Mybaties的序列化

6 事务属性：事务隔离级别、传播行为

在事务的并发操作中可能会出现脏读，不可重复读，幻读。

• 脏读：读到未提交事务，转账的例子 事务T1正在操作一条数据，此时事务T2获取该条数据纪录， 如果T1异常，事务回滚，T2读取到的数据就是脏数据，这种现象称为脏读。
• 不可重复读 事务T1多次读取某条记录，在读取间隔中，事务T2更新了该技术的数据， 当T1再次读取该记录时，获取到的数据不一致，这种现象称为不可重复读。产生的原因主要是数据的更新。
• 幻读 事务T1批量处理多条记录，此时事务T2新增或删除了一条或多条记录，当T1处理完成，查询处理结果， 会发现有记录没有处理（T2新增的）或者发现记录少了（T2删除的），会有一种幻觉的感觉，这种现象称为幻读。主要是数据的新增或删除导致。

数据库事务的隔离级别有4种 Read uncommitted：读未提交，一个事务可以读取另一个未提交事务的数据； Read committed：读提交，可以避免脏读； Repeatable read：可重复读，可以避免脏读和不可重复读； Serializable：序列化，事务串行化顺序执行，可以避免脏读、不可重复读和幻读。

• spring事务管理API：
  • PlatformTransactionManager
  • TransactionDefinition
  • TransactionStatus
• 编程式事务和声明式事务实现方式
• 参考：
  • www.ibm.com/developerwo…
  • juejin.im/post/5b00c5…

7 mysql数据如何存储：

• 数据存储在磁盘，如果一条条记录进行修改，从硬盘读入到内存，修改完毕后，再写回内存中，所耗的时间较长，所以就提出了页的概念
• 页：16KB，里面可以放很多条记录，这样对页内记录修改，以及对页进行持久化时间效率会大大提升
• 页和页之间是双向链表，页内存有一条条记录，记录的保存方式是单链表，默认是按主键从大到小的顺序排列，如何高效的从页中找到对应主键的记录呢，这就提出了槽的概念
• 槽：一个槽中可以存放4-7个不等的记录，每个页内会有一个连续的空间存储这些槽，槽内的记录是按照主键的顺序存放，再查询的过程中会使用二分法进行快速定位到槽的位置，然后再依次遍历槽内存的单链表记录
• 额外的概念：每个记录并不是只有列和主键这些信息，其中还保存了一些额外的信息，比如记录的类型，每个页都存在两条记录，一个是最小记录，一个是最大记录，最小记录的类型值为2，最大记录的类型值为3，普通记录的类型值为0 juejin.im/post/5df09d…

为什么要给表加上主键？

• 不加主键，数据在磁盘中是一行一行，无序的
• 增加主键，数据在磁盘中是一颗平衡树，查询效率提高。主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。

为什么加索引后会使查询变快？

• 增加索引之后，数据存储的格式是一颗平衡树

为什么加索引后会使写入、修改、删除变慢？

• 数据并更时，需要维护索引结构的正确性 所以一个表建的索引尽量不要超过5个，增删改时维护索引开销大，查询时选择索引也有开销

什么情况下要同时在两个字段上建索引？ 例如：查询学生表的一条记录，获取学号

• 建立姓名的索引：根据姓名找到所有对应记录的主键ID，根据主键ID找到对应的数据，再获取学号
• 建立姓名和学号的索引：根据姓名可以找到所有对应记录的主键ID和学号，少了两个步骤，可以提高效率

最左前缀匹配原则 1 对于联合索引，MySQL 会一直向右匹配直到遇到范围查询（> ， < ，between，like）就停止匹配。 比如 a = 3 and b = 4 and c > 5 and d = 6，如果建立的是（a,b,c,d）这种顺序的索引，那么 d 是用不到索引的， 但是如果建立的是 （a,b,d,c）这种顺序的索引的话，那么就没问题，而且 a，b，d 的顺序可以随意调换。 2 = 和 in 可以乱序，比如 a = 3 and b = 4 and c = 5 建立 （a，b，c）索引可以任意顺序。 3 如果建立的索引顺序是 （a，b）那么直接采用 where b = 5 这种查询条件是无法利用到索引的，这一条最能体现最左匹配的特性。

8 数据库锁

• 共享锁（读锁、S锁）：加了共享锁后，其他事物可以继续加共享锁
• 排他锁（写锁、X锁）：加了排他锁后，其他事物不能加锁
• 意向锁：
• 表锁：不命中索引
• 行锁：命中索引，包括记录锁，间隙锁，临键锁

  • 记录锁：命中索引并且索引是唯一索引

  • 间隙锁：区间锁，查询条件命中索引，并且没有查询到符合条件的记录，锁定区间是左开右闭区间如(1,3]。间隙锁只会出现在可重复读的事务隔离级别中

  • 临键锁：查询条件命中索引，有匹配到数据库记录；锁定当前记录的区间和下一个记录的区间，如数据id范围是17，查询范围是17，则锁定范围为(1,7]。如果查询范围是1~8，则锁定范围为(1，+OO)

zhuanlan.zhihu.com/p/52678870 juejin.im/post/5b6c5b… juejin.im/post/5b55b8…

9 in和exist的区别

• in：遍历次数A.length*B.length，适用于A表（tb_user）数据大于B表（tb_user_region）数据的情况。in()只执行一次,它查出B表中的所有id字段并缓存起来。 select * from tb_user where user_name in (select user_name from tb_user_region)
• exists：遍历次数A.length，所以适用于A表数据小于B表数据的情况。exists()不缓存结果集,只判断结果集中是否有记录,如果有则返回true,没有则返回false。 select us.* from tb_user us where exists (select 1 from tb_user_region ur where us.user_name = ur.user_name)

10 Mybaties的#和$区别

@Select("select user_name from tb_user where is_delete = 0 and #{userName,jdbcType=VARCHAR}") 预编译 Preparing: SELECT user_name FROM tb_user WHERE is_delete = 0 AND user_name = ? Parameters: ply(String) Total: 1

@Select("select user_name from tb_user where is_delete = 0 and user_name = '${userName}'") 预编译 Preparing: select user_name from tb_user where is_delete = 0 and user_name = 'ply' Parameters: Total: 1

区别

• #解析参数会自动加上''，$是直接拼接，不加''，预编译结果为 select user_name from tb_user where is_delete = 0 and user_name = ply
• 能使用#的地方就用#
  • 相同的预编译sql可以重复利用，可以提高性能
  • $会存在sql注入问题，举例：

  select * from ${tableName} where name = #{name} 假如参数tableName为user; delete user; --， 那么SQL动态解析阶段之后，预编译之前的sql将变为： select * from user; delete user; -- where name = ? -- 之后的语句将作为注释，不起作用，因此本来的一条查询语句偷偷的包含了一个删除表数据的 SQL。

11 Mybatis特点

优点： 支持动态sql（if标签动态拼接查询条件），可以写复杂的sql，通过xml统一管理sql 缺点： sql依赖于数据库，导致数据库移植性差（如拼接分页参数，oracle使用rownum，mysql使用limit） 日志功能薄弱

缓存： 一级缓存：一级缓存的作用域是SqlSession，即同一个session进行两次相同查询，MyBatis只查1次数据库。 一级缓存不需要配置，默认是打开的，缓存使用Map<Object, Object>存储，执行新增或更新或删除操作，一级缓存会被清除。 二级缓存：二级缓存的作用域是全局的，二级缓存在SqlSession关闭或提交之后才会生效。 查询数据时，先从二级缓存中拿数据，没查到再去一级缓存中拿，一级缓存也没有的话再查询数据库。 开启二级缓存需要增加节点，存储接口是Cache接口的实现类，可以是map，也可以是其他数据结构。 www.cnblogs.com/jtlgb/p/603…

12 hibernate特点

优点： 动态生成sql，代码简洁 数据库移植性好 有更好的二级缓存机制，可以使用第三方缓存 有完整的日志系统 缺点： 学习门槛高，如何设计O/R映射，在性能和对象模型直接如何平衡，怎样用好这个框架都需要经验和能力 sql不够灵活

调Dao接口时怎么找到mapper的？Dao接口方法能重载吗？ 当调用Dao接口时，可以找到唯一一个，namespace为dao类包名的，id为方法名的MappedStatement。 Dao接口的工作原理是JDK动态代理，使用JDK动态代理为Dao接口生成代理proxy对象， 代理对象proxy会拦截接口方法，转而执行MappedStatement所代表的sql，然后将sql执行结果返回。

13 Mybatis是如何进行分页的？分页插件的原理是什么？

分页插件的原理是使用Mybatis提供插件接口，实现自定义的插件，在插件的拦截方法内拦截待执行的sql，然后重写sql， 根据dialect方言，添加对应的物理分页语句和物理分页参数。

14 Mybatis如何将sql执行结果映射为目标对象?

• 定义别名，将列别名定义为对象属性名
• resultMap，逐一定义列名和对象属性名之间的映射关系

15 Mybatis源码解读，代码入口，事务，数据库连接

blog.csdn.net/heroqiang/a… 获取SqlSession，获取mapper，执行sql www.cnblogs.com/dongying/p/… 常见问题汇总 blog.csdn.net/weixin_4357…

Mybatis代码入口 SqlSessionFactoryBuilder.build()，创建SqlSessionFactory XMLConfigBuilder.parse()，SqlSessionFactoryBuilder 通过XMLConfigBuilder 去解析我们传入的mybatis的配置文件 XMLConfigBuilder.parseConfiguration()，在根节点configuration节点下，可以配置10个子节点包括：properties、typeAliases、plugins、、settings、environments、typeHandlers、mappers等

Mybaties的序列化 配置typeHandler，写sql时，#{regTime,javaType=Date,jdbcType=VARCHAR,typeHandler=com.yihaomen.mybatis.type.MyDateTypeHandler}) www.cnblogs.com/happyflying… blog.csdn.net/java_tzx/ar…

16 sql语句编写

sql查询：查询用户名，工资，查询最高的前100个，倒序 工资表（salary） 用户名 基本工资 绩效表（merity） 用户名 绩效得分

SELECT A .access_name, SUM ( A .salary + A .salary * ( CASE WHEN b.merity_key >= 90 THEN 1.3 WHEN b.merity_key >= 80 AND b.merity_key < 90 THEN 1.2 WHEN b.merity_key >= 70 AND b.merity_key < 80 THEN 1.1 WHEN b.merity_key >= 60 AND b.merity_key < 70 THEN 1.0 ELSE 0.8 END ) ) AS money FROM tb_third_api A JOIN tb_ezviz_user b ON A .access_name = b.login_name GROUP BY A .access_name ORDER BY money DESC LIMIT 4 offset 0