mysql数据库建表的完整步骤 mysql添加数据语句怎么写

一个查询语句经过哪些步骤

这次我们从MySQL的整体架构来讲SQL的执行过程，如下图:

在整体分为两部分Server和引擎层，这里引擎层我使用InnoDB去代替，引擎层的设计是插件形式的，可以任意替代，接下来我们开始介绍每个组件的作用:

Server层

连接器:连接器负责跟客户端建立连接、获取权限、维持和管理连接；

查询缓存:服务的查询缓存，如果能找到对应的查询，则不必进行查询解析，优化，执行等过程，直接返回缓存中的结果集；

解析器:解析器会根据查询语句，构造出一个解析树，主要用于根据语法规则来验证语句是否正确，比如SQL的关键字是否正确，关键字的顺序是否正确;

优化器:解析树转化为查询计划，一般情况下，一条查询可以有很多种执行方式，最终返回相同的结果，优化器就是根据成本找到这其中最优的执行计划;

执行器:执行计划调用查询执行引擎，而查询引擎通过一系列API接口查询到数据;

InnoDB

后台线程:负责刷新内存池中的数据，保证缓存池中的内存缓存是最近的数据，将已修改的数据刷新到磁盘文件，同时保证数据库发生异常的情况能恢复到正常情况；

内存池:内存池也可以叫做缓存池，主要为弥补磁盘的速度较慢对数据库产生的影响，查询的时候，首先将磁盘读到的页的数据放在内存池中，下次读取的时候直接从内存池中读取数据，修改数据的时候，首先修改内存池中的数据，然后后台线程按照一定的频率刷新到磁盘上。

文件:主要是指表空间文件，而外还有一些日志文件；

以上大致的介绍一下MySQL的整体架构，其中内存池、文件、后台线程等一些跟细节的东西没有介绍，后面我们介绍其他时候在带出来其中的详细的部分，另外在附上一张MySQL5.6整体架构图：

InnoDB如何保存数据

这部分内容是建立在上部分的基础上，需要对内存池、文件、后台线程深入到细节去了解组成，接下我们还是分三部分开始讲解:

文件

文件分为日志文件和存储文件，分为两部分讲起:

存储文件

存储文件也就是表数据的存储，整体的存储结构如下图:

表空间主要分为两类文件，一类是共享表空间，一类是每张表单独的表空间，单独的表空间存放的是表中的数据、索引等信息，共享的表空间主要是存储事务信息、回滚信息等数据；表空间由段(Segment)、区(Extend)、页(Page)、行(Row)组成，接下来简单介绍一下这4种结构:

1. 段(Segment)
   常见的Segment有数据段、索引段、回滚段等， 数据段为B+树的叶子节点(Leaf node segment)、索引段为B+树的非叶子节点(Non-leaf node segment)。如下图：每创建索引就会创建一个索引段，索引段的叶子节点指向数据段，通过这样的组合来完成我们查询数据时候需要，因此创建索引越多，会导致需要构建的索引段就越多，导致插入数据时间就会增加。
2. 区(Extend)
   区是构成段的基本元素，一个段由若干个区构成，一个区是物理上连续分配的一段空间，每一个段至少会有一个区，在创建一个段时会创建一个默认的区。如果存储数据时，一个区已经不足以放下更多的数据，此时需要从这个段中分配一个新的区来存放新的数据。一个段所管理的空间大小是无限的，可以一直扩展下去，但是扩展的最小单位就是区。每个区大小固定为1MB，区由页组成，为保证区中Page的连续性通常InnoDB会一次从磁盘中申请4-5个区。在默认Page的大小为16KB的情况下，一个区则由64个连续的Page组成。
3. 页(Page):
   页是构成区的基本单位，是InnoDB磁盘管理的最小单位。在逻辑上（页面号都是从小到大连续的）及物理上都是连续的。在向表中插入数据时，如果一个页面已经被写完，系统会从当前区中分配一个新的空闲页面处理使用，如果当前区中的64个页面都被分配完，系统会从当前页面所在段中分配一个新的区，然后再从这个区中分配一个新的页面来使用。
4. 行(Row):
   InnoDB按照行进行存放数据，每个页存放的数据有硬性规定，最多存放16KB，当数据大于16KB的时候会发生行溢出，会存储到而外的页(Uncompressed BLOB Page)当中。

日志文件

关于日志文件这里主要介绍三种日志文件，分别为binlog、redo log、redo log:

binlog

binlog用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog是mysql的逻辑日志，并且由Server层进行记录，使用任何存储引擎的mysql数据库都会记录binlog日志。binlog是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。

binlog日志格式

1. ROW
   基于行的复制，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了。优点:不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题；缺点:因为每行都要记录日志，会照成日志量暴涨；
2. STATMENT
   基于SQL语句的复制，每一条会修改数据的sql语句会记录到binlog中。优点:不需要记录每一行的变化，减少了binlog日志量，节约了IO, 从而提高了性能；缺点:在某些情况下会导致主从数据不一致，比如执行sysdate()等函数的时候。
3. MIXED
   基于STATMENT和ROW两种模式的混合复制，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog

使用场景

binlog的主要使用场景有两个，分别是主从复制和数据恢复;

1. 主从复制
   在Master端开启binlog，然后将binlog发送到各个Slave端，Slave端重放binlog从而达到主从数据一致。
2. 数据恢复
   恢复到某一时刻的日志，通过使用mysqlbinlog工具来恢复数据；

刷盘时机

对于InnoDB存储引擎而言，只有在事务提交时才会记录biglog，此时记录还在内存中，Mysql通过sync_binlog参数控制biglog的刷盘时机，取值范围是0-N，

N代表多少条以后开始进行刷盘，当设置为0的时候由系统自行判断何时写入磁盘，当设置为1的时候，相当于每次Commit就进行刷盘一次，但是这个时候要注意与redo log日志可能存在不一致的情况，这个时候需要设置innodb_support_xa参数也为1，这样就能保证两个两份日志是同步的。

redo log

redo log包括两部分:redo log buffer和redo log file,redo log buffer是在内存中，redo log file是在磁盘上，当MySQL执行DML语句的时候，首先写入redo log buffer，然后按照一定条件顺序写入redo log file，什么时候会触发buffer内容写入到file当中呢？

1. InnoDB后台线程中的主线程，每秒会进行一次将buffer中的数据刷入到磁盘当中；
2. 通过设置innodb_flush_log_at_trx_commit参数，来控制刷新的时机，当设置为 1 的时候，事务每次提交都会将 log buffer 中的日志写入 os buffer 并调用 fsync()刷到 log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO 的性能较差。当设置为 0 的时候，事务提交时不会将 log buffer 中日志写入到 os buffer，而是每秒写入 os buffer 并调用fsync()写入到 log file on disk 中。也就是说设置为 0 时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失 1 秒钟的数据。当设置为 2 的时候，每次提交都仅写入到 os buffer，然后是每秒调用 fsync() 将 os buffer 中的日志写入到 log file on disk。

redo log日志格式

redo log记录数据页的变更，在设计上redo log采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志，本质上就是一个环状。

rdo log刷盘完成以后，其实数据最终还没刷新到真正数据磁盘上，因此还需要刷新到真正的数据磁盘上，本质上redo log的设计就是为了降低对数据页刷盘的要求，接下来我们结合上图来聊聊是如何刷新到数据文件文件上的,也就是checkpoint机制:

首先看下环，环上有4个ib_logfile_*的文件，该文件就是存储redo log日志的文件，可以通过控制innodb_log_files_in_group的数量来控制文件的个数，通过innodb_log_file_size来控制文件的大小，不介意将文件的设置的太大，如果设置的太大会导致奔溃恢复的时候过于缓慢，也不能设置的太小，这样可能导致一次事务需要切换多次日志文件，此外还会照成频繁写入磁盘文件，照成性能抖动；

接下来我们看两个端点write pos和check point,write pos到check point之间的部分是redo log空着的部分，用于记录新的记录；check point到write pos之间是redo log待落盘的数据页更改记录。当write pos追上check point时，会先推动check point向前移动，空出位置再记录新的日志。

InnoDB在启动的时候，不管上次数据库是否正常关闭，都会尝试进行恢复操作，分为两种情况:

1. checkpoint表示已经完整刷到磁盘上data page上的LSN，因此恢复时仅需要恢复从checkpoint开始的日志部分，LSN表示写入日志的字节的总量，例如，当数据库在上一次checkpoint的LSN为10000时宕机，且事务是已经提交过的状态。启动数据库时会检查磁盘中数据页的LSN，如果数据页的LSN小于日志中的LSN，则会从检查点开始恢复。
2. 在宕机前正处于checkpoint的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度。这时候一宕机，数据页中记录的LSN就会大于日志页中的LSN，在重启的恢复过程中会检查到这一情况，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。

在恢复的过程中因为redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如binlog)要快很多；

使用的场景

MySQL用来确保事务的持久性。redo log记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

undo log

undo log记录数据的逻辑变化，用户事务的回滚操作和MVCC, undo log 存放在共享表空间中，以段(rollback segment)的形式存在。

undo log日志格式

逻辑格式的日志，在事务进行回滚的时候，可以将数据从逻辑上恢复至事务之前的状态。

使用的场景

保证数据的原子性，保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。

刷盘时机

当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由Purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

内存池

InnoDB 存储引擎是基于磁盘存储的,也就是说数据都是存储在磁盘上的,由于 CPU 速度和磁盘速度之间的鸿沟, InnoDB 引擎使用缓冲池技术来提高数据库的整体性能。内存池简单来说就是一块内存区域.在数据库中进行读取页的操作,首先将从磁盘读到的页存放在内存池中,下一次读取相同的页时,首先判断该页是不是在内存池中,若在,称该页在内存池中被命中,直接读取该页。否则,读取磁盘上的页。对于数据库中页的修改操作,首先修改在内存池中页,然后再以一定的频率刷新到磁盘,并不是每次页发生改变就刷新回磁盘。

内存池中缓存的信息主要有:index page、data page、insert buffer、自适应哈希索引、 lock info、数据字典信息等。索引页和数据页占缓冲池的很大一部分。在InnoDB中，内存池中的页大小默认为16KB，和磁盘的页的大小默认一样。我们已经介绍过数据文件的存储结构相信大家对缓存结构的内容也会有一定理解，我们就不单独介绍了，后面只会重点强调一下insert buffer和自适应哈希索引这两块内容，以及扩展下内存池的设计原理。

Insert Buffer

Insert Buffer的设计，对于非聚集索引的插入和更新操作，不是每一次直接插入到索引页中，而是先判断插入非聚集索引页是否在缓冲池中，若存在，则直接插入，不存在，则先放入一个Insert Buffer对象中。数据库这个非聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置。然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge（合并）操作，这时通常能将多，这就大大提高了对于非聚集索引插入的性能。这个时候可能会照成一种情况，当MySQL数据库发生宕机的时候有有大量的Insert Buffer没有被合并到非聚集索引的页当中的时候，这个时候MySQL恢复需要很长的时间。

需要满足的条件:

索引是非聚集索引，索引不是唯一的；

对于具体的实现我们下次再聊；

自适应哈希索引

InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以提升速度，这简历哈希索引，称之为自适应哈希索引。AHI是通过缓冲池的B+树页构造而来的。因此建立的速度非常快，且不要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动的为某些热点页建立哈希索引。

后台线程

Master Thread

这是最核心的一个线程,主要负责将缓冲池中的数据异步刷新到磁盘,保证数据的一致性,包括赃页的刷新、合并插入缓冲等。

IO Thread

在 InnoDB 存储引擎中大量使用了异步 IO 来处理写 IO 请求, IO Thread 的工作主要是负责这些 IO 请求的回调处理。

Purge Thread

事务被提交之后, undo log 可能不再需要,因此需要 Purge Thread 来回收已经使用并分配的 undo页. InnoDB 支持多个 Purge Thread, 这样做可以加快 undo 页的回收。

完成整体功能介绍以后，我们开始聊聊数据如何插入到InnoDB引擎上的:

假设场景如下:

首先我们创建一张表T，主键为Id，辅助索引为a

create table T(id int primary key, a int not null, name varchar(16),index (a))engine=InnoDB;

接下来插入一条数据，

insert into t(id,a,name) values(id1,a1,’哈哈’),(id2,a2,’哈哈哈’);

我们介绍过MySQL读取数据的流程，Server层我们还是会经过连接器、解析器、优化器、执行器这些东西，这些我们就不介绍了，我们主要介绍剩下的操作：

插入数据时候可能有两种场景:

第一种场景:假设Id1这条数据在内存池中,

1. 直接更新Buffer Pool中的Index Page和Data Page;
2. 写入redo log中，处于预提交状态；
3. 写入binlog中，
4. 提交事务，处于commit状态，两阶段提交；
5. 后台线程写入到数据文件的索引段和数据段中;

第二种场景假设id2这条数据不再内存池中,

1. 数据写入到内存池中，非聚集索引写入到Insert Buffer，其他数据写入Data Page中;
2. 后续的动作保持和上面剩下的步骤一样。

扩展阅读

我们来聊聊内存池(Buffer Pool)运行原理，可以从以下3个方面来看:

1. 如何管理缓存的页？
   InnoDB为每一个缓存页都创建了一些控制信息，这些控制信息包括该页所属的表空间编号、页号、页在Buffer Pool中的地址、LSN等信息，每个缓存页对应的控制信息占用的内存大小是相同的，我们就把每个页对应的控制信息占用的一块内存称为一个控制块吧，控制块和缓存页是一一对应的，它们都被存放到 Buffer Pool 中，其中控制块被存放到 Buffer Pool 的前边，缓存页被存放到 Buffer Pool 后边，所以整个Buffer Pool对应的内存空间看起来就是这样的：碎片就是空间不够分配的缓存页。当我们最初启动MySQL服务器的时候，需要完成对Buffer Pool的初始化过程，就是分配Buffer Pool的内存空间，把它划分成若干对控制块和缓存页。但是此时并没有真实的磁盘页被缓存到Buffer Pool中，之后随着程序的运行，会不断的有磁盘上的页被缓存到Buffer Pool中，接下来会有一个问题就是怎么区分Buffer Pool中哪些缓存页是空闲的，哪些已经被使用？我们最好在某个地方记录一下哪些页是可用的，我们可以把所有空闲的页包装成一个节点组成一个链表，这个链表也可以被称作Free链表。因为刚刚完成初始化的Buffer Pool中所有的缓存页都是空闲的，所以每一个缓存页都会被加入到Free链表中，整体设计如下图：从图中可以看出，Free链表包含着链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息。每个Free链表的节点中都记录了某个缓存页控制块的地址，而每个缓存页控制块都记录着对应的缓存页地址，所以相当于每个Free链表节点都对应一个空闲的缓存页。每当需要从磁盘中加载一个页到Buffer Pool中时，就从Free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的Free链表节点从链表中移除，表示该缓存页已经被使用了。
2. 缓存的淘汰#
   机器的内存大小是有限的，所以MySQL的InnoDB Buffer Pool的大小同样是有限的，如果需要缓存的页占用的内存大小超过了Buffer Pool大小,InnoDB Buffer Pool采用经典的LRU算法来进行页面淘汰，以提高缓存命中率。当Buffer Pool中不再有空闲的缓存页时，就需要淘汰掉部分最近很少使用的缓存页。当我们需要访问某个页时，可以这样处理LRU链表：1.如果该页不在Buffer Pool中，在把该页从磁盘加载到Buffer Pool中的缓存页时，就把该缓存页包装成节点塞到链表的头部。2.如果该页在Buffer Pool中，则直接把该页对应的LRU链表节点移动到链表的头部。但是这样做会有一些性能上的问题，比如你的一次全表扫描或一次逻辑备份就把热数据给冲完了，就会导致导致缓冲池污染问题！Buffer Pool中的所有数据页都被换了一次血，其他查询语句在执行时又得执行一次从磁盘加载到Buffer Pool的操作，而这种全表扫描的语句执行的频率也不高，每次执行都要把Buffer Pool中的缓存页刷新一次，这严重的影响到其他查询对 Buffer Pool 的使用，降低了缓存命中率。针对这种场景InnoDB存储引擎对传统的LRU算法做了一些优化，在InnoDB中加入了midpoint。新读到的页，虽然是最新访问的页，但并不是直接插入到LRU列表的首部，而是插入LRU列表的midpoint位置。这个算法称之为midpoint insertion stategy。默认配置插入到列表长度的5/8处。midpoint由参数innodb_old_blocks_pct控制。midpoint之前的列表称之为new列表，之后的列表称之为old列表。可以简单的将new列表中的页理解为最为活跃的热点数据。
3. 脏页如何实现刷新#
   更新是在缓存池中先进行的，那它就和磁盘上的页不一致了，这样的缓存页也被称为脏页。所以需要考虑这些被修改的页面什么时候刷新到磁盘？当然，最简单的做法就是每发生一次修改就立即同步到磁盘上对应的页上，但是频繁的往磁盘中写数据会严重的影响程序的性能。所以每次修改缓存页后，我们并不着急立即把修改同步到磁盘上，而是在未来的某个时间点进行同步，由后台刷新线程依次刷新到磁盘，实现修改落地到磁盘。但是如果不立即同步到磁盘的话，那之后再同步的时候我们怎么知道Buffer Pool中哪些页是脏页，哪些页从来没被修改过呢？我们需要创建一个存储脏页的链表，凡是在LRU链表中被修改过的页都需要加入这个链表中，因为这个链表中的页都是需要被刷新到磁盘上的，所以也叫Flush链表，链表的构造和Free链表差不多，这里的脏页修改指的此页被加载进Buffer Pool后第一次被修改，只有第一次被修改时才需要加入Flush链表，如果这个页被再次修改就不会再放到Flush链表了，因为已经存在。需要注意的是，脏页数据实际还在LRU链表中，而Flush链表中的脏页记录只是通过指针指向LRU链表中的脏页。