黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mys

## 什么是数据库, 数据库管理系统, 数据库系统, 数据库管理员?

* 数据库：是长期储存在计算机内、有组织的、可共享的大量**数据的集合**。

* 数据库管理系统（DBMS）：例如mysql，是**位于用户与操作系统之间**的一层数据管理软件。DBMS能够科学地组织和存储数据、高效地获取和维护数据。DBMS具有数据定义，数据组织、存储和管理，数据库的事务管理和运行管理（安全性等），数据库的建立和维护等功能。

* 数据库系统（DBS）：数据库系统由**数据库、数据库管理系统（及其应用开发工具）、应用程序、数据库管理员**四部分构成。

- 数据库管理员 : 数据库管理员 (DBA) 负责全面管理和控制数据库系统。

## 通用语法及分类

- DDL: 数据定义语言，用来定义数据库对象（数据库、表、字段）

- DML: 数据操作语言，用来对数据库表中的数据进行增删改

- DQL: 数据查询语言，用来查询数据库中表的记录

- DCL: 数据控制语言，用来创建数据库用户、控制数据库的控制权限

## ER图

由实体，属性，联系组成

## 三范式

- **1NF(第一范式)：属性不可再分。**

 

- **2NF(第二范式)：1NF 的基础之上，消除了非主属性对于码的部分函数依赖。**

 - **码**：唯一标识实体的列。

 - **候选码**：若关系中的某一属性或属性组的值能唯一的标识一个元组，而其任何、子集都不能再标识，则称该属性组为候选码。

 - **非主属性**：不是候选码的属性。

 - **函数依赖**：在属性X 的确定的情况下，必定能确定属性 Y 的值，那么就可以说 Y 函数依赖于 X。

 - **部分函数依赖**：Y依赖X，且Y还依赖X的一部分（真子集）。

 > 表中任意一个主键（或联合主键），可以确定除该主键外的所有的非主键值。

 >

 > 根据主键或者联合主键,将一个表最有关联的属性分别放在一起

- **3NF(第三范式)：3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖 。**

  如在关系 R(学号 , 姓名, 系名，系主任)中，学号 → 系名，系名 → 系主任，所以存在非主属性系主任对于学号的传递函数依赖。

## 数据库设计通常分为哪几步?

1. **需求分析** : 分析用户的需求，包括数据、功能和性能需求。

2. **概念结构设计** : 主要采用 E-R 模型进行设计，包括画 E-R 图。

3. **逻辑结构设计** : 通过将 E-R 图转换成表，实现从 E-R 模型到关系模型的转换。

4. **物理结构设计** : 主要是为所设计的数据库选择合适的存储结构和存取路径。

5. **数据库实施** : 包括编程、测试和试运行

6. **数据库的运行和维护** : 系统的运行与数据库的日常维护。

## MYSQL

1. 默认端口号是**3306**。

2. 当前默认的存储引擎是 InnoDB

3. 采用的是 **插件式架构**，存储引擎是基于表的，而不是数据库，因此可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。

4. MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎。区别：

  1. MyISAM 只有表级锁，InnoDB 支持行级锁和表级锁

  2. MyISAM 不提供事务支持。InnoDB 提供事务支持

  3. MyISAM 不支持外键，而 InnoDB 支持外键

  4. MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持

  5. InnoDB 的性能比 MyISAM 更强大

## 事务的 ACID 特性

1. **原子性**（`Atomicity`）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

2. **一致性**（`Consistency`）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；一致性是AID的目的。

3. **隔离性**（`Isolation`）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

4. **持久性**（`Durability`）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

## 并发事务带来了哪些问题?

1. 脏读：读到了另一个事务修改但没有提交的数据，之后另一个事务又回滚，则读到的是脏数据。

2. 不可重复读：一个事务内多次读同一数据值不一样（被另一个事物修改并提交）。

3. 幻读：一个事物内多次读取数据时发现查到的记录增加了。（是不可重复读的一种情况）

4. 丢失修改：两个事务都修改同一数据，导致第一个事物修改的丢失。

## 事物隔离级别

并发事务隔离级别：

| 隔离级别 | 脏读 | 不可重复读 | 幻读 |

| ------------ | ------------ | ------------ | ------------ |

| Read uncommitted | √ | √ | √ |

| Read committed | × | √ | √ |

| Repeatable Read(默认) | × | × | √ |

| Serializable | × | × | × |

## 当前读和快照读

**当前读：**读取的是记录的最新版本，会对读取的记录进行加锁（保证其他并发事务不能修改当前记录）。对于select ... lock in share mode(共享锁)，select ... for update、 update、insert、delete(排他锁)都是一种当前读。

**快照读：**简单的select (不加锁) 就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

- Read Committed: 每次select，都生成一个快照读

- Repeatable Read:开启事务后第一个select语句才是快照读的地方

- Serializable:快照读会退化为当前读

## 锁

* **分类**

按照粒度分：

1. 全局锁：锁数据库中所有表。使用场景：全库备份。

2. 表级锁：锁整张表

3. 行级锁：锁对应行

### 共享锁和排他锁

不论是表级锁还是行级锁，都存在共享锁和排他锁

> 排他锁与任何的锁都不兼容，S仅和S兼容。

- 共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁。

- 排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁。

**意向共享锁和意向排他锁**

意向锁是由数据引擎自己维护的，无法手动操作，在为数据行加共享/排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。意向锁是表级锁，共有两种：意向共享锁（Intention Shared Lock，IS 锁）和意向排他锁（Intention Exclusive Lock，IX 锁。

> 意向锁之间互不排斥，但除了 IS 与 S 兼容外，意向锁会与 S/ X 互斥。

* IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。

* 意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。

### 表级锁和行级锁

- 表级锁：对整张表加锁，针对非索引字段加锁，简单，开销少，加锁快，不会出现死锁，易冲突。

行级锁：粒度最小的锁，针对**索引**字段。会发生死锁。当 UPDATE、DELETE 时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。

 **行级锁分类**：

- 记录锁：单个行记录上的锁

- 间隙锁：锁定一个范围，不包括记录本身

- 临键锁：锁定一个范围，包含记录本身

##MySQL优化

1. 避免关联操作

  - 经常一起使用的列放到一个表中

  - 不要过分追求满足第三范式

  - 在多表 join 的关联列建立索引

2. 避免使用 TEXT 类型

  - MySQL 内存临时表不支持 TEXT这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。而且对于这种数据，MySQL 还是要进行二次查询，会使 sql 性能变得很差。

3. 尽可能把所有列定义为 NOT NULL，要用""

  - 索引 NULL 列需要额外的空间来保存，所以要占用更多的空间；

  - 进行比较和计算时要对 NULL 值做特别的处理。

4. 使用 TIMESTAMP(4 字节) 或 DATETIME 类型 (8 字节) 存储时间

  - 方便使用日期函数进行计算和比较

  - 节省空间

  - Timestamp 和时区有关，在不同时区，查询到同一个条记录此字段的值会不一样。

  - 表示的时间范围：

  - DateTime：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

  - Timestamp：1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

  - 时间戳也是一个选择，4字节，缺点是可读性不高。

   

5. 金额类数据必须使用 decimal 类型

  - 在计算时不会丢失精度

  - 占用空间由定义的宽度决定，可存储比 bigint 更大的整型数据

6. 禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询

7. 减少同数据库的交互次数

8. 避免隐式数据类型转换

  - 当操作符左右两边的数据类型不一致时，会发生隐式转换。

  - 隐式转换会导致索引失效

9. 超 100 万行的批量写操作,要分批多次进行操作

  - 会造成主库与从库长时间的延迟情况

## 索引

### 索引分类：

**InnoDB**

1. 主键索引：设定为主键后数据库会自动建立索引，innodb为聚效索引：MySQL每张表中都必须有一个主键索引，在InnoDB的实现中，把主键作为关键字组织到B+树的各个节点上，而叶子节点上存储的是主键列的值和对应的整行数据。

2. 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

3. 唯一索引：索引列的值必须唯一，但允许有空值（但只能存在一个null）。

4. 复合索引：即一个素引包含多个列。遵循最左前缀法则：索引为 name age sex，会匹配（name），（name age）, （name age sex）。

**MyISAM**

全文索引：目前只有 `CHAR`、`VARCHAR` ，`TEXT` 列上可以创建全文索引。一般不会使用。

## 二级索引

在使用中为了提高查询效率，开发者会根据查询条件自己创建一些索引，而这些索引就叫作二级索引。二级索引又称为辅助索引.

**InnoDB**中的二级索引的叶子结点中存的是索引列的值和主键值，所以在使用二级索引查询的时候，首先通过二级索引查找到主键值，然后再根据主键值到主键索引的叶子结点中查到对应的整行数据。

**MyISAM**的二级索引的叶子节点中保存的是指向物理数据的指针，因此它的主建索引和二级索引的结构并没有任何区别，只是说主键索引的索引值是唯一且非空的。

唯一索引，普通索引，前缀索引，全文索引属于二级索引。

### 二级索引不一定要回表

如果一个索引包含所有需要查询的字段的值，我们就称之为 **覆盖索引**，要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

## 聚簇索引与非聚簇索引

> 聚簇索引和非聚簇索引本质的区别就是B+树的叶子节点上存储的是行数据还是行数据的地址(行号)。

### 聚簇索引：

索引结构和数据一起存放的索引。InnoDB中每张表有且仅有一个聚簇索引(就是主键索引)

- **优点：**

1. 速度快

- **缺点：**

1. 依赖于有序的数据

2. 更新代价大

### 非聚簇索引：

索引结构和数据分开存放的索引，InnoDB中的二级索引是非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

- **优点**

1. 更新代价比聚簇索引要小

- **缺点**

1. 依赖有序的数据

2. 可能会回表

## 索引优化

### 1. 选择合适的字段创建索引

- 不为 NULL 的字段：对于数据为 NULL 的字段，数据库较难优化。

- 被频繁查询的字段。

- 被作为条件查询的字段。

- 频繁需要排序的字段。

- 被经常频繁用于连接的字段。

### 2. 频繁修改的字段不宜建立索引

### 3. 限制每张表上的索引数量

  建议单张表索引不超过 5 个，MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。

### 4. 尽可能的考虑建立联合索引而不是单列索引

可以节省磁盘空间，提高修改效率

### 5. 注意避免冗余索引

### 6. 避免索引失效

- 创建了组合索引，但查询条件未遵守最左匹配原则;

- 在索引列上进行计算、函数、类型转换等操作;

- 发生隐式转换;

### 7.使用 EXPLAIN 命令来分析 SQL 的 执行计划 

执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。

##