MYSQL表类型（存储引擎）

 

 

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不会重新建立表，而是一行一行的删除。