MySQL存储引擎介绍

一、各存储引擎介绍

• MySQL中的存储引擎常用的INNODB和MyISAM，其次是memory

• MySQL默认支持的是INNODB

• INNODB:

  • INNODB存储引擎提供了具有提交、回滚等事务功能、支持自动增长列，外键等功能，
  • INNODB采用聚集索引，索引和数据是存储在同一个文件下，所以INNODB的表的磁盘上存储两个文 件，扩展名
  • .frm(存储表的定义)和.ibd(索引和数据)

• MyISAM:

  • MyISAM存储引擎不支持事务，也不支持外键，索引采用的是非聚集性索引，优势在访问速度比较快,对事务没有要求或者select、insert等为主的应用基本上可以采用这个存储引擎
  • 项目MyISAM的表在磁盘上存储就有三个文件，文件扩展名为
    .frm(存储表的定义)
    .MYI(MYIndex:存储索引)
    .MYD(MYData:存储数据)

• momery:

momery存储引擎是使用内存中来创建表，每个momery表的实际内存存储在内存在内存中，表的定义结构在磁盘中，momery存储引擎数据访问非常快，momery的数据组织是通过哈希索引(不支持范围询)，一旦服务关闭，内存上的数据就会丢失

二、存储引擎相关命令

• 查看存储引擎

show engines;

• 查看表的存储引擎

SHOW CREATE TABLE 表名;

• 创建表时指定存储引擎

create table 表名 engine=MyISAM;

• 修改表的存储引擎

alter table 表名 engine=myisam;

• 注意：
  可以修改MySQL的配置文件来达到对全部表的存储引擎修改，在windows下是my.ini配置文件，Linux是my.cnf配置文件[mysqld]下添加一行配置配置完保存并重启MySQL Server服务器defult-storage-engine=innodb;

三、MyISAM和InnoDB区别

区别：

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

3. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
   MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
   也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；

5. Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了

6. MyISAM表格可以被压缩后进行查询操作

7. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

8. InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有

9. Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
   Innodb：frm是表定义文件，ibd是数据文件
   Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

种类	锁机制	B-树索引	哈希索引	外键	事务	索引缓存	数据缓存
MYISAM	表锁	支持	不支持	不支持	不支持	支持	不支持
INNODB	行锁	支持	不支持	支持	支持	支持	支持
memory	表锁	支持	支持	不支持	不支持	支持	支持

• 锁机制：表示数据库在并发请求访问的时候，多个事务在操作时，并发操作的粒度
• B-树索引和哈希索引：主要是加速SQL的查询速度
• 外键：子表的字段依赖父表的主键，设置两张表的依赖关系
• 事务：多个SQL语句，保证他们共同执行的原子操作，要么成功，要么失败，不能只成功一部分，失败需要考虑回滚事务.
• 索引缓存和数据缓存：和MYSQL server的查询缓存相关，在没有对数据和索引做修改之前，重复查询可以不用进行磁盘I/O，读取上一次内存中查询的缓存就可以了。
• MyISAM和INNODB存储引擎使用的锁
  MyISAM使用的表级锁
  INNODB采用的是行即锁
• 表级锁：对当前操作的整张表进行加锁，是一个粒度比较大的锁，实现比较简单，资源消耗比较小，加锁比较快，不会出现死锁。粒度比较大，触发锁的冲突的概率最高，并发度最大。
• 行级锁：只对当前操作的数据行进行加锁，是一种粒度比较小的锁，行级锁能大大降低数据库操作的冲突，并发度是比较高的，加锁的开销是比较大，加锁比较慢，会出现死锁。
• INNODB行级锁是通过给索引上的索引项进行加锁来实现的，而不是对数据行进行加锁
  意味着只能通过索引条件来检索数据，INNODB才能使用行级锁，如果没有索引存在，INNODB将使用表级锁。