MySQL概述

1.MySQL的历史发展

2.了解关系数据库

3.MySQL的工作原理

4.MySQL数据库存储引擎

1.1MySQL的发展历史

    MySQL最初是由一个名叫蒙蒂·维德纽斯（Monty Widenius）的小职员为一个叫TcX的公司设计的一款底层面向报表的存储引擎工具——Unireg。

    在1985年，Monty和几个志同道合的朋友在瑞典成立了一家公司，也就是MySQL AB的前身。

    1996年，MySQL 1.0正式发布，提供的功能非常简单，只有表数据的INSERT（插入）、UPDATE（更新）、DELETE（删除）和SELECT（查询）操作。

    1999～2000年，Monty团队成立了MySQL AB公司，与Sleepcat合作开发了Berkeley DB引擎，MySQL从此支持事务处理。

    2000年，MySQL公布了源码，并采用了通用公共许可协议（GNU General Public License，GPL）；4月，MySQL对旧的存储引擎进行了整理，命名为MyISAM，同时支持全文搜索。

    2001年，Heikki Tuuri提议集成自己的InnoDB引擎到MySQL中，这个存储引擎不仅支持行级锁，而且还支持事务处理，MySQL和InnoDB正式结合版本为4.0。

    2004年，MySQL发了4.1版本，新增了子查询。

    2005年10月，MySQL发布了5.0版本，新版本加入了存储过程、触发器、视图等，MySQL逐渐向高性能数据库方向发展。

    2008年1月，MySQL被Sun公司收购。

    2009年4月，Oracle公司收购了Sun公司，MySQL转入Oracle旗下。

    2010年，MySQL 5.5发布，新特性包括半同步的复制以及对SIGNAL/RESIGNAL异常处理功能的支持，同时InnoDB引擎变为MySQL的默认存储引擎，还加强了在企业应用方面的特性。令人庆幸的是，Oracle承诺MySQL 5.5和未来的版本仍然是采用GPL协议的开源数据库。

    在 MySQL 5.5 发布两年后，Oracle 宣布 MySQL 5.6 正式版发布，首个正式版版本号为 5.6.10。在 MySQL 5.5 中使用的是 InnoDB 作为默认的存储引擎，而MySQL 5.6则对InnoDB 引擎进行了改造，提供全文索引能力，使 InnoDB 适合各种应用场景。

    2015 年，MySQL 5.7 GA 重磅发布，新特性包括160 万只读 QPS，动态修改 Buffer Pool，提高运维效率，优化器 Server 层改进、InnoDB 层优化等。

2.2 了解关系数据库

    MySQL作为最流行的关系型数据库管理系统之一，具备了容易理解、使用方便、易于维护等特性。

1.2.1 什么是关系数据库

    数据库（Database）是以特定数据结构组织，在计算机设备上存储和管理数据的“仓库”。在日常生活和工作中，有许多数据集，需要将它们归档到这样一个仓库中，以便进行数据统计和查询等相关管理。数据库有很多类型，通常根据不同数据组织类型分为层次式数据库、网络式数据库、关系数据库和面向对象的数据库4种。

    关系数据库管理系统（Relational Database Management System， RDBMS），是指通过关系模型来组织数据的数据库。关系数据库把世界看成由实体和联系组成。

    关系模型最初在1970年由IBM的研究员E.F.Codd博士提出，在之后的几十年中，关系模型的概念得到了很好的发展，并且逐渐成为主流数据库架构模型。可以简单地把关系数据库理解为由二维表格建立的数据组织（类似Excel，由行和列组织数据）和二维表格之间的联系构成的数据关联。

1.2.2　关系模型

    关系模型是用二维表的形式表示实体以及实体之间联系的数据模型。数据都是以表格的形式存在的，每行对应一个实体的记录，每列对应实体的某种属性，若干行和列构成了整个表数据。

    实体关系模型是能直观表示实体、属性以及和实体间联系的模型，可以通过实体关系图（Entity Relationship Diagram， E-R图）来表示，是用来理解现实生活中的实体关系、建立概念模型非常有效的工具。

    例如，某个社交网站和用户之间的关系如图1.2所示。

在图1.2的E-R图中，社交网站和用户代表实体，用矩形表示，社交网站拥有属性：企业性质、网站名、上市，用椭圆形表示；用户拥有属性：姓名、手机号、生日，也用椭圆表示；实体社交网站和实体用户之间的联系，用菱形表示。在实体关系模型中，联系（也称关系）有3种类型：一对一关系（1:1），如用户有一个会员编号，一个会员编号能确定唯一一个用户，他们之间是一一对应的；一对多关系（1:N），如用户可以注册多个登录账号，而注册账号只能被当前的用户使用；多对多关系（N:N），如社交网站可以有多个用户，而用户同样可以拥有多个社交网站。

 

1.2.3　常用的关系型数据库

    比较常用的关系型数据库有Oracle、MySQL、SQL Server、 IBM DB2、Sybase、Access等，Oracle是收费商用的数据库，提供很好的维护与支持，适用于业务逻辑较复杂、数据量大的大中型项目；MySQL数据库由于体积小、速度快、总体拥有成本低、开放源码，受到很多中小型公司的青睐；SQL Server数据库的功能比较全面、效率高，适用于中型企业或单位的数据库平台。

1.2.4　SQL结构化查询语言

    SQL（Structured Query Language）意为结构化查询语言，是一种用于数据库查询和程序设计的语言，可用于操作数据库的查询、存取更新数据以及管理数据库，是通用的数据库查询语言，它具有极大的灵活性和很多强大的功能。虽然SQL是国际标准的关系型数据库管理语言，但事实上，很多流行的数据库都对SQL规范做了些修改扩充，所以，不同的数据库之间不能完全地互通，如MySQL没有top命令，SQL Server没有limit命令等。

1.3　MySQL的工作原理

    了解了关系数据库的基础知识，下面我们开始讲解MySQL。

    为了理解MySQL的工作原理，我们先看一张经典的架构图，如图1.3所示。

    MySQL的内部架构由以下几个部分组成。

（1）编程语言交互接口（Connectors）：指的是不同语言与SQL的交互接口，如Java的JDBC、.Netframework的ODBC。

（2）系统管理和控制工具集合（Management Services & Utilities）：提供管理配置服务、备份还原、安全复制等功能。

 

（3）连接池（Connection Pool） ：接受客户端的请求，缓存请求，检查内存可利用情况，如果没有可用线程，就创建线程执行任务，有可用线程就重复利用。

（4）解析器（Parser）：解析验证SQL语法，分解SQL成相应的数据结构，以备后面处理。

（5）查询优化器（Optimizer）：对SQL语句进行优化处理，优化执行路径，生成执行树，最终数据库会选择认为最优的方案执行并返回结果。

（6）SQL接口（SQL Interface）：接受用户的SQL命令，并返回结果。

（7）查询缓存（Cache & Buffer）：缓存查询结果。如果SQL查询中命中查询结果，将直接从缓存中返回结果，不再执行SQL分析等操作；没有命中，才会进行后续的解析、查询优化、执行SQL接口，返回结果，同时将结果加入缓存中。

（8）存储引擎（Pluggable Storage Engines）：是MySQL中具体的与文件打交道的子系统，可以看到它是以插件形式存在的，意味着可以自定义存储引擎，这是MySQL很特别的地方。MySQL提供了很多存储引擎，其优势各不一样，有的查询效率高、有的支持事务等，最常用的有MyISAM、InnoDB、BDB。

（9）文件系统（File System）：是存放数据库表数据以及相关配置的地方。

1.4　MySQL数据库存储引擎

    MySQL中的数据可以采用不同的技术存储在文件（或内存）中。这些技术都使用不同的存储机制、索引技巧、锁定水平，并且最终提供广泛的、不同的功能和能力。

    通过选择不同的技术，我们能够获得额外的速度或者功能，从而改善应用的整体功能，每种技术以及配套的相关功能就可以看成是一种数据库存储引擎，MySQL默认配置了许多不同的存储引擎，这些存储引擎可以预先设置或者在MySQL服务器中启用。

MySQL自身提供的存储引擎有InnoDB（5.5版本后为默认存储引擎）、MyISAM、MEMORY、CSV、ARCHIVE、BLACKHOLE等。

1.4.1　InnoDB引擎

    作为默认存储引擎，InnoDB具备以下主要优势。

（1）数据操纵语言（Data Manipulation Language，DML）操作遵循事务的4个特性——原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability），并通过commit、rollback、crash-recovery保障数据的安全。

（2）具有行级锁和Oracle风格的读一致性，通过一种更改缓存机制对新增、更新和删除进行优化，增加了对用户并发读写操作和性能。

（3）对表进行基于主键的优化查询，每张表都有一个基于主键的聚集索引，以此达到减少磁盘I/O，进而提高了搜索效率和性能。

（4）支持外键约束，检查外键、插入、更新和删除，以确保数据的完整性。

（5）InnoDB引擎提供了专门的缓存池，在内存中缓存了表和索引的数据，常用的数据可以直接从内存中处理，比直接从磁盘获取数据处理速度快。

（6）可以压缩表和相关索引，创建和删除索引以达到提高性能的目的。

（7）快速压缩表空间，并能释放磁盘空间，保证系统能够重用，而不仅仅是腾出空间给InnoDB复用。

（8）创建表时可以通过语句显示指定表引擎：

CREATE TABLE TABLE_NAME (I INT) ENGINE = INNODB;

1.4.2　MyISAM引擎

    MyISAM引擎不支持事务、外键，不过访问速度非常快，表的存储分为以下3个文件。

（1）frm文件：存储表定义。

（2）MYD（MYData）文件：存储数据。

（3）MYI（MYIndex）文件：存储索引。

它有以下特点。

（1）所有数值类型键值都是以高字节存储的，以便于更好地索引压缩。

（2）每张MyISAM表最多支持（232）2（1.844E+19）行。

（3）每张MyISAM表支持的最大索引数是64，每个索引最多16列。

（4）当表字段是通过自增长（auto_increment）的，索引树节点只会包含一个键，这样可以提高索引的空间利用率。

（5）在INSERT、UPDATE时，MyISAM有内部处理会自动更新auto_ increment字段，这使auto_increment处理更快（至少10%）。

（6）当进行混合操作（删除、更新、插入同时进行）时，MyISAM通过自动合并和扩展删除块，减少了行碎片。

（7）MyISAM支持并发插入数据：如果一个表中的数据文件中没有空闲块，则可以在插入数据的同时通过其他线程读取表数据。空闲块是由删除或更新操作时数据长度超过当前行内容长度引起的。

（8）可以通过将数据文件（MYD）和索引文件（MYI）放在不同物理设备的不同目录上，来更快地创建表。

（9）BLOB和TEXT可以被索引，索引列中允许NULL值，不过需要占0～1个字节。

（10）创建表时可以通过语句显示指定表引擎：

CREATE TABLE TABLE_NAME (I INT) ENGINE = MYISAM;

1.4.3　MEMORY引擎

    MEMORY引擎又称为HEAP引擎，用来创建特殊用途的表，且内容存储在内存中。将数据存储在内存中，能够实现快速访问和低延迟。

    因此，使用MEMORY引擎，在出现数据崩溃、硬件故障等问题时，数据极易丢失；它适合临时态和非关键数据（如会管理或缓存等）的操作。

    创建表时可以通过语句显示指定表引擎：

CREATE TABLE TABLE_NAME (I INT) ENGINE = MEMORY;

1.4.4　CSV引擎

    当用户创建一个CSV引擎的表时，服务器会在数据库目录中创建一个“表名.frm”的格式文件，同时还会创建一个“表名.csv”的数据文件，该数据文件中的数据是以逗号分隔保存的，它主要用于CSV报表格式的数据存储，应用面比较窄。

    缺点：CSV引擎不支持索引，也不支持分区，并且所有列必须指明为NOT NULL。

    创建表时可以通过语句显示指定表引擎：

CREATE TABLE TABLE_NAME (I INT) ENGINE = CSV;

1.4.5　ARCHIVE引擎

    ARCHIVE引擎用于数据归档，它的压缩比例非常高，适合存储历史数据（前提是不做查询操作），所占的存储空间不到InnoDB引擎的1/10；它支持行级锁实现并发插入操作，却不支持事务，其设计目的在于提供高速插入和压缩功能；另外，它不支持索引。

    创建表时可以通过语句显示指定表引擎：

CREATE TABLE TABLE_NAME (I INT) ENGINE = ARCHIVE;

1.4.6　BLACKHOLE引擎

    BLACKHOLE引擎是很特别的一种引擎，它的表不存储任何数据，就像是“黑洞”一样。它主要用于充当伪服务器、日志服务器、增量备份服务器等。

    创建表时可以通过语句显示指定表引擎：

CREATE TABLE TABLE_NAME (I INT) ENGINE = ARCHIVE;

    MySQL还支持其他引擎：MERGE引擎、FEDERATED引擎、EXAMPLE引擎等，可以查看相关文档。

1.4.7　存储引擎特点对比

    MySQL在5.5版本之后默认的存储引擎是InnoDB存储引擎，下面对比一下MySQL 5.7版本存储引擎的特点，如表1.1所示。

                                      表1.1　MySQL 5.7版本存储引擎的特点

 

选择存储引擎的建议如下。

（1）MySQL的存储引擎很多，不同的库、不同的表可以选择不同的存储引擎，推荐同一个库用同一种存储引擎，因为不同存储引擎的表之间join操作比较慢。

（2）InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全表，如果需要事务处理、ACID事务支持，则选择InnoDB存储引擎。

（3）Memory将所有数据保存在缓存RAM中，可以提供极快的访问速度。

（4）尽量不要选择MyISAM存储引擎，它只能用单个CPU，内存只能用到4GB，内存里只有索引，而且并发能力差。

MySQL作为语言，还包括下列成份：

1. 数据定义语言(Data Definition Language, DDL)

数据定义语言用于对数据库及数据库中的各种对象进行创建、删除、修改等操作。数据定义语言的主要SQL语句有CREATE语句、ALTER语句、DROP语句等。

2. 数据操纵语言(Data Manipulation Language, DML)

数据操纵语言用于操纵数据库中各种对象，进行检索、插入、修改、删除等操作。数据操纵语言的主要SQL语句有INSERT语句、UPDATE语句、DELETE语句等。

3. 数据查询语言（data query language，DQL）

数据查询语言用于从表或视图中检索数据，SELECT语句是使用最频繁的SQL语句之一。

4. 数据控制语言（data control language，DCL）

数据控制语言用于安全管理，即确定哪些用户可以查看或修改数据库中的数据。数据控制语言的主要SQL语句有：GRANT语句，用于授予权限；REVOKE语句，用于收回权限。

5. MySQL扩展增加的语言要素

这部分不是标准SQL所包含的内容，而是为了用户编程的方便而增加的语言要素。这些语言要素包括常量、变量、运算符、表达式、内置函数等。