数据库之MySQL

MySQL是一种开放源代码的关系型数据库管理系统，使用结构化查询语言(Structured Query Language)SQL进行数据库管理，而且是开放源代码的。
MySQL常用的存储引擎包括：
●InnoDB
InnoDB是MySQL的默认存储引擎，支持事务、行锁和外键等操作。
●MyISAM
MyISAM是MySQL 5.1版本前的默认存储引擎，并发性比较差,不支持事务和外键等操作，默认的锁粒度为表级锁。

 

MySQL体系结构

一、连接池(网络接入层)

主要负责连接管理、授权认证、安全等等。每个客户端连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时，服务器对其进行认证。可以通过用户名与密码认证，也可以通过SSL证书进行认证。登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限。

二、查询处理器(服务层)

第二层服务层是MySQL的核心，MySQL的核心服务层都在这层。查询解析，SQL执行计划分析，SQL执行计划优化，查询缓存，以及跨存储引擎的功能都在这一层实现: 存储过程，触发器，视图等。内部结构:

图中红色框中标出来的就是MySQL服务层内部执行的过程。组成：管理服务和工具组件，SQL接口组件，查询分析器组件，查询优化器组件。SQL语句在服务层中具体的流程:

查询缓存
在解析查询之前，服务器会检查查询缓存，如果能找到对应的查询，服务器不必进行查询解析、
优化和执行的过程，直接返回缓存中的结果集。

解析器与预处理器
MySQL会解析查询，并创建了一个内部数据结构(解析树) 。这个过程解析器主要通过语法规则来验证和解析。
比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理会根据MySQL的规则进一步检查
解析树是否合法。比如要查询的数据表和数据列是否存在等。

查询优化器
优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。
优化器的作用就是找到这其中最好的执行计划。它并不关心使用什么存储引擎，
但是存储引擎对优化查询是有影响的。优化器要求存储引擎提供容量或某个具体操作的开销信息来评估执行时间。

查询引擎
在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令
调用存储引擎的接口得出结果。

三、存储管理器(存储引擎层)

负责MySQL中数据的存储与提取。服务器中的查询执行引擎通过API与存储引擎进行通信，通过接口屏蔽了不同存储引擎之间的差异。MySQL采用插件式的存储引擎，提供了许多存储引擎，每种都有不同的特点。可以根据不同的业务特点选择最适合的存储引擎。如果对于存储引擎的性能不满意，可以通过修改源码来得到自己想要的性能。
存储引擎是针对于表的而不是针对库的(一个库中不同表可以使用不同的存储引擎)，服务器通过API与存储引擎进行通信，用来屏蔽不同存储引擎之间的差异。

InnoDB是事务安全的MySQL存储引擎，设计上采用了类似于Oracle的架构。一般而言在OLTP的应用中，InoDB应该作为核心应用表的首选存储引擎。

1.事务管理器

事务(Transaction) 是数据库区别于文件系统的重要特性之一。在文件系统中，如果写文件时操作系统崩溃了，这个文件就很有可能被破坏。有一些机制可以把文件恢复到某个时间点，不过如果需要保证两个文件同步，这些文件系统可能就无能为力了。如需要更新两个文件时，更新完一个文件后，在更新完第二个文件之前系统重启了，就会有两个不同步的文件。
这正是数据库系统引入事务的主要目的：事务会把数据库从一种一致状态转换为另一种一致状态。在数据库提交工作时，可以确保其要么所有修改都已经保存了，要么所有修改都不保存。

在MySQL命令行的默认设置下，事务都是自动提交的，即执行SQL语句后就会马上执行COMMIT操作。因此开始一个事务，必须使用BEGIN, START TRANSACTION，或者执行SET AUTOCOMMIT=0，以禁用当前会话的自动提交。这和Microsoft SQL Server数据库的方式一致，需要显式地开始一个事务。而Oracle数据库不需要专门的语句来开始事务，事务会在修改数据的第一条语句处隐式地开始。

InnoDB存储引擎默认的支持隔离级别是REPEATABLE READ。与标准SQL不同的是，InnoDB存储引擎在REPEATABLE READ事务隔离级别下，使用Next-Key Lock锁的算法避免幻读的产生，与其他数据库系统不同。所以InnoDB存储引擎在默认REPEATABLE READ的事务隔离级别下已经能完全保证事务的隔离性要求，达到SQL标准的SERIALIZABLE隔离级别。
隔离级别越低，事务请求的锁越少，或者保持锁的时间就越短。这也是为什么大多数数据库系统默认的事务隔离级别是READ COMMITTED。

2.锁

锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访向。InnoDB存储引擎会在行级别上对表数据上锁，也会在数据库内部其他多个地方使用锁，从而允许对多种不同资源提供并发访问。InnoDB存储引擎实现了如下两种标准的行级锁:
共享锁(S Lock), 允许事务读一行数据。
排他锁(X Lock), 允许事务删除或者更新一行数据。
InnoDB存储引擎支持多粒度锁定，这种锁定允许在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，称为意向锁。意向锁是表级别的锁，其设计目的主要是为了在一个事务中揭示下一行将被请求的锁的类型。InnoDB存储引擎支持两种意向锁:
意向共享锁(IS Lock)，事务想要获得一个表中某几行的共享锁。
意向排他锁(IX Lock)，事务想要获得一个表中某几行的排他锁。
因为InnoDB存储引擎支持的是行级别的锁，所以意向锁不会阻塞除全表扫以外的任何请求。

一致性的非锁定行读 (consistent nonlocking read)是指InnoDB存储引擎通过行多版本控制(multi versioning)的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE、UPDATE操作，这时读取操作不会等待行上锁的释放，相反，InnoDB存储引擎会去读取行的一个快照数据。如图所示:

称其为非锁定读，因为不需要等待访问的行上X锁的释放。快照数据是指该行之前版本的数据，该实现通过Undo段来实现。而Undo用来在事务中回滚数据，因此快照数据本身没有额外的开销。此外，读取快照数据是不需要上锁的，因为没有必要对历史的数据进行修改。非锁定读的机制大大提高了数据读取的并发性，在InnoDB存储引擎默认设置下，这是默认的读取方式，即读取不会占用和等待表上的锁。但是在不同事务隔离级别下，读取的方式不同。并不是每个事务隔离级别下读取的都是一致性读。同样，即使都是使用一致性读，对于快照数据的定义也不相同。
快照数据是当前行数据的历史版本，一个行可能有不止一个快照数据。称这种技术为行多版本技术。由此带来的并发控制，称为多版本并发控制(Multi Version Concurrency Control, MVCC)。
在Read Committed和Repeatable Read (InnoDB存储引擎的默认事务隔离级别)下，InnoDB存储引擎使用非锁定的一致性读。然而对于快照数据的定义不同。在Read Committed事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。在Repeatable事务隔离级别下和Repeatable Read事务隔离级别下，非一致性读总是读取事务开始时的行数据版本。

锁的算法

InnoDB存储引擎有3种行锁的算法，分别是:
Record Lock：单个行记录上的锁。
GapLock：间隙锁，锁定一个范围，但不包含记录本身。
Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，井且锁定记录本身。
Record Lock总是会去锁住索引记录。如果InnoDB存储引擎表建立的时候没有设置任何一个索引，这时InnoDB存储引擎会使用隐式的主键来进行锁定。Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法，在Next-Key Lock算法下，InnoDB对于行的查询都是采用这种锁定算法。对于不同SQL查询语句，可以设置共享的Next-Key Lock和排他的Next-Key Lock。

锁的问题

丟失更新(lost update)是一个经典的数据库问题。实际上，所有多用户计算机系统环境下都有可能产生这个问题。出现下面的情况时，就会发生丢失更新:
(1)事务T1查询一行数据，放入本地内存，并显示给一个终端用户Userl。
(2)事务T2也查询该行数据，并将取得的数据显示给终端用户User2。
(3) User1修改这行记录，更新数据库并提交。
(4) User2修改这行记录，更新数据库并提交。
显然，这个过程中用户User1的修改更新操作“丢失”了。

脏页指在缓冲池中已经被修改的页，但是还没有刷新到磁盘，即数据库内存中的页和磁盘的页中的数据是不一致的，当然在刷新到磁盘之前，日志都已经被写入了重做日志文件中。脏数据指在缓冲池中被修改的数据，并且还没有被提交。
脏页的读取，是非常正常的。脏页是因为数据库实例内存和磁盘的异步同步造成的，这并不影响数据的一致性。并且因为是异步的，因此可以带来性能的提高。而脏数据不同，脏数据是指未提交的数据。如果读到了脏数据，即一个事务可以读到另外一个事务中未提交的数据，则违反了数据库的隔离性。
脏读指的就是在不同的事务下，可以读到另外事务未提交的数据。发生的条件是事务的隔离级别为READ UNCOMMITTED，目前绝大部分的数据库都至少设置成READ COMMITTED。InnoDB存储引擎默认的事务隔离级别为READ REPEATABLE，Microsoft SQL Server数据库为READ COMMITTED，Oracle 数据库同样也是READ COMMITTED。

不可重复读指在一个事务内多次读同一数据。在这个事务还没有结束时，另外一个事务修改同一数据。在第一个事务的两次读数据之间，由于第二个事务的修改，第一个事务两次读到的数据不一样。这样就发生了在一个事务内两次读到的数据不同，称为不可重复读。
不可重复读和脏读的区别是：脏读是读到未提交的数据:而不可重复读读到的是已经提交的数据，但是其违反了数据库事务一致性的要求。 
一般不可重复读的问题是可以接受的，因为其读到的是已经提交的数据，不会带来很大的问题。因此很多数据库厂商将数据库事务的默认隔离级别设置为READ COMMITTED，在这种隔离级别下允许不可重复读的现象。
InnoDB存储引擎中，通过使用Next-Key Lock算法来避免不可重复读的问题。在Next-Key Lock算法下，对于索引的扫描不仅仅是锁住扫描到的索引，而且还锁住这些索引覆盖的范围。 对于这个范围内的插入都是不允许的。这样就避免了另外的事务在这个范围内插入数据导致的不可重复读的问题。
锁升级(Lock Escalation)指将当前锁的粒度降低。数据库可以把一个表的1000个行锁升级为一个页锁，或者将页锁升级为表锁。这种升级保护了系统资源，防止系统使用太多的内存来维护锁，一定程度上提高了效率，带来的一个问题是锁粒度的降低导致并发性能降低。
InnoDB存储引擎不存在锁升级的问题。在InnoDB存储引擎中，1个锁的开销与1 000 000个锁是一样的，都没有开销。这一点和Oracle数据库类似。

3.日志恢复

隔离性由锁实现。原子性，一致性，持久性通过数据库的redo和undo来完成。
在InnoDB存储引擎中，事务日志通过重做(redo)日志文件和InnoDB存储引擎的日志缓冲(InnoDB Log Buffer)来实现。当开始一个事务时，会记录该事务的一个LSN(Log Sequence Number,日志序列号)；当事务执行时，会往InnoDB存储引擎的日志缓冲里插入事务日志；当事务提交时，必须将InnoDB存储引擎的日志缓冲写入磁盘(默认的实现，即innodb_flush_log_at_trx_commit=1)。也就是在写数据前，需要先写日志。这种方式称为预写日志方式(Write- Ahead Logging, WAL)。
InnoDB存储引擎通过预写日志的方式来保证事务的完整性。这意味着磁盘上存储的数据页和内存缓冲池中的页是不同步的，对于内存缓冲池中页的修改，先是写入重做日志文件，然后再写入磁盘，因此是一种异步的方式。可以通过命令SHOW ENGINE INNODBSTATUS来观察当前磁盘和日志的“差距"。

重做日志记录了事务的行为，可以通过其进行“重做"。但是事务有时还需要撒销，这时就需要undo。undo与redo正好相反，对于数据库进行修改时，数据库不但会产生redo，而且还会产生一定量的undo。即使执行的事务或语句由于某种原因失败了，或者如果用一条ROLLBACK语句请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子。与redo不同的是，redo存放在重做日志文件中，undo存放在数据库内部的一个特殊段中，称为undo段(undo segment)，位于共享表空间内。

4.MyISAM

MyISAM存储引擎是MySQL官方提供的存储引擎。其特点是不支持事务、外键，对于一些OLAP (Online Analytical Processing,在线分析处理)操作速度快。除Windows版本外，是所有MySQL版本默认的存储引擎。MyISAM存储引擎表由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件。可以使用myisampack工具来压缩解压数据文件。

5.不同存储引擎的比较 

每种存储引擎的实现都不相同。有些不支持事务。可以通过SHOW ENGINES语句查看当前使用的MySQL数据库所支持的存储引擎，也可以通过查找information_schema架构下的ENGINES表来查看。

四、物理部分(系统文件层)

构成MySQL数据库和InnoDB存储引擎表的文件有以下这些。

参数文件：告诉MySQL实例启动时在哪里可以找到数据库文件，并且指定某些初始化参数，这些参数定义了某种内存结构的大小等设置。
日志文件：用来记录MySQL实例对某种条件做出响应时写入的文件，如错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件等。
socket文件：用UNIX域套接字方式进行连接时需要的文件。
pid文件：MySQL实例的进程ID文件。
MySQL表结构文件：用来存放MySQL表结构定义文件。
存储引擎文件：每个存储引擎都有自己的文件来保存各种数据，这些存储引擎真正存储了记录和索引等数据。

InnoDB存储引擎表类型
在InnoDB存储引擎表中，每张表都有个主键，如果在创建表时没有显式地定义主键(Primary Key)，则InnoDB存储引擎会按如下方式选择或创建主键。首先表中是否有非空的唯一索引(Unique NOT NULL)，如果有则该列为主键。否则InnoDB存储引擎自动创建一个6字节大小的指针。

1.逻辑存储结构

InnoDB存储引擎的逻辑存储结构和Oracle大致相同，所有数据都被逻辑地存放在一个空间中，称为表空间(tablespace)。表空间又由段(segment)、区(extent)、页(page)组成。页有时也称为块(block)，InnoDB存储引擎的逻辑存储结构大致如图所示。

表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都是存放在表空间中。默认情况下InnoDB存储引擎有一个共享表空间ibdatal，所有数据都放在这个表空间内。如果启用了参数innodb_file_per_table，则每张表内的数据可以单独放到一个表空间内。

对于启用了innodb_file_per_table的参数选项，每张表的表空间内存放的只是数据，索引和插入缓冲，其他类的数据，如撤销(Undo) 信息、系统事务信息、二次写缓冲(double write buffer)等还是存放在原来的共享表空间内。说明即使启用了参数innodb_file_per_table，共享表空间还是会不断地增加其大小。

页

在操作系统的概念中，往磁盘取出1KB数据，操作系统不会只取1KB的数据，而是会取出4KB的数据，因为操作系统的一个页表项的大小是4KB。这涉及到一个程序局部性的概念，一个程序在访问了一条数据之后，在之后很可能再次访问这条数据和这条数据的相邻数据，所以索性直接加载4KB的数据到内存中，下次要访问这一页的数据时，直接从内存中找，可以减少磁盘IO次数。磁盘IO是影响程序性能的主要因素，因为磁盘IO的速度远远低于内存IO。

MySQL的数据以页组成，它有指向下一页的指针和上一页的指针。MySQL在插入数据的时候，在页中排好了序。MySQL的InnoDb引擎中，页的大小是16KB，是操作系统的4倍，而int类型的数据是4个字节，其它类型的数据字节数通常也在4000字节以内，所以一页可以存放很多条数据。MySQL的数据正是以页为基本单位组合成的。在引入页的概念之后，MySQL会将多条数据存在一个叫“页”的数据结构中，当MySQL读取id=i的数据时，会将id=i数据所在的页整页读到内存中，然后在内存中进行遍历判断。由于内存的IO速度比磁盘高很多，所以相对于磁盘IO，几乎可以忽略不计。

页内数据区和多页模式本质上都是链表，可以采用相同的方式来对其进行优化，就是目录页。对比页内数据区分析如何优化多页结构。单页时采用了页目录的目录项来指向一行数据，这条数据就是存在于这个目录项中的最小数据，就可以通过页目录来查找所需数据。对于多页结构也可以采用这种方式，使用一个目录项来指向某一页， 而这个目录项存放的就是这一页中存放的最小数据的索引值。和页目录不同的地方在于，这种目录管理的级别是页，而页目录管理的级别是行。

目录页的本质也是页，普通页中存的数据是项目数据，而目录页中存的数据是普通页的地址。上图就是MySQL中的一种索引结构一B+树。

2.索引

索引是在存储引擎中实现的，不同的存储引擎会使用不同的索引。索引其实是一种数据结构，其功能是快速匹配查找到需要的数据行，其作用相当于超市里的导购员、书本里的目录。
不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多。如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据。例如有一张person表，其中有2W条记录，记录2W个人的信息。有一个Phone的字段记录每个人的电话号码，现在想要查询出电话号码为xxxx的人的信息。如果没有索引，那么将从表中第一条记录一条条往下遍历，直到找到该条信息为止。如果有了索引，那么会将该Phone字段，通过一定的方法进行存储，好让查询该字段上的信息时，能够快速找到对应的数据，而不必遍历2W条数据了。

InnoDB存储引擎支持两种常见的索引，一种是B+树索引，另一种是哈希索引。InnoDB存储引擎支持的哈希索引是自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。
B+树索引就是传统意义上的索引，这是目前关系型数据库系统中最常用、最有效的索引。B+树索引的构造类似于二叉树，根据键值(Key Value)快速找到数据。B+树中的B不是代表二叉(binary), 而是代表平衡(balance)，因为B+树是从最早的平衡二叉树演化而来，但是B+树不是一个二叉树。B+ 树索引并不能找到一个给定键值的具体行，能找到的只是被查找数据行所在的页，然后数据库通过把页读入内存，再在内存中进行查找，最后得到查找的数据。B+树的高度一般都在2~3层，对于查找某一键值的行记录，最多只需要2到3次IO。
B+树相较B树的优势:
1. B+树的磁盘读写代价更低。2. B+树的查询效率更稳定。3. B+树更有利于对数据库的扫描。

由于Hash索引会产生Hash冲突，存在Hash冲突的数据会被连接到同一个链表上，当大量数据被连接到相同链表上时，查询某条数据就变成了扫描该链表，时间复杂度不能保证在O(1)。

索引的特点

●加快了查询速度
●加速表与表之间的连接
●将服务器的随机IO变为顺序IO(因为B+树叶子节点连接在一起)

●只能创建在表上，不能创建在视图上
●既可以直接创建也可以间接创建
索引缺点:
带来额外的存储空间占用。其次在插入，更新和删除操作的同时需要维护索引，带来额外的时间开销。

索引使用原则

对经常更新的表避免对其进行过多的索引，对经常用于查询的字段应该创建索引；

数据量小的表最好不要使用索引，因为数据较少可能查询全部数据花费的时间比遍历索引的时间还短；

在值少的列上(字段上)不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反，在一个字段上不同值较多可以建立索引；
适合索引的列是出现在where子句中的列，或者连接子句中指定的列；

使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间。

不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。。

索引失效情况

●以%开头的like查询，key的值为null
●索引列上做计算，函数，类型转换会导致索引失效，转向全表扫描
●正则表达式不会使用索引
●复合索引的情况下，假如查询条件不满足最左原则，不会使用索引
●如果MySQL估计使用索引比全表扫描更慢，则不使用索引
●用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
●使用负向查询(not，not in， not like，<>,!= ,!> ,!< )不会使用索引

索引的优化

前缀索引:在对一个比较长的字符串进行索引时，可以仅索引开始的一部分字符，这样可以节约索引空间，提高索引效率。但是会降低索引的选择性。

聚簇索引
聚簇索引不是一种索引类型，而是一种存储数据的方式。Innodb的聚簇索引是在同一个数据结构中保存了索引和数据。一个表上只能有一个聚簇索引。Innodb使用主键来进行聚簇索引，没有主键就选择一个唯一的非空索引。如果还没有，innodb会选择生成一个隐式的主键来进行聚簇索引。一个数据表中的数据有且只有一种排序方式来存储在磁盘上。这也是innodb推荐使用自增主键的原因，因为自增主键自增且连续，在插入的时候只需要不断的在数据后面追加即可。
在其他索引的叶子节点中，存储的"数据"不是该数据的真实物理地址，而是该数据的主键，查找到主键之后再根据主键进行一次索引，拿到数据。聚簇索引和非聚簇索引的区别可以用一个简单的例子来说明：
一本书的目录就是主键，是一个聚簇索引，因为在目录中连续的内容，在正文中也是连续的。查看某章节只需要在目录中找到它对应的页面，然后去对应的页码查看正文即可。而非聚簇索引则类似书后面的附录专有名词索引一样(二级普通索引)，查找某个词，附录告诉你这个名词出现在某节，然后去目录(主键索引)中再次查找到对应的页码。

覆盖索引

辅助索引(二级索引)：非主键索引，叶子节点=键值+书签。Innodb存储引擎的书签就是相应行数据的主键索引值。再来看看什么是覆盖索引，有下面三种理解：

解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了满足查询语句中字段与条件的数据就叫做覆盖索引。

解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即索引包含了查询正在查找的所有数据）。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。当使用InnoDB引擎发起一个被索引覆盖的查询(也叫作索引覆盖查询)时，在EXPLAIN的Extra列可以看到“Using index”的信息。从执行结果上看，SQL语句只通过索引就取到了所需要的数据，这个过程就叫做索引覆盖。