【MySQL学习】1.基础

1 推荐书籍

-《深入浅出 MySQL》
-《深入理解 MySQL 核心技术》
-《高性能 MySQL》
-《MySQL 技术内幕 : InnoDB 存储引擎 第 2 版》
-《MySql必知必会》

2 MySQL 的分支、变种与替代

MySQL 变种有好几个，主要有三个久经考验的主流变种:Percona Server， MariaDB 和 Drizzle。它们都有活跃的用户社区和某种程度上的商业支持，均由独立的服务供应商支持。

2.1 Drizzle

Drizzle 是真正的 MySQL 分支，而且是完全开源的产品，而非只是个变种或增强版本。它并不与 MySQL 兼容，不能简单地将 MySQL 后端替换为 Drizzle。

Drizzle 与 MySQL 有很大差别，进行了一些重大更改，甚至 SQL 语法的变化都非常大，设计目标之一是提供一种出色的解决方案来解决高可用性问题。在实现上，Drizzle 清除了一些表现不佳和不必要的功能，将很多代码重写，对它们进行了优化，甚至将所用语言从 C 换成了 C++。

此外，Drizzle 另一个设计目标是能很好的适应具有大量内容的多核服务器、 运行 Linux 的 64 位机器、云计算中使用的服务器、托管网站的服务器和每分钟接收数以万计点击率的服务器并且大幅度的削减服务器成本。

2.2 MariaDB

在 Sun 收购 MySQL 后，Monty Widenius，这位 MySQL 的创建者，因不认同 MySQL 开发流程而离开 Sun。他成立了 Monty 程序公司，创立了 MariaDB。MariaDB 的目标是社区开发，Bug 修复和许多的新特性。实际上，可以将 MariaDB 视为 MySQL 的扩展集，它不仅提供 MySQL 提供的所有功能，还提供其他功能。MariaDB 是原版 MySQL 的超集，因此已有的系统不需要任何修改就可以运行。

诸如 Google，Facebook、维基百科等公司或者网站所使用了 MariaDB。不过 Monty 公司不是以赢利为目的，而是由产品驱动的，这可能会带来问题，因为没有赢利的公司不一定能长久维持下去。

2.3 Percona Server

由领先的 MySQL 咨询公司 Percona 发布，Percona 公司的口号就是The Database Performance Experts，Percona 的创始人也就是《高性能 MySQL》书的作者。

Percona Server 是个与 MySQL 向后兼容的替代品，它尽可能不改变 SQL 语法、 客户端/服务器协议和磁盘上的文件格式。任何运行在 MySQL 上的都可以运行在 Percona Server 上而不需要修改。切换到 Percona Server 只需要关闭 MySQL 和启动 PerconaServer，不需要导出和重新导入数据。

Percona Server 有三个主要的目标：

• 透明，增加允许用户更紧密地查看服务器内部信息和行为的方法。比如慢查询日志中特别增加的详细信息；
• 性能，Percona Server 包含许多性能和可扩展性方面的改进，还加强了性能的可预测性和稳定性。其中主要集中于 InnoDB；
• 操作灵活性，Percona Server 使操作人员和系统管理员在让 MySQL 作为架构的一部分而可靠并稳定运行时提供了很多便利。

一般来说，Percona Server 中的许多特性会在后来的标准 MySQL 中出现。 国内公司阿里内部就运行了上千个 Percona Server 的实例。

2.4 Postgre SQL

PostgreSQL 称自己是世界上最先进的开源数据库，同时也是个一专多长的全栈数据库。最初是 1985 年在加利福尼亚大学伯克利分校开发的。

• PostgreSQL 的稳定性极强，在崩溃、断电之类的灾难场景下依然可以保证数据的正确；
• 在高并发读写，负载逼近极限下，PG 的性能指标仍可以维持双曲线甚至对数曲线，到顶峰之后不再下降，表现得非常稳定，而 MySQL 明显出现一个波峰后下滑；
• PG 多年来在 GIS 领域处于优势地位，因为它有丰富的几何类型，实际上不止几何类型，PG 有大量字典、数组、bitmap 等数据类型，相比之下 mysql 就差很多。

所以总的来说，PostgreSQL 更学术化一些，在绝对需要可靠性和数据完整性的时候，PostgreSQL 是更好的选择。但是从商业支持、文档资料、 易用性，第三方支持来说，MySQL 无疑更好些。

2.5 SQLite

SQLite 是世界上部署最广泛的数据库引擎，为物联网(IoT)下的数据库首选，并且是手机，PDA，甚至 MP3 播放器下的首选。

SQLite 代码占用空间小， 并且不需要数据库管理员的维护。

SQLite 没有单独的服务器进程，提供的事务也基本符合 ACID。当然，简单也就意味着功能和性能受限。

3 MySQL 体系架构

上图

MySQL体系架构

可以看出 MySQL 最上层是连接组件。下面服务器是由连接池、管理工具和服务、SQL 接口、解析器、优化器、缓存、存储引擎、文件系统组成。

3.1 连接池

由于每次建立建立需要消耗很多时间，连接池的作用就是将这些连接缓存下来，下次可以直接用已经建立好的连接，提升服务器性能。

3.2 管理工具和服务

系统管理和控制工具，例如备份恢复、Mysql 复制、集群等。

3.3 SQL接口

接受用户的SQL命令，并且返回用户需要查询的结果。比如select from 就是调用 SQL Interface。

3.4 解析器

SQL 命令传递到解析器的时候会被解析器验证和解析。

解析器主要功能：

• 将 SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后 SQL 语句的传递和处理就是基于这个结构的；
• 如果在分解构成中遇到错误，那么就说明这个 SQL 语句是不合理的。

3.5 优化器

查询优化器，SQL 语句在查询之前会使用查询优化器对查询进行优化。

3.6 缓存器

查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key 缓存， 权限缓存等。

3.7 存储引擎

是底层物理结构和实际文件读写的实现。

类型有InnoDB存储引擎，MylSAM 存储引擎，CSV 引擎，Memory 引擎，NDB 集群引擎，Federated 引擎，Blackhole 引擎，Archive 引擎，Mrg_MylSAM。

还有第三方引擎Percona 的 XtraDB 存储引擎，TokuDB 引擎，Infobright。

从MySQL 5.6及以后，MySQL数据库默认的引擎就是InnoDB，在 MySQL 5.1 及之前的版本，MyISAM 是默认的存储引擎。

3.7.1 InnoDB 存储引擎

• 特性
  • InnoDB是事务性存储引擎，支持事务，也是MySQL内唯一一个支持事务的存储引擎；
  • 完全支持事务的ACID特性；
  • InnoDB支持行锁、页锁和表锁，在使用得当的时候，可以有效地提高数据库的并发程度。

3.7.2 MylSAM 存储引擎

提供如压缩表、空间数据索引、全文索引等特性。

不支持事务和行级锁，崩溃后无法安全恢复，但它绝不是一无是处的。对于只读的数据，或者表比较小、可以忍受修复(repair)操作，则依然可以继续使用 MyISAM。

3.7.3 MyISAM和InnoDB的比较

MyISAM和InnoDB是MySQL数据库中最常用的两个引擎，下面比较一下两个引擎的区别。

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁 不适合高并发的读写操作	行锁 适合高并发操作 同时也支持表锁和页锁
缓存	只缓存索引，不缓存真实数据	不仅缓存索引，还要缓存真实数据，对内存要求高
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

3.8 文件系统

3.8.1 目录和文件

• bin目录：存放着许多可执行文件，比如mysqld、mysqldump。
• 数据目录

像InnoDB、MyISAM 这样的存储引擎都是把表存储在磁盘上的，而操作系统用来管理磁盘的那个东东又被称为文件系统，所以用专业一点的话来表述就是：

像 InnoDB、MyISAM 这样的存储引擎都是把表存储在文件系统上的。
当我们想读取数据的时候，这些存储引擎会从文件系统中把数据读出来返回给我们， 当我们想写入数据的时候，这些存储引擎会把这些数据又写回文件系统。

3.8.1.1 确定 MySQL 中的数据目录

show variables like 'datadir';

3.8.1.2 数据目录的作用

MySOL 在运行过程中都会产生哪些数据呢?
当然会包含我们创建的数据库、 表、视图和触发器等用户数据，除了这些用户数据，为了程序更好的运行，MySQL 也会创建一些其他的额外数据。

• 数据库在文件系统中的表示
  • 在数据目录下创建一个和数据库名同名的子目录（或者说是文件夹)；
  • 在与该数据库名同名的子目录下创建一个名为db.opt 的文件，这个文件中包含了该数据库的各种属性，比方说该数据库的字符集和比较规则是个啥。
• 表在文件系统中的表示
  • 表结构的定义：格式：表名.frm；
  • 表中的数据：在MyISAM、InnoDB存储引擎中又各不相同。
    • MyISAM：数据和索引是分开存放的；无表空间一说；表数据都存放到对应的数据库子目录下。
    • InnoDB：在MySQL5.6版本之前，InnoDB默认是采用系统表空间，但是5.6以后就默认采用了独立表空间；

Tips：

• 系统表空间：一个MySQL数据库中，存储的数据都是放在一个系统级的文件中，也就是所有表的数据都存储在同一个文件中。
• 独立表空间：每个表的数据对应一个数据文件，互不干扰。

独立表空间和系统表空间的比较：

• 系统表空间无法简单的收缩文件的大小；
• 独立表空间可以通过optimize table收缩文件；
• 系统表空间会产生IO瓶颈，在数据量大的时候，读取效率较独立表空间慢；
• 独立表空间可以同时向多个文件刷新数据，因为每个表对应的存储文件是独立的。

InnoDB下的独立表空间

3.8.1.2 日志文件

• 错误日志（error log）
  • 对MySQL 的启动、运行、关闭过程进行了记录；
  • 也记录一些警告信息或正确的信息；
  • 查看错误日志文件的位置：show variables like 'log_error'\G。
• 慢查询日志（slow query log）
• 查询日志（query log）：默认文件名：主机名.log。
• 二进制文件（bin log）
  • 记录了对MySQL 数据库执行更改的所有操作；
  • 不包括select 和show 这类操作；
  • 几种作用：恢复（recovery）,复制（replication）,审计（audit）。

3.8.1.3 其他的额外数据

• 服务器进程文件
• socket 文件
• 默认/自动生成的SSL 和RSA 证书和密钥文件。

4 系统库

4.1 performance_schema

保存MySQL 服务器运行过程中的一些状态信息，包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等等信息。

4.2 sys

通过视图的形式把information_schema 和 performance_schema 结合起来，让程序员可以更方便地了解MySQL 服务器的一些性能信息。

支持MySQL 5.6 或更高版本。

4.3 information_schema

保存着MySQL 服务器维护的所有其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据。

4.4 mysql

在 MySQL 系统库中，MySQL 访问权限系统表，放在 MySQL 库中，主要包含如下几个表：

• user：包含用户账户、全局权限和其他非权限列表(安全配置字段和资 源控制字段)。
• db：数据库级别的权限表。该表中记录的权限信息代表用户是否可以使 用这些权限来访问被授予访问的数据库下的所有对象(表或存储程序)。
• tables_priv：表级别的权限表。
• columns_priv：字段级别的权限表。
• procs_priv：存储过程和函数权限表。
• proxies_priv：代理用户权限表。

下一章节讲讲事务和事务的隔离级别。