`

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_waitstable_locks_immediate1。可以通过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不会重新建立表,而是一行一行的删除。

分享到:
评论

相关推荐

    Mysql 表类型(存储引擎)

    MySQL数据库系统支持多种表类型,也称为存储引擎。这些存储引擎各具特色,适用于不同的应用场景,从而满足用户在性能、可扩展性、事务安全性等方面的需求。以下是对MySQL中几种主要表类型的详细介绍: 1. **InnoDB*...

    mysql-存储引擎-实验四.docx

    在 MySQL 中,可以使用 ALTER TABLE 语句将表的存储引擎修改为其他类型。 4. 建立学生表,学号 3188909101 开始自动增长。 在实验中,我们建立了一个学生表,并使用自动增长列设置学号的初始值为 3188909101。在 ...

    mysql 存储引擎和表类型

    MySQL数据库系统支持多种存储引擎,每种引擎都有其特定的功能和...不同的存储引擎在表锁定策略、索引类型、数据持久化和恢复机制等方面存在差异,因此理解和掌握这些特点对于优化MySQL数据库的性能和可靠性至关重要。

    mysql存储引擎介绍

    MySQL 存储引擎详解 MySQL 存储引擎是 MySQL 数据库管理系统中的一种机制,用于管理和存储数据。 MySQL 提供了多种存储引擎,每种引擎都有其特点和优缺。了解 MySQL 存储引擎的特点和优缺是非常重要的,因为它们...

    mysql中的表引擎

    MySQL中的表引擎是数据库管理系统的重要组成部分,它们负责数据的存储、检索和管理。数据库引擎的定义可以理解为数据处理的核心服务,它解析SQL语句,执行数据操作,并控制访问权限,确保数据的安全和高效处理。在...

    MySQL数据库:MySQL存储引擎.pptx

    而MySQL数据库提供了多种存储引擎,用户可以根据不同的需求为数据表选择不同的存储引擎,用户也可以根据自己的需要编写自己的存储引擎,MySQL的核心就是存储引擎。 ;MySQL 5.7支持的存储引擎有:InnoDB、MyISAM、...

    mysql插件式存储引擎体系结构

    3. **灵活性**:MySQL允许在同一服务器或方案中使用多种不同的存储引擎,这意味着可以在不同表之间使用最适合的存储引擎来满足特定的需求。 #### 三、存储引擎的关键组件 在讨论存储引擎的具体实现之前,我们先来...

    mysql架构与存储引擎(MySQL逻辑架构、InnoDB引擎、MyISAM引擎、存储引擎选择).docx

    ### MySQL架构与存储引擎 #### 一、MySQL逻辑架构 MySQL作为一款广泛使用的开源关系型数据库管理系统,其内部架构设计非常精妙,旨在提供高效、可靠的数据存储与管理能力。 ##### 1、逻辑架构图 MySQL采用了典型...

    mysql-存储引擎和数据库对象实验五.docx

    本实验报告主要介绍 MySQL 中的存储引擎和数据库对象,涵盖了 MEMORY 存储引擎、MERGE 存储引擎、CHAR、VARCHAR、TEXT、BLOB 等类型的使用和特点,以及浮点数、定点数、日期类型的使用和设置规则。 一、MEMORY 存储...

    MySQL存储引擎解析.doc

    其中,存储引擎是专门处理表数据的模块,不同类型的存储引擎决定了数据的存储格式、索引类型以及并发控制策略。 MySQL提供了多种存储引擎,常见的如MyISAM和InnoDB。MyISAM是早期常用的引擎,它支持表级锁定,这...

    mysql存储引擎比较

    ### MySQL存储引擎比较 #### 一、概述 MySQL是一款广泛使用的开源关系型数据库管理系统,在不同的应用场景下,选择合适的存储引擎至关重要。MySQL支持多种存储引擎,每种存储引擎都有其独特的特性和适用场景。本文...

    mysql 存储引擎1

    在MySQL中,你可以通过以下命令来查看当前数据库中所有表的存储引擎: ```sql SHOW TABLE STATUS; ``` 此命令会列出数据库中所有表的信息,包括表名、引擎类型、数据大小、索引大小等。你也可以针对特定数据库或表...

    MySQL+InnoDB存储引擎的一些参数.doc

    - **表存储类型说明符**:MySQL 3.23后,用户可以选择存储引擎,如MYISAM(默认)、ISAM和HEAP。MYISAM提供更好的空间利用率,支持大文件,且在跨平台时保持兼容性。ISAM是较老的格式,而HEAP是内存中的表,速度极...

    MySql的存储引擎.doc

    查看表的存储引擎类型,可以使用`DESCRIBE tablename;`或`SHOW CREATE TABLE tablename;`命令。 了解并选择合适的存储引擎对于优化MySQL数据库的性能至关重要。比如,如果应用程序需要事务处理和高并发,InnoDB可能...

    MySQL数据库:存储引擎深入解析

    **存储引擎**,也称为**表类型**,是MySQL数据库系统中负责数据的存储与检索的核心组件。每种存储引擎都拥有自己独特的优势和局限性,比如支持的特性(如事务处理、索引类型、存储格式等)、性能表现以及适用场景等...

    浅谈MySql的存储引擎(表类型)

    MySQL的一大特色是其插入式存储引擎机制,这意味着它允许用户为不同的数据表选择不同的存储方式,以满足特定的需求。存储引擎决定了数据如何存储、索引如何创建以及数据的更新和查询如何执行。由于数据在关系数据库...

    可插式存储引擎MySQL走向企业级保障

    总结来说,MySQL 的可插式存储引擎是其企业级特性的重要组成部分,它使得MySQL能够适应各种复杂业务场景,满足不同类型的性能需求,从而成为广泛应用的开源数据库解决方案。理解并灵活运用这些存储引擎,可以显著...

Global site tag (gtag.js) - Google Analytics