mysql 学习、复习资料整理详细

1、数据库基础

1.1 数据库设计遵循的原则

 设计数据表的时候，要考虑很多的问题:
  （1） 用户需要哪些数据，我们在数据表中要保存哪一些数据
  （2）怎么保证数据表中的数据的正确性
  （3）如何降低数据表的冗余度
  （4）开发人员怎么才能更方便的使用数据库
 如果数据库设计得不合理的话，可能导致下面的几种问题:
  （1）设计容易，信息重复，存储空间浪费
  （2）数据更新，插入，删除的异常
  （3）不能正确表示信息
  （4）丢失有效信息
  （5）程序性能差
 我们可以看出设计良好的数据库是很重要的，它有下面的优点:
  （1）节省数据的存储空间
  （2）能够保证数据的完整性
  （3）方便进行数据库应用系统的开发
 设计数据库，我们得重视数据表的设计，为了建立冗余度小，结构合理的数据库，设计数据库必须遵循一定的规则。

1.2 数据库范式

  关系型数据库中，关于数据表设计的基本原则，规则就称为范式，范式是我们在设计数据库结构过程中需要遵循的规则和指导方法。

范式的详细介绍，参考链接:数据库的三大范式

1.2 数据库完整性的实现

1. 数据库表设计遵循三大范式原则 ；
2. 可以使用约束实现数据完整性，一个约束只能对一个表的一个列产生作用，如
   
3. 可以使用规则和默认值，规则和默认值在创建了以后，它不属于某个表，只有在绑定了命令了以后才会发生关系，可以绑定到多个表多个列中。
   
   

详细描述参考链接：数据库—怎样实现数据完整性

2、mysql特点

1. 功能强大：MySQL提供了多种数据库存储引擎，各个引擎各有所长，适用于不同应用场合。用户可以选择最合适的引擎以得到最高性能，这些引擎升值可以应用处理每天访问量数亿的高强度Web搜索站点。MySQL支持事务、视图、存储过程和触发器等。
2. 支持跨平台：MySQL支持至少20种以上的开发平台，包括Linux、Windows、FreeBSD、IBMAIX、AIX和FreeBSD等。这使得在任何平台下编写的程序都可以进行移植，而不需要对程序做任何修改。
3. 运行速度快：高速是MySQL的显著特性。在MySQL中，使用了极快的B书磁盘表（MyISAM）和索引压缩；通过使用优化的单扫描多连接，能够极快的实现连接；SQL函数使用高度优化的类库实现，运行速度极快。
4. 支持面向对象：PHP支持混合编程方式。编程方式可分为纯粹面向对象、纯粹面向过程、面向对象与面向过程混合3种方式。
5. 安全性高；灵活安全的权限和密码系统允许主机的基本验证。连接到服务器时，所有的密码传输均采用加密形式，从而保证了密码的安全。
6. 成本低：MySQL数据库是一种完全免费的产品，用户可以直接从网上下载。
7. 支持各种开发语言：MySQL为各种流行的程序设计语言提供支持，为他们提供了很多的API函数。
8. 数据库存储容量大：MySQL数据库的最大有效容量通常是由操作系统对文件大小的限制决定的，而不是由MySQL内部限制决定的。InnDB存储引擎将InnDB表保存在一个表空间内，该表空间可由数个文件创建，表空间的最大容量为64TB,可以轻松处理拥有上万条记录的大型数据库。
9. 支持强大的内置函数：PHP中提供了大量内置函数，几乎涵盖了Web应用开发中的所有功能。它内置了数据连接、文件上传等功能，MySQL支持大量的扩展库，如MySQLi等，为快速开发Web应用提供方便。

3、事务

  事务指的是一组命令操作，在执行的过程中，要么全部成功，要么全部失败。
  由引擎层支持事务，MyISAM就不支持事务，而InnoDB是支持事务的。

3.1 事务的四大特性 – ACID

 事务具有以下四大特性（ACID）：

1. 原子性（Atomicity）：
     指事务不可分割，要么全部成功，要么全部失败，不可能存在部分成功或部分失败的情况。如果执行某一条语句失败后，将会触发之前所有执行过的语句的回滚，因此靠的是undo log。
2. 一致性（Consistency）：
     在事务执行前后，数据的完整性没有遭到破坏。一致性是mysql追求的最终目标，需要数据库层面与应用层面同时来维护。需要先满足原子性、隔离性与持久性，同时也需要应用层面做保障，即在应用层面对数据进行检验。
3. 隔离性（Isolation）：
     事务之间是隔离的，并发执行的事务之间不存在互相影响，mysql通过锁以及MVCC来保证隔离性。
4. 持久性（Durability）：
     事务一旦提交，那么对数据的操作就是永久性的，即使接下来数据库宕机也不会有影响。mysql是通过redo log来实现宕机恢复的，而binlog主要是用来误删恢复与主从复制的。

3.2 并发事务问题

并发事务问题就是多个事务同时执行的时候出现的问题

  当事务存在并发时，就会产生以下问题：

1. 脏读：即读取到别的事务未提交的数据。
     例如：A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。这种情况常发生于转账与取款操作中。
2. 不可重复读：即某个事务前后多次读取，数据内容不一致。
     例如：事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据，比如此时读取了小明的年龄为20岁，事务B执行更改操作，将小明的年龄更改为30岁，此时事务A第二次读取到小明的年龄时，发现其年龄是30岁，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，成为不可重复读。
3. 幻读：即某个事务前后多次读取，读到的数据总量不一致。
     例如：事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据，称为幻读。

3.3 事务的四大隔离级别

事务隔离级别就是多个事务执行时，互不影响的程度，事务隔离级别不同，出现的事务并发问题就不一样。

  事务隔离级别，就是在不同程度上解决上述脏读、不可重复读、幻读的问题。有四种隔离级别，分别是

1. 读未提交（Read Uncommitted）：
     在这种隔离级别下，所有事务能够读取其他事务未提交的数据。读取其他事务未提交的数据，会造成脏读。因此在该种隔离级别下，不能解决脏读、不可重复读和幻读。读未提交可能会产生脏读的现象，那么怎么解决脏读呢？那就是使用读已提交。
2. 读已提交（Read Committed）：
     在这种隔离级别下，所有事务只能读取其他事务已经提交的内容。能够彻底解决脏读的现象。但在这种隔离级别下，会出现一个事务的前后多次的查询中却返回了不同内容的数据的现象，也就是出现了不可重复读。
     这是大多数数据库系统默认的隔离级别，例如Oracle和SQL Server，但mysql不是。已提交可能会产生不可重复读的现象，我们可以使用可重复读。
3. 可重复读（Repeatable Read）：
     在这种隔离级别下，所有事务前后多次的读取到的数据内容是不变的。也就是某个事务在执行的过程中，不允许其他事务进行update操作，但允许其他事务进行add操作，造成某个事务前后多次读取到的数据总量不一致的现象，从而产生幻读。
     这才是mysql的默认事务隔离级别可重复读依然会产生幻读的现象，此时我们可以使用串行化来解决。
4. 串行化（Serializable）：
     在这种隔离级别下，所有的事务顺序执行，所以他们之间不存在冲突，从而能有效地解决脏读、不可重复读和幻读的现象。但是安全和效率不能兼得，串行化会大大降低数据库的性能，一般不使用这种级别。
     下面用一张表格来表示他们能够解决的问题，x代表未解决，√代表能够解决。
   

注意：隔离级别越高，数据越安全，但是性能越低，在开发时应根据实际情况选择合适的隔离级别。

当然，以上所说的隔离级别及当前级别存在的问题只是一种规范，不同的数据库厂商可以有不同的实现。
例如在mysql的可重复读的级别上，使用临键锁的方式就已经解决了幻读的问题。

3.4 事务隔离级别操作sql

1. 查看事务隔离级别：

SELECT @@TRANSACTION_ISOLATION;

默认级别为Repeatable read。

2. 设置事务隔离级别：

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL 隔离级别;

SESSION表示当前设置的隔离级别只对当前会话有效，即当前建立的数据库连接。GLOBAL表示全局有效。

3.5 事务原理 – LBCC MVCC

  mysql为了实现以上隔离级别，提出了LBCC(Lock-Based Concurrent Control，基于锁的并发控制)与MVCC(Multi-Version Concurrent Control，基于多版本的并发控制)。
  在LBCC中，读写冲突，会使用诸如记录锁、间隙锁与临键锁等锁来实现数据的并发安全，因此读写性能不高。

关于锁的分类，可以参考:谈谈锁的类型

  在MVCC中，读写不冲突，记录每一行的多个版本，来避免在多个事务之间的竞争。以空间换时间的思路，极大地提高了读写性能。
  MVCC主要靠undo log版本链与ReadView来实现。
  对于MVCC，简单来讲，就是mysql保存了一行数据在多个时间点的快照，是一种使用空间换取时间的策略，能做到读(快照读，可以理解就是普通的select语句)写不加锁。你可以暂时理解为，每一份快照包含了一行undo log日志，各个版本的快照。

3.4.1 行的隐藏列

  在数据库中的每一行上，除了存放真实的数据以外，还存在着3个隐藏列：row_id、trx_id与roll_pointer。

1. row_id，行号
     如果当前表有整数类型的主键，则row_id就是主键的值。
     如果没有整数类型的主键，则mysql会按照字段顺序选择一个非空的整数类型的唯一索引作为row_id。
     如果mysql没有找到，则会自动生成一个自动增长的整数作为row_id。
     那row_id和今天的MVCC有什么关系呢？只能说毫无关系。
2. trx_id，事务号
     当一个事务开始执前，mysql会为这个事务分配一个全局自增的事务id。
     之后该事务对当前行进行的增、删、改操作时，都会将自己的事务id记录到trx_id中。
3. roll_pointer，回滚指针
     事务对当前行进行改动时，会将旧数据写入进undo log中，再将新数据写入当前行，且当前行的roll_pointer指向刚才那个undo log，因此可以通过roll_pointer找到该行的前一个版本。
     当一直有事务对该行改动时，就会一直生成undo log，因此roll_pointer可以将这些不同版本的undo log串联起来，形成undo log版本链。

3.4.2 ReadView

  首先需要理解一下快照读与当前读

1. 快照读：
     简单的select查询，即不包括 select … lock in share mode, select … for update，可能会读到数据的历史版本。
2. 当前读：
     以下语句都是当前读，总是读取最新版本，会对读取的最新版本加锁。

select ... lock in share mode
select ... for update
insert
update
delete

  在事务执行每一个快照读或事务初次执行快照读时，会生成一致性视图，即ReadView。
  ReadView的作用是，判断undo log版本链中的哪些数据对当前事务可见。

3.4.3 MVCC在四种隔离级别下的区别

所以，MVCC只在RC与RR级别下工作，区别在于生成ReadView的频率不同。

3.5 undo log、binlog、redo log

详情参考文章：数据库日志：binlog、redo log、undo log详解

3.6 事务提交过程

  MySQL是通过WAL方式，来保证数据库事务的一致性和持久性，即ACID特性中的C(consistent)和D(durability)。
  WAL（Write-Ahead Logging）是一种实现事务日志的标准方法，具体而言就是:
1、修改记录前，一定要先写日志；
2、 事务提交过程中，一定要保证日志先落盘，才能算事务提交完成。
  通过WAL方式，在保证事务特性的情况下，可以提高数据库的性能。从上述流程可以看出，提交过程中，主要做了4件事情：

1. 先记录 undo/redo log，确保日志刷到磁盘上持久存储。
2. 更新数据记录，缓存操作并异步刷盘。
3. 如果开启了 binlog，将事务日志持久化到 binlog。
4. 提交事务，在 redo log 中写入commit记录。

  在 mysql 执行事务过程中如果因故障中断，可以通过 redo log 来重做事务或通过 undo log 来回滚，确保了数据的一致性。只要 binlog 没写成功，整个事务是需要回滚的，而 binlog 写成功后即使 Mysql Crash 了都可以恢复事务并完成提交。

3.7 事务的操作sql📌

1. 查看事务提交方式：SELECT @@autocommit;
   默认值为1，表示执行完SQL语句后自动提交，若在中间发生了错误，则会提交错误之前的所有SQL
2. 设置事务提交方式：SET @@autocommit = 0;
   @@autocommit值为0表示手动提交事务。
3. 提交事务：COMMIT;
   在开启事务的SQL语句执行后，手动COMMIT提交事务。注意，一旦COMMIT后就不能回滚事务，这也对应了事务特性的最后一点----持久性
4. 回滚事务：ROLLBACK;
   在事务执行完毕，没有提交的时候，可以使用ROLLBACK进行回滚事务，撤销对数据的操作。
5. 开启事务：START TRANSACTION 或 BEGIN
   开启事务后，后面对数据库的修改操作就需要手动提交(COMMIT)和回滚（ROLLBACK）。

3.8 MySQL中是如何实现事务提交和回滚的？

 为了保证数据的持久性，数据库在执行SQL操作数据之前会先记录redo log和undo log
 redo log是重做日志，通常是物理日志，记录的是物理数据页的修改，它用来恢复提交后的物理数据页。
 undo log是回滚日志，用来回滚行记录到某个版本，undo log一般是逻辑日志，根据行的数据变化进行记录。
 redo/undo log都是写先写到日志缓冲区，再通过缓冲区写到磁盘日志文件中进行持久化保存
 undo日志还有一个用途就是用来控制数据的多版本（MVCC）

简单理解就是：
redo log是用来恢复数据的，用于保障已提交事务的持久性
undo log是用来回滚事务的，用于保障未提交事务的原子性

4、数据库主从读写

4.1 数据库主从复制原理

参考链接：数据库的主从复制原理（超级详细）

4.2 数据库主从读写延迟解决方案

参考链接：数据库主从读写延迟解决方案

5、锁

5.1 锁类型

  MySQL中的锁分为表锁以及行锁。区别如下：

5.2 锁模式

  锁模式分为读锁、写锁、意向锁。区别如下：

  意向锁不会和行锁冲突，意向锁只会阻塞对表的S锁或X锁。事务1对表加IX锁，然后对记录a加X锁，事务2需要修改记录b时，并不需要判断是否存在意向锁。

5.3 加锁方法

  加锁方法分为记录锁、间隙锁、临键锁。

1. 记录锁：也称Record Lock，是行锁中最简单的实现。比如，对于表student，id为主键，name为普通索引，age为普通字段。执行下列语句时，则会锁住主键索引上id为1的索引记录，记住，锁的是索引项，并不是真正的数据行。

update student set name="tom" where id=1;

2. 间隙锁：也称Gap Lock，顾名思义，锁的是一段间隙，至于这段间隙有多大，什么时候会触发间隙锁，需要分情况讨论。在可重复读(RR)的隔离级别下，才会有间隙锁的产生。
   产生情况：
   （1）在普通索引上，不管怎么查，只要加锁，必定会产生间隙锁。
   （2）在主键索引或唯一索引上，只要记录存在，则间隙锁会变成记录锁。记录不存在，则会产生间隙锁。
3. 临键锁：也称Next-key Lock，是记录锁与间隙锁的组合，既包含索引记录，也包含索引区间。

问：select 语句什么时候行锁变表锁？
如select * from xxx where column1 = “xxx” for update
 (1)如果在column1上建立了索引，那么就只是给该行数据添加了行锁
 (2)如果在column1上没有建立索引，那么就会将行锁升级为表锁
底层原理：
  首先当你发出上锁的请求的时候，MYSQL首先会去定位需要上锁的数据所在的位置。MYSQL会将在定位目标数据过程中所有扫描到的数据进行一并加锁。也就是在INNODB加行锁的时候不仅会将该行数据进行加锁，还会将在此过程中扫描过的数据进行额外的加锁，所以如果MYSQL在找到目标数据的时候进行的是全表扫描，那么实际上就不是行锁了，而是直接将整个表给锁住了，如果是通过索引的方式确定到目标上锁的行数据，那么只会给目标数据进行上锁（因为如果使用索引查找是不用进行全表的数据遍历的）。

关于锁的详细介绍可参考:谈谈锁的类型

6、主键、外键

6.1 主键

  primary key用来唯一的约束字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键，而且主键所在的列通常是整数类型。

注意：主键所在的列不能重复，不能为空。

6.1.1 主键sql

1. 主键创建
    在创建表的时候，在字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为主键，可以使用复合主键。
2. 追加创建主键
    格式为：alter table 表名 add primary key(字段列表)
3. 删除主键
   格式为：alter table 表名 drop primary key;

6.1.2 主键自增设置

  在数据库应用中，经常希望在每次插入新纪录时，系统自动生成字段的主键值。可以通过为表主键添加​​AUTO_INCREMENT​​关键字来实现。
   默认情况下，在MYSQL中​​自增id​​​的初始值是1，每新增一条记录，字段值自动加1.
  一个表只能有一个字段属用​​AUTO_INCREMENT​​​约束，且该字段必须为主键的一部分。​​AUTO_INCREMENT​​​约束的字段可以是任何整数类型(​​TINTINT、SMALLINT、INT、BIGINT等​​)
  添加自增的格式为：字段名 数据类型 AUTO_INCREMENT

6.1.3 自增主键与uuid区别

6.1.4 主键与索引的区别

6.2 外键

参考链接：MySQL中的外键（foreign key）

7、索引

详情参考链接：mysql学习(复习)总结 - 索引机制及分类
包含索引定义、索引机制（红黑树、哈希、b-tree、b+tree）、索引分类（聚集索引、非聚集索引、 辅助\二级索引、单列索引、联合索引、主键索引、唯一索引、普通索引），以及联合索引的最左匹配原则，非聚集索引的回表查询和覆盖索引。

8、存储引擎

参考链接：一文带你看懂 MySQL 存储引擎

9、分页

9.1 分页介绍

1. 背景：
   如果查询时候数据量太大，一次性返回所有查询结果既耗费网络资源、又降低了查询效率，用户也不可能一下子看完成千上万条数据。分页可以只显示指定数量的数据。
2. 规则：使用关键字 LIMIT M,N;
   LIMIT 后第一个参数是初始偏移量，即从第几条数据开始显示；
   第二个参数是每页要显示多少条数据。
3. 总结分页公式：
   设每页显示的数据个数为 pageSize，当前是第 pageNo 页，那么就有如下 LIMIT 查询公式：LIMIT (pageNo - 1) * pageSize, pageSize
   

详情参考链接：MySQL中如何实现分页操作

9.2 优化mysql分页的方法

方法1：直接使用数据库提供的SQL语句分页

1. 语句样式：MySQL中，可用如下方法：SELECT * FROM 表名 LIMIT M，N
2. 适应场景：适用于数据量较少的情况(元组百/千级)。
3. 原因/缺点：全表扫描，速度会很慢且有的数据库结果集返回不稳定(如某次返回1，2，3，另外的一次返回2，1，3)。Limit限制的是从结果集的M位置处取出N条输出，其余抛弃。

方法2：建立主键或唯一索引，利用索引分页

1. 语句样式：MySQL中，可用方法：SELECT * FROM 表名 WHERE id_pk > (pageNum*10) LIMIT M。
2. 适应场景：适用于数据量多的情况(元组数上万)。
3. 原因/缺点：索引扫描，速度会很快。有朋友提出因为数据查询出来并不是按照pk_id排序的，所以会有漏掉数据的情况，只能方法3。

方法3：基于索引再排序

1. 语句样式：MySQL中，可用方法：SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M。
2. 适应场景：适用于数据量多的情况(元组数上万). 最好ORDER BY后的列对象是主键或唯一所以，使得ORDERBY操作能利用索引被消除但结果集是稳定的(稳定的含义，参见方法1)。
3. 原因/缺点：索引扫描，速度会很快. 但MySQL的排序操作，只有ASC没有DESC(DESC是假的，未来会做真正的DESC，期待)。

方法4：基于索引使用prepare

1. 语句样式：MySQL中，可用方法：PREPARE stmt_name FROM SELECT * FROM 表名 WHERE id_pk > (?* ?) ORDER BY id_pk ASC LIMIT M。
2. 适应场景：大数据量。
3. 原因/缺点：索引扫描，速度会很快. prepare语句又比一般的查询语句快一点。

详情参考链接：MySQL分页优化方法

10、分区

详细描述参考链接：mysql创建表分区详细介绍及示例

11、聚合函数

1. 聚合函数作用于一组数据，并对一组数据返回一个值。
2. 聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。
3. 聚合函数类型：
   （1）AVG()：求一组数据的平均值，适用于数值类型
   （2）SUM()：求一组数据的和，适用于数值类型
   （3）MAX()：求一组数据的最大值，适用于任意数据类型
   （4）MIN()：求一组数据的最小值，适用于任意数据类型
   （5）COUNT()：返回表中数据和，适用于任意数据类型
   如：

SELECT AVG(salary),SUM(salary),AVG(salary )*107 FROM employees;
SELECT MAX(name),MIN(last_name),MAX(date),MIN(hire_date) FROM employees;
SELECT COUNT(id),COUNT(2*salary),COUNT(1),COUNT(2),COUNT(*)FROM employees;
# COUNT(1),COUNT(2)相当于COUNT(*)

参考链接：MySQL聚合函数

12、查询

12.1 联表查询 - mysql的连接类型 - 7种

详细描述参考链接：Mysql中的七种常用查询连接详解

12.2 where having

	where	having
使用	SQL语句	当SQL语句中使用聚合函数，然后又需要对聚合函数的记录集进行筛选过滤时
作用	作用于表数据过滤，在分组和聚合之前选取数据	作用于组数据的过滤，在分组和聚合之后选取分组数据。

where > 聚合函数 > having

   若需要对聚合函数对group by的结果进行过滤，只能使用having。Having语句通常与Group by语句联合使用，用来过滤Group by语句返回的结果集，Having语句的存在弥补了Where关键字不能与聚合函数联合使用的不足。
例如：查询平均成绩大于60分的同学的学号和平均成绩

 select s_id, AVG(s_score) s_avg from Score group by s_id Having AVG(s_score) > 60;

也可以写成如下形式：

 select s_id, AVG(s_score) s_avg from Score group by s_id Having s_avg > 60;

如果将Having改成where 就会报错，因为where的执行顺序大于聚合函数。

详细参考链接：Mysql中where和having用法及区别

12.3 drop delete truncate

1. delete ：属于DML语言，删除表中数据而不删除表的结构(定义)，同时也不释放空间

delete  from table_name (where...);

2. truncate：只能操作表，将表中数据全部删除，删除内容、释放空间但不删除表的结构(定义)。

truncate table table_name;

3. drop：将删除表的结构，以及被依赖的约束(constrain),触发器(trigger),索引(index)

drop table table_name;

区别：
1.delete删除内容，不删除表结构，可以删除全部数据，也可以删除部分数据，一行行删除，效率较低，可以回滚。
2.truncate删除内容，不删除表结构，删除表全部数据，操作生效后，自动提交，不能回滚。
3.drop既删除表内容，又删除表结构，执行速度最快。

参考链接：MySQL删除表操作（delete、truncate、drop的区别）