MySQL存储引擎

MySQL各存储引擎介绍

        MySQL中存储引擎常用的有：InnoDB、MyISAM、memory。

        MySQL默认支持的是InnoDB。

        InnoDB存储引擎：InnoDB存储引擎提供了具有提交、回滚等事务功能，支持外键，支持自动增长列等功能。InnoDB采用的是聚集性索引，索引和数据是存储在同一个文件下的，所以InnoDB的表在磁盘上有两个文件：.frm：存储表的结构，.ibd：存储索引和表的数据。

        MyISAM存储引擎：MyISAM存储引擎不支持事务，不支持外键，索引采用的是非聚集性索引。所以MyISAM的表在磁盘上存储有三个文件，文件扩展名.frm：存储表的结构，.MYI：存储表的索引结构，.MYD：存储表的数据。

        memory存储引擎：memory使用内存来创建表，每个memory表的实际内存存储在内存中，表的定义结构在磁盘中，memory存储引擎访问数据非常快，memory的数据结构是通过哈希索引，一旦服务关闭，内存上的数据就会丢失。

存储引擎相关命令

        ① 查看存储引擎：show engines;

        ② 创建表时指定存储引擎：create table (字段 字段类型...) ENGINES = 引擎名；

        ③ 修改表的存储引擎：alter table 表名 engine = 存储引擎名

        我们可以修改MySQL配置文件来达到对全部标的存储引擎的修改，在windows环境下，修改my.ini配置文件。[mysqld]下添加 default-storage-engine = innodb;，添加后重启MySQL服务即可。

MyISAM和InnoDB的区别

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

        通过上面我们可以看到，MyISAM使用的是表锁，InnoDB使用的是行锁。

        表级锁：对当前操作的整张表进行加锁，是一个粒度比较大的锁，实现比较简单，资源消耗比较小，加锁比较快，不会出现死锁，粒度较大，触发 锁的冲突概率的最该，并发度最大。

        行级锁：只对当前操作的数据行进行加锁，是一种粒度比较小的锁，行锁能大大降低数据库操作的冲突，并发度是比较高的，枷锁开销大，加锁比较慢，会出现死锁。

        特别注意一点：InnoDB行级锁是通过给索引上的索引项来进行加锁的，不是对数据行进行加锁的。也就是说，只能通过索引条件来检索数据，InnoDB才能使用行级锁，如果没有索引存在，InnoDB会退换成表级锁。

        我们来测试一下,使用 for update主动获取锁。还是用两个MySQL窗口。

        test表如图所示，使用的是InnoDB存储引擎。

        我们使用for update来模拟获取锁的过程，在窗口1查询id = 1的学生信息，窗口2查询id = 2 的学生信息，如下图所示：

        我们发现，窗口一正常返回数据，窗口二并没有正常返回，这就是由于，我们的查询条件 id 不是索引结构，索引InnoDB的会退化成表锁。当我们使用commit;提交第一个窗口的查询语句后，第二个窗口才会显示出数据。

        我们将id字段设置为 主键索引，如下：
create index id_index on test(id);

        我们再去模仿并发查询id = 1 和 id = 2的学生信息。

        我们发现，此时，两个窗口都可以正常查询，这就是因为，InnoDB的行锁是对 索引项 进行加锁的，我们使用id字段作为条件查询，此时，在底层就会生成一个包含id字段中所有值的B+树，InnoDB会对这个B+树中的每个数据项进行加锁，进而实现了并发查询。