myisam、innodb存储引擎比较

时间:2023-03-08 17:47:05

MYSQL表类型(存储引擎)



1.概述
MySQL数据库其中一个特性是它的存储引擎是插件式的。用户可以根据应用需要选择存储引擎。Mysql默认支持多种存储引擎,以适用各种不同的应用需要。
默认情况下,创建表不指定表的存储引擎,则新表是默认存储引擎的。可以通过show engines来查看MySQL支持的存储引擎和默认的存储引擎。

如果想改变默认的存储引擎,可以修改my.ini文件中的default-storage-engine。在创建新表的时候,可以通过增加ENGINE关键字设置新表的存储引擎。
如:
Create table ai{
id bigint(20) not null auto_increment,
primary key(id)
} ENGINE=MyISAM default charset=utf-8;
Create table bi{
id bigint(20) not null auto_increment,
primary key(id)
} ENGINE=InnoDB default charset=utf-8;


2.MyISAM
MyISAM不支持事务,不支持外键,其优势是访问速度快,对事务完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表。
每个MyISAM在磁盘上存储成3个文件,其文件名都和表名相同,扩展名分别是:
.frm(存储表定义);
MYD(MYData, 存储数据);
MYI(MYIndex, 存储索引)。
数据文件和索引文件可以放置在不同的目录,平均分布IO,获取更快的速度。
要指定索引文件和数据文件的路径,需要在创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定,文件路径需要绝对路径,并且具有访问权限。

3.InnoDB

1) InnoDB概述
存储引擎提供了具有提交,回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎,InnoDB写的处理效率要差一些,并且会占用更多的磁盘空间以保留数据和索引。InnoDB提供了行锁(locking on row level),提供与 Oracle 类型一致的不加锁读取(non-locking read inSELECTs)。这些特性均提高了多用户并发操作的性能表现。在InnoDB表中不需要扩大锁定(lock escalation),因为InnoDB的列锁定(row level locks)适宜非常小的空间。InnoDB 是 MySQL上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。InnoDB 的设计目标是处理大容量数据库系统,它的 CPU 利用率是其它基于磁盘的关系数据库引擎所不能比的。在技术上,InnoDB是一套放在 MySQL后台的完整数据库系统,InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。 InnoDB把数据和索引存放在表空间里,可能包含多个文件,这与其它的不一样,举例来说,在MyISAM中,表被存放在单独的文件中。InnoDB表的大小只受限于操作系统的文件大小,一般为 2 GB。表上处理着平均每秒 800 次的插入/更新的负载。

2) 外键约束:
MySQL支持外键的存储引擎只有InnoDB,在创建外键的时候,要求父表必须有对应的索引,子表在创建外键的时候也会自动创建对应的索引。
存储方式:
InnoDB存储表和索引有以下两种方式。
使用共享表空间存储,这种方式创建的表的表结构保存在.frm文件中,数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中,可以是多个文件。
使用多表空间存储,这种方式创建的表的表结构仍然保存在.frm文件中,但是每个表的数据和索引单独保存在.ibd中。如果是个分区表,则每个分区对应单独的.ibd文件,文件名是“表名+分区名”,可以在创建分区的时候指定每个分区的数据文件的位置,以此来将表的IO均匀分布在多个磁盘上。
要使用多表空间的存储方式,需要设置参数innodb_file_per_table,并重启服务。

4. InnoDB最佳实践
1). 主键应该尽可能的短。
长主键浪费空间,主键尽量要保证唯一。
2). 插入和更新应该使用主键顺序
3). 增加log file size
当InnoDB把日志文件写满了,会增加不必要的磁盘写操作。
4). 避免大事务操作
事务太大会增加buffer pool, cpu的负累,测试发现,500次操作提交一次事务是最快速的,但是实际应用中,需要考虑到业务问题。
5). 避免大量插入
会在InnoDB表中影响关键码压缩

5.InnoDB行锁
1) 概述
InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与ORACLE不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!在实际应用中,要特别注意InnoDB行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能。
例子一,where条件中不是索引,出现了表锁:
create table tab_no_index(id int,name varchar(10)) engine=innodb;
insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');
Session 1
Session 2
set autocommit=0;

select * from tab_no_index where id = 1 for update;


select * from tab_no_index where id = 2 for update;

等待
看起来session_1只给一行加了排他锁,但session_2在请求其他行的排他锁时,却出现了锁等待!原因就是在没有索引的情况下,InnoDB只能使用表锁。
当我们给其增加一个索引后,InnoDB就只锁定了符合条件的行,
create table tab_with_index(id int,name varchar(10)) engine=innodb;
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');
alter table tab_with_index add index id(id);
Session 1
Session 2
set autocommit=0;
set autocommit=0;
select * from tab_with_index where id = 1 for update;


select * from tab_with_index where id = 2 for update;

结果出来

1. 不通过索引来检索数据时,innodb使用表锁而不是行锁;
table_locks_waited计数不会增加,innodb_row_lock_waits,table_locks_immediate加1。可以通过show status like ‘%lock%’来查看。或者用show innodb status查看。
2. 由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的。
3. 当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。
4. 即便在条件中使用了索引字段,但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的,如果 MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB使用表锁,而不是行锁。
另外,在UPDATE、DELETE操作时,MySQL不仅锁定WHERE条件扫描过的所有索引记录,而且会锁定相邻的键值,即所谓的next-key locking。
获取InnoDB行锁争用情况
show status like 'innodb_row_lock%';

如果发现锁争用比较严重,如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高,还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等,并分析锁争用的原因。具体方法如下:

mysql> CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
Query OK, 0 rows affected (0.14 sec)

然后就可以用下面的语句来进行查看:
mysql> Show innodb status\G;

InnoDB 默认的事务隔离级是REPEATABLE READ。 SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, 和 DELETE ,这些以唯一条件搜索唯一索引的,只锁定所找到的索引记录,而不锁定该索引之前的间隙。 否则这些操作将使用 next-key 锁定,以 next-key 和 gap locks 锁定找到的索引范围,并阻塞其它用户的新建插入。
1) 如何避免死锁
死锁是事务处理型数据库系统的一个经典问题,但是它们并不是很危险的, 除非它们如此地频繁以至于你根本处理不了几个事务。 当因死锁而产生了回滚时,你通常可以在你的应用程序中重新发出一个事务即可。
InnoDB 使用自动地行级锁定。你可能恰好在插入或删除单一一条记录时产生死锁。 这是因为这些操作并不是真正“原子(atomic)”级的:他们会自动地在锁定 inserted/deleted 行的索引记录(可能有几个)。

可以通过下面所示的技巧来应付死锁或减少死锁的次数:
1) 使用 SHOW INNODB STATUS 来确定引起最后一个死锁的原因。这可以帮助你调整你的应用程序来避免死锁。
2) 总是准备在因死锁而发生错误时重新发出一个事务。死锁并不危险。仅仅只需重试一遍。
3) 经常提交你的事务。小的事务有较少的碰撞可能。
4) 如果使用锁定读取 SELECT ... FOR UPDATE 或 ... LOCK IN SHARE MODE,尽量使用较低的隔离级 READ COMMITTED。
5) 以一个固定秩序(a fixed order)访问你的表和记录。这样事务将形成一个较精细的队列,而避免死锁。
6) 为你的表添加合适的索引。那么你的查询只需要扫描较少的索引,因而设置较少的锁定。使用 EXPLAIN SELECT 来确定 MySQL 为你的查询挑选的适当的索引。
7) 尽量少用锁定:如果可以通过一个 SELECT 在一个较老的数据快照中获得所需数据,就不要再添加子句 FOR UPDATE 或 LOCK IN SHARE MODE 。在这时使用 READ COMMITTED 隔离级是较好的主意,因为在同一个事务中的每个 consistent read 只读取它最先确定的数据快照。

6. MyISAM or InnoDB?
InnoDB和MyISAM是在使用MySQL最常用的两个表类型,各有优缺点,视具体应用而定。基本的差别为:MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持。MyISAM类型的表强调的是性能,其执行速度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务支持以及外部键等高级数据库功能。

MyISAM:如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择MyISAM是非常适合的。MyISAM在数据仓库下是最常用使用的存储引擎之一。
InnoDB:用于事务处理应用程序,支持外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询以外,还包括很多的更新、删除操作,那么InnoDB存储引擎应该是比较合适的选择。InnoDB存储引擎除了有效地降低由于删除和更新导致的锁定,还可以确保事务的完整性提交和回滚。
两种类型最主要的差别就是InnoDB 支持事务处理与外键和行级锁。而MyISAM不支持。
MyISAM的索引和数据是分开的,并且索引是有压缩的,内存使用率就对应提高了不少。能加载更多索引,而InnoDB是索引和数据是紧密捆绑的,没有使用压缩从而会造成InnoDB比MyISAM体积庞大不小。
InnoDB不仅仅只是行锁,select count(*) 和order by这种操作Innodb其实也是会锁表的, Innodb虽然是行级锁,但是那个只是where对它主键是有效,非主键的都会锁全表的。
InnoDB 中不保存表的具体行数,也就是说,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行数即可。注意的是,当count(*)语句包含 where条件时,两种表的操作是一样的。
InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表。
对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引。
DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。