数据库

概述

• 数据库是一堆数据的集合
• 数据库有若干张数据表，每个表中有一些数据
• 数据库是一个存放所有数据的最大单位
• 一个数据库 DB 能够存多少数据 => 上亿条数据可能才会影响到查询速度

数据库优点

• 数据库提供结构化数据的持久化存储
• 索引保证数据查询的速度
• 事务的原子性保证数据不丢失

事务

事务是数据库管理系统执行过程中的一个逻辑单元，由一个有限的数据库操作序列构成。具有 ACID 性质

ACID

• Atomicity => 原子性 -> 事务作为一个整体被执行，包含在其中的对数据库的操作要么全部执行，要么都不执行
• Consistency => 一致性 -> 事务应确保数据库的状态从一个一致状态转变为另一个一致状态 => 一致状态的含义是数据库中的数据应满足完整性约束
• Isolation => 隔离性 -> 多个事务并发执行时，一个事务的执行不应影响其他事务的执行
• Durability => 持久性 -> 已被提交的事务对数据库的修改应该永久保存在数据库中

数据类型

• 整数类型 => int | bigint
• 字符串类型 => varchar(100) | TEXT
• 时间类型 => timestamp

数据结构

数据库数据结构一般为 B+ 树 | B 树

在 B 树中，每一个节点可以有若干个子节点，当数据插入的时候，这些节点会动态的变化，它是一个多叉树

B+ 树更充分的利用了节点的空间，更加稳定 => 引入了从子节点到下一个兄弟节点的指针 => 参考

优点

1. 多叉树，每一个节点包含多个记录 => 树的高度特别低 => 使得找到一个数据只需要很少的查找次数
2. 所有的节点都包括范围 => 可以迅速找到头节点和尾节点

索引

在数据之外，MySQL 会维护一个以 Primary Key 为主索引的 B+ 树。如果创建了新的索引，那么将会维护另一个以新的索引的 B+ 树，其中每一个记录都指向了对应的 Primary Key。之后根据新的索引去查找数据，首先执行新的索引，找到后，查到指向的 Primary Key，之后去 Primary Key 索引的 B+ 树上查找，拿到真正的数据，真正的数据可能非常长，索引是很少的一部分数据

• 索引可以大大提高查询的效率，几百倍到几千倍，取决于具体的 SQL 以及具体分析
• 索引可以使得千万级别的数据查找起来非常地快，千万级别的数据也可以达到毫秒级的速度
• 索引对范围查询的效率是很低的
• 使用索引的第二次及以后的查询会快很多 => 在数据库中有多级的缓存 => 第一次启动称为冷启动，要去查真正的数据并且因为索引是有重复的，所以效率稍微有一点低 => 再次查询时，缓存开始生效，这个过程称为预热(Warm Up)，速度会更快
• 基于值等于操作
• MySQL 的长处在于非文本的索引，对于文本类的搜索，MySQL 力不从心

联合索引

CREATE INDEX <index_name> ON table_name (column1, column2, ...);

• (a, b) 索引 => 联合索引 => 建立两个索引 => a 索引 + (a, b) 索引
• (a, b, c) 索引 => 建立三个索引 => a 索引 + (a, b) 索引 + (a, b, c) 索引

举例

(a, b, c, d) 索引
select * from xx where a = 1 and b = 2 and c > 3 and d = 4;
c > 3 是范围查找，有了范围索引之后，意味着索引用处不大了，只能找到索引，之后把后面的记录都捞出来。查询优化器看到范围索引，就到此为止，将前面的条件试图使用一个联合索引的方式。遇到 c > 3 使用 (a, b) 联合索引会比较快，d = 4 是用不到索引的

上述 d = 4 为啥用不到索引？
答：上述等价于 select * from xx where a = 1 and b = 2 and d = 4 and c > 3，在 (a, b, c, d) 索引内是没有 (a, b, d)
联合索引的。此时可以根据具体业务创建 (a, b, d, c) 联合索引

索引优化原则

• 最左前缀匹配
• 选择数据不重复的列作为索引
• 使用联合索引代替新建索引 => 有了 a 索引，不要新建 b 索引，而是创建 (a, b) 联合索引

最左前缀匹配

在 MySQL 建立联合索引是会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配

倒排索引 => Elasticsearch

传统的关系型数据库按照 id 搜索很快 => 建立了以 id 为索引的 B+ 树 => 相等操作 => 文本是 contain 操作 => B+ 树不适用于文本搜索场景

数据库表设计原则

数据库别名 => alias => 关系型数据库 => 表不仅存储实体的数据，还存储这些实体间的关系 => 例如：订单表维护了用户和商品之间的关系

• 每个实体一张表 => 用户 | 商品
• 每个实体都有一个主键(唯一的整数)id
• 按照业务需要去建立索引
• 每个关系用一张表联系

SQL

• create table => 建表
• drop table => 删除表
• alter table => 修改表

基本 SQL

• insert into => insert into table_name (column1, column2, column3, ...) values (value1, value2, value3, ...)
• delete from => delete from table_name where some_column=some_value
• update => update table_name set column1=value1, column2=value2, ... where some_column=some_value
• select => select * from table_name where id = 1

SQL 注意事项

• insert | delete | update 修改数据后需要 commit 操作
• SQL 语句不区分大小写
• 命名风格是 snake_case 分割两个单词
• 数据库中的字符串使用单引号
• 数据库的注释是 --
• 在执行 update | delete 操作时确保有 where
• 分号分割多个 SQL 语句
• 数据库中钱💰的类型 => decimal | int(金额 * 100，将单位变成分)

select 基本语句

• select * | select count(*) | select count(1) | select id, name from user where address='shanghai'
• select max | select min | select avg
• select limit => 分页
• select order by => 排序
• select is null | select is not null
• select where id in ()

select 高级语句

• 分页 => select * from user limit <从第几个元素开始查找>, <最多返回几个元素>
• 分组 => select address from user group by address
• 统计 => select address, count(*) from user group by address
• 别名 => select goods_id, count(*) as count from "ORDER" group by goods_id
• 去重 => select distinct user_id from "ORDER" where goods_id=1 => select count(distinct user_id) from "ORDER" where goods_id=1
• 乘法 & 求和 => select goods_id, sum(goods_price * goods_num) as sales from "ORDER" group by goods_id order by sales desc
• 子查询 => select count(*) from user where id in (select user_id from "ORDER" where goods_id=1)
• 合并表 => select "ORDER".id, "ORDER".goods_id, "ORDER".user_id, goods.name from "ORDER" join goods on "ORDER".goods_id=goods.id => join 合并两张表，on 按照一定的关系关联起来 => join 默认是 inner join 内连接
• select "ORDER".id, "ORDER".goods_id, "ORDER".user_id, goods.name, user.name from "ORDER" join goods on "ORDER".goods_id=goods.id join user on "ORDER".user_id=user.id

inner join vs left join vs right join

JDBC

• JDBC => Java Database Connection
• 提供了通用的可以使用 Java 去连接数据库的能力
• 本质：通过一个连接字符串，就可以进行读取数据库的信息(username + password)

使用 JDBC 连接数据库

• 连接串
  • H2 => jdbc:h2:[file:][<path>]<databaseName>
  • mysql => jdbc:mysql://192.168.0.10/数据库名?user=USR&password=PWD
• 用户名
• 密码

使用 JDBC 从数据库读取数据

注：先确保 SQL 语句正确

• java.sql.Statement => 一个 SQL 语句
• java.sql.PreparedStatement => 防止 SQL 注入 => 尽可能使用 PreparedStatement

SQL 注入

通过把 SQL 命令插入到 Web 表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意代码的 SQL 命令

// Statement
Statement statement = connect.Statement();
String sql = "select * from user where name = '" + username + "' and password = '" + password + "'";
statement.executeQuery(sql);

// PreparedStatement
PreparedStatement statement = connection.prepareStatement("select * from user where name = ? and password = ?");
statement.setString(1, username);
statement.setString(2, password);
statement.executeQuery();

使用 Statement 会在 execute 的时候将 SQL 语句解析为语法树(AST)，但是 PrepareStatement 会预先编译 SQL 语句，预先解析 SQL 语句，传入的字符串，不再解析为新的语法树(AST)，只是替换原有 SQL 语句中的 ?

MyBatis

• MyBatis => 一个 ORM 框架，简单方便，工作在 JVM 中的一小段程序，通过调用底层的 JDBC 操作，和数据库发生交互
• ibatis == mybatis => 同一种东西，不同版本
  • ibatis => version 2
  • mybatis => version 3+
• ORM => Object Relationship Mapping => 对象关系映射 => 自动完成 Java 对象到数据库的映射
• Java Object <==> MyBatis <==> Database
• 开发者只需要和 MyBatis 交互即可
• MyBatis 通过注解 + 代理模式拿到数据库数据，之后通过反射构造相应的 Java 对象

configuration

• driver => 驱动 => MySQL | H2 => search key：h2 database driver class name
• url => 连接字符串
• username =>
• password =>

Mapper

• 由 MyBatis 动态代理实现的接口
• SQL 简单时很方便，SQL 复杂时不够方便 => <mapper class="${FQCN}"> + interface MyCustomMapper + Annotation(@select | @delete) + session.getMapper(<Class>)

<!-- config.xml -->
<mappers>
    <mapper class="org.mybatis.<FQCN>.MyCustomMapper"/>
</mappers>

// MyCustomMapper.java
public interface MyCustomMapper {
    @Delete("delete from USER where id = #{id}")
    void deleteUserById(Integer id);

    @Select("select * from User where id = #{id}")
    User selectUserById(Integer id);
}

// Main.java
public class Main {
    public User selectUserById(Integer id) {
        try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
            MyCustomMapper mapper = sqlSession.getMapper(MyCustomMapper.class);
            return mapper.selectUserById(id);
        }
    }
}

Configuration XML

使用 XML 编写复杂 SQL => 可以方便的使用 MyBatis 的强大功能 => SQL 和代码分离

<!-- config.xml -->
<mapper namespace="org.apache.ibatis.submitted.rounding.Mapper">
    <select id="selectUser" parameterType="int" resultType="map">
        select * from user
    </select>
</mapper>

parameterType

• 参数的 #{} 和 ${} => 优先使用 #{}

  <select id="selectUser" parameterType="int" resultType="map">
      select * from user where id=#{id} // 此处的 id 从传入的参数中寻找 => prepareStatement
      select * from user where name='${name}' // 此处 name 会直接替换，所以需要加 '' => 此时有SQL注入危险
  </select>
  

• 读参数是按照 Java Bean 读取的

resultType

• 写参数是按照 Java Bean 约定的

动态 SQL

• test 作比较时，常量需要使用 '' => <when test="name == 'testName'">
• <if>
• <choose>
• <foreach>
• <script>

Docker

Empowering App Development for Developers

Docker 安装数据库优点

• 百分百兼容
• 百分百无残留 => Docker 是一个隔离的容器环境，它和宿主机没有任何的关系
• 百分百统一、方便

Docker SQL

// 启动一个 MySQL 实例
docker run --name some-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag

// 启动一个 PostgreSQL 实例
docker run --name postgres -p 5432:5432 -e POSEGRES_PASSWORD=123 -d postgres:12

• --name => 容器名字
• -e => environment => 环境变量
• MYSQL_ROOT_PASSWORD => 指定 root 用户的密码
• tag => Docker 镜像的 tag，代表了镜像的版本，用来区分软件发布的不同版本 => 如果不指定 tag，则会拉取 latest 版本，latest 版本是会变的 => 保持环境的稳定和一致性 => 最佳实践：启动容器时永远指定版本
• -p => port => 可以将 docker 容器的端口映射到宿主机上的端口上
• -v => volume => 将 Docker 容器内的数据文件映射到宿主机上的一个文件
• MySQL 默认端口 => 3306
• postgres 默认端口 => 5432

docker rm -f <dockerName>

现在使用 docker 启动的数据库的数据是不持久化的 => 如果容器重启，那么数据库中数据的修改将会丢失 => 除非在启动容器的时候使用 -v 参数 => 将 Docker 容器内的数据文件映射到宿主机上的一个文件上

Example

1. 创建一个 data 文件夹，用于映射 MySQL 数据库文件

   mkdir mysql-data
   

2. 启动一个 MySQL 实例

   docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -v `pwd`/mysql-data:/var/lib/mysql -d mysql:8
   

   Docker MySQL 实例

3. 使用 IDEA JDBC 连接数据库

   
   Data Sources and Drivers
   • 此时 MySQL 中没有 Database，需要连接上之后手动创建
   • 可以点击 Test Connection 尝试连接
     Test Connection
4. 创建 Database => create database <database_name>
5. 使用 Flyway 进行相关数据的迁移

   <!-- pom.xml -->
   <plugin>
       <groupId>org.flywaydb</groupId>
       <artifactId>flyway-maven-plugin</artifactId>
       <version>7.13.0</version>
       <configuration>
           <url>jdbc:mysql://localhost:3306/mall?characterEncoding=utf-8</url>
           <user>root</user>
           <password>123456</password>
       </configuration>
       <executions>
           <execution>
               <id>init</id>
               <phase>initialize</phase>
               <goals>
                   <goal>migrate</goal>
               </goals>
           </execution>
       </executions>
   </plugin>
   
   <!--  Terminal => 需要有 migration https://flywaydb.org/documentation/getstarted/firststeps/maven#creating-the-first-migration  -->
   <!--  mvn flyway:migrate  -->
   

知识点

1. 主键最好不要用可能会修改的值
2. 外键 => 一个表的某个列是其他表的主键
3. 数据库是天然的线程安全的，不需要额外的同步
4. select 之后根据拿出来的结果进行判断是否要 delete => 不是原子操作
5. insert 是原子操作
6. MySQL 的 charset utf8mb4 是真正的 utf8
7. 当 SQL 运行特别慢的时候，通过一些性能分析，进行相应优化 => explain
8. Error => expected "identifier"; => SQL 语句有关键字
9. DDL => Data Definition Language => 数据定义语言
10. 删除
    • 物理删除 => 数据被从数据库中抹去了
    • 逻辑删除 => 数据还在数据库中，只是我们假装看不见而已