MySQL 学习心得和知识总结(三)

MySQL最大的特色就是插件式的存储引擎，所以说对于MySQL的常用存储引擎，尤其是MyISAM和InnoDB要有清晰的认识。至于插件式：插入不同的存储引擎进去，就会展示出不同的操作方法。MySQL常用的存储引擎有：MyISAM、InNoDB和Memory，其默认的是InNoDB。MyISAM、InNoDB的数据和索引都是存放在磁盘上，要进行操作的话 先要从磁盘上加载到内存上的。MyISAM是先把索引加载到内存上，通过索引找到对应的位置 再到磁盘里面把数据加载到内存上，通过这个位置再把数据真正的读取出来。

MyISAM

MyISAM用的是非聚集索引，数据和索引是不存放在一起的。MyISAM 不支持事务（无论执行的SQL是一条还是多条，其执行过程要么全部成功 要么全部失败）、也不支持外键、索引采用非聚集索引。每次查询具有原子性,其执行数度比InnoDB类型更快。非聚集索引的优势是访问的速度快。对事务完整性没有要求，或者 以 SELECT、INSERT 为主的应用基本上都可以使用这个存储引擎来创建表。MyISAM的表在磁盘上存储成 3 个文件，其文件名都和表名相同，扩展名分别是：.frm（存储表定义） .MYD（MYData，存储数据） .MYI （MYIndex，存储索引）。MyISAM是非事务安全型的，而InnoDB是事务安全型的，默认开启自动提交，宜合并事务，一同提交，减小数据库多次提交导致的开销，大大提高性能。

注：MyISAM的存储空间可被压缩，且空间相对较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。

InnoDB

InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全，支持自动增长列，外键等功能。索引采用的是聚集索引，索引和数据存储在同一个文件，所以InnoDB存储引擎的表在磁盘上有两个文件，其文件名都和表名相同，扩展名分别是：
.frm（存储表的定义）.ibd（存储数据和索引）。与传统的ISAM与MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事务（Transaction）功能，InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响速度，所以最好是把多条SQL语句显示放在begin和commit之间，组成一个事务去提交。InnoDB的存储空间需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

Memory

MEMORY 存储引擎使用存在内存中的内容来创建表。每个 MEMORY 表实际只对应一个磁盘文件，格式是.frm（表的结构定义）。MEMORY 类型的表访问（读或者写）非常得快，因为它的数据是放在内存中的，并且默认使用 HASH 索引（不适合做范围查询），但是一旦服务关闭（MySQL server的一个实例），表中的数据就会丢失掉。

MySQL存储引擎的差异

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

1. 锁机制：表示数据库在并发请求访问的时候，多个事务在操作时，并发操作的粒度不一样，这里涉及到表锁 或者 行锁。
2. B-树索引和哈希索引：主要是加速SQL的查询速度。
3. 外键：子表的字段依赖父表的主键，目的是：设置两张表的依赖关系。
4. 事务：多个SQL语句，保证它们共同执行的原子操作，要么全部成功，要么全部失败。不能只成功一部分，失败需要回滚事务。
5. 索引缓存和数据缓存：和MySQL Server的查询缓存相关，在没有对数据和索引做修改之前，重复查询可以不用进行磁盘I/O（数据库的性能提升，各种优化措施，目的就是为了减少磁盘I/O操作来提升数据库访问效率），读取上一次内存中查询的缓存（索引缓存和数据缓存）就可以了。
6. 表主键：MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址。而InnoDB如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。InnoDB的主键范围更大，最大是MyISAM的2倍。
7. 表锁：在并发写的时候，同一时刻 只能有一个线程去写。
8. 行锁：锁的粒度会小一些，并发度会更高一些 同一时刻，操作这张表的线程就多一些。
9. 计数：没有where的count( * )使用MyISAM要比InnoDB快得多。因为MyISAM内置了一个计数器，count( * )时它直接从计数器中读，而InnoDB则必须扫描全表。所以在InnoDB上执行count( * )时一般要伴随where，且where中要包含主键以外的索引列。为什么这里特别强调“主键以外”？因为InnoDB中primary index是和raw data存放在一起的，而secondary index则是单独存放，然后有个指针指向主键。所以只是count( * )的话使用secondary index扫描更快，而主键则主要在扫描索引同时要返回raw data时的作用较大。MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。在并发很多的情况下，InnoDB的表现肯定要比MyISAM强很多。当然任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL的性能优势。

MySQL设置存储引擎

查看MySQL版本支持的哪些存储引擎

查看 MySQL 当前默认的存储引擎

查看表使用的引擎

创建表时指定存储引擎

修改表的存储引擎

当然 修改配置文件，MySQL server在启动时 会加载配置文件（然后对全部表的存储引擎的修改）。Windows下，
Linux下的my.cnf里面，里面有个mysqld下添加default-storage-engine=INNODB。就可以修改表的默认存储引擎了。

MyISAM和InnoDB的应用场景分析

1. MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
2. InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

总之：InnoDB 和 MyISAM 是在使用 MySQL 最常用的两个表类型，各有优缺点，视具体应用而定。基本的差别为：MyISAM 类型不支持事务处理等高级处理，而 InnoDB 类型支持。MyISAM 类型的表强调的是性能，其执行数度比 InnoDB 类型更快，但是不提供事务支持，而 InnoDB 提供事务支持已经外部键等高级数据库功能。
MyISAM 适合：(1)做很多 count 的计算；(2)插入不频繁，查询非常频繁；(3)没有事务。
InnoDB 适合：(1)可靠性要求比较高，或者要求事务；(2)表更新和查询都相当的频繁，并且表锁定的机会比较大的情况。