一、存储引擎的概念

用来处理数据库的相关CRUD操作

查看MySQL支持的引擎。

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

官方存储引擎

  • MyISAM
  • InnoDB – 推荐；其他引擎已经体停止维护和开发
  • Memory
  • Federated
  • CSV

  • Archive

    第三方存储引擎

  • TokuDB – 开源，适合插入密集型
  • InfoBright – 商业，开源版本有数据量限制。属于列存储，面向OLAP场景
  • Spider

查看默认的存储引擎

mysql> SHOW VARIABLES LIKE '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)

二、MySQL的存储引擎

1.innodb存储引擎

  • InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。InnoDB主要特性有：

    • 1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合

    • 2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的

    • 3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上

    • 4、InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键

    • 5、InnoDB被用在众多需要高性能的大型数据库站点上

    • 6 InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件

2.myisam存储引擎

• MySQL5.1版本之前的默认存储引擎 • 堆表数据结构 • 表锁设计 • 支持数据静态压缩 • 不支持事物 • 数据容易丢失 • 索引容易损坏 • 唯一优点 ◦ 数据文件可以直接拷贝到另一台服务器使用 现在MySQL中还有用MyISAM的表，主要是历史原因。数据库文件以MY 开头的基本都是MyISAM的表

  1. MyISAM还在使用的原因 • 历史原因，需要逐步替换 • 部分如User，DB等系统表(MyISAM引擎)，可以直接拷贝，比较方便 • 性能好，或者存储小不是MyISAM的优点，也不是存在的原因
  2. MyISAM文件组成 • frm 表结构文件 • MYI 索引文件 • MYD 数据文件 ◦ 数据文件是堆表数据结构，堆是无序数据的集合 ◦ MYI 中的叶子节点，指向MYD 中的数据页 ◦ 当数据移动到页外时，需要修改对应指针
  3. myisamchk myisamchk 通过扫描MYD文件来重建MYI文件；如果MYD文件中某条记录有问题，将跳过该记录

3.memory存储引擎

  1. Memory介绍

  2. 全内存存储的引擎 数据库重启后数据丢失 支持哈希索引 不支持事物

  3. Memory特性

  4. 千万不要用Memory存储引擎去做缓存(Cache) , 性能上不及Redis和Memcahced
  5. Memory 不能禁用，当涉及内部排序操作的临时表时，使用该存储引擎
  6. max_heap_table_size 决定使用内存的大小，默认时16M
  7. 无论该表使用的什么引擎，只要使用到临时表，或者指定Memory，都受参数影响
  8. 当上面设置的内存放不下数据时，(>=5.6)转为MyISAM,(>=5.7)转为InnoDB
  9. 注意磁盘上临时路径空间的大小( tmpdir )
  10. 内存使用为会话(SESSION)级别，当心内核OOM
  11. 支持哈希索引，且仅支持等值查询
mysql> show global status like "%tmp%tables";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 37    |  --内存放不下，转成磁盘存储的数量，如果过大，考虑增大内存参数
| Created_tmp_tables      | 307   |  --创建临时表的数量
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql> show variables like 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |  --memory转成磁盘存储的路径
+---------------+-------+
1 row in set (0.00 sec)

4.csv存储引擎

  1. CSV介绍 • CSV - Comma-Separated Values，使用逗号分隔 • 不支持特殊字符 • CSV是一种标准文件格式 • 文件以纯文本形式存储表格数据 • 使用广泛
  2. CSV文件组成 • frm 表结构 • CSV 数据文件 • CSM 元数据信息
  3. CSV特性 • MySQL CSV存储引擎运行时，即创建CSV 文件 • 通过MySQL标准接口来查看和修改CSV文件 • 无需将CSV文件导入到数据库，只需创建相同字段的表结构，拷贝CSV文件即可 • CSV存储引擎表每个字段必须是NOT NULL 属性

5.federated存储引擎

  1. Federated介绍 • 允许本地访问远程MySQL数据库中表的数据 • 本地不存储任何数据文件 • 类似Oracle中的DBLink • Federated存储引擎默认不开启, 需要在my.cnf 的[mysqld] 标签下添加federated • MySQL的Federated不支持异构数据库访问，MariaDB中的FederatedX 支持
  2. Federated 语法 scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name CONNECTION='mysql://username:password@hostname:port/database/tablename'

示例：

CREATE TABLE `T1` (
`A` VARCHAR(100),
UNIQUE KEY (`A` (30))
) ENGINE=FEDERATED
CONNECTION='MYSQL://nazeebo:123456@127.0.0.1:3306/TEST/T1';

三、存储引擎的选择

不同的存储引擎都有各自的特点，以适应不同的需求，如下表所示：

1.如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择 2.如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率 3.如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果 4.如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive 5.使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能

以当前的发展来看，选默认的innodb存储引擎是99.9999%没错的！^_^

四、innodb与myisam的对比

Copyright © suredandan 2018 all right reserved，powered by GitbookUpdateTime: 2020-04-09 16:35:45

results matching ""

    No results matching ""