日期：2022年8月16日

MySql 优化(高薪常问)

1. 定位执行效率慢的 sql 语句.(了解)

• 命令:show status like ‘Com____’,通过这条命令, 我们可以知道当前数据库是以查询为主 还是更新为主. 如果是查询为主, 就重点查询; 如果增删改多就重点优化写入操作.
• explain + sql语句查询sql执行过程, 通过执行计划，我们能得到哪些信息：
  – A：哪些步骤花费的成本比较高
  – B：哪些步骤产生的数据量多，数据量的多少用线条的粗细表示，很直观
  – C：这条sql语句是否走索引
• show profile 分析 SQL,可以查看所有 sql 语句的执行效率(所用时间). 前提是这个命令需要被打开, 严格的说也就是打开这个命令后执行的所有 sql 语句, 它都能记录下执行时间, 并展示出来. 可以通过这个命令分析哪些 sql 语句执行效率低. 耗时长, 就更有针对性的优化这条 sql.
• 慢查询日志(常用的工具)
  慢 查 询 日 志 记 录 了 所 有 执 行 时 间 超 过 参 数 long_query_time 的 sql 语 句 的 日 志 , long_query_time 默认为 10 秒(可以通过配置文件设置), 日志保存在 /var/lib/mysql/目录下, 有个 slow_query.log 文件,

2. 优化索引(高薪)
   2.1 索引设计原则
   索引的设计需要遵循一些已有的原则, 这样便于提升索引的使用效率, 更高效的使用索引.

• 对查询频次较高, 且数据量比较大的表, 建立索引.

• 索引字段的选择, 最佳候选列应当从 where 子句的条件中提取, 如果 where 子句中的组合比较多, 那么应当挑选最常用, 过滤效果最好的列的组合.

• 使用唯一索引, 区分度越高, 使用索引的效率越高.

• 索引并非越多越好, 如果该表赠,删,改操作较多, 慎重选择建立索引, 过多索引会降低表维护效率.

• 使用短索引, 提高索引访问时的 I/O 效率, 因此也相应提升了 Mysql 查询效率.

• 如果 where 后有多个条件经常被用到, 建议建立复合索引, 复合索引需要遵循最左前缀法则, N 个列组合而成的复合索引, 相当于创建了 N 个索引. 复合索引命名规则 index_表名_列名 1_列名 2_列明 3 比如:create index idx_seller_name_sta_addr on tb_seller(name, status, address)

  2.2 避免索引失效

• 如果在查询的时候, 使用了复合索引, 要遵循最左前缀法则, 也就是查询从索引的最左列开始, 并且不能跳过索引中的列.

• 尽量不要在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

• 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。

• 不做列运算where age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数.计算表达式等, 都会是索引失效.

• 查询 like，如果是 ‘%aaa’ 也会造成索引失效.

• 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段
  没有索引，将导致引擎放弃使用索引而进行全表扫描

3. Sql 语句调优(高薪)

• 根据业务场景建立复合索引只查询业务需要的字段，如果这些字段被索引覆盖，将极 大的提高查询效率.
• 多表连接的字段上需要建立索引，这样可以极大提高表连接的效率.
• where 条件字段上需要建立索引, 但 Where 条件上不要使用运算函数，以免索引失效.
• 排序字段上, 因为排序效率低, 添加索引能提高查询效率.
• 优化 insert 语句: 批量列插入数据要比单个列插入数据效率高.
• 优化 order by 语句: 在使用 order by 语句时, 不要使用 select *, select 后面要查有索引的列, 如果一条 sql 语句中对多个列进行排序, 在业务允许情况下, 尽量同时用升序或同时用降序.
• 优化 group by 语句: 在我们对某一个字段进行分组的时候, Mysql 默认就进行了排序, 但是排序并不是我们业务所需的, 额外的排序会降低效率. 所以在用的时候可以禁止排序, 使用 order by null 禁用. select age, count(*) from emp group by age order by null
• 尽量避免子查询, 可以将子查询优化为 join 多表连接查询

4. 合理的数据库设计(了解)
   根据数据库三范式来进行表结构的设计。设计表结构时，就需要考虑如何设计才能更有效的查询, 遵循数据库三范式：

i. 第一范式：数据表中每个字段都必须是不可拆分的最小单元，也就是确保每一列的原子性； 

ii. 第二范式：满足一范式后，表中每一列必须有唯一性，都必须依赖于主键； 

iii. 第三范式：满足二范式后，表中的每一列只与主键直接相关而不是间接相关(外键 也是直接相关)，字段没有冗余。
1
2
3
4
5

注意：没有最好的设计，只有最合适的设计，所以不要过分注重理论。三范式可以作为一个基本依据，不要生搬硬套。有时候可以根据场景合理地反规范化：

A：保留冗余字段。当两个或多个表在查询中经常需要连接时，可以在其中一个表上增加若干冗余的字段，以避免表之间的连接过于频繁，一般在冗余列的数据不经常变动的情况下使用。

B：增加派生列。派生列是由表中的其它多个列的计算所得，增加派生列可以减少统计运算，在数据汇总时可以大大缩短运算时间, 前提是这个列经常被用到, 这也就是反第三范式。

C：分割表。 数据表拆分：主要就是垂直拆分和水平拆分。 水平切分:将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询, 提高效率。垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系。

D: 字段设计

1. 表的字段尽可能用 NOT NULL
2. 字段长度固定的表查询会更快
3. 把数据库的大表按时间或一些标志分成小表

框架

1.Mybatis 框架

谈一谈你对 Mybatis 框架的理解(了解)
MyBatis 是一款优秀的持久层框架，一个半 ORM（对象关系映射）框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生类型、接口和 Java 的 POJO（Plain Old Java Objects，普通老式 Java 对象）为数据库中的记录。

在 mybatis 中,${} 和 #{} 的区别是什么?(必会)

#{} 是占位符，预编译处理，$ {}是字符串替换。 Mybatis 在处理#{}时，会将 sql 中的#{}替换为?号，调用 PreparedStatement 的 set 方法来赋值； Mybatis 在处理$ {}时，就是把${}替换成变量的值。 使用#{}可以有效的防止 SQL 注入，提高系统安全性。

MyBatis 编程步骤是什么样的？(了解)
1、 创建 SqlSessionFactory
2、 通过 SqlSessionFactory 创建 SqlSession
3、 通过 sqlsession 执行数据库操作
4、 调用 session.commit()提交事务
5、 调用 session.close()关闭会话

在 mybatis 中,resultType 和 ResultMap 的区别是什么?(必会)

如果数据库结果集中的列名和要封装实体的属性名完全一致的话用 resultType 属性。

如果数据库结果集中的列名和要封装实体的属性名有不一致的情况用 resultMap 属性，通过 resultMap 手动建立对象关系映射，resultMap 要配置一下表和类的一 一对应关系，所以说就算你的字段名和你的实体类的属性名不一样也没关系，都会给你映射出来

在 Mybatis 中你知道的动态 SQL 的标签有哪些?作用分别是什么?(必会)

1. if 是为了判断传入的值是否符合某种规则,比如是否不为空.
2. where 标签可以用来做动态拼接查询条件,当和 if 标签配合的时候,不用显示的声明类型 where 1 = 1 这种无用的条件
3. foreach 标签可以把传入的集合对象进行遍历,然后把每一项的内容 作为参数传到 sql 语句中.
4. include 可以把大量的重复代码整理起来,当使用的时候直接 include 即可,减少重复代码的编写;
5. 适用于更新中,当匹配某个条件后,才会对该字段进行跟新操作

谈一下你对 mybatis 缓存机制的理解?(了解)

Mybatis 有两级缓存，一级缓存是 SqlSession 级别的，默认开启，无法关闭；二级缓存是 Mapper 级别的，二级缓存默认是没有开启的，但是手动开启

• 一级缓存：基础 PerpetualCache 的 HashMap 本地缓存，其存储作用域为 Session, 当 Session flush 或 close 之后，Session 中的所有 Cache 就将清空
• 二级缓存其存储作用域为 Mapper(Namespace），使用二级缓存属性类需要实现 Serializable 序列化接口
• 对于缓存数据更新机制，当某一个作用域(一级缓存Session/二级缓存 Namespaces) 的进行了 C(增加)/U(更新)/D(删除)操作后，默认该作用域下所有 select 中的缓存将被 clear. 需要在 setting 全局参数中配置开启二级缓存，如下 conf.xml 配置：
  
  当我们的配置文件配置了 cacheEnabled=true 时，就会开启二级缓存，二级缓存是 mapper 级别的，如果你配置了二级缓存，那么查询数据的顺序应该为：二级缓存→一级缓 存→数据库。