2 MySQL存储引擎

2 MySQL存储引擎

• SQL命令
• MyISAM
• Innodb
  • 物理结构
  • 底层存储结构

SQL命令

• 查看所有存储引擎：show engines;
• MySQL默认存储引擎是InnoDB，是事务性存储引擎
• 查看默认存储引擎：show variables like ‘%storage_engine%’;
• 查看表的存储引擎：show table status like ‘tableName’

MyISAM

• 存储结构：每一个表都被存放为三个以表名命名的物理文件

  • 存放表结构定义信息的.frm 文件
  • 存放了表数据的.MYD文件
  • 存放所有索引数据的.MYI文件

• 支持的三种类型索引
  1、B-Tree 索引
  B-Tree 索引，顾名思义，就是所有的索引节点都按照 balance tree 的数据结构来存储，所有的索引数据节点都在叶节点
  MyISAM 的 B-Tree 索引有一个较大的限制，那就是参与一个索引的所有字段的长度之和不能超过 1000 字节

  2、R-Tree 索引
  R-Tree 索引的存储方式和 b-tree 索引有一些区别，主要设计用于为存储空间和多维数据的字段做索引，所以目前的 MySQL 版本来说，也仅支持 geometry 类型的字段作索引

  3、Full-text 索引
  Full-text 索引就是我们长说的全文索引，他的存储结构也是 b-tree。主要是为了解决在我们需要用 like 查询的低效问题

• MYD文件的存放格式

  • 静态（FIXED ） 固定长度、动态（DYNAMIC）可变长度以及压缩（COMPRESSED）
  • 创建表的时候指定是否压缩：row_format [compressed|default]
  • 默认是不压缩的,是静态还是动态，就和表中个字段的定义相关了。只要表中有可变长度类型的字段存在，那么该表就肯定是 DYNAMIC 格式的，如果没有任何可变长度的字段，则为 FIXED 格式

• 不支持事务和行级锁，但性能极佳，提供大量特性，包括全文索引、压缩、空间函数等

• 最大缺陷：奔溃后无法安全恢复

• 适用于读密集

• 与InnoDB区别

  • 是否支持行级锁：MyISAM只有表级锁，InnoDB支持行级锁（默认）和表级锁
  • 是否支持事务和崩溃后安全恢复
    • MyISAM强调性能，每次查询具有原子性，不支持事务
    • InnoDB支持事务，外部键等高级数据库功能，具有崩溃恢复能力(crash recovery capabilities)和事务安全型表(transaction-safe(ACID compliant))
  • 是否支持外键：MyISAM不支持外键，InnoDB支持
  • 是否支持MVCC：仅InnoDB支持
    • MVCC只在READ-COMMITTED和REPEATABLE READ两个隔离级别下工作
    • MVCC可以使用乐观锁和悲观锁来实现

• MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成.另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的.不支持事务处理

Innodb

物理结构

• 数据文件（表数据和索引数据）
  • 存放数据表中的数据和所有的索引数据，包括主键和其他普通索引
  • Innodb 的表空间分为两种形式：一种是共享表空间，也就是所有表和索引数据被存放在同一个表空间（一个或多个数据文件）中，通过 innodb_data_file_path来指定，增加数据文件需要停机重启。 另外一种是独享表空间，也就是每个表的数据和索引被存放在一个单独的.ibd 文件中
  • 共享表空间都是必须存在的，因为 Innodb 的 undo 信息和其他一些元数据信息都是存放在共享表空间里面的
  • 共享表空间的数据文件是可以设置为固定大小和可自动扩展大小两种形式的，自动扩展形式的文件可以设置文件的最大大小和每次扩展量

底层存储结构

• 底层存储结构为B+树，每个节点对应一个page，大小是固定的，一般为16k，非页子节点只有键值，叶子节点包含数据