MySQL存储引擎介绍

一、MySQL存储引擎

MySQL种的存储引擎常用的是InnoDB和MylSAM，其次是memory。
MySQL默认的存储引擎是InnoDB。

1.INNODB

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

2.MyISAM

1.MyISAM存储引擎不支持事务、不支持外键，索引采用的是非聚集索引，有事再访问速度上较快，对事物没有要求或者 select、insert等为主的应用基本上可以采用这种存储引擎。
2.MyISAM的表再磁盘上的存储有三个文件，文件扩展名为.frm（存储表的定义）、.MY（MYIndex：存储引擎）、.MYD（MYData：存储数据）。

3.momery

1.momery存储引擎是使用内存来创建表，每个momery表实际内存存储在内存中，表的定义结构在磁盘中。momery存储引擎数据访问非常快，其数据组织是通过哈希索引（不支持范围查询）。
2.服务关闭，存在内存上的东西就会消失。

4.与存储引擎有关的sql命令

1.show engines \G：查看数据库支持哪些存储引擎

mysql> show engines \G
*************************** 1. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 9. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.46 sec)

2.alter table student engine=myisam; 修改表的存储引擎

mysql> alter table student engine=myisam;
Query OK, 7 rows affected (1.28 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> show create table student;
+---------+-----------------------------------+
| Table   | Create Table                                                                                                                                                                                                                    |
+---------+------------------------------------+
| student | CREATE TABLE `student` (
  `sid` bigint(20) DEFAULT NULL,
  `sname` varchar(255) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `sage` int(11) DEFAULT NULL,
  KEY `inx_all` (`sid`,`sage`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

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

二、MyLSAM和InnoDB存储引擎区别

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

锁机制:表示数据库在并发请求访问的时候，多个事务在操作时，并发操作的粒度B-树索引和哈希索引:主要是加速SQL的查询速度
外键:子表的字段依赖父表的主键，设置两张表的依赖关系
事务︰多个SQL语句，保证他们共同执行的原子操作，要么成功，要么失败，不能只成功一部分，失败需要考虑回滚事务
索引缓存和数据缓存︰和MYSQL server的查询缓存相关，在没有对数据和索引做修改之前，重复查询可以不用进行磁盘l/O，读取上一次内存中查询的缓存就可以了。

三、MyLSAM和InnoDB存储引擎使用的锁

MylSAM使用的表级锁：表级锁对当前操作的整张表进行加锁，是一个力度较大的锁，实现比较简单。资源消耗较小，加锁快，会出现死锁。触发锁的冲突的概率大，并发度最大。
INNODB采用的是行级锁：行级锁只对当前操作的数据行进行加锁，是一种力度比较小的锁，行级锁能大大降低数据库操作的冲突，并发度是比较高的，加锁的开销是比较大，加锁比较慢，会出现死锁。

INNODB行级锁是通过给索引上的索引项进行加锁来实现的，而不是对数据行进行加锁
意味着只能通过索引条件来检索数据，INNODB才能使用行级锁，如果没有索引存在，INNODB将使用表级锁。