一、基础知识：

1.1 SQL分类：

• 数据查询语言(DQL-Data Query Language)

  带有select关键字的都是查询语句

• 数据操纵语言(DML-Data Manipulation Language)

  DML操作的是表中的数据。比如：insert、delete、update

• 数据定义语言(DDL-Data Definition Language)

  DDL主要操作的是表的结构。比如： create、drop、alter

• 事务控制语言(TCL-Transactional Control Language)

  事务提交：commit 事务回滚：rollback

• 数据控制语言(DCL-Data Control Language)

  比如： 授权grant、撤销权限revoke

1.2 条件查询：

特别注意：

1. 在数据库当中null不能使用等号进行衡量。需要使用is null
2. and和or同时出现，and优先级较高。如果想让or先执行，需要加“小括号”

1.3 排序：

排序采用 order by 子句， order by 后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔， order by 默认采用升 序，如果存在 where 子句那么 order by 必须放到 where 语句的后面

升序： asc

降序： desc

1.4 单行处理函数:

1.5 聚合函数/分组函数：

特别注意：

1. 分组函数自动忽略NULL，不需要提前对NULL进行处理。
2. count(具体字段)：表示统计该字段下所有不为NULL的元素的总数。 count(*)：统计表当中的总行数。（只要有一行数据count则++）
3. 分组函数不能够直接使用在where子句中。

1.6 *分组查询：

select 
    ...
from
    ...
where
    ...
group by
    ...
having
    ...
order by
    ...

执行顺序：

1. from
2. where
3. group by
4. having
5. select
6. order by

关于having的说明：:

使用having可以对分完组之后的数据进一步过滤。having不能单独使用，having不能代替where，having必须和group by联合使用。

where和having的区别： 一个是在分组前执行，一个是在分组后执行，where和having，优先选择where，where实在完成不了了，再选择having。

1.7 连接查询：

根据表连接的方式分类：

• 内连接：
  • 等值连接 join
  • 非等值连接
  • 自连接
• 外连接：
  • 左外连接 (left join)
  • 右外连接 (right join)

当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是两张表条数的乘积，这种现象被称为：笛卡尔积现象。（笛卡尔发现的，这是一个数学现象。）

1.8 子查询:

• where后面的子查询
• from后面的子查询
• select后面的子查询 [了解]

1.9 union合并：

union的效率要高一些。

1.10 limit分页：

limit startIndex,length

第一个参数代表取数据的起始下标，默认从0开始

第二个参数代表取多少个数据

1.11 *日期：

mysql的日期格式： %Y 年 %m 月 %d 日 %h 时 %i 分 %s 秒

str_to_date函数可以把字符串varchar转换成日期date类型数据

• str_to_date('字符串日期', '日期格式')

date_format函数通常使用在查询日期方面。设置展示的日期格式。

• date_format(日期类型数据, '日期格式')

date是短日期：只包括年月日信息。 datetime是长日期：包括年月日时分秒信息。

mysql短日期默认格式：%Y-%m-%d mysql长日期默认格式：%Y-%m-%d %h:%i:%s

二、表相关：

2.1 添加、修改、删除表结构：

2.2 *约束:

• 非空约束：not null
• 唯一性约束: unique
• 主键约束: primary key （简称PK）
• 外键约束：foreign key（简称FK）
• 检查约束：check（mysql不支持，oracle支持）

2.2.1 非空约束：

​ 非空约束not null约束的字段不能为NULL。只有列级约束，没有表级约束！

create table t(
        id int,
        name varchar(255) not null  // not null只有列级约束，没有表级约束！
);

2.2.2 唯一性约束：

​ 唯一性约束unique约束的字段不能重复，但是可以为NULL。

create table t(
        id int,
        name varchar(255) unique,   #列级约束
        email varchar(255)
);

create table t(
        id int,
        name varchar(255),
        email varchar(255),
      unique(name,email)  #表级约束。
);

#unique 和not null可以联合
create table t(
            id int,
            name varchar(255) not null unique  #一个字段同时被not null和unique约束的话,该字段自动变成主键字段
);

2.2.3 主键约束：

主键值是每一行记录的唯一标识。

create table t(
            id int primary key,  #列级约束  单一主键
            name varchar(255)
);


create table t(
            id int,
            name varchar(255),
            email varchar(255),
            primary key(id,name)   # 使用表级约束添加复合主键
);

实际开发中一般只使用单一主键。

一张表，主键约束只能添加1个（主键只能有1个）

2.2.4 外键约束：

外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必须来源于参照的表的字段。

三、存储引擎：

3.1 MyISAM存储引擎：

MyISAM索引文件和数据文件是分离的（非聚集）。

使用三个文件表示每个表： ​ 1. 格式文件 — 存储表结构的定义（mytable.frm） ​ 2. 数据文件 — 存储表数据（mytable.MYD） ​ 3. 索引文件 — 存储表索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。 ​
​ 对于一张表来说，只要是主键，或者加有unique约束的字段上会自动创建索引。

MyISAM存储引擎特点： 可被转换为压缩、只读表来节省空间 这是这种存储引擎的优势！！！！ MyISAM不支持事务机制，安全性低。

3.2 InnoDB存储引擎：

聚集

​ 表数据文件本身就是按B+树组织的一个索引结构文件

​ 聚集索引--叶子节点包含了完整的数据记录

​ 这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。 ​ InnoDB支持事务，支持数据库崩溃后自动恢复机制。 ​ InnoDB存储引擎最主要的特点是：非常安全。

它管理的表具有下列主要特征： – 每个 InnoDB 表在数据库目录中以.frm 格式文件表示 – InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引。） – 提供一组用来记录事务性活动的日志文件 – 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理 – 提供全 ACID 兼容 – 在 MySQL 服务器崩溃后提供自动恢复 – 多版本（MVCC）和行级锁定 – 支持外键及引用的完整性，包括级联删除和更新

InnoDB最大的特点就是支持事务： 以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读， 不能很好的节省存储空间。

3.2.1 InnoDB页结构：

Innodb基本单位页结构：

Buffer Pool 原理：

​ Buffer Pool用来缓存客户端可能要频繁访问的数据，基本单位是页，下图中绿色的页表示当前页缓存了磁盘中的某一页数据，白色的页表示Buffer Pool的空闲区，黄色的页是代表了该缓存的页被客户端修改了，此时的数据状态和磁盘对应的文件不一致。

三个链表：

​ Free链表是记录了Buffer Pool中当前空闲的区域，当有查询请求触发了从磁盘加载某一页数据时，会同时将该数据缓存起来。

​ Flush链表记录了被修改过的页，后台会有线程定时将数据刷新到磁盘，保证数据一致性。

​ LRU链表是用来记录和更新频繁访问的页数据，从而实现高效的数据访问性能。

​ 对于像全表查询这种请求，会严重影响LRU的性能，所以在MySQL中的LRU链表分为了两部分，热数据区域和冷数据区域，这也就涉及到一个问题，冷热数据的变更，在MySQL规定了一个阈值：1秒，只有在冷数据区域的数据，临近两次访问的时间间隔大于1秒，数据才会被提到热数据区域。而对于全表查询这种请求，会在短时间内频繁访问某一页，所以它并不会被提取到热数据区域，从而避免了全表查询引起LRU大面积缓存失效。

3.3 MEMORY存储引擎

​ 使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定， ​ 这两个特点使得 MEMORY 存储引擎非常快。

MEMORY 存储引擎管理的表具有下列特征： – 在数据库目录内，每个表均以.frm 格式的文件表示。 – 表数据及索引被存储在内存中。（目的就是快，查询快！） – 表级锁机制。 – 不能包含 TEXT 或 BLOB 字段。

MEMORY 存储引擎以前被称为HEAP 引擎。

MEMORY引擎优点：查询效率是最高的。不需要和硬盘交互。 MEMORY引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。

四、事务：

查看当前事务级别：

select @@tx_isolation;

一个事务其实就是一个完整的业务逻辑,是一个最小的工作单元。不可再分。

与事务相关的操作是DML语句，也就是insert、update、delete

4.1 事务的四大特性;

A：原子性
    说明事务是最小的工作单元。不可再分。

C：一致性
    所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，
    以保证数据的一致性。

I：隔离性
    A事务和B事务之间具有一定的隔离。
    教室A和教室B之间有一道墙，这道墙就是隔离性。
    A事务在操作一张表的时候，另一个事务B也操作这张表会那样？？？

D：持久性
    事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据
    保存到硬盘上！

4.2 隔离性的四个级别：

假设现在有两个事务，事务A和事务B。

• 读未提交 read uncommitted（最低的隔离级别）

  A事务执行过程中，B事务读取了A事务的修改。但是由于某些原因，A事务可能没有完成提交，发生RollBack了操作，则B事务所读取的数据就会是不正确的。这个未提交数据就是脏读。

  存在问题：

  ​ 脏读现象

• 读已提交：read committed （ORACLE默认级别）

  在读已提交这个级别下，事务A只能读取到事务B提交之后的数据。

  解决了脏读现象的问题

  存在问题：

  ​ 不可重复读现象：在当前事务开启后，对于同一条查询语句，在当前事务中的不同时间点执行后出现了结果不一致的现象，称为不可重复读，因为在该隔离级别下，两次查询的间隔中有可能存在其他事务对该表进行了修改操作，并且事务完成提交了，所以会出现这种情况。

• 可重复读：repeatable read （MySQL默认级别）

  在事务A开启后，在事务中的任何时间点，读取到的数据都是一致的。即使中途有其他事务对表做了修改操作并且提交了，事务A读取到的数据还是没有发生改变，这就是可重复读。

  解决了不可重复读问题

  存在问题：

  ​ 幻读现象：B事务读取了两次数据，在这两次的读取过程中A事务添加了数据，B事务的这两次读取出来的集合不一样。

• 序列化/串行化：serializable（最高的隔离级别）

  这种隔离级别最高，效率最低，解决了上面的所有问题。 它可以理解为事务排队执行，不能并发

  通常数据库不会用这个隔离级别，我们需要其他的机制来解决这些问题:乐观锁和悲观锁。

4.3 MVCC机制：

MySQL中 InnoDB实现了MVCC的事务并发处理机制

多版本并发控制：

MVCC提供了时间一致性的处理思路，在MVCC下读事务时，通常使用一个时间戳或者事务ID来确定访问哪个状态的数据库及哪些版本的数据。

• ReadView：一致性视图

• DB_TRX_ID: 6Byte InnoDB中每个事务有一个唯一的事务ID叫做 transaction id。在事务开始时向InnoDB事务系统申请得到，是按申请顺序严格递增的

• Roll point：回滚指针，7Byte，指向当前记录的ROLLBACK SEGMENT 的undolog记录，通过这个指针获得之前版本的数据。该行记录上所有旧版本在 undo log 中都通过链表的形式组织。

• DB_ROW_ID：如果声明了主键，InnoDB以用户指定的主键构建B+Tree，如果未声明主键，InnoDB 会自动生成一个隐藏主键，说的就是DB_ROW_ID

  另外，每条记录的头信息（record header）里都有一个专门的bit（deleted_flag）来表示当前记录是否已经被删除

要实现read committed在另一个事务提交之后其他事务可见和repeatable read在一个事务中SELECT操作一致，就是依靠ReadView，对于read uncommitted，直接读取最新值即可，而serializable采用加锁的策略通过牺牲并发能力而保证数据安全，因此只有RC和RR这两个级别需要在MVCC机制下通过ReadView来实现。

在read committed级别下，readview会在事务中的每一个SELECT语句查询发送前生成（也可以在声明事务时显式声明START TRANSACTION WITH CONSISTENT SNAPSHOT），因此每次SELECT都可以获取到当前已提交事务和自己修改的最新版本。

而在repeatable read级别下，每个事务只会在第一个SELECT语句查询发送前或显式声明处生成，其他查询操作都会基于这个ReadView，这样就保证了一个事务中的多次查询结果都是相同的，因为他们都是基于同一个ReadView下进行MVCC机制的查询操作。

InnoDB为每一个事务构造了一个数组m_ids用于保存一致性视图生成瞬间当前所有活跃事务(开始但未提交事务)的ID，将数组中事务ID最小值记为低水位min_id，当前系统中已创建事务ID最大值+1记为高水位max_id

一致性视图下查询操作的流程如下:

1. 当查询发生时根据以上条件生成ReadView，该查询操作遍历Undo log链，根据当前被访问版本(可以理解为Undo log链中每一个记录即一个版本，遍历都是从最新版本向老版本遍历)的DB_TRX_ID，如果DB_TRX_ID小于min_id,则该版本在ReadView生成前就已经完成提交，该版本可以被当前事务访问。DB_TRX_ID在绿色范围内的可以被访问

2. 若被访问版本的DB_TRX_ID大于max_id，说明该版本在ReadView生成之后才生成，因此该版本不能被访问，根据当前版本指向上一版本的指针DB_ROLL_PT访问上一个版本，继续判断。DB_TRX_ID在蓝色范围内的都不允许被访问

3. 若被访问版本的DB_TRX_ID在[min_id,max_id)区间内，则判断DB_TRX_ID是否等于当前事务ID，等于则证明是当前事务做的修改，可以被访问，否则不可被访问, 继续向上寻找。只有DB_TRX_ID等于当前事务ID才允许访问橙色范围内的版本

4. 最后，还要确保满足以上要求的可访问版本的数据的delete_flag不为true，否则查询到的就会是删除的数据。

一致性读和当前读:

当前读会触发视图更新

4.4 事务底层原理:

4.4.1事务执行过程：

1. 首先执行查询语句，如果数据不在buffer pool中则需要从磁盘中加载数据到buffer pool
2. 修改buffer pool里面的页数据-------（这时磁盘和缓冲区的数据是不一致的）
3. 根据相应的DML语句生成redo log --> log bufer
4. redo log 持久化（有三种方案：0：不立即进行持久化，由后台线程去做；1： 写到操作系统缓存区，然后立即持久化到磁盘；2：写到操作系统缓存区，然后定时刷新到磁盘）
5. bin log 持久化
6. undo log 持久化
7. 操作成功，事务提交。

4.4.2 三种log的区别：

• Redo log: InnoDB引擎中的概念，记录了页中的具体行被修改的数据信息
• Bin log: MySQL中的概念，记录的是sql语句， 比如在主从复制的时候，只能依赖于Bin log文件
• Undo log: 记录事务执行过程前的数据状态信息，在roolback操作时 使用Undo log文件

4.5 锁机制：

五、索引：

5.1 索引操作：

创建索引：

create index index_name on table_name(column_name)

删除索引：

drop index index_name on table_name

5.2 索引失效：

• 查询条件使用了百分号在第一位的模糊查询

  select * from user where user_name like '%k';

• 使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会失效。

• 使用复合索引的时候，没有使用左侧的列查找，索引失效。

• 在where当中索引列参加了运算，索引失效。

  select * from score where score+1 = 61;

• 在where当中索引列使用了函数。

  select * from user where lower(user_name)='jack';

5.3 索引数据结构:

5.3.1 二叉树(搜索二叉树)：

对于单边增长的数据来说，用搜索二叉树和全表扫描效率一样，所以mysql没有选择这种数据结构

5.3.2 红黑树(一种平衡二叉树)：

数据量大的时候，树的高度不可控

5.3.3 Hash：

优点：

1. 键值对唯一，速度快

不足：

1. 无法支持范围查询
2. 存在hash冲突的问题：  解决方法有： 开放定址法、链地址法
3. 不支持队列联合索引
4. 无法利用索引完成排序，以及like这样的部分模糊查询

5.3.4 B树：

不支持范围查询，非叶子节点存储data‘数据占用空间

5.3.5 MySQL中的B+树：

B+树在B树的基础上做了改变。多叉平衡二叉树

• 叶子节点存储所有索引字段
• 叶子节点多了一个双向指针，可以支持范围查询。
• 非叶子节点不存储data，只存储索引
• 叶子节点用指针连接，提高区间访问的性能

1. 为什么推荐必须建主键？

如果没有主键的话，mysql会自动帮表建一个隐式的字段，还要帮忙维护，增加数据库压力

2. 为什么推荐使用自增主键？

   B+树叶子节点本身是有序的排列，如果是自增的话，只会一直在末尾加节点，不是自增的话，可能会引起树的结构发生很大的变化。

5.4 联合索引：

联合索引的底层数据结构？

联合索引的排序是怎么排的？

​ 按照最左匹配原则，逐个字段比较。

5.5 一张表建立多个索引：

在Innodb中，我们在数据库中会根据主键索引建立一棵B+树，data字段是包含了表中的所有数据（聚集索引）。如果还有其他字段的索引或者是联合索引的话，对于每一个索引都会建立一个B+树，但是其他树的data并不是包含表中的所有数据（非聚集索引），因为这样的话相当于是将一张表的数据拷贝的多份，既浪费资源同时在做更新操作时又会增加维护成本，所以在MySQL中，其他的索引的data字段其实是绑定了主键，通过索引找到主键，然后再基于主键索引查找完整的记录。这就是回表查询。

六. 数据库三范式：

6.1 第一范式：

要求任何一张表必须有主键，每一个字段原子性不可再分。

6.2 第二范式：

建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。

6.3 第三范式：

建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

七. MySQL优化：