【后端面经-数据库】MySQL的存储引擎简介

三思而折腾

目录

• MySQL的存储引擎
  
  
  
  • 0. 存储引擎的查看和修改
    
  • 1. MyISAM
    
  • 2. InnoDB
    
  • 3. MEMORY
    
  • 4. MERGE
    
  • 5. 总结
    
  • 6. 参考博客
    
  
  

MySQL的存储引擎

mysql主要有四类存储引擎，目前主要使用InnoDB作为存储引擎。
0. 存储引擎的查看和修改

• 查看当前数据库的默认存储引擎
  1. show variables like 'default_storage_engine';

  复制代码
• 查看当前数据库所支持的存储引擎
  1. show engine;//语句1
  2. show variables like 'have_%';//语句2

  复制代码
• 查看支持事务处理的存储引擎
  1. select engine from information_schema.engines where transactions='yes';

  复制代码
• 设置新表的存储引擎
  1. create table 表名 (字段名 字段类型) engine=存储引擎;//语句1
  2. create table 表名 (字段名 字段类型) type=存储引擎;//语句2

  复制代码

1. MyISAM

• 文件组成
  
  
  
  • .frm文件：表结构定义，frame，可以理解成对整体框架的存储
    
  • .MYD文件：数据文件，存储的是具体的数据库数据条目
    
  • .MYI文件：索引文件，存储的是数据库表项的索引文件
    如果用图书管理系统做比喻的话，那么，.frm文件存放的是书架本身，.MYD文件存储具体的书籍，.MYI文件存储检索书籍的索引目录。
    .MYI文件和.MYD文件可以存储在不同的文件目录中，从而分散IO读写压力，提高访问速度，具体操作可在创建表的时候，通过DATA DIRECTORY和INDEX DIRECTORY属性进行设置。
    
  
  
• 适用范围
  由于MyISAM不支持事务，不支持外键，访问速度快的特点，适用于以下特点的数据库：
  - 不要求事务完整性
  - 操作主要是查找SELECT和INSERT
  
• 安全性
  MyISAM表中有一个标志，用于存储上次退出表是否是正常退出，每次启动该表之前会检查该标志，如果上次是异常退出，则考虑进行检查和修复。
  可使用CHECK TABLE和REPAIR TABLE命令进行表的检查和修改。
  
• 支持的存储格式
  
  
  
  • 静态表：每个条目长度固定
    
  • 动态表：每个条目长度不固定
    
  • 压缩表：压缩存储，节省空间
    
  
  

三者的特点如下所示：

• 优缺点
  
  
  • 优点：访问速度快，
    
  • 缺点：不支持事务，不支持外键，不支持行级锁，不支持崩溃后的安全恢复，不支持并发插入（性能方面）
    
  
  

2. InnoDB

• 自动增长列
  指的是InnoDB支持用户手动插入的条目遵循索引项的自动增长，而不需要用户自己设置。
  然而，在InnoDB中，自动增长列必须是索引项，如果是组合索引，则是其中的第一列；而对于MyISAM，则可以是任意数据项。
  创建一个表之后，其自动增长列的起始值默认是1，也可以在创建表的时候进行修改：
  1. CREATE TABLE t1 (
  2.     id INT NOT NULL AUTO_INCREMENT,
  3.     name CHAR(30) NOT NULL,
  4.     PRIMARY KEY (id)
  5. ) ENGINE=InnoDB AUTO_INCREMENT=某个起始数字;

  复制代码
  在插入条目的时候，也可以通过ALTER TABLE命令进行修改：
  1. ALTER TABLE 具体条目 AUTO_INCREMENT=某个起始数字;

  复制代码
• 外键约束
  
  
  
  • 在本文介绍的四个存储引擎中，只有InnoDB支持外键约束。
    
  • 外键约束可以指定数据表中，对父表的操作如何影响子表，具体参数如下：
    
    
    
    • restrict：父表的删除和更新不会对子表产生影响
      
    • cascade：级联操作，父表的删除和更新操作会影响子表，
      
    • set null：父表的删除和更新将会使得子表中相关条目设置为null值
      
    • no action：效果等同于restrict，父表的删除和更新操作不会对子表产生影响。
      
    
    
  • 外键约束的开关
    根据set forgein_key_checks=0或者1的值，可以开启（值为1）或者关闭（值为0）外键约束。
    
  
  
• 优缺点
  
  
  
  • 优点：支持回滚等事务处理
    
  • 缺点：访问效率低，花费内存存储索引结构，占用内存较大
    
  
  

3. MEMORY

<ol>使用内存内容
MEMORY表使用内存中的数据进行存储管理，因此，在使用期间需要足够的内存空间，当使用结束之后，需要进行内存释放，命令如下

1. DROP FROM MEMORY 表名;
2. TRUNCATE table 表名;

复制代码

hash结构
MEMORY表的索引结构为hash，因此，有很快的访问速度，但是也引申出对于数据库操作指令的响应性能问题：<ul>相等比较：=，，性能较好
范围比较：>，