数据库

概述

  • 数据库是一堆数据的集合
  • 数据库有若干张数据表，每个表中有一些数据
  • 数据库是一个存放所有数据的最大单位
  • 一个数据库 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. 删除
    • 物理删除 => 数据被从数据库中抹去了
    • 逻辑删除 => 数据还在数据库中，只是我们假装看不见而已
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,732评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,496评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,264评论 0赞 338
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,807评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,806评论 5赞 368
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,675评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,029评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,683评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 41,704评论 1赞 299
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,666评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,773评论 1赞 332
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,413评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,016评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,978评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,204评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,083评论 2赞 350
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,503评论 2赞 343

推荐阅读更多精彩内容