《MySQL系列-InnoDB引擎01》MySQL体系结构和存储引擎

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

1 数据库和实例

  数据库：物理操作系统或者其他文件类型的集合，在MySQL中通常以frm、idb、MYD、MYI结尾的文件。

  数据库实例：MySQL数据库实例在系统上的表现就是一个进程。

  小结 => 数据库与数据库实例通常是一一对应的关系。但是在集群情况下，可能会存在一个数据库被多个数据实例使用的情况。

2 MySQL配置文件

  当启动实例时，MySQL数据库会去读配置文件，根据配置文件的参数来启动数据库实例。通过以下命令，可以查看当MySQL数据库实例启动时，会在哪些位置查找配置文件。

[root@zxy_master /]# mysql --help | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

  如上所示，MySQL数据库是按照顺序依次读取。那么就会存在这种情况：如果这几个文件都有同一个配置参数，那么MySQL会以哪个为准？MySQL会以读取到的最后一个配置文件的参数为准。在Linux环境下，配置文件通常会在/etc/my.cnf处。在Windows环境下，配置文件后缀名可能是.cnf，也有可能是.ini。

3 MySQL数据库路径

  MySQL启动配置文件后，就可以正常的创建数据库并进行使用了，那么我们创建的数据库文件是怎么存储的，存储在哪个位置呢？可通过以下命令查询：

mysql> show variables like 'datadir'\G;
*************************** 1. row ***************************
Variable_name: datadir
        Value: /var/lib/mysql/
1 row in set (0.00 sec)

  如上所示，MySQL的数据库文件存储到/var/lib/mysql/目录下，在这个目录下，会有文件夹名对应你的数据库名称。数据库中的每个表一般有两个文件，分别是.frm和.ibd来存储表空间和表数据。这两个文件可用来备份、迁移数据库。

通过表的.frm和.idb文件备份MySQL表

4 MySQL体系结构

MySQL体系结构通常由这几部分组成:

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

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

5 MySQL存储引擎

  每个存储引擎都有各自的特点，能够根据具体的应用建立不同的存储引擎表。MySQL的存储引擎可以分为MySQL官方存储引擎和第三方存储引擎。例如InnoDB最初的时候就是第三方引擎，后被Oracle收购，InnoDB引擎的应用极其广泛。

  可通过show engines命令查看数据库中的存储引擎列表

mysql> show engines\G;
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
9 rows in set (0.00 sec)

5.1 InnoDB存储引擎

  InnoDB存储引擎支持事务、行锁设计、支持外键，主要面向OLTP数据库应用。支持类似于Oracle的非锁定读，即默认读取操作不会产生锁。从MySQL 5.5.8版本开始，默认的存储引擎就是InnoDB存储引擎。

  InnoDB存储表由frm和ibd组成，frm为表空间，idb为表数据

5.2 MyISAM存储引擎

  MyISAM存储引擎不支持事务、表锁设计，支持全文索引，主要面向一些OLAP数据库应用。MyISAM的特点是，它的缓冲池只缓存索引文件，不缓存数据文件。

  MyISAM存储表由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件。

5.3 NDB存储引擎

  NDB存储引擎是一个集群存储引擎，其结构是share nothing的集群结构，因此能提供更高的可用性。NDB的特点是数据全部放在内存中(从MySQL 5.1版本开始，可以将非索引数据放在磁盘上)，因此主键查找的速度极快。NDB有一个问题值得注意的是，NDB存储引擎的连接操作(JOIN)是在MySQL数据库层完成，而不是存储引擎层完成，这意味着复杂的连接操作，需要巨大的网络开销。

5.4 Memory存储引擎

  Memory存储引擎将表中数据存放在内存中，如果数据库重启或发生故障，则表中数据都将消失。所以它更适合存储临时数据，以及数据仓库的维度表。Memory存储引擎默认使用哈希索引，而不是B+树索引。

  需要注意的是，MySQL数据使用Memory存储引擎作为临时表来存放查询的中间结果集。如果中间结果集大于Memory存储引擎表的容量设置，又或者中间结果含有含有TEXT或BLOB列类型字段，则MySQL数据库会把其转换到MyISAM存储引擎表而存放到磁盘中。之前提到的MyISAM不缓存数据文件，因此这时产生的临时表的性能对于查询会有损失。

5.5 Archive存储引擎

  Archive存储引擎只支持INSERT和SELECT操作，从MySQL5.1开始支持索引。Archive存储引擎使用zlib算法将数据行进行压缩后存储，压缩比为1:10。正如其名字所示，Archive存储引擎非常适合存储归档数据，例如日期信息。

  Archive存储引擎使用行锁来实现高并发的插入操作，但是其本身并不支持事务安全的存储引擎，其设计目标主要是提供插入和压缩功能。

5.6 Federated存储引擎

  Federate的存储引擎表并不存放数据，它是指向一台远程MySQL数据库服务器上的表。

6 连接MySQL

  需要理解的是，连接MySQL操作是一个连接进程和MySQL数据库实例进行通信。从程序设计角度来说，本质上是进程通信。常用的进程通信方式有管道、命名管道、命名字、TCP/IP套接字、UNIX域套接字。MySQL数据库提供的连接方式从本质上看都是上述提及的进程通信方式。

6.1 TCP/IP

  TCP/IP套接字是MySQL数据库在任何平台下都提供的连接方式，也是网络中使用最多的一种方式，这种方式在TCP/IP连接上建立一个基于网络的连接请求，一般情况下客户端(client)在一台服务器上，而MySQL实例(server)在另一台服务器上，这两台服务器通过TCP/IP网络连接。

  当客户端在请求连接MySQL实例的时候，MySQL实例会优先检验客户端的用户名以及IP是否允许登录。而校验的标准就是根据MySQL数据库中的user权限视图。

6.2 命名管道和共享内存

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

6.3 UNIX域套接字

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

mysql> show variables like 'socket';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| socket        | /var/lib/mysql/mysql.sock |
+---------------+---------------------------+
1 row in set (0.00 sec)

  通过-S指定UNIX域套接字登录

[root@zxy_master zxy]# mysql -uroot -S /var/lib/mysql/mysql.sock -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 572
Server version: 5.7.38-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>