mysql之存储引擎

mysql的体系结构

sql语句的执行过程

问到这个问题，首先介绍mysql的体系结构，介绍完之后说，sql既然可以执行，说明我们的客户端进程和服务端数据库进程已经建立了连接。

• 第一步：数据库客户端把sql语句发送给服务端数据库进程执行（连接层）

​ 比如说我们在java程序中写了一条sql语句，这条语句首先会发送给服务端的数据库进程

• 第二步：服务端解析这条sql语句（大部分是在服务层）

  • 首先会查询高速缓存。服务端接收到这条sql语句后，不会直接去数据库表查询，而是先在数据库的高速缓存（一般在内存）中去查找，如果命中了那么就可以直接执行了
  • 检查语句的合法性。如果高速缓存没有命中的话，sql服务器就会检查这条sql语句有没有语法错误，如果有的话，就直接把错误信息返回给数据库客户端。这里注意：检查的范围不包括表名，列名等信息，只是语法检测
  • 检查语义的合法性。如果语法没有问题，就检查语句中的表名、字段名等是否可以和数据库表对应的上，如果有错误，就把错误信息返回给客户端。所以当我们写一个查询语句的时候，如果语法和表名、列名都有错的话，会先提示语法错误。
  • 获得对象解析锁。当语法和这个语义都正确了，服务端就会对我们要操作的数据库表对象加锁，这个是为了保障数据的一致性。（防止我们在操作时，其他用户改变这个对象的结构）
  • 核对数据访问权限。当这些准备工作都做好之后，服务端会核对一下客户端连接的数据库用户的权限，看一下你连接的用户是否有这个数据访问的权限
  • 服务器优化sql。权限也匹配上之后，服务器会对这条sql语句进行一定的优化，优化完之后就会把这条sql语句，以及它的执行计划保存在高速缓存中。

• 第三步：执行sql语句（引擎层）

  第二步解析的作用是确保服务器可以知道客户端传来的sql语句表达的是什么意思。

  执行也分成两种情况

  • 被选中行所在的数据块已经被读到数据缓冲区了，那么服务器就会直接把数据传递给客户端，不用去数据库文件表中查询了。
  • 如果数据没有在缓冲区里，那么服务器就需要去数据库表中取出对应的数据块返回给客户端，同时把这块数据存到缓冲区。

​ 这里还有一个点，数据库服务器里有一个专门负责数据提取的一段代码，把查询到的数据结果返回给客户端进程。

Mysql中的两个引擎

存储引擎是基于表的，不是基于数据库的，所以存储引擎也被称为表的类型。创建表的时候，使用ENGINE = INNODB 来指定此表的存储引擎。show engines; 查看当前数据库支持的存储引擎

MyISAM：5.5之前的默认引擎。不支持事务，不支持外键，不支持行锁，支持表锁。每一个MyISAM表都对应磁盘上的三个文件。.sdi文件：存储表结构信息；.MYD: 存储表的数据；.MYI: 存储索引文件

InnoDB：mysql5.5之后的默认引擎，支持事务和回滚，运行时会在内存中建立缓冲池，用于缓冲数据和索引。不过它也有缺点，没有保存表的行数，所以使用count函数统计时会扫描全表。

InnoDB的逻辑存储结构

• Tablespace：表空间。也是InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个Segment段。
• Segment（段）： 表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。
• Extent（区）： 区是表空间的单元结构，每个区的大小为1M。默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页。
• page（页）：页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
• Row（行）：InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段（事务id：Trx_id和滚动指针：Roll_pointer）。

MyISAM和InnoDB对比

• 是否支持行级锁: MyISAM只有表级锁(table-level locking),而InnoDB 支持行级锁(row-level locking)和表级锁，默认为行级锁
• 是否支持外键: MyISAM不支持， 而InnoDB支持
• 是否支持MVCC:仅InnoDB支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在READ COMMITTED和REPEATABLE READ两个隔离级别下工作;MVCC可以使用乐观锁和悲观锁来实现;各数据库中MVCC实现并不统一。
• 是否支持事务和崩溃后的安全恢复: MyISAM 强调的是性能，每次查询具有原子性其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB提供事务支持，外键等高级数据库功能。具有事务(commit)、 回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant)型表。
• InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的。 MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的。也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。