mysql 系统性浅聊 myisam 存储引擎【原创】

时间:2023-12-11 18:07:56
》》思维导图

mysql 系统性浅聊 myisam 存储引擎【原创】

》》介绍

  mysql中的存储引擎都是以插件的形式存在,目前用的最多存储引擎就是innodb和myisam。MySQL5.5.5以后(包括5.5.5)默认使用InnoDB存储引擎,之前的版本基本就是MyISAM的了。现在有很多人都在网上问,innodb作为存储引擎之后,myisam引擎是不是要被淘汰了?其实它们两者之间各有优势,暂时还不能说谁替代谁,不存在滴。

》》存储结构

  创建一个myisam数据表

mysql> create table myisam(
-> id int not null primary key auto_increment,
-> cs varchar(50) not null comment 'test'
-> )engine=myisam default charset=utf8;
Query OK, 0 rows affected (0.10 sec)

  查看表结构(具体的查看和操作,请看上篇MySQL查看和修改表的存储引擎

mysql> show create table myisam;
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------+
| Table | Create Table |
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------+
| myisam | CREATE TABLE `myisam` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cs` varchar(50) NOT NULL COMMENT 'test',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------+
1 row in set (0.00 sec)

  再具体看下底层的myisam数据表的文件组成:

windows下的表结构:

mysql 系统性浅聊 myisam 存储引擎【原创】

linux下的表结构是一样的,我就不贴出来了。

看到myisam表是由.frm, .MYD, .MYI三个文件组成:

.frm 是表结构文件

.MYD 是数据文件

.MYI 是索引文件

》》特性--不支持事务

  例子如下:

--》上面已经创建了一个myisam表,表结构如下:

mysql> desc myisam;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| cs | varchar(50) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

--》操作如下:

mysql 系统性浅聊 myisam 存储引擎【原创】

--》好吧!没有对比就没有伤害!下面试下Innodb是否成功:

mysql 系统性浅聊 myisam 存储引擎【原创】

》》特性--修复表

  主要两个SQL操作语句:

  check table YOUR_TABLE_NAME

  repair table YOUR_TABLE_NAME

实例如下:

mysql 系统性浅聊 myisam 存储引擎【原创】

注意:这里的修复表,并不是说可以将之前删除的数据找回来,切记切记!

》》特性--加锁和并发
   加锁:对整张表进行加锁,而不是行。
           并发:在读数据的时候,所有的表上都可以获得共享锁(读锁),每个连接都不互相干扰。
                在写数据的时候,获得排他锁,会把整个表进行加锁,而其他的连接请求(读,写请求)都处于等待中。
  注意:这里就是为什么MyISAM表不适合做频繁的读写操作的数据表的存储引擎!
》》特性--列索引以及延迟索引

  列索引,其实在这里我并不想写太多,后面我会专门写一篇关于如何建立索引,注意事项以及如何优化索引。

  这里我主要想讲的是延迟索引这个参数,挺有意思的,myisam专有物

  延迟更新索引,MYISAM 默认把DELAY_KEY_WRITE开启, 整个选项是MYISAM引擎独有的。

  mysql 系统性浅聊 myisam 存储引擎【原创】

很多人都知道在建立索引上的表进行写操作时,插入大量数据的速度会非常慢,很多办法是先关闭索引,然后插入数据,再打开索引。其实将delay_key_write选项打开,可以得到一定程度的插入速度优化。如果你的某个表有很多update操作,这个参数的优势会很好的体现出来。因为这个参数能延迟更新索引到表关闭。再提醒一次:

  注意:DELAY_KEY_WRITE选项是MYISAM存储引擎的专有的

》》特性--表压缩

主要使用myisampack命令

mysql 系统性浅聊 myisam 存储引擎【原创】

由于这张表是空表,所以在压缩的时候加了-f,表示强制压缩。下面看下压缩后的文件大小:

mysql 系统性浅聊 myisam 存储引擎【原创】

大家有没有发现多了一个OLD文件,这是对原来MYI文件的一个备份,之前是0kb,然后再看下新的MYI文件,目前是1.0K,压缩后的文件没有变小反而变大了,为什么呢?

这是由于原文件是个空文件,强制压缩之后肯定会比原文件大的,多了一些乱七八糟的东西在里面啦。

注意:压缩后的文件再执行写操作时会报错,这是由于压缩后的文件会变成只读状态!!切记啊。

》》基础限制

  在版本 < MySQL 5.0 时默认表的大小为4G,这对于一些大业务的数据来说是不够的。

  有什么办法可以扩展吗?有的。

  如存储大表则要修改MAX_Rows 和 AVG_ROW_LENGTH这两个选项。但是一定配置了这两个参数后,表会立刻重写。

  所以最好的建议就是更新MySQL的版本啦。

  在版本 > MySQL 5.0 时默认表的大小为256TB,这对于我们绝大部分的业务来说都是足够的了。

》》应用场景

  最后来说下myisam存储引擎适合使用在哪些应用场景下:

  1.看了上面的特性,其实我们很容易知道,需要用到事务型的应用都不适合,只能使用在非事务型应用

  2.对于锁表而不是锁行的行为,同样的myisam不适合用在频繁操作写的应用下,这会造成阻塞,最好用在只读类应用下。

    3.这个就比较特殊了,上面也没提到过,空间类应用,在MySQL 5.7只前,myisam是唯一支持空间函数的存储引擎,如果想存储空间数据例如GPS数据,或者使用空间函数运算的话,只能使用MyISAM存储引擎。

欢迎留言。O(∩_∩)O哈哈~