愿你坚持不懈,努力进步,进阶成自己理想的人

—— 2017.09, 写给3年后的自己

MySQL存储引擎

MySQL内置多种存储引擎,每种引擎各有各的特点,为了更好地对这些存储引擎进行选择,做个总结

可以通过mysql客户端查看支持的引擎:show engines;,得到:

我们也可以从图中看出,MySQL支持有9种存储引擎,分别是FEDERATEDMRG_MYISAMMyISAMBLACKHOLECSVMEMORYARCHIVEInnoDBPERFORMANCE_SCHEMA

MyISAM

MyISAM是此前Mysql默认的存储引擎。它的特点如下:

  • 支持B-TreeFullTextR-Tree索引类型
  • 锁级别是表锁,表锁的开销小,加锁快。但是锁的粒度大,发生锁冲突的概率较高,因此并发度就较低
  • 不支持事务,也不支持外键
  • MyISAM表是独立于操作系统的,创建一个MyISAM引擎的表时,本地磁盘上会建立有三个文件:.frm.MYD.MYI,它们的作用分别是:存储表定义存储表数据存储表索引

适用场景:

  • 适用于读多于写的情况(读写比大于100,且较少的Update操作)
  • 低并发场景,不需要使用到事务
  • 表数据量不大的情况
  • 需要使用到全文搜索


InnoDB

InnoDB是MySQL中目前唯一一个支持事务的引擎。特点如下:

  • 支持Hash/B-Tree索引类型
  • 锁是行锁,并发性能好。但是额外消耗更多,需要更多的内存。支持事务和回滚,适合处理多重并发的更新请求
  • 支持外键约束,InnoDB也是目前唯一支持外键的MySQL引擎
  • 支持自动灾难恢复


Memory

我们可以从show engines;的结果中看出,Memory引擎的特点是Hash based, stored in memory, useful for temporary tables,即它是一个基于散列,存储在内存中,适用于临时存储的表的引擎。因为内存的访问速度要大大快于外存,所以选用这种存储方式的出发点就是为了速度。特点为:

  • 访问很快,但是若mysqld守护进程奔溃,那么所有的Memory数据就都会丢失
  • 存储在表里的数据要求使用定长的格式,这意味着BLOB/TEXT的数据类型不可用,但是可以用VARCHAR(以为MYSQL内部将它当做CHAR处理)
  • 支持Hash/B-Tree索引类型,可以在创建表的时候指定所需的索引类型(using hash (username)using btree (username)。Hash类型对于相等比较较快,但是范围比较要慢得多,比较适合=<>操作符的场景。而B-Tree则适用于部分查询和统配查询


MRG-MyISAM

这种表存储引擎适用于分表的场景。它是一组MyISAM表的组合,这些MySQL表结构必须完全相同。如:

CREATE TABLE `article_1` (
 `aid` BIGINT(20) NOT NULL,
 `subject` VARCHAR(200) NOT NULL,
 `content` TEXT NOT NULL,
 PRIMARY KEY (`aid`)
) Engine=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `article_2` (
 `aid` BIGINT(20) NOT NULL,
 `subject` VARCHAR(200) NOT NULL,
 `content` TEXT NOT NULL,
 PRIMARY KEY (`aid`)
) Engine=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `article_3` (
 `aid` BIGINT(20) NOT NULL,
 `subject` VARCHAR(200) NOT NULL,
 `content` TEXT NOT NULL,
 PRIMARY KEY (`aid`)
) Engine=MyISAM DEFAULT CHARSET=utf8;

这种情况下,就可以使用MRG-MyISAM,使得 当需要查询的时候,不需要使用article_分表号的形式来查询,而只需要查询使用MRG-MyISAM类型的表就可以了,需要注意的是,MRG表的结果也应该和分表结构一致。如:

CREATE TABLE `articles` (
 `aid` BIGINT(20) NOT NULL,
 `subject` VARCHAR(200) NOT NULL,
 `content` TEXT NOT NULL,
 PRIMARY KEY (`aid`)
) Engine=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=NO UNION=(
    `article_1`,
    `article_2`,
    `article_3`
)

此时,我们需要查询的时候,只需要使用SELECT * FROM articles这种形式就可以了,这样子就可以很方便地进行查询,避免出错。但是如果需要进行增/删/插操作的话,则需要这么处理:

  • 创建一个中间表articles_id
CREATE TABLE `articles_id` (
 `aid` BIGINT(20) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`aid`)
) Engine=MyISAM
  • 插入使用getAid()获得id,然后使用getTableId()获得分表号
function getAid() {
    $sql = "INSERT INTO articles_id (aid) VALUES('')";
    $this->db->query($sql);

    return $this->db->insert_id();
}

function getTableId($id) {
    return 'article_' . ( intval($id)%3 );
}