MySQL

MySQL是关系型数据库管理系统（RDBMS）。

1 安装MySQL

1、下载

官网：https://dev.mysql.com/downloads/mysql/

图片说明

2、解压

图片说明

3、配置环境变量

path=MySQL文件夹路径\bin

4、新建my.ini

图片说明

[mysqld]
basedir=MySQL文件夹路径\
datadir=MySQL文件夹路径\data\
port=3306
skip-grant-tables

5、初次安装MySQL

打开CMD：

# 1、进入MySQL的bin目录
cd MySQL文件夹路径\bin    
# 2、安装MySQL
mysqld -install
# 3、初始化数据库文件
mysqld --initialize-insecure --user=mysql    
# 4、启动MySQL服务
net start mysql
# 5、进入MySQL管理界面
mysql -u root -p
# 6、更改root密码
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
# 7、刷新权限
flush privileges;

修改my.ini，删除（或注释）跳过密码：
```
#skip-grant-tables
```
打开CMD，重启MySQL服务：

net stop mysql
net start mysql

6、登录、退出MySQL

# 方式一：
mysql -u root -p
# 方式二：
mysql -u root -p123456

退出：

exit

2 创建数据库、表

1、创建数据库

MySQL命令：

# 1、创建数据库
CREATE DATABASE `db_test`CHARACTER SET utf8 COLLATE utf8_general_ci; 
# 2、使用数据库
USE `db_test`;

SQLyog：

图片说明

2、创建表

MySQL命令：

CREATE TABLE `db_test`.`student` ( 
    `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT ***生id', 
    `name` VARCHAR(100) NOT NULL COMMENT ***生姓名',
    `age` INT(3) NOT NULL COMMENT ***生年龄', 
    PRIMARY KEY (`id`)
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;

SQLyog：

3 基础命令

1、登录、退出

-- 方式一：
mysql -u root -p
-- 方式二：
mysql -u root -p<密码>

退出：

exit;

2、修改用户密码

-- 1、修改密码
update mysql.user set authentication_string=password('<密码>') where user='<用户名>' and Host='localhost';
-- 2、刷新权限
flush privileges;

3、操作数据库

列出所有数据库：

show databases;

创建数据库：

create database [if not exists] <数据库名>;

使用数据库：

use <数据库名>;

删除数据库：

drop database [if exists] <数据库名>;

4、操作表

列出所有表：

show tables;

创建表：

CREATE TABLE `db_test`.`student` ( 
    `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT ***生id', 
    `name` VARCHAR(100) NOT NULL COMMENT ***生姓名', 
    `age` INT(3) NOT NULL COMMENT ***生年龄',
    PRIMARY KEY (`id`)
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 

CREATE TABLE [IF NOT EXISTS] <表名> (
    <字段名> <数据类型> [字段属性] [注释],
    <字段名> <数据类型> [字段属性] [注释],
    ……
    <字段名> <数据类型> [字段属性] [注释],
    [索引]
) [存储引擎] [字符]

修改表：

# 修改表名
alter table <表名> rename as <新表名>;
# 增加字段
alter table <表名> add <字段名> <数据类型> [字段属性] [注释];
# 修改字段类型、约束
alter table <表名> modify <字段名> <数据类型> [字段属性] [注释];
# 修改字段名
alter table <表名> change <字段名> <新字段名>;
# 删除字段
alter table <表名> drop <字段名>;

删除表：

drop table [if exists] <表名>;

显示表信息：

describe <表名>;        # 显示表的结构
show create table <表名>;    # 查看创建该表的语句

清空表数据：

truncate <表名>;

4 数据类型、字段属性、索引、外键

4.1 数据类型

1、数值型

- 整数

tinyint：1byte。
smallint：2byte。
mediumint：3byte。
int：4byte。
bigint：8byte。

- 浮点数

float：4byte。
double：8byte。

- 字符串浮点数

decimal：金融计算时使用。

2、字符串

char：固定大小，0~255。
varchar：可变大小，0~65535。
tinytext：2^8^ - 1。
text：2^16^ - 1。

3、时间、日期

date：YYYY-MM-DD。
time：HH:mm:ss。
datetime：YYYY-MM-DD HH:mm:ss。
timestamp：时间戳。
year：年。

4、空值

null。

4.2 字段属性

unsigned：无符号整数，该列不能为负数。
zerofill：不足的位数使用0来填充（0001）。
auto increment：自增。
null：可以为空。
not null：非空。
default：默认值。

3、索引

索引是帮助MySQL高效或数据的数据结构（B树或B+树）。

普通索引：没有限制。
唯一索引：要求数据唯一，不能重复。
主键索引：特殊的唯一索引，不允许为null。
全文索引：便于全文搜索。

# 方式一：建表时添加索引
CREATE TABLE [IF NOT EXISTS] <表名> (
    <字段名> <数据类型> [字段属性] [注释],
    <字段名> <数据类型> [字段属性] [注释],
    ……
    <字段名> <数据类型> [字段属性] [注释],
    CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…),
    KEY|INDEX [<索引名>] [<索引类型>] (<列名>,…),
    UNIQUE [INDEX|KEY] [<索引名>] [<索引类型>] (<列名>,…),
    FULLTEXT [索引名] (<字段名>)
) [存储引擎] [字符]

# 方式二：建表后添加索引(不能添加主键索引)
CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC]);

# 方式三：建表后添加索引
ALTER TABLE <表名> ADD INDEX [<索引名>] (<列名>,…);
ALTER TABLE <表名> ADD PRIMARY KEY (<列名>,…);
ALTER TABLE <表名> ADD UNIQUE [INDEX|KEY] [<索引名>] (<列名>,…);
ALTER TABLE <表名> ADD FULLTEXT [INDEX|KEY] [<索引名>] (<列名>,…);

性能优化：

explain <select语句>;

索引原则：
- 索引不是越多越好。
- 索引一般加在常用来查询的字段上。
- 不要对经常变动的字段加索引。
- 小数据量的表不需要加索引。

4、外键（物理外键，数据库级别的外键）

命令：

# 创建表时添加外键
key <外键名> (<字段名>),
constraint <外键名> foreign key (<字段名>) references <外键引用表名> (<外键引用字段名>)
# 创建表后添加外键
alter table <表名> add constraint <约束名> foreign key (<字段名>) references <外键引用表名> (<外键引用字段名>);

SQLyog：

图片说明

5 表的存储引擎

# 查看当前数据库支持的存储引擎
show engines;

1、InnoDB（默认）

支持事务、行锁、外键约束、全文索引。
表空间大小：较大，约为MyISAM的2倍。

2、MyISAM（旧版使用）

不支持事务。支持表锁，不支持行锁。不支持外键约束。不支持全文索引。
表空间大小：较小。节约空间，速度较快。

6 增删改（DML，数据库操作语言）

1、插入（insert）

插入所有字段：

# 1、插入单行
insert into <表名> values (<值1>, <值2>, ……);
# 2、插入多行
insert into <表名> values (<值1>, <值2>, ……), (<值1>, <值2>, ……), ……;

插入指定字段：

# 1、插入单行
insert into <表名> (<字段1>, <字段2>, ……) values (<值1>, <值2>, ……);
# 2、插入多行
insert into <表名> (<字段1>, <字段2>, ……) values (<值1>, <值2>, ……), (<值1>, <值2>, ……), ……;

2、修改（update）

修改单个字段：

update <表名> set <字段名> = <新值> where <条件>;

修改多个字段：

update <表名> set <字段1> = <值1>, <字段2> = <值2>, ……, <字段n> = <值n> where <条件>;

3、删除（delete）

delete from <表名> where <条件>;

4、条件表达式

操作符	含义	示例
=	等于	id = 1
<>或!=	不等于	`id` <> 1或 id != 1
>	大于	`id` > 1
<	小于	`id` < 1
>=	大于等于	`id` >= 1
<=	小于等于	`id` <= 1
between <值1> and <值2>	[值1, 值2]	between 1 and 3
<表达式1> and <表达式2>	&&	id = 1 and name = '张三'
<表达式1> or <表达式2>	类似\|\|	id = 1 or name = '张三'
is null、is not null	值是否为空
like	模糊查询	name like '%张三_'
in(值1, 值2, ……)	选择匹配	name in ('张三', '李四')

7 查（DQL，数据库查询语言）

1、基础查询

查询所有字段：

select * from <表名>;

查询指定字段：

select <字段1>, <字段2>, ……, <字段n> from <表名>;

别名：

<字段名或表名> as <别名>
<字段名或表名> <别名>

2、聚合函数

统计：

# 方式一：统计总共有多少记录（包含null）
select count(*) from <表名>;
select count(1) from <表名>;
# 方式二：统计<字段名>中的有值的记录个数（不包含null）
select count(<字段名>) from <表名>;

求和：

select sum(<字段名>) from <表名>;

求平均值：

select avg(<字段名>) from <表名>;

求最大值：

select max(<字段名>) from <表名>;

求最小值：

select min(<字段名>) from <表名>;

3、去重（distinct）

select distinct * from <表名>;

4、条件查询（where）

select * from <表名> where <条件>;

5、分页查询（limit）

pageSize：每页最多显示的数据数量。
pageIndex：当前页第一个数据的索引值（从0开始）。
第n页：pageIndex = (pageSize - 1) * n。

# 方式一：
select * 
from <表名> 
limit <pageSize> offset <pageIndex>;
# 方式二：
select * 
from <表名> 
limit <pageIndex>, <pageSize>;

6、连接查询（join）

内连接（交集）：

select <字段>
from <表1>
inner join <表2>
on <条件>;

左[外]连接（不满足条件的值填充为null）：

select <字段>
from <表1>
left [outer] join <表2>
on <条件>;

右[外]连接（不满足条件的值填充为null）：

select <字段>
from <表1>
right [outer] join <表2>
on <条件>;

全外连接（不满足条件的值填充为null）：

select <字段>
from <表1>
full outer join <表2>
on <条件>;

多表连接：

select <字段>
from <表1>
inner join <表2>
on <条件1>
inner join <表3>
on <条件2>;

自连接：

select t1.<字段>, t2.<字段>
from <表> as t1, <表> as t2
where t1.<字段1> = t2.<字段2>;

7、排序（order by）

select *
from <表>
order by <字段> [desc|asc];

8、子查询（select）

select *
from <表1>
where <字段> <条件操作符> select <字段> from <表2>;

9、分组（group by）、过滤（having）

分组、过滤通常与聚合函数配合使用。

select count(*)
from <表>
group by <字段>
[having] <条件>;

10、查询公式

SELECT [distinct] {* | <字段列名>}
[FROM <表>
     [left|right|inner join <表> on <条件>]
    [WHERE <条件>]
    [GROUP BY <字段> [HAVING <条件>] ]
    [ORDER BY <字段>]
    [LIMIT <offset>, <pageSize>]
]

8 事务

1、概念

事务：将多条SQL语句作为一个整体执行，要么同时成功，要么同时失败。

对于单条SQL语句，数据库系统自动将其作为一个事务执行，这种事务被称为隐式事务。

2、特性：ACID

A：Atomicity（原子性）

将一个事务中的多条语句作为一个原子操作执行，要么同时成功，要么同时失败。
C：Consistency（一致性）

事务完成后，所有数据状态的变化都是一致的，即数据守恒，A的数值减少100，则B的数值必定增加100。
I：Isolation（隔离性）

多个事务并发执行，每个事务做出的修改要与其他事务隔离，涉及到隔离级别。
D：Durability（持久性）

事务完成后，对数据库的修改即被持久化存储了。

3、隔离级别

- 读未提交（Read Uncommitted）

事务A可以读取到事务B修改后，但是未提交的数据。
脏读（Dirty Read）：如果事务B回滚（没有提交数据），则事务A读取到的数据为脏数据。

- 读已提交（Read Committed）

事务A只能读取到事务B已经提交的数据。
不可重复读（Non Repeatable Read）：事务A中，多次读取某个数据。在事务A执行期间，事务B修改了这个数据，此时事务A多次读取这个数据，得到的值可能不同。

- 可重复读（Repeatable Read）

事务A中多次读取某个数据的值都是本事务中更新的值，即使它被事务B修改并提交。
幻读（Phantom Read）：事务A中，多次读取不存在的数据（null）。在事务A执行期间，事务B插入了这个数据，此时事务A多次读取这个数据，之前读取为null，但在事务A中可以更新这个不存在的数据，更新之后读取突然出现了这个数据。

- 序列化（Serializable）

所有事务按照序列依次执行，即任意两个事务不能同时执行。
效率非常低。

4 默认隔离级别

在MySQL中，如果使用InnoDB，默认的隔离级别是可重复读（Repeatable Read）。

9 规约

1、建表规约

【强制】表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint
（1 表示是，0 表示否）。
说明：任何字段如果为非负数，必须是 unsigned。
注意：POJO 类中的任何布尔类型的变量，都不要加 is 前缀，所以，需要在<resultmap>设置从 is_xxx 到
Xxx 的映射关系。数据库表示是与否的值，使用 tinyint 类型，坚持 is_xxx 的命名方式是为了明确其取值含
义与取值范围。
正例：表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除。</resultmap>
【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只
出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
说明：MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、
字段名，都不允许出现任何大写字母，避免节外生枝。
正例：aliyun_admin，rdc_config，level3_name
反例：AliyunAdmin，rdcConfig，level_3_name
【强制】表名不使用复数名词。
说明：表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应于 DO 类名也是单数形式，符合
表达习惯。
【强制】禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。
【强制】主键索引名为 pk_ 字段名；唯一索引名为 uk 字段名；普通索引名则为 idx _字段名。
说明：pk 即 primary key；uk_ 即 unique key；idx_ 即 index 的简称。
【强制】小数类型为 decimal，禁止使用 float 和 double。
说明：在存储的时候，float 和 double 都存在精度损失的问题，很可能在比较值的时候，得到不正确的
结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数并分开存储。
【强制】如果存储的字符串长度几乎相等，使用 char 定长字符串类型。
【强制】 varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度
大于此值，定义字段类型为 text ，独立出来一张表，用主键来对应，避免影响其它字段索引效
率。
【强制】表必备三字段：id, gmt_create, gmt_modified。
说明：其中 id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1。gmt_create, gmt_modified
的类型均为 datetime 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新。
【推荐】表的命名最好是遵循“业务名称_表的作用”。
正例：alipay_task / force_project / trade_config
【推荐】库名与应用名称尽量一致。
【推荐】如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。
【推荐】字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：
1）不是频繁修改的字段。
2）不是唯一索引的字段。
3）不是 varchar 超长字段，更不能是 text 字段。
正例：各业务线经常冗余存储商品名称，避免查询时需要调用 IC 服务获取。
【推荐】单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。
说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索
速度。
正例：无符号值可以避免误存负数，且扩大了表示范围。
对象年龄区间类型字节表示范围
人 150 岁之内 tinyint unsigned 1 无符号值：0 到 255
龟数百岁 smallint unsigned 2 无符号值：0 到 65535
恐龙化石数千万年 int unsigned 4 无符号值：0 到约 43 亿
太阳约 50 亿年 bigint unsigned 8 无符号值：0 到约 10 的 19 次方

2、索引规约

【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，
即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。
【强制】超过三个表禁止 join 。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，
保证被关联的字段需要有索引。
说明：即使双表 join 也要注意表索引、SQL 性能。
【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据
实际文本区分度决定索引长度。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
【推荐】如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索
引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
正例：where a=? and b=? order by c; 索引：a_b_c
反例：索引如果存在范围查询，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引 a_b 无法排序。
【推荐】利用覆盖索引来进行查询操作，避免回表。
说明：如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。
正例：能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用 explain 的结果，extra 列会出现：using index。
【推荐】利用延迟关联或者子查询优化超多分页场景。
说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL改写。
正例：先快速定位需要获取的 id 段，然后再关联：
```
SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
```
【推荐】 SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts
最好。
说明：
1） consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
2） ref 指的是使用普通的索引（normal index）。
3） range 对索引进行范围检索。
反例：explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range还低，与全表扫描是小巫见大巫。
【推荐】建组合索引的时候，区分度最高的在最左边。
正例：如果 where a=? and b=?，a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。
说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where c>? and d=?
那么即使 c 的区分度更高，也必须把 d 放在索引的最前列，即建立组合索引 idx_d_c。
【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。
【参考】创建索引时避免有如下极端误解：
1）索引宁滥勿缺。认为一个查询就需要建一个索引。
2）吝啬索引的创建。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。
3）抵制惟一索引。认为惟一索引一律需要在应用层通过“先查后插”方式解决。

3、SQL 语句

【强制】不要使用 count(列名)或 count(常量)来替代 count()，count()是 SQL92 定义的标
准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
【强制】count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct col1,
col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。
【强制】当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果为
NULL，因此使用 sum()时需注意 NPE 问题。
正例：可以使用如下方式来避免 sum 的 NPE 问题：SELECT IFNULL(SUM(column), 0) FROM table;
【强制】使用 ISNULL() 来判断是否为 NULL 值。
说明：NULL 与任何值的直接比较都为 NULL。
1） NULL<>NULL 的返回结果是 NULL，而不是 false。
2） NULL=NULL 的返回结果是 NULL，而不是 true。
3） NULL<>1 的返回结果是 NULL，而不是 true。
反例：在 SQL 语句中，如果在 null 前换行，影响可读性。select * from table where column1 is null and column3 is not null; 而ISNULL(column)是一个整体，简洁易懂。从性能数据上分析，ISNULL(column)执行效率更快一些。
【强制】代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。
【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。
【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
【强制】数据订正（特别是删除或修改记录操作）时，要先 select ，避免出现误删除，确认无
误才能执行更新语句。
【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或
表名）进行限定。
说明：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在时，就会抛异常。
正例：select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id;
反例：在某业务中，由于多表关联查询语句没有加表的别名（或表名）的限制，正常运行两年后，最近在某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句出现出 1052 异常：Column 'name' in field list is ambiguous。
【推荐】SQL 语句中表的别名前加 as，并且以 t1、t2、t3、...的顺序依次命名。
说明：1）别名可以是表的简称，或者是根据表出现的顺序，以 t1、t2、t3 的方式命名。2）别名前加 as
使别名更容易识别。
正例：select t1.name from table_first as t1, table_second as t2 where t1.id=t2.id;
【推荐】 in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控
制在 1000 个之内。
【参考】因国际化需要，所有的字符存储与表示，均采用 utf 8 字符集，那么字符计数方法需
要注意。
说明：
SELECT LENGTH("轻松工作")；返回为 12
SELECT CHARACTER_LENGTH("轻松工作")；返回为 4
如果需要存储表情，那么选择 utf8mb4 来进行存储，注意它与 utf8 编码的区别。
【参考】 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE
无事务且不触发 trigger ，有可能造成事故，故不建议在开发代码中使用此语句。
说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

4、ORM 映射

【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。
说明：1）增加查询分析器解析成本。2）增减字段容易与 resultMap 配置不一致。3）无用字段增加网络
消耗，尤其是 text 类型的字段。
【强制】POJO 类的布尔属性不能加 is，而数据库字段必须加 is_，要求在 resultMap 中进行
字段与属性之间的映射。
说明：参见定义 POJO 类以及数据库字段定义规定，在 sql.xml 增加映射，是必须的。
【强制】不要用 resultClass 当返回参数，即使所有类属性名与数据库字段一一对应，也需要
定义<resultmap>；反过来，每一个表也必然有一个<resultmap>与之对应。
说明：配置映射关系，使字段与 DO 类解耦，方便维护。</resultmap></resultmap>
【强制】sql. xml 配置参数使用：#{}，# param # 不要使用${} 此种方式容易出现 SQL 注入。
【强制】 iBATIS 自带的 queryForList(String statementName , int start , int size) 不推荐使用。
说明：其实现方式是在数据库取到 statementName 对应的 SQL 语句的所有记录，再通过 subList 取
start,size 的子集合。
正例：
Map<String, Object> map = new HashMap<>();
map.put("start", start);
map.put("size", size);
【强制】不允许直接拿 HashMap 与 Hashtable 作为查询结果集的输出。
反例：某同学为避免写一个<resultMap>，直接使用 HashTable 来接收数据库返回结果，结果出现日常
是把 bigint 转成 Long 值，而线上由于数据库版本不一样，解析成 BigInteger，导致线上问题。
【强制】更新数据表记录时，必须同时更新记录对应的 gmt_modified 字段值为当前时间。
【推荐】不要写一个大而全的数据更新接口。传入为 POJO 类，不管是不是自己的目标更新字
段，都进行 update table set c1=value1,c2=value2,c3=value3; 这是不对的。执行 SQL 时，
不要更新无改动的字段，一是易出错；二是效率低；三是增加 binlog 存储。
【参考】@Transactional 事务不要滥用。事务会影响数据库的 QPS，另外使用事务的地方需
要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。
【参考】<isequal>中的 compareValue 是与属性值对比的常量，一般是数字，表示相等时
带上此条件；<isnotempty>表示不为空且不为 null 时执行；<isnotnull>表示不为 null 值
时执行。</isnotnull></isnotempty></isequal>

10 权限管理

1、SQLyog

图片说明

2、SQL命令

用户表：mysql.user。
创建用户：

create user <用户名> identified by <密码>;

修改密码：

# 修改当前用户密码
set password = password(<新密码>);

# 修改指定用户密码
set password for <用户名> = password(<新密码>);

重命名：

rename user <用户名> to <新用户名>;

授权：

grant all privileges on *.* to <用户名>;    # 给用户授予所有权限（除了授权的权限）

查看权限：

show grants for <用户名>;    # 查看指定用户的权限
show grants for root@localhost;    # 查看root的权限

撤销权限：

revoke all privileges on *.* from <用户名>;    # 撤销用户的所有权限

删除用户：

drop user <用户名>;

11 备份

1、SQLyog

图片说明

2、命令行

备份（CMD）：

mysqldump -h<主机名> -u<用户名> -p<密码> <数据库> [<表1> <表2> <表3> ……] > <地址/文件名.sql>
# 示例
mysqldump -hlocalhost -uroot -proot school student >D:/a.sql

导入（mysql）：

use <数据库名>; # 导入表需要指定数据库，导入数据库不必指定
source <文件名>;

导入（CMD）：

mysql -u<用户名> -p<密码> <数据库> < <文件名>

3、拷贝物理文件

拷贝Data文件中的特定数据库。

图片说明

12 数据库设计

1、步骤

分析需求。
标识实体，创建表。
分析实体间关系（E-R图）。

2、三大范式

- 第一范式（1NF）

每列保持原子性：表的每一列都是不可分割的原子数据项。

- 第二范式（2NF）

前提：满足第一范式。
表中的每列都和主键相关。

- 第三范式（3NF）

前提：满足第一范式和第二范式。
表中的每一列数据都和主键直接相关，而不能间接相关。

3、规范与性能

考虑商业化的需求和目标，数据库的性能更加重要。
关联查询的表不得超过三张表（阿里规约）。
给某些表增加一些冗余的字段，从多表查询变为单表查询。
增加一些计算列，从大数据量降为小数据量的查询。

13 JDBC

测试数据：

CREATE TABLE `db_test`.`user` ( 
    `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '用户id', 
    `name` VARCHAR(40) NOT NULL COMMENT '用户名', 
    `password` VARCHAR(40) NOT NULL COMMENT '密码', 
    `email` VARCHAR(60) NOT NULL COMMENT '邮箱', 
    `birthday` DATE NOT NULL COMMENT '生日',
    PRIMARY KEY (`id`)
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 

INSERT INTO `db_test`.`user` (`name`, `password`, `email`, `birthday`)
VALUES ('张三', 'zhangsan', 'zhangsan@qq.com', '1980-1-1'), ('李四', 'lisi', 'lisi@qq.com', '1988-7-7'), ('王五', 'wangwu', 'wangwu@qq.com', '1998-9-9');

1、导入数据库驱动

Maven仓库：https://mvnrepository.com/artifact/mysql/mysql-connector-java

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

2、JDBC的基本使用

加载驱动；
连接数据库；
执行SQL；
释放连接。

import java.sql.*;

public class Test {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/db_test?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "root";
        // 1、加载驱动
        Class.forName(driver);
        // 2、连接数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        // 3、执行SQL
        Statement statement = connection.createStatement();
        String sql = "select * from user";
        ResultSet resultSet = statement.executeQuery(sql);    // 执行查询
        // statement.excuteUpdate(sql)执行增、删、改
        while (resultSet.next()) {
            System.out.print("id = " + resultSet.getObject("id") + ", ");
            System.out.print("name = " + resultSet.getObject("name") + ", ");
            System.out.print("password = " + resultSet.getObject("password") + ", ");
            System.out.print("email = " + resultSet.getObject("email") + ", ");
            System.out.println("birthday = " + resultSet.getObject("birthday"));
        }
        // 4、释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

3、工具类

db.properties：

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db_test?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=root

JdbcUtils.java：

package utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    static {
        try {
            InputStream input = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(input);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            // 1、加载驱动
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    // 2、获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    // 4、释放连接
    public static void release(Connection connection, Statement statement, ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

测试：

import utils.JdbcUtils;

import java.sql.*;

public class Test {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            // 1、加载驱动
            // 2、连接数据库
            connection = JdbcUtils.getConnection();
            // 3、执行SQL
            statement = connection.createStatement();
            // 插入
            String sql1 = "insert into user (name, password, email, birthday) values('赵六', 'zhaoliu', 'zhaoliu@qq.com', '1999-9-9')";
            int ins = statement.executeUpdate(sql1);
            System.out.println("更新条数：" + ins);
            // 查询
            String sql2 = "select * from user";
            resultSet = statement.executeQuery(sql2);
            while (resultSet.next()) {
                System.out.print("id = " + resultSet.getObject("id") + ", ");
                System.out.print("name = " + resultSet.getObject("name") + ", ");
                System.out.print("password = " + resultSet.getObject("password") + ", ");
                System.out.print("email = " + resultSet.getObject("email") + ", ");
                System.out.println("birthday = " + resultSet.getObject("birthday"));
            }
            // 删除
            String sql3 = "delete from user where id = 4";
            int del = statement.executeUpdate(sql3);
            System.out.println("删除条数：" + del);
            // 更新
            String sql4 = "update user set name = '张三三' where id = 1";
            int upt = statement.executeUpdate(sql4);
            System.out.println("更新条数：" + upt);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 4、释放连接
            JdbcUtils.release(connection, statement, resultSet);
        }
    }
}

4、SQL注入

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

Statement对象存在SQL注入风险，PreparedStatement可以防止SQL注入。

import utils.JdbcUtils;

import java.sql.*;

public class Test {
    public static void main(String[] args) {
        // 1、正常输入
        login("李四", "lisi");  // id = 2, name = 李四, password = lisi, email = lisi@qq.com, birthday = 1988-07-07
        // 2、SQL注入：select * from user where name = '' or '1' and password = '' or '1'
        login("'or '1", "'or '1"); // 查询出所有数据
    }

    public static void login(String username, String password) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "select * from user where name = '" + username + "' and password = '" + password + "'";
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                System.out.print("id = " + resultSet.getObject("id") + ", ");
                System.out.print("name = " + resultSet.getObject("name") + ", ");
                System.out.print("password = " + resultSet.getObject("password") + ", ");
                System.out.print("email = " + resultSet.getObject("email") + ", ");
                System.out.println("birthday = " + resultSet.getObject("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, resultSet);
        }
    }
}

5、PreparedStatement

PreparedStatement将传入的参数当做字符串，可以防止SQL注入，并且效率更高。

import utils.JdbcUtils;

import java.sql.*;

public class Test {
    public static void main(String[] args) {
        // 1、正常输入
        login("李四", "lisi");  // id = 2, name = 李四, password = lisi, email = lisi@qq.com, birthday = 1988-07-07
        // 2、SQL注入：select * from user where name = "'' or '1'" and password = "'' or '1'"
        login("'or '1", "'or '1"); // 无法查出数据，防止SQL注入
    }

    public static void login(String username, String password) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 1、加载驱动；2、连接数据库
            connection = JdbcUtils.getConnection();
            // 3、执行SQL
            String sql = "select * from user where name = ? and password = ?";  // 使用?占位
            preparedStatement = connection.prepareStatement(sql);
            // 设值，index从1开始
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, password);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.print("id = " + resultSet.getObject("id") + ", ");
                System.out.print("name = " + resultSet.getObject("name") + ", ");
                System.out.print("password = " + resultSet.getObject("password") + ", ");
                System.out.print("email = " + resultSet.getObject("email") + ", ");
                System.out.println("birthday = " + resultSet.getObject("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 4、释放连接
            JdbcUtils.release(connection, preparedStatement, resultSet);
        }
    }
}

6、IDEA连接MySQL

图片说明

7、事务

测试数据：

CREATE TABLE `db_test`.`account` ( 
    `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '账户id', 
    `name` VARCHAR(20) NOT NULL COMMENT '账户名', 
    `money` DECIMAL NOT NULL COMMENT '账户余额', 
    PRIMARY KEY (`id`) 
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 
INSERT INTO `db_test`.`account` (`name`, `money`) VALUES ('A', '100'); 
INSERT INTO `db_test`.`account` (`name`, `money`) VALUES ('B', '100');

JDBC使用事务：

import utils.JdbcUtils;

import java.sql.*;

public class Test {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            // 1、关闭数据库自动提交（开启事务）
            connection.setAutoCommit(false);
            String sql1 = "update account set money = money - 100 where name = 'A'";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();
            int x = 1 / 0;  // 执行事务中的错误
            String sql2 = "update account set money = money + 100 where name = 'B'";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();
            ///2、提交事务
            connection.commit();
        } catch (SQLException e) {
            // 3、回滚（默认出现异常自动回滚）
//            try {
//                connection.rollback();
//            } catch (SQLException e1) {
//                e1.printStackTrace();
//            }
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, preparedStatement, resultSet);
        }
    }
}

8、数据库连接池（DataSource）

数据库连接、释放十分浪费系统资源。
池化技术：提前保存大量的资源，需要时直接使用，用完放回。

- DBCP2

Maven仓库：https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2

<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.1.1</version>
</dependency>

dbcp-config.properties：

# 连接数据库基本信息
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db_test?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=root
# 初始连接数
initialSize=10
# 最大连接数
maxActive=50
# 最大空闲连接
maxIdle=20
# 最小空闲连接
minIdle=5
# 超时等待时间（毫秒）
maxWait=60000
# 驱动附带属性
connectionProperties=useUnicode=true;characterEncoding=utf8

DbcpUtils.java：

package utils;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class DbcpUtils {
    private static DataSource dataSource = null;

    static {
        try {
            InputStream input = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcp-config.properties");
            Properties properties = new Properties();
            properties.load(input);
            // 创建数据源
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    /**
     * 释放连接
     * @param connection
     * @param statement
     * @param resultSet
     */
    public static void release(Connection connection, Statement statement, ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

使用：

import utils.DbcpUtils;

import java.sql.*;

public class Test {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 获取连接
            connection = DbcpUtils.getConnection();
            String sql = "select * from user where name = ? and password = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "李四");
            preparedStatement.setString(2, "lisi");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.print("id = " + resultSet.getObject("id") + ", ");
                System.out.print("name = " + resultSet.getObject("name") + ", ");
                System.out.print("password = " + resultSet.getObject("password") + ", ");
                System.out.print("email = " + resultSet.getObject("email") + ", ");
                System.out.println("birthday = " + resultSet.getObject("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放连接
            DbcpUtils.release(connection, preparedStatement, resultSet);
        }
    }
}

- C3P0

Maven仓库：https://mvnrepository.com/artifact/com.mchange/c3p0

<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.5.2</version>
</dependency>

c3p0-config.xml：

<c3p0-config>
    <!-- 默认数据源 -->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/db_test</property>
        <property name="user">root</property>
        <property name="password">root</property>

        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>
    </default-config>

    <!-- 指定数据源 -->
    <named-config name="mySource">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/bookstore</property>
        <property name="user">root</property>
        <property name="password">xxxx</property>

        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>
    </named-config>
</c3p0-config>

C3p0Utils.java：

package utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class C3p0Utils {
    private static DataSource dataSource = null;

    static {
        try {
            dataSource = new ComboPooledDataSource();   // 使用默认数据源
//            dataSource = new ComboPooledDataSource("mySource");   // 使用指定数据源：mySource
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    /**
     * 释放连接
     * @param connection
     * @param statement
     * @param resultSet
     */
    public static void release(Connection connection, Statement statement, ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

测试：

import utils.C3p0Utils;

import java.sql.*;

public class Test {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 获取连接
            connection = C3p0Utils.getConnection();
            String sql = "select * from user where name = ? and password = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "李四");
            preparedStatement.setString(2, "lisi");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.print("id = " + resultSet.getObject("id") + ", ");
                System.out.print("name = " + resultSet.getObject("name") + ", ");
                System.out.print("password = " + resultSet.getObject("password") + ", ");
                System.out.print("email = " + resultSet.getObject("email") + ", ");
                System.out.println("birthday = " + resultSet.getObject("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放连接
            C3p0Utils.release(connection, preparedStatement, resultSet);
        }
    }
}

- Druid

待补充……

14 性能优化

1、影响性能的因素

- 商业需求对性能的影响

1）不合理需求

需求：一个论坛帖子总量的统计。附加需求：实时更新。

方式一：

select count(id)

方式二：新建一个表，在这个表中更新这个汇总数据（频率问题）。

方式三：不实时更新，创建一个统计表，每隔一段时间统计一次并存入。

2）无用功能堆积

无用的列堆积，错误的表设计，无用的表关联。

- 系统架构及实现对性能影响

1）哪些数据不适合放在数据库中

二进制数据（音频、图像等），超大文本等：降低查询效率，增加IO次数。

流水队列数据（日志、事件等）：写频率过高。

2）合理的cache

哪些数据适合放到cache中：系统配置信息、活跃用户的基本信息、活跃用户的定制化信息、基于时间段的统计数据、读远大于写的数据。

3）减少数据库交互次数

N+1问题：使用连接查询；使用冗余字段；使用1+1查询（推荐）。

4）过渡依赖数据库SQL语句的功能

交叉表；不必要的表连接。

5）重复执行相同的SQL

在一个页面中，有相同的内容，但是使用2条SQL去查询。

6）其他常见系统架构和实现问题

Cache系统的不合理利用导致Cache命中率低下造成数据库访问量的增加，同时也浪费了Cache系统的硬件资源投入；

过度依赖面向对象思想，对系统可扩展性的过渡追求，促使系统设计的时候将对象拆得过于离散，造成系统中大量的复杂Join语句，而MySQL Server在各数据库系统中的主要优势在于处理简单逻辑的查询，这与其锁定的机制也有较大关系；

对数据库的过渡依赖，将大量更适合存放与文件系统中的数据存入了数据库中，造成数据库资源的浪费，影响到系统的整体性能，如各种日志信息；

过度理想化系统的用户体验，使大量非核心业务消耗过多的资源，如大量不需要实时更新的数据做了实时统计计算。

- 其他因素

1）SQL引起性能问题的原因

MySQL执行过程：

客户端发送一条查询给服务器；
服务器会先检查查询缓存，如果命中缓存，则立即返回存储在缓存中的结果，否则进入下一阶段；
服务器端进行SQL解析、预处理，再由优化器根据SQL所涉及到的数据表的统计信息进行计算，生成对应的执行计划；
MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
将结果返回给客户端。

图片说明

磁盘IO

SQL执行的最大瓶颈在于磁盘的IO，即数据的读取；不同SQL的写***造成不同的执行计划的执行，而不同的执行计划在IO上面临完全不一样的数量级，从而造成性能的差距。

优化SQL，就是让查询优化器根据程序员的计划选择匹配的执行计划，来减少查询的IO。

2）Schema（表结构）设计对系统性能的影响

冗余数据的处理

适当的数据冗余可以提高系统的整体查询性能；

关系型数据库的三范式：
1. 第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库，是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值。
2. 第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。
3. 第三范式（3NF）要求一个数据库表中不包含已在其他表中已包含的非主关键信息（不允许有冗余数据）。
大表拆小表，有大数据的列单独拆成小表
1. 在一个数据库中，一般不会设计属性过多的表。
2. 在一个数据库中，一般不会有超过500或1000万数据的表（拆表：按逻辑拆分、按业务拆分）。
3. 有大数据的列单独拆成小表。
根据需求的展示设置更合理的表结构
把常用的属性分离成小表
1. 减少查询常用属性需要查询的列。
2. 便于常用属性的集中缓存。

3）硬件环境对性能的影响

提高IO指标
- IOPS：每秒可提供的IO访问次数。
- IO吞吐量：每秒的IO总流量。
提高CPU计算能力
如果是单独的数据库服务器，提高网络能力

4）数据库系统场景：OLTP、OLAP

OLTP（on-line transaction processing，联机事务处理）

OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，如银行交易。

OLTP的数据库应用特点：
1. 系统总体数据量较大，但活跃数据量较小。
2. IO访问频繁，但涉及数据量较小，分布离散。
3. 并发很高。
4. 网络交互数据量较小，但交互频繁。
OLTP系统硬件架构选型：
1. 大量合理的Cache设计，能够大大减少数据库的交互；尽量扩大内存容量。
2. IOPS指标要求较高。
3. CPU的计算能力，并行计算能力要求较高。
4. 对外网络要求较高。
OLAP（on-line analytical processing，联机分析处理）

OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果，数据仓库就是一个典型应用场景。

特点：
1. 数据量非常大，数据访问集中，数据活跃度分布平均。
2. 并发访问较低。
3. 每次检索的数量非常多。
架构选型：
1. 硬盘存储容量需要非常大。
2. 对存储设备的IO吞吐量要求很高。
3. CPU要求较低。
4. 对外网络要求不高。

- 综合考虑

需求和架构及业务实现优化：55%。
Query语句的优化：30%。
数据库自身的优化：15%。

2、SQL优化

- 合理使用索引

1）理解MySQL的索引

索引的原理：把无序的数据变成有序的查询。
查询流程（没有索引）：
```
select * from user where number = 6;
```
1. 遍历整张表的内容，比较number是否为6。
2. 如果为6，就把这条数据放到内存中的结果集中。
3. 这种情况下的问题是全表扫描，需要把表所有的数据查询一遍（IO多）。
建立索引的流程：
1. 把表的内容按照索引列排序。
2. 把排序的结果变成一个链表。
查询流程（有索引）：
1. 在索引中查询number为6的那条链表数据。
2. 顺序得到对应的数据。
3. 把数据放到结果集中。
修改数据：
1. 增加一条数据（id = 10, number = 6）：插入数据，添加到链表中。
2. 删除一条数据：查询这条数据对应索引的位置，然后从链表中删除这条数据。
3. 修改数据：如果修改的数据中，涉及到了索引列的值的改变，先删除数据，再增加数据。
索引的物理结构：
1. 数据库文件存储的位置：my.ini配置文件中dataDir对应的目录中。
2. 每一个数据库对应一个文件夹。
  1. MyISAM引擎：每个表（table_name）--> table_name.MYI（数据表对应的索引信息和索引内容）、table_name.FRM（数据表的结构信息）、table_name.MYD（数据表的内容）。
  2. InnoDB引擎：每个表（table_name）--> table_name.FRM（数据表的结构信息）。数据文件和索引文件都是统一放在ibdata文件中。
  3. 索引文件都是额外存在的，对索引的查询和维护都是需要消耗IO的。
索引的结构：
1. 默认情况下，一旦创建了一个表，这个表设置了主键，那么MySQL会自动为这个主键创建一个Unique的索引。
2. 索引类型：
  - Normal：普通的索引，允许一个索引值后面关联多个行值（链表）。
  - Unique：唯一索引：允许一个索引值后面只能有一个行值（链表）。对列添加唯一约束其实就是为这个列添加一个unique索引。为一个表添加一个主键，其实就是为这个表主键列设置了非空约束，并为主键列添加了一个唯一索引。
  - FullText：全文检索，MySQL的全文检索只能用MyISAM引擎，性能较低，不建议使用。
3. 索引的方法（规定索引的存储结构）：
  1. B-Tree

使用平衡数实现索引，是MySQL中使用最多的索引类型。
在InnoDB中，存在两种索引类型，第一种是主键索引（Primary key），在索引内容中直接保存数据的地址；第二种是其他索引，在索引内容中保存的是指向主键索引的引用。
在使用InnoDB时，要尽量使用主键索引，速度非常快。
B-Tree中保存的数据都是按照一定顺序保存的数据，是允许在范围之内进行查询：
```
   select * from user where id < 100;
```
1. Hash

图片说明

把索引的值做hash运算，并存放到Hash表中。
使用较少，一般是Memory引擎使用。
优点：按照常理，Hash的性能比B-Tree高很多。
缺点：
1. Hash索引只能适用于精确值的比较，如=、in或<>等，并且无法使用范围查询。
2. 无法使用索引排序。
3. 组合Hash索引无法使用部分索引。
4. 如果大量索引Hash值相同，性能较低。

InnoDB中索引的存储结构：
- 主键索引区（PI）：B-Tree：节点（主键，即id）、值（数据地址adx）。
- 普通索引区（SI）：B-Tree：节点（索引列的值）、值（id地址链表）。
- 普通索引区（SI）：Hash：节点（哈希值）、值（id地址链表）。

2）索引的利弊

索引的好处：
1. 提高表数据的检索效率。
2. 如果排序的列（或查询的列）是索引列，大大降低排序成本。
3. 在分组操作中，如果分组条件是索引列，也会提高效率。
索引的问题：

索引需要额外的维护成本。

因为索引文件是单独存在的文件，对数据的增加、修改、删除，都会产生额外的对索引文件的操作，这些操作需要消耗额外的IO，会降低增、改、删的执行效率。

3）如何创建索引

较频繁的作为查询条件的字段应该创建索引。
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件。

作为索引的列，如果不能有效的区分数据，那么这个列就不适合作为索引列，如性别等状态不多的列。但是，如果可能出现值按照该条件查询，那么就要考虑到其他提升性能的方式。

更新非常频繁的字段不适合创建索引。
不会出现在WHERE子句中的字段不该创建索引。
索引不是越多越好（只为必要的列创建索引）

不管有多少索引，一次查询至多采用一个索引。索引和索引之间是独立的。

因为索引和索引之间是独立的，所以每一个索引都是单独维护的。数据的增、改、删，会导致所有的索引都要单独维护。

4）单值索引和组合索引

因为一个查询一次至多只能使用一个索引。所以，如果都使用单值索引，在数据量较大的情况下，不能很好地区分数据。

所以，MySQL引入了多值索引：由多列的值组成的索引（多列的索引是有顺序的）。

复合索引的原理：类似ORDER BY，在排序和分组（创建链表时），按照多个列进行排序和合并。

select * from user where a = 'a' and num > 15;    # 可以使用a+num的复合索引
select * from user where a = 'a';    # 可以使用a+num的复合索引
select * from user where num > 15;    # 不可以使用a+num的复合索引
select * from user where num > 15 and a = 'a';    # 不可以使用a+num的复合索引

组合索引在查询的时候，遵循向左原则：只要在查询的时候，是按照复合索引从左到右的顺序依次查询，不管查询条件是否完全满足所有的复合索引的列，都可以使用部分的复合索引。

在实际应用中，基本上都使用复合索引。

5）MySQL中索引使用限制

Blob和Text类型的列只能创建前缀索引。
MySQL目前不支持函数索引（在MySQL中，索引只能是一个列的原始值，不能把列通过计算的值作为索引）。
使用不等于（!=或<>）的时候MySQL无法使用索引。
过滤字段使用了函数运算后（如abs(column)），MySQL无法使用索引。
JOIN语句中JOIN条件字段类型不一致时，MySQL无法使用索引。
使用LIKE操作时，如果条件以通配符开始（"%abc……"），MySQL无法使用索引。

字符串可以用作索引，按照字母顺序排序。

如果使用LIKE：select * from user where name like "李%"，可以使用索引。

但是：like "_李"、like "%李"都不能使用索引。
使用非等值查询时，MySQL无法使用Hash索引。

- 使用Explain和Profiling

1）Explain命令

Explain可以查看MySQL执行一条SQL所选择的执行计划。

explain <sql语句>

id：选择标识符
select_type：表示查询的类型。
table：输出结果集的表
partitions：匹配的分区
type：表示表的连接类型
possible_keys：表示查询时，可能使用的索引
key：表示实际使用的索引
key_len：索引字段的长度
ref：列与索引的比较
rows：扫描出的行数(估算的行数)
filtered：按表条件过滤的行百分比
Extra：执行情况的描述和说明

2）Profiling的使用

Profiling可以用来准确定位一条SQL的性能瓶颈。

set profiling = 1;    # 1、开启profiling
<sql语句>    # 2、运行sql
set profiling = 0;    # 3、关闭profiling
show profiles;    # 4、查询sql的执行时间
show profile for query <sql序号>;    # 5、查看具体sql的执行状态
show profile cpu, block io for query <sql序号>;    # 6、查询具体sql的CPU、IO

- 优化JOIN

1）理解JOIN原理

在MySQL中使用Nested Loop Join来实现JOIN。

A JOIN B：通过A表的结果集作为循环基础，一条一条地通过结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。

2）JOIN优化原则

尽量减少JOIN语句中的Nested Loop的循环总次数，用小结果集驱动大结果集（注意不是小表 JOIN 大表）。
优先优化Nested Loop的内层循环（大结果集中的ON条件字段）。
保证JOIN语句中被驱动表上JOIN条件字段已经被索引（大结果集中的ON条件字段）。
扩大JOIN Buffer的大小。

- 其他优化

1）优化ORDER BY

2）优化GROUP BY

3）优化DISTINCT

- SQL优化原则

1）选择需要优化的SQL

不是所有的SQL都需要优化。
在优化的过程中，首选更需要优化的SQL。
优先选择优化——高并发、低消耗的SQL。
SQL运行较慢有两个影响因素：IO、CPU。

2）Explain和Profile

任何SQL的优化，都从Explain语句开始，Explain语句能够得到数据库执行该SQL选择的执行计划。
首先明确需要的执行计划，再使用Explain检查。
使用Profiling明确SQL的问题和优化的结果。

3）永远用小结果集驱动大的结果集

4）在索引中完成排序

5）使用最小Columns

特别是需要使用column排序的时候。
减少网络传输数据量。
MySQL排序原理：把所有的column数据全部取出，在排序缓存区排序，再返回结果。如果column数据量大，排序区容量不够的时候，就会使用先column排序，再取数据，再返回的多次请求方式。

6）使用最有效的过滤条件

过多的WHERE不一定能够提高访问性能。
一定要让WHERE条件使用自己预期的执行计划。

7）避免复杂的JOIN和子查询

复杂的JOIN和子查询，需要锁定过多的资源，MySQL在大量并发情况下处理锁定性能下降较快。
不要过多依赖SQL的功能，把复杂的SQL拆分为简单的SQL。
MySQL子查询性能较低，应尽量避免使用。

3、其他优化

- Query Cache

- `Innodb_buffer_pool_size`

Innodb_buffer_pool_size：InnoDB的缓存，可以用于缓存索引，同时还会缓存实际的数据。
Innodb_buffer_pool_size用来设置InnoDB最主要的Buffer（InnoDB_Buffer_Pool）的大小，对InnoDB整体性能也最大，可以按需要设置大一些（2~3G）。

- 事务优化

1）隔离级别优化

2）InnoDB中的事务处理

事务在Buffer中对数据进行修改。
事务的变化记录在事务日志中。
在合适的时机同步事务日志中的数据到数据库中。

3）`innodb_flush_log_at_trx_commit`

通过设置innodb_flush_log_at_trx_commit，可以修改事务日志同步时机：
- innodb_flush_log_at_trx_commit=0：每1秒同步一次事务日志文件
- innodb_flush_log_at_trx_commit=1：默认设置，每一个事务完成之后，同步一次事务日志文件。
- innodb_flush_log_at_trx_commit=2：事务完成之后，写到事务日志文件中，等到日志覆盖再同步数据。
1性能最差；2不能完全保证数据是写到数据文件中，如果宕机，可能会有数据丢失现象，但性能最高；0性能和安全性居中。

4、MySQL主从复制

- MySQL主从

系统优化：假如现在用户反映系统太卡：
1. 如果是请求过多，判定Web服务器的压力过大，增加前端的Web服务器，做负载均衡。
2. 如果请求静态页面不卡了，但是请求动态数据还是卡，说明MySQL处理的请求太多了，在应用层增加缓存。
3. 如果请求激增，还是有大量的查询到MySQL，想办法处理MySQL。
在一个应用中，大量消耗MySQL的地方在查询，我们可以把MySQL拆开，把MySQL做成主从。
就算MySQL拆成了多个，也必须分出主和从，所有的写操作必须要在主MySQL上完成。
所有的从MySQL的数据都来自于（同步于）主MySQL。
既然涉及到同步，就一定有延迟，就一定可能在读的时候产生脏数据。所以，能够在从MySQL上进行的读操作，一定是对实时性和脏数据有一定容忍度的数据（如登录日志、后台报表、首页统计信息来源、文章、咨询、SNS消息等）。
对实时性要求高的数据，必须在主MySQL上执行。
在MySQL主从时，如果一个业务（Service中的一个方法）中，如果既有读操作，又有写操作。因为写操作一定要在MySQL上，所以在一个事务中所有的数据来源都只能来自于一个MySQL（主MySQL）。

- MySQL主从原理

点击查看源网页

要完成主从同步，必须让在主机上执行的所有DML和DDL能够正确地在从机上再执行一遍（MySQL使用文件来记录SQL）。
①把在主机上的bin-log（二进制文件）打开，bin-log文件就可以记录在MySQL上执行的所有DML、DDL、TCL。
MySQL使用被动注册的方式来让从MySQL请求同步主MySQL的bin-log。被动注册的方式，主MySQL不需要知道有哪些从MySQL，额外添加/移除从MySQL对主MySQL的正常运行没有任何影响。
②从MySQL后台一个线程发送一个请求，到主MySQL请求更新bin-log某一行之后的数据。
③主MySQL后台一个线程接收到从MySQL发送的请求，读取bin-log文件中指定的内容，放在从MySQL的请求响应中。
④从MySQL的请求带回同步的数据，写在从MySQL的relay-log中。
⑤从MySQL后台一个线程专门用于从relay-log中读取同步回来的SQL，并写入到从MySQL中，完成同步。

- MySQL主从配置

添加一个新的MySQL实例：
1. 复制MySQL安装目录。
2. 修改my.ini配置信息（端口port、安装目录basedir、数据目录datadir等）。
3. 创建MySQL数据目录（从MySQL安装目录中拷贝data文件夹）。
4. 创建服务：mysqld install MySQL2 --defaults-file="<新的配置文件的地址>"
5. 启动服务。
配置主从：
1. 通过主MySQL备份，将开启bin-log之前的数据同步到从MySQL。
2. 配置主MySQL的my.ini：
  1. server-id：数据库服务的唯一标识，一般设置服务器IP的末尾号。
  2. log-bin=master-bin：开启二进制文件，master-bin是二进制文件的名字前缀。
  3. log-bin-index=maxter-bin.index：开启二进制文件的索引。
3. 启动主MySQL，执行show master status。
4. 配置从MySQL的my.ini：
  1. server-id
  2. relay-log=slave-relay-bin
  3. relay-log-index=slave-relay-bin.index
5. 启动从MySQL。
6. 备份到从MySQL。
7. 在从MySQL中配置主从：
```
change master to master_host='127.0.0.1',    # Master服务器IP
                 master_port=3306,    # Master服务器端口
                 master_user='root',    # Master服务器同步账户
                 master_password='root',    # Master同步账户的密码
                 master_log_file='master-bin.000001',    # Master服务器产生的日志
                 master_log_pos=0;    # 指定请求同步Master的bin-log的哪一行之后的内容
```
8. 启动从MySQL：start slave。

- MySQL读写分离

需求：在后台的登录日志中，让登录日志的查询从数据库中查询，其他业务还是都使用主数据库。
一个Service方法必须定位到一个唯一的数据库上。
引入路由DataSource之后：
- 在应用中，需要自己去确定（告诉路由DS）要访问的真实的DataSource。
- 让路由DS知道有哪些真实的DataSource和它们对应的名字。
- 让路由DS根据传入的名字去返回真实的DataSource。
Spring的AbstractRoutingDataSource：
- targetDataSource：配置真实的DataSource，key是DS的名字，value是DS对象。
- defaultTargetDataSource：默认的DataSource。
- abstract Object determineCurrentLoopupKey()：需要实现的方法，返回DS的名字。
- 可以使用ThreadLocal、AOP、Annotation等方式实现。