第一章 MySQL 体系结构和存储引擎（《MySQL 技术内幕 InnoDB 存储引擎》读书笔记）

本文链接：https://blog.csdn.net/m0_52462015/article/details/118656819

MySQL 被设计为一个可移植的数据库，几乎在当前所有的系统上都能运行，如 Linux，Solaris，FreeBSD，Mac 和 Windows。尽管各平台在底层（如线程）实现方面都各有不同，但是 MySQL 基本上能保证在各平台上的物理体系结构的一致性。

1.1 定义数据库和实例

数据库：物理操作系统文件或其他形式文件类型的集合。
实例：MySQL 数据库通常由后台线程以及一个共享内存区组成。共享内存可以被运行的后台线程所共享。需要牢记的是，数据库实例才是真正用于操作数据库文件的。

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

MySQL 被设计为一个单进程多线程架构的数据库，也就是说，MySQL 数据库实例在系统上的表现就是一个进程。

当启动实例时，MySQL数据库会去读取配置文件，根据配置文件的参数来启动数据库实例，这与 Oracle 的参数文件相似，不同的是，Oracle 中如果没有参数文件，在启动实例会提示找不到该参数文件，数据库启动失败。而在 MySQL 数据库中，可以没有配置文件，在这种情况下，MySQL 会按照编译时的默认参数设置启动实例。

MySQL 数据库是按 /etc/my.cnf -> /etc/mysql/my.cnf -> /usr/local/mysql/etc/my.cnf -> ~/.my.cnf 的顺序来读取配置文件的，如果几个配置文件中都有同一个参数，以读取到的最后一个配置文件的参数为准！

1.2 MySQL 体系结构

从概念上来说

数据库是文件的集合，是依照某种数据模型组织起来并存放于二级存储器中的数据集合。
数据库实例是程序，是位于用户和操作系统之间的一层数据管理软件，用户对数据库的任何操作，包括数据库定义、数据查询、数据维护、数据库运行控制等都是在数据库实例下进行的，应用程序只有通过数据库实例才能和数据库打交道。

MySQL 数据库体系结构

连接者：不同语言的代码程序和mysql的交互（SQL交互）

1、连接池 管理、缓冲用户的连接，线程处理等需要缓存的需求

2、管理服务和工具组件 系统管理和控制工具，例如备份恢复、Mysql复制、集群等

3、sql接口 接受用户的SQL命令，并且返回用户需要查询的结果

4、查询解析器 SQL命令传递到解析器的时候会被解析器验证和解析(权限、语法结构)

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

select id,name from user where age = 40;
a、这个 select 查询先根据 where 语句进行选取，而不是先将表全部查询出来以后再进行age过滤
b、这个 select 查询先根据 id 和 name 进行属性投影，而不是将属性全部取出以后再进行过滤
c、将这两个查询条件联接起来生成最终查询结果
6、缓存 如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据
7、插入式存储引擎 存储引擎说白了就是如何管理操作数据（存储数据、如何更新、查询数据等）的一种方法。因为在关系数据库
中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

需要注意的是：存储引擎是基于表的，而不是数据库！

1.3 MySQL 存储引擎

存储引擎的好处是，每个存储引擎都有各自的特点，能够根据具体的应用建立不同存储引擎表。

1.3.1 InnoDB 存储引擎

InnoDB 存储引擎支持事务，其设计目标主要面向在线事务处理（OLTP）的应用。其特点是行锁设计、支持外键、并支持类似于 Oracle 的非锁定读，即默认读取操作不会产生锁。MySQL 数据库从 5.5.8 开始，InnoDB 存储引擎是默认的存储引擎。

InnoDB 存储引擎将数据放在一个逻辑的表空间中，这个表空间就像一个黑盒一样由 InnoDB 存储引擎自身进行管理。此外，InnoDB 存储引擎支持用裸设备用来建立其表空间。

InnoDB 通过使用多版本并发控制（MVCC）来获得高并发性，并且实现了 SQL 标准的四种隔离级别（四种隔离级别分别为读未提交、读已提交、可重复读、序列化），默认为 REPEATABLE（可重复读）。同时，使用一种被称为 next-key-locking（间隙锁）的策略来避免幻读现象的产生。除此之外，InnoDB 存储引擎还提供了插入缓冲、二次写、自适应哈希索引、预读等高性能和高可用的功能。

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

高可用性、高性能、高扩展性！

1.3.2 MyISAM 存储引擎

MyISAM 存储引擎不支持事务、表锁设计，支持全文索引，主要面向一些 OLAP 数据库应用。

数据库系统与文件系统很大的一个不同之处就是在于对于事务的支持，而 MyISAM 存储引擎是不支持事务的。

1.4 各存储引擎之间的比较

InnoDB 支持事务，MyISAM 不支持，对于 InnoDB 每一条 SQL 语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条 SQL 语言放在 begin 和 commit 之间，组成一个事务
InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MyISAM 会失败
InnoDB 是聚集索引，使用 B+Tree 作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按 B+Tree 组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。MyISAM 是非聚集索引，也是使用 B+Tree 作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

   MyISAM 是非聚集索引，也是使用B+Tree  作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
   也就是说：InnoDB 的 B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而 MyISAM 的 B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针

InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何 WHERE 条件）；

那么为什么 InnoDB 没有了这个变量呢？

	因为 InnoDB 的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此 count 统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。InnoDB 会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在，InnoDB还会尝试去遍历其他聚簇索引。

	如果索引并没有完全处于 InnoDB 维护的缓冲区（Buffer Pool）中，count 操作会比较费时。可以建立一个记录总行数的表并让你的程序在 INSERT/DELETE 时更新对应的数据。和上面提到的问题一样，如果此时存在多个事务的话这种方案也不太好用。如果得到大致的行数值已经足够满足需求可以尝试 SHOW TABLE STATUS

InnoDB 不支持全文索引，而 MyISAM 支持全文索引，在涉及全文索引领域的查询效率上 MyISAM 速度更快高；PS：5.7以后的InnoDB 支持全文索引了
MyISAM 表可以被压缩后进行查询操作
InnoDB 支持表、行(默认)级锁，而 MyISAM 只支持表级锁
InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有
Innodb存储文件有 frm、ibd，而 MyISAM是frm、MYD、MYI
- Innodb：frm 是表定义文件，ibd是数据文件
- Myisam：frm 是表定义文件，myd 是数据文件，myi 是索引文件