java核心知识点--数据库操作

1、MyBatis 核心组件
MyBatis 介绍
MyBatis 是一款优秀的 ORM（Object Relational Mapping，对象关系映射）框架，它可以通过对象和数据库之间的映射，将程序中的对象自动存储到数据库中。它是 Apache 提供的一个开源项目，之前的名字叫做 iBatis，2010 年迁移到了 Google Code，并且将名字改为我们现在所熟知的 MyBatis，又于 2013 年 11 月迁移到了 Github。

MyBatis 提供了普通 SQL 查询、事务、存储过程等功能，它的优缺点如下。

优点：

相比于 JDBC 需要编写的代码更少
使用灵活，支持动态 SQL
提供映射标签，支持对象与数据库的字段关系映射
缺点：

SQL 语句依赖于数据库，数据库移植性差
SQL 语句编写工作量大，尤其在表、字段比较多的情况下
总体来说，MyBatis 是一个非常优秀和灵活的数据持久化框架，适用于需求多变的互联网项目，也是当前主流的 ORM 框架。

MyBatis 重要组件
MyBatis 中的重要组件如下：

Mapper 配置：用于组织具体的查询业务和映射数据库的字段关系，可以使用 XML 格式或 Java 注解格式来实现；
Mapper 接口：数据操作接口也就是通常说的 DAO 接口，要和 Mapper 配置文件中的方法一一对应；
Executor：MyBatis 中所有的 Mapper 语句的执行都是通过 Executor 执行的；
SqlSession：类似于 JDBC 中的 Connection，可以用 SqlSession 实例来直接执行被映射的 SQL 语句；
SqlSessionFactory：SqlSessionFactory 是创建 SqlSession 的工厂，可以通过 SqlSession openSession() 方法创建 SqlSession 对象。
MyBatis 执行流程
MyBatis 完整执行流程如下图所示：

MyBatis 执行流程说明：

首先加载 Mapper 配置的 SQL 映射文件，或者是注解的相关 SQL 内容。
创建会话工厂，MyBatis 通过读取配置文件的信息来构造出会话工厂（SqlSessionFactory）。
创建会话，根据会话工厂，MyBatis 就可以通过它来创建会话对象（SqlSession），会话对象是一个接口，该接口中包含了对数据库操作的增、删、改、查方法。
创建执行器，因为会话对象本身不能直接操作数据库，所以它使用了一个叫做数据库执行器（Executor）的接口来帮它执行操作。
封装 SQL 对象，在这一步，执行器将待处理的 SQL 信息封装到一个对象中（MappedStatement），该对象包括 SQL 语句、输入参数映射信息（Java 简单类型、HashMap 或 POJO）和输出结果映射信息（Java 简单类型、HashMap 或 POJO）。
操作数据库，拥有了执行器和 SQL 信息封装对象就使用它们访问数据库了，最后再返回操作结果，结束流程。
MyBatis XML 版
MyBatis 使用分为两个版本：XML 版和 Java 注解版。接下来我们使用 Spring Boot 结合 MyBatis 的 XML 版，来实现对数据库的基本操作，步骤如下。

1）创建数据表
drop table if exists t_user;
create table t_user (
id bigint(20) not null auto_increment comment ‘主键id’,
username varchar(32) default null comment ‘用户名’,
password varchar(32) default null comment ‘密码’,
nick_name varchar(32) default null,
primary key (id)
) engine=innodb auto_increment=1 default charset=utf8;
2）添加依赖
在项目添加对 MyBatis 和 MySQL 支持的依赖包，在 pom.xml 文件中添加如下代码：

org.mybatis.spring.boot mybatis-spring-boot-starter 2.1.0 mysql mysql-connector-java 8.0.16 mybatis-spring-boot-starter 是 MyBatis 官方帮助我们快速集成 Spring Boot 提供的一个组件包，mybatis-spring-boot-starter 2.1.0 对应 MyBatis 的版本是 3.5.2。

3）增加配置文件
在 application.yml 文件中添加以下内容：

spring:
datasource:
url: jdbc:mysql://localhost:3306/learndb?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
mybatis:
config-location: classpath:mybatis/mybatis-config.xml
mapper-locations: classpath:mybatis/mapper/*.xml
type-aliases-package: com.interview.mybatislearning.model
其中：

mybatis.config-location：配置 MyBatis 基础属性；
mybatis.mapper-locations：配置 Mapper 对应的 XML 文件路径；
mybatis.type-aliases-package：配置项目中实体类包路径。
注：如果配置文件使用的是 application.properties，配置内容是相同的，只是内容格式不同。

4）创建实体类
public class UserEntity implements Serializable {
private static final long serialVersionUID = -5980266333958177104L;
private Integer id;
private String userName;
private String passWord;
private String nickName;
public UserEntity(String userName, String passWord, String nickName) {
this.userName = userName;
this.passWord = passWord;
this.nickName = nickName;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassWord() {
return passWord;
}
public void setPassWord(String passWord) {
this.passWord = passWord;
}
public String getNickName() {
return nickName;
}
public void setNickName(String nickName) {
this.nickName = nickName;
}
}
5）创建 XML 文件
mybatis-config.xml（基础配置文件）：

<?xml version="1.0" encoding="UTF-8" ?> mybatis-config.xml 主要是为常用的数据类型设置别名，用于减少类完全限定名的长度，比如：resultType="Integer" 完整示例代码如下： select count(*) from t_user UserMapper.xml（业务配置文件）： <?xml version="1.0" encoding="UTF-8" ?> id, username, password, nick_name and userName = #{userName} SELECT FROM t_user SELECT FROM t_user WHERE id = #{id} INSERT INTO t_user (username,password,nick_name) VALUES (#{userName}, #{passWord}, #{nickName}) UPDATE t_user SET username = #{userName}, password = #{passWord}, nick_name = #{nickName} WHERE id = #{id} DELETE FROM t_user WHERE id =#{id} 以上配置我们增加了增删改查等基础方法。

6）增加 Mapper 文件
此步骤我们需要创建一个与 XML 对应的业务 Mapper 接口，代码如下：

public interface UserMapper {
List getAll();
UserEntity getOne(Long id);
void insert(UserEntity user);
void update(UserEntity user);
void delete(Long id);
}
7）添加 Mapper 包扫描
在启动类中添加 @MapperScan，设置 Spring Boot 启动的时候会自动加载包路径下的 Mapper。

@SpringBootApplication
@MapperScan(“com.interview.mybatislearning.mapper”)
public class MyBatisLearningApplication {
public static void main(String[] args) {
SpringApplication.run(MyBatisLearningApplication.class, args);
}
}
8）编写测试代码
经过以上步骤之后，整个 MyBatis 的集成就算完成了。接下来我们写一个单元测试，验证一下。

@RunWith(SpringRunner.class)
@SpringBootTest
public class MybatislearningApplicationTests {
@Resource
private UserMapper userMapper;
@Test
public void testInsert() {
userMapper.insert(new UserEntity(“laowang”, “123456”, “老王”));
Assert.assertEquals(1, userMapper.getAll().size());
}
}
总结
通过本文我们知道 MyBatis 是一个优秀和灵活的数据持久化框架，MyBatis 包含 Mapper 配置、Mapper 接口、Executor、SqlSession、SqlSessionFactory 等几个重要的组件，知道了 MyBatis 基本流程：MyBatis 首先加载 Mapper 配置和 SQL 映射文件，通过创建会话工厂得到 SqlSession 对象，再执行 SQL 语句并返回操作信息。我们也使用 XML 的方式，实现了 MyBatis 对数据库的基础操作。

2、对数据库的基本操作步骤
MyBatis 最初的设计是基于 XML 配置文件的，但随着 Java 的发展（Java 1.5 开始引入注解）和 MyBatis 自身的迭代升级，终于在 MyBatis 3 之后就开始支持基于注解的开发了。

下面我们使用 Spring Boot + MyBatis 注解的方式，来实现对数据库的基本操作，具体实现步骤如下。

MyBatis 注解版
1）创建数据表
drop table if exists t_user;
create table t_user (
id bigint(20) not null auto_increment comment ‘主键id’,
username varchar(32) default null comment ‘用户名’,
password varchar(32) default null comment ‘密码’,
nick_name varchar(32) default null,
primary key (id)
) engine=innodb auto_increment=1 default charset=utf8;
2）添加依赖

org.mybatis.spring.boot mybatis-spring-boot-starter 2.1.0 mysql mysql-connector-java 8.0.16 3）增加配置文件 在 application.yml 文件中添加以下内容：

spring:
datasource:
url: jdbc:mysql://localhost:3306/learndb?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
mybatis:
type-aliases-package: com.interview.model
4）创建实体类
public class UserEntity implements Serializable {
private static final long serialVersionUID = -5980266333958177105L;
private Integer id;
private String userName;
private String passWord;
private String nickName;
public UserEntity(String userName, String passWord, String nickName) {
this.userName = userName;
this.passWord = passWord;
this.nickName = nickName;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassWord() {
return passWord;
}
public void setPassWord(String passWord) {
this.passWord = passWord;
}
public String getNickName() {
return nickName;
}
public void setNickName(String nickName) {
this.nickName = nickName;
}
}
5）增加 Mapper 文件
public interface UserMapper {
@Select(“select * from t_user”)
@Results({
@Result(property = “nickName”, column = “nick_name”)
})
List getAll();

@Select("select * from t_user where id = #{id}")
@Results({
        @Result(property = "nickName", column = "nick_name")
})
UserEntity getOne(Long id);

@Insert("insert into t_user(username,password,nick_name) values(#{userName}, #{passWord}, #{nickName})")
void insert(UserEntity user);

@Update("update t_user set username=#{userName},nick_name=#{nickName} where id =#{id}")
void update(UserEntity user);

@Update({"<script> ",
        "update t_user ",
        "<set>",
        " <if test='userName != null'>userName=#{userName},</if>",
        " <if test='nickName != null'>nick_name=#{nickName},</if>",
        " </set> ",
        "where id=#{id} ",
        "</script>"})
void updateUserEntity(UserEntity user);

@Delete("delete from t_user where id =#{id}")
void delete(Long id);

}
使用 @Select、@Insert、@Update、@Delete、@Results、@Result 等注解来替代 XML 配置文件。

6）添加 Mapper 包扫描
在启动类中添加 @MapperScan，设置 Spring Boot 启动的时候会自动加载包路径下的 Mapper。

@SpringBootApplication
@MapperScan(“com.interview.mapper”)
public class MybatisApplication {
public static void main(String[] args) {
SpringApplication.run(MybatisApplication.class, args);
}
}
7）编写测试代码
@RunWith(SpringRunner.class)
@SpringBootTest
public class MybatisApplicationTests {
@Autowired
private UserMapper userMapper;
@Test
public void testInsert() {
userMapper.insert(new UserEntity(“laowang”, “123456”, “老王”));
Assert.assertEquals(1, userMapper.getAll().size());
}
}
相关面试题
1.MyBatis 有哪些优缺点？
答：MyBatis 优缺点如下：

优点：

相比于 JDBC 需要编写的代码更少
使用灵活，支持动态 SQL
提供映射标签，支持对象与数据库的字段关系映射
缺点：

SQL 语句依赖于数据库，数据库移植性差
SQL 语句编写工作量大，尤其在表、字段比较多的情况下
总体来说，MyBatis 是一个非常不错的持久层解决方案，它专注于 SQL 本身，非常灵活，适用于需求变化较多的互联网项目，也是当前国内主流的 ORM 框架。

2.以下不属于 MyBatis 优点的是？
A：可以灵活的编辑 SQL 语句
B：很好的支持不同数据库之间的迁移
C：能够很好的和 Spring 框架集成
D：提供映射标签支持对象和数据库的字段映射

答：B

题目解析：因为 MyBatis 需要自己编写 SQL 语句，但每个数据库的 SQL 语句有略有差异，所以 MyBatis 不能很好的支持不同数据库之间的迁移。

3.MyBatis 和 Hibernate 有哪些不同？
答：MyBatis 和 Hibernate 都是非常优秀的 ORM 框架，它们的区别如下：

灵活性：MyBatis 更加灵活，自己可以写 SQL 语句，使用起来比较方便；
可移植性：MyBatis 有很多自己写的 SQL，因为每个数据库的 SQL 可以不相同，所以可移植性比较差；
开发效率：Hibernate 对 SQL 语句做了封装，让开发者可以直接使用，因此开发效率更高；
学习和使用门槛：MyBatis 入门比较简单，使用门槛也更低。
4.“#”和“KaTeX parse error: Expected 'EOF', got '#' at position 12: ”有什么区别？ 答：“#̲”是预编译处理，“”是字符替换。 在使用“#”时，MyBatis 会将 SQL 中的参数替换成“?”，配合 PreparedStatement 的 set 方法赋值，这样可以有效的防止 SQL 注入，保证程序的运行安全。

5.在 MyBatis 中怎么解决实体类属性名和表字段名不一致的问题？
答：通常的解决方案有以下两种方式。

① 在 SQL 语句中重命名为实体类的属性名，可参考以下配置：

select order_id id, order_no orderno form order where order_id=#{id}; ② 通过 映射对应关系，可参考以下配置： select * from t_user 6.在 MyBatis 中如何实现 like 查询？ 答：可以在 Java 代码中添加 SQL 通配符来实现 like 查询，这样也可以有效的防治 SQL 注入，具体实现如下：

Java 代码：

String name = “%wang%”:
List list = mapper.likeName(name);
Mapper 配置：

select * form t_user where name like #{name}; 7.MyBatis 有几种分页方式？ 答：MyBatis 的分页方式有以下两种：

逻辑分页，使用 MyBatis 自带的 RowBounds 进行分页，它是一次性查询很多数据，然后在数据中再进行检索；
物理分页，自己手写 SQL 分页或使用分页插件 PageHelper，去数据库查询指定条数的分页数据形式。
8.RowBounds 是一次性查询全部结果吗？为什么？
答：RowBounds 表面是在“所有”数据中检索数据，其实并非是一次性查询出所有数据。因为 MyBatis 是对 JDBC 的封装，在 JDBC 驱动中有一个 Fetch Size 的配置，它规定了每次最多从数据库查询多少条数据，假如你要查询更多数据，它会在执行 next() 的时候，去查询更多的数据。 就好比你去自动取款机取 10000 元，但取款机每次最多能取 2500 元，要取 4 次才能把钱取完。对于 JDBC 来说也是一样，这样做的好处是可以有效的防止内存溢出。

9.为什么阿里巴巴不允许使用 HashMap 或 Hashtable 作为查询结果集直接输出？
答：因为使用 HashMap 或 Hashtable 作为查询结果集直接输出，会导致值类型不可控，给调用人员造成困扰，给系统带来更多不稳定的因素。

10.什么是动态 SQL？
答：动态 SQL 是指可以根据不同的参数信息来动态拼接的不确定的 SQL 叫做动态 SQL，MyBatis 动态 SQL 的主要元素有：if、choose/when/otherwise、trim、where、set、foreach 等。 以 if 标签的使用为例：

select * from t_user where id = #{id} and username = #{username} and password = #{password} 11.为什么不建议在程序中滥用事务？ 答：因为事务的滥用会影响数据的 QPS（每秒查询率），另外使用事务的地方还要考虑各方面回滚的方案，如缓存回滚、搜索引擎回滚、消息补偿、统计修正等。

12.如何开启 MyBatis 的延迟加载？
答：只需要在 mybatis-config.xml 设置 即可打开延迟缓存功能，完整配置文件如下：

13.什么是 MyBatis 的一级缓存和二级缓存？ 答：MyBatis 缓存如下：

一级缓存是 SqlSession 级别的，是 MyBatis 自带的缓存功能，并且无法关闭，因此当有两个 SqlSession 访问相同的 SQL 时，一级缓存也不会生效，需要查询两次数据库；
二级缓存是 Mapper 级别的，只要是同一个 Mapper，无论使用多少个 SqlSession 来操作，数据都是共享的，多个不同的 SqlSession 可以共用二级缓存，MyBatis 二级缓存默认是关闭的，需要使用时可手动开启，二级缓存也可以使用第三方的缓存，比如，使用 Ehcache 作为二级缓存。
手动开启二级缓存，配置如下：

14.如何设置 Ehcache 为 MyBatis 的二级缓存？ 答：可直接在 XML 中配置开启 EhcacheCache，代码如下：

<select id="findById" parameterType="java.lang.Long" resultType="com.interview.entity.Classes">
    select * from classes where id = #{id}
</select>

15.MyBatis 有哪些拦截器？如何实现拦截功能？ 答：MyBatis 提供的连接器有以下 4 种。

Executor：拦截内部执行器，它负责调用 StatementHandler 操作数据库，并把结果集通过 ResultSetHandler 进行自动映射，另外它还处理了二级缓存的操作。
StatementHandler：拦截 SQL 语法构建的处理，它是 MyBatis 直接和数据库执行 SQL 脚本的对象，另外它也实现了 MyBatis 的一级缓存。
ParameterHandler：拦截参数的处理。
ResultSetHandler：拦截结果集的处理。
拦截功能具体实现如下：

@Intercepts({@Signature(type = Executor.class, method = “query”,
args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
public class TestInterceptor implements Interceptor {
public Object intercept(Invocation invocation) throws Throwable {
Object target = invocation.getTarget(); //被代理对象
Method method = invocation.getMethod(); //代理方法
Object[] args = invocation.getArgs(); //方法参数
// 方法拦截前执行代码块
Object result = invocation.proceed();
// 方法拦截后执行代码块
return result;
}
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
}
总结
通过本文可以看出 MyBatis 注解版和 XML 版的主要区别是 Mapper 中的代码，注解版把之前在 XML 的 SQL 实现，全部都提到 Mapper 中了，这样就省去了配置 XML 的麻烦。

3、MySQL 面试题汇总
1.说一下 MySQL 执行一条查询语句的内部执行过程？
答：MySQL 执行一条查询的流程如下：

客户端先通过连接器连接到 MySQL 服务器；
连接器权限验证通过之后，先查询是否有查询缓存，如果有缓存（之前执行过此语句）则直接返回缓存数据，如果没有缓存则进入分析器；
分析器会对查询语句进行语法分析和词法分析，判断 SQL 语法是否正确，如果查询语法错误会直接返回给客户端错误信息，如果语法正确则进入优化器；
优化器是对查询语句进行优化处理，例如一个表里面有多个索引，优化器会判别哪个索引性能更好；
优化器执行完就进入执行器，执行器则开始执行语句进行查询比对了，直到查询到满足条件的所有数据，然后进行返回。
2.MySQL 查询缓存有什么优缺点？
答：MySQL 查询缓存功能是在连接器之后发生的，它的优点是效率高，如果已经有缓存则会直接返回结果。

查询缓存的缺点是失效太频繁导致缓存命中率比较低，任何更新表操作都会清空查询缓存，因此导致查询缓存非常容易失效。

3.MySQL 的常用引擎都有哪些？
答：MySQL 的常用引擎有 InnoDB、MyISAM、Memory 等，从 MySQL 5.5.5 版本开始 InnoDB 就成为了默认的存储引擎。

4.常用的存储引擎 InnoDB 和 MyISAM 有什么区别？
答：InnoDB 和 MyISAM 最大的区别是 InnoDB 支持事务，而 MyISAM 不支持事务，它们其他主要区别如下：

InnoDB 支持崩溃后安全恢复，MyISAM 不支持崩溃后安全恢复；
InnoDB 支持行级锁，MyISAM 不支持行级锁，只支持到表锁；
InnoDB 支持外键，MyISAM 不支持外键；
MyISAM 性能比 InnoDB 高；
MyISAM 支持 FULLTEXT 类型的全文索引，InnoDB 不支持 FULLTEXT 类型的全文索引，但是 InnoDB 可以使用 sphinx 插件支持全文索引，并且效果更好；
InnoDB 主键查询性能高于 MyISAM。
5.什么叫回表查询？
答：普通索引查询到主键索引后，回到主键索引树搜索的过程，我们称为回表查询。

6.如果把一个 InnoDB 表的主键删掉，是不是就没有主键，就没办法进行回表查询了？
答：不是，如果把主键删掉了，那么 InnoDB 会自己生成一个长度为 6 字节的 rowid 作为主键。

7.一张自增表中有三条数据，删除两条数据之后重启数据库，再新增一条数据，此时这条数据的 ID 是几？
答：如果这张表的引擎是 MyISAM，那么 ID=4，如果是 InnoDB 那么 ID=2（MySQL 8 之前的版本）。

8.什么是独立表空间和共享表空间？它们的区别是什么？
答：共享表空间指的是数据库的所有表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在 data 目录下。

独立表空间：每一个表都将会生成以独立的文件方式来进行存储。

共享表空间和独立表空间最大的区别是如果把表放再共享表空间，即使表删除了空间也不会删除，因此表依然很大，而独立表空间如果删除表就会清除空间。

9.清空表的所有数据性能最好的语句是？
A：delete from t
B：delete t
C：drop table t
D：truncate table t

答：D

题目解析：truncate 清除表数据不会写日志，delete 要写日志，因此 truncate 的效率要高于 delete。

10.唯一索引和普通索引哪个性能更好？
答：唯一索引和普通索引的性能对比分为以下两种情况：

对于查询来说两者都是从索引数进行查询，性能几乎没有任何区别；
对于更新操作来说，因为主键索引需要先将数据读取到内存，然后需要判断是否有冲突，因此比唯一索引要多了判断操作，从而性能就比普通索引性能要低。
11.left join 和 right join 的区别是什么？
答：left join 和 right join 的区别如下：

left join（左联结），返回左表全部记录和右表联结字段相等的记录；
right join（右联结），返回右表全部记录和左表联结字段相等的记录。
12.什么是最左匹配原则？它的生效原则有哪些？
答：最左匹配原则也叫最左前缀原则，是 MySQL 中的一个重要原则，指的是索引以最左边为起点任何连续的索引都能匹配上，当遇到范围查询（>、<、between、like）就会停止匹配。 生效原则来看以下示例，比如表中有一个联合索引字段 index(a,b,c)：

where a=1 只使用了索引 a；
where a=1 and b=2 只使用了索引 a,b；
where a=1 and b=2 and c=3 使用a,b,c；
where b=1 or where c=1 不使用索引；
where a=1 and c=3 只使用了索引 a；
where a=3 and b like ‘xx%’ and c=3 只使用了索引 a,b。
13.以下 or 查询有什么问题吗？该如何优化？
select * from t where num=10 or num=20;

答：如果使用 or 查询会使 MySQL 放弃索引而全表扫描，可以改为：

select * from t where num=10

union

select * from t where num=20;

14.事务是什么？它有什么特性？
答：事务是一系列的数据库操作，是数据库应用的基本单位。

在 MySQL 中只有 InnDB 引擎支持事务，它的四个特性如下：

原子性（Atomic），要么全部执行，要么全部不执行；
一致性（Consistency），事务的执行使得数据库从一种正确状态转化为另一种正确状态；
隔离性（Isolation），在事务正确提交之前，不允许把该事务对数据的任何改变提供给其他事务；
持久性（Durability），事务提交后，其结果永久保存在数据库中。
15.MySQL 中有几种事务隔离级别？分别是什么？
答：MySQL 中有四种事务隔离级别，分别是：

read uncommited，未提交读，读到未提交数据；
read committed，读已提交，也叫不可重复读，两次读取到的数据不一致；
repetable read，可重复读；
serializable，串行化，读写数据都会锁住整张表，数据操作不会出错，但并发性能极低，开发中很少用到。
MySQL 默认使用 repetable read 的事务隔离级别。

16.如何设置 MySQL 的事务隔离级别？
答：MySQL 事务隔离级别 mysql.cnf 文件里设置的（默认目录 /etc/my.cnf），在文件的文末添加配置：

transaction-isolation = REPEATABLE-READ

可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。

17.MySQL 出现了中文乱码该如何解决？
答：解决 MySQL 中文乱码的问题，可以设置全局编码或设置某个数据库或表的编码为 utf8。 设置全局编码：

set character_set_client=‘utf8’;
set character_set_connection=‘utf8’;
set character_set_results=‘utf8’;
设置数据库的编码：

alter database db character set utf8;
设置表的编码：

alter table t character set utf8;
18.InnoDB 为什么要使用 B+ 树，而不是 B 树、Hash、红黑树或二叉树？
答：因为 B 树、Hash、红黑树或二叉树存在以下问题。

B 树：不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低。
Hash：虽然可以快速定位，但是没有顺序，IO 复杂度高。
二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且 IO 代价高。
红黑树：树的高度随着数据量增加而增加，IO 代价高。
19.MySQL 是如何处理死锁？
答：MySQL 对待死锁常见的两种策略：

通过 innodb_lock_wait_timeout 来设置超时时间，一直等待直到超时；
发起死锁检测，发现死锁之后，主动回滚死锁中的某一个事务，让其他事务继续执行。
20.什么是全局锁？它的应用场景有哪些？
答：全局锁就是对整个数据库实例加锁，它的典型使用场景就是做全款逻辑备份，这个时候整个库会处于完全的只读状态。

21.使用全局锁会导致什么问题？
答：使用全局锁会使整个系统不能执行更新操作，所有的更新业务会出于等待状态；如果你是在从库进行备份，则会导致主从同步严重延迟。

22.InnoDB 存储引擎有几种锁算法？
答：InnoDB 的锁算法包括以下三种：

Record Lock — 单个行记录上的锁；
Gap Lock — 间隙锁，锁定一个范围，不包括记录本身；
Next-Key Lock — 锁定一个范围，包括记录本身。
23.InnoDB 如何实现行锁？
答：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则 InnoDB 将使用表锁。使用 for update 来实现行锁，具体脚本如下：

select * from t where id=1 for update

其中 id 字段必须有索引。

24.MySQL 性能指标都有哪些？如何得到这些指标？
答：MySQL 最重要的性能指标有以下两个：

QPS（Queries Per Second），每秒查询数，一台数据库每秒能够处理的查询次数；
TPS（Transactions Per Second），每秒处理事务数。
这些性能指标可以通过 show status 来查询当前数据库状态的结果信息中估算出来，show status 会有 300 多条状态信息记录，其中以下这些信息 QPS 和 TPS 有关系：

Uptime，服务器已经运行的时间，单位秒；
Questions，已经发送给数据库查询数；
Com_select，查询次数，实际查询次数；
Com_insert，插入次数；
Com_delete，删除次数；
Com_update，更新次数；
Com_commit，事务次数；
Com_rollback，回滚次数。
25.MySQL 中的重要日志分为哪几个？
① 错误日志：用来记录 MySQL 服务器运行过程中的错误信息，比如，无法加载 MySQL 数据库的数据文件，或权限不正确等都会被记录在此，还有复制环境下，从服务器进程的信息也会被记录进错误日志。默认情况下，错误日志是开启的，且无法被禁止。默认情况下，错误日志是存储在数据库的数据文件目录中，名称为 hostname.err，其中 hostname 为服务器主机名。在 MySQL 5.5.7 之前，数据库管理员可以删除很长时间之前的错误日志，以节省服务器上的硬盘空间， MySQL 5.5.7 之后，服务器将关闭此项功能，只能使用重命名原来的错误日志文件，手动冲洗日志创建一个新的，命令为：

mv hostname.err hostname.err.old

mysqladmin flush-logs

② 查询日志：查询日志在 MySQL 中被称为 general log（通用日志），查询日志里的内容不要被“查询日志”误导，认为里面只存储 select 语句，其实不然，查询日志里面记录了数据库执行的所有命令，不管语句是否正确，都会被记录，具体原因如下:

insert 查询为了避免数据冲突，如果此前插入过数据，则当前插入的数据如果跟主键或唯一键的数据重复那肯定会报错；
update 时也会查询因为更新的时候很可能会更新某一块数据；
delete 查询，只删除符合条件的数据；
因此都会产生日志，在并发操作非常多的场景下，查询信息会非常多，那么如果都记录下来会导致 IO 非常大，影响 MySQL 性能。因此如果不是在调试环境下，是不建议开启查询日志功能的。

查询日志的开启有助于帮助我们分析哪些语句执行密集，执行密集的 select 语句对应的数据是否能够被缓存，同时也可以帮助我们分析问题，因此，可以根据自己的实际情况来决定是否开启查询日志。

查询日志模式是关闭的，可以通过以下命令开启查询日志：

set global general_log=1

set global log_output=‘table’;

general_log=1 为开启查询日志，0 为关闭查询日志，这个设置命令即时生效，不用重启 MySQL 服务器。

③ 慢日志：慢查询会导致 CPU、IOPS、内存消耗过高，当数据库遇到性能瓶颈时，大部分时间都是由于慢查询导致的。开启慢查询日志，可以让 MySQL 记录下查询超过指定时间的语句，之后运维人员通过定位分析，能够很好的优化数据库性能。默认情况下，慢查询日志是不开启的，只有手动开启了，慢查询才会被记录到慢查询日志中。使用如下命令记录当前数据库的慢查询语句：

set global slow_query_log=‘ON’;

使用 set global slow_query_log=‘ON’ 开启慢查询日志，只是对当前数据库有效，如果 MySQL 数据库重启后就会失效。因此如果要永久生效，就要修改配置文件 my.cnf，设置 slow_query_log=1 并重启 MySQL 服务器。

④ redo log（重做日志）：为了最大程度的避免数据写入时，因为 IO 瓶颈造成的性能问题，MySQL 采用了这样一种缓存机制，先将数据写入内存中，再批量把内存中的数据统一刷回磁盘。为了避免将数据刷回磁盘过程中，因为掉电或系统故障带来的数据丢失问题，InnoDB 采用 redo log 来解决此问题。

⑤ undo log（回滚日志）：用于存储日志被修改前的值，从而保证如果修改出现异常，可以使用 undo log 日志来实现回滚操作。

undo log 和 redo log 记录物理日志不一样，它是逻辑日志，可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录，当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。undo log 默认存放在共享表空间中，在 ySQL 5.6 中，undo log 的存放位置还可以通过变量 innodb_undo_directory 来自定义存放目录，默认值为“.”表示 datadir 目录。

⑥ bin log（二进制日志）：是一个二进制文件，主要记录所有数据库表结构变更，比如，CREATE、ALTER TABLE 等，以及表数据修改，比如，INSERT、UPDATE、DELETE 的所有操作，bin log 中记录了对 MySQL 数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其他额外信息，但是它不记录 SELECT、SHOW 等那些不修改数据的 SQL 语句。 binlog 的作用如下：

恢复（recovery）：某些数据的恢复需要二进制日志。比如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行 point-in-time 的恢复；
复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的 MySQL 数据库（一般称为 slave 或者 standby）与一台 MySQL 数据库（一般称为 master 或者 primary）进行实时同步；
审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。
除了上面介绍的几个作用外，binlog 对于事务存储引擎的崩溃恢复也有非常重要的作用，在开启 binlog 的情况下，为了保证 binlog 与 redo 的一致性，MySQL 将采用事务的两阶段提交协议。当 MySQL 系统发生崩溃时，事务在存储引擎内部的状态可能为 prepared（准备状态）和 commit（提交状态）两种，对于 prepared 状态的事务，是进行提交操作还是进行回滚操作，这时需要参考 binlog，如果事务在 binlog 中存在，那么将其提交；如果不在 binlog 中存在，那么将其回滚，这样就保证了数据在主库和从库之间的一致性。

binlog 默认是关闭状态，可以在 MySQL 配置文件（my.cnf）中通过配置参数 log-bin = [base-name] 开启记录 binlog 日志，如果不指定 base-name，则默认二进制日志文件名为主机名，并以自增的数字作为后缀，比如：mysql-bin.000001，所在目录为数据库所在目录（datadir）。

通过以下命令来查询 binlog 是否开启：

show variables like ‘log_%’;

binlog 格式分为 STATEMENT、ROW 和 MIXED 三种。

STATEMENT 格式的 binlog 记录的是数据库上执行的原生 SQL 语句。这种格式的优点是简单，简单地记录和执行这些语句，能够让主备保持同步，在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。另一个好处是二进制日志里的时间更加紧凑，因此相对而言，基于语句的复制模式不会使用太多带宽，同时也节约磁盘空间，并且通过 mysqlbinlog 工具容易读懂其中的内容。缺点就是同一条 SQL 在主库和从库上执行的时间可能稍微或很大不相同，因此在传输的二进制日志中，除了查询语句，还包括了一些元数据信息，如当前的时间戳。即便如此，还存在着一些无法被正确复制的 SQL，比如，使用 INSERT INTO TB1 VALUE(CUURENT_DATE()) 这一条使用函数的语句插入的数据复制到当前从服务器上来就会发生变化，存储过程和触发器在使用基于语句的复制模式时也可能存在问题；另外一个问题就是基于语句的复制必须是串行化的，比如，InnoDB 的 next-key 锁等，并不是所有的存储引擎都支持基于语句的复制。
ROW 格式是从 MySQL 5.1 开始支持基于行的复制，也就是基于数据的复制，基于行的更改。这种方式会将实际数据记录在二进制日志中，它有其自身的一些优点和缺点，最大的好处是可以正确地复制每一行数据，一些语句可以被更加有效地复制，另外就是几乎没有基于行的复制模式无法处理的场景，对于所有的 SQL 构造、触发器、存储过程等都能正确执行；它的缺点就是二进制日志可能会很大，而且不直观，因此，你不能使用 mysqlbinlog 来查看二进制日志，也无法通过看二进制日志判断当前执行到那一条 SQL 语句。现在对于 ROW 格式的二进制日志基本是标配了，主要是因为它的优势远远大于缺点，并且由于 ROW 格式记录行数据，因此可以基于这种模式做一些 DBA 工具，比如数据恢复，不同数据库之间数据同步等。
MIXED 也是 MySQL 默认使用的二进制日志记录方式，但 MIXED 格式默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。比如用到 UUID()、USER()、CURRENT_USER()、ROW_COUNT() 等无法确定的函数。
26.redo log 和 binlog 有什么区别？
redo log（重做日志）和 binlog（归档日志）都是 MySQL 的重要的日志，它们的区别如下：

redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；
binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”；
redo log 是 InnoDB 引擎特有的，binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用；
redo log 是循环写的，空间固定会用完，binlog 是可以追加写入的，“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
最开始 MySQL 里并没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，因此 InnoDB 使用另外一套日志系统，也就是 redo log 来实现 crash-safe 能力。

27.慢查询日志的获取方式有哪些？
答：慢查询日志的常见获取方式如下。

使用 MySQL 自带功能，开启慢查询日志，在 MySQL 的安装目录下找到 my.cnf 文件设置 slow-query-log=On 开启慢查询，慢查询默认时长为 10s，默认存储文件名为 host_name-slow.log。
使用三方开源方案 zabbix，zabbix 是一个基于 Web 界面的提供分布式系统监视以及网络监视功能的企业级的开源解决方案，能监视各种网络参数，保证服务器系统的安全运营；并提供灵活的通知机制以让系统管理员快速定位/解决存在的各种问题。
28.如何定位慢查询？
答：使用 MySQL 中的 explain 分析执行语句，比如：

explain select * from t where id=5;

如下图所示：

其中：

id — 选择标识符，id 越大优先级越高，越先被执行
select_type — 表示查询的类型。
table — 输出结果集的表
partitions — 匹配的分区
type — 表示表的连接类型
possible_keys — 表示查询时，可能使用的索引
key — 表示实际使用的索引
key_len — 索引字段的长度
ref— 列与索引的比较
rows — 大概估算的行数
filtered — 按表条件过滤的行百分比
Extra — 执行情况的描述和说明
其中最重要的就是 type 字段，type 值类型如下：

all — 扫描全表数据
index — 遍历索引
range — 索引范围查找
index_subquery — 在子查询中使用 ref
unique_subquery — 在子查询中使用 eq_ref
ref_or_null — 对 null 进行索引的优化的 ref
fulltext — 使用全文索引
ref — 使用非唯一索引查找数据
eq_ref — 在 join 查询中使用主键或唯一索引关联
const — 将一个主键放置到 where 后面作为条件查询， MySQL 优化器就能把这次查询优化转化为一个常量，如何转化以及何时转化，这个取决于优化器，这个比 eq_ref 效率高一点
29.MySQL 中常见的读写分离方案有哪些？
答：MySQL 中常见的读写分离方案通常为以下两种：

使用 MySQL 官方提供的数据库代理产品 MySql ProxySQL 搭建自动分配的数据库读写分离环境；
在程序层面配置多数据源使用代码实现读写分离。
30.怎样保证主备数据库无延迟？
答：通常保证主备数据库无延迟有以下三种方法。

每次从库执行查询请求前，先判断 seconds_behind_master 是否已经等于 0。如果还不等于 0 ，那就必须等到这个参数变为 0 才能执行查询请求，seconds_behind_master 参数是用来衡量主备延迟时间的长短。
对比位点确保主备无延迟。Master_Log_File 和 Read_Master_Log_Pos，表示的是读到的主库的最新位点，Relay_Master_Log_File 和 Exec_Master_Log_Pos，表示的是备库执行的最新位点。
对比 GTID 集合确保主备无延迟。Auto_Position=1 ，表示这对主备关系使用了 GTID 协议。Retrieved_Gtid_Set，是备库收到的所有日志的 GTID 集合；Executed_Gtid_Set，是备库所有已经执行完成的 GTID 集合。
31.什么是 MySQL 多实例，如何配置 MySQL 多实例？
答：MySQL 多实例就是在同一台服务器上启用多个 MySQL 服务，它们监听不同的端口，运行多个服务进程，它们相互独立，互不影响的对外提供服务，便于节约服务器资源与后期架构扩展。 多实例的配置方法有两种：

一个实例一个配置文件，不同端口；
同一配置文件（my.cnf）下配置不同实例，基于 MySQL 的 d_multi 工具。
32.表的优化策略有哪些？
「参考答案」常见的大表优化策略如下。

读写分离，主库负责写，从库负责读。
垂直分区，根据数据属性单独拆表甚至单独拆库。
水平分区，保持表结构不变，根据策略存储数据分片，这样每一片数据被分散到不同的表或者库中。水平拆分只是解决了单一表数据过大的问题，表数据还在同一台机器上，对于并发能力没有什么意义，因此水平拆分最好分库。另外分片事务难以解决，跨节点 join 性能较差。
33.数据库分片方案有哪些？
「参考答案」数据库分片方案有哪些？ 答：数据库创建的分片方案有两种方式：客户端代理方式和中间件代理方式。

客户端代理 — 分片逻辑在应用端，封装在 jar 包中，通过修改或者封装 JDBC 层来实现，比如 Sharding-JDBC、阿里 TDDL 等。
中间件代理 — 在应用层和数据层中间加了一个代理层。分片逻辑统一维护在中间件服务中，比如 MyCat、网易的 DDB 都是中间件代理的典型代表。
34.查询语句的优化方案有哪些？
「参考答案」常见优化方案如下：

不做列运算，把计算都放入各个业务系统实现；
查询语句尽可能简单，大语句拆小语句，减少锁时间；
不使用 select * 查询；
or 查询改写成 in 查询；
不用函数和触发器；
避免 %xx 查询；
少用 join 查询；
使用同类型比较，比如 ‘123’ 和 ‘123’、123 和 123；
尽量避免在 where 子句中使用 != 或者 <> 操作符，查询引用会放弃索引而进行全表扫描；
列表数据使用分页查询，每页数据量不要太大。
35.MySQL 毫无规律的异常重启，可能产生的原因是什么？该如何解决？
「参考答案」可能是积累的长连接导致内存占用太多，被系统强行杀掉导致的异常重启，因为在 MySQL 中长连接在执行过程中使用的临时内存对象，只有在连接断开的时候才会释放，这就会导致内存不断飙升，解决方案如下：

定期断开空闲的长连接；
如果是用的是 MySQL 5.7 以上的版本，可以定期执行 mysql_reset_connection 重新初始化连接资源，这个过程会释放之前使用的内存资源，恢复到连接刚初始化的状态。