夯实基础系列三：数据库知识总结

前言 对于后端开发人员来说，经常会和数据打交道，今天总结下数据库相关的知识。包括MySQL，JDBC基础，JDBC进阶，MongoDB，性能优化。以下对这些内容做一些简单的总结，同时我也有完整的思维导图，博客上不方便展示，若有需要，请关注微

前言

对于后端开发人员来说，经常会和数据打交道，今天总结下数据库相关的知识。包括MySQL，JDBC基础，JDBC进阶，MongoDB，性能优化。以下对这些内容做一些简单的总结，同时我也有完整的思维导图，博客上不方便展示，若有需要，请关注微信公众号蜗牛互联网，后台回复 数据库 即可获取。

细节

1. MySQL

1.1 数据库的概述

• DBMS
• 解释：关系型数据库管理系统
• 库管+N个仓库
• 常见的数据库管理系统
• MySQL
• Orcale
• DB2
• SQL Server
• Sybase

1.2 安装MySQL

• 目录结构
• mysql.exe：客户端
• mysqld.exe：服务器
• my.ini：总配置文件
• 在windows下开户和关闭mysql服务器
• net start mysql
• net stop mysql
• 登录和退出mysql客户端
• mysql -u用户名 -p密码 -hIP
• exit或quit

1.3 SQL

• 概述
• 解释：结构化查询语言
• ISO：国际标准化组织
• ISO 定义了很多 SQL 标准，例如：SQL99
• 各个数据库厂商需要遵循这个标签！但各个厂商也有自己的方言
• SQL的分类
• DDL：数据定义语言
• DML：数据操作语言
• DCL：数据控制语言
• DQL（不在标准中）：数据查询语言

1.4 DDL

• 数据库的操作
• create database
• show databases
• use mydb1
• alter database
• drop database
• 表的操作
• create table
• show tables
• desc mytable1
• drop table
• alter table
• add
• modify
• change
• drop
• rename to

1.5 DML

• 插入记录
• 修改记录
• 删除记录
• delete
• truncat

1.6 DCL

• 创建用户
• 授权
• 撤消权限
• 查看权限
• 删除用户

1.7 DQL

• 基本查询
• 查看整张表
• select * from 表名
• 列控制
• 列运算：select sal * 1.5 from emp
• 列运算：连接字符串：select concat(‘我的名称是’, ename) from emp
• 处理 NULL 值：select sal + IFNULL(comm, 0) from emp
• select ename 姓名 from emp
• 去除完全重复的行：select distinct comm from emp
• 条件查询
• 模糊查询
• 下划线
• 百分号
• 排序
• desc
• asc
• 聚合函数
• count
• min
• max
• sum
• avg
• 分组
• group by
• having
• limit

1.8 约束

• 约束保证数据的完整性和一致性
• 约束分为表级约束和列级约束
• 对一个数据列建立的约束，列级约束
• 对多个数据列建立的约束，表级约束
• 约束类型包括：
• NOT NULL
• PRIMARY KEY
• UNIQUE KEY
• DEFAULT
• FOREIGN KEY

1.9 中文无法插入解决办法

• dos 下不支持直接使用 utf8,set names gbk，再插入即可。

1.10 连接类型

• 内连接
• 显示左表及右表符合连接条件的记录
• 左外连接
• 显示左表的全部记录及右表符合连接条件的记录
• 右外连接
• 显示右表的全部记录及左表符合连接条件的记录
• 自身连接
• 同一个数据表对其自身进行连接

2. JDBC

2.1 JDBC 的原理

• 是由 JavaEE 提供的连接数据库的规范
• 需要由各大数据库的厂商提供对 JDBC 的实现类

2.2 四大核心类

• DriverManager
• getConnection
• Connection
• createStatement
• prepareStatement(String sql)
• Statement

1.executeUpdate()–>增、删、改

2.executeQuery()–>查

3.addBatch(String sql)

4.executeBatch()

5.execute(String sql)

• ResultSet
• getXxx()系统方法
• 移动光标系统方法

2.3 四大参数

• driverClassName
• url
• username
• password

2.4 预编译语句集

• 防SQL攻击
• 可读性提高了
• 效率高

2.5 DAO模式

• 面向接口编程
• DAO接口
• DAO实现，可以提供多个实现
• DAO工厂
• DAO配置文件：提供实现类名称

2.6 时间类型的转换

• 领域对象中的日期时间，必须为 util 的Date
• 在 JDBC 中使用的都是 sql 的 Date
• insert、update、delete，需要把领域对象中的 util 的 Date 转换成 sql 的 Date
• 在 select 时，需要把 sql 的 Date 赋给领域对象的 util 的 Date 类型的属性，这不需要处理

2.7 大数据存取

• 把文件转换成 Blob 类型
• 把 Blob 类型转换成文件

2.8 批处理

• 添加批
• 执行批

3. JDBC_进阶

3.1 事务

• ACID
• 原子性
• 一致性
• 隔离性
• 持久性
• mysql 中开启和关闭事务
• 开启事务：START TRANSACTION
• 结束事务
• 提交事务：COMMIT
• 回滚事务：ROLLBACK
• JDBC 中开启和关闭事务
• 开启事务：connection.setAutoCommit(false)
• 结束事务
• connection.commit()
• connection.rollback()
• 格式：

con.setAutoCommit(false);//开始事务

…

con.commit();//提交事务

} catch(…) {

con.rollback();//回滚事务

}

3.2 三种并发读问题

• 脏读
• 读到未提交
• 不可重复读
• 两次读取不一致，读取到另一事务修改的记录
• 幻读
• 两次读取不一致，读取到另一事务插入的记录

3.3 四种隔离级别

• 串行化
• 可重复读
• 防止了脏读、不可重复读 MySQL
• 读已提交
• 防止了脏读 Oracle
• 读未提交

3.4 连接池

• 必须实现 javax.sql.DataSource
• DBCP
• C3P0

3.5 JNDI

• 在 tomcat 的 conf/catalina/localhost 下创建 xml 文件
• 配置元素
• 在中配置元素
• 在代码中使得 Context 类的 lookup 方法来获取资源
• java:comp/env/ 资源名称

3.6 ThreadLocal

• 内部有一个 Map
• key 是当前线程

4. MongoDB

4.1 MongoDB 安装

• 在 github 上获取源码包 mongo-r2.6.5.zip
• 解压缩

unzip mongo-r2.6.5.zip

• 编译源码文件

cd mongo-r2.6.5

scons all -j 12 //12 为 CPU 核数，用来加速编译过程

若未安装 scons，使用命令 sudo apt-get install scons

4.2 编译后的文件

• mongod:mongodb 的启动文件，用来部署数据库
• mongo：链接服务器的客户端
• mongoimport,,mongoexport:用来导入导出数据库
• mongodump,mongorestore:用来导入导出二进制数据，一般用来数据的备份与恢复。
• mongooplog 操作日志
• mongostat:用来查看 mongodb 服务器的各种状态。

4.3 MongoDB 搭建

• 建立相关目录
• data (用于存放数据文件)
• log（用于存放日志文件）
• conf（用于存放数据库配置文件 mongod.conf）
• bin（用于存放数据库可执行文件 mongod）
• 编辑配置文件

port = 12345 /监听端口/

dbpath = data /指定数据文件/

logpath = log/mongod.log /指定日志文件/

fork = ture /linux后台运行标志，window无效/

}

• 运行程序

mongod -f conf/mongod.conf /运行mongod 加载指定配置文件/

• 查看运行状态

tail mongod.log /查看日志/

4.4 MongoDB 连接

4.5 数据库使用

4.6 数据更新 update

4.7 MongoDB 方法API

4.8 命令小结

5. 性能优化

5.1 MySQL 性能优化

• 表的设计合理化(符合3NF)
• 1NF 是对属性的原子性约束，要求属性(列)具有原子性，不可再分解；(只要是关系型数据库都满足1NF)
• 2NF 是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；
• 3NF 是对字段冗余性的约束，它要求字段没有冗余。 没有冗余的数据库设计可以做到。
• 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引 unique、全文索引]
• 较频繁的作为查询条件字段应该创建索引
• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
• 更新非常频繁的字段不适合创建索引
• 不会出现在 WHERE 子句中的字段不该创建索引
• 分表技术(水平分割、垂直分割)
• 读写[写: update/delete/add]分离
• 存储过程 [模块化编程，可以提高速度]
• 对 mysql 配置优化 [配置最大并发数 my.ini, 调整缓存大小 ]
• mysql 服务器硬件升级
• 定时的去清除不需要的数据,定时进行碎片整理(MyISAM)

5.2 SQL语句优化

• 通过 show status 命令了解各种 SQL 的执行频率。
• 定位执行效率较低的 SQL 语句-（重点 select）
• 通过 explain 分析低效率的 SQL
• 确定问题并采取相应的优化措施

5.3 索引

• 索引的类型
• 主键索引，主键自动的为主索引 (类型 Primary)
• 唯一索引 (UNIQUE)
• 普通索引 (INDEX)
• 全文索引 (FULLTEXT) [适用于MyISAM] ——》sphinx + 中文分词 coreseek [sphinx 的中文版 ]
• 综合使用=>复合索引
• 可能使用到索引
• 对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。
• 对于使用like的查询，查询如果是 ‘%aaa’ 不会使用到索引， ‘aaa%’ 会使用到索引。
• 不使用索引
• 如果条件中有 or，即使其中有条件带索引也不会使用。
• 对于多列索引，不是使用的第一部分，则不会使用索引。
• like 查询是以%开头
• 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须”)
• 如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

我组建了一个技术交流群，提供免费的每日科技早报服务，里边也会有众多知名互联网企业的技术大佬一起交流学习，共同成长。

动手验证

# 看进程各线程的内存 / CPU 占用
top -H -p <pid>

# 某个端口被谁占着
lsof -i :8080

# 抓一次 HTTP 请求内容
sudo tcpdump -A -i any 'tcp port 8080' -c 50

延伸阅读：官方与权威资料

• Oracle Java SE 官方文档
• Baeldung Java 教程
• Spring 官方参考文档
• Java 官方教程（The Java Tutorials）

相关阅读（站内）

• Java G1 GC 技术一览
• Java 面向对象一览
• 【Java基础教程02】JDK版本怎

常见问题