mysql进阶系列:mysql的存储引擎有什么区别,该怎么选择

本文mysql实验版本 : 5.7.21

基础架构篇了解到执行器执行这个执行计划,通过调用存储引擎的API来操作数据。

mysql提供了一系列存储引擎的API,所有的存储引擎都要符合API要求,因此可以实现这种插件式的存储引擎,可以根据不同的需求选择合适的存储引擎(就像握推杠铃一样,可以按需选择不同大小的杠铃片,嗯对的)。

存储引擎是针对表的而不是库,对于同一个库不同的表可以使用不同的存储引擎

常见的存储引擎有 MyISAMInnoDBMemory

查看当前数据支持的存储引擎:

mysql进阶系列:mysql的存储引擎有什么区别,该怎么选择

 

  1. 在新建表的时候可以选择存储引擎

CREATE TABLE  user  (
  id bigint(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ‘‘
) ENGINE = MyISAM

ENGINE = MyISAM 代表这个表的存储引擎是MyISAM 。

 

  1. 查看表相关信息,例如mysql库中的user表

  • 使用show table status 查看表信息(不限版本)

      需要先切换到对应的数据库下再执行此命令

mysql> use mysql;
Database changed
mysql> show table status like user \G ;
*************************** 1. row ***************************
           Name: user
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 3
 Avg_row_length: 128
    Data_length: 384
Max_data_length: 281474976710655
   Index_length: 4096
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2018-06-11 09:51:16
    Update_time: 2018-06-11 09:53:08
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options: 
        Comment: Users and global privileges
1 row in set (0.00 sec)

 

  • 还可以使用information_schema查看表信息(mysql5.0以后的版本支持)

会查出实例中所有库中的表信息,但是可以指定TABLE_SCHEMA查询指定库的表


 

简单介绍小输出字段的含义:

Name: 表名。

Engine: 存储引擎。

Version:版本,默认10。

Row_format: 行的格式。

Rows: 表中的行数,对应MyISAM和其他一些存储引擎,该值是精确的; 而InnoDB该值是估计的。

Avg_row_length: 平均每行包含的字节数。

Data_length: 表数据的大小(字节)。

Max_data_length: 表数据的最大容量(和存储引擎有关)。

Index_length: 索引的大小(字节)。

Data_free: 对于MyISAM表,表示已经分配但是没有使用的空间。

Auto_increment: 下一个auto_increment值。

Create_time: 表的创建时间。

Update_time: 表数据最后修改时间。

Check_time: 使用check table命令或者myisamchk工具最后一次检查表的时间。

Collation: 表的默认字符集和字符列排序规则。

Checksum: 如果启用保存的是整个表的实时校验和。

Create_options: 创建表是指定的其他选项。

Comment: 包含其他额外信息

 

1. InnoDB

InnoDB是mysql5.5.x开始默认的事务型引擎,也是使用最广泛的存储引擎。被设计用来处理大量短期事务的。

InnoDB所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),表的大小只受限于操作系统文件的大小。表的结构定义存在.frm后缀文件中,数据和索引集中存放在.idb后缀文件中。因为表数据和索引是在同一个文件,InnoDB的索引是聚簇索引

InnoDB采用MVCC支持高并发,并且实现了四种标准的隔离级别(读未提交,读已提交,可重复读,可串行化),其默认级别是REPEATABLE-READ(可重复读),并且通过间隙锁(next-key locking)策略防止幻读的出现。间隙锁不仅仅锁定查询涉及的行,还会对索引中的间隙行进行锁定,以防止幻影行的插入。

InnoDB表是基于聚簇索引建立的,聚簇索引对主键的查询有很高的性能。但是InnoDB的非主键索引中必须包含主键列,所以如果主键列很大的话,非主键索引也会很大。如果一张表的索引较多,主键应该尽可能的小。关于索引,后面会详细讲解。

InnoDB的内部优化,包括磁盘预读(从磁盘读取数据时采用可预测性读取),自适应哈希(自动在内存中创建hash索引以加速读操作)以及能够加速插入操作的插入缓冲区

 

2. MyISAM

在mysql5.1及之前的版本,MyISAM是默认的存储引擎。提供了大量的特性,包括全文索引,压缩,空间函数等,但是不支持事务和行级锁,而且有一个严重的问题是奔溃后无法安全恢复。

MyISAM的数据表存储在磁盘上是3个文件,表结构定义存在.frm后缀文件中,表数据存储在.MYD后缀文件中,表索引存储在.MYI后缀文件中。表数据和表索引在不同的文件中,所以MyISAM索引是非聚簇索引。而且MyISAM可以存储表数据的总行数

MyISAM表支持数据压缩,对于表创建后并导入数据以后,不需要修改操作,可以采用MyISAM压缩表。压缩命令:myisampack,压缩表可以极大的减少磁盘空间占用,因此也可以减少磁盘I/O,提高查询性能。而且压缩表中的数据是单行压缩,所以单行读取是不需要解压整个表。

 

3. Memory

Memory存储引擎的数据是存放在内存中的,所以如果服务器重启会导致数据丢失,但是表结构还是存在的表结构是以 .frm 后缀的文件中。

Memory默认hash索引,因此查询非常快。Memory表是表级锁,因此并发写入的性能较低。不支持BLOB或TEXT类型的列,并且每行的长度都是固定的,所以即使指定了varchar列实际存储也会转换成char,会导致内存浪费。

如果mysql查询过程中需要使用临时表来保存中间结果,内部使用的临时表就是Memory表,如果中间结果太大超出Memory表的限制或者含有BLOB或TEXT字段,那么临时表会转换成MyISAM表。

 

 

上面介绍了三种,你如何选择存储引擎呢:

  • 事务 :目前只有Innodb能完美的支持事务。

  • 备份 :只有Innodb有免费的在线热备方案,mysqldump不算在线热备的方案,它需要对数据加锁。

  • 崩溃恢复:myisam表由于系统崩溃导致数据损坏的概率比Innodb高跟很多,而且恢复速度也没有innodb快。

  • 特有的特性:如需要聚簇索引,那就需要选择innodb存储引擎,有的需要使用地理空间搜索,那就选择myisam 。

 

mysql的存储引擎有很多,这里主要介绍了以上3中,其中InnoDB是现在使用最广泛也是默认的存储引擎,如果没有特殊需求使用默认的即可,也就是InnoDB。后面有文章详解InnoDB.

 

mysql进阶系列:mysql的存储引擎有什么区别,该怎么选择

 

mysql进阶系列:mysql的存储引擎有什么区别,该怎么选择

上一篇:Mogodb和hbase的区别


下一篇:分布式数据库(DorisDB、Clickhouse、TiDB)调研