赞
踩
存储引擎是指存储数据,建立索引,查询和更新数据等技术的实现方式。Oracle,Sql Server等数据库只有一种存储引擎,而MySQL提供了多种存储引擎的选择。因为MySQL存储引擎是插件式的,所以用户可以根据需要使用最优的存储引擎,甚至编写自己的存储引擎。
MySQL5.0支持的存储引擎包含:InnoDB、MyISAM、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等,其中InnoDB和BDB提供事务安全表,其他存储引擎是非事务安全表。
查看MySQL数据库默认的存储引擎:
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> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
从事务、锁等多个方面,对比一下几种常用的存储引擎,如下表所示:
特点 | InnoDB | MyISAM | MEMORY | MERGE | NDB |
---|---|---|---|---|---|
存储限制 | 64TB | 有 | 有 | 没有 | 有 |
事务安全 | 支持 | 表锁 | 行锁 | ||
锁机制 | 行锁(适合高并发) | 表锁 | 表锁 | 支持 | 支持 |
B树索引 | 支持 | 支持 | 支持 | 支持 | |
哈希索引 | 支持 | 支持 | 低 | ||
全文索引 | 支持(5.6版本之后) | 支持 | 低 | 高 | |
集群索引 | 支持 | 低 | 高 | ||
数据索引 | 支持 | 支持 | 支持 | 高 | |
索引缓存 | 支持 | 支持 | 支持 | ||
数据可压缩 | 支持 | N/A | |||
空间使用 | 高 | 低 | 中等 | ||
内存使用 | 高 | 低 | 高 | ||
批量插入速度 | 低 | 高 | |||
支持外键 | 支持 |
下面我们将重点介绍最常使用的两种存储引擎:MyISAM、InnoDB。
MyISAM是MySQL5.5以前的默认存储引擎。MyISAM不支持事务、也不支持外键,其优势是访问的速度快,对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎。MyISAM的优点还包括数据紧凑存储,因此可获得更小的索引和更快的全表扫描性能。
每个MyISAM表在磁盘上存储成3个文件,其文件名都和表名相同,但扩展名分别是:
.frm
:存储表定义;.MYD
:存储数据;.MYI
:存储索引;MyISAM把数据和索引存在不同的文件中,甚至可以把数据文件和索引文件放在不同的路径下,以获得更快速度。如果要设置索引文件和数据文件的路径,需要在创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定。文件路径需要是绝对路径,并且要有访问权限。
MyISAM表支持3种不同的存储格式,分别是:
MyISAM表可能会损坏,原因是多种多样的,损坏的表可能不能被访问,会提示需要修复或访问后返回错误的结果。MyISAM有检查和修复的工具,可以用CHECK TABLE语句来检查MyISAM表的健康,并用REPAIR TABLE语句修复一个损坏的MyISAM表。
InnoDB是MySQL5.5及以后的默认存储引擎。InnoDB存储引擎支持外键和事务,具有事务提交、回滚、崩溃恢复的能力,而且支持行级锁。但是对比MyISAM存储引擎,InnoDB写的处理效率差一些,并且会占用更多的磁盘空间以保留数据和索引。
InnoDB表的自动增长列可以手动插入,但是如果插入的值是0或者NULL,则实际插入的将是自动增长后的值。
InnoDB的默认事务隔离级别是REPEATABLE READ,并且通过间隙锁策略防止幻读,到达了最高隔离级别的效果。
InnoDB的主键索引是聚簇索引,通过主键进行查询有很高的性能;通过非主键索引查询会查出主键值,如果信息不足够再通过主键值进行查询(二次回表)。非主键索引底层也是B+树,叶子节点存放了主键值,所以如果主键很大的话其他的非主键索引都会很大,因此主键应当尽可能小。
外键是指创建一个表(子表)时,使某一列参考另一个表(父表)中的某一列,其中子表中的这一列称为外键。
在所有存储引擎中,只有InnoDB支持外键。在创建外键时,要求父表中被参考的列必须有对应的索引,子表会在外键上自动创建索引。子表中的外键通常会参考父表中的主键。
举例:
CREATE country_tab (
country_id int(11) UNSIGNED PRIMARY KEY AUTO_INCREMENT
) ENGINE=Innodb;
CREATE city_tab (
city_id int(11) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
country_id int(11) UNSIGNED NOT NULL,
CONSTRAINT `fk_city_country` FOREIGN KEY (country_id) REFERENCES country_tab(country_id)
) ENGINE=Innodb;
外键的使用条件包括:
在子表创建外键时,可以指定在删除、更新父表时,对子表进行的相应操作,包括RESTRICT、CASCADE、SET NULL和 NO ACTION:
外键的优点:可以使得两张表关联,保证数据的一致性和实现一些级联操作。
外键的缺点:表之间存在硬性的关联,删除或更新父表可能会导致额外的操作。所以,不推荐使用外键。
InnoDB存储表文件和索引文件有以下两种方式 :
.frm
文件中,数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中,可以是多个文件。.frm
文件中,但是每个表的数据和索引单独保存在.ibd
文件中。要使用多表空间的存储方式,需要设置参数innodb_file_per_tab,并且重新启动服务后才可以生效。对于新建的表,按照多空间的方式创建,已有的表仍然使用共享表空间存储。
多表空间的数据文件没有大小限制,不需要设置初始大小,也不需要设置文件的最大限制、扩展大小等参数。对于使用多表空间特性的表,可以比较方便地进行单表备份和恢复操作。
事务是一组SQL语句组成的逻辑操作单元。
事务具有4个特性(简称为ACID):
默认情况下,事务是默认提交的,可以查看autocommit变量:
mysql> show variables like 'autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
当事务是默认提交时,执行一个SELECT、INSERT、UPDATE、DELETE语句都会创建一个事务并自动提交。
如果需要在一个事务中包含多个语句,可以使用BEGIN (或START TRANSACTION)、COMMIT命令,其中BEGIN (或START TRANSACTION)命令用于开启一个事务,COMMIT用于提交一个事务。
另外,也可以通过设置autocommit变量关闭自动提交,
mysql> SET autocommit = 0;
mysql> show variables like 'autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)
注意,设置autommit 是 session 级别的,就是当前连接更改了 autocommit=0,对其他连接没有影响。
当事务不是自动提交时,执行一个SELECT、INSETRT、UPDATE、DELETE语句会开启一个事务,然后需要手动执行COMMIT命令提交事务,否则数据变动无法被其他的session观察到。
redo log又称重做日志,是Innodb存储引擎自带的日志,用于记录事务操作的变化。redo log记录的是数据修改之后的值,不管事务是否提交都会记录下来。
在数据库服务器崩溃时,比如如数据库突然断电,redo log文件就能派上用场。InnoDB存储引擎会使用redo log恢复到崩溃前的时刻,以此来保证数据的完整性。
MyISAM是MySQL5.5版本之前的默认数据库引擎。MyISAM性能比较好,支持全文索引、数据压缩、空间函数等,但不支持事务、行级锁和崩溃恢复。InnoDB是MySQL5.5及以后的默认存储引擎。InnoDB存储引擎支持外键和事务,具有事务提交、回滚、崩溃恢复的能力,而且支持行级锁。
两者的对比:
绝大部分情况,我们都应该使用InnoDB 存储引擎,因为其支持事务和崩溃恢复。但是,在某些情况下使用 MyISAM 也是合适的,比如读密集、对事务的完整性没有要求的场景。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。