MySQL数据库

作者: yaco | 来源:发表于2020-07-27 11:21 被阅读0次

[TOC]

MySQL总结

一、基本概念

1、为什么要使用数据库？

数据存放的位置通常由两个地方，内存和硬盘，存放在内存中的数据访问速度块，但是系统断电就会丢失；保存在硬盘中的数据，访问数据慢，但是断电不会丢失，而且可以存储的数据量也远大于内存。

所以就产生了数据库的概念，它使得我们更加高效的使用以及管理数据，它有以下三个突出功能：

数据永久保存
使用SQL语言，查询方法，效率高
数据管理更加方便

2、基本概念

SQL：结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询语言。用于存取数据、查询、更新和管理关系数据库系统。

RDBMS：(Relational Database Management Syste）关系型数据库管理系统

MySQL：MySQL是一个关系型数据库管理系统，属于 Oracle 旗下产品。MySQL是最好的 RDBMS应用软件之一。

3、数据库三大范式

第一范式：每个列都不可以再拆分。案例如下：

表 : 姓名，性别，电话

问题：若某个人有两个电话，家庭电话和手机，这样则不符合第一范式。

解决：把电话列分成两个列即可。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。(主要解决多对多的关系)

表 : 学号, 姓名, 年龄, 课程名称, 成绩, 学分;

问题：这个表明显说明了两个事务:学生信息, 课程信息，不符合第二范式。

解决：分成学生表和课程表分别存储即可，然后用一个选课表将两张表关联起来，解决多对多的问题。

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。（主要解决一对多的关系）

表：学号, 姓名, 年龄, 所在学院, 学院联系电话，关键字为单一关键字"学号";

问题：可以看出来一个学院可以有多个学生，而且但是一个学院只有一个学院联系电话，电话依赖与学院，不唯一依赖于主键，不满足第三范式

解决：分出一个学院表，在学生表中分出一个学院id的外键约束

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。

二、MySQL入门

1、MySQL的基本数据类型

整数类型：包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字节、4字节、8字节整数。任何整数类型都可以加上UNSIGNED属性，表示数据是无符号的，即非负整数。

整数类型	tinyInt	很小的整数(8位二进制)
	smallint	小的整数(16位二进制)
	mediumint	中等大小的整数(24位二进制)
	int(integer)	普通大小的整数(32位二进制)
	bigint	普通大小的整数(364位二进制)

浮点型：包括FLOAT、DOUBLE、DECIMAL。DECIMAL可以用于存储比BIGINT还大的整型，能存储精确的小数；而FLOAT和DOUBLE是有取值范围的，并支持使用标准的浮点进行近似计算；计算时FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串进行处理。

小数类型	float	单精度浮点数
	double	双精度浮点数
	decimal(m,d)	压缩严格的定点数

字符串型：包括VARCHAR、CHAR、TEXT、BLOB；其中TEXT和BLOB又可以细分成四种规定的长度；BLOB可以存储图片,而TEXT不行。TEXT只能存储纯文本文件

字符串文本型	CHAR(M)	M为0~255之间的整数
	VARCHAR(M)	M为0~65535之间的整数
	TINYBLOB	允许长度0~255字节
	BLOB	允许长度0~65535字节
	MEDIUMBLOB	允许长度0~167772150字节
	LONGBLOB	允许长度0~4294967295字节
	TINYTEXT	允许长度0~255字节
	TEXT	允许长度0~65535字节
	MEDIUMTEXT	允许长度0~167772150字节
	LONGTEXT	允许长度0~4294967295字节
	VARBINARY(M)	允许长度0~M个字节的变长字节字符串
	BINARY(M)	允许长度0~M个字节的定长字节字符串

日期类型：包括DATE、YEAR、TIME、DATETIME、TIMESTAMP

日期类型	year	YYYY 1901~2155
	time	HH:MM:SS -838:59:59~838:59:59
	date	YYYY-MM-DD 1000-01-01~9999-12-3
	datetime	YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59
	timestamp	YYYY-MM-DD HH:MM:SS 19700101 00:00:01 UTC~2038-01-19 03:14:07UTC

枚举类型：ENUM

2、主键和外键

主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）

外键：在一个表中存在的另一个表的主键称此表的外键。

3、SQL执行语句分类

DDL：数据定义语言DDL（Data Ddefinition Language）CREATE，DROP，ALTER；主要对逻辑结构等有操作的，其中包括表结构，视图和索引。

DQL：数据查询语言DQL（Data Query Language）SELECT；各种简单查询，连接查询等都属于DQL。

DML：数据操纵语言DML（Data Manipulation Language）INSERT，UPDATE，DELETE；对应上面所说的查询操作 DQL与DML共同构建了多数初级程序员常用的增删改查操作。而查询是较为特殊的一种被划分到DQL中。

DCL：数据控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK；主要对数据库安全性完整性等有操作的，可以简单的理解为权限控制等。

4、SQL 约束有哪几种？

NOT NULL: 用于控制字段的内容一定不能为空（NULL）。

UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。

PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。

FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

CHECK: 用于控制字段的值范围。

5、几种关联查询

内连接查询（INNER JOIN）

是指所有查询出的结果都是能够在连接的表中有对应记录的
左外连接查询（LEFT OUTER JOIN）

是指以左边的表的数据为基准，去匹配右边的表的数据，如果匹配到就显示，匹配不到就显示为null
右外连接查询（RIGHT OUTER JOIN）

是指以右边的表的数据为基准，去匹配左边的表的数据，如果匹配到就显示，匹配不到就显示为null
全外连接查询（LEFT OUTER JOIN #### UNION #### RIGHT OUTER JOIN）

顾名思义，把两张表的字段都查出来，没有对应的值就显示null；mysql是没有全外连接的(mysql中没有full outer join关键字)，想要达到全外连接的效果，可以使用union关键字连接左外连接和右外连接。
自连接查询

自连接查询就是当前表与自身的连接查询，关键点在于虚拟化出一张表给一个别名

这里可以参考一篇博客——Mysql中的关联查询(内连接，外连接，自连接)

6、SQL关键字补充

varchar与char的区别

char长度固定，varchar可变长
如果插入的字符没有达到预设的长度时，char会自动补齐，varchar按原始长度插入
char因为定长的原因，因此存取速度更快，而carchar更加节省空间

char(10)中10的含义

10位固定字符串，不足补空格最多10个字符

varchar(50)中50的涵义

最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存

int(20)中20的涵义

是指显示字符的长度。20表示最大显示宽度为20，但仍占4字节存储，存储范围不变；

UNION与UNION ALL的区别？

如果使用UNION ALL，不会合并重复的记录行
效率 UNION 高于 UNION ALL

drop、delete与truncate的区别

drop、delete与truncate的区别

三、存储引擎

1、常见的存储引擎

InnoDB引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系
MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

2、InnoDB和MyISAM的区别

功能方面：InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁；InnoDB 提供事务支持事务，外部键等高级数据库功能；InnoDB支持外键约束； InnoDB 支持MVCC（多版本并发控制）；以上的所有MyISAM都不支持
存储形式上：MyISAM每张表被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件；而InnoDB所有的表都保存在同一个数据文件中
性能上： MyISAM查询更优；而InnoDB增删改更优
索引区别：
- InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
- InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
- MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
- InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

四、索引

1、什么是索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

2、索引的优缺点

索引的优点：

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点：

时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
空间方面：索引需要占物理空间。

3、常见的索引类型

（1）前缀索引

前缀索引也叫局部索引，比如给身份证的前 10 位添加索引，类似这种给某列部分信息添加索引的方式叫做前缀索引。

语法：index(field(10))，使用字段值的前10个字符建立索引，默认是使用字段的全部内容建立索引。

（2）聚簇索引

将数据存储与索引放到了一块，找到索引也就找到了数据

（3）非聚簇索引

将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。

（4）联合索引

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

（5）主键索引

数据列不允许重复，不允许为NULL，一个表只能有一个主键。

（6）唯一索引

数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

（7）普通索引

基本的索引类型，没有唯一性的限制，允许为NULL值。

可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

（8）全文索引

是目前搜索引擎使用的一种关键技术。可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

（9）覆盖索引

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

索引分类

4、（非）聚簇索引优缺点分析

聚簇索引的优点：

聚集索引的查询速度非常的快，因为整个B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

聚簇索引的缺点：

依赖于有序的数据 ：因为B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或UUID这种又长又难比较的数据，插入或查找的速度肯定比较慢。
更新代价大 ：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

非聚簇索引的优点：

更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的

非聚簇索引的缺点：

跟聚集索引一样，非聚集索引也依赖于有序的数据
可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

5、非聚集索引一定回表查询吗(覆盖索引)?

非聚集索引不一定回表查询。

如果发生了覆盖索引的情况，那么就不会发生回表的现象，举例说明如下，先有两条SQL语句，看看他们是否进行了回表操作：

# 只查询name
SELECT username FROM table WHERE username='guang19';

# 查询所有
SELECT * FROM table WHERE username='guang19';

对于第一条语句，查询的类容就是索引的类容，所以不会发生回表操作
对于第二条语句，查询索引，会首先通过非聚簇索引查询出主键的值，然后再回表通过主键查询出所需要的值

即使是MYISAM也是这样，虽然MYISAM的主键索引确实需要回表，因为它的主键索引的叶子节点存放的是指针。但是如果SQL查的就是主键呢?

SELECT id FROM table WHERE id=1;

主键索引本身的key就是主键，查到返回就行了。这种情况就称之为覆盖索引了。

6、索引常用的数据结构

（1）哈希索引

对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

（2）BTree索引

B树索引是Mysql数据库中使用最频繁的索引类型，基本所有存储引擎都支持BTree索引。通常我们说的索引不出意外指的就是（B树）索引（实际是用B+树实现的，因为在查看表索引时，mysql一律打印BTREE，所以简称为B树索引）

（3）哈希索引和BTree索引的优劣分析

在单条记录的查询效率上，哈希索引的效率是最快的，直接利用Hash函数计算出桶位置，接近O（1）的时间成本取出数据
在哈希索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询，所以在范围查找中体现了BTree索引的优势
哈希索引没有BTree索引丰富的索引机制，通值哈希索引的性能也不是很稳定，当数据量过大的时候，哈希索引的维护成本显得更大。

7、B树和B+树的区别

首先，从数据结构上来看：

在B树中，数据存放在任意的节点；但是在B+树中，只有叶子节点才会存放数据，而非叶子节点存放的都是方便搜索数据的键值。
B树中，每个叶子节点都是相互独立的；但是在B+树中，叶子节点之间存在一条相互连接的链。

B树和B+树的区别

其次，从落地使用的角度来看：

B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。
因为B+树的非叶子节点不存放数据，因此占用的内存相对来说较小，这样在B+树的每一层，就可以划分出更多的空间存放间隙值，是的最终划分的间隙较小，降低树的层数，减少IO操作；
B+又因为所有的数据均存放在叶子节点的中，所以不管那一次发起的查找，查到数据的时间成本都是一样的，因此B+树又被称为多路平衡二叉树。
B+在进行全盘数据遍历的时候，只需要找到最左边的叶子节点，然后通过遍历的方式全盘扫描，效率很高。但是B数只能通过传统树的遍历方式

总结来看：B树的突出优势在于对应靠近根节点的值查询效率很高，B+树的突出优势在于降低树的层度、均衡查询效率、减少IO操作（每一层就对应一次IO）、提升全盘扫描效率等等。

8、MyISAM和InnoDB实现BTree索引方式的区别

（1）MyISAM实现索引的方式

B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

（2）InnoDB实现索引的方式

区别于MyISAM，InnoDB采用了聚簇索引的设计思想，非叶子节点中存放的收拾主键的值，叶子节点中存放了主键的值和对应的行数据

当使用主键索引进行搜索的时候，可以直接通过搜索到主键key对应得叶子节点获取所有得数据信息
当使用辅助索引进行搜索得时候，会找到叶子节点中存储的主键key值，然后再进行回表操作，找到主键对应的数据记录。

9、索引的添加方式

（1）添加PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

（2）添加UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

（3）添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

（4）添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

（5）添加联合索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

10、使用索引的原则

索引虽好，但也不是无限制的使用，最好符合一下几个原则：

较频繁作为查询条件的字段才去创建索引
定义有外键的数据列一定要建立索引。
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间。
========================================================================================
区分度不是很高的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
更新频繁字段不适合创建索引
对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
对于定义为text、image和bit的数据类型的列不要建立索引。
不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

11、什么是最左匹配原则

在联合索引下，索引之间满足一定的顺序关系，寻找索引总是通过联合索引的顺序去优先匹配；如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：

select * from user where name=xx and city=xx ; //可以命中索引 
select * from user where name=xx ;             // 可以命中索引 
select * from user where city=xx ;             // 无法命中索引

这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

总结一下，假设有 a 、b、c、d四个索引构成的联合索引{a 、b、c、d}

a 可以匹配到索引，因为联合索引的第一条就是a
a、b、c 也可以匹配到索引，因为列之间按照联合索引的顺序进行放置了
c、a 匹配不到索引，首先没有达到联合索引的全貌，最左边不是a，满足不了最左匹配原则

五、事务

1、什么是事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

2、事务的四大性质

[图片上传失败...(image-abe74c-1595819573477)]

原子性（Atomicity）： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性（Consistency）： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

3、并发事务会带来哪些问题

并发事务的理解： 多个事务同时对数据库进行操作，比如一读一写，一读一改等，会造成以下一些严重的问题

脏读（Dirty read）:当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中；这时另外一个事务也访问了这个数据，然后使用了这个数据。第二事务拿到的数据不是当前数据库中的实际数据，这种现象称为脏读。

丢失修改（Lost to modify）: 两个事务同时对数据库中的数据进行修改，比如同时做加1操作，因为第一个事务执行加1操作还没有提交的时候，另一个事务也拿到相同的数据进行加1操作，使得两次加1操作的结果就变成了一次，这种现象叫做丢失修改。

不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。另一个事务在此期间进行了修改，当时事务在前后两次读取的数据不一样，这种现象叫做不可重读。

幻读（Phantom read）: 幻读与不可重复读类似，区别在于幻读在读取数据的时候，发现记录增加了或者减少了，他是一种对记录数的变更。比如一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录

4、事务的隔离级别

READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别	脏读	不可重复读	幻影读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

MySQL的默认事务隔离级别是REPEATABLE-READ(可重复读)， Oracle默认事务隔离级别是READ-COMMITTED(读取已提交)

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读）并不会有任何性能损失，原因就在于MVCC。

InnoDB 存储引擎在分布式事务的情况下一般会用到SERIALIZABLE(可串行化)隔离级别。

5、常用的事务控制语句

START TRANSACTION或者BEGIN：显示的开启事务。需要注意的是在存储过程中只能用START TRANSACTION开启事务，因为存储过程本来有BEGIN…END语法，两者会冲突。
COMMIT：提交事务。也可以写成COMMIT WORK。
ROLLBACK：回滚事务。也可以写成ROLLBACK WORK。
SAVEPOINT identifier：自定义保存点，适用于长事务，可以回滚到我们自定义的位置。
RELEASE SAVEPOINT identifier：删除一定保存点，如果没有保存点的时候，会报错
ROLLBACK TO[SAVEPOINT] identifier：回滚到指定保存点。

六、锁

1、隔离级别与锁的关系

在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突；

在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；

在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。

SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

2、锁的分类

（1）按照锁的粒度划分

行级锁： 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页级锁：页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
表级锁：表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

（2）按照类别进行划分

共享锁：又叫做读锁。当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。
排它锁：又叫做写锁。当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

3、MySQL中InnoDB引擎的行锁是怎么实现的？

InnoDB是基于索引来完成行锁

例: select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起

4、InnoDB存储引擎的锁的算法

Record lock：单个行记录上的锁
Gap lock：间隙锁，锁定一个范围，不包括记录本身
Next-key lock：record+gap 锁定一个范围，包含记录本身

5、InnoDB对锁算法的应用

InnoDB对于行查询使用的是next-key lock
因为next-key lock实际上就是一种间隙锁，因此可以有效避免幻读的产生。
当查询的索引含有唯一属性时，将next-key lock降级为record key
Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生

6、什么是死锁？怎么解决？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

常见的解决死锁的方法

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

如果业务处理不好可以用分布式事务锁或者使用乐观锁

7、数据库的乐观锁和悲观锁实现

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：乐观锁一般会使用版本号机制或CAS算法实现。

两种锁的使用场景

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

七、表优化

1、单表记录过大，怎么解决？

（1）限制数据范围

务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。

（2）读/写分离

经典的数据库拆分方案，主库负责写，从库负责读；

（3）缓存

使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；

（4）垂直分表

根据数据库里面数据表的相关性进行拆分。 把一张表拆分成多张表

（5）水平分表

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。

2、分库分表的理解

（1）垂直拆分

根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。把主键和一些列放在一个表，然后把主键和另外的列放在另一个表中。

垂直拆分

垂直拆分的优点： 可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

适用场景：

如果一个表中某些列常用，另外一些列不常用
可以使数据行变小，一个数据页能存储更多数据，查询时减少I/O次数

（2）水平拆分

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

水平拆分

水平拆分可以支持非常大的数据量。需要注意的一点是:分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库。

水平拆分的缺点：

给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需UNION操作；
在许多数据库应用中，这种复杂度会超过它带来的优点，查询时会增加读一个索引层的磁盘次数

（3）水平拆分的常见方案

客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

（4）分库分表后面临的问题

事务支持：分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。
跨库join：只要是进行切分，跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。分库分表方案产品
跨节点的count,order by,group by以及聚合函数问题： 这些是一类问题，因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。解决方案：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。
数据迁移，容量规划，扩容等问题： 来自淘宝综合业务平台团队，它利用对2的倍数取余具有向前兼容的特性（如对4取余得1的数对2取余也是1）来分配数据，避免了行级别的数据迁移，但是依然需要进行表级别的迁移，同时对扩容规模和分表数量都有限制。总得来说，这些方案都不是十分的理想，多多少少都存在一些缺点，这也从一个侧面反映出了Sharding扩容的难度。
ID问题：一旦数据库被切分到多个物理结点上，我们将不能再依赖数据库自身的主键生成机制。一方面，某个分区数据库自生成的ID无法保证在全局上是唯一的；另一方面，应用程序在插入数据之前需要先获得ID,以便进行SQL路由. 一些常见的主键生成策略

3、MySQL的复制原理以及流程

（1）什么是主从复制

将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

（2）基本流程

主从复制

主表master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。
从表salve开启一个I/O Thread，读取主表中的binlog日志
从表salve通过I/O操作经读取到的日志写进自己的Relaylog中
然后交给从表的执行引擎进行执行

（3）MySQL主从复制解决的问题

主数据库出现问题，可以切换到从数据库。
可以进行数据库层面的读写分离。
负载均衡：降低单个服务器的压力
可以在从数据库上进行日常备份。

4、读写分离有哪些解决方案？

读写分离是依赖于主从复制，而主从复制又是为读写分离服务的。因为主从复制要求slave不能写只能读（如果对slave执行写操作，那么show slave status将会呈现Slave_SQL_Running=NO，此时你需要按照前面提到的手动同步一下slave）。

（1）方案一

使用mysql-proxy代理

优点：直接实现读写分离和负载均衡，不用修改代码，master和slave用一样的帐号，mysql官方不建议实际生产中使用

缺点：降低性能，不支持事务

（2）方案二

使用AbstractRoutingDataSource+aop+annotation在dao层决定数据源。

如果采用了mybatis，可以将读写分离放在ORM层，比如mybatis可以通过mybatis plugin拦截sql语句，所有的insert/update/delete都访问master库，所有的select 都访问salve库，这样对于dao层都是透明。 plugin实现时可以通过注解或者分析语句是读写方法来选定主从库。不过这样依然有一个问题，也就是不支持事务，所以我们还需要重写一下DataSourceTransactionManager，将read-only的事务扔进读库，其余的有读有写的扔进写库。

(3）方案三

使用AbstractRoutingDataSource+aop+annotation在service层决定数据源，可以支持事务.

缺点：类内部方法通过this.xx()方式相互调用时，aop不会进行拦截，需进行特殊处理。

八、执行流程

1、MySQL 基本架构

下图是 MySQL 的一个简要架构图，首先简单介绍一下下图涉及的一些组件的基本作用

MySQL 基本架构

连接器：身份认证和权限相关(登录 MySQL 的时候)。
查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
分析器: 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
优化器： 按照 MySQL 认为最优的方案去执行。
执行器: 执行语句，然后从存储引擎返回数据。

简单来说 MySQL 主要分为 Server 层和存储引擎层：

Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。
存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

2、SQL 语句的生命周期

（1）查询语句

sql 可以分为两种，一种是查询，一种是更新（增加，更新，删除）。我们先分析下查询语句，语句如下：

select * from tb_student  A where A.age='18' and A.name=' 张三 ';

分析下这个语句的执行流程：

先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 sql 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。
通过分析器进行词法分析，提取 sql 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student,需要查询所有的列，查询条件是这个表的 id='1'。然后判断这个 sql 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。
接下来就是优化器进行确定执行方案，上面的 sql 语句，可以有两种执行方案：

a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18。

b.先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。
那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。
进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

（2）更新语句

以上就是一条查询 sql 的执行流程，那么接下来我们看看一条更新语句如何执行的呢？sql 语句如下：

update tb_student A set A.age='19' where A.name=' 张三 ';

我们来给张三修改下年龄，在实际数据库肯定不会设置年龄这个字段的，不然要被技术负责人打的。其实更新语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志啦，这就会引入日志模块了，MySQL 自带的日志模块式 binlog（归档日志） ，所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志），我们就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：

先查询到张三这一条数据，如果有缓存，也是会用到缓存。

然后拿到查询的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。
执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。
更新完成。

（3）为什么要用两个日志模块，用一个日志模块不行吗?

这是因为最开始 MySQL 并没与 InnoDB 引擎( InnoDB 引擎是其他公司以插件形式插入 MySQL 的) ，MySQL 自带的引擎是 MyISAM，但是我们知道 redo log 是 InnoDB 引擎特有的，其他存储引擎都没有，这就导致会没有 crash-safe 的能力(crash-safe 的能力即使数据库发生异常重启，之前提交的记录都不会丢失)，binlog 日志只能用来归档。

并不是说只用一个日志模块不可以，只是 InnoDB 引擎就是通过 redo log 来支持事务的。那么，又会有同学问，我用两个日志模块，但是不要这么复杂行不行，为什么 redo log 要引入 prepare 预提交状态？这里我们用反证法来说明下为什么要这么做？

先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 bingog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

如果采用 redo log 两阶段提交的方式就不一样了，写完 binglog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。那么问题来了，有没有一个极端的情况呢？假设 redo log 处于预提交状态，binglog 也已经写完了，这个时候发生了异常重启会怎么样呢？这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：

判断 redo log 是否完整，如果判断是完整的，就立即提交。
如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务。

这样就解决了数据一致性的问题

3、SQL执行很慢的原因

参考自：腾讯面试：一条SQL语句执行得很慢的原因有哪些？---不看后悔系列

（1）SQL刷脏页机制

当我们要往数据库插入一条数据、或者要更新一条数据的时候，我们知道数据库会在内存中把对应字段的数据更新了，但是更新之后，这些更新的字段并不会马上同步持久化到磁盘中去，而是把这些更新的记录写入到 redo log 日记中去，等到空闲的时候，在通过 redo log 里的日记把最新的数据同步到磁盘中去。

刷脏页有下面4种场景（后两种不用太关注“性能”问题）：

redolog写满了：redo log 里的容量是有限的，如果数据库一直很忙，更新又很频繁，这个时候 redo log 很快就会被写满了，这个时候就没办法等到空闲的时候再把数据同步到磁盘的，只能暂停其他操作，全身心来把数据同步到磁盘中去的，而这个时候，就会导致我们平时正常的SQL语句突然执行的很慢，所以说，数据库在在同步数据到磁盘的时候，就有可能导致我们的SQL语句执行的很慢了。
内存不够用了：如果一次查询较多的数据，恰好碰到所查数据页不在内存中时，需要申请内存，而此时恰好内存不足的时候就需要淘汰一部分内存数据页，如果是干净页，就直接释放，如果恰好是脏页就需要刷脏页。
MySQL 认为系统“空闲”的时候：这时系统没什么压力。
MySQL 正常关闭的时候：这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

（2）锁机制

这个就比较容易想到了，我们要执行的这条语句，刚好这条语句涉及到的表，别人在用，并且加锁了，我们拿不到锁，只能慢慢等待别人释放锁了。或者，表没有加锁，但要使用到的某个一行被加锁了，这个时候，我也没办法啊。

如果要判断是否真的在等待锁，我们可以用 show processlist这个命令来查看当前的状态哦，这里我要提醒一下，有些命令最好记录一下，反正，我被问了好几个命令，都不知道怎么写，呵呵。

（3）选错索引

我们先来假设我们有一个表，表里有下面两个字段,分别是主键 id，和两个普通字段 c 和 d。

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

没有用上索引，我觉得这个原因是很多人都能想到的，例如你要查询这条语句

1）字段没有索引

select * from t where 100 <c and c < 100000;

刚好你的 c 字段上没有索引，那么抱歉，只能走全表扫描了，你就体验不会索引带来的乐趣了，所以，这回导致这条查询语句很慢。

2）字段有索引，但却没有用索引

这个时候给 c 这个字段加上了索引，然后又查询了一条语句

select * from t where c - 1 = 1000;

这里查询用不到c的索引，因为在等号左边对C进行了计算

3）字段有索引，且用到了索引

正确的查询应该如下

select * from t where c = 1000 + 1;

4 ）函数操作导致没有用上索引

如果我们在查询的时候，对字段进行了函数操作，也是会导致没有用上索引的，例如

select * from t where pow(c,2) = 1000;

所以呢，一条语句执行都很慢的时候，可能是该语句没有用上索引了，不过具体是啥原因导致没有用上索引的呢，你就要会分析了，我上面列举的三个原因，应该是出现的比较多的吧。

（4）数据库自己选错索引

由于统计的失误，导致系统没有走索引，而是走了全表扫描，而这，也是导致我们 SQL 语句执行的很慢的原因。

（5）总结

通过以上分析可以发现SQL执行很慢大致可以分为两种情况：

大多数情况下很正常，偶尔很慢，则有如下原因：
- 数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。
- 执行的时候，遇到锁，如表锁、行锁。
这条 SQL 语句一直执行的很慢，则有如下原因
- 没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。
- 数据库选错了索引。

九、补充概念

1、日志文件

（1）MySQL中常见的日志

bin log（二进制日志）：记录增删改的一些操作，不记录查询日志，配合relay log进行主从复制。
relayl log （中继日志）：主从复制时使用的日志，读取从主表中读取到的bin log，然后执行相应语句
error log （错误日志）：记录了MySQL服务启动和停止正确和错误的信息，还记录了mysqld实例运行过程中发生的错误事件信息。
slow log （慢查询日志）：查询超出变量 long_query_time 指定时间值的为慢查询。但是查询获取锁(包括锁等待)的时间不计入查询时间内。
general log（一般查询日志）：指定时间内的查询日志
redo log （重做日志）：属于InnoDB执行引擎，数据库关闭重启后，执行redo log日志，重新启动；在增删改操作的时候，与bin log联合使用，执行语句之后，先将redo log置为预提交状态，让后等待写入bin log日志，写完之后提交redo log日志
undo log（回滚日志）：属于InnoDB执行引擎，执行回滚操作时使用，多版本控制（MVCC）页用到了undo log

日志

（2）bin log

binlog记录了数据库表结构和表数据变更,比如update/delete/insert/truncate/create。它不会记录select，因为这个没有对表进行变更。binlog我们可以理解位存储着每条变更的SQL语句。

bin log主要的两个作用：复制和恢复数据

(3) redo log

我们来看一条sql语句

update user_table set name='java3y' where id = '3';

MySQL执行这条语句，肯定先把id=3这条语句查出来，然后将name字段给改掉。这没问题吧？

实际上MySQL的基本存储结构是页，所以MySQL是先把这条记录所在的页找到，然后把该页加载到内存中，将对应记录进行修改。

现在就可能出现一个问题；如果在内存中把数据改了还没来得及写入磁盘，而此时的数据库挂了怎么办，显然这次修改就丢失了。

如果每个请求都需要将数据立马写入磁盘之后，那速度会很慢，MySQL可能也顶不住，所以MySQL引入了redo log, 内存写完了，然后会写一份redo log, 这份redo log 记载着这次在某个页做了什么修改。

其实写redo log的时候，也会有buffer, 是先写buffer，在真正写入到磁盘中的，至于从buffer什么时候写入磁盘，会有配置供我们配置。

写redo log也是需要写磁盘的，但它的好处就是顺序IO，所以，redo log 的存在为了当我们修改的时候，写完内存了，但数据还没真正写到磁盘的时候，此时数据库挂了，我们可以对数据进行恢复。因为redo log 是顺序IO，所以写入速度很快，并且redo log 记载的是物理变化，文件的体积小，恢复速度很快。

redo log的作用是为了持久化而生的。写完内存，如果数据库挂了，那我们可以通过redo log来恢复内存还没来得及写入磁盘的数据，将redo log加载到内存里面，那内存就能恢复到挂掉之前的数据了

(4) undo log

undo log主要有两个作用：回滚和多版本控制(MVCC)

在数据修改的时候，不仅记录了redo log，还记录undo log，如果因为某些原因导致事务失败或回滚了，可以用undo log进行回滚

undo log主要存储的也是逻辑日志，比如我们要insert一条数据了，那undo log会记录的一条对应的delete日志。我们要update一条记录时，它会记录一条对应相反的update记录。

这也应该容易理解，毕竟回滚嘛，跟需要修改的操作相反就好，这样就能达到回滚的目的。因为支持回滚操作，所以我们就能保证：“一个事务包含多个操作，这些操作要么全部执行，要么全都不执行”。

参考博主：

本文在大佬博客的基础之上进行了一些整理和理解，非常感谢大佬的分享，附上两位大佬的开源空间。

ThinkWon

JavaGuide