MySQL技术内幕-InnoDB存储引擎-第一章、MySQL体系结构和存储引擎

第一章、MySQL体系结构和存储引擎

一、定义数据库和实例

两个容易混淆的词，“数据库”和“实例”

• 数据库
  物理操作系统或其他形式文件类型的集合。
  数据库文件可以是frm、MYD、MYDI、ibd结尾的文件，当使用NDB存储引擎的时候，数据库的文件可能不是操作系统上的文件，而是存在内存中的文件，但是定义仍然不变。

• 实例(程序)
  MySQL数据库由后台线程以及一个共享内存区组成。共享内存可以被运行的后台线程所共享。
  数据库实例是程序，是位于用户和操作系统之间的一层数据管理软件，要对这些文件进行select、update、insert、delete数据库操作，都是在数据库实例下进行的，应用程序只有通过数据库实例才能和数据库打交道。

数据库是一个个文件组成(二进制文件)的，需要实例来完成对数据库进行操作。
一般情况下一个数据库对应一个实例，但是在集群情况下，一个数据库被多个数据实例使用的情况。

MySQL是一个单进程多线程的数据库
可以使用mysqld_safe启动数据库实例(启动方式不止这一种)，并通过ps观察MySQL数据库启动后的进程情况。
当启动实例时，MySQL数据库会去读取配置文件，根据配置文件的参数来启动数据库实例，这与Oracle的参数文件(spfile)类似，不同的是，Oracle中如果没有参数文件，在启动实例的时候会提示找不到参数文件，数据库启动失败，在MySQL中，如果没有配置文件，MySQL会按照编译时的默认参数设置启动实例。用以下命令可以查看当MySQL数据库实例，启动时，会在哪些位置查找配置文件。
从上图可以看出，MySQL数据库是按照/etc/my.cnf-> /etc/mysql/my.cnf->/usr/local/mysql/etc/my.cnf ~/.cnf的顺序读取配置文件的，MySQL会以读取到的最后一个配置文件中的参数为准。在Linux环境下，配置文件一般放在/etc/my.cnf下，在Windows平台下，配置文件的后缀名可能.cnf，也可能是.ini。例如在Windows操作系统下运行mysql --help，可以找到如下类似内容：

配置文件中有一个参数datadir，该参数指定了数据库所在的路径，在Linux操作系统下，默认datadir为/usr/local/mysql/data，用户可以修改该参数，当然也可以使用该路径，不过该路径只是一个链接。
数据库文件就放在datadir指定的目录下，如下：

从上面可以看到，其实datadir目录是一个链接，该链接指向了/opt/mysql_data目录，当然，用户必须保证/opt/mysql_data的用户和权限，使得只有mysql用户和组可以访问。

二、MySQL体系结构

从图1-1可以看出，MySQL由以下几个部分组成：

• 连接池组件
• 管理服务和工具组件
• SQL接口组件
• 查询分析器组件
• 优化器组件
• 缓存组件
• 插件式存储引擎
• 物理文件

MySQL数据库区别于其他数据库的最重要的一个特点是其插件式的表存储引擎。需要特别注意的是，存储引擎是基于表的，而不是数据库。

三、MySQL存储引擎

存储引擎的好处是，每个存储引擎都有各自的特点，能够根据具体的应用建立不同存储引擎表。
由于MySQL数据库开源的特性，用户可以根据MySQL预定义的存储引擎接口编写自己的存储引擎，若用户对某一种存储引擎的性能或功能不满意，可以通过修改源代码来得到想要的特性，这就是开源带给我们的方便和力量。
MySQL存储引擎可以分为官方存储引擎和第三方存储引擎，有些第三方存储引擎很强大，如大名鼎鼎的InnoDB存储引擎。

1、InnoDB存储引擎

InnoDB存储引擎支持事务，设计目标主要面向在线事务处理(OLTP)的应用。其特点是行锁设计和支持外键，并支持Oracle的非锁定读即默认读取操作不产生锁，从5.5.8版本开始，InnoDB存储引擎是默认的存储引擎。

• 支持事务
• 支持行锁
• 支持外键
• 默认读操作不产生锁

InnoDB通过使用多版本并发控制(MVCC)来获得高并发性，并且实现了SQL标准的4种隔离级别，默认为可重复读级别。同时使用一种被称为next-key locking的策略来避免幻读现象的产生。

Innodb默认的隔离级别是可重复读级别。同时使用next-key lock解决幻读
除此之外，InnoDB还提供了插入缓存、二次写、自适应哈希索引、预读等高性能和高可用的功能。

对于表中的数据，InnoDB存储引擎采用了聚集(clustered)的方式，因此每张表的存储都是按照主键的顺序进行存放。如果没有显式的在表定义的时候指定主键，InnoDB会为每一行生成一个ROWID，并以此作为主键。

2、MyISAM存储引擎

• 不支持事务
• 支持表锁
• 支持全文索引
  5.5.8之前，MyISAM存储引擎是默认的存储引擎(除了windows版本外)。
  MyISAM存储引擎的另外一个与众不同的地方是它的缓存池只缓存索引文件，而不缓存数据文件。

MyISAM存储引擎表由MYD和MYI组成，MYD用来存储数据文件，MYI用来存储索引文件，可以通过使用myisampack工具来进一步压缩数据文件，因此使用myisampack工具压缩后的表是只读的，当然用户也可以通过myisampack来解压数据文件。

在MySQL5.0版本之前，MyISAM默认支持的表大小为4g，如果需要支持大于4g的MyISAM表的时候，则需要指定MAX_ROWS和AVG_ROW_LENGTH属性，从MySQL5.0开始，MyISAM默认支持256TB的单表数据，这足够满足一般应用需求。

3、NDB存储引擎

NDB存储引擎的特点是数据全部放在内存中(从5.1版本开始，可以将非索引数据放在磁盘上)，因此主键查找的速度极快，并且通过添加NDB数据存储节点可以线性的提高数据库性能，是高可用和高性能的集群系统。

四、各存储引擎之间的比较

下面的表展示了一些常用MySQL存储引擎之间的不同之处，包括存储容量的限制、事务支持、锁的粒度、MVCC支持、支持的索引、备份和复制等。

可以使用SHOW ENGINES语句查看当前使用的MySQL数据库所支持的存储引擎，也可以通过查找information_schema架构下的ENGINES表，如下所示：


查看engines表的数据：

比较不同引擎下表的大小

使用MyISAM存储引擎的时候，表的大小为40.7MB，使用InnoDB存储引擎的时候表的大小增加到了113.6MB，使用Archive存储引擎的时候表的大小却只有20.2MB。

五、连接数据库

连接MySQL操作是一个连接进程和MySQL数据库实例进行通信，从程序设计的角度看，本质上是进程通信，如果对进程通信比较了解，可以知道常用的进程通信方式有管道、命名管道、命名字、TCP/IP套接字、UNIX域套接字。

1、TCP/IP

TCP/IP套接字方式是MySQL数据库在任何平台下都提供的连接方式，也是网络中使用最多的一种方式，这种方式在TCP/IP连接上建立一个基于网络的连接请求。例如用户可以在windows服务器下请求一台远程linux服务器下的MySQL实例，如下所示：

远程连接阿里云服务器的问题

阿里云会关闭这些功能，需要开下来，主机才能连接远程MySQL。
同时需要修改配置文件，把bind 127.0.0.1注释掉

否则会报下面的错误：

需要注意的是，在通过TCP/IP连接到MySQL实例的时候，MySQL数据库会先检查一张权限视图，用来判断发起请求的客户端IP是否允许连接到MySQL实例，该视图在MySQL架构下，表名为user。

从上面的表可以看出，MySQL允许root在任何IP段下连接该实例，而只允许renjie在本地连接该实例。

2、命名管道和共享内存

在Windows2000、Windows XP、Windows 2003和Windows Vista以及在此之上的平台上，如果两个需要进程通信的进程在同一个服务器上，那么可以使用命名管道，在MySQL数据库中须在配置文件中启用–enable-named-pipe选项，在MySQL4.1之后的版本中，MySQL还提供了共享内存的连接方式，也是通过在配置文件中添加–shared-memory实现的，如果你想使用共享内存的方式，在连接时，MySQL客户端还必须使用–protocol=memory选项。

3、UNIX套接字

在Linux和UNIX环境下，还可以使用UNIX域套接字，UNIX域套接字其实不是一个网络协议，所以只能在MySQL客户端和数据库实例在一台服务器上的情况下使用，用户可以在配置文件中指定套接字文件的路径，如–socket=/tmp/mysql.sock。当数据库实例启动后，用户可以通过下列命令来进行UNIX域套接字文件的查找。