亲宝软件园·资讯

展开

mysql存储引擎简介

浪潮之巅、 人气:1

mysql存储引擎简介

什么是存储引擎

   mysql存储引擎是数据库如何存储数据、怎样建立索引以及如何查询更新数据等技术的实现方法,数据通过使用不同的技术存储在文件中,使用不同的存储机制、索引方式来提供不同的功能。在mysql这种关系型数据中心来说,数据是以表的方式来存储的,因此,简单点来说,存储引擎就是存储和操作此表的类型。
mysql中有多种存储引擎,使用命令:show engines 可以查看当前版本支持的存储引擎。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | https://img.qb5200.com/download-x/dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

   从结果来看,我们可以看到只有InnoDB是支持事务的,并且是mysql 的默认存储引擎。下面分别介绍几种常见的存储引擎。

存储引擎的种类

InnoDB

InnoDB是MySQL5.5版本及以上的默认存储引擎,也是最重要,使用最广泛的存储引擎,它主要有以下几种特点:

  • 是mysql存贮引擎中唯一支持事务的,采用MVCC支持高并发,并且实现了四个标准的隔离级别,默认级别为REPEATABLE READ(可重复读)。
  • 表基于聚簇索引建立
  • 支持外键
  • 支持行锁,不过要基于索引,如果是全表扫描的话,仍然是表锁

InnoDB文件结构:
.frm与表相关的元数据信息都存放在frm文件,包括表结构的定义信息等;
.ibd文件和.ibdata文件;

   独享表空间存储方式使用.ibd文件,并且每个表一个ibd文件;共享表空间存储方式使用.ibdata文件,所有表共同使用一个ibdata文件;
   InnoDB使用B+Tree的方式存储索引,Innodb的一个表可能包含多个索引,每个索引都使用B+树来存储。而索引包括聚集索引和非聚集索引(二级索引),聚集索引使用表的主键作为索引键,包含表的所有字段。非聚集索引只包含索引键和聚集索引键(主键)的内容,不包括其他字段。每一个索引都是一棵B+树,每棵B+树由很多页面组成,而每个页面大小一般为16K。
我们通过下图看下表聚集索引和非聚集索引的差别:

聚集索引将索引和数据行保存在同一个B-Tree中,查询通过聚集索引可以直接获取数据
                                                

   非聚集索引的叶子节点中保存的不是指向行的物理指针,而是行的主键值。当通过聚集索引查找行,存储引擎需要在聚集索引中找到相应的叶子节点,获得行的主键值,然后使用主键去聚簇索引中查找数据行,这需要两次B-Tree查找。
                                                

MyISAM

MyISAM提供了大量的特性,包括全文索引、压缩、空间函数等,主要有以下特点:

  • 不支持事务,不支持行锁,支持表锁,读取时会对需要读取到的所有表加共享锁,写入时则对表加排他锁。
  • 奔溃后无法安全修复,可以手工或者自动检查和修复,但是可能导致一些数据的丢失,并且修复工作非常慢。
  • MyISAM会将表存储在两个文件中,数据文件和索引文件,分别以 .MYD.MYI为扩展名
  • 支持全文索引,即使是BOLB或者TEXT等长字段,也可以基于前500个字符创建索引。

每个MyISAM在磁盘上存储成三个文件:

.frm文件:与表相关的元数据信息都存放在frm文件,包括表结构的定义信息等。
.myd文件:用于存储myisam表的数据
.myi文件:,用于存储myisam表的索引相关信息

   MyISAM索引文件.MYI (MYIndex)和数据文件.MYD (MYData)是分离的,索引文件仅保存记录所在页的指针(物理位置),通过这些地址来读取页,进而读取被索引的行。
主键索引跟非主键索引的结构是一样:

                                                

   树中叶子保存的是对应行的物理位置。通过该值,存储引擎能顺利地进行回表查询,得到一行完整记录。同时,每个叶子页也保存了指向下一个叶子页的指针。从而方便叶子节点的范围遍历。

  • MyISAM引擎适用的生产业务场景:
  • 不需要事务支持的业务
  • 一般为读数据比较多的应用,读写都频繁场景不适合
  • 读写并发访问都相对较低的业务
  • 对数据一致性要求不是很高的业务。
  • 中小型的网站部分业务会用。
CSV引擎

   CSV存储引擎可以将csv文件作为mysql的表进行处理。存储格式就是普通的csv文件,数据以文本方式存储在文件中,所有的列必须都是不能为NULL的,不支持索引,可以直接对文件进行编辑。
在mysql中新建一张表,以CSV为存储引擎,插入一条数据:

mysql> insert into csvtest (id,name) values (1,"mike");

使用命令查看文件存储位置:

mysql> show global variables like "%datadir%";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)

打开存储文件,查看csv文件

[root@localhost shoes]# cat csvtest.CSV
1,"mike"

csv文件可以直接修改,我们向表中插入几条记录

[root@localhost shoes]# vi csvtest.CSV
1,"mike"
2,"jack"
3,"Daniel"

刷新表,然后查看数据

mysql> flush tables;
Query OK, 0 rows affected (0.51 sec)

mysql> select * from csvtest;
+----+--------+
| id | name   |
+----+--------+
|  1 | mike   |
|  2 | jack   |
|  3 | Daniel |
+----+--------+
3 rows in set (0.00 sec)

   根据CSV的特性,可以在数据库运行时复制或者到导出文件,可以将Excel等电子表格中的数据存储为CSV文件,然后复制到MySQL的目录下面,就能在MySQL中打开使用。外部程序也可以从表的数据文件中读取CSV格式的数据,因此CSV引擎可以作为数据交换的一种机制。

Memory

   Memory存储引擎使用存在于内存中的内容来创建表,Memory表至少比MyISAM表要快一个数量级,因为所有的数据都保存在内存中,不需要进行磁盘I/O,Meomory表的结构在重启后还会保留,但是数据会丢失。Memory表支持Hash索引,是表级锁,因此并发写入的时候性能较低,并且不支持BLOB和TEXT类型的列,每行长度固定,即使指定了varchar类型,但是实际存储的时候仍然会转换成char。
Memory适用于下几种场景:

  • 用于查找或者映射表
  • 用于缓存周期性聚合的数据,内容变化不频繁的数据
  • 用于保存数据分析中产生的中间数据

InnoDB和MyISAM的比较

  1. InnoDB支持事务,MyISAM不支持事务
  2. InnoDB支持行锁,表锁,MyISAM只支持表锁,因此MyISAM不会发生死锁,但是InnoDB会。
  3. 接上面一点,由于锁级别的问题,MyISAM并发效率比InnoDB低
  4. InnoDB支持外键,而MyISAM不支持
  5. 索引结构有区别,InnoDB有聚簇索引和非聚簇索引,聚簇索引的叶子节点保存了数据,MyISAM是非聚簇索 引,索引和数据文件是分离的,索引保存的是数据文件的指针。
  6. Innodb存储文件有frm、ibd,而Myisam是frm、MYD、MYI。
  7. 还有一个区别是查询总数,InnoDB不保存表的具体行数,select count(*)的时候需要全表扫描,而 MyISAM用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快

总结:选择合适的引擎

   MySQL有多种引擎,怎么基于业务规则与数据特点去选择呢?大部分情况下,InnoDB都是比较合适的选择,否则也不会是默认的存储引擎了。因此,除非是需要用到InnoDB不具备的特性并且没有其他办法替代,否则都应该优先选择InnoDB,像需要支持事务,在线热备份、奔溃恢复等使用InnoDB都是非常正确的选择。

加载全部内容

相关教程
猜你喜欢
用户评论