MySQL数据库

2020-07-27  本文已影响0人  yaco

[TOC]


MySQL总结

一、基本概念

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

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

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

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、几种关联查询

  1. 内连接查询（INNER JOIN）

    是指所有查询出的结果都是能够在连接的表中有对应记录的

  2. 左外连接查询 （LEFT OUTER JOIN）

    是指以左边的表的数据为基准，去匹配右边的表的数据，如果匹配到就显示，匹配不到就显示为null

  3. 右外连接查询 （RIGHT OUTER JOIN）

    是指以右边的表的数据为基准，去匹配左边的表的数据，如果匹配到就显示，匹配不到就显示为null

  4. 全外连接查询 （LEFT OUTER JOIN #### UNION #### RIGHT OUTER JOIN）

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

  5. 自连接查询

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

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

6、SQL关键字补充

varchar与char的区别

char(10)中10的含义

varchar(50)中50的涵义

int(20)中20的涵义

UNION与UNION ALL的区别？

drop、delete与truncate的区别

drop、delete与truncate的区别

三、存储引擎

1、常见的存储引擎

2、InnoDB和MyISAM的区别

四、索引

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值，一个表允许多个列创建唯一索引。

（7）普通索引

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

（8）全文索引

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

（9）覆盖索引

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

索引分类

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

聚簇索引的优点：

聚簇索引的缺点：

非聚簇索引的优点：

非聚簇索引的缺点：

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索引的优劣分析

7、B树和B+树的区别

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

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

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

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

（1）MyISAM实现索引的方式

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

（2）InnoDB实现索引的方式

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

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、使用索引的原则

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

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}

五、事务

1、什么是事务

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

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

2、事务的四大性质

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

  1. 原子性（Atomicity）： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
  2. 一致性（Consistency）： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
  3. 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
  4. 持久性（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 × × ×

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

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

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

5、常用的事务控制语句

六、锁

1、隔离级别与锁的关系

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

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

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

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

2、锁的分类

（1）按照锁的粒度划分

（2）按照类别进行划分

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

InnoDB是基于索引来完成行锁

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

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

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

5、InnoDB对锁算法的应用

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

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

常见的解决死锁的方法

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

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

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

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

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

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

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

两种锁的使用场景

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

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

七、表优化

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

（1）限制数据范围

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

（2）读/写分离

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

（3）缓存

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

（4）垂直分表

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

（5）水平分表

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

2、分库分表的理解

（1）垂直拆分

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

垂直拆分

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

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

适用场景：

（2）水平拆分

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

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

水平拆分

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

水平拆分的缺点：

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

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

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

（1）什么是主从复制

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

（2）基本流程

主从复制

（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 主要分为 Server 层和存储引擎层：

2、SQL 语句的生命周期

（1）查询语句

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

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

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

（2）更新语句

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

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

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

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

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

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

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

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

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

3、SQL执行很慢的原因

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

（1）SQL刷脏页机制

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

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

（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执行很慢大致可以分为两种情况：

九、补充概念

1、日志文件

（1）MySQL中常见的日志

日志

（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

上一篇 下一篇

猜你喜欢

热点阅读