MySQL

什么是MySQL数据库？

首先，我们先了解什么是数据库？

数据库是指按照数据结构来组织，存储和管理数据仓库。

数据库管理系统是指数据库系统中对数据进行管理的软件系统。

MySQL数据库是现在比较流行的关系型数据库管理系统之一。

要想深度学习MySQL，不能仅知道增删查改，还要了解它的存储引擎、锁机制等相关原理。

 

设计关系型数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

第一范式（1NF）用来确保每列的原子性，要求每列（或者每个属性值）都是不可再分的最小数据单元（也称为最小的原子单元）。

第二范式（2NF）在第一范式的基础上更进一层，要求表中的每列都和主键相关，即要求实体的唯一性。如果一个表满足第一范式，

并且除了主键以外的其他列全部都依赖于该主键，那么该表满足第二范式。

第三范式（3NF）在第二范式的基础上更进一层，第三范式是确保每列都和主键列直接相关，而不是间接相关，即限制列的冗余性。

如果一个关系满足第二范式，并且除了主键以外的其他列都依赖于主键列，列和列之间不存在相互依赖关系，则满足第三范式。

 

 

MySQL架构

和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好的作用。主要体现在存储引擎的架构上。

插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

第一层是连接层，第二层是服务层，第三层是引擎层，第四层存储层

连接层：最上层是一些客户端和连接服务。主要完成一些类似于连接处理，授权认证，及相关的安全方案。

服务层：主要完成大部分的核心服务功能，包括查询解析，分析，优化，缓存，以及所有的内置函数，所有跨存储引擎的功能也都在这一层实现，如触发器，存储过程，视图。

引擎层：存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同。

存储层：主要是将数据存储在运行该设备的文件系统之上，并完成与存储引擎的交互。

 

存储引擎

存储引擎是MySQL的组件，用于处理不同类型的SQL操作。不同的存储引擎提供不同的存储机制，索引技巧，锁定水平等功能。使用不同的存储引擎，

还可以获得特定的功能。一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎，将会提高整个数据库的性能。

InnoDB是MySQL默认的存储引擎，常见的存储引擎还有MyISAM，Memory，NDB

 MyISAMM和InnoDB的区别（常见面试题）

1.InnoDB支持事务，MyISAM不支持。这是MySQL默认存储引擎从MyISAM变成InnoDB的重要原因之一；

2.InnoDB支持外键，而MyISAM不支持，对一个包含外键的InnoDB表转化为MyISAM会失败；

3.InnoDB是聚簇索引，MyISAM是非聚簇索引。聚簇索引的文件存放在主键索引的叶子结点上，因此InnoDB必须要有

主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后通过主键查询到数据。因此主键不应该过大。

因为主键太大，其他索引也都会很大。而MyISAM是非聚簇索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4.InnoDB不保存表的具体行数，执行select   count(*)  from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，

执行上述语句时只需要读出该变量即可，速度很快；

5.InnoDB最小的锁粒度是行锁，而MyISAM最小的锁粒度是表锁。如果执行一个条更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发

访问受限 。这也是MySQL将默认存储引擎从MyISAM变成InnoDB的重要原因之一。

 

ID自增主键？？MyISAM表会把自增主键的最大ID记录到数据文件中，重启数据库后自增主键的最大ID也不会丢失。

而InnoDB表是把自增主键的最大ID记录到内存中，所以重启数据库或表进行option操作，都会导致最大ID丢失。 

 

数据类型

整数类型：bit , bool , tiny  ,small int medium int ,int , big int 

浮点数类型：float , double , decimal

字符串类型：char , varchar, tiny text , text,等

日期类型：Date ， DataTime，TimeStamp，Time，Year

其他数据类型：binary , varbinary , enum 等

char  和 varchar  的区别？（面试题）

1.char（n）是固定长度，不管实际存储数据的长度是多少，都会占用n个字符的空间。而varchar（n）的长度是可变的。意味着会

根据实际存储的数据分配最终的存储空间。

2.超过char，varchar最大字符长度n的限制后,字符串会被截断。

3.char在存储时会截断尾部的空格，而varchar不会。

 

 索引

索引：索引是帮助MySQL高效获取数据的数据结构，所以说索引的本质是：数据结构，索引的目的在于提高查询效率，可以类比字典，火车站的车次表，图书的目录等。

索引本身也很大，不可能全部存储在内存中，一般以索引文件的形式存储在磁盘中。索引一般用的是B+树结构组织的索引。

创建索引：create  [unique] index indexName  on mytable(username[length])

查看索引：show  index  from table_name

添加索引：alter table tableName  add [unique]  index  indexName(columnName)

删除索引：drop  index  [index] on mytable

优点：1.提高数据检索效率，减低数据库IO成本

　　　2.减低数据排序的成本，减低CPU的消耗。

缺点：1.索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存。

　　　2.虽然索引大大提高了查询速度，但同时也会减低更新表的速度，如对表进行CRUD时，不仅要保存数据，还要保存索引文件每次

　　　更新添加了索引列的字段，因为更新所带来的键值变化后的索引信息。

 

索引的分类：

从数据结构来看分为：B+树，Hash索引，Full—Text全文索引

从物理存储来看分为：聚簇索引和非聚簇索引

从逻辑角度来看分为：主键索引，单列索引，多列索引，唯一索引或非唯一索引，空间索引。

为什么推荐使用整形自增主键而不是UUID？（面试题）

1.UUID是字符串，比整形消耗更大的内存空间；

2.在B+树中进行查找时，需要跟经过的节点值比较大小，整形数据的比较低字符串更快；

3.自增的整形索引在磁盘中会连续存储，在读取一页数据时也是连续的，而UUID是随机产生的，

读取的上下两行数据是分散的，所以不适合执行某些条件查询语句。如：where id > 5 and id <20;

4.在插入或删除数据时，整形自增主键会在叶子结点的末尾建立新的叶子结点，不会破坏左侧子树的结构，

而UUID主键很容易出现这样的情况，B+树为了维持自身的特性，有可能会进行结构的重构，消耗更多的时间。

 

 

 

MySQL事务

MySQL事务主要用来处理操作量大，复杂度高的数据，它是由一组SQL语句组成的逻辑处理单元。

事务基本要素---ACID

A是指原子性，整个事务中的所有操作，要么全部成功，要么全部失败，不可能停滞在中间某个环节。事务在执行过程中发生错误，

会被回滚（rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。我们可以想象一下去银行取钱的过程来理解事务的ACID。

C是指一致性，执行事务的前后，数据保持一致，多个事务对同一个数据读取的结果是相同的。

I是指隔离性，一个事务的执行不能被其它事务干扰。即一个事务内部的操作和使用数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰

D是指持久性，在事务完成之后，该事务所对数据库操作的更改便永久的保存在数据库之中，不会被回滚。

 

 事务的隔离级别有哪些？

首先了解隔离级别前应该先了解什么是幻读和脏读？？？

脏读：一个事务A读取到了另一个事务B没有提交的内容，那么事务A读取到的数据是脏数据。（即事务A读取了事务B更新的数据，然后B进行回滚）

不可重复读：在一个事务范围内，多次查询某个数据，却得到不同的结果。（违背了一致性原则）

幻读：与不可重复读类似。它是发生在一个事务A读取了几行数据，接着另一个并发事务B插入了一些数据，事务A就会发现多了一些原本

不存在的记录，就好像产生了幻觉一样，所以称为幻读。

 

不可重复读和幻读的区别：

不可重复读的重点是修改：在同一个事务中，同样的条件，第一次和第二次读出来的数据不一样。（因为中间有其他事务提交了修改操作）

幻读的重点是在于新增或者删除：在同一个事务中，同样的条件，第一次和第二次读出来的记录数不一样。（因为中间有其他事务提交了插入和删除操作）

 

事务的隔离级别有四种，由低到高分别为：

读未提交（Read-UnCommitted）：是指一个事务可以读取到一个未提交事务的数据。可能会导致会产生脏读，幻读或不可重复读。
读已提交（Read-Committed）：是指一个事务要等到另一个事务提交后才能读取到数据。解决了产生脏读问题，但可能会产生幻读或者不可重复读问题。
可重复读（Repeatable-Read 也是默认级别）：是指在开始读取数据（事务开启）时，不在允许修改操作。可能会产生幻读问题。可以阻止脏读和不可重复读，但是幻读可能会发生。

串行化（Seralizable，也叫序列化）：Serializable是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读，不可重复读和幻读。但是这种事务隔离级别效率低下，比较耗费

数据库性能，一般不使用。

 

并发事务处理带来的问题的解决办法：

一种是加锁：在读取数据之前，对其加锁，阻止其他事务对数据进行修改。

另一种是数据多版本并发控制（MVCC）：不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照，并用这个快照来提供一定级别（语句或事务级）的一致性读取。

从用户的角度来看，好像是数据库可以提供同一数据的多个版本。 

 

典型的实现MVCC方式分为乐观并发控制和悲观并发控制。

 InnoDB下的MVCC是通过在每行记录后面保存了两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（删除时间）。

当然存储的并不是真实的时间，而是系统版本号。每开始一个新的事务，系统版本号就会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来查询到的每行记录的版本号进行比较。

 

EPEATABLE READ（可重读）隔离级别下MVCC如何工作： SELECT InnoDB会根据以下两个条件检查每行记录： InnoDB只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行，要么是在开始事务之前已经存在要么是事务自身插入或者修改过的 行的删除版本号要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除 只有符合上述两个条件的才会被查询出来

INSERT：InnoDB为新插入的每一行保存当前系统版本号作为行版本号

DELETE：InnoDB为删除的每一行保存当前系统版本号作为行删除标识

UPDATE：InnoDB为插入的一行新纪录保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识

保存这两个额外系统版本号，使大多数操作都不用加锁。使数据操作简单，性能很好，并且也能保证只会读取到符合要求的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作和一些额外的维护工作。

MVCC 只在 读提交和可重复读这两种隔离级别下工作。

 

 

事务日志

InnoDB使用日志来减少提交事务是的开销。因为日志中已经记录了事务，就无须在每个事务提交时把缓存池中的脏块刷新到磁盘中。

InnoDB使用日志把随机IO变成顺序IO。一旦日志安全写到磁盘，事务就持久化了，即失断电，InnoDB可以重放日志并且恢复日志已经提交的事务。

 

事务的实现

事务的实现是基于数据库的存储引擎。不同的存储引擎对事务的支持程度不一样。MySQL中支持事务的存储引擎有InnoDB和NDB。

事务的实现就是如何实现ACID特性。

事务的隔离性是通过锁来实现的，而事务的原子性，一致性和持久性则是通过事务日志实现的。

 

事务日志包括：重做日志（redo  log）和回滚日志（undo  log）

redo  log 用来实现持久性和原子性

在innoDB的存储引擎中，事务日志通过重做(redo)日志和innoDB存储引擎的日志缓冲(InnoDB Log Buffer)实现。事务开启时，事务中的操作，都会先写入存储引擎的日志缓冲中，在事务提交之前，这些缓冲的日志都需要提前刷新到磁盘上持久化，这就是DBA们口中常说的“日志先行”(Write-Ahead Logging)。当事务提交之后，在Buffer Pool中映射的数据文件才会慢慢刷新到磁盘。此时如果数据库崩溃或者宕机，那么当系统重启进行恢复时，就可以根据redo log中记录的日志，把数据库恢复到崩溃前的一个状态。未完成的事务，可以继续提交，也可以选择回滚，这基于恢复的策略而定。

在系统启动的时候，就已经为redo log分配了一块连续的存储空间，以顺序追加的方式记录Redo Log，通过顺序IO来改善性能。所有的事务共享redo log的存储空间，它们的Redo Log按语句的执行顺序，依次交替的记录在一起。

undo  log用来是实现一致性

undo log 主要为事务的回滚服务。在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，

就可以根据undo log进行回滚操作。单个事务的回滚，只会回滚当前事务做的操作，并不会影响到其他的事务做的操作。

Undo记录的是已部分完成并且写入硬盘的未完成的事务，默认情况下回滚日志是记录下表空间中的（共享表空间或者独享表空间）。

二种日志均可以视为一种恢复操作，redo_log是恢复提交事务修改的页操作，而undo_log是回滚行记录到特定版本。二者记录的内容也不同，redo_log是物理日志，

记录页的物理修改操作，而undo_log是逻辑日志，根据每行记录进行记录。

 

锁

首先，数据库是用来存储资源和数据的，而资源和数据应该是共享的，所以为了保证数据的一致性，并且使得各种共享资源在被并发访问变得有序而设计出来一种机制---锁机制

锁的分类：

对数据操作进行分类：

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行，不会互相影响。

写锁（排它锁）：当前写操作没有完成前，他回阻断其他写锁和读锁。

 

对数据操作的粒度分类：

1.表级锁：开销小，加锁快，不会出现 死锁；锁定粒度大，发生锁冲突的概率最高，并发读最低，（MyISAM和Memory采用的是表级锁）。

2.行级锁：开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高，（InnoDB即支持表级锁也支持行级锁，默认是行级锁）

3.页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

 

使用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用。

 

什么死锁？

所谓死锁：是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用，它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁，

这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。

缠身死锁的原因？

1.进程推进顺序不当；

2.竞争资源。

产生死锁的四大必要条件

互斥条件：指进程对所分配到的资源进行排它性使用，即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源，则请求者只能等待，直至占有资源的进程用毕释放

请求和保持条件：指进程已经保持至少一个资源，但又提出了新的资源请求，而该资源已被其它进程占有，此时请求进程阻塞，但又对自己已获得的其它资源保持不放

不可剥夺条件：指进程已获得的资源，在未使用完之前，不能被剥夺，只能在使用完时由自己释放

环路等待条件：指在发生死锁时，必然存在一个进程——资源的环形链，即进程集合{P0，P1，P2，···，Pn}中的P0正在等待一个P1占用的资源；P1正在等待P2占用的资源，……，Pn正在等待已被P0占用的资源

这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死锁。

 

分区，分库和分表

为什么进行分区，分库，分表？？？

一般情况下我们创建的表对应一组存储文件，使用MyISAM存储引擎是是一个.MYI和.MYD文件，使用InnoDB存储引擎时是一个.idb和.frm（表结构）文件。

当数据量很大时（一般指千万条记录级别以上），MySQL的性能会开始下降，这时我们需要将数据分散到多组存储文件中，保证其单个文件的执行效率。

 

 分区

数据分区是一种物理数据库的设计技术，它的目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。

分区并不是生成新的数据表，而是将表的数据均衡分摊到不同的硬盘，系统或是不同服务器存储介子中，实际上还是一张表。

另外，分区可以做到将表的数据均衡到不同的地方，提高数据检索的效率，降低数据库的频繁IO压力值。

 分区分为  水平分区和垂直分区

 

什么时候考虑使用分区？

• 一张表的查询速度已经慢到影响使用的时候。

• sql经过优化

• 数据量大

• 表中的数据是分段的

• 对数据的操作往往只涉及一部分数据，而不是所有的数据

 

什么时候考虑使用分库？

• 单台DB的存储空间不够
• 随着查询量的增加单台数据库服务器已经没办法支撑

分库解决的问题

其主要目的是为突破单节点数据库服务器的 I/O 能力限制，解决数据库扩展性问题。 

 

什么时候考虑分表？

• 一张表的查询速度已经慢到影响使用的时候。

• sql经过优化

• 数据量大

• 当频繁插入或者联合查询时，速度变慢

分表解决的问题

分表后，单表的并发能力提高了，磁盘I/O性能也提高了，写操作效率提高了

• 查询一次的时间短了
• 数据分布在不同的文件，磁盘I/O性能提高
• 读写锁影响的数据量变小
• 插入数据库需要重新建立索引的数据减少

分表的实现方式（复杂）

需要业务系统配合迁移升级，工作量较大

 

分区和分表的区别与联系

• 分区和分表的目的都是减少数据库的负担，提高表的增删改查效率。

• 分区只是一张表中的数据的存储位置发生改变，分表是将一张表分成多张表。
• 当访问量大，且表数据比较大时，两种方式可以互相配合使用。

• 当访问量不大，但表数据比较多时，可以只进行分区。

 

 

MySQL主从复制

概念：是指将一台服务器的数据，复制到其他的服务器。前者称为主节点(master/leader)，后者称为从节点(slave/follower) ; 

数据的复制是单向的，只能由主节点到从节点。Master以写为主，Slave以读为主。

 

为什么需要主从复制
读写分离：通过主从复制的方式来同步数据，然后通过读写分离提高数据库并发处理能力，提高数据库的吞吐量。
数据备份：我们通过主从复制将主库上的数据复制到了从库上，相当于是一种热备份机制，也就是在主库正常运行的情况下进行的备份，不会影响到服务。
高可用性：数据备份实际上是一种冗余的机制，通过这种冗余的方式可以换取数据库的高可用性，也就是当服务器出现故障或宕机的情况下，可以切换到从服务器上，保证服务的正常运行。

 

主从同步的原理就是基于 Binlog 进行数据同步的。在主从复制过程中，会基于 3 个线程来操作，一个主库线程，两个从库线程。

二进制日志转储线程（Binlog dump thread）是一个主库线程。当从库线程连接的时候，主库可以将二进制日志发送给从库，当主库读取事件的时候，会在 Binlog 上加锁，读取完成之后，再将锁释放掉。（当从库线程连接的时候可以将二进制日志发送给从库）

从库 I/O 线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地形成中继日志（Relay log）。（接到主库,向主库发送请求更新binlog）

从库 SQL 线程会读取从库中的中继日志，并且执行日志中的事件，从而将从库中的数据与主库保持同步。（读取从库中的中继日志,并且执行日志中的事件）

即

从库会生成两个线程,一个I/O线程,一个SQL线程;

I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;

主库会生成一个log dump线程,用来给从库I/O线程传binlog;

SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行。。

 

 

参考文章：

https://juejin.cn/post/6850037271233331208?share_token=d9179c2b-6b75-4772-9298-d12bd5e582d9

https://blog.csdn.net/qq_28289405/article/details/80576614

https://blog.csdn.net/belongtocode/article/details/106928372