MySQL存储引擎
- MySQL存储引擎
- 1、什么是存储引擎？
- 2、存储引擎的分类
- 2.1、InnoDB存储引擎特征（mysql默认存储引擎）
- 2.2、MyISAM存储引擎特征
- 3、InnoDB & MyISAM的区别（基于上述特征，重点，五颗星）
- 4、常见其他问题（重要）
- 参考
MySQL存储引擎
1、什么是存储引擎？
MySQL的逻辑架构如下：存储引擎在MySQL的逻辑架构中位于第三层。
数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。
- 简单来说：可以把存储引擎看作是
一种存储文件的方式以及该方式附属的一整套工具，其中每个存储文件方式的特点就是该存储引擎的特点。 - 例如：Memory存储引擎将数据保存至内存中，其优点是：读写快，但是数据不是持久到磁盘中的，非常容易丢失等等。
MySQL 5.7 支持的存储引擎有 InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE 等。可以使用show storage engines;语句查看系统所支持的引擎类型，结果如下所示：
mysql> show storage engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+---------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)2、存储引擎的分类
Mysql 底层数据引擎以插件形式设计，最常见的是 Innodb 引擎和 Myisam 引擎。
比如下面的例子，Mysql 建立表的时候就可以指定引擎，就是分别指定了 Myisam 和 Innodb（默认引擎） 作为 user2 表和 user 表的数据引擎。
执行这两个指令后，系统出现了以下的文件，说明这两个引擎数据和索引的组织方式是不一样的。
Innodb 创建表后生成的文件有：
-
frm:创建表的语句 -
idb:表里面的数据+索引文件
Myisam 创建表后生成的文件有：
-
frm:创建表的语句 -
MYD:表里面的数据文件（myisam data） -
MYI:表里面的索引文件（myisam index）
MyISAM 引擎把数据和索引分开了，一人一个文件，这叫做非聚集索引方式；Innodb 引擎把数据和索引放在同一个文件里了，这叫做聚集索引方式。
2.1、InnoDB存储引擎特征（mysql默认存储引擎）
- 1、支持事务。默认的事务隔离级别为
可重复读（REPEATABLE-READ），通过MVCC（并发版本控制）来实现。 - 2、使用的锁粒度默认为
行级锁，可以支持更高的并发；当然，也支持表锁。 - 3、支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度。
- 4、可以通过自动增长列，方法是
auto_increment。 - 5、配合一些热备工具可以支持在线热备份；
- 6、在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
- 7、对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；
- 8、InnoDB 表的
select count(*) 比 MyISAM慢很多；当执行select count(*) from t时，会先把数据读出来，一行一行的累加，最后返回总数量。 是的，真的会很慢。需要注意的是，当count(*)语句包含where条件时，两种表的操作是一样的。 - 9、
DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
对于InnoDB来说，最大的优势在于支持事务，当然这是以牺牲效率为代价的。
2.2、MyISAM存储引擎特征
- 1、
不支持事务。像是挣脱了枷锁，在读写（Insert、select）效率上，要高于InnoDB不少。场景在：日志记录、调查统计表时，绝对值得一用。对了，不支持事务，自然就不支持锁！ - 2、体积小，质量大。MyISAM的
索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。同时能加载更多索引，而Innodb的索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比 MyISAM 数据文件体积庞大很多。 - 3、从以往经验来说，
select count(*) 和 orderby大概是使用最频繁的，大概能占了整个sql总语句的60%以上的操作，而这种操作Innodb其实也是会锁表的，很多人以为Innodb是行级锁，那个只是where对它主键是有效，非主键的还是会锁全表的。 - 4、如果和 MyISAM 比Insert写操作的话，Innodb还达不到MyISAM的写性能，如果是针对基于索引的update操作，确实MyISAM会慢与InnoDB，但在并发环境下，从库同步也是个事儿，还不如通过多实例分库分表架构来解决。
- 5、MyISAM表的select count(*) 是非常快的；在 MyISAM存储引擎中，把表的总行数（row）存储在磁盘上，当执行
select count(*) from t时，直接返回总数据。同样，当count(*)语句包含where条件时，两种表的操作是一样的。 - 6、DELETE FROM table时，MyISAM会先将表结构备份到一张虚拟表中，然后执行drop，最后根据备份重建该表。
3、InnoDB & MyISAM的区别（基于上述特征，重点，五颗星）
序号 | 功能 | InnoDB | MyISAM |
1 | 事务 | 支持 | 不支持 |
2 | 行级锁 | 支持 | 不支持 |
3 | MVCC | 支持 | 不支持 |
4 | 外键 | 支持 | 不支持 |
5 | 全文索引 | 支持 | 支持 |
6 | AUTO_INCREMENT | InnoDB中必须包含AUTO_INCREMENT类型字段的索引 | MyISAM中可以使AUTO_INCREMENT类型字段建立联合索引 |
7 | 表压缩 | 表格很难被压缩 | 表格可以被压缩 |
8 | count(*) 行数 | 不保存表的具体行数，扫描表来计算有多少行 | 保存表的具体行数,不带where时，直接返回保存的行数 |
9 | 清空整个表 | InnoDB是一行一行的删除，效率非常慢 | DELETE 表时，先drop表，然后重建表 |
10 | 索引和数据存储 | InnoDB 把数据和索引存放在表空间里面 | MyISAM 表被存放在三个文件 。frm 文件存放表格定义。 数据文件是MYD (MYData) 。 索引文件是MYI (MYIndex)引伸 |
4、常见其他问题（重要）
InnoDB： count(*) 时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，那么为什么InnoDB没有了这个变量呢？
- 因为InnoDB的
事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。 - InnoDB会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在，InnoDB还会尝试去遍历其他聚簇索引。
- 如果索引并没有完全处于InnoDB维护的缓冲区（BufferPool）中，count操作会比较费时。可以建立一个记录总行数的表并让你的程序在INSERT/DELETE时更新对应的数据。和上面提到的问题一样，如果此时存在多个事务的话这种方案也不太好用。如果得到大致的行数值已经足够满足需求可以尝试SHOW TABLE STATUS
MyISAM与InnoDB表锁和行锁的解释
MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。什么意思呢，就是说对MyISAM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作；而对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。
InnoDB行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁！行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源。在InnoDB两个事务发生死锁的时候，会计算出每个事务影响的行数，然后回滚行数少的那个事务。当锁定的场景中不涉及Innodb的时候，InnoDB是检测不到的。只能依靠锁定超时来解决。
MyISAM查询性能比InnoDB更高
InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。
MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
存储数据模式如下：
综上特点：
- MyISAM 直接找到物理地址后就可以直接定位到数据记录；
- InnoDB查询到叶子节点后，还需要再查询一次主键索引树，才可以定位到具体数据。
- 等于 MyISAM 一步就查到了数据，但是 InnoDB要两步，那当然 MyISAM 查询性能更高。
MyISAM与InnoDB使用场景
MyISAM：
- （1）做很多count 的计算；
- （2）插入不频繁，查询非常频繁，如果执行大量的SELECT，MyISAM是更好的选择；
- （3）没有事务。
InnoDB：
- （1）
可靠性要求比较高，或者要求事务； - （2）表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定数据引擎的创建；
- （3）如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表；
- （4）DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的 删除；
- （5）LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适
